def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency""" \
group_by_condition = "group by name"
if filters.get("group_by") == "Group by Voucher":
group_by_condition = "group by voucher_type, voucher_no, account, cost_center"
gl_entries = frappe.db.sql(
"""
select
posting_date, account, party_type, party,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition
),
filters, as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def set_gl_entries_by_account(
company, from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
gl_entries = frappe.db.sql("""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
and account in (select name from `tabAccount`
where lft >= %(lft)s and rgt <= %(rgt)s)
order by account, posting_date""".format(additional_conditions=additional_conditions),
{
"company": company,
"from_date": from_date,
"to_date": to_date,
"lft": root_lft,
"rgt": root_rgt
},
as_dict=True)
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account, creation"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("include_default_book_entries"):
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"), 'default_finance_book')
gl_entries = frappe.db.sql(
"""
select
name as gl_entry, posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
order_by_statement=order_by_statement
),
filters, as_dict=1)
#if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(select_fields=select_fields,
conditions=get_conditions(filters),
order_by_statement=order_by_statement),
filters,
as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def set_gl_entries_by_account(
company, from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
accounts = frappe.db.sql_list("""select name from `tabAccount`
where lft >= %s and rgt <= %s""", (root_lft, root_rgt))
additional_conditions += " and account in ('{}')"\
.format("', '".join([frappe.db.escape(d) for d in accounts]))
gl_entries = frappe.db.sql("""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
order by account, posting_date""".format(additional_conditions=additional_conditions),
{
"company": company,
"from_date": from_date,
"to_date": to_date,
"cost_center": filters.cost_center,
"project": filters.project,
"finance_book": filters.get("finance_book")
},
as_dict=True)
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def set_gl_entries_by_account(
company, from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
accounts = frappe.db.sql_list("""select name from `tabAccount`
where lft >= %s and rgt <= %s""", (root_lft, root_rgt))
additional_conditions += " and account in ('{}')"\
.format("', '".join([frappe.db.escape(d) for d in accounts]))
gl_entries = frappe.db.sql("""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
order by account, posting_date""".format(additional_conditions=additional_conditions),
{
"company": company,
"from_date": from_date,
"to_date": to_date,
"cost_center": filters.cost_center,
"project": filters.project,
"finance_book": filters.get("finance_book")
},
as_dict=True)
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account, creation"
if filters.get("order_by") == _("creation_desc"):
order_by_statement = "order by creation desc"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("include_default_book_entries"):
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"),
'default_finance_book')
# get data for customer_group w/o customer list
gl_entries_party_type = []
if filters.get("party_type") == 'Customer Group' and filters.get("party"):
gl_entries_party_type = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(select_fields=select_fields,
conditions=get_conditions(filters, party_type='Customer Group'),
order_by_statement=order_by_statement),
filters,
as_dict=1)
#normal flow
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(select_fields=select_fields,
conditions=get_conditions(filters),
order_by_statement=order_by_statement),
filters,
as_dict=1)
# if gl_entries_party_type has data append to normal flow
if gl_entries_party_type:
for d in gl_entries_party_type:
gl_entries.append(d)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
group_by_statement = ''
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == _("Group by Voucher (Consolidated)"):
group_by_statement = "group by voucher_type, voucher_no, account, cost_center"
select_fields = """, sum(debit) as debit, sum(credit) as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency"""
if filters.get("include_default_book_entries"):
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"),
'default_finance_book')
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions} {group_by_statement}
{order_by_statement}
""".format(select_fields=select_fields,
conditions=get_conditions(filters),
group_by_statement=group_by_statement,
order_by_statement=order_by_statement),
filters,
as_dict=1)
for gl_entry in gl_entries:
gl_entry.entry_type = ''
if gl_entry.voucher_type and gl_entry.voucher_no:
voucher = frappe.get_doc(gl_entry.voucher_type,
gl_entry.voucher_no)
if voucher:
if gl_entry.voucher_type == 'Journal Entry':
entry_type = voucher.voucher_type
if entry_type:
gl_entry.entry_type = entry_type
if gl_entry.voucher_type == 'Payment Entry':
entry_type = voucher.payment_type
if entry_type:
gl_entry.entry_type = entry_type
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def set_gl_entries_by_account(company,
from_date,
to_date,
root_lft,
root_rgt,
filters,
gl_entries_by_account,
ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = get_additional_conditions(from_date,
ignore_closing_entries,
filters)
accounts = frappe.db.sql_list(
"""select name from `tabAccount`
where lft >= %s and rgt <= %s and company = %s""",
(root_lft, root_rgt, company))
if accounts:
additional_conditions += " and account in ({})"\
.format(", ".join([frappe.db.escape(d) for d in accounts]))
gl_filters = {
"company": company,
"from_date": from_date,
"to_date": to_date,
"finance_book": cstr(filters.get("finance_book"))
}
if filters.get("include_default_book_entries"):
gl_filters["company_fb"] = frappe.db.get_value(
"Company", company, 'default_finance_book')
for key, value in filters.items():
if value:
gl_filters.update({key: value})
gl_entries = frappe.db.sql(
"""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
order by account, posting_date""".format(
additional_conditions=additional_conditions),
gl_filters,
as_dict=True) #nosec
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
group_by_statement = ''
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == _("Group by Voucher (Consolidated)"):
group_by_statement = "group by voucher_type, voucher_no, account, cost_center"
select_fields = """, sum(debit) as debit, sum(credit) as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency"""
gl_entries = frappe.db.sql(
"""
select
posting_date, account, party_type, party,
voucher_type, voucher_no,J_Cheque_No, J_Cheque_Date, ifnull(`tabGL Entry`.cost_center,JE.J_cost_center) as cost_center, ifnull(`tabGL Entry`.project,JE.J_Project) as project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
left join (select E.parent J_name,E.account as J_account,ifnull(E.project,J.project) J_Project,ifnull(E.cost_center,J.cost_center) J_cost_center, cheque_no J_Cheque_No, cheque_date J_Cheque_Date
from `tabJournal Entry Account` as E
left join `tabJournal Entry` as J
on J.name=E.parent
union
select P.name J_name,P.paid_to as J_account,P.project_new J_Project,P.cost_center J_cost_center, P.reference_no J_Cheque_No, P.reference_date J_Cheque_Date
from `tabPayment Entry` as P
union
select DP.name J_name,DP.paid_from as J_account,DP.project_new J_Project,DP.cost_center J_cost_center, DP.reference_no J_Cheque_No, DP.reference_date J_Cheque_Date
from `tabPayment Entry` as DP
) as JE
on JE.J_name = voucher_no
and JE.J_account =account
where company=%(company)s {conditions} {group_by_statement}
{order_by_statement}
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
group_by_statement=group_by_statement,
order_by_statement=order_by_statement
),
filters, as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def get_gl_entries(filters, accounting_dimensions):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account, creation"
if filters.get("include_dimensions"):
order_by_statement = "order by posting_date, creation"
if filters.get("group_by") == "Group by Voucher":
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == "Group by Account":
order_by_statement = "order by account, posting_date, creation"
if filters.get("include_default_book_entries"):
filters["company_fb"] = frappe.db.get_value("Company",
filters.get("company"),
"default_finance_book")
dimension_fields = ""
if accounting_dimensions:
dimension_fields = ", ".join(accounting_dimensions) + ","
gl_entries = frappe.db.sql(
"""
select
name as gl_entry, posting_date, account, party_type, party,
voucher_type, voucher_no, {dimension_fields}
cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening, creation {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(
dimension_fields=dimension_fields,
select_fields=select_fields,
conditions=get_conditions(filters),
order_by_statement=order_by_statement,
),
filters,
as_dict=1,
)
if filters.get("presentation_currency"):
return convert_to_presentation_currency(gl_entries, currency_map,
filters.get("company"))
else:
return gl_entries
def get_gl_entries(filters):
currency_map = get_currency(filters)
# frappe.throw("%s" % ('Khaled'))
select_fields = """, sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency""" \
group_by_condition = "group by voucher_type, voucher_no, account, cost_center" \
if filters.get("group_by_voucher") else "group by name"
gl_entries = frappe.db.sql(
"""
select
posting_date, account, party_type, party,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
(select code_jour from `tabSeries` where name = substring(voucher_no, 1, length(name)) limit 1) as num_jour,
(select sum(cast(right(e.name,5) as int))/count(e.name) from `tabGL Entry` e group by e.voucher_no
having e.voucher_no = g.voucher_no) as rank, DENSE_RANK() OVER (ORDER BY rank) as num_piece,
remarks, against, is_opening {select_fields}
from `tabGL Entry` g
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, creation, account
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition
),
filters, as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
group_by_statement = ''
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == _("Group by Voucher (Consolidated)"):
group_by_statement = "group by voucher_type, voucher_no, account, cost_center"
select_fields = """, sum(debit) as debit, sum(credit) as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency"""
gl_entries = frappe.db.sql(
"""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions} {group_by_statement}
{order_by_statement}
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
group_by_statement=group_by_statement,
order_by_statement=order_by_statement
),
filters, as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def get_gl_entries(filters):
currency_map = get_currency(filters)
filters.ledger_currency = currency_map.get(
"presentation_currency") or currency_map.get("company_currency")
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project, account_currency,
debit, credit, debit_in_account_currency, credit_in_account_currency,
remarks, against, is_opening, against_voucher_type, against_voucher, reference_no, reference_date,
%(ledger_currency)s as currency
from `tabGL Entry`
where company=%(company)s {conditions}
order by posting_date, voucher_type, voucher_no, account
""".format(conditions=get_conditions(filters)),
filters,
as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries
def get_gl_entries(filters):
if filters.get("presentation_currency"):
currency = filters["presentation_currency"]
else:
if filters.get("company"):
currency = get_company_currency(filters["company"])
else:
company = get_default_company()
currency = get_company_currency(company)
currency_map = get_currency(filters)
tempDict = []
tempVar = ""
oldVar= ""
temp = 1
temp1 = False
columns = []
tempColoumn = []
select_fields = """, (B.debit_in_account_currency) as debit_in_account_currency, (B.credit_in_account_currency) as credit_in_account_currency""" \
group_by_condition = " B.account, B.cost_center" \
if filters.get("group_by_voucher") else "group by E.name"
mydata = """select E.posting_date, E.is_opening as is_opening, B.is_advance as is_advance, E.title as party_name, E.bill_no as bill_no, E.company as company, E.voucher_type as voucher_type, E.owner as created_by_whom, E.modified_by as modified_by_whom, B.party_type as party_type, E.bill_date as bill_date, B.parent as voucher_no, B.account, B.party,(E.total_debit) as debit, B.credit as account_credit, B.debit as account_debit, (E.total_credit) as credit, B.cost_center, B.project,account_currency,E.remark, E.is_opening {select_fields} from `tabJournal Entry` E LEFT JOIN `tabJournal Entry Account` B ON B.parent = E.name where {conditions} order by E.posting_date, B.account """.format(select_fields=select_fields, conditions=get_conditions(filters))
gl_entries = frappe.db.sql(mydata,filters, as_dict=1)
if gl_entries:
for j in gl_entries:
if(temp == 1):
temp = 2
mykey = str(j['account'])
# tempColoumn = []
if (j['party_type'] != ''):
j['particulars'] = j['account']
j['party_type'] = j['party_type']
mykey = "gross"
j[mykey] = j['account_debit']
j['bill_no'] = j['bill_no']
j['credit'] = float("-"+str(j['credit']))
if (str(j['account_debit']) == '0.0'):
j[mykey] = "-"+str(j['account_credit'])
if (mykey =='gross'):
j[mykey] = str(j['account_credit'])
tempDict.append(j)
columnsObj = {}
if (mykey != 'gross'):
columnsObj['label'] = ""+mykey+""
columnsObj['fieldname'] = ""+mykey+""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if(checkDuplicate(columnsObj['fieldname'],tempColoumn)):
tempColoumn.append(columnsObj)
else:
if tempDict:
temp1 = True
for m in tempDict:
if(m['voucher_no'] == j['voucher_no']):
mykey = str(j['account'])
m[mykey] = j['account_debit']
m['bill_no'] = j['bill_no']
if (j['party_type'] != ''):
j['particulars'] = j['account']
j['party_type'] = j['party_type']
mykey = "gross"
if (str(j['account_debit']) == '0.0'):
m[mykey] = "-"+str(j['account_credit'])
if (mykey =='gross'):
j[mykey] = str(j['account_credit'])
columnsObj = {}
if (mykey != 'gross'):
columnsObj['label'] = ""+mykey+""
columnsObj['fieldname'] = ""+mykey+""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if(checkDuplicate(columnsObj['fieldname'],tempColoumn)):
tempColoumn.append(columnsObj)
temp1 = False
if temp1:
j['credit'] = float("-"+str(j['credit']))
if (j['party_type'] != ''):
j['particulars'] = j['account']
j['party_type'] = j['party_type']
mykey = "gross"
tempDict.append(j)
mykey = str(j['account'])
j[mykey] = j['account_debit']
if (str(j['account_debit']) == '0.0'):
j[mykey] = "-"+str(j['account_credit'])
if (mykey =='gross'):
j[mykey] = str(j['account_credit'])
columnsObj = {}
if (mykey != 'gross'):
columnsObj['label'] = ""+mykey+""
columnsObj['fieldname'] = ""+mykey+""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if(checkDuplicate(columnsObj['fieldname'],tempColoumn)):
tempColoumn.append(columnsObj)
# frappe.msgprint(len(tempColoumn))
gl_entries = tempDict
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries, tempColoumn
def get_gl_entries(filters, accounting_dimensions):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account, creation"
if filters.get("include_dimensions"):
order_by_statement = "order by posting_date, creation"
if filters.get("group_by") == "Group by Voucher":
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == "Group by Account":
order_by_statement = "order by account, posting_date, creation"
if filters.get("include_default_book_entries"):
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"),
'default_finance_book')
dimension_fields = ""
if accounting_dimensions:
dimension_fields = ', '.join(accounting_dimensions) + ','
distributed_cost_center_query = ""
if filters and filters.get('cost_center'):
select_fields_with_percentage = """, debit*(DCC_allocation.percentage_allocation/100) as debit,
credit*(DCC_allocation.percentage_allocation/100) as credit,
debit_in_account_currency*(DCC_allocation.percentage_allocation/100) as debit_in_account_currency,
credit_in_account_currency*(DCC_allocation.percentage_allocation/100) as credit_in_account_currency """
distributed_cost_center_query = """
UNION ALL
SELECT name as gl_entry,
posting_date,
account,
party_type,
party,
voucher_type,
voucher_no, {dimension_fields}
cost_center, project,
against_voucher_type,
against_voucher,
account_currency,
remarks, against,
is_opening, `tabGL Entry`.creation {select_fields_with_percentage}
FROM `tabGL Entry`,
(
SELECT parent, sum(percentage_allocation) as percentage_allocation
FROM `tabDistributed Cost Center`
WHERE cost_center IN %(cost_center)s
AND parent NOT IN %(cost_center)s
GROUP BY parent
) as DCC_allocation
WHERE company=%(company)s
{conditions}
AND posting_date <= %(to_date)s
AND cost_center = DCC_allocation.parent
""".format(dimension_fields=dimension_fields,
select_fields_with_percentage=select_fields_with_percentage,
conditions=get_conditions(filters).replace(
"and cost_center in %(cost_center)s ", ''))
gl_entries = frappe.db.sql("""
select
name as gl_entry, posting_date, account, party_type, party,
voucher_type, voucher_no, {dimension_fields}
cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening, creation {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{distributed_cost_center_query}
{order_by_statement}
""".format(dimension_fields=dimension_fields,
select_fields=select_fields,
conditions=get_conditions(filters),
distributed_cost_center_query=distributed_cost_center_query,
order_by_statement=order_by_statement),
filters,
as_dict=1)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map,
filters.get('company'))
else:
return gl_entries
def _get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency, credit_in_account_currency """
order_by_statement = "order by posting_date, account, creation"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("include_default_book_entries"):
filters["company_fb"] = frappe.db.get_value("Company",
filters.get("company"),
"default_finance_book")
gl_entries = frappe.db.sql(
"""
SELECT
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
FROM `tabGL Entry`
WHERE company=%(company)s
AND voucher_type != 'Journal Entry' {conditions}
{order_by_statement}
""".format(
select_fields=select_fields,
conditions=general_ledger.get_conditions(filters),
order_by_statement=order_by_statement,
),
filters,
as_dict=1,
)
je_order_by_statement = "order by je.posting_date, jea.account, jea.creation"
if filters.get("group_by") == _("Group by Voucher"):
je_order_by_statement = "order by je.posting_date, je.name"
je_conditions = _get_conditions(filters, True)
journal_entries = frappe.db.sql(
"""
SELECT
jea.name as gl_entry,
je.posting_date,
jea.account,
'Journal Entry' as voucher_type,
je.name as voucher_no,
jea.party_type,
jea.party,
jea.reference_type as against_voucher_type,
jea.reference_name as against_voucher,
jea.cost_center,
jea.project,
jea.account_currency,
jea.user_remark as remarks,
jea.against_account as against {select_fields}
FROM `tabJournal Entry Account` jea
INNER JOIN `tabJournal Entry` je ON jea.parent = je.name
WHERE je.company=%(company)s AND je.docstatus=1 {conditions}
{order_by_statement}
""".format(
select_fields=select_fields,
conditions=je_conditions,
order_by_statement=je_order_by_statement,
),
filters,
as_dict=1,
)
entries = [*gl_entries, *journal_entries]
if filters.get("presentation_currency"):
return convert_to_presentation_currency(entries, currency_map)
else:
return entries
def set_gl_entries_by_account(
company, from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
accounts = frappe.db.sql_list("""select name from `tabAccount`
where lft >= %s and rgt <= %s and company = %s""", (root_lft, root_rgt, company))
if accounts:
additional_conditions += " and account in ({})"\
.format(", ".join([frappe.db.escape(d) for d in accounts]))
gl_filters = {
"company": company,
"from_date": from_date,
"to_date": to_date,
"finance_book": cstr(filters.get("finance_book"))
}
if filters.get("include_default_book_entries"):
gl_filters["company_fb"] = frappe.db.get_value("Company",
company, 'default_finance_book')
for key, value in filters.items():
if value:
gl_filters.update({
key: value
})
distributed_cost_center_query = ""
if filters and filters.get('cost_center'):
distributed_cost_center_query = """
UNION ALL
SELECT posting_date,
account,
debit*(DCC_allocation.percentage_allocation/100) as debit,
credit*(DCC_allocation.percentage_allocation/100) as credit,
is_opening,
fiscal_year,
debit_in_account_currency*(DCC_allocation.percentage_allocation/100) as debit_in_account_currency,
credit_in_account_currency*(DCC_allocation.percentage_allocation/100) as credit_in_account_currency,
account_currency
FROM `tabGL Entry`,
(
SELECT parent, sum(percentage_allocation) as percentage_allocation
FROM `tabDistributed Cost Center`
WHERE cost_center IN %(cost_center)s
AND parent NOT IN %(cost_center)s
GROUP BY parent
) as DCC_allocation
WHERE company=%(company)s
{additional_conditions}
AND posting_date <= %(to_date)s
AND is_cancelled = 0
AND cost_center = DCC_allocation.parent
""".format(additional_conditions=additional_conditions.replace("and cost_center in %(cost_center)s ", ''))
gl_entries = frappe.db.sql("""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
and is_cancelled = 0
{distributed_cost_center_query}
order by account, posting_date""".format(
additional_conditions=additional_conditions,
distributed_cost_center_query=distributed_cost_center_query), gl_filters, as_dict=True) #nosec
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def set_gl_entries_by_account(company,
from_date,
to_date,
root_lft,
root_rgt,
filters,
gl_entries_by_account,
ignore_closing_entries=False):
"""
Organise the General Ledger by mapping the account name to all of its ledger entries.
Args:
company (string): The name of the company
from_date (datetime): starting date of the time period
to_date (datetime): ending date of the time period
root_lft (int): left branch of the the accounts tree
root_rgt (int): right branch of the accounts tree
filters (list): list of filters to be applied to the general ledger entries
gl_entries_by_account (list of dict): GL Entries to be processed
ignore_closing_entries (bool, optional): Defaults to False.
Returns:
dict: {"account": [gl entries], ... }
"""
additional_conditions = get_additional_conditions(from_date,
ignore_closing_entries,
filters)
#get a sublist of accounts/child accounts that fall within the tree branch between lft and rgt passed
accounts = frappe.db.sql_list(
"""select name from `tabAccount`
where lft >= %s and rgt <= %s and company = %s order by lft""",
(root_lft, root_rgt, company))
if accounts:
additional_conditions += " and account in ({})"\
.format(", ".join([frappe.db.escape(d) for d in accounts]))
gl_filters = {
"company": company,
"from_date": from_date,
"to_date": to_date,
"finance_book": cstr(filters.get("finance_book"))
}
if filters.get("include_default_book_entries"):
gl_filters["company_fb"] = frappe.db.get_value(
"Company", company, 'default_finance_book')
for key, value in filters.items():
if value:
gl_filters.update({key: value})
gl_entries = frappe.db.sql(
"""select posting_date, account, debit, credit, is_opening, fiscal_year,
debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
order by account, posting_date""".format(
additional_conditions=additional_conditions),
gl_filters,
as_dict=True) #nosec
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def get_gl_entries(filters):
if filters.get("presentation_currency"):
currency = filters["presentation_currency"]
else:
if filters.get("company"):
currency = get_company_currency(filters["company"])
else:
company = get_default_company()
currency = get_company_currency(company)
currency_map = get_currency(filters)
tempDict = []
tempVar = ""
oldVar = ""
temp = 1
temp1 = False
columns = []
tempColoumn = []
totalGross = 0.0
setParticulars = []
myVar = 0
select_fields = """, (B.debit_in_account_currency) as debit_in_account_currency, (B.credit_in_account_currency) as credit_in_account_currency""" \
group_by_condition = " B.account, B.cost_center" \
if filters.get("group_by_voucher") else "group by E.name"
mydata = """select E.posting_date, E.is_opening as is_opening, B.is_advance as is_advance, E.title, E.bill_no as bill_no, E.company as company, E.voucher_type as voucher_type, E.voucher_type as voucher_type_link, E.owner as created_by_whom, E.modified_by as modified_by_whom, B.party_type as party_type, E.bill_date as bill_date, B.parent as voucher_no, B.account, B.party as party_name,(E.total_debit) as debit, B.name as childId, B.credit as account_credit, B.debit as account_debit, (E.total_credit) as credit, B.cost_center, B.project,account_currency,E.remark, E.is_opening {select_fields} from `tabJournal Entry` E LEFT JOIN `tabJournal Entry Account` B ON B.parent = E.name where E.docstatus ='1' AND {conditions} order by E.posting_date, B.account """.format(
select_fields=select_fields, conditions=get_conditions(filters))
gl_entries = frappe.db.sql(mydata, filters, as_dict=1)
if gl_entries:
for j in gl_entries:
j['voucher_type_link'] = 'Journal Entry'
mykey = ''
setPriority = ''
totalGross = 0.0
if (temp == 1):
temp = 2
myVar = myVar + 1
j['particulars'] = ''
setPriority, totalGross, isDuplicate = setPriorityOfAccount(
j.voucher_no, setParticulars, tempColoumn)
if setPriority and isDuplicate:
setParticulars.append(setPriority)
if setPriority:
mykey = 'gross'
j['particulars'] = str(setPriority)
j['gross'] = str(totalGross)
if (str(j['account']) != str(setPriority)):
j['credit'] = float("-" + str(j['credit']))
mykey = str(j['account'])
j[mykey] = str(j['account_debit'])
if (str(j['account_credit']) != '0.0'):
j[mykey] = '-' + str(j['account_credit'])
j['bill_no'] = j['bill_no']
tempDict.append(j)
columnsObj = {}
if (mykey != 'gross' and mykey != ''):
columnsObj['label'] = "" + mykey + ""
columnsObj['fieldname'] = "" + mykey + ""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if (checkDuplicate(columnsObj['fieldname'], tempColoumn)):
tempColoumn.append(columnsObj)
else:
if tempDict:
temp1 = True
for m in tempDict:
if (m['voucher_no'] == j['voucher_no']):
mykey = ''
alredyExist = True
if m.has_key(str(j['account'])):
alredyExist = False
if (str(j['account_credit']) != '0.0'):
m[str(j['account'])] = float(m[str(
j['account'])]) - float(
j['account_credit'])
else:
m[str(j['account'])] = float(m[str(
j['account'])]) + float(
j['account_debit'])
if alredyExist:
if (str(m['particulars']) != str(
j['account'])):
mykey = str(j['account'])
if (str(j['account_debit']) != '0.0'):
m[mykey] = float(j['account_debit'])
else:
m[mykey] = '-' + str(
float(j['account_credit']))
columnsObj = {}
if (mykey != 'gross' and mykey != ''):
columnsObj['label'] = "" + mykey + ""
columnsObj['fieldname'] = "" + mykey + ""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if (checkDuplicate(columnsObj['fieldname'],
tempColoumn)):
tempColoumn.append(columnsObj)
temp1 = False
if temp1:
setPriority = ''
myVar = myVar + 1
j['particulars'] = ''
setPriority, totalGross, isDuplicate = setPriorityOfAccount(
j.voucher_no, setParticulars, tempColoumn)
if setPriority != None and str(
setPriority) != '' and isDuplicate == True:
setParticulars.append(setPriority)
if setPriority:
mykey = 'gross'
j['particulars'] = str(setPriority)
j['gross'] = str(totalGross)
if (str(j['account']) != str(setPriority)):
if float(j['credit']) > 0.0:
j['credit'] = float("-" + str(j['credit']))
mykey = str(j['account'])
j[mykey] = str(j['account_debit'])
if (str(j['account_credit']) != '0.0'):
j[mykey] = '-' + str(j['account_credit'])
j['bill_no'] = j['bill_no']
tempDict.append(j)
columnsObj = {}
if (mykey != 'gross' and mykey != ''):
columnsObj['label'] = "" + mykey + ""
columnsObj['fieldname'] = "" + mykey + ""
columnsObj['fieldtype'] = "Float"
columnsObj['width'] = 90
if columnsObj:
if (checkDuplicate(columnsObj['fieldname'],
tempColoumn)):
tempColoumn.append(columnsObj)
gl_entries = tempDict
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries, tempColoumn
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("include_default_book_entries"):
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"),
'default_finance_book')
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{order_by_statement}
""".format(select_fields=select_fields,
conditions=get_conditions(filters),
order_by_statement=order_by_statement),
filters,
as_dict=1)
converted_gl_list = []
company_currency = currency_map['company_currency']
for entry in gl_entries:
docktype = entry['voucher_type']
docname = entry['voucher_no']
entry_currency = entry['account_currency']
doc_currency = company_currency
items_list = ""
if docktype == "Payment Entry":
doc = frappe.db.sql("""
select
payment_type, paid_to_account_currency, paid_from_account_currency, received_amount, paid_amount
from `tabPayment Entry`
where name=%(docname)s
limit 1
""", {'docname': docname},
as_dict=1)
if doc[0].payment_type == "Receive":
doc_currency = doc[0].paid_to_account_currency
doc_amount = doc[0].received_amount
elif doc[0].payment_type == "Pay":
doc_currency = doc[0].paid_from_account_currency
doc_amount = doc[0].paid_amount
elif doc[0].payment_type == "Internal Transfer":
if entry.get('credit'):
doc_currency = doc[0].paid_from_account_currency
doc_amount = doc[0].paid_amount
else:
doc_currency = doc[0].paid_to_account_currency
doc_amount = doc[0].received_amount
elif docktype == "Sales Invoice":
if entry["party"]:
doc = frappe.db.sql("""
select
name, currency, grand_total ,rounded_total
from `tabSales Invoice`
where name=%(docname)s
limit 1
""", {'docname': entry['voucher_no']},
as_dict=1)
doc_currency = doc[0].currency
doc_amount = doc[0].rounded_total or doc[0].grand_total
items = frappe.db.sql("""
select
item_name, service_start_date, service_end_date
from `tabSales Invoice Item`
where parent=%(docname)s
""", {'docname': entry['voucher_no']},
as_dict=1)
for item in items:
items_list += "({0}".format(item.item_name)
if item.service_start_date:
items_list += " , {0} ".format(item.service_start_date)
if item.service_end_date:
items_list += ", {0}".format(item.service_end_date)
items_list += ") "
elif docktype == "Purchase Invoice":
if entry["party"]:
doc = frappe.db.sql("""
select
name, currency, grand_total, rounded_total
from `tabPurchase Invoice`
where name=%(docname)s
limit 1
""", {'docname': entry['voucher_no']},
as_dict=1)
doc_currency = doc[0].currency
doc_amount = doc[0].rounded_total or doc[0].grand_total
items = frappe.db.sql("""
select
item_name, service_start_date, service_end_date
from `tabPurchase Invoice Item`
where parent=%(docname)s
""", {'docname': entry['voucher_no']},
as_dict=1)
for item in items:
items_list += "({0}".format(item.item_name)
if item.service_start_date:
items_list += " , {0} ".format(item.service_start_date)
if item.service_end_date:
items_list += ", {0}".format(item.service_end_date)
items_list += ") "
elif docktype == "Journal Entry":
doc_currency = entry_currency
if entry.get('credit'):
doc_amount = entry['credit_in_account_currency']
else:
doc_amount = entry['debit_in_account_currency']
if company_currency != doc_currency:
if entry.get('debit'):
entry['debit_foreign'] = doc_amount
entry['exchange_rate'] = entry.get('debit') / doc_amount
if entry.get('credit'):
entry['credit_foreign'] = doc_amount
entry['exchange_rate'] = entry.get('credit') / doc_amount
entry['foreign_currency'] = doc_currency
entry['items'] = str(items_list) if items_list else ""
converted_gl_list.append(entry)
if filters.get('presentation_currency'):
return convert_to_presentation_currency(converted_gl_list,
currency_map)
else:
return converted_gl_list
def get_gl_entries(filters):
currency_map = get_currency(filters)
select_fields = """, debit, credit, debit_in_account_currency,
credit_in_account_currency """
# group_by_statement = ''
order_by_statement = "order by posting_date, account"
if filters.get("group_by") == _("Group by Voucher"):
order_by_statement = "order by posting_date, voucher_type, voucher_no"
if filters.get("group_by") == _("Group by Voucher (Consolidated)"):
# group_by_statement = "group by voucher_type, voucher_no, account, cost_center"
select_fields = """, sum(debit) as debit, sum(credit) as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency"""
gl_entries = frappe.db.sql(
"""
select
GL.posting_date, GL.account, GL.party_type, GL.party,
GL.voucher_type, GL.voucher_no,J_Cheque_No, J_Cheque_Date, ifnull(GL.cost_center,JE.J_cost_center) as cost_center, ifnull(GL.project,JE.J_Project) as project,
GL.against_voucher_type, GL.against_voucher, GL.account_currency,
GL.remarks, GL.against, GL.is_opening, GL.debit as debit, GL.credit as credit,
GL.debit_in_account_currency as debit_in_account_currency,
GL.credit_in_account_currency as credit_in_account_currency
from(
select
'' as finance_book,company,posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
remarks, against, is_opening, debit as debit, credit as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency
from `tabGL Entry` where voucher_type != 'Journal Entry'
group by voucher_type,voucher_no, account, cost_center
union all
select J.finance_book,J.company,J.posting_date,E.account,E.party_type,E.party,'Journal Entry',J.name,E.cost_center,
E.project,'','',E.account_currency,J.remark,E.against_account,J.is_opening, E.debit,E.credit,
E.debit_in_account_currency, E.credit_in_account_currency
from `tabJournal Entry Account` as E
left join `tabJournal Entry` as J
on J.name=E.parent) GL
left join
(
select E.parent J_name,E.account as J_account,ifnull(E.project,J.project) J_Project,ifnull(E.cost_center,J.cost_center) J_cost_center, cheque_no J_Cheque_No, cheque_date J_Cheque_Date
from `tabJournal Entry Account` as E
left join `tabJournal Entry` as J
on J.name=E.parent
union
select P.name J_name,P.paid_to as J_account,P.project_new J_Project,P.cost_center J_cost_center, P.reference_no J_Cheque_No, P.reference_date J_Cheque_Date
from `tabPayment Entry` as P
union
select DP.name J_name,DP.paid_from as J_account,DP.project_new J_Project,DP.cost_center J_cost_center, DP.reference_no J_Cheque_No, DP.reference_date J_Cheque_Date
from `tabPayment Entry` as DP
) as JE
on JE.J_name = GL.voucher_no
and JE.J_account =GL.account
where company=%(company)s {conditions}
{order_by_statement}
""".format(
select_fields=select_fields, conditions=get_conditions(filters),
order_by_statement=order_by_statement
),
filters, as_dict=1)
# frappe.msgprint(get_conditions(filters))
# frappe.msgprint(str(order_by_statement))
if filters.get('presentation_currency'):
return convert_to_presentation_currency(gl_entries, currency_map)
else:
return gl_entries