def _get_account_type_based_data(filters,
account_names,
period_list,
accumulated_values,
opening_balances=0):
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
if opening_balances:
date_info = dict(date=start_date)
months_map = {'Monthly': -1, 'Quarterly': -3, 'Half-Yearly': -6}
years_map = {'Yearly': -1}
if months_map.get(filters.periodicity):
date_info.update(months=months_map[filters.periodicity])
else:
date_info.update(years=years_map[filters.periodicity])
if accumulated_values:
start, end = add_to_date(start_date, years=-1), add_to_date(
period['to_date'], years=-1)
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
gl_sum = frappe.db.sql_list(
"""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
and account in ( SELECT name FROM tabAccount WHERE name IN %s
OR parent_account IN %s)
""", (company, start, end, account_names, account_names))
else:
gl_sum = frappe.db.sql_list(
"""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
and account in ( SELECT name FROM tabAccount WHERE name IN %s
OR parent_account IN %s)
""", (company, start_date if accumulated_values else period['from_date'],
period['to_date'], account_names, account_names))
if gl_sum and gl_sum[0]:
amount = gl_sum[0]
else:
amount = 0
total += amount
data.setdefault(period["key"], amount)
data["total"] = total
return data
def _get_account_type_based_data(filters, account_names, period_list, accumulated_values, opening_balances=0):
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
accounts = ', '.join(['"%s"' % d for d in account_names])
if opening_balances:
date_info = dict(date=start_date)
months_map = {'Monthly': -1, 'Quarterly': -3, 'Half-Yearly': -6}
years_map = {'Yearly': -1}
if months_map.get(filters.periodicity):
date_info.update(months=months_map[filters.periodicity])
else:
date_info.update(years=years_map[filters.periodicity])
if accumulated_values:
start, end = add_to_date(start_date, years=-1), add_to_date(period['to_date'], years=-1)
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
gl_sum = frappe.db.sql_list("""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
and account in ( SELECT name FROM tabAccount WHERE name IN (%s)
OR parent_account IN (%s))
""", (company, start, end, accounts, accounts))
else:
gl_sum = frappe.db.sql_list("""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
and account in ( SELECT name FROM tabAccount WHERE name IN (%s)
OR parent_account IN (%s))
""", (company, start_date if accumulated_values else period['from_date'],
period['to_date'], accounts, accounts))
if gl_sum and gl_sum[0]:
amount = gl_sum[0]
else:
amount = 0
total += amount
data.setdefault(period["key"], amount)
data["total"] = total
return data
def _get_account_type_based_data(
filters, account_names, period_list, accumulated_values, opening_balances=0
):
if not account_names or not account_names[0] or not type(account_names[0]) == str:
# only proceed if account_names is a list of account names
return {}
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
GLEntry = frappe.qb.DocType("GL Entry")
Account = frappe.qb.DocType("Account")
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
account_subquery = (
frappe.qb.from_(Account)
.where((Account.name.isin(account_names)) | (Account.parent_account.isin(account_names)))
.select(Account.name)
.as_("account_subquery")
)
if opening_balances:
date_info = dict(date=start_date)
months_map = {"Monthly": -1, "Quarterly": -3, "Half-Yearly": -6}
years_map = {"Yearly": -1}
if months_map.get(filters.periodicity):
date_info.update(months=months_map[filters.periodicity])
else:
date_info.update(years=years_map[filters.periodicity])
if accumulated_values:
start, end = add_to_date(start_date, years=-1), add_to_date(period["to_date"], years=-1)
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
start, end = get_date_str(start), get_date_str(end)
else:
start, end = start_date if accumulated_values else period["from_date"], period["to_date"]
start, end = get_date_str(start), get_date_str(end)
result = (
frappe.qb.from_(GLEntry)
.select(Sum(GLEntry.credit) - Sum(GLEntry.debit))
.where(
(GLEntry.company == company)
& (GLEntry.posting_date >= start)
& (GLEntry.posting_date <= end)
& (GLEntry.voucher_type != "Period Closing Voucher")
& (GLEntry.account.isin(account_subquery))
)
).run()
if result and result[0]:
gl_sum = result[0][0]
else:
gl_sum = 0
total += flt(gl_sum)
data.setdefault(period["key"], flt(gl_sum))
data["total"] = total
return data