def get_conditions(self, get_invoices=False, get_payments=False, get_return_invoices=False): condition = " and company = '{0}' ".format(self.company) if get_invoices: condition += " and posting_date >= {0}".format( frappe.db.escape( self.from_invoice_date)) if self.from_invoice_date else "" condition += " and posting_date <= {0}".format( frappe.db.escape( self.to_invoice_date)) if self.to_invoice_date else "" dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "credit_in_account_currency") if self.minimum_invoice_amount: condition += " and `{0}` >= {1}".format( dr_or_cr, flt(self.minimum_invoice_amount)) if self.maximum_invoice_amount: condition += " and `{0}` <= {1}".format( dr_or_cr, flt(self.maximum_invoice_amount)) elif get_return_invoices: condition = " and doc.company = '{0}' ".format(self.company) condition += " and doc.posting_date >= {0}".format( frappe.db.escape( self.from_payment_date)) if self.from_payment_date else "" condition += " and doc.posting_date <= {0}".format( frappe.db.escape( self.to_payment_date)) if self.to_payment_date else "" dr_or_cr = ("gl.debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "gl.credit_in_account_currency") if self.minimum_invoice_amount: condition += " and `{0}` >= {1}".format( dr_or_cr, flt(self.minimum_payment_amount)) if self.maximum_invoice_amount: condition += " and `{0}` <= {1}".format( dr_or_cr, flt(self.maximum_payment_amount)) else: condition += " and posting_date >= {0}".format( frappe.db.escape( self.from_payment_date)) if self.from_payment_date else "" condition += " and posting_date <= {0}".format( frappe.db.escape( self.to_payment_date)) if self.to_payment_date else "" if self.minimum_payment_amount: condition += " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount)) if get_payments \ else " and total_debit >= {0}".format(flt(self.minimum_payment_amount)) if self.maximum_payment_amount: condition += " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount)) if get_payments \ else " and total_debit <= {0}".format(flt(self.maximum_payment_amount)) return condition
def get_balance_on_voucher(voucher_type, voucher_no, party_type, party, account, dr_or_cr=None): if not dr_or_cr: if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" res = frappe.db.sql( """ select ifnull(sum({dr_or_cr}), 0) from `tabGL Entry` where party_type=%(party_type)s and party=%(party)s and account=%(account)s and ((voucher_type=%(voucher_type)s and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher='')) or (against_voucher_type=%(voucher_type)s and against_voucher=%(voucher_no)s)) """.format(dr_or_cr=dr_or_cr), { "voucher_type": voucher_type, "voucher_no": voucher_no, "party_type": party_type, "party": party, "account": account }) return flt(res[0][0]) if res else 0.0
def get_unnallocated_parties(doctype, txt, searchfield, start, page_len, filters): company = filters["company"] party_type = filters["party_type"] bank_cash_account = filters["bank_cash_account"] receivable_payable_account = filters["receivable_payable_account"] dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(party_type) == 'Receivable' else "debit_in_account_currency") bank_account_condition = ("t2.against_account like %(bank_cash_account)s" if bank_cash_account else "1=1") limit_cond = "limit 100" paid_parties = frappe.db.sql( """ select DISTINCT party from `tabJournal Entry` t1, `tabJournal Entry Account` t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1 and t2.party_type = %(party_type)s and t2.account = %(account)s and {dr_or_cr} > 0 and (t2.reference_type is null or t2.reference_type = '' or (t2.reference_type in ('Sales Order', 'Purchase Order') and t2.reference_name is not null and t2.reference_name != '')) and (CASE WHEN t1.voucher_type in ('Debit Note', 'Credit Note') THEN 1=1 ELSE {bank_account_condition} END) order by t1.posting_date {limit_cond} """.format( **{ "dr_or_cr": dr_or_cr, "bank_account_condition": bank_account_condition, "limit_cond": limit_cond }), { "party_type": party_type, "account": receivable_payable_account, "bank_cash_account": "%%%s%%" % bank_cash_account }, as_dict=0) unreconciled_parties = [] condition = "" for party in paid_parties: invoices = get_outstanding_invoices(party_type, party, receivable_payable_account, condition=condition) if invoices: unreconciled_parties.append(party) return unreconciled_parties
def reconcile(self, args): for e in self.get('payments'): e.invoice_type = None if e.invoice_number and " | " in e.invoice_number: e.invoice_type, e.invoice_number = e.invoice_number.split(" | ") self.get_invoice_entries() self.validate_invoice() dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") lst = [] for e in self.get('payments'): if e.invoice_number and e.allocated_amount: lst.append(frappe._dict({ 'voucher_type': e.reference_type, 'voucher_no' : e.reference_name, 'voucher_detail_no' : e.reference_row, 'against_voucher_type' : e.invoice_type, 'against_voucher' : e.invoice_number, 'account' : self.receivable_payable_account, 'party_type': self.party_type, 'party': self.party, 'is_advance' : e.is_advance, 'dr_or_cr' : dr_or_cr, 'unadjusted_amount' : flt(e.amount), 'allocated_amount' : flt(e.allocated_amount) })) if lst: from erpnext.accounts.utils import reconcile_against_document reconcile_against_document(lst) msgprint(_("Successfully Reconciled")) self.get_unreconciled_entries()
def reconcile(self): for e in self.get('payments'): e.invoice_type = None if e.invoice_number and " | " in e.invoice_number: e.invoice_type, e.invoice_number = e.invoice_number.split(" | ") self.get_invoice_entries() self.validate_invoice() dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") lst = [] for e in self.get('payments'): if e.invoice_number and e.allocated_amount: lst.append(frappe._dict({ 'voucher_type': e.reference_type, 'voucher_no' : e.reference_name, 'voucher_detail_no' : e.reference_row, 'against_voucher_type' : e.invoice_type, 'against_voucher' : e.invoice_number, 'account' : self.receivable_payable_account, 'party_type': self.party_type, 'party': self.party, 'dr_or_cr' : dr_or_cr, 'unadjusted_amount' : flt(e.amount), 'allocated_amount' : flt(e.allocated_amount) })) if lst: from erpnext.accounts.utils import reconcile_against_document reconcile_against_document(lst) msgprint(_("Successfully Reconciled")) self.get_unreconciled_entries()
def get_dr_or_cr_notes(self): dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") reconciled_dr_or_cr = ("debit_in_account_currency" if dr_or_cr == "credit_in_account_currency" else "credit_in_account_currency") voucher_type = ('Sales Invoice' if self.party_type == 'Customer' else "Purchase Invoice") return frappe.db.sql( """ SELECT `tab{doc}`.name as reference_name, %(voucher_type)s as reference_type, (sum(`tabGL Entry`.{dr_or_cr}) - sum(`tabGL Entry`.{reconciled_dr_or_cr})) as amount FROM `tab{doc}`, `tabGL Entry` WHERE (`tab{doc}`.name = `tabGL Entry`.against_voucher or `tab{doc}`.name = `tabGL Entry`.voucher_no) and `tab{doc}`.is_return = 1 and `tabGL Entry`.against_voucher_type = %(voucher_type)s and `tab{doc}`.docstatus = 1 and `tabGL Entry`.party = %(party)s and `tabGL Entry`.party_type = %(party_type)s and `tabGL Entry`.account = %(account)s GROUP BY `tabSales Invoice`.name Having amount > 0 """.format(doc=voucher_type, dr_or_cr=dr_or_cr, reconciled_dr_or_cr=reconciled_dr_or_cr), { 'party': self.party, 'party_type': self.party_type, 'voucher_type': voucher_type, 'account': self.receivable_payable_account }, as_dict=1)
def reconcile(self, args): for e in self.get('payments'): e.invoice_type = None if e.invoice_number and " | " in e.invoice_number: e.invoice_type, e.invoice_number = e.invoice_number.split(" | ") self.get_invoice_entries() self.validate_invoice() dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") lst = [] dr_or_cr_notes = [] for e in self.get('payments'): reconciled_entry = [] if e.invoice_number and e.allocated_amount: if e.reference_type in ['Sales Invoice', 'Purchase Invoice']: reconciled_entry = dr_or_cr_notes else: reconciled_entry = lst reconciled_entry.append(self.get_payment_details(e, dr_or_cr)) if lst: reconcile_against_document(lst) if dr_or_cr_notes: reconcile_dr_cr_note(dr_or_cr_notes, self.company) msgprint(_("Successfully Reconciled")) self.get_unreconciled_entries()
def get_balance_on_voucher(voucher_type, voucher_no, party_type, party, account, dr_or_cr=None): if not dr_or_cr: if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" if isinstance(account, list): account = ["'{0}'".format(frappe.db.escape(d)) for d in account] account_condition = "account in ({0})".format(", ".join(account)) else: account_condition = "account = '{0}'".format(frappe.db.escape(account)) res = frappe.db.sql( """ select ifnull(sum({dr_or_cr}), 0) from `tabGL Entry` where party_type=%(party_type)s and party=%(party)s and {account_condition} and ((voucher_type=%(voucher_type)s and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher='')) or (against_voucher_type=%(voucher_type)s and against_voucher=%(voucher_no)s)) """.format(dr_or_cr=dr_or_cr, account_condition=account_condition), { "voucher_type": voucher_type, "voucher_no": voucher_no, "party_type": party_type, "party": party }) return flt(res[0][0]) if res else 0.0
def reconcile(self): self.validate_allocation() dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") entry_list = [] dr_or_cr_notes = [] for row in self.get('allocation'): reconciled_entry = [] if row.invoice_number and row.allocated_amount: if row.reference_type in ['Sales Invoice', 'Purchase Invoice']: reconciled_entry = dr_or_cr_notes else: reconciled_entry = entry_list reconciled_entry.append(self.get_payment_details( row, dr_or_cr)) if entry_list: reconcile_against_document(entry_list) if dr_or_cr_notes: reconcile_dr_cr_note(dr_or_cr_notes, self.company) msgprint(_("Successfully Reconciled")) self.get_unreconciled_entries()
def validate_party_details(self): if self.party: if not frappe.db.exists(self.party_type, self.party): frappe.throw(_("Invalid {0}: {1}").format(self.party_type, self.party)) if self.party_account and self.party_type in ("Customer", "Supplier"): self.validate_account_type(self.party_account, [erpnext.get_party_account_type(self.party_type)])
def validate_party_details(self): if self.party: if not frappe.db.exists(self.party_type, self.party): frappe.throw(_("Invalid {0}: {1}").format(self.party_type, self.party)) if self.party_account and self.party_type in ("Customer", "Supplier"): self.validate_account_type(self.party_account, [erpnext.get_party_account_type(self.party_type)])
def check_if_advance_entry_modified(args): """ check if there is already a voucher reference check if amount is same check if jv is submitted """ if not args.get("unreconciled_amount"): args.update({"unreconciled_amount": args.get("unadjusted_amount")}) ret = None if args.voucher_type == "Journal Entry": ret = frappe.db.sql( """ select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2 where t1.name = t2.parent and t2.account = %(account)s and t2.party_type = %(party_type)s and t2.party = %(party)s and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order")) and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s and t1.docstatus=1 """.format( dr_or_cr=args.get("dr_or_cr") ), args, ) else: party_account_field = ( "paid_from" if erpnext.get_party_account_type(args.party_type) == "Receivable" else "paid_to" ) if args.voucher_detail_no: ret = frappe.db.sql( """select t1.name from `tabPayment Entry` t1, `tabPayment Entry Reference` t2 where t1.name = t2.parent and t1.docstatus = 1 and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s and t2.reference_doctype in ("", "Sales Order", "Purchase Order") and t2.allocated_amount = %(unreconciled_amount)s """.format( party_account_field ), args, ) else: ret = frappe.db.sql( """select name from `tabPayment Entry` where name = %(voucher_no)s and docstatus = 1 and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s and unallocated_amount = %(unreconciled_amount)s """.format( party_account_field ), args, ) if not ret: throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
def get_advance_payment_entries(party_type, party, party_account, order_doctype, order_list=None, include_unallocated=True, against_all_orders=False, against_account=None, limit=1000): payment_entries_against_order, unallocated_payment_entries = [], [] party_account_type = erpnext.get_party_account_type(party_type) party_account_field = "paid_from" if party_account_type == "Receivable" else "paid_to" against_account_field = "paid_to" if party_account_type == "Receivable" else "paid_from" payment_type = "Receive" if party_account_type == "Receivable" else "Pay" against_account_condition = "" if against_account: against_account_condition = "and pe.{against_account_field} = {against_account}".format( against_account_field=against_account_field, against_account=frappe.db.escape(against_account)) if order_list or against_all_orders: if order_list: reference_condition = " and pref.reference_name in ({0})" \ .format(', '.join(['%s'] * len(order_list))) else: reference_condition = "" order_list = [] payment_entries_against_order = frappe.db.sql(""" select "Payment Entry" as reference_type, pe.name as reference_name, pe.remarks, pref.allocated_amount as amount, pref.name as reference_row, pref.reference_name as against_order, pe.posting_date from `tabPayment Entry` pe, `tabPayment Entry Reference` pref where pe.name = pref.parent and pe.{party_account_field} = %s and pe.payment_type = %s and pe.party_type = %s and pe.party = %s and pe.docstatus = 1 and pref.reference_doctype = %s {reference_condition} {against_account_condition} order by pe.posting_date limit %s """.format( party_account_field=party_account_field, reference_condition=reference_condition, against_account_condition=against_account_condition ), [party_account, payment_type, party_type, party, order_doctype] + order_list + [limit or 1000], as_dict=1) if include_unallocated: unallocated_payment_entries = frappe.db.sql(""" select "Payment Entry" as reference_type, name as reference_name, remarks, unallocated_amount as amount from `tabPayment Entry` pe where {party_account_field} = %s and party_type = %s and party = %s and payment_type = %s and docstatus = 1 and unallocated_amount > 0 {against_account_condition} order by posting_date limit %s """.format( party_account_field=party_account_field, against_account_condition=against_account_condition ), [party_account, party_type, party, payment_type, limit or 1000], as_dict=1) return list(payment_entries_against_order) + list(unallocated_payment_entries)
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) # confirm that Supplier is not blocked if args.get('party_type') == 'Supplier': supplier_status = get_supplier_block_status(args['party']) if supplier_status['on_hold']: if supplier_status['hold_type'] == 'All': return [] elif supplier_status['hold_type'] == 'Payments': if not supplier_status['release_date'] or getdate(nowdate()) <= supplier_status['release_date']: return [] party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.get_cached_value('Company', args.get("company"), "default_currency") # Get outstanding invoices condition = "" if args.get("voucher_type") and args.get("voucher_no"): condition = " and voucher_type='{0}' and voucher_no='{1}'"\ .format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"])) # Add cost center condition if args.get("cost_center") and get_allow_cost_center_in_entry_of_bs_account(): condition += " and cost_center='%s'" % args.get("cost_center") negative_invoices = False party_account_type = erpnext.get_party_account_type(args.get("party_type")) if (args.get("payment_type") == "Receive" and party_account_type == "Payable") \ or (args.get("payment_type") == "Pay" and party_account_type == "Receivable"): negative_invoices = True outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition, negative_invoices=negative_invoices) for d in outstanding_invoices: d["exchange_rate"] = 1 if party_account_currency != company_currency: if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"): d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate") elif d.voucher_type == "Journal Entry": d["exchange_rate"] = get_average_party_exchange_rate_on_journal_entry(d.voucher_no, args.get("party_type"), args.get("party"), args.get("party_account")) if d.voucher_type in ("Purchase Invoice"): d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no") # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = [] if not negative_invoices: orders_to_be_billed = get_orders_to_be_billed(args.get("posting_date"),args.get("party_type"), args.get("party"), party_account_currency, company_currency) return outstanding_invoices + orders_to_be_billed
def check_condition(self): cond = " and posting_date >= '{0}'".format(frappe.db.escape(self.from_date)) if self.from_date else "" cond += " and posting_date <= '{0}'".format(frappe.db.escape(self.to_date)) if self.to_date else "" dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "credit_in_account_currency") if self.minimum_amount: cond += " and `{0}` >= {1}".format(dr_or_cr, flt(self.minimum_amount)) if self.maximum_amount: cond += " and `{0}` <= {1}".format(dr_or_cr, flt(self.maximum_amount)) return cond
def add_party_gl_entries(self, gl_entries): if self.party_account: if self.payment_type == "Receive": against_account = self.paid_to else: against_account = self.paid_from party_gl_dict = self.get_gl_dict({ "account": self.party_account, "party_type": self.party_type, "party": self.party, "against": against_account, "account_currency": self.party_account_currency }) dr_or_cr = "credit" if erpnext.get_party_account_type( self.party_type) == 'Receivable' else "debit" for d in self.get("references"): gle = party_gl_dict.copy() gle.update({ "against_voucher_type": d.reference_doctype, "against_voucher": d.reference_name }) allocated_amount_in_company_currency = flt( flt(d.allocated_amount) * flt(d.exchange_rate), self.precision("paid_amount")) gle.update({ dr_or_cr + "_in_account_currency": d.allocated_amount, dr_or_cr: allocated_amount_in_company_currency }) gl_entries.append(gle) if self.unallocated_amount: base_unallocated_amount = base_unallocated_amount = self.unallocated_amount * \ (self.source_exchange_rate if self.payment_type=="Receive" else self.target_exchange_rate) gle = party_gl_dict.copy() gle.update({ dr_or_cr + "_in_account_currency": self.unallocated_amount, dr_or_cr: base_unallocated_amount }) gl_entries.append(gle)
def check_condition(self): cond = " and posting_date >= {0}".format(frappe.db.escape(self.from_date)) if self.from_date else "" cond += " and posting_date <= {0}".format(frappe.db.escape(self.to_date)) if self.to_date else "" dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "credit_in_account_currency") if self.minimum_amount: cond += " and `{0}` >= {1}".format(dr_or_cr, flt(self.minimum_amount)) if self.maximum_amount: cond += " and `{0}` <= {1}".format(dr_or_cr, flt(self.maximum_amount)) return cond
def execute(): doctypes = [('Sales Invoice', 'Customer'), ('Purchase Invoice', 'Supplier'), ('Fees', 'Student'), ('Expense Claim', 'Employee')] for dt, party_type in doctypes: dr_or_cr = "debit - credit" if erpnext.get_party_account_type( party_type) == 'Receivable' else "credit - debit" frappe.db.sql(""" update `tabGL Entry` set against_voucher = '', against_voucher_type = '' where voucher_type = '{dt}' and against_voucher_type = voucher_type and against_voucher = voucher_no and {dr_or_cr} > 0 """.format(dt=dt, dr_or_cr=dr_or_cr))
def get_jv_entries(self): condition = self.get_conditions() dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == "Receivable" else "debit_in_account_currency") bank_account_condition = ( "t2.against_account like %(bank_cash_account)s" if self.bank_cash_account else "1=1") journal_entries = frappe.db.sql( """ select "Journal Entry" as reference_type, t1.name as reference_name, t1.posting_date, t1.remark as remarks, t2.name as reference_row, {dr_or_cr} as amount, t2.is_advance, t2.account_currency as currency from `tabJournal Entry` t1, `tabJournal Entry Account` t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1 and t2.party_type = %(party_type)s and t2.party = %(party)s and t2.account = %(account)s and {dr_or_cr} > 0 {condition} and (t2.reference_type is null or t2.reference_type = '' or (t2.reference_type in ('Sales Order', 'Purchase Order') and t2.reference_name is not null and t2.reference_name != '')) and (CASE WHEN t1.voucher_type in ('Debit Note', 'Credit Note') THEN 1=1 ELSE {bank_account_condition} END) order by t1.posting_date """.format( **{ "dr_or_cr": dr_or_cr, "bank_account_condition": bank_account_condition, "condition": condition, }), { "party_type": self.party_type, "party": self.party, "account": self.receivable_payable_account, "bank_cash_account": "%%%s%%" % self.bank_cash_account, }, as_dict=1, ) return list(journal_entries)
def add_party_gl_entries(self, gl_entries): if self.party_account: if self.payment_type=="Receive": against_account = self.paid_to else: against_account = self.paid_from party_gl_dict = self.get_gl_dict({ "account": self.party_account, "party_type": self.party_type, "party": self.party, "against": against_account, "account_currency": self.party_account_currency, "cost_center": self.cost_center }) dr_or_cr = "credit" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit" for d in self.get("references"): gle = party_gl_dict.copy() gle.update({ "against_voucher_type": d.reference_doctype, "against_voucher": d.reference_name }) allocated_amount_in_company_currency = flt(flt(d.allocated_amount) * flt(d.exchange_rate), self.precision("paid_amount")) gle.update({ dr_or_cr + "_in_account_currency": d.allocated_amount, dr_or_cr: allocated_amount_in_company_currency }) gl_entries.append(gle) if self.unallocated_amount: base_unallocated_amount = base_unallocated_amount = self.unallocated_amount * \ (self.source_exchange_rate if self.payment_type=="Receive" else self.target_exchange_rate) gle = party_gl_dict.copy() gle.update({ dr_or_cr + "_in_account_currency": self.unallocated_amount, dr_or_cr: base_unallocated_amount }) gl_entries.append(gle)
def get_difference_amount(self, child_row): if child_row.get("reference_type") != 'Payment Entry': return child_row = frappe._dict(child_row) if child_row.invoice_number and " | " in child_row.invoice_number: child_row.invoice_type, child_row.invoice_number = child_row.invoice_number.split(" | ") dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") row = self.get_payment_details(child_row, dr_or_cr) doc = frappe.get_doc(row.voucher_type, row.voucher_no) update_reference_in_payment_entry(row, doc, do_not_save=True) return doc.difference_amount
def get_dr_or_cr_notes(self): condition = self.get_conditions(get_return_invoices=True) dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == "Receivable" else "debit_in_account_currency") reconciled_dr_or_cr = ("debit_in_account_currency" if dr_or_cr == "credit_in_account_currency" else "credit_in_account_currency") voucher_type = "Sales Invoice" if self.party_type == "Customer" else "Purchase Invoice" return frappe.db.sql( """ SELECT doc.name as reference_name, %(voucher_type)s as reference_type, (sum(gl.{dr_or_cr}) - sum(gl.{reconciled_dr_or_cr})) as amount, doc.posting_date, account_currency as currency FROM `tab{doc}` doc, `tabGL Entry` gl WHERE (doc.name = gl.against_voucher or doc.name = gl.voucher_no) and doc.{party_type_field} = %(party)s and doc.is_return = 1 and ifnull(doc.return_against, "") = "" and gl.against_voucher_type = %(voucher_type)s and doc.docstatus = 1 and gl.party = %(party)s and gl.party_type = %(party_type)s and gl.account = %(account)s and gl.is_cancelled = 0 {condition} GROUP BY doc.name Having amount > 0 ORDER BY doc.posting_date """.format( doc=voucher_type, dr_or_cr=dr_or_cr, reconciled_dr_or_cr=reconciled_dr_or_cr, party_type_field=frappe.scrub(self.party_type), condition=condition or "", ), { "party": self.party, "party_type": self.party_type, "voucher_type": voucher_type, "account": self.receivable_payable_account, }, as_dict=1, )
def get_jv_entries(self): dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") bank_account_condition = "t2.against_account like %(bank_cash_account)s" \ if self.bank_cash_account else "1=1" limit_cond = "limit %s" % (self.limit or 1000) journal_entries = frappe.db.sql(""" select "Journal Entry" as reference_type, t1.name as reference_name, t1.posting_date, t1.remark as remarks, t2.name as reference_row, {dr_or_cr} as amount, t2.is_advance from `tabJournal Entry` t1, `tabJournal Entry Account` t2 where t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1 and t2.party_type = %(party_type)s and t2.party = %(party)s and t2.account = %(account)s and {dr_or_cr} > 0 and (t2.reference_type is null or t2.reference_type = '' or (t2.reference_type in ('Sales Order', 'Purchase Order') and t2.reference_name is not null and t2.reference_name != '')) and (CASE WHEN t1.voucher_type in ('Debit Note', 'Credit Note') THEN 1=1 ELSE {bank_account_condition} END) order by t1.posting_date {limit_cond} """.format(**{ "dr_or_cr": dr_or_cr, "bank_account_condition": bank_account_condition, "limit_cond": limit_cond }), { "party_type": self.party_type, "party": self.party, "account": self.receivable_payable_account, "bank_cash_account": "%%%s%%" % self.bank_cash_account }, as_dict=1) return list(journal_entries)
def get_dr_or_cr_notes(self): dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency") reconciled_dr_or_cr = ("debit_in_account_currency" if dr_or_cr == "credit_in_account_currency" else "credit_in_account_currency") voucher_type = ('Sales Invoice' if self.party_type == 'Customer' else "Purchase Invoice") return frappe.db.sql( """ SELECT doc.name as reference_name, %(voucher_type)s as reference_type, (sum(gl.{dr_or_cr}) - sum(gl.{reconciled_dr_or_cr})) as amount, account_currency as currency FROM `tab{doc}` doc, `tabGL Entry` gl WHERE (doc.name = gl.against_voucher or doc.name = gl.voucher_no) and doc.{party_type_field} = %(party)s and doc.is_return = 1 and ifnull(doc.return_against, "") = "" and gl.against_voucher_type = %(voucher_type)s and doc.docstatus = 1 and gl.party = %(party)s and gl.party_type = %(party_type)s and gl.account = %(account)s and gl.is_cancelled = 0 GROUP BY doc.name Having amount > 0 """.format(doc=voucher_type, dr_or_cr=dr_or_cr, reconciled_dr_or_cr=reconciled_dr_or_cr, party_type_field=frappe.scrub(self.party_type)), { 'party': self.party, 'party_type': self.party_type, 'voucher_type': voucher_type, 'account': self.receivable_payable_account }, as_dict=1, debug=1)
def check_if_advance_entry_modified(args): """ check if there is already a voucher reference check if amount is same check if jv is submitted """ ret = None if args.voucher_type == "Journal Entry": ret = frappe.db.sql(""" select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2 where t1.name = t2.parent and t2.account = %(account)s and t2.party_type = %(party_type)s and t2.party = %(party)s and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order")) and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s and t1.docstatus=1 """.format(dr_or_cr = args.get("dr_or_cr")), args) else: party_account_field = ("paid_from" if erpnext.get_party_account_type(args.party_type) == 'Receivable' else "paid_to") if args.voucher_detail_no: ret = frappe.db.sql("""select t1.name from `tabPayment Entry` t1, `tabPayment Entry Reference` t2 where t1.name = t2.parent and t1.docstatus = 1 and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s and t2.reference_doctype in ("", "Sales Order", "Purchase Order") and t2.allocated_amount = %(unadjusted_amount)s """.format(party_account_field), args) else: ret = frappe.db.sql("""select name from `tabPayment Entry` where name = %(voucher_no)s and docstatus = 1 and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s and unallocated_amount = %(unadjusted_amount)s """.format(party_account_field), args) if not ret: throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
def get_outstanding_journal_entries(party_account, party_type, party): if erpnext.get_party_account_type(party_type) == "Receivable": bal_dr_or_cr = "gle_je.credit_in_account_currency - gle_je.debit_in_account_currency" payment_dr_or_cr = "gle_payment.debit_in_account_currency - gle_payment.credit_in_account_currency" else: bal_dr_or_cr = "gle_je.debit_in_account_currency - gle_je.credit_in_account_currency" payment_dr_or_cr = "gle_payment.credit_in_account_currency - gle_payment.debit_in_account_currency" return frappe.db.sql(""" select je.name, je.posting_date, je.user_remark, ifnull(sum({bal_dr_or_cr}), 0) - ( select ifnull(sum({payment_dr_or_cr}), 0) from `tabGL Entry` gle_payment where gle_payment.against_voucher_type = gle_je.voucher_type and gle_payment.against_voucher = gle_je.voucher_no and gle_payment.party_type = gle_je.party_type and gle_payment.party = gle_je.party and gle_payment.account = gle_je.account and abs({payment_dr_or_cr}) > 0 ) as balance from `tabGL Entry` gle_je inner join `tabJournal Entry` je on je.name = gle_je.voucher_no where gle_je.party_type = %(party_type)s and gle_je.party = %(party)s and gle_je.account = %(account)s and gle_je.voucher_type = 'Journal Entry' and (gle_je.against_voucher = '' or gle_je.against_voucher is null) and abs({bal_dr_or_cr}) > 0 group by gle_je.voucher_no having abs(balance) > 0.005 order by gle_je.posting_date""".format( bal_dr_or_cr=bal_dr_or_cr, payment_dr_or_cr=payment_dr_or_cr ), { "party_type": party_type, "party": party, "account": party_account }, as_dict=1)
def get_outstanding_invoices(party_type, party, account, condition=None, limit=1000): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency" invoice = 'Sales Invoice' if erpnext.get_party_account_type( party_type) == 'Receivable' else 'Purchase Invoice' held_invoices = get_held_invoices(party_type, party) limit_cond = "limit %s" % (limit or 1000) invoice_list = frappe.db.sql(""" select voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {dr_or_cr} > 0 {condition} and ((voucher_type = 'Journal Entry' and (against_voucher = '' or against_voucher is null)) or (voucher_type not in ('Journal Entry', 'Payment Entry'))) group by voucher_type, voucher_no order by posting_date, name {limit_cond}""".format(dr_or_cr=dr_or_cr, invoice=invoice, condition=condition or "", limit_cond=limit_cond), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) payment_entries = frappe.db.sql(""" select against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {payment_dr_or_cr} > 0 and against_voucher is not null and against_voucher != '' group by against_voucher_type, against_voucher """.format(payment_dr_or_cr=payment_dr_or_cr), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) pe_map = frappe._dict() for d in payment_entries: pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount) for d in invoice_list: payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0) outstanding_amount = flt(d.invoice_amount - payment_amount, precision) if outstanding_amount > 0.5 / (10**precision): if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: due_date = frappe.db.get_value( d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date") outstanding_invoices.append( frappe._dict({ 'voucher_no': d.voucher_no, 'voucher_type': d.voucher_type, 'posting_date': d.posting_date, 'invoice_amount': flt(d.invoice_amount), 'payment_amount': payment_amount, 'outstanding_amount': outstanding_amount, 'due_date': due_date })) outstanding_invoices = sorted( outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate())) return outstanding_invoices
def get_outstanding_invoices(party_type, party, account, condition=None): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency" invoice = 'Sales Invoice' if erpnext.get_party_account_type( party_type) == 'Receivable' else 'Purchase Invoice' held_invoices = get_held_invoices(party_type, party) invoice_list = frappe.db.sql(""" select voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount, ( select ifnull(sum({payment_dr_or_cr}), 0) from `tabGL Entry` payment_gl_entry where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type and if(invoice_gl_entry.voucher_type='Journal Entry', payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no, payment_gl_entry.against_voucher = invoice_gl_entry.against_voucher) and payment_gl_entry.party_type = invoice_gl_entry.party_type and payment_gl_entry.party = invoice_gl_entry.party and payment_gl_entry.account = invoice_gl_entry.account and {payment_dr_or_cr} > 0 ) as payment_amount from `tabGL Entry` invoice_gl_entry where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {dr_or_cr} > 0 {condition} and ((voucher_type = 'Journal Entry' and (against_voucher = '' or against_voucher is null)) or (voucher_type not in ('Journal Entry', 'Payment Entry'))) group by voucher_type, voucher_no having (invoice_amount - payment_amount) > 0.005 order by posting_date, name""".format(dr_or_cr=dr_or_cr, invoice=invoice, payment_dr_or_cr=payment_dr_or_cr, condition=condition or ""), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) for d in invoice_list: if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: due_date = frappe.db.get_value( d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date") outstanding_invoices.append( frappe._dict({ 'voucher_no': d.voucher_no, 'voucher_type': d.voucher_type, 'posting_date': d.posting_date, 'invoice_amount': flt(d.invoice_amount), 'payment_amount': flt(d.payment_amount), 'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision), 'due_date': due_date })) outstanding_invoices = sorted( outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate())) return outstanding_invoices
def get_outstanding_invoices(party_type, party, account, condition=None, filters=None): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 if account: root_type, account_type = frappe.get_cached_value( "Account", account, ["root_type", "account_type"] ) party_account_type = "Receivable" if root_type == "Asset" else "Payable" party_account_type = account_type or party_account_type else: party_account_type = erpnext.get_party_account_type(party_type) if party_account_type == "Receivable": dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency" held_invoices = get_held_invoices(party_type, party) invoice_list = frappe.db.sql( """ select voucher_no, voucher_type, posting_date, due_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount, account_currency as currency from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {dr_or_cr} > 0 and is_cancelled=0 {condition} and ((voucher_type = 'Journal Entry' and (against_voucher = '' or against_voucher is null)) or (voucher_type not in ('Journal Entry', 'Payment Entry'))) group by voucher_type, voucher_no order by posting_date, name""".format( dr_or_cr=dr_or_cr, condition=condition or "" ), { "party_type": party_type, "party": party, "account": account, }, as_dict=True, ) payment_entries = frappe.db.sql( """ select against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {payment_dr_or_cr} > 0 and against_voucher is not null and against_voucher != '' and is_cancelled=0 group by against_voucher_type, against_voucher """.format( payment_dr_or_cr=payment_dr_or_cr ), {"party_type": party_type, "party": party, "account": account}, as_dict=True, ) pe_map = frappe._dict() for d in payment_entries: pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount) for d in invoice_list: payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0) outstanding_amount = flt(d.invoice_amount - payment_amount, precision) if outstanding_amount > 0.5 / (10**precision): if ( filters and filters.get("outstanding_amt_greater_than") and not ( outstanding_amount >= filters.get("outstanding_amt_greater_than") and outstanding_amount <= filters.get("outstanding_amt_less_than") ) ): continue if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: outstanding_invoices.append( frappe._dict( { "voucher_no": d.voucher_no, "voucher_type": d.voucher_type, "posting_date": d.posting_date, "invoice_amount": flt(d.invoice_amount), "payment_amount": payment_amount, "outstanding_amount": outstanding_amount, "due_date": d.due_date, "currency": d.currency, } ) ) outstanding_invoices = sorted( outstanding_invoices, key=lambda k: k["due_date"] or getdate(nowdate()) ) return outstanding_invoices
def get_conditions(self, get_invoices=False, get_payments=False, get_return_invoices=False): condition = " and company = '{0}' ".format(self.company) if self.get("cost_center") and (get_invoices or get_payments or get_return_invoices): condition = " and cost_center = '{0}' ".format(self.cost_center) if get_invoices: condition += (" and posting_date >= {0}".format( frappe.db.escape(self.from_invoice_date)) if self.from_invoice_date else "") condition += (" and posting_date <= {0}".format( frappe.db.escape(self.to_invoice_date)) if self.to_invoice_date else "") dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == "Receivable" else "credit_in_account_currency") if self.minimum_invoice_amount: condition += " and {dr_or_cr} >= {amount}".format( dr_or_cr=dr_or_cr, amount=flt(self.minimum_invoice_amount)) if self.maximum_invoice_amount: condition += " and {dr_or_cr} <= {amount}".format( dr_or_cr=dr_or_cr, amount=flt(self.maximum_invoice_amount)) elif get_return_invoices: condition = " and doc.company = '{0}' ".format(self.company) condition += (" and doc.posting_date >= {0}".format( frappe.db.escape(self.from_payment_date)) if self.from_payment_date else "") condition += (" and doc.posting_date <= {0}".format( frappe.db.escape(self.to_payment_date)) if self.to_payment_date else "") dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == "Receivable" else "credit_in_account_currency") if self.minimum_invoice_amount: condition += " and gl.{dr_or_cr} >= {amount}".format( dr_or_cr=dr_or_cr, amount=flt(self.minimum_payment_amount)) if self.maximum_invoice_amount: condition += " and gl.{dr_or_cr} <= {amount}".format( dr_or_cr=dr_or_cr, amount=flt(self.maximum_payment_amount)) else: condition += (" and posting_date >= {0}".format( frappe.db.escape(self.from_payment_date)) if self.from_payment_date else "") condition += (" and posting_date <= {0}".format( frappe.db.escape(self.to_payment_date)) if self.to_payment_date else "") if self.minimum_payment_amount: condition += (" and unallocated_amount >= {0}".format( flt(self.minimum_payment_amount)) if get_payments else " and total_debit >= {0}".format( flt(self.minimum_payment_amount))) if self.maximum_payment_amount: condition += (" and unallocated_amount <= {0}".format( flt(self.maximum_payment_amount)) if get_payments else " and total_debit <= {0}".format( flt(self.maximum_payment_amount))) return condition
def get_advance_journal_entries(party_type, party, party_account, order_doctype, order_list=None, include_unallocated=True, against_all_orders=False, against_account=None, limit=None): journal_entries = [] if erpnext.get_party_account_type(party_type) == "Receivable": dr_or_cr = "credit_in_account_currency" bal_dr_or_cr = "gle_je.credit_in_account_currency - gle_je.debit_in_account_currency" payment_dr_or_cr = "gle_payment.debit_in_account_currency - gle_payment.credit_in_account_currency" else: dr_or_cr = "debit_in_account_currency" bal_dr_or_cr = "gle_je.debit_in_account_currency - gle_je.credit_in_account_currency" payment_dr_or_cr = "gle_payment.credit_in_account_currency - gle_payment.debit_in_account_currency" limit_cond = "limit %(limit)s" if limit else "" # JVs against order documents if order_list or against_all_orders: if order_list: order_condition = "and ifnull(jea.reference_name, '') in ('{0}')" \ .format("', '".join([frappe.db.escape(d) for d in order_list])) else: order_condition = "and ifnull(jea.reference_name, '') != ''" against_account_condition = "and jea.against_account like '%%{0}%%'".format(frappe.db.escape(against_account)) \ if against_account else "" journal_entries += frappe.db.sql(""" select "Journal Entry" as reference_type, je.name as reference_name, je.remark as remarks, jea.{dr_or_cr} as amount, jea.name as reference_row, jea.reference_name as against_order from `tabJournal Entry` je, `tabJournal Entry Account` jea where je.name = jea.parent and jea.account = %(account)s and jea.party_type = %(party_type)s and jea.party = %(party)s and {dr_or_cr} > 0 and jea.reference_type = '{order_doctype}' and je.docstatus = 1 {order_condition} {against_account_condition} order by je.posting_date {limit_cond}""".format( dr_or_cr=dr_or_cr, order_doctype=order_doctype, order_condition=order_condition, against_account_condition=against_account_condition, limit_cond=limit_cond ), { "party_type": party_type, "party": party, "account": party_account, "limit": limit }, as_dict=1) # Unallocated payment JVs if include_unallocated: against_account_condition = "" if against_account: against_account_condition = "and GROUP_CONCAT(gle_je.against) like '%%{0}%%'".format(frappe.db.escape(against_account)) journal_entries += frappe.db.sql(""" select gle_je.voucher_type as reference_type, je.name as reference_name, je.remark as remarks, ifnull(sum({bal_dr_or_cr}), 0) - ( select ifnull(sum({payment_dr_or_cr}), 0) from `tabGL Entry` gle_payment where gle_payment.against_voucher_type = gle_je.voucher_type and gle_payment.against_voucher = gle_je.voucher_no and gle_payment.party_type = gle_je.party_type and gle_payment.party = gle_je.party and gle_payment.account = gle_je.account and abs({payment_dr_or_cr}) > 0 ) as amount from `tabGL Entry` gle_je inner join `tabJournal Entry` je on je.name = gle_je.voucher_no where gle_je.party_type = %(party_type)s and gle_je.party = %(party)s and gle_je.account = %(account)s and gle_je.voucher_type = 'Journal Entry' and (gle_je.against_voucher = '' or gle_je.against_voucher is null) and abs({bal_dr_or_cr}) > 0 group by gle_je.voucher_no having amount > 0.005 {against_account_condition} order by gle_je.posting_date {limit_cond}""".format( bal_dr_or_cr=bal_dr_or_cr, payment_dr_or_cr=payment_dr_or_cr, against_account_condition=against_account_condition, limit_cond=limit_cond ), { "party_type": party_type, "party": party, "account": party_account, "limit": limit }, as_dict=True) return list(journal_entries)
def get_outstanding_invoices(party_type, party, account, condition=None, limit=None): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency" invoice = 'Sales Invoice' if erpnext.get_party_account_type(party_type) == 'Receivable' else 'Purchase Invoice' held_invoices = get_held_invoices(party_type, party) limit_cond = "limit %s" % limit if limit else "" invoice_list = frappe.db.sql(""" select voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {dr_or_cr} > 0 {condition} and ((voucher_type = 'Journal Entry' and (against_voucher = '' or against_voucher is null)) or (voucher_type not in ('Journal Entry', 'Payment Entry'))) group by voucher_type, voucher_no order by posting_date, name {limit_cond}""".format( dr_or_cr=dr_or_cr, invoice = invoice, condition=condition or "", limit_cond = limit_cond ), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) payment_entries = frappe.db.sql(""" select against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {payment_dr_or_cr} > 0 and against_voucher is not null and against_voucher != '' group by against_voucher_type, against_voucher """.format(payment_dr_or_cr=payment_dr_or_cr), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) pe_map = frappe._dict() for d in payment_entries: pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount) for d in invoice_list: payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0) outstanding_amount = flt(d.invoice_amount - payment_amount, precision) if outstanding_amount > 0.5 / (10**precision): if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: due_date = frappe.db.get_value( d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date") outstanding_invoices.append( frappe._dict({ 'voucher_no': d.voucher_no, 'voucher_type': d.voucher_type, 'posting_date': d.posting_date, 'invoice_amount': flt(d.invoice_amount), 'payment_amount': payment_amount, 'outstanding_amount': outstanding_amount, 'due_date': due_date }) ) outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate())) return outstanding_invoices
def check_if_advance_entry_modified(args): """ check if there is already a voucher reference check if amount is same check if jv is submitted """ ret = None if args.voucher_type == "Journal Entry": if args.voucher_detail_no: ret = frappe.db.sql( """select je.name from `tabJournal Entry` je, `tabJournal Entry Account` jea where je.name = jea.parent and jea.account = %(account)s and je.docstatus=1 and je.name = %(voucher_no)s and jea.name = %(voucher_detail_no)s and jea.party_type = %(party_type)s and jea.party = %(party)s and ifnull(jea.reference_type, '') in ('', 'Sales Order', 'Purchase Order', 'Employee Advance') and jea.{dr_or_cr} = %(unadjusted_amount)s""".format( dr_or_cr=args.dr_or_cr), args) else: if erpnext.get_party_account_type(args.party_type) == 'Receivable': dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "debit_in_account_currency - credit_in_account_currency" ret = frappe.db.sql( """ select sum({dr_or_cr}) as outstanding_amount from `tabGL Entry` where ((voucher_type='Journal Entry' and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher='')) or (against_voucher_type='Journal Entry' and against_voucher=%(voucher_no)s)) and party_type=%(party_type)s and party=%(party)s and account=%(account)s having outstanding_amount=%(unadjusted_amount)s""".format( dr_or_cr=dr_or_cr), args) else: party_account_field = ("paid_from" if erpnext.get_party_account_type( args.party_type) == 'Receivable' else "paid_to") if args.voucher_detail_no: ret = frappe.db.sql( """select pe.name from `tabPayment Entry` pe, `tabPayment Entry Reference` pref where pe.name = pref.parent and pe.docstatus = 1 and pe.name = %(voucher_no)s and pref.name = %(voucher_detail_no)s and pe.party_type = %(party_type)s and pe.party = %(party)s and pe.{0} = %(account)s and pref.reference_doctype in ('Sales Order', 'Purchase Order', 'Employee Advance') and pref.allocated_amount = %(unadjusted_amount)s """.format(party_account_field), args) else: ret = frappe.db.sql( """select name from `tabPayment Entry` where name = %(voucher_no)s and docstatus = 1 and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s and unallocated_amount = %(unadjusted_amount)s """.format(party_account_field), args) if not ret: throw( _("""Payment Entry has been modified after you pulled it. Please pull it again.""" ))
def get_outstanding_invoices(party_type, party, account, condition=None, include_negative_outstanding=False): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 if erpnext.get_party_account_type(party_type) == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency" held_invoices = get_held_invoices(party_type, party) invoice_list = frappe.db.sql(""" select voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and (against_voucher = '' or against_voucher is null) and voucher_type != 'Payment Entry' {condition} group by voucher_type, voucher_no order by posting_date, name """.format(dr_or_cr=dr_or_cr, condition=condition or ""), { "party_type": party_type, "party": party, "account": account }, as_dict=True) payment_entries = frappe.db.sql(""" select against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and against_voucher is not null and against_voucher != '' group by against_voucher_type, against_voucher """.format(payment_dr_or_cr=payment_dr_or_cr), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) pe_map = frappe._dict() for d in payment_entries: pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount) for d in invoice_list: payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0) outstanding_amount = flt(d.invoice_amount - payment_amount, precision) diff = abs(outstanding_amount ) if include_negative_outstanding else outstanding_amount if diff > 0.5 / (10**precision): if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: due_date = frappe.db.get_value( d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date") outstanding_invoices.append( frappe._dict({ 'voucher_no': d.voucher_no, 'voucher_type': d.voucher_type, 'posting_date': d.posting_date, 'invoice_amount': flt(d.invoice_amount), 'payment_amount': payment_amount, 'outstanding_amount': outstanding_amount, 'due_date': due_date })) outstanding_invoices = sorted( outstanding_invoices, key=lambda k: (k['outstanding_amount'] > 0, k['due_date'] or getdate(nowdate()))) return outstanding_invoices