def execute(filters=None): general_ledger.get_gl_entries = _get_gl_entries col, res = general_ledger.execute(filters) # Add new column to the existing General Ledger # insert after [index] + 1 party_index = [i for i, d in enumerate(col) if 'party' in d.values()][0] col.insert(party_index + 1, { 'label': _('Party Name'), 'fieldname': 'party_name', 'width': 180 }) party_names = _get_party_names(res) for row in res: if row.get('party', None) in party_names: row['party_name'] = party_names[row.get('party')] return col, res
def execute(filters=None): general_ledger.get_gl_entries = _get_gl_entries col, res = general_ledger.execute(filters) # Add new column to the existing General Ledger # insert after [index] + 1 party_index = [i for i, d in enumerate(col) if "party" in d.values()][0] col.insert( party_index + 1, { "label": _("Party Name"), "fieldname": "party_name", "width": 180 }, ) party_names = _get_party_names(res) for row in res: if row.get("party", None) in party_names: row["party_name"] = party_names[row.get("party")] return col, res
def get(filters=None): timespan = filters.get("timespan") timegrain = filters.get("timegrain") account = filters.get("account") company = filters.get("company") from_date = get_from_date_from_timespan(timespan) to_date = nowdate() filters = frappe._dict({ "company": company, "from_date": from_date, "to_date": to_date, "account": account, "group_by": "Group by Voucher (Consolidated)" }) report_results = execute(filters=filters)[1] interesting_fields = ["posting_date", "balance"] _results = [] for row in report_results[1:-2]: _results.append([row[key] for key in interesting_fields]) _results = add_opening_balance(from_date, _results, report_results[0]) grouped_results = groupby(_results, key=itemgetter(0)) results = [list(values)[-1] for key, values in grouped_results] results = add_missing_dates(results, from_date, to_date) results = granulate_results(results, from_date, to_date, timegrain) return { "labels": [result[0] for result in results], "datasets": [{ "name": account, "values": [result[1] for result in results] }] }
def get_pending_so(**kwargs): try: data = { "items": [], "customer": "", "pending_bal": 0, "closing_bal": 0 } sa_data = {} customer = '' unpaid_dn_amt = 0 sa_total_amt = 0 sa_items = {} payment_entry = kwargs.get("payment_entry") customer = kwargs.get("customer") or \ kwargs.get("stock_allocation_party") if customer: # unpaid delivery note amount unpaid_dn_amt = frappe.db.sql(""" select sum( CASE when per_billed > 0 THEN grand_total - per_billed/100 ELSE grand_total END ) as unpaid_amt from `tabDelivery Note` where status in ('To Bill') and docstatus = 1 and customer = '{}' """.format(customer), as_dict=True) unpaid_dn_amt = unpaid_dn_amt[0]["unpaid_amt"] or 0 # warehouse warehouse = frappe.db.get_singles_value("MedTech Settings", "rm_warehouse") warehouse_cond = '' if warehouse: warehouse_cond += "and b.warehouse = '{}'".format(warehouse) else: warehouse_cond += "and soi.warehouse = b.warehouse" #filters for general ledger filters = frappe._dict({ "to_date": today(), "from_date": add_days(today(), -30), "company": frappe.defaults.get_user_default("Company"), "party": [customer], "party_type": "Customer", "group_by": "Group by Voucher (Consolidated)", "cost_center": [], "project": [] }) closing_bal = ledger_bal = pending_bal = 0 #general_ledger_report data for closing bal gl_data = execute(filters) data_ = gl_data[1] if len(data) else [] ledger_bal = data_[-1]["balance"] # gl data for closing balance filters.update({ "to_date": kwargs.get("posting_date"), "from_date": add_days(kwargs.get("posting_date"), -30) }) gl_data = execute(filters) if not payment_entry: pe_data = frappe.db.sql("""select name from `tabPayment Entry` where party = '{0}' and docstatus = 1 and posting_date = '{1}' order by creation desc limit 1 """.format(customer, kwargs.get("posting_date")), as_dict=True) if len(pe_data): payment_entry = pe_data[0]["name"] if payment_entry: for row in gl_data[1]: if row.get("voucher_no") == payment_entry: closing_bal = row.get("balance") query = """ select so.name, so.customer, so.workflow_state as status, so.transaction_date, soi.item_code, soi.item_name, (soi.qty - soi.delivered_qty) as qty, CASE WHEN (soi.rate_with_tax = 0) THEN soi.rate ELSE soi.rate_with_tax END as rate, soi.amount, b.actual_qty as stock_qty, 0 as carton_qty, 0 as revised_amt, 0 as approval, '' as remark from `tabSales Order` so left join `tabSales Order Item` soi on so.name = soi.parent left join `tabBin` b on soi.item_code = b.item_code {} where so.customer = '{}' and so.delivery_status in ('Not Delivered', 'Partly Delivered') and so.workflow_state = 'PI Pending' and so.docstatus = 1 """.format(warehouse_cond, customer) so_data = frappe.db.sql(query, as_dict=True) # existing stock allocation data merge if int(kwargs.get("fetch_existing", 0)): sa = frappe.db.get_value("Stock Allocation", { "customer": customer, "docstatus": 0 }) if sa: sa_doc = frappe.get_doc("Stock Allocation", sa) # localstorage seq: [0:qty, 1:rate, 2:amount, 3:sales_order, 4:is_approved, 5:remark] for row in sa_doc.items: sa_items[row.item_code] = [ row.qty, row.rate, row.amount, row.against_sales_order, row.is_approved, row.remarks ] # merge for r in so_data: if r.item_code in sa_items and \ r.name == sa_items[r.item_code][3]: r["carton_qty"] = sa_items[r.item_code][0] r["rate"] = sa_items[r.item_code][1] r["revised_amt"] = sa_items[r.item_code][2] r["approval"] = sa_items[r.item_code][4] r["remark"] = sa_items[r.item_code][5] sa_total_amt += sa_items[r.item_code][2] sa_data["total_amount"] = sa_total_amt data["sa_items"] = sa_items data["sa_total_amt"] = sa_total_amt data["items"] = so_data data["customer"] = customer data["pending_bal"] = (closing_bal * -1) - (unpaid_dn_amt + sa_total_amt) data["closing_bal"] = closing_bal * -1 data["ledger_bal"] = ledger_bal * -1 data["unpaid_dn_amt"] = unpaid_dn_amt return data except Exception as e: print("#################### Error:", str(e)) frappe.msgprint(_("Something went wrong, while fetching the data."))
def get_pending_so(**kwargs): try: data = { "items": [], "customer": "", "pending_bal": 0, "closing_bal": 0 } customer = '' unpaid_dn_amt = 0 if kwargs.get("stock_allocation_party"): customer = kwargs.get("stock_allocation_party") # unpaid delivery note amount unpaid_dn_amt = frappe.db.sql(""" select sum( CASE when per_billed > 0 THEN grand_total - per_billed/100 ELSE grand_total END ) as unpaid_amt from `tabDelivery Note` where status in ('To Bill') and docstatus = 1 and customer = '{}' """.format(customer), as_dict=True) unpaid_dn_amt = unpaid_dn_amt[0]["unpaid_amt"] or 0 # warehouse warehouse = frappe.db.get_singles_value("MedTech Settings", "rm_warehouse") warehouse_cond = '' if warehouse: warehouse_cond += "and b.warehouse = '{}'".format(warehouse) else: warehouse_cond += "and soi.warehouse = b.warehouse" #filters for general ledger filters = frappe._dict({ "to_date": today(), "from_date": add_days(today(), -30), "company": frappe.defaults.get_user_default("Company"), "party": [customer], "party_type": "Customer", "group_by": "Group by Voucher (Consolidated)", "cost_center": [], "project": [] }) closing_bal = ledger_bal = pending_bal = 0 #general_ledger_report data for closing bal gl_data = execute(filters) data_ = gl_data[1] if len(data) else [] ledger_bal = data_[-1]["balance"] # gl data for closing balance filters.update({ "to_date": kwargs.get("posting_date"), "from_date": add_days(kwargs.get("posting_date"), -30) }) gl_data = execute(filters) for row in gl_data[1]: if row.get("voucher_no") == kwargs.get("payment_entry"): closing_bal = row.get("balance") query = """ select so.name, so.customer, so.status, so.transaction_date, soi.item_code, soi.item_name, (soi.qty - soi.delivered_qty) as qty, soi.rate, soi.amount, b.actual_qty as stock_qty from `tabSales Order` so left join `tabSales Order Item` soi on so.name = soi.parent left join `tabBin` b on soi.item_code = b.item_code {} where so.customer = '{}' and so.delivery_status in ('Not Delivered', 'Partly Delivered') and so.docstatus = 1""".format(warehouse_cond, customer) so_data = frappe.db.sql(query, as_dict=True) data["items"] = so_data data["customer"] = customer data["pending_bal"] = (closing_bal * -1) - unpaid_dn_amt data["closing_bal"] = closing_bal * -1 data["ledger_bal"] = ledger_bal * -1 data["unpaid_dn_amt"] = unpaid_dn_amt return data except Exception as e: print("#################### Error:", str(e)) frappe.msgprint(_("Something went wrong, while fetching the data."))
def test_foreign_account_balance_after_exchange_rate_revaluation(self): """ Checks the correctness of balance after exchange rate revaluation """ # create a new account with USD currency account_name = "Test USD Account for Revalutation" company = "_Test Company" account = frappe.get_doc({ "account_name": account_name, "is_group": 0, "company": company, "root_type": "Asset", "report_type": "Balance Sheet", "account_currency": "USD", "inter_company_account": 0, "parent_account": "Bank Accounts - _TC", "account_type": "Bank", "doctype": "Account", }) account.insert(ignore_if_duplicate=True) # create a JV to debit 1000 USD at 75 exchange rate jv = frappe.new_doc("Journal Entry") jv.posting_date = today() jv.company = company jv.multi_currency = 1 jv.cost_center = "_Test Cost Center - _TC" jv.set( "accounts", [ { "account": account.name, "debit_in_account_currency": 1000, "credit_in_account_currency": 0, "exchange_rate": 75, "cost_center": "_Test Cost Center - _TC", }, { "account": "Cash - _TC", "debit_in_account_currency": 0, "credit_in_account_currency": 75000, "cost_center": "_Test Cost Center - _TC", }, ], ) jv.save() jv.submit() # create a JV to credit 900 USD at 100 exchange rate jv = frappe.new_doc("Journal Entry") jv.posting_date = today() jv.company = company jv.multi_currency = 1 jv.cost_center = "_Test Cost Center - _TC" jv.set( "accounts", [ { "account": account.name, "debit_in_account_currency": 0, "credit_in_account_currency": 900, "exchange_rate": 100, "cost_center": "_Test Cost Center - _TC", }, { "account": "Cash - _TC", "debit_in_account_currency": 90000, "credit_in_account_currency": 0, "cost_center": "_Test Cost Center - _TC", }, ], ) jv.save() jv.submit() # create an exchange rate revaluation entry at 77 exchange rate revaluation = frappe.new_doc("Exchange Rate Revaluation") revaluation.posting_date = today() revaluation.company = company revaluation.set( "accounts", [{ "account": account.name, "account_currency": "USD", "new_exchange_rate": 77, "new_balance_in_base_currency": 7700, "balance_in_base_currency": -15000, "balance_in_account_currency": 100, "current_exchange_rate": -150, }], ) revaluation.save() revaluation.submit() # post journal entry to revaluate frappe.db.set_value("Company", company, "unrealized_exchange_gain_loss_account", "_Test Exchange Gain/Loss - _TC") revaluation_jv = revaluation.make_jv_entry() revaluation_jv = frappe.get_doc(revaluation_jv) revaluation_jv.cost_center = "_Test Cost Center - _TC" for acc in revaluation_jv.get("accounts"): acc.cost_center = "_Test Cost Center - _TC" revaluation_jv.save() revaluation_jv.submit() # check the balance of the account balance = frappe.db.sql( """ select sum(debit_in_account_currency) - sum(credit_in_account_currency) from `tabGL Entry` where account = %s group by account """, account.name, ) self.assertEqual(balance[0][0], 100) # check if general ledger shows correct balance columns, data = execute( frappe._dict({ "company": company, "from_date": today(), "to_date": today(), "account": [account.name], "group_by": "Group by Voucher (Consolidated)", })) self.assertEqual(data[1]["account"], account.name) self.assertEqual(data[1]["debit"], 1000) self.assertEqual(data[1]["credit"], 0) self.assertEqual(data[2]["debit"], 0) self.assertEqual(data[2]["credit"], 900) self.assertEqual(data[3]["debit"], 100) self.assertEqual(data[3]["credit"], 100)