def get_outstanding_reference_documents(args): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total" negative_outstanding_invoices = get_negative_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), total_field) # Get positive outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account")) for d in outstanding_invoices: d["exchange_rate"] = 1 if party_account_currency != company_currency \ and d.voucher_type in ("Sales Invoice", "Purchase Invoice"): d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate") # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"), party_account_currency, company_currency) return negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def populate_matching_invoices(self): self.payment_invoice_items = [] self.map_unknown_transactions() added_invoices = [] for entry in self.new_transaction_items: if (not entry.party or entry.party_type == "Account"): continue account = self.receivable_account if entry.party_type == "Customer" else self.payable_account invoices = get_outstanding_invoices(entry.party_type, entry.party, account) transaction_date = datetime.strptime(entry.transaction_date, "%Y-%m-%d").date() outstanding_invoices = [invoice for invoice in invoices if invoice.posting_date <= transaction_date] amount = abs(entry.amount) matching_invoices = [invoice for invoice in outstanding_invoices if invoice.outstanding_amount == amount] sorted(outstanding_invoices, key=lambda k: k['posting_date']) for e in (matching_invoices + outstanding_invoices): added = next((inv for inv in added_invoices if inv == e.get('voucher_no')), None) if (added is not None): continue ent = self.append('payment_invoice_items', {}) ent.transaction_date = entry.transaction_date ent.payment_description = frappe.safe_decode(entry.description) ent.party_type = entry.party_type ent.party = entry.party ent.invoice = e.get('voucher_no') added_invoices += [ent.invoice] ent.invoice_type = "Sales Invoice" if entry.party_type == "Customer" else "Purchase Invoice" ent.invoice_date = e.get('posting_date') ent.outstanding_amount = e.get('outstanding_amount') ent.allocated_amount = min(float(e.get('outstanding_amount')), amount) amount -= float(e.get('outstanding_amount')) if (amount <= 5): break self.match_invoice_to_payment() self.populate_matching_vouchers() self.map_transactions_on_journal_entry()
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 get_outstanding_vouchers(args): from erpnext.accounts.utils import get_outstanding_invoices if not frappe.has_permission("Payment Tool"): frappe.throw(_("No permission to use Payment Tool"), frappe.PermissionError) args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") if ((args.get("party_type") == "Customer" and args.get("received_or_paid") == "Paid") or (args.get("party_type") == "Supplier" and args.get("received_or_paid") == "Received")): frappe.throw(_("Please enter the Against Vouchers manually")) # Get all outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account")) # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"), party_account_currency, company_currency) return outstanding_invoices + orders_to_be_billed
def get_outstanding_vouchers(args): from erpnext.accounts.utils import get_outstanding_invoices if not frappe.has_permission("Payment Tool"): frappe.throw(_("No permission to use Payment Tool"), frappe.PermissionError) args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") if (args.get("party_type") == "Customer" and args.get("received_or_paid") == "Paid") or ( args.get("party_type") == "Supplier" and args.get("received_or_paid") == "Received" ): frappe.throw(_("Please enter the Against Vouchers manually")) # Get all outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account") ) # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed( args.get("party_type"), args.get("party"), party_account_currency, company_currency ) return outstanding_invoices + orders_to_be_billed
def get_outstanding_reference_documents(args): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total" negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), total_field) # Get positive outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account")) for d in outstanding_invoices: d["exchange_rate"] = 1 if party_account_currency != company_currency \ and d.voucher_type in ("Sales Invoice", "Purchase Invoice"): d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate") # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"), party_account_currency, company_currency) return negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_outstanding_vouchers(args): from erpnext.accounts.utils import get_outstanding_invoices if not frappe.has_permission("Payment Tool"): frappe.throw(_("No permission to use Payment Tool"), frappe.PermissionError) args = json.loads(args) if args.get("party_type") == "Customer" and args.get( "received_or_paid") == "Received": amount_query = "ifnull(debit, 0) - ifnull(credit, 0)" elif args.get("party_type") == "Supplier" and args.get( "received_or_paid") == "Paid": amount_query = "ifnull(credit, 0) - ifnull(debit, 0)" else: frappe.throw(_("Please enter the Against Vouchers manually")) # Get all outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices(amount_query, args.get("party_account"), args.get("party_type"), args.get("party")) # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party")) return outstanding_invoices + orders_to_be_billed
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) if args.get('party_type') == 'Member': return # 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 negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") in ("Supplier", "Customer") and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees 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") outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition) 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_exchange_rate( party_account_currency, company_currency, d.posting_date ) 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 (args.get("party_type") in ("Supplier", "Customer")): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) if args.get('party_type') == 'Member': return # 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 negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee"] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees 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") outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition) 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_exchange_rate( party_account_currency, company_currency, d.posting_date ) 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 (args.get("party_type") != "Student"): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_invoice_entries(self): #Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against condition = self.check_condition() non_reconciled_invoices = get_outstanding_invoices(self.party_type, self.party, self.receivable_payable_account, condition=condition, limit=self.limit) self.add_invoice_entries(non_reconciled_invoices)
def get_invoice_entries(self): #Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against condition = self.check_condition() non_reconciled_invoices = get_outstanding_invoices(self.party_type, self.party, self.receivable_payable_account, condition=condition) self.add_invoice_entries(non_reconciled_invoices)
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee" ] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees 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"])) outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition) 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_exchange_rate(party_account_currency, company_currency, d.posting_date) 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 (args.get("party_type") != "Student"): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_invoice_entries(self): #Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against condition = self.get_conditions(get_invoices=True) non_reconciled_invoices = get_outstanding_invoices( self.party_type, self.party, self.receivable_payable_account, condition=condition) if self.invoice_limit: non_reconciled_invoices = non_reconciled_invoices[:self. invoice_limit] self.add_invoice_entries(non_reconciled_invoices)
def get_outstanding_reference_documents(args): if isinstance(args, basestring): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total" negative_outstanding_invoices = [] if (args.get("party_type") != "Student"): negative_outstanding_invoices = get_negative_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), total_field) # Get positive outstanding sales /purchase invoices/ Fees outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account")) 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_exchange_rate(party_account_currency, company_currency, d.posting_date) 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 (args.get("party_type") != "Student"): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_invoice_entries(self): #Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against condition = self.check_condition() non_reconciled_invoices = [] if self.party_type == 'Customer Group' and self.party: lft, rgt = frappe.db.get_value("Customer Group", self.party, ['lft', 'rgt']) get_parent_customer_groups = frappe.db.sql( """select name from `tabCustomer Group` where lft >= %s and rgt <= %s""", (lft, rgt), as_dict=1) customer_groups = [ "%s" % (frappe.db.escape(d.name)) for d in get_parent_customer_groups ] if customer_groups: cond = "and 1=1" customer_group_condition = ",".join( ['%s'] * len(customer_groups)) % (tuple(customer_groups)) condition_customer = "{0} in ({1})".format( ' and customer_group', customer_group_condition) cond += condition_customer customer_list = frappe.db.sql( """ select name from `tabCustomer` where docstatus < 2 {cond} """ .format(cond=cond), as_list=1) if len(customer_list) > 0: # frappe._dict({'party': customer[0]}) for customer in customer_list: outstanding_invoices = get_outstanding_invoices( "Customer", customer, self.receivable_payable_account, condition=condition) for invoice in outstanding_invoices: invoice.update({"party": customer[0]}) non_reconciled_invoices += outstanding_invoices if self.limit: non_reconciled_invoices = non_reconciled_invoices[:self. limit] self.add_invoice_entries(non_reconciled_invoices)
def get_outstanding_reference_documents(args): if isinstance(args, basestring): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee"] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees 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"])) outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition) 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_exchange_rate( party_account_currency, company_currency, d.posting_date ) 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 (args.get("party_type") != "Student"): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_outstanding_vouchers(args): from erpnext.accounts.utils import get_outstanding_invoices if not frappe.has_permission("Payment Tool"): frappe.throw(_("No permission to use Payment Tool"), frappe.PermissionError) args = json.loads(args) if args.get("party_type") == "Customer" and args.get("received_or_paid") == "Received": amount_query = "ifnull(debit, 0) - ifnull(credit, 0)" elif args.get("party_type") == "Supplier" and args.get("received_or_paid") == "Paid": amount_query = "ifnull(credit, 0) - ifnull(debit, 0)" else: frappe.throw(_("Please enter the Against Vouchers manually")) # Get all outstanding sales /purchase invoices outstanding_invoices = get_outstanding_invoices(amount_query, args.get("party_account")) # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party_name")) return outstanding_invoices + orders_to_be_billed
def get_outstanding_reference_documents(args): if isinstance(args, basestring): args = json.loads(args) party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total" negative_outstanding_invoices = [] if (args.get("party_type") != "Student"): negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), total_field) # Get positive outstanding sales /purchase invoices/ Fees outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account")) 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_exchange_rate( party_account_currency, company_currency, d.posting_date ) 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 (args.get("party_type") != "Student"): 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 negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) if args.get("party_type") == "Member": return # 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 positive outstanding sales /purchase invoices/ Fees 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 # Remarked MPC_TZ 2021-11-12 05:30:32 to avoid cost center filter # if args.get("cost_center"): # condition += " and cost_center='%s'" % args.get("cost_center") date_fields_dict = { "posting_date": ["from_posting_date", "to_posting_date"], "due_date": ["from_due_date", "to_due_date"], } for fieldname, date_fields in date_fields_dict.items(): if args.get(date_fields[0]) and args.get(date_fields[1]): condition += " and {0} between '{1}' and '{2}'".format( fieldname, args.get(date_fields[0]), args.get(date_fields[1])) if args.get("company"): condition += " and company = {0}".format( frappe.db.escape(args.get("company"))) outstanding_invoices = get_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), filters=args, condition=condition, ) 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_exchange_rate(party_account_currency, company_currency, d.posting_date) 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 = [] # Get negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee" ] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), args.get("company"), party_account_currency, company_currency, condition=condition) if args.get("party_type") != "Student": orders_to_be_billed = get_orders_to_be_billed( args.get("posting_date"), args.get("party_type"), args.get("party"), args.get("company"), party_account_currency, company_currency, filters=args, ) data = negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed if not data: frappe.msgprint( _("No outstanding invoices found for the {0} {1} which qualify the filters you have specified." ).format( args.get("party_type").lower(), frappe.bold(args.get("party"))), alert=True, ) return data
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) if args.get('party_type') == 'Member': return args['party_account'] = frappe.db.get_value('Account', {'account_type': 'Payable','is_group': 0, 'company': args.get('company')}) # 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 negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee"] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), args.get("company"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees 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"): condition += " and cost_center='%s'" % args.get("cost_center") date_fields_dict = { 'posting_date': ['from_posting_date', 'to_posting_date'], 'due_date': ['from_due_date', 'to_due_date'] } for fieldname, date_fields in date_fields_dict.items(): if args.get(date_fields[0]) and args.get(date_fields[1]): condition += " and {0} between '{1}' and '{2}'".format(fieldname, args.get(date_fields[0]), args.get(date_fields[1])) if args.get("company"): condition += " and company = {0}".format(frappe.db.escape(args.get("company"))) outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), filters=args, condition=condition) 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_exchange_rate( party_account_currency, company_currency, d.posting_date ) 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 (args.get("party_type") != "Student"): orders_to_be_billed = get_orders_to_be_billed(args.get("posting_date"),args.get("party_type"), args.get("party"), args.get("company"), party_account_currency, company_currency, filters=args) data = negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed if not data: frappe.msgprint(_("No outstanding invoices found for the {0} {1} which qualify the filters you have specified.") .format(args.get("party_type").lower(), frappe.bold(args.get("party")))) return data