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 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 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 set_gl_entries_by_account(from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, accounts_by_name, ignore_closing_entries=False): """Returns a dict like { "account": [gl entries], ... }""" company_lft, company_rgt = frappe.get_cached_value('Company', filters.get('company'), ["lft", "rgt"]) additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters) companies = frappe.db.sql(""" select name, default_currency from `tabCompany` where lft >= %(company_lft)s and rgt <= %(company_rgt)s""", { "company_lft": company_lft, "company_rgt": company_rgt, }, as_dict=1) currency_info = frappe._dict({ 'report_date': to_date, 'presentation_currency': filters.get('presentation_currency') }) for d in companies: gl_entries = frappe.db.sql("""select gl.posting_date, gl.account, gl.debit, gl.credit, gl.is_opening, gl.company, gl.fiscal_year, gl.debit_in_account_currency, gl.credit_in_account_currency, gl.account_currency, acc.account_name, acc.account_number from `tabGL Entry` gl, `tabAccount` acc where acc.name = gl.account and gl.company = %(company)s {additional_conditions} and gl.posting_date <= %(to_date)s and acc.lft >= %(lft)s and acc.rgt <= %(rgt)s order by gl.account, gl.posting_date""".format(additional_conditions=additional_conditions), { "from_date": from_date, "to_date": to_date, "lft": root_lft, "rgt": root_rgt, "company": d.name, "finance_book": filters.get("finance_book"), "company_fb": frappe.db.get_value("Company", d.name, 'default_finance_book') }, as_dict=True) if filters and filters.get('presentation_currency') != d.default_currency: currency_info['company'] = d.name currency_info['company_currency'] = d.default_currency convert_to_presentation_currency(gl_entries, currency_info) for entry in gl_entries: key = entry.account_number or entry.account_name validate_entries(key, entry, accounts_by_name) gl_entries_by_account.setdefault(key, []).append(entry) return gl_entries_by_account
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 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 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 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 set_gl_entries_by_account(from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account, accounts_by_name, ignore_closing_entries=False): """Returns a dict like { "account": [gl entries], ... }""" company_lft, company_rgt = frappe.get_cached_value('Company', filters.get('company'), ["lft", "rgt"]) additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters) companies = frappe.db.sql(""" select name, default_currency from `tabCompany` where lft >= %(company_lft)s and rgt <= %(company_rgt)s""", { "company_lft": company_lft, "company_rgt": company_rgt, }, as_dict=1) currency_info = frappe._dict({ 'report_date': to_date, 'presentation_currency': filters.get('presentation_currency') }) for d in companies: gl_entries = frappe.db.sql("""select gl.posting_date, gl.account, gl.debit, gl.credit, gl.is_opening, gl.company, gl.fiscal_year, gl.debit_in_account_currency, gl.credit_in_account_currency, gl.account_currency, acc.account_name, acc.account_number from `tabGL Entry` gl, `tabAccount` acc where acc.name = gl.account and gl.company = %(company)s {additional_conditions} and gl.posting_date <= %(to_date)s and acc.lft >= %(lft)s and acc.rgt <= %(rgt)s order by gl.account, gl.posting_date""".format(additional_conditions=additional_conditions), { "from_date": from_date, "to_date": to_date, "lft": root_lft, "rgt": root_rgt, "company": d.name }, as_dict=True) if filters and filters.get('presentation_currency') != d.default_currency: currency_info['company'] = d.name currency_info['company_currency'] = d.default_currency convert_to_presentation_currency(gl_entries, currency_info) for entry in gl_entries: key = entry.account_number or entry.account_name validate_entries(key, entry, accounts_by_name) gl_entries_by_account.setdefault(key, []).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): 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): 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 """ # 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
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