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)