Anand Doshi | 885e074 | 2015-03-03 14:55:30 +0530 | [diff] [blame] | 1 | # Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors |
Rushabh Mehta | e67d1fb | 2013-08-05 14:59:54 +0530 | [diff] [blame] | 2 | # License: GNU General Public License v3. See license.txt |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 3 | |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 4 | |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 5 | from json import loads |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 6 | from typing import TYPE_CHECKING, List, Optional, Tuple |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 7 | |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 8 | import frappe |
Nabin Hait | 9b20e07 | 2017-04-25 12:10:24 +0530 | [diff] [blame] | 9 | import frappe.defaults |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 10 | from frappe import _, qb, throw |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 11 | from frappe.model.meta import get_field_precision |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 12 | from frappe.query_builder import AliasedQuery, Criterion, Table |
| 13 | from frappe.query_builder.functions import Sum |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 14 | from frappe.query_builder.utils import DocType |
Ankush Menat | 5c6f22f | 2022-06-15 19:30:26 +0530 | [diff] [blame] | 15 | from frappe.utils import ( |
| 16 | cint, |
| 17 | create_batch, |
| 18 | cstr, |
| 19 | flt, |
| 20 | formatdate, |
| 21 | get_number_format_info, |
| 22 | getdate, |
| 23 | now, |
| 24 | nowdate, |
| 25 | ) |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 26 | from pypika import Order |
| 27 | from pypika.terms import ExistsCriterion |
Anand Doshi | cd0989e | 2015-09-28 13:31:17 +0530 | [diff] [blame] | 28 | |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 29 | import erpnext |
| 30 | |
| 31 | # imported to enable erpnext.accounts.utils.get_account_currency |
| 32 | from erpnext.accounts.doctype.account.account import get_account_currency # noqa |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 33 | from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_dimensions |
Anurag Mishra | a11e738 | 2019-10-31 15:55:03 +0530 | [diff] [blame] | 34 | from erpnext.stock import get_warehouse_account_map |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 35 | from erpnext.stock.utils import get_stock_value_on |
| 36 | |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 37 | if TYPE_CHECKING: |
| 38 | from erpnext.stock.doctype.repost_item_valuation.repost_item_valuation import RepostItemValuation |
| 39 | |
Anurag Mishra | a11e738 | 2019-10-31 15:55:03 +0530 | [diff] [blame] | 40 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 41 | class FiscalYearError(frappe.ValidationError): |
| 42 | pass |
| 43 | |
| 44 | |
| 45 | class PaymentEntryUnlinkError(frappe.ValidationError): |
| 46 | pass |
| 47 | |
Nabin Hait | 2b06aaa | 2013-08-22 18:25:43 +0530 | [diff] [blame] | 48 | |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 49 | GL_REPOSTING_CHUNK = 100 |
| 50 | |
| 51 | |
Neil Trini Lasrado | 78fa695 | 2014-10-03 17:43:02 +0530 | [diff] [blame] | 52 | @frappe.whitelist() |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 53 | def get_fiscal_year( |
Deepesh Garg | 0aadb68 | 2023-03-19 12:46:42 +0530 | [diff] [blame] | 54 | date=None, fiscal_year=None, label="Date", verbose=1, company=None, as_dict=False, boolean=False |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 55 | ): |
Deepesh Garg | 0aadb68 | 2023-03-19 12:46:42 +0530 | [diff] [blame] | 56 | fiscal_years = get_fiscal_years( |
| 57 | date, fiscal_year, label, verbose, company, as_dict=as_dict, boolean=boolean |
| 58 | ) |
| 59 | if boolean: |
| 60 | return fiscal_years |
| 61 | else: |
| 62 | return fiscal_years[0] |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 63 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 64 | |
| 65 | def get_fiscal_years( |
Deepesh Garg | 0aadb68 | 2023-03-19 12:46:42 +0530 | [diff] [blame] | 66 | transaction_date=None, |
| 67 | fiscal_year=None, |
| 68 | label="Date", |
| 69 | verbose=1, |
| 70 | company=None, |
| 71 | as_dict=False, |
| 72 | boolean=False, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 73 | ): |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 74 | fiscal_years = frappe.cache().hget("fiscal_years", company) or [] |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 75 | |
| 76 | if not fiscal_years: |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 77 | # if year start date is 2012-04-01, year end date should be 2013-03-31 (hence subdate) |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 78 | FY = DocType("Fiscal Year") |
Nabin Hait | fff3ab7 | 2015-01-14 16:27:13 +0530 | [diff] [blame] | 79 | |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 80 | query = ( |
| 81 | frappe.qb.from_(FY) |
| 82 | .select(FY.name, FY.year_start_date, FY.year_end_date) |
| 83 | .where(FY.disabled == 0) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 84 | ) |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 85 | |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 86 | if fiscal_year: |
| 87 | query = query.where(FY.name == fiscal_year) |
| 88 | |
| 89 | if company: |
| 90 | FYC = DocType("Fiscal Year Company") |
| 91 | query = query.where( |
| 92 | ExistsCriterion(frappe.qb.from_(FYC).select(FYC.name).where(FYC.parent == FY.name)).negate() |
| 93 | | ExistsCriterion( |
| 94 | frappe.qb.from_(FYC) |
| 95 | .select(FYC.company) |
| 96 | .where(FYC.parent == FY.name) |
| 97 | .where(FYC.company == company) |
| 98 | ) |
| 99 | ) |
| 100 | |
Shridhar Patil | 69efd2e | 2022-10-03 11:07:24 +0530 | [diff] [blame] | 101 | query = query.orderby(FY.year_start_date, order=Order.desc) |
Gavin D'souza | 0727d1d | 2022-06-13 12:39:28 +0530 | [diff] [blame] | 102 | fiscal_years = query.run(as_dict=True) |
| 103 | |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 104 | frappe.cache().hset("fiscal_years", company, fiscal_years) |
Nabin Hait | fff3ab7 | 2015-01-14 16:27:13 +0530 | [diff] [blame] | 105 | |
Prssanna Desai | 82ddef5 | 2020-06-18 18:18:41 +0530 | [diff] [blame] | 106 | if not transaction_date and not fiscal_year: |
| 107 | return fiscal_years |
| 108 | |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 109 | if transaction_date: |
| 110 | transaction_date = getdate(transaction_date) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 111 | |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 112 | for fy in fiscal_years: |
| 113 | matched = False |
| 114 | if fiscal_year and fy.name == fiscal_year: |
| 115 | matched = True |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 116 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 117 | if ( |
| 118 | transaction_date |
| 119 | and getdate(fy.year_start_date) <= transaction_date |
| 120 | and getdate(fy.year_end_date) >= transaction_date |
| 121 | ): |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 122 | matched = True |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 123 | |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 124 | if matched: |
| 125 | if as_dict: |
| 126 | return (fy,) |
| 127 | else: |
| 128 | return ((fy.name, fy.year_start_date, fy.year_end_date),) |
| 129 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 130 | error_msg = _("""{0} {1} is not in any active Fiscal Year""").format( |
| 131 | label, formatdate(transaction_date) |
| 132 | ) |
Anuja P | 2e4faf9 | 2020-12-05 13:36:43 +0530 | [diff] [blame] | 133 | if company: |
Anuja P | 550cb9c | 2020-12-07 11:11:00 +0530 | [diff] [blame] | 134 | error_msg = _("""{0} for {1}""").format(error_msg, frappe.bold(company)) |
rohitwaghchaure | d60ff83 | 2021-02-16 09:12:27 +0530 | [diff] [blame] | 135 | |
Deepesh Garg | 0aadb68 | 2023-03-19 12:46:42 +0530 | [diff] [blame] | 136 | if boolean: |
| 137 | return False |
| 138 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 139 | if verbose == 1: |
| 140 | frappe.msgprint(error_msg) |
Deepesh Garg | 0aadb68 | 2023-03-19 12:46:42 +0530 | [diff] [blame] | 141 | |
cclauss | 6848708 | 2017-07-27 07:08:35 +0200 | [diff] [blame] | 142 | raise FiscalYearError(error_msg) |
Nabin Hait | 9784d27 | 2016-12-30 16:21:35 +0530 | [diff] [blame] | 143 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 144 | |
Prssanna Desai | 82ddef5 | 2020-06-18 18:18:41 +0530 | [diff] [blame] | 145 | @frappe.whitelist() |
| 146 | def get_fiscal_year_filter_field(company=None): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 147 | field = {"fieldtype": "Select", "options": [], "operator": "Between", "query_value": True} |
Prssanna Desai | 82ddef5 | 2020-06-18 18:18:41 +0530 | [diff] [blame] | 148 | fiscal_years = get_fiscal_years(company=company) |
| 149 | for fiscal_year in fiscal_years: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 150 | field["options"].append( |
| 151 | { |
| 152 | "label": fiscal_year.name, |
| 153 | "value": fiscal_year.name, |
| 154 | "query_value": [ |
| 155 | fiscal_year.year_start_date.strftime("%Y-%m-%d"), |
| 156 | fiscal_year.year_end_date.strftime("%Y-%m-%d"), |
| 157 | ], |
| 158 | } |
| 159 | ) |
Prssanna Desai | 82ddef5 | 2020-06-18 18:18:41 +0530 | [diff] [blame] | 160 | return field |
| 161 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 162 | |
Nabin Hait | 8bf5836 | 2017-03-27 12:30:01 +0530 | [diff] [blame] | 163 | def validate_fiscal_year(date, fiscal_year, company, label="Date", doc=None): |
| 164 | years = [f[0] for f in get_fiscal_years(date, label=_(label), company=company)] |
Rushabh Mehta | c2563ef | 2013-02-05 23:25:37 +0530 | [diff] [blame] | 165 | if fiscal_year not in years: |
Rushabh Mehta | d60acb9 | 2015-02-19 14:51:58 +0530 | [diff] [blame] | 166 | if doc: |
| 167 | doc.fiscal_year = years[0] |
| 168 | else: |
| 169 | throw(_("{0} '{1}' not in Fiscal Year {2}").format(label, formatdate(date), fiscal_year)) |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 170 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 171 | |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 172 | @frappe.whitelist() |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 173 | def get_balance_on( |
| 174 | account=None, |
| 175 | date=None, |
| 176 | party_type=None, |
| 177 | party=None, |
| 178 | company=None, |
| 179 | in_account_currency=True, |
| 180 | cost_center=None, |
| 181 | ignore_account_permission=False, |
| 182 | ): |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 183 | if not account and frappe.form_dict.get("account"): |
| 184 | account = frappe.form_dict.get("account") |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 185 | if not date and frappe.form_dict.get("date"): |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 186 | date = frappe.form_dict.get("date") |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 187 | if not party_type and frappe.form_dict.get("party_type"): |
| 188 | party_type = frappe.form_dict.get("party_type") |
| 189 | if not party and frappe.form_dict.get("party"): |
| 190 | party = frappe.form_dict.get("party") |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 191 | if not cost_center and frappe.form_dict.get("cost_center"): |
| 192 | cost_center = frappe.form_dict.get("cost_center") |
| 193 | |
Nabin Hait | 9837285 | 2020-07-30 20:52:20 +0530 | [diff] [blame] | 194 | cond = ["is_cancelled=0"] |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 195 | if date: |
Suraj Shetty | bfc195d | 2018-09-21 10:20:52 +0530 | [diff] [blame] | 196 | cond.append("posting_date <= %s" % frappe.db.escape(cstr(date))) |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 197 | else: |
| 198 | # get balance of all entries that exist |
| 199 | date = nowdate() |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 200 | |
Deepesh Garg | 8c21703 | 2019-07-16 09:41:01 +0530 | [diff] [blame] | 201 | if account: |
| 202 | acc = frappe.get_doc("Account", account) |
| 203 | |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 204 | try: |
Deepesh Garg | 96d40ec | 2020-07-03 22:59:00 +0530 | [diff] [blame] | 205 | year_start_date = get_fiscal_year(date, company=company, verbose=0)[1] |
Rushabh Mehta | 9f0d625 | 2014-04-14 19:20:45 +0530 | [diff] [blame] | 206 | except FiscalYearError: |
Nabin Hait | 23941aa | 2013-01-29 11:32:38 +0530 | [diff] [blame] | 207 | if getdate(date) > getdate(nowdate()): |
| 208 | # if fiscal year not found and the date is greater than today |
| 209 | # get fiscal year for today's date and its corresponding year start date |
| 210 | year_start_date = get_fiscal_year(nowdate(), verbose=1)[1] |
| 211 | else: |
| 212 | # this indicates that it is a date older than any existing fiscal year. |
| 213 | # hence, assuming balance as 0.0 |
| 214 | return 0.0 |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 215 | |
deepeshgarg007 | 2ddbebf | 2019-07-20 14:52:59 +0530 | [diff] [blame] | 216 | if account: |
deepeshgarg007 | e097d4f | 2019-07-20 14:49:32 +0530 | [diff] [blame] | 217 | report_type = acc.report_type |
deepeshgarg007 | 1ee3d04 | 2019-07-20 14:46:59 +0530 | [diff] [blame] | 218 | else: |
| 219 | report_type = "" |
| 220 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 221 | if cost_center and report_type == "Profit and Loss": |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 222 | cc = frappe.get_doc("Cost Center", cost_center) |
| 223 | if cc.is_group: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 224 | cond.append( |
| 225 | """ exists ( |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 226 | select 1 from `tabCost Center` cc where cc.name = gle.cost_center |
| 227 | and cc.lft >= %s and cc.rgt <= %s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 228 | )""" |
| 229 | % (cc.lft, cc.rgt) |
| 230 | ) |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 231 | |
| 232 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 233 | cond.append("""gle.cost_center = %s """ % (frappe.db.escape(cost_center, percent=False),)) |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 234 | |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 235 | if account: |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 236 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 237 | if not (frappe.flags.ignore_account_permission or ignore_account_permission): |
Rushabh Mehta | 2e7f9d2 | 2015-10-15 16:31:16 +0530 | [diff] [blame] | 238 | acc.check_permission("read") |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 239 | |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 240 | # different filter for group and ledger - improved performance |
Rushabh Mehta | 38c6b52 | 2015-04-23 13:14:17 +0530 | [diff] [blame] | 241 | if acc.is_group: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 242 | cond.append( |
| 243 | """exists ( |
Nabin Hait | 6b01abe | 2015-06-14 17:49:29 +0530 | [diff] [blame] | 244 | select name from `tabAccount` ac where ac.name = gle.account |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 245 | and ac.lft >= %s and ac.rgt <= %s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 246 | )""" |
| 247 | % (acc.lft, acc.rgt) |
| 248 | ) |
Anand Doshi | cd0989e | 2015-09-28 13:31:17 +0530 | [diff] [blame] | 249 | |
| 250 | # If group and currency same as company, |
Nabin Hait | 59f4fa9 | 2015-09-17 13:57:42 +0530 | [diff] [blame] | 251 | # always return balance based on debit and credit in company currency |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 252 | if acc.account_currency == frappe.get_cached_value("Company", acc.company, "default_currency"): |
Nabin Hait | 59f4fa9 | 2015-09-17 13:57:42 +0530 | [diff] [blame] | 253 | in_account_currency = False |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 254 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 255 | cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False),)) |
Anand Doshi | c75c1d7 | 2016-03-11 14:56:19 +0530 | [diff] [blame] | 256 | |
Nabin Hait | 6f17cf9 | 2014-09-17 14:11:22 +0530 | [diff] [blame] | 257 | if party_type and party: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 258 | cond.append( |
| 259 | """gle.party_type = %s and gle.party = %s """ |
| 260 | % (frappe.db.escape(party_type), frappe.db.escape(party, percent=False)) |
| 261 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 262 | |
Nabin Hait | 85648d9 | 2016-07-20 11:26:45 +0530 | [diff] [blame] | 263 | if company: |
Suraj Shetty | bfc195d | 2018-09-21 10:20:52 +0530 | [diff] [blame] | 264 | cond.append("""gle.company = %s """ % (frappe.db.escape(company, percent=False))) |
Anand Doshi | 0b031cd | 2015-09-16 12:46:54 +0530 | [diff] [blame] | 265 | |
Nabin Hait | dc76823 | 2015-08-17 11:27:00 +0530 | [diff] [blame] | 266 | if account or (party_type and party): |
Nabin Hait | c0e3b1a | 2015-09-04 13:26:23 +0530 | [diff] [blame] | 267 | if in_account_currency: |
Anand Doshi | 602e825 | 2015-11-16 19:05:46 +0530 | [diff] [blame] | 268 | select_field = "sum(debit_in_account_currency) - sum(credit_in_account_currency)" |
Nabin Hait | c0e3b1a | 2015-09-04 13:26:23 +0530 | [diff] [blame] | 269 | else: |
Anand Doshi | 602e825 | 2015-11-16 19:05:46 +0530 | [diff] [blame] | 270 | select_field = "sum(debit) - sum(credit)" |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 271 | bal = frappe.db.sql( |
| 272 | """ |
Nabin Hait | c0e3b1a | 2015-09-04 13:26:23 +0530 | [diff] [blame] | 273 | SELECT {0} |
Nabin Hait | dc76823 | 2015-08-17 11:27:00 +0530 | [diff] [blame] | 274 | FROM `tabGL Entry` gle |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 275 | WHERE {1}""".format( |
| 276 | select_field, " and ".join(cond) |
| 277 | ) |
| 278 | )[0][0] |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 279 | |
Nabin Hait | dc76823 | 2015-08-17 11:27:00 +0530 | [diff] [blame] | 280 | # if bal is None, return 0 |
| 281 | return flt(bal) |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 282 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 283 | |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 284 | def get_count_on(account, fieldname, date): |
Nabin Hait | 9837285 | 2020-07-30 20:52:20 +0530 | [diff] [blame] | 285 | cond = ["is_cancelled=0"] |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 286 | if date: |
Suraj Shetty | bfc195d | 2018-09-21 10:20:52 +0530 | [diff] [blame] | 287 | cond.append("posting_date <= %s" % frappe.db.escape(cstr(date))) |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 288 | else: |
| 289 | # get balance of all entries that exist |
| 290 | date = nowdate() |
| 291 | |
| 292 | try: |
| 293 | year_start_date = get_fiscal_year(date, verbose=0)[1] |
| 294 | except FiscalYearError: |
| 295 | if getdate(date) > getdate(nowdate()): |
| 296 | # if fiscal year not found and the date is greater than today |
| 297 | # get fiscal year for today's date and its corresponding year start date |
| 298 | year_start_date = get_fiscal_year(nowdate(), verbose=1)[1] |
| 299 | else: |
| 300 | # this indicates that it is a date older than any existing fiscal year. |
| 301 | # hence, assuming balance as 0.0 |
| 302 | return 0.0 |
| 303 | |
| 304 | if account: |
| 305 | acc = frappe.get_doc("Account", account) |
| 306 | |
| 307 | if not frappe.flags.ignore_account_permission: |
| 308 | acc.check_permission("read") |
| 309 | |
| 310 | # for pl accounts, get balance within a fiscal year |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 311 | if acc.report_type == "Profit and Loss": |
| 312 | cond.append( |
| 313 | "posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" % year_start_date |
| 314 | ) |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 315 | |
| 316 | # different filter for group and ledger - improved performance |
| 317 | if acc.is_group: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 318 | cond.append( |
| 319 | """exists ( |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 320 | select name from `tabAccount` ac where ac.name = gle.account |
| 321 | and ac.lft >= %s and ac.rgt <= %s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 322 | )""" |
| 323 | % (acc.lft, acc.rgt) |
| 324 | ) |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 325 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 326 | cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False),)) |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 327 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 328 | entries = frappe.db.sql( |
| 329 | """ |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 330 | SELECT name, posting_date, account, party_type, party,debit,credit, |
| 331 | voucher_type, voucher_no, against_voucher_type, against_voucher |
| 332 | FROM `tabGL Entry` gle |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 333 | WHERE {0}""".format( |
| 334 | " and ".join(cond) |
| 335 | ), |
| 336 | as_dict=True, |
| 337 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 338 | |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 339 | count = 0 |
| 340 | for gle in entries: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 341 | if fieldname not in ("invoiced_amount", "payables"): |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 342 | count += 1 |
| 343 | else: |
| 344 | dr_or_cr = "debit" if fieldname == "invoiced_amount" else "credit" |
| 345 | cr_or_dr = "credit" if fieldname == "invoiced_amount" else "debit" |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 346 | select_fields = ( |
| 347 | "ifnull(sum(credit-debit),0)" |
| 348 | if fieldname == "invoiced_amount" |
| 349 | else "ifnull(sum(debit-credit),0)" |
| 350 | ) |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 351 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 352 | if ( |
| 353 | (not gle.against_voucher) |
| 354 | or (gle.against_voucher_type in ["Sales Order", "Purchase Order"]) |
| 355 | or (gle.against_voucher == gle.voucher_no and gle.get(dr_or_cr) > 0) |
| 356 | ): |
| 357 | payment_amount = frappe.db.sql( |
| 358 | """ |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 359 | SELECT {0} |
| 360 | FROM `tabGL Entry` gle |
| 361 | WHERE docstatus < 2 and posting_date <= %(date)s and against_voucher = %(voucher_no)s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 362 | and party = %(party)s and name != %(name)s""".format( |
| 363 | select_fields |
| 364 | ), |
| 365 | {"date": date, "voucher_no": gle.voucher_no, "party": gle.party, "name": gle.name}, |
| 366 | )[0][0] |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 367 | |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 368 | outstanding_amount = flt(gle.get(dr_or_cr)) - flt(gle.get(cr_or_dr)) - payment_amount |
| 369 | currency_precision = get_currency_precision() or 2 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 370 | if abs(flt(outstanding_amount)) > 0.1 / 10**currency_precision: |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 371 | count += 1 |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 372 | |
RobertSchouten | 8d43b32 | 2016-09-20 13:41:39 +0800 | [diff] [blame] | 373 | return count |
| 374 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 375 | |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 376 | @frappe.whitelist() |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 377 | def add_ac(args=None): |
Saurabh | 2f02101 | 2017-01-11 12:41:01 +0530 | [diff] [blame] | 378 | from frappe.desk.treeview import make_tree_args |
| 379 | |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 380 | if not args: |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 381 | args = frappe.local.form_dict |
Saurabh | 2f02101 | 2017-01-11 12:41:01 +0530 | [diff] [blame] | 382 | |
Nabin Hait | 02d987e | 2017-02-17 15:50:26 +0530 | [diff] [blame] | 383 | args.doctype = "Account" |
Saurabh | 2f02101 | 2017-01-11 12:41:01 +0530 | [diff] [blame] | 384 | args = make_tree_args(**args) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 385 | |
Anand Doshi | 3e41fd1 | 2014-04-22 18:54:54 +0530 | [diff] [blame] | 386 | ac = frappe.new_doc("Account") |
Rushabh Mehta | 0dcb861 | 2016-05-31 07:22:37 +0530 | [diff] [blame] | 387 | |
Nabin Hait | 665568d | 2016-05-13 15:56:00 +0530 | [diff] [blame] | 388 | if args.get("ignore_permissions"): |
| 389 | ac.flags.ignore_permissions = True |
| 390 | args.pop("ignore_permissions") |
Rushabh Mehta | 0dcb861 | 2016-05-31 07:22:37 +0530 | [diff] [blame] | 391 | |
Anand Doshi | 3e41fd1 | 2014-04-22 18:54:54 +0530 | [diff] [blame] | 392 | ac.update(args) |
Saurabh | 0e47bfe | 2016-05-30 17:54:16 +0530 | [diff] [blame] | 393 | |
| 394 | if not ac.parent_account: |
| 395 | ac.parent_account = args.get("parent") |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 396 | |
Anand Doshi | f78d1ae | 2014-03-28 13:55:00 +0530 | [diff] [blame] | 397 | ac.old_parent = "" |
| 398 | ac.freeze_account = "No" |
Nabin Hait | a1ec7f1 | 2016-05-11 12:37:22 +0530 | [diff] [blame] | 399 | if cint(ac.get("is_root")): |
| 400 | ac.parent_account = None |
Rushabh Mehta | 0dcb861 | 2016-05-31 07:22:37 +0530 | [diff] [blame] | 401 | ac.flags.ignore_mandatory = True |
| 402 | |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 403 | ac.insert() |
Anand Doshi | 3e41fd1 | 2014-04-22 18:54:54 +0530 | [diff] [blame] | 404 | |
Anand Doshi | f78d1ae | 2014-03-28 13:55:00 +0530 | [diff] [blame] | 405 | return ac.name |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 406 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 407 | |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 408 | @frappe.whitelist() |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 409 | def add_cc(args=None): |
Saurabh | 2f02101 | 2017-01-11 12:41:01 +0530 | [diff] [blame] | 410 | from frappe.desk.treeview import make_tree_args |
| 411 | |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 412 | if not args: |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 413 | args = frappe.local.form_dict |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 414 | |
Nabin Hait | 02d987e | 2017-02-17 15:50:26 +0530 | [diff] [blame] | 415 | args.doctype = "Cost Center" |
Saurabh | 2f02101 | 2017-01-11 12:41:01 +0530 | [diff] [blame] | 416 | args = make_tree_args(**args) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 417 | |
Zarrar | 7ab70ca | 2018-06-08 14:33:15 +0530 | [diff] [blame] | 418 | if args.parent_cost_center == args.company: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 419 | args.parent_cost_center = "{0} - {1}".format( |
| 420 | args.parent_cost_center, frappe.get_cached_value("Company", args.company, "abbr") |
| 421 | ) |
Zarrar | 7ab70ca | 2018-06-08 14:33:15 +0530 | [diff] [blame] | 422 | |
Anand Doshi | 3e41fd1 | 2014-04-22 18:54:54 +0530 | [diff] [blame] | 423 | cc = frappe.new_doc("Cost Center") |
| 424 | cc.update(args) |
Saurabh | 0e47bfe | 2016-05-30 17:54:16 +0530 | [diff] [blame] | 425 | |
| 426 | if not cc.parent_cost_center: |
| 427 | cc.parent_cost_center = args.get("parent") |
| 428 | |
Anand Doshi | f78d1ae | 2014-03-28 13:55:00 +0530 | [diff] [blame] | 429 | cc.old_parent = "" |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 430 | cc.insert() |
Anand Doshi | f78d1ae | 2014-03-28 13:55:00 +0530 | [diff] [blame] | 431 | return cc.name |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 432 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 433 | |
Deepesh Garg | 016ed95 | 2023-06-20 13:22:32 +0530 | [diff] [blame] | 434 | def reconcile_against_document(args, skip_ref_details_update_for_pe=False): # nosemgrep |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 435 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 436 | Cancel PE or JV, Update against document, split if required and resubmit |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 437 | """ |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 438 | # To optimize making GL Entry for PE or JV with multiple references |
| 439 | reconciled_entries = {} |
| 440 | for row in args: |
| 441 | if not reconciled_entries.get((row.voucher_type, row.voucher_no)): |
| 442 | reconciled_entries[(row.voucher_type, row.voucher_no)] = [] |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 443 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 444 | reconciled_entries[(row.voucher_type, row.voucher_no)].append(row) |
| 445 | |
| 446 | for key, entries in reconciled_entries.items(): |
| 447 | voucher_type = key[0] |
| 448 | voucher_no = key[1] |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 449 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 450 | # cancel advance entry |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 451 | doc = frappe.get_doc(voucher_type, voucher_no) |
Subin Tom | b8845b9 | 2021-07-30 16:30:18 +0530 | [diff] [blame] | 452 | frappe.flags.ignore_party_validation = True |
ruthra kumar | 11cf694 | 2023-01-14 12:22:22 +0530 | [diff] [blame] | 453 | _delete_pl_entries(voucher_type, voucher_no) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 454 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 455 | for entry in entries: |
| 456 | check_if_advance_entry_modified(entry) |
| 457 | validate_allocated_amount(entry) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 458 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 459 | # update ref in advance entry |
| 460 | if voucher_type == "Journal Entry": |
| 461 | update_reference_in_journal_entry(entry, doc, do_not_save=True) |
| 462 | else: |
ruthra kumar | ed14d1c | 2023-04-22 17:24:35 +0530 | [diff] [blame] | 463 | update_reference_in_payment_entry( |
| 464 | entry, doc, do_not_save=True, skip_ref_details_update_for_pe=skip_ref_details_update_for_pe |
| 465 | ) |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 466 | |
| 467 | doc.save(ignore_permissions=True) |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 468 | # re-submit advance entry |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 469 | doc = frappe.get_doc(entry.voucher_type, entry.voucher_no) |
Deepesh Garg | 016ed95 | 2023-06-20 13:22:32 +0530 | [diff] [blame] | 470 | gl_map = doc.build_gl_map() |
ruthra kumar | 11cf694 | 2023-01-14 12:22:22 +0530 | [diff] [blame] | 471 | create_payment_ledger_entry(gl_map, update_outstanding="No", cancel=0, adv_adj=1) |
Deepesh Garg | 1894dc8 | 2023-06-23 21:53:34 +0530 | [diff] [blame] | 472 | |
| 473 | if voucher_type == "Payment Entry": |
Deepesh Garg | f2edc91 | 2023-06-27 18:11:47 +0530 | [diff] [blame] | 474 | doc.make_advance_gl_entries() |
ruthra kumar | 11cf694 | 2023-01-14 12:22:22 +0530 | [diff] [blame] | 475 | |
| 476 | # Only update outstanding for newly linked vouchers |
| 477 | for entry in entries: |
| 478 | update_voucher_outstanding( |
| 479 | entry.against_voucher_type, entry.against_voucher, entry.account, entry.party_type, entry.party |
| 480 | ) |
ruthra kumar | 524c175 | 2022-05-26 16:00:40 +0530 | [diff] [blame] | 481 | |
Subin Tom | b8845b9 | 2021-07-30 16:30:18 +0530 | [diff] [blame] | 482 | frappe.flags.ignore_party_validation = False |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 483 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 484 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 485 | def check_if_advance_entry_modified(args): |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 486 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 487 | check if there is already a voucher reference |
| 488 | check if amount is same |
| 489 | check if jv is submitted |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 490 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 491 | if not args.get("unreconciled_amount"): |
| 492 | args.update({"unreconciled_amount": args.get("unadjusted_amount")}) |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 493 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 494 | ret = None |
| 495 | if args.voucher_type == "Journal Entry": |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 496 | journal_entry = frappe.qb.DocType("Journal Entry") |
| 497 | journal_acc = frappe.qb.DocType("Journal Entry Account") |
| 498 | |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 499 | q = ( |
| 500 | frappe.qb.from_(journal_entry) |
Deepesh Garg | 3aead05 | 2023-06-22 11:41:43 +0530 | [diff] [blame] | 501 | .inner_join(journal_acc) |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 502 | .on(journal_entry.name == journal_acc.parent) |
Deepesh Garg | d81d606 | 2023-06-22 18:28:16 +0530 | [diff] [blame] | 503 | .select(journal_acc[args.get("dr_or_cr")]) |
| 504 | .where( |
| 505 | (journal_acc.account == args.get("account")) |
| 506 | & ((journal_acc.party_type == args.get("party_type"))) |
| 507 | & ((journal_acc.party == args.get("party"))) |
| 508 | & ( |
| 509 | (journal_acc.reference_type.isnull()) |
| 510 | | (journal_acc.reference_type.isin(["", "Sales Order", "Purchase Order"])) |
| 511 | ) |
| 512 | & ((journal_entry.name == args.get("voucher_no"))) |
| 513 | & ((journal_acc.name == args.get("voucher_detail_no"))) |
| 514 | & ((journal_entry.docstatus == 1)) |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 515 | ) |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 516 | ) |
| 517 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 518 | else: |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 519 | payment_entry = frappe.qb.DocType("Payment Entry") |
| 520 | payment_ref = frappe.qb.DocType("Payment Entry Reference") |
| 521 | |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 522 | q = ( |
| 523 | frappe.qb.from_(payment_entry) |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 524 | .select(payment_entry.name) |
| 525 | .where(payment_entry.name == args.get("voucher_no")) |
| 526 | .where(payment_entry.docstatus == 1) |
| 527 | .where(payment_entry.party_type == args.get("party_type")) |
| 528 | .where(payment_entry.party == args.get("party")) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 529 | ) |
rohitwaghchaure | e8358f3 | 2018-05-16 11:02:26 +0530 | [diff] [blame] | 530 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 531 | if args.voucher_detail_no: |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 532 | q = ( |
| 533 | q.inner_join(payment_ref) |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 534 | .on(payment_entry.name == payment_ref.parent) |
| 535 | .where(payment_ref.name == args.get("voucher_detail_no")) |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 536 | .where(payment_ref.reference_doctype.isin(("", "Sales Order", "Purchase Order"))) |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 537 | .where(payment_ref.allocated_amount == args.get("unreconciled_amount")) |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 538 | ) |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 539 | else: |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 540 | q = q.where(payment_entry.unallocated_amount == args.get("unreconciled_amount")) |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 541 | |
Gursheen Anand | 7461926 | 2023-06-02 17:13:51 +0530 | [diff] [blame] | 542 | ret = q.run(as_dict=True) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 543 | |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 544 | if not ret: |
Akhilesh Darjee | 4f72156 | 2014-01-29 16:31:38 +0530 | [diff] [blame] | 545 | throw(_("""Payment Entry has been modified after you pulled it. Please pull it again.""")) |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 546 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 547 | |
Nabin Hait | 576f025 | 2014-04-30 19:30:50 +0530 | [diff] [blame] | 548 | def validate_allocated_amount(args): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 549 | precision = args.get("precision") or frappe.db.get_single_value( |
| 550 | "System Settings", "currency_precision" |
| 551 | ) |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 552 | if args.get("allocated_amount") < 0: |
Kenneth Sequeira | a29ed40 | 2019-05-27 11:47:07 +0530 | [diff] [blame] | 553 | throw(_("Allocated amount cannot be negative")) |
Deepesh Garg | f54d596 | 2021-03-31 14:06:02 +0530 | [diff] [blame] | 554 | elif flt(args.get("allocated_amount"), precision) > flt(args.get("unadjusted_amount"), precision): |
Kenneth Sequeira | a29ed40 | 2019-05-27 11:47:07 +0530 | [diff] [blame] | 555 | throw(_("Allocated amount cannot be greater than unadjusted amount")) |
Nabin Hait | 576f025 | 2014-04-30 19:30:50 +0530 | [diff] [blame] | 556 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 557 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 558 | def update_reference_in_journal_entry(d, journal_entry, do_not_save=False): |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 559 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 560 | Updates against document, if partial amount splits into rows |
Nabin Hait | fb3fd6e | 2013-01-30 19:16:13 +0530 | [diff] [blame] | 561 | """ |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 562 | jv_detail = journal_entry.get("accounts", {"name": d["voucher_detail_no"]})[0] |
Rushabh Mehta | 1828c12 | 2015-08-10 17:04:07 +0530 | [diff] [blame] | 563 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 564 | if flt(d["unadjusted_amount"]) - flt(d["allocated_amount"]) != 0: |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 565 | # adjust the unreconciled balance |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 566 | amount_in_account_currency = flt(d["unadjusted_amount"]) - flt(d["allocated_amount"]) |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 567 | amount_in_company_currency = amount_in_account_currency * flt(jv_detail.exchange_rate) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 568 | jv_detail.set(d["dr_or_cr"], amount_in_account_currency) |
| 569 | jv_detail.set( |
| 570 | "debit" if d["dr_or_cr"] == "debit_in_account_currency" else "credit", |
| 571 | amount_in_company_currency, |
| 572 | ) |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 573 | else: |
| 574 | journal_entry.remove(jv_detail) |
Anand Doshi | 0b031cd | 2015-09-16 12:46:54 +0530 | [diff] [blame] | 575 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 576 | # new row with references |
| 577 | new_row = journal_entry.append("accounts") |
Anuja Pawar | 1a6e98e | 2021-10-29 20:52:47 +0530 | [diff] [blame] | 578 | |
| 579 | new_row.update((frappe.copy_doc(jv_detail)).as_dict()) |
Rushabh Mehta | 2e7f9d2 | 2015-10-15 16:31:16 +0530 | [diff] [blame] | 580 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 581 | new_row.set(d["dr_or_cr"], d["allocated_amount"]) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 582 | new_row.set( |
| 583 | "debit" if d["dr_or_cr"] == "debit_in_account_currency" else "credit", |
| 584 | d["allocated_amount"] * flt(jv_detail.exchange_rate), |
| 585 | ) |
Rushabh Mehta | 2e7f9d2 | 2015-10-15 16:31:16 +0530 | [diff] [blame] | 586 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 587 | new_row.set( |
| 588 | "credit_in_account_currency" |
| 589 | if d["dr_or_cr"] == "debit_in_account_currency" |
| 590 | else "debit_in_account_currency", |
| 591 | 0, |
| 592 | ) |
| 593 | new_row.set("credit" if d["dr_or_cr"] == "debit_in_account_currency" else "debit", 0) |
Rushabh Mehta | 2e7f9d2 | 2015-10-15 16:31:16 +0530 | [diff] [blame] | 594 | |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 595 | new_row.set("reference_type", d["against_voucher_type"]) |
| 596 | new_row.set("reference_name", d["against_voucher"]) |
| 597 | |
| 598 | new_row.against_account = cstr(jv_detail.against_account) |
| 599 | new_row.is_advance = cstr(jv_detail.is_advance) |
| 600 | new_row.docstatus = 1 |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 601 | |
| 602 | # will work as update after submit |
Anuja Pawar | 3e404f1 | 2021-08-31 18:59:29 +0530 | [diff] [blame] | 603 | journal_entry.flags.ignore_validate_update_after_submit = True |
| 604 | if not do_not_save: |
| 605 | journal_entry.save(ignore_permissions=True) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 606 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 607 | |
ruthra kumar | ed14d1c | 2023-04-22 17:24:35 +0530 | [diff] [blame] | 608 | def update_reference_in_payment_entry( |
| 609 | d, payment_entry, do_not_save=False, skip_ref_details_update_for_pe=False |
| 610 | ): |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 611 | reference_details = { |
| 612 | "reference_doctype": d.against_voucher_type, |
| 613 | "reference_name": d.against_voucher, |
| 614 | "total_amount": d.grand_total, |
| 615 | "outstanding_amount": d.outstanding_amount, |
| 616 | "allocated_amount": d.allocated_amount, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 617 | "exchange_rate": d.exchange_rate |
| 618 | if not d.exchange_gain_loss |
| 619 | else payment_entry.get_exchange_rate(), |
| 620 | "exchange_gain_loss": d.exchange_gain_loss, # only populated from invoice in case of advance allocation |
Gursheen Anand | b65e58c | 2023-06-08 18:15:37 +0530 | [diff] [blame] | 621 | "account": d.account, |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 622 | } |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 623 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 624 | if d.voucher_detail_no: |
| 625 | existing_row = payment_entry.get("references", {"name": d["voucher_detail_no"]})[0] |
| 626 | original_row = existing_row.as_dict().copy() |
| 627 | existing_row.update(reference_details) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 628 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 629 | if d.allocated_amount < original_row.allocated_amount: |
| 630 | new_row = payment_entry.append("references") |
| 631 | new_row.docstatus = 1 |
Achilles Rasquinha | efb7319 | 2018-05-23 01:01:24 -0500 | [diff] [blame] | 632 | for field in list(reference_details): |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 633 | new_row.set(field, original_row[field]) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 634 | |
Nabin Hait | 28a0528 | 2016-06-27 17:41:39 +0530 | [diff] [blame] | 635 | new_row.allocated_amount = original_row.allocated_amount - d.allocated_amount |
| 636 | else: |
| 637 | new_row = payment_entry.append("references") |
| 638 | new_row.docstatus = 1 |
| 639 | new_row.update(reference_details) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 640 | |
Rohit Waghchaure | a2408a6 | 2019-06-24 01:52:48 +0530 | [diff] [blame] | 641 | if d.difference_amount and d.difference_account: |
Saqib | a20999c | 2021-07-12 14:33:23 +0530 | [diff] [blame] | 642 | account_details = { |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 643 | "account": d.difference_account, |
| 644 | "cost_center": payment_entry.cost_center |
| 645 | or frappe.get_cached_value("Company", payment_entry.company, "cost_center"), |
Saqib | a20999c | 2021-07-12 14:33:23 +0530 | [diff] [blame] | 646 | } |
| 647 | if d.difference_amount: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 648 | account_details["amount"] = d.difference_amount |
Saqib | a20999c | 2021-07-12 14:33:23 +0530 | [diff] [blame] | 649 | |
| 650 | payment_entry.set_gain_or_loss(account_details=account_details) |
Rohit Waghchaure | a2408a6 | 2019-06-24 01:52:48 +0530 | [diff] [blame] | 651 | |
Devin Slauenwhite | 98c39c4 | 2023-01-01 22:25:12 -0500 | [diff] [blame] | 652 | payment_entry.flags.ignore_validate_update_after_submit = True |
| 653 | payment_entry.setup_party_account_field() |
| 654 | payment_entry.set_missing_values() |
ruthra kumar | ed14d1c | 2023-04-22 17:24:35 +0530 | [diff] [blame] | 655 | if not skip_ref_details_update_for_pe: |
| 656 | payment_entry.set_missing_ref_details() |
Devin Slauenwhite | 98c39c4 | 2023-01-01 22:25:12 -0500 | [diff] [blame] | 657 | payment_entry.set_amounts() |
| 658 | |
Rohit Waghchaure | a2408a6 | 2019-06-24 01:52:48 +0530 | [diff] [blame] | 659 | if not do_not_save: |
| 660 | payment_entry.save(ignore_permissions=True) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 661 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 662 | |
Nabin Hait | 297d74a | 2016-11-23 15:58:51 +0530 | [diff] [blame] | 663 | def unlink_ref_doc_from_payment_entries(ref_doc): |
| 664 | remove_ref_doc_link_from_jv(ref_doc.doctype, ref_doc.name) |
| 665 | remove_ref_doc_link_from_pe(ref_doc.doctype, ref_doc.name) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 666 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 667 | frappe.db.sql( |
| 668 | """update `tabGL Entry` |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 669 | set against_voucher_type=null, against_voucher=null, |
| 670 | modified=%s, modified_by=%s |
| 671 | where against_voucher_type=%s and against_voucher=%s |
| 672 | and voucher_no != ifnull(against_voucher, '')""", |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 673 | (now(), frappe.session.user, ref_doc.doctype, ref_doc.name), |
| 674 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 675 | |
ruthra kumar | 537d953 | 2022-10-10 10:17:19 +0530 | [diff] [blame] | 676 | ple = qb.DocType("Payment Ledger Entry") |
| 677 | |
| 678 | qb.update(ple).set(ple.against_voucher_type, ple.voucher_type).set( |
| 679 | ple.against_voucher_no, ple.voucher_no |
| 680 | ).set(ple.modified, now()).set(ple.modified_by, frappe.session.user).where( |
| 681 | (ple.against_voucher_type == ref_doc.doctype) |
| 682 | & (ple.against_voucher_no == ref_doc.name) |
| 683 | & (ple.delinked == 0) |
| 684 | ).run() |
| 685 | |
Nabin Hait | 297d74a | 2016-11-23 15:58:51 +0530 | [diff] [blame] | 686 | if ref_doc.doctype in ("Sales Invoice", "Purchase Invoice"): |
| 687 | ref_doc.set("advances", []) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 688 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 689 | frappe.db.sql( |
| 690 | """delete from `tab{0} Advance` where parent = %s""".format(ref_doc.doctype), ref_doc.name |
| 691 | ) |
| 692 | |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 693 | |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 694 | def remove_ref_doc_link_from_jv(ref_type, ref_no): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 695 | linked_jv = frappe.db.sql_list( |
| 696 | """select parent from `tabJournal Entry Account` |
| 697 | where reference_type=%s and reference_name=%s and docstatus < 2""", |
| 698 | (ref_type, ref_no), |
| 699 | ) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 700 | |
| 701 | if linked_jv: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 702 | frappe.db.sql( |
| 703 | """update `tabJournal Entry Account` |
Rushabh Mehta | 1828c12 | 2015-08-10 17:04:07 +0530 | [diff] [blame] | 704 | set reference_type=null, reference_name = null, |
Nabin Hait | dc15b4f | 2014-01-20 16:48:49 +0530 | [diff] [blame] | 705 | modified=%s, modified_by=%s |
Rushabh Mehta | 1828c12 | 2015-08-10 17:04:07 +0530 | [diff] [blame] | 706 | where reference_type=%s and reference_name=%s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 707 | and docstatus < 2""", |
| 708 | (now(), frappe.session.user, ref_type, ref_no), |
| 709 | ) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 710 | |
Suraj Shetty | 48e9bc3 | 2020-01-29 15:06:18 +0530 | [diff] [blame] | 711 | frappe.msgprint(_("Journal Entries {0} are un-linked").format("\n".join(linked_jv))) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 712 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 713 | |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 714 | def remove_ref_doc_link_from_pe(ref_type, ref_no): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 715 | linked_pe = frappe.db.sql_list( |
| 716 | """select parent from `tabPayment Entry Reference` |
| 717 | where reference_doctype=%s and reference_name=%s and docstatus < 2""", |
| 718 | (ref_type, ref_no), |
| 719 | ) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 720 | |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 721 | if linked_pe: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 722 | frappe.db.sql( |
| 723 | """update `tabPayment Entry Reference` |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 724 | set allocated_amount=0, modified=%s, modified_by=%s |
| 725 | where reference_doctype=%s and reference_name=%s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 726 | and docstatus < 2""", |
| 727 | (now(), frappe.session.user, ref_type, ref_no), |
| 728 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 729 | |
Nabin Hait | e0cc87d | 2016-07-21 18:30:03 +0530 | [diff] [blame] | 730 | for pe in linked_pe: |
Deepesh Garg | c5276f3 | 2021-08-01 17:48:50 +0530 | [diff] [blame] | 731 | try: |
Deepesh Garg | 1894dc8 | 2023-06-23 21:53:34 +0530 | [diff] [blame] | 732 | pe_doc = frappe.get_doc("Payment Entry", pe) |
Deepesh Garg | 7141860 | 2021-08-10 22:21:28 +0530 | [diff] [blame] | 733 | pe_doc.set_amounts() |
Deepesh Garg | da6bc1a | 2023-06-23 20:57:51 +0530 | [diff] [blame] | 734 | pe_doc.make_advance_gl_entries(against_voucher_type=ref_type, against_voucher=ref_no, cancel=1) |
Deepesh Garg | b516239 | 2021-08-10 14:52:24 +0530 | [diff] [blame] | 735 | pe_doc.clear_unallocated_reference_document_rows() |
| 736 | pe_doc.validate_payment_type_with_outstanding() |
Deepesh Garg | c5276f3 | 2021-08-01 17:48:50 +0530 | [diff] [blame] | 737 | except Exception as e: |
| 738 | msg = _("There were issues unlinking payment entry {0}.").format(pe_doc.name) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 739 | msg += "<br>" |
Deepesh Garg | 188bba8 | 2021-08-10 14:04:31 +0530 | [diff] [blame] | 740 | msg += _("Please cancel payment entry manually first") |
Deepesh Garg | 7141860 | 2021-08-10 22:21:28 +0530 | [diff] [blame] | 741 | frappe.throw(msg, exc=PaymentEntryUnlinkError, title=_("Payment Unlink Error")) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 742 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 743 | frappe.db.sql( |
| 744 | """update `tabPayment Entry` set total_allocated_amount=%s, |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 745 | base_total_allocated_amount=%s, unallocated_amount=%s, modified=%s, modified_by=%s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 746 | where name=%s""", |
| 747 | ( |
| 748 | pe_doc.total_allocated_amount, |
| 749 | pe_doc.base_total_allocated_amount, |
| 750 | pe_doc.unallocated_amount, |
| 751 | now(), |
| 752 | frappe.session.user, |
| 753 | pe, |
| 754 | ), |
| 755 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 756 | |
Suraj Shetty | 48e9bc3 | 2020-01-29 15:06:18 +0530 | [diff] [blame] | 757 | frappe.msgprint(_("Payment Entries {0} are un-linked").format("\n".join(linked_pe))) |
Nabin Hait | 0fc2454 | 2013-03-25 11:06:00 +0530 | [diff] [blame] | 758 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 759 | |
Rushabh Mehta | 793ba6b | 2014-02-14 15:47:51 +0530 | [diff] [blame] | 760 | @frappe.whitelist() |
Rohit Waghchaure | 2a14f25 | 2021-07-30 12:36:35 +0530 | [diff] [blame] | 761 | def get_company_default(company, fieldname, ignore_validation=False): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 762 | value = frappe.get_cached_value("Company", company, fieldname) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 763 | |
Rohit Waghchaure | 2a14f25 | 2021-07-30 12:36:35 +0530 | [diff] [blame] | 764 | if not ignore_validation and not value: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 765 | throw( |
| 766 | _("Please set default {0} in Company {1}").format( |
| 767 | frappe.get_meta("Company").get_label(fieldname), company |
| 768 | ) |
| 769 | ) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 770 | |
Nabin Hait | 0fc2454 | 2013-03-25 11:06:00 +0530 | [diff] [blame] | 771 | return value |
Nabin Hait | 8b509f5 | 2013-05-28 16:52:30 +0530 | [diff] [blame] | 772 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 773 | |
Nabin Hait | 8b509f5 | 2013-05-28 16:52:30 +0530 | [diff] [blame] | 774 | def fix_total_debit_credit(): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 775 | vouchers = frappe.db.sql( |
| 776 | """select voucher_type, voucher_no, |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 777 | sum(debit) - sum(credit) as diff |
| 778 | from `tabGL Entry` |
Nabin Hait | 8b509f5 | 2013-05-28 16:52:30 +0530 | [diff] [blame] | 779 | group by voucher_type, voucher_no |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 780 | having sum(debit) != sum(credit)""", |
| 781 | as_dict=1, |
| 782 | ) |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 783 | |
Nabin Hait | 8b509f5 | 2013-05-28 16:52:30 +0530 | [diff] [blame] | 784 | for d in vouchers: |
| 785 | if abs(d.diff) > 0: |
| 786 | dr_or_cr = d.voucher_type == "Sales Invoice" and "credit" or "debit" |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 787 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 788 | frappe.db.sql( |
| 789 | """update `tabGL Entry` set %s = %s + %s |
| 790 | where voucher_type = %s and voucher_no = %s and %s > 0 limit 1""" |
| 791 | % (dr_or_cr, dr_or_cr, "%s", "%s", "%s", dr_or_cr), |
| 792 | (d.diff, d.voucher_type, d.voucher_no), |
| 793 | ) |
| 794 | |
Anand Doshi | cd71e1d | 2014-04-08 13:53:35 +0530 | [diff] [blame] | 795 | |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 796 | def get_currency_precision(): |
Nabin Hait | 9b20e07 | 2017-04-25 12:10:24 +0530 | [diff] [blame] | 797 | precision = cint(frappe.db.get_default("currency_precision")) |
| 798 | if not precision: |
| 799 | number_format = frappe.db.get_default("number_format") or "#,###.##" |
| 800 | precision = get_number_format_info(number_format)[2] |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 801 | |
Nabin Hait | 9b20e07 | 2017-04-25 12:10:24 +0530 | [diff] [blame] | 802 | return precision |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 803 | |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 804 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 805 | def get_stock_rbnb_difference(posting_date, company): |
| 806 | stock_items = frappe.db.sql_list( |
| 807 | """select distinct item_code |
| 808 | from `tabStock Ledger Entry` where company=%s""", |
| 809 | company, |
| 810 | ) |
| 811 | |
| 812 | pr_valuation_amount = frappe.db.sql( |
| 813 | """ |
Anand Doshi | 602e825 | 2015-11-16 19:05:46 +0530 | [diff] [blame] | 814 | select sum(pr_item.valuation_rate * pr_item.qty * pr_item.conversion_factor) |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 815 | from `tabPurchase Receipt Item` pr_item, `tabPurchase Receipt` pr |
Suraj Shetty | 084b0b3 | 2018-05-26 09:12:59 +0530 | [diff] [blame] | 816 | where pr.name = pr_item.parent and pr.docstatus=1 and pr.company=%s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 817 | and pr.posting_date <= %s and pr_item.item_code in (%s)""" |
| 818 | % ("%s", "%s", ", ".join(["%s"] * len(stock_items))), |
| 819 | tuple([company, posting_date] + stock_items), |
| 820 | )[0][0] |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 821 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 822 | pi_valuation_amount = frappe.db.sql( |
| 823 | """ |
Anand Doshi | 602e825 | 2015-11-16 19:05:46 +0530 | [diff] [blame] | 824 | select sum(pi_item.valuation_rate * pi_item.qty * pi_item.conversion_factor) |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 825 | from `tabPurchase Invoice Item` pi_item, `tabPurchase Invoice` pi |
Suraj Shetty | 084b0b3 | 2018-05-26 09:12:59 +0530 | [diff] [blame] | 826 | where pi.name = pi_item.parent and pi.docstatus=1 and pi.company=%s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 827 | and pi.posting_date <= %s and pi_item.item_code in (%s)""" |
| 828 | % ("%s", "%s", ", ".join(["%s"] * len(stock_items))), |
| 829 | tuple([company, posting_date] + stock_items), |
| 830 | )[0][0] |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 831 | |
| 832 | # Balance should be |
| 833 | stock_rbnb = flt(pr_valuation_amount, 2) - flt(pi_valuation_amount, 2) |
| 834 | |
| 835 | # Balance as per system |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 836 | stock_rbnb_account = "Stock Received But Not Billed - " + frappe.get_cached_value( |
| 837 | "Company", company, "abbr" |
| 838 | ) |
Nabin Hait | c0e3b1a | 2015-09-04 13:26:23 +0530 | [diff] [blame] | 839 | sys_bal = get_balance_on(stock_rbnb_account, posting_date, in_account_currency=False) |
Nabin Hait | 9a0c46f | 2014-08-07 15:10:05 +0530 | [diff] [blame] | 840 | |
| 841 | # Amount should be credited |
| 842 | return flt(stock_rbnb) + flt(sys_bal) |
Ankit Javalkar | 8e7ca41 | 2014-09-12 15:18:53 +0530 | [diff] [blame] | 843 | |
tunde | c4b0d17 | 2017-09-26 00:48:30 +0100 | [diff] [blame] | 844 | |
tundebabzy | ad08d4c | 2018-05-16 07:01:41 +0100 | [diff] [blame] | 845 | def get_held_invoices(party_type, party): |
| 846 | """ |
| 847 | Returns a list of names Purchase Invoices for the given party that are on hold |
| 848 | """ |
| 849 | held_invoices = None |
| 850 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 851 | if party_type == "Supplier": |
tundebabzy | ad08d4c | 2018-05-16 07:01:41 +0100 | [diff] [blame] | 852 | held_invoices = frappe.db.sql( |
mergify[bot] | 872a23c | 2023-07-10 20:34:54 +0530 | [diff] [blame] | 853 | "select name from `tabPurchase Invoice` where on_hold = 1 and release_date IS NOT NULL and release_date > CURDATE()", |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 854 | as_dict=1, |
tundebabzy | ad08d4c | 2018-05-16 07:01:41 +0100 | [diff] [blame] | 855 | ) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 856 | held_invoices = set(d["name"] for d in held_invoices) |
tundebabzy | ad08d4c | 2018-05-16 07:01:41 +0100 | [diff] [blame] | 857 | |
| 858 | return held_invoices |
| 859 | |
| 860 | |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 861 | def get_outstanding_invoices( |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 862 | party_type, |
| 863 | party, |
| 864 | account, |
| 865 | common_filter=None, |
| 866 | posting_date=None, |
| 867 | min_outstanding=None, |
| 868 | max_outstanding=None, |
ruthra kumar | 6d9d730 | 2022-12-14 16:05:15 +0530 | [diff] [blame] | 869 | accounting_dimensions=None, |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 870 | ): |
| 871 | |
| 872 | ple = qb.DocType("Payment Ledger Entry") |
Anand Doshi | d40d1e9 | 2015-11-12 17:05:29 +0530 | [diff] [blame] | 873 | outstanding_invoices = [] |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 874 | precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 |
Anand Doshi | d40d1e9 | 2015-11-12 17:05:29 +0530 | [diff] [blame] | 875 | |
Nabin Hait | 9db9edc | 2019-11-19 18:44:32 +0530 | [diff] [blame] | 876 | if account: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 877 | root_type, account_type = frappe.get_cached_value( |
| 878 | "Account", account, ["root_type", "account_type"] |
| 879 | ) |
Nabin Hait | 9db9edc | 2019-11-19 18:44:32 +0530 | [diff] [blame] | 880 | party_account_type = "Receivable" if root_type == "Asset" else "Payable" |
Saqib | 9291df4 | 2020-01-24 16:22:49 +0530 | [diff] [blame] | 881 | party_account_type = account_type or party_account_type |
Nabin Hait | 9db9edc | 2019-11-19 18:44:32 +0530 | [diff] [blame] | 882 | else: |
| 883 | party_account_type = erpnext.get_party_account_type(party_type) |
| 884 | |
tundebabzy | ad08d4c | 2018-05-16 07:01:41 +0100 | [diff] [blame] | 885 | held_invoices = get_held_invoices(party_type, party) |
| 886 | |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 887 | common_filter = common_filter or [] |
| 888 | common_filter.append(ple.account_type == party_account_type) |
| 889 | common_filter.append(ple.account == account) |
| 890 | common_filter.append(ple.party_type == party_type) |
| 891 | common_filter.append(ple.party == party) |
Ankit Javalkar | 8e7ca41 | 2014-09-12 15:18:53 +0530 | [diff] [blame] | 892 | |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 893 | ple_query = QueryPaymentLedger() |
| 894 | invoice_list = ple_query.get_voucher_outstandings( |
| 895 | common_filter=common_filter, |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 896 | posting_date=posting_date, |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 897 | min_outstanding=min_outstanding, |
| 898 | max_outstanding=max_outstanding, |
| 899 | get_invoices=True, |
ruthra kumar | 6d9d730 | 2022-12-14 16:05:15 +0530 | [diff] [blame] | 900 | accounting_dimensions=accounting_dimensions or [], |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 901 | ) |
tunde | c4b0d17 | 2017-09-26 00:48:30 +0100 | [diff] [blame] | 902 | |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 903 | for d in invoice_list: |
ruthra kumar | 2198553 | 2022-06-30 15:14:38 +0530 | [diff] [blame] | 904 | payment_amount = d.invoice_amount_in_account_currency - d.outstanding_in_account_currency |
| 905 | outstanding_amount = d.outstanding_in_account_currency |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 906 | if outstanding_amount > 0.5 / (10**precision): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 907 | if ( |
ruthra kumar | 8c87674 | 2022-06-14 17:46:04 +0530 | [diff] [blame] | 908 | min_outstanding |
| 909 | and max_outstanding |
| 910 | and not (outstanding_amount >= min_outstanding and outstanding_amount <= max_outstanding) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 911 | ): |
Rohit Waghchaure | 0f065d5 | 2019-05-02 00:06:04 +0530 | [diff] [blame] | 912 | continue |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 913 | |
Rohit Waghchaure | 0f065d5 | 2019-05-02 00:06:04 +0530 | [diff] [blame] | 914 | if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 915 | outstanding_invoices.append( |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 916 | frappe._dict( |
| 917 | { |
| 918 | "voucher_no": d.voucher_no, |
| 919 | "voucher_type": d.voucher_type, |
| 920 | "posting_date": d.posting_date, |
ruthra kumar | 2198553 | 2022-06-30 15:14:38 +0530 | [diff] [blame] | 921 | "invoice_amount": flt(d.invoice_amount_in_account_currency), |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 922 | "payment_amount": payment_amount, |
| 923 | "outstanding_amount": outstanding_amount, |
| 924 | "due_date": d.due_date, |
| 925 | "currency": d.currency, |
Gursheen Anand | 4ee1637 | 2023-06-08 13:15:23 +0530 | [diff] [blame] | 926 | "account": d.account, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 927 | } |
| 928 | ) |
Nabin Hait | ac18498 | 2019-02-04 21:13:43 +0530 | [diff] [blame] | 929 | ) |
Rushabh Mehta | e9d9b8e | 2016-12-15 11:27:35 +0530 | [diff] [blame] | 930 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 931 | outstanding_invoices = sorted( |
| 932 | outstanding_invoices, key=lambda k: k["due_date"] or getdate(nowdate()) |
| 933 | ) |
Anand Doshi | d40d1e9 | 2015-11-12 17:05:29 +0530 | [diff] [blame] | 934 | return outstanding_invoices |
Saurabh | 3a26829 | 2016-02-22 15:18:40 +0530 | [diff] [blame] | 935 | |
| 936 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 937 | def get_account_name( |
| 938 | account_type=None, root_type=None, is_group=None, account_currency=None, company=None |
| 939 | ): |
Saurabh | 3a26829 | 2016-02-22 15:18:40 +0530 | [diff] [blame] | 940 | """return account based on matching conditions""" |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 941 | return frappe.db.get_value( |
| 942 | "Account", |
| 943 | { |
| 944 | "account_type": account_type or "", |
| 945 | "root_type": root_type or "", |
| 946 | "is_group": is_group or 0, |
| 947 | "account_currency": account_currency or frappe.defaults.get_defaults().currency, |
| 948 | "company": company or frappe.defaults.get_defaults().company, |
| 949 | }, |
| 950 | "name", |
| 951 | ) |
| 952 | |
Saurabh | a9ba734 | 2016-06-21 12:33:12 +0530 | [diff] [blame] | 953 | |
| 954 | @frappe.whitelist() |
| 955 | def get_companies(): |
| 956 | """get a list of companies based on permission""" |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 957 | return [d.name for d in frappe.get_list("Company", fields=["name"], order_by="name")] |
| 958 | |
Saurabh | a9ba734 | 2016-06-21 12:33:12 +0530 | [diff] [blame] | 959 | |
| 960 | @frappe.whitelist() |
Rushabh Mehta | 4313326 | 2017-11-10 18:52:21 +0530 | [diff] [blame] | 961 | def get_children(doctype, parent, company, is_root=False): |
Nabin Hait | af98f5d | 2018-04-02 10:14:32 +0530 | [diff] [blame] | 962 | from erpnext.accounts.report.financial_statements import sort_accounts |
Rushabh Mehta | d50da78 | 2017-07-28 11:39:01 +0530 | [diff] [blame] | 963 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 964 | parent_fieldname = "parent_" + doctype.lower().replace(" ", "_") |
| 965 | fields = ["name as value", "is_group as expandable"] |
| 966 | filters = [["docstatus", "<", 2]] |
Suraj Shetty | fbb6b3d | 2018-05-16 13:53:31 +0530 | [diff] [blame] | 967 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 968 | filters.append(['ifnull(`{0}`,"")'.format(parent_fieldname), "=", "" if is_root else parent]) |
Suraj Shetty | 084b0b3 | 2018-05-26 09:12:59 +0530 | [diff] [blame] | 969 | |
Rushabh Mehta | 4313326 | 2017-11-10 18:52:21 +0530 | [diff] [blame] | 970 | if is_root: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 971 | fields += ["root_type", "report_type", "account_currency"] if doctype == "Account" else [] |
| 972 | filters.append(["company", "=", company]) |
Suraj Shetty | 084b0b3 | 2018-05-26 09:12:59 +0530 | [diff] [blame] | 973 | |
Saurabh | a9ba734 | 2016-06-21 12:33:12 +0530 | [diff] [blame] | 974 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 975 | fields += ["root_type", "account_currency"] if doctype == "Account" else [] |
| 976 | fields += [parent_fieldname + " as parent"] |
Suraj Shetty | 084b0b3 | 2018-05-26 09:12:59 +0530 | [diff] [blame] | 977 | |
| 978 | acc = frappe.get_list(doctype, fields=fields, filters=filters) |
Saurabh | a9ba734 | 2016-06-21 12:33:12 +0530 | [diff] [blame] | 979 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 980 | if doctype == "Account": |
Nabin Hait | af98f5d | 2018-04-02 10:14:32 +0530 | [diff] [blame] | 981 | sort_accounts(acc, is_root, key="value") |
Saurabh | a9ba734 | 2016-06-21 12:33:12 +0530 | [diff] [blame] | 982 | |
| 983 | return acc |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 984 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 985 | |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 986 | @frappe.whitelist() |
| 987 | def get_account_balances(accounts, company): |
| 988 | |
Ankush Menat | 8fe5feb | 2021-11-04 19:48:32 +0530 | [diff] [blame] | 989 | if isinstance(accounts, str): |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 990 | accounts = loads(accounts) |
| 991 | |
| 992 | if not accounts: |
| 993 | return [] |
| 994 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 995 | company_currency = frappe.get_cached_value("Company", company, "default_currency") |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 996 | |
| 997 | for account in accounts: |
| 998 | account["company_currency"] = company_currency |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 999 | account["balance"] = flt( |
| 1000 | get_balance_on(account["value"], in_account_currency=False, company=company) |
| 1001 | ) |
Saqib | 9051735 | 2021-10-04 11:44:46 +0530 | [diff] [blame] | 1002 | if account["account_currency"] and account["account_currency"] != company_currency: |
| 1003 | account["balance_in_account_currency"] = flt(get_balance_on(account["value"], company=company)) |
| 1004 | |
| 1005 | return accounts |
| 1006 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1007 | |
Mangesh-Khairnar | 97ab96c | 2020-09-22 12:58:32 +0530 | [diff] [blame] | 1008 | def create_payment_gateway_account(gateway, payment_channel="Email"): |
Deepesh Garg | a72589c | 2021-05-29 23:54:51 +0530 | [diff] [blame] | 1009 | from erpnext.setup.setup_wizard.operations.install_fixtures import create_bank_account |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1010 | |
Daizy Modi | fdfe5cb | 2022-11-17 19:14:10 +0530 | [diff] [blame] | 1011 | company = frappe.get_cached_value("Global Defaults", "Global Defaults", "default_company") |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1012 | if not company: |
| 1013 | return |
| 1014 | |
| 1015 | # NOTE: we translate Payment Gateway account name because that is going to be used by the end user |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1016 | bank_account = frappe.db.get_value( |
| 1017 | "Account", |
| 1018 | {"account_name": _(gateway), "company": company}, |
| 1019 | ["name", "account_currency"], |
| 1020 | as_dict=1, |
| 1021 | ) |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1022 | |
| 1023 | if not bank_account: |
| 1024 | # check for untranslated one |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1025 | bank_account = frappe.db.get_value( |
| 1026 | "Account", |
| 1027 | {"account_name": gateway, "company": company}, |
| 1028 | ["name", "account_currency"], |
| 1029 | as_dict=1, |
| 1030 | ) |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1031 | |
| 1032 | if not bank_account: |
| 1033 | # try creating one |
| 1034 | bank_account = create_bank_account({"company_name": company, "bank_account": _(gateway)}) |
| 1035 | |
| 1036 | if not bank_account: |
| 1037 | frappe.msgprint(_("Payment Gateway Account not created, please create one manually.")) |
| 1038 | return |
| 1039 | |
| 1040 | # if payment gateway account exists, return |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1041 | if frappe.db.exists( |
| 1042 | "Payment Gateway Account", |
| 1043 | {"payment_gateway": gateway, "currency": bank_account.account_currency}, |
| 1044 | ): |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1045 | return |
| 1046 | |
| 1047 | try: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1048 | frappe.get_doc( |
| 1049 | { |
| 1050 | "doctype": "Payment Gateway Account", |
| 1051 | "is_default": 1, |
| 1052 | "payment_gateway": gateway, |
| 1053 | "payment_account": bank_account.name, |
| 1054 | "currency": bank_account.account_currency, |
| 1055 | "payment_channel": payment_channel, |
| 1056 | } |
| 1057 | ).insert(ignore_permissions=True, ignore_if_duplicate=True) |
Saurabh | b835fef | 2016-09-09 11:19:22 +0530 | [diff] [blame] | 1058 | |
| 1059 | except frappe.DuplicateEntryError: |
| 1060 | # already exists, due to a reinstall? |
cclauss | 6848708 | 2017-07-27 07:08:35 +0200 | [diff] [blame] | 1061 | pass |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1062 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1063 | |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1064 | @frappe.whitelist() |
Deepesh Garg | 817cbc4 | 2020-06-15 12:07:04 +0530 | [diff] [blame] | 1065 | def update_cost_center(docname, cost_center_name, cost_center_number, company, merge): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1066 | """ |
| 1067 | Renames the document by adding the number as a prefix to the current name and updates |
| 1068 | all transaction where it was present. |
| 1069 | """ |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1070 | validate_field_number("Cost Center", docname, cost_center_number, company, "cost_center_number") |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1071 | |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1072 | if cost_center_number: |
| 1073 | frappe.db.set_value("Cost Center", docname, "cost_center_number", cost_center_number.strip()) |
| 1074 | else: |
| 1075 | frappe.db.set_value("Cost Center", docname, "cost_center_number", "") |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1076 | |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1077 | frappe.db.set_value("Cost Center", docname, "cost_center_name", cost_center_name.strip()) |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1078 | |
Nabin Hait | af21a11 | 2022-09-15 12:09:18 +0530 | [diff] [blame] | 1079 | new_name = get_autoname_with_number(cost_center_number, cost_center_name, company) |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1080 | if docname != new_name: |
Deepesh Garg | 817cbc4 | 2020-06-15 12:07:04 +0530 | [diff] [blame] | 1081 | frappe.rename_doc("Cost Center", docname, new_name, force=1, merge=merge) |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1082 | return new_name |
| 1083 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1084 | |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1085 | def validate_field_number(doctype_name, docname, number_value, company, field_name): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1086 | """Validate if the number entered isn't already assigned to some other document.""" |
Zlash65 | 1aeca1d | 2018-07-06 17:15:33 +0530 | [diff] [blame] | 1087 | if number_value: |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1088 | filters = {field_name: number_value, "name": ["!=", docname]} |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1089 | if company: |
Saqib | a877987 | 2020-04-30 11:28:43 +0530 | [diff] [blame] | 1090 | filters["company"] = company |
| 1091 | |
| 1092 | doctype_with_same_number = frappe.db.get_value(doctype_name, filters) |
| 1093 | |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1094 | if doctype_with_same_number: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1095 | frappe.throw( |
| 1096 | _("{0} Number {1} is already used in {2} {3}").format( |
| 1097 | doctype_name, number_value, doctype_name.lower(), doctype_with_same_number |
| 1098 | ) |
| 1099 | ) |
| 1100 | |
Zarrar | 4417590 | 2018-06-08 16:35:21 +0530 | [diff] [blame] | 1101 | |
Nabin Hait | af21a11 | 2022-09-15 12:09:18 +0530 | [diff] [blame] | 1102 | def get_autoname_with_number(number_value, doc_title, company): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1103 | """append title with prefix as number and suffix as company's abbreviation separated by '-'""" |
Nabin Hait | af21a11 | 2022-09-15 12:09:18 +0530 | [diff] [blame] | 1104 | company_abbr = frappe.get_cached_value("Company", company, "abbr") |
| 1105 | parts = [doc_title.strip(), company_abbr] |
| 1106 | |
Zlash65 | 1aeca1d | 2018-07-06 17:15:33 +0530 | [diff] [blame] | 1107 | if cstr(number_value).strip(): |
| 1108 | parts.insert(0, cstr(number_value).strip()) |
Nabin Hait | af21a11 | 2022-09-15 12:09:18 +0530 | [diff] [blame] | 1109 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1110 | return " - ".join(parts) |
| 1111 | |
Zarrar | 254ce64 | 2018-06-28 14:15:34 +0530 | [diff] [blame] | 1112 | |
Deepesh Garg | 6270607 | 2023-07-16 12:58:42 +0530 | [diff] [blame] | 1113 | def parse_naming_series_variable(doc, variable): |
| 1114 | if variable == "FY": |
| 1115 | return get_fiscal_year(date=doc.get("posting_date"), company=doc.get("company"))[0] |
| 1116 | |
| 1117 | |
Zarrar | 254ce64 | 2018-06-28 14:15:34 +0530 | [diff] [blame] | 1118 | @frappe.whitelist() |
| 1119 | def get_coa(doctype, parent, is_root, chart=None): |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 1120 | from erpnext.accounts.doctype.account.chart_of_accounts.chart_of_accounts import ( |
| 1121 | build_tree_from_json, |
| 1122 | ) |
Zarrar | 254ce64 | 2018-06-28 14:15:34 +0530 | [diff] [blame] | 1123 | |
| 1124 | # add chart to flags to retrieve when called from expand all function |
| 1125 | chart = chart if chart else frappe.flags.chart |
| 1126 | frappe.flags.chart = chart |
| 1127 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1128 | parent = None if parent == _("All Accounts") else parent |
| 1129 | accounts = build_tree_from_json(chart) # returns alist of dict in a tree render-able form |
Zarrar | 254ce64 | 2018-06-28 14:15:34 +0530 | [diff] [blame] | 1130 | |
| 1131 | # filter out to show data for the selected node only |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1132 | accounts = [d for d in accounts if d["parent_account"] == parent] |
Zarrar | 254ce64 | 2018-06-28 14:15:34 +0530 | [diff] [blame] | 1133 | |
| 1134 | return accounts |
Sanjay Kumar | 1b49f3a | 2018-09-06 13:09:35 +0400 | [diff] [blame] | 1135 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1136 | |
| 1137 | def update_gl_entries_after( |
| 1138 | posting_date, |
| 1139 | posting_time, |
| 1140 | for_warehouses=None, |
| 1141 | for_items=None, |
| 1142 | warehouse_account=None, |
| 1143 | company=None, |
| 1144 | ): |
| 1145 | stock_vouchers = get_future_stock_vouchers( |
| 1146 | posting_date, posting_time, for_warehouses, for_items, company |
| 1147 | ) |
Nabin Hait | 9b178bc | 2021-02-16 14:57:00 +0530 | [diff] [blame] | 1148 | repost_gle_for_stock_vouchers(stock_vouchers, posting_date, company, warehouse_account) |
| 1149 | |
| 1150 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1151 | def repost_gle_for_stock_vouchers( |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1152 | stock_vouchers: List[Tuple[str, str]], |
| 1153 | posting_date: str, |
| 1154 | company: Optional[str] = None, |
| 1155 | warehouse_account=None, |
| 1156 | repost_doc: Optional["RepostItemValuation"] = None, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1157 | ): |
Ankush Menat | 67c2632 | 2022-06-04 14:46:35 +0530 | [diff] [blame] | 1158 | |
| 1159 | from erpnext.accounts.general_ledger import toggle_debit_credit_if_negative |
| 1160 | |
Ankush Menat | 700e864 | 2022-04-19 13:24:29 +0530 | [diff] [blame] | 1161 | if not stock_vouchers: |
| 1162 | return |
| 1163 | |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1164 | if not warehouse_account: |
| 1165 | warehouse_account = get_warehouse_account_map(company) |
| 1166 | |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1167 | stock_vouchers = sort_stock_vouchers_by_posting_date(stock_vouchers) |
| 1168 | if repost_doc and repost_doc.gl_reposting_index: |
| 1169 | # Restore progress |
| 1170 | stock_vouchers = stock_vouchers[cint(repost_doc.gl_reposting_index) :] |
| 1171 | |
Rohit Waghchaure | bc8c9de | 2021-02-23 17:50:49 +0530 | [diff] [blame] | 1172 | precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit")) or 2 |
| 1173 | |
Ankush Menat | 5c6f22f | 2022-06-15 19:30:26 +0530 | [diff] [blame] | 1174 | for stock_vouchers_chunk in create_batch(stock_vouchers, GL_REPOSTING_CHUNK): |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1175 | gle = get_voucherwise_gl_entries(stock_vouchers_chunk, posting_date) |
| 1176 | |
| 1177 | for voucher_type, voucher_no in stock_vouchers_chunk: |
| 1178 | existing_gle = gle.get((voucher_type, voucher_no), []) |
| 1179 | voucher_obj = frappe.get_doc(voucher_type, voucher_no) |
| 1180 | # Some transactions post credit as negative debit, this is handled while posting GLE |
| 1181 | # but while comparing we need to make sure it's flipped so comparisons are accurate |
| 1182 | expected_gle = toggle_debit_credit_if_negative(voucher_obj.get_gl_entries(warehouse_account)) |
| 1183 | if expected_gle: |
| 1184 | if not existing_gle or not compare_existing_and_expected_gle( |
| 1185 | existing_gle, expected_gle, precision |
| 1186 | ): |
ruthra kumar | 9209ec5 | 2022-10-21 15:18:40 +0530 | [diff] [blame] | 1187 | _delete_accounting_ledger_entries(voucher_type, voucher_no) |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1188 | voucher_obj.make_gl_entries(gl_entries=expected_gle, from_repost=True) |
| 1189 | else: |
ruthra kumar | 9209ec5 | 2022-10-21 15:18:40 +0530 | [diff] [blame] | 1190 | _delete_accounting_ledger_entries(voucher_type, voucher_no) |
Ankush Menat | 86919d2 | 2022-06-15 21:19:09 +0530 | [diff] [blame] | 1191 | |
| 1192 | if not frappe.flags.in_test: |
| 1193 | frappe.db.commit() |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1194 | |
| 1195 | if repost_doc: |
| 1196 | repost_doc.db_set( |
| 1197 | "gl_reposting_index", |
Ankush Menat | 5c6f22f | 2022-06-15 19:30:26 +0530 | [diff] [blame] | 1198 | cint(repost_doc.gl_reposting_index) + len(stock_vouchers_chunk), |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1199 | ) |
| 1200 | |
| 1201 | |
ruthra kumar | 9209ec5 | 2022-10-21 15:18:40 +0530 | [diff] [blame] | 1202 | def _delete_pl_entries(voucher_type, voucher_no): |
ruthra kumar | 6599230 | 2022-10-04 16:17:56 +0530 | [diff] [blame] | 1203 | ple = qb.DocType("Payment Ledger Entry") |
| 1204 | qb.from_(ple).delete().where( |
| 1205 | (ple.voucher_type == voucher_type) & (ple.voucher_no == voucher_no) |
| 1206 | ).run() |
Ankush Menat | eb53a97 | 2022-06-04 18:19:44 +0530 | [diff] [blame] | 1207 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1208 | |
ruthra kumar | 9209ec5 | 2022-10-21 15:18:40 +0530 | [diff] [blame] | 1209 | def _delete_gl_entries(voucher_type, voucher_no): |
| 1210 | gle = qb.DocType("GL Entry") |
| 1211 | qb.from_(gle).delete().where( |
| 1212 | (gle.voucher_type == voucher_type) & (gle.voucher_no == voucher_no) |
| 1213 | ).run() |
| 1214 | |
| 1215 | |
| 1216 | def _delete_accounting_ledger_entries(voucher_type, voucher_no): |
| 1217 | """ |
| 1218 | Remove entries from both General and Payment Ledger for specified Voucher |
| 1219 | """ |
| 1220 | _delete_gl_entries(voucher_type, voucher_no) |
| 1221 | _delete_pl_entries(voucher_type, voucher_no) |
| 1222 | |
| 1223 | |
Ankush Menat | 700e864 | 2022-04-19 13:24:29 +0530 | [diff] [blame] | 1224 | def sort_stock_vouchers_by_posting_date( |
| 1225 | stock_vouchers: List[Tuple[str, str]] |
| 1226 | ) -> List[Tuple[str, str]]: |
| 1227 | sle = frappe.qb.DocType("Stock Ledger Entry") |
| 1228 | voucher_nos = [v[1] for v in stock_vouchers] |
| 1229 | |
| 1230 | sles = ( |
| 1231 | frappe.qb.from_(sle) |
| 1232 | .select(sle.voucher_type, sle.voucher_no, sle.posting_date, sle.posting_time, sle.creation) |
| 1233 | .where((sle.is_cancelled == 0) & (sle.voucher_no.isin(voucher_nos))) |
| 1234 | .groupby(sle.voucher_type, sle.voucher_no) |
Ankush Menat | 2535d5e | 2022-06-14 18:20:33 +0530 | [diff] [blame] | 1235 | .orderby(sle.posting_date) |
| 1236 | .orderby(sle.posting_time) |
| 1237 | .orderby(sle.creation) |
Ankush Menat | 700e864 | 2022-04-19 13:24:29 +0530 | [diff] [blame] | 1238 | ).run(as_dict=True) |
| 1239 | sorted_vouchers = [(sle.voucher_type, sle.voucher_no) for sle in sles] |
| 1240 | |
| 1241 | unknown_vouchers = set(stock_vouchers) - set(sorted_vouchers) |
| 1242 | if unknown_vouchers: |
| 1243 | sorted_vouchers.extend(unknown_vouchers) |
| 1244 | |
| 1245 | return sorted_vouchers |
| 1246 | |
| 1247 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1248 | def get_future_stock_vouchers( |
| 1249 | posting_date, posting_time, for_warehouses=None, for_items=None, company=None |
| 1250 | ): |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1251 | |
| 1252 | values = [] |
| 1253 | condition = "" |
| 1254 | if for_items: |
| 1255 | condition += " and item_code in ({})".format(", ".join(["%s"] * len(for_items))) |
| 1256 | values += for_items |
| 1257 | |
| 1258 | if for_warehouses: |
| 1259 | condition += " and warehouse in ({})".format(", ".join(["%s"] * len(for_warehouses))) |
| 1260 | values += for_warehouses |
| 1261 | |
rohitwaghchaure | d60ff83 | 2021-02-16 09:12:27 +0530 | [diff] [blame] | 1262 | if company: |
Nabin Hait | 9b178bc | 2021-02-16 14:57:00 +0530 | [diff] [blame] | 1263 | condition += " and company = %s" |
rohitwaghchaure | d60ff83 | 2021-02-16 09:12:27 +0530 | [diff] [blame] | 1264 | values.append(company) |
| 1265 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1266 | future_stock_vouchers = frappe.db.sql( |
| 1267 | """select distinct sle.voucher_type, sle.voucher_no |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1268 | from `tabStock Ledger Entry` sle |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1269 | where |
| 1270 | timestamp(sle.posting_date, sle.posting_time) >= timestamp(%s, %s) |
| 1271 | and is_cancelled = 0 |
| 1272 | {condition} |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1273 | order by timestamp(sle.posting_date, sle.posting_time) asc, creation asc for update""".format( |
| 1274 | condition=condition |
| 1275 | ), |
| 1276 | tuple([posting_date, posting_time] + values), |
| 1277 | as_dict=True, |
| 1278 | ) |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1279 | |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1280 | return [(d.voucher_type, d.voucher_no) for d in future_stock_vouchers] |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1281 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1282 | |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1283 | def get_voucherwise_gl_entries(future_stock_vouchers, posting_date): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1284 | """Get voucherwise list of GL entries. |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1285 | |
| 1286 | Only fetches GLE fields required for comparing with new GLE. |
| 1287 | Check compare_existing_and_expected_gle function below. |
rohitwaghchaure | 058d983 | 2021-09-07 12:14:40 +0530 | [diff] [blame] | 1288 | |
| 1289 | returns: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1290 | Dict[Tuple[voucher_type, voucher_no], List[GL Entries]] |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1291 | """ |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1292 | gl_entries = {} |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1293 | if not future_stock_vouchers: |
| 1294 | return gl_entries |
| 1295 | |
| 1296 | voucher_nos = [d[1] for d in future_stock_vouchers] |
| 1297 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1298 | gles = frappe.db.sql( |
| 1299 | """ |
rohitwaghchaure | 058d983 | 2021-09-07 12:14:40 +0530 | [diff] [blame] | 1300 | select name, account, credit, debit, cost_center, project, voucher_type, voucher_no |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1301 | from `tabGL Entry` |
| 1302 | where |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1303 | posting_date >= %s and voucher_no in (%s)""" |
| 1304 | % ("%s", ", ".join(["%s"] * len(voucher_nos))), |
| 1305 | tuple([posting_date] + voucher_nos), |
| 1306 | as_dict=1, |
| 1307 | ) |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1308 | |
| 1309 | for d in gles: |
| 1310 | gl_entries.setdefault((d.voucher_type, d.voucher_no), []).append(d) |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1311 | |
Prssanna Desai | 82ddef5 | 2020-06-18 18:18:41 +0530 | [diff] [blame] | 1312 | return gl_entries |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1313 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1314 | |
Rohit Waghchaure | bc8c9de | 2021-02-23 17:50:49 +0530 | [diff] [blame] | 1315 | def compare_existing_and_expected_gle(existing_gle, expected_gle, precision): |
Ankush | 9152715 | 2021-08-11 11:17:50 +0530 | [diff] [blame] | 1316 | if len(existing_gle) != len(expected_gle): |
| 1317 | return False |
| 1318 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1319 | matched = True |
| 1320 | for entry in expected_gle: |
| 1321 | account_existed = False |
| 1322 | for e in existing_gle: |
| 1323 | if entry.account == e.account: |
| 1324 | account_existed = True |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1325 | if ( |
| 1326 | entry.account == e.account |
| 1327 | and (not entry.cost_center or not e.cost_center or entry.cost_center == e.cost_center) |
| 1328 | and ( |
| 1329 | flt(entry.debit, precision) != flt(e.debit, precision) |
| 1330 | or flt(entry.credit, precision) != flt(e.credit, precision) |
| 1331 | ) |
| 1332 | ): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1333 | matched = False |
| 1334 | break |
| 1335 | if not account_existed: |
| 1336 | matched = False |
| 1337 | break |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1338 | return matched |
| 1339 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1340 | |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1341 | def get_stock_accounts(company, voucher_type=None, voucher_no=None): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1342 | stock_accounts = [ |
| 1343 | d.name |
| 1344 | for d in frappe.db.get_all( |
| 1345 | "Account", {"account_type": "Stock", "company": company, "is_group": 0} |
| 1346 | ) |
| 1347 | ] |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1348 | if voucher_type and voucher_no: |
| 1349 | if voucher_type == "Journal Entry": |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1350 | stock_accounts = [ |
| 1351 | d.account |
| 1352 | for d in frappe.db.get_all( |
| 1353 | "Journal Entry Account", {"parent": voucher_no, "account": ["in", stock_accounts]}, "account" |
| 1354 | ) |
| 1355 | ] |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1356 | |
| 1357 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1358 | stock_accounts = [ |
| 1359 | d.account |
| 1360 | for d in frappe.db.get_all( |
| 1361 | "GL Entry", |
| 1362 | {"voucher_type": voucher_type, "voucher_no": voucher_no, "account": ["in", stock_accounts]}, |
| 1363 | "account", |
| 1364 | ) |
| 1365 | ] |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1366 | |
| 1367 | return stock_accounts |
| 1368 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1369 | |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1370 | def get_stock_and_account_balance(account=None, posting_date=None, company=None): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1371 | if not posting_date: |
| 1372 | posting_date = nowdate() |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1373 | |
| 1374 | warehouse_account = get_warehouse_account_map(company) |
| 1375 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1376 | account_balance = get_balance_on( |
| 1377 | account, posting_date, in_account_currency=False, ignore_account_permission=True |
| 1378 | ) |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1379 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1380 | related_warehouses = [ |
| 1381 | wh |
| 1382 | for wh, wh_details in warehouse_account.items() |
| 1383 | if wh_details.account == account and not wh_details.is_group |
| 1384 | ] |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1385 | |
s-aga-r | e782a05 | 2023-04-25 13:54:36 +0530 | [diff] [blame] | 1386 | total_stock_value = get_stock_value_on(related_warehouses, posting_date) |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1387 | |
| 1388 | precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency") |
| 1389 | return flt(account_balance, precision), flt(total_stock_value, precision), related_warehouses |
| 1390 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1391 | |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1392 | def get_journal_entry(account, stock_adjustment_account, amount): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1393 | db_or_cr_warehouse_account = ( |
| 1394 | "credit_in_account_currency" if amount < 0 else "debit_in_account_currency" |
| 1395 | ) |
| 1396 | db_or_cr_stock_adjustment_account = ( |
| 1397 | "debit_in_account_currency" if amount < 0 else "credit_in_account_currency" |
| 1398 | ) |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1399 | |
| 1400 | return { |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1401 | "accounts": [ |
| 1402 | {"account": account, db_or_cr_warehouse_account: abs(amount)}, |
| 1403 | {"account": stock_adjustment_account, db_or_cr_stock_adjustment_account: abs(amount)}, |
| 1404 | ] |
Nabin Hait | b99c77b | 2020-12-25 18:12:35 +0530 | [diff] [blame] | 1405 | } |
Shadrak Gurupnor | 7433757 | 2021-08-27 18:00:16 +0530 | [diff] [blame] | 1406 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1407 | |
Shadrak Gurupnor | 7433757 | 2021-08-27 18:00:16 +0530 | [diff] [blame] | 1408 | def check_and_delete_linked_reports(report): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1409 | """Check if reports are referenced in Desktop Icon""" |
| 1410 | icons = frappe.get_all("Desktop Icon", fields=["name"], filters={"_report": report}) |
Shadrak Gurupnor | 7433757 | 2021-08-27 18:00:16 +0530 | [diff] [blame] | 1411 | if icons: |
| 1412 | for icon in icons: |
| 1413 | frappe.delete_doc("Desktop Icon", icon) |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1414 | |
| 1415 | |
ruthra kumar | 4f51c5a | 2023-07-03 14:35:33 +0530 | [diff] [blame] | 1416 | def create_err_and_its_journals(companies: list = None) -> None: |
| 1417 | if companies: |
| 1418 | for company in companies: |
| 1419 | err = frappe.new_doc("Exchange Rate Revaluation") |
| 1420 | err.company = company.name |
| 1421 | err.posting_date = nowdate() |
| 1422 | err.rounding_loss_allowance = 0.0 |
| 1423 | |
| 1424 | err.fetch_and_calculate_accounts_data() |
| 1425 | if err.accounts: |
| 1426 | err.save().submit() |
| 1427 | response = err.make_jv_entries() |
| 1428 | |
| 1429 | if company.submit_err_jv: |
| 1430 | jv = response.get("revaluation_jv", None) |
| 1431 | jv and frappe.get_doc("Journal Entry", jv).submit() |
| 1432 | jv = response.get("zero_balance_jv", None) |
| 1433 | jv and frappe.get_doc("Journal Entry", jv).submit() |
| 1434 | |
| 1435 | |
| 1436 | def auto_create_exchange_rate_revaluation_daily() -> None: |
| 1437 | """ |
| 1438 | Executed by background job |
| 1439 | """ |
| 1440 | companies = frappe.db.get_all( |
| 1441 | "Company", |
| 1442 | filters={"auto_exchange_rate_revaluation": 1, "auto_err_frequency": "Daily"}, |
| 1443 | fields=["name", "submit_err_jv"], |
| 1444 | ) |
| 1445 | create_err_and_its_journals(companies) |
| 1446 | |
| 1447 | |
| 1448 | def auto_create_exchange_rate_revaluation_weekly() -> None: |
| 1449 | """ |
| 1450 | Executed by background job |
| 1451 | """ |
| 1452 | companies = frappe.db.get_all( |
| 1453 | "Company", |
| 1454 | filters={"auto_exchange_rate_revaluation": 1, "auto_err_frequency": "Weekly"}, |
| 1455 | fields=["name", "submit_err_jv"], |
| 1456 | ) |
| 1457 | create_err_and_its_journals(companies) |
| 1458 | |
| 1459 | |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1460 | def get_payment_ledger_entries(gl_entries, cancel=0): |
| 1461 | ple_map = [] |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1462 | if gl_entries: |
| 1463 | ple = None |
| 1464 | |
| 1465 | # companies |
| 1466 | account = qb.DocType("Account") |
| 1467 | companies = list(set([x.company for x in gl_entries])) |
| 1468 | |
| 1469 | # receivable/payable account |
| 1470 | accounts_with_types = ( |
| 1471 | qb.from_(account) |
| 1472 | .select(account.name, account.account_type) |
| 1473 | .where( |
| 1474 | (account.account_type.isin(["Receivable", "Payable"]) & (account.company.isin(companies))) |
| 1475 | ) |
| 1476 | .run(as_dict=True) |
| 1477 | ) |
| 1478 | receivable_or_payable_accounts = [y.name for y in accounts_with_types] |
| 1479 | |
| 1480 | def get_account_type(account): |
| 1481 | for entry in accounts_with_types: |
| 1482 | if entry.name == account: |
| 1483 | return entry.account_type |
| 1484 | |
| 1485 | dr_or_cr = 0 |
| 1486 | account_type = None |
| 1487 | for gle in gl_entries: |
| 1488 | if gle.account in receivable_or_payable_accounts: |
| 1489 | account_type = get_account_type(gle.account) |
| 1490 | if account_type == "Receivable": |
| 1491 | dr_or_cr = gle.debit - gle.credit |
| 1492 | dr_or_cr_account_currency = gle.debit_in_account_currency - gle.credit_in_account_currency |
| 1493 | elif account_type == "Payable": |
| 1494 | dr_or_cr = gle.credit - gle.debit |
| 1495 | dr_or_cr_account_currency = gle.credit_in_account_currency - gle.debit_in_account_currency |
| 1496 | |
| 1497 | if cancel: |
| 1498 | dr_or_cr *= -1 |
| 1499 | dr_or_cr_account_currency *= -1 |
| 1500 | |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1501 | ple = frappe._dict( |
| 1502 | doctype="Payment Ledger Entry", |
| 1503 | posting_date=gle.posting_date, |
| 1504 | company=gle.company, |
| 1505 | account_type=account_type, |
| 1506 | account=gle.account, |
| 1507 | party_type=gle.party_type, |
| 1508 | party=gle.party, |
| 1509 | cost_center=gle.cost_center, |
| 1510 | finance_book=gle.finance_book, |
| 1511 | due_date=gle.due_date, |
| 1512 | voucher_type=gle.voucher_type, |
| 1513 | voucher_no=gle.voucher_no, |
Deepesh Garg | 1e078d0 | 2023-06-29 12:18:25 +0530 | [diff] [blame] | 1514 | voucher_detail_no=gle.voucher_detail_no, |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1515 | against_voucher_type=gle.against_voucher_type |
| 1516 | if gle.against_voucher_type |
| 1517 | else gle.voucher_type, |
| 1518 | against_voucher_no=gle.against_voucher if gle.against_voucher else gle.voucher_no, |
| 1519 | account_currency=gle.account_currency, |
| 1520 | amount=dr_or_cr, |
| 1521 | amount_in_account_currency=dr_or_cr_account_currency, |
| 1522 | delinked=True if cancel else False, |
| 1523 | remarks=gle.remarks, |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1524 | ) |
| 1525 | |
| 1526 | dimensions_and_defaults = get_dimensions() |
| 1527 | if dimensions_and_defaults: |
| 1528 | for dimension in dimensions_and_defaults[0]: |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1529 | ple[dimension.fieldname] = gle.get(dimension.fieldname) |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1530 | |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1531 | ple_map.append(ple) |
| 1532 | return ple_map |
| 1533 | |
| 1534 | |
| 1535 | def create_payment_ledger_entry( |
Deepesh Garg | 1e078d0 | 2023-06-29 12:18:25 +0530 | [diff] [blame] | 1536 | gl_entries, cancel=0, adv_adj=0, update_outstanding="Yes", from_repost=0, partial_cancel=False |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1537 | ): |
| 1538 | if gl_entries: |
| 1539 | ple_map = get_payment_ledger_entries(gl_entries, cancel=cancel) |
| 1540 | |
| 1541 | for entry in ple_map: |
| 1542 | |
| 1543 | ple = frappe.get_doc(entry) |
| 1544 | |
| 1545 | if cancel: |
Deepesh Garg | 1e078d0 | 2023-06-29 12:18:25 +0530 | [diff] [blame] | 1546 | delink_original_entry(ple, partial_cancel=partial_cancel) |
ruthra kumar | 9b50221 | 2022-10-13 14:13:48 +0530 | [diff] [blame] | 1547 | |
| 1548 | ple.flags.ignore_permissions = 1 |
| 1549 | ple.flags.adv_adj = adv_adj |
| 1550 | ple.flags.from_repost = from_repost |
| 1551 | ple.flags.update_outstanding = update_outstanding |
| 1552 | ple.submit() |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1553 | |
| 1554 | |
ruthra kumar | 7312f22 | 2022-05-29 21:33:08 +0530 | [diff] [blame] | 1555 | def update_voucher_outstanding(voucher_type, voucher_no, account, party_type, party): |
| 1556 | ple = frappe.qb.DocType("Payment Ledger Entry") |
| 1557 | vouchers = [frappe._dict({"voucher_type": voucher_type, "voucher_no": voucher_no})] |
| 1558 | common_filter = [] |
| 1559 | if account: |
| 1560 | common_filter.append(ple.account == account) |
| 1561 | |
| 1562 | if party_type: |
| 1563 | common_filter.append(ple.party_type == party_type) |
| 1564 | |
| 1565 | if party: |
| 1566 | common_filter.append(ple.party == party) |
| 1567 | |
| 1568 | ple_query = QueryPaymentLedger() |
| 1569 | |
| 1570 | # on cancellation outstanding can be an empty list |
| 1571 | voucher_outstanding = ple_query.get_voucher_outstandings(vouchers, common_filter=common_filter) |
ruthra kumar | 43b8068 | 2022-10-18 09:33:37 +0530 | [diff] [blame] | 1572 | if ( |
| 1573 | voucher_type in ["Sales Invoice", "Purchase Invoice", "Fees"] |
| 1574 | and party_type |
| 1575 | and party |
| 1576 | and voucher_outstanding |
| 1577 | ): |
ruthra kumar | 7312f22 | 2022-05-29 21:33:08 +0530 | [diff] [blame] | 1578 | outstanding = voucher_outstanding[0] |
| 1579 | ref_doc = frappe.get_doc(voucher_type, voucher_no) |
| 1580 | |
| 1581 | # Didn't use db_set for optimisation purpose |
ruthra kumar | b9a7ff7 | 2023-02-12 14:06:40 +0530 | [diff] [blame] | 1582 | ref_doc.outstanding_amount = outstanding["outstanding_in_account_currency"] or 0.0 |
ruthra kumar | 7312f22 | 2022-05-29 21:33:08 +0530 | [diff] [blame] | 1583 | frappe.db.set_value( |
ruthra kumar | b9a7ff7 | 2023-02-12 14:06:40 +0530 | [diff] [blame] | 1584 | voucher_type, |
| 1585 | voucher_no, |
| 1586 | "outstanding_amount", |
| 1587 | outstanding["outstanding_in_account_currency"] or 0.0, |
ruthra kumar | 7312f22 | 2022-05-29 21:33:08 +0530 | [diff] [blame] | 1588 | ) |
| 1589 | |
| 1590 | ref_doc.set_status(update=True) |
| 1591 | |
| 1592 | |
Deepesh Garg | 1e078d0 | 2023-06-29 12:18:25 +0530 | [diff] [blame] | 1593 | def delink_original_entry(pl_entry, partial_cancel=False): |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1594 | if pl_entry: |
| 1595 | ple = qb.DocType("Payment Ledger Entry") |
| 1596 | query = ( |
| 1597 | qb.update(ple) |
| 1598 | .set(ple.delinked, True) |
| 1599 | .set(ple.modified, now()) |
| 1600 | .set(ple.modified_by, frappe.session.user) |
| 1601 | .where( |
| 1602 | (ple.company == pl_entry.company) |
| 1603 | & (ple.account_type == pl_entry.account_type) |
| 1604 | & (ple.account == pl_entry.account) |
| 1605 | & (ple.party_type == pl_entry.party_type) |
| 1606 | & (ple.party == pl_entry.party) |
| 1607 | & (ple.voucher_type == pl_entry.voucher_type) |
| 1608 | & (ple.voucher_no == pl_entry.voucher_no) |
| 1609 | & (ple.against_voucher_type == pl_entry.against_voucher_type) |
| 1610 | & (ple.against_voucher_no == pl_entry.against_voucher_no) |
| 1611 | ) |
| 1612 | ) |
Deepesh Garg | 1e078d0 | 2023-06-29 12:18:25 +0530 | [diff] [blame] | 1613 | |
| 1614 | if partial_cancel: |
| 1615 | query = query.where(ple.voucher_detail_no == pl_entry.voucher_detail_no) |
| 1616 | |
ruthra kumar | 451cf3a | 2022-05-16 14:29:58 +0530 | [diff] [blame] | 1617 | query.run() |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1618 | |
| 1619 | |
| 1620 | class QueryPaymentLedger(object): |
| 1621 | """ |
| 1622 | Helper Class for Querying Payment Ledger Entry |
| 1623 | """ |
| 1624 | |
| 1625 | def __init__(self): |
| 1626 | self.ple = qb.DocType("Payment Ledger Entry") |
| 1627 | |
| 1628 | # query result |
| 1629 | self.voucher_outstandings = [] |
| 1630 | |
| 1631 | # query filters |
| 1632 | self.vouchers = [] |
| 1633 | self.common_filter = [] |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 1634 | self.voucher_posting_date = [] |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1635 | self.min_outstanding = None |
| 1636 | self.max_outstanding = None |
| 1637 | |
| 1638 | def reset(self): |
| 1639 | # clear filters |
| 1640 | self.vouchers.clear() |
| 1641 | self.common_filter.clear() |
| 1642 | self.min_outstanding = self.max_outstanding = None |
| 1643 | |
| 1644 | # clear result |
| 1645 | self.voucher_outstandings.clear() |
| 1646 | |
| 1647 | def query_for_outstanding(self): |
| 1648 | """ |
| 1649 | Database query to fetch voucher amount and voucher outstanding using Common Table Expression |
| 1650 | """ |
| 1651 | |
| 1652 | ple = self.ple |
| 1653 | |
| 1654 | filter_on_voucher_no = [] |
| 1655 | filter_on_against_voucher_no = [] |
| 1656 | if self.vouchers: |
| 1657 | voucher_types = set([x.voucher_type for x in self.vouchers]) |
| 1658 | voucher_nos = set([x.voucher_no for x in self.vouchers]) |
| 1659 | |
| 1660 | filter_on_voucher_no.append(ple.voucher_type.isin(voucher_types)) |
| 1661 | filter_on_voucher_no.append(ple.voucher_no.isin(voucher_nos)) |
| 1662 | |
| 1663 | filter_on_against_voucher_no.append(ple.against_voucher_type.isin(voucher_types)) |
| 1664 | filter_on_against_voucher_no.append(ple.against_voucher_no.isin(voucher_nos)) |
| 1665 | |
| 1666 | # build outstanding amount filter |
| 1667 | filter_on_outstanding_amount = [] |
| 1668 | if self.min_outstanding: |
| 1669 | if self.min_outstanding > 0: |
| 1670 | filter_on_outstanding_amount.append( |
| 1671 | Table("outstanding").amount_in_account_currency >= self.min_outstanding |
| 1672 | ) |
| 1673 | else: |
| 1674 | filter_on_outstanding_amount.append( |
| 1675 | Table("outstanding").amount_in_account_currency <= self.min_outstanding |
| 1676 | ) |
| 1677 | if self.max_outstanding: |
| 1678 | if self.max_outstanding > 0: |
| 1679 | filter_on_outstanding_amount.append( |
| 1680 | Table("outstanding").amount_in_account_currency <= self.max_outstanding |
| 1681 | ) |
| 1682 | else: |
| 1683 | filter_on_outstanding_amount.append( |
| 1684 | Table("outstanding").amount_in_account_currency >= self.max_outstanding |
| 1685 | ) |
| 1686 | |
| 1687 | # build query for voucher amount |
| 1688 | query_voucher_amount = ( |
| 1689 | qb.from_(ple) |
| 1690 | .select( |
| 1691 | ple.account, |
| 1692 | ple.voucher_type, |
| 1693 | ple.voucher_no, |
| 1694 | ple.party_type, |
| 1695 | ple.party, |
| 1696 | ple.posting_date, |
| 1697 | ple.due_date, |
| 1698 | ple.account_currency.as_("currency"), |
| 1699 | Sum(ple.amount).as_("amount"), |
| 1700 | Sum(ple.amount_in_account_currency).as_("amount_in_account_currency"), |
| 1701 | ) |
| 1702 | .where(ple.delinked == 0) |
| 1703 | .where(Criterion.all(filter_on_voucher_no)) |
| 1704 | .where(Criterion.all(self.common_filter)) |
ruthra kumar | 6d9d730 | 2022-12-14 16:05:15 +0530 | [diff] [blame] | 1705 | .where(Criterion.all(self.dimensions_filter)) |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 1706 | .where(Criterion.all(self.voucher_posting_date)) |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1707 | .groupby(ple.voucher_type, ple.voucher_no, ple.party_type, ple.party) |
| 1708 | ) |
| 1709 | |
| 1710 | # build query for voucher outstanding |
| 1711 | query_voucher_outstanding = ( |
| 1712 | qb.from_(ple) |
| 1713 | .select( |
| 1714 | ple.account, |
| 1715 | ple.against_voucher_type.as_("voucher_type"), |
| 1716 | ple.against_voucher_no.as_("voucher_no"), |
| 1717 | ple.party_type, |
| 1718 | ple.party, |
| 1719 | ple.posting_date, |
| 1720 | ple.due_date, |
| 1721 | ple.account_currency.as_("currency"), |
| 1722 | Sum(ple.amount).as_("amount"), |
| 1723 | Sum(ple.amount_in_account_currency).as_("amount_in_account_currency"), |
| 1724 | ) |
| 1725 | .where(ple.delinked == 0) |
| 1726 | .where(Criterion.all(filter_on_against_voucher_no)) |
| 1727 | .where(Criterion.all(self.common_filter)) |
| 1728 | .groupby(ple.against_voucher_type, ple.against_voucher_no, ple.party_type, ple.party) |
| 1729 | ) |
| 1730 | |
| 1731 | # build CTE for combining voucher amount and outstanding |
| 1732 | self.cte_query_voucher_amount_and_outstanding = ( |
| 1733 | qb.with_(query_voucher_amount, "vouchers") |
| 1734 | .with_(query_voucher_outstanding, "outstanding") |
| 1735 | .from_(AliasedQuery("vouchers")) |
| 1736 | .left_join(AliasedQuery("outstanding")) |
| 1737 | .on( |
| 1738 | (AliasedQuery("vouchers").account == AliasedQuery("outstanding").account) |
| 1739 | & (AliasedQuery("vouchers").voucher_type == AliasedQuery("outstanding").voucher_type) |
| 1740 | & (AliasedQuery("vouchers").voucher_no == AliasedQuery("outstanding").voucher_no) |
| 1741 | & (AliasedQuery("vouchers").party_type == AliasedQuery("outstanding").party_type) |
| 1742 | & (AliasedQuery("vouchers").party == AliasedQuery("outstanding").party) |
| 1743 | ) |
| 1744 | .select( |
| 1745 | Table("vouchers").account, |
| 1746 | Table("vouchers").voucher_type, |
| 1747 | Table("vouchers").voucher_no, |
| 1748 | Table("vouchers").party_type, |
| 1749 | Table("vouchers").party, |
| 1750 | Table("vouchers").posting_date, |
| 1751 | Table("vouchers").amount.as_("invoice_amount"), |
| 1752 | Table("vouchers").amount_in_account_currency.as_("invoice_amount_in_account_currency"), |
| 1753 | Table("outstanding").amount.as_("outstanding"), |
| 1754 | Table("outstanding").amount_in_account_currency.as_("outstanding_in_account_currency"), |
| 1755 | (Table("vouchers").amount - Table("outstanding").amount).as_("paid_amount"), |
| 1756 | ( |
| 1757 | Table("vouchers").amount_in_account_currency - Table("outstanding").amount_in_account_currency |
| 1758 | ).as_("paid_amount_in_account_currency"), |
| 1759 | Table("vouchers").due_date, |
| 1760 | Table("vouchers").currency, |
| 1761 | ) |
| 1762 | .where(Criterion.all(filter_on_outstanding_amount)) |
| 1763 | ) |
| 1764 | |
| 1765 | # build CTE filter |
| 1766 | # only fetch invoices |
| 1767 | if self.get_invoices: |
| 1768 | self.cte_query_voucher_amount_and_outstanding = ( |
| 1769 | self.cte_query_voucher_amount_and_outstanding.having( |
| 1770 | qb.Field("outstanding_in_account_currency") > 0 |
| 1771 | ) |
| 1772 | ) |
| 1773 | # only fetch payments |
| 1774 | elif self.get_payments: |
| 1775 | self.cte_query_voucher_amount_and_outstanding = ( |
| 1776 | self.cte_query_voucher_amount_and_outstanding.having( |
| 1777 | qb.Field("outstanding_in_account_currency") < 0 |
| 1778 | ) |
| 1779 | ) |
| 1780 | |
| 1781 | # execute SQL |
| 1782 | self.voucher_outstandings = self.cte_query_voucher_amount_and_outstanding.run(as_dict=True) |
| 1783 | |
| 1784 | def get_voucher_outstandings( |
| 1785 | self, |
| 1786 | vouchers=None, |
| 1787 | common_filter=None, |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 1788 | posting_date=None, |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1789 | min_outstanding=None, |
| 1790 | max_outstanding=None, |
| 1791 | get_payments=False, |
| 1792 | get_invoices=False, |
ruthra kumar | 6d9d730 | 2022-12-14 16:05:15 +0530 | [diff] [blame] | 1793 | accounting_dimensions=None, |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1794 | ): |
| 1795 | """ |
| 1796 | Fetch voucher amount and outstanding amount from Payment Ledger using Database CTE |
| 1797 | |
| 1798 | vouchers - dict of vouchers to get |
| 1799 | common_filter - array of criterions |
| 1800 | min_outstanding - filter on minimum total outstanding amount |
| 1801 | max_outstanding - filter on maximum total outstanding amount |
| 1802 | get_invoices - only fetch vouchers(ledger entries with +ve outstanding) |
| 1803 | get_payments - only fetch payments(ledger entries with -ve outstanding) |
| 1804 | """ |
| 1805 | |
| 1806 | self.reset() |
| 1807 | self.vouchers = vouchers |
| 1808 | self.common_filter = common_filter or [] |
ruthra kumar | 6d9d730 | 2022-12-14 16:05:15 +0530 | [diff] [blame] | 1809 | self.dimensions_filter = accounting_dimensions or [] |
ruthra kumar | 5f1562c | 2022-07-28 11:57:27 +0530 | [diff] [blame] | 1810 | self.voucher_posting_date = posting_date or [] |
ruthra kumar | 7b38388 | 2022-05-25 15:51:16 +0530 | [diff] [blame] | 1811 | self.min_outstanding = min_outstanding |
| 1812 | self.max_outstanding = max_outstanding |
| 1813 | self.get_payments = get_payments |
| 1814 | self.get_invoices = get_invoices |
| 1815 | self.query_for_outstanding() |
| 1816 | |
| 1817 | return self.voucher_outstandings |