def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) account_supplier = dict( webnotes.conn.sql("""select account.name, supplier.supplier_name from `tabAccount` account, `tabSupplier` supplier where account.master_type="Supplier" and supplier.name=account.master_name""" )) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount( gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [ gle.posting_date, gle.account, account_supplier.get(gle.account, ""), gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_entries(filters) invoice_posting_date_map = get_invoice_posting_date_map(filters) against_date = "" outstanding_amount = 0.0 data = [] for d in entries: if d.against_voucher: against_date = d.against_voucher and invoice_posting_date_map[ d.against_voucher] or "" outstanding_amount = d.debit or -1 * d.credit else: against_date = d.against_invoice and invoice_posting_date_map[ d.against_invoice] or "" outstanding_amount = d.credit or -1 * d.debit row = [ d.name, d.account, d.posting_date, d.against_voucher or d.against_invoice, against_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark ] if d.against_voucher or d.against_invoice: row += get_ageing_data(d.posting_date, against_date, outstanding_amount) else: row += ["", "", "", "", ""] data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_entries(filters) invoice_posting_date_map = get_invoice_posting_date_map(filters) against_date = "" outstanding_amount = 0.0 data = [] for d in entries: if d.against_voucher: against_date = d.against_voucher and invoice_posting_date_map[d.against_voucher] or "" outstanding_amount = d.debit or -1*d.credit else: against_date = d.against_invoice and invoice_posting_date_map[d.against_invoice] or "" outstanding_amount = d.credit or -1*d.debit row = [d.name, d.account, d.posting_date, d.against_voucher or d.against_invoice, against_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark] if d.against_voucher or d.against_invoice: row += get_ageing_data(d.posting_date, against_date, outstanding_amount) else: row += ["", "", "", "", ""] data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_entries(filters) pi_posting_date_map = get_pi_posting_date_map() data = [] for d in entries: against_voucher_date = d.against_voucher and pi_posting_date_map[ d.against_voucher] or "" row = [ d.name, d.account, d.posting_date, d.against_voucher, against_voucher_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark ] if d.against_voucher: row += get_ageing_data(against_voucher_date, d.posting_date, d.debit or -1 * d.credit) else: row += ["", "", "", "", ""] data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 paid_amount = get_paid_amount( gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - paid_amount if abs(flt(outstanding_amount)) > 0.01: row = [ gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} supplier_naming_by = webnotes.conn.get_value("Buying Settings", None, "supp_master_name") columns = get_columns(supplier_naming_by) entries = get_gl_entries(filters) account_map = dict(((r.name, r) for r in webnotes.conn.sql("""select acc.name, supp.supplier_name, supp.name as supplier from `tabAccount` acc, `tabSupplier` supp where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1))) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() voucher_detail_map = get_voucher_details() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {}) invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount(gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""), voucher_details.get("bill_date", ""), invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = voucher_details.get("due_date", "") else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \ [account_map.get(gle.account).get("supplier") or ""] if supplier_naming_by == "Naming Series": row += [account_map.get(gle.account).get("supplier_name") or ""] row += [account_supplier_type_map.get(gle.account), gle.remarks] data.append(row) for i in range(0, len(data)): data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \ % ("/".join(["#Form", data[i][2], data[i][3]]),)) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) account_supplier = dict(webnotes.conn.sql("""select account.name, supplier.supplier_name from `tabAccount` account, `tabSupplier` supplier where account.master_type="Supplier" and supplier.name=account.master_name""")) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount(gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [gle.posting_date, gle.account, account_supplier.get(gle.account, ""), gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 paid_amount = get_paid_amount(gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - paid_amount if abs(flt(outstanding_amount)) > 0.01: row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_entries(filters) si_posting_date_map = get_si_posting_date_map() data = [] for d in entries: against_invoice_date = d.against_invoice and si_posting_date_map[d.against_invoice] or "" row = [d.name, d.account, d.posting_date, d.against_invoice, against_invoice_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark] if d.against_invoice: row += get_ageing_data(against_invoice_date, d.posting_date, d.credit or -1*d.debit) else: row += ["", "", "", "", ""] data.append(row) return columns, data
def execute(filters=None): if not filters: filters = {} supplier_naming_by = webnotes.conn.get_value("Buying Settings", None, "supp_master_name") columns = get_columns(supplier_naming_by) entries = get_gl_entries(filters) account_map = dict(((r.name, r) for r in webnotes.conn.sql("""select acc.name, supp.supplier_name, supp.name as supplier from `tabAccount` acc, `tabSupplier` supp where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1))) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() voucher_detail_map = get_voucher_details() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get( gle.voucher_no, {}) invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount( gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [ gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""), voucher_details.get("bill_date", ""), invoiced_amount, paid_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = voucher_details.get("due_date", "") else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \ [account_map.get(gle.account).get("supplier") or ""] if supplier_naming_by == "Naming Series": row += [ account_map.get(gle.account).get("supplier_name") or "" ] row += [ account_supplier_type_map.get(gle.account), gle.remarks ] data.append(row) for i in range(0, len(data)): data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \ % ("/".join(["#Form", data[i][2], data[i][3]]),)) return columns, data