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