AR/AP Report Enhancement (#16175)
* Modified AR/AP print format
* Filter Sales Person based on either Customer or Voucher's assigned Sales Person
* -Added Sales Person column to Accounts Receivable Summary
-Added all filters in AR/AP Summary Report as in AR/AP Report
-Reordered filters for better grouping
-Fixed a bug that customer_name was not unset after removing Customer from filter
diff --git a/erpnext/accounts/report/accounts_payable/accounts_payable.js b/erpnext/accounts/report/accounts_payable/accounts_payable.js
index 9370f04..0a025f6 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.js
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.js
@@ -11,36 +11,6 @@
"default": frappe.defaults.get_user_default("Company")
},
{
- "fieldname":"finance_book",
- "label": __("Finance Book"),
- "fieldtype": "Link",
- "options": "Finance Book"
- },
- {
- "fieldname":"supplier",
- "label": __("Supplier"),
- "fieldtype": "Link",
- "options": "Supplier",
- on_change: () => {
- var supplier = frappe.query_report.get_filter_value('supplier');
- frappe.db.get_value('Supplier', supplier, "tax_id", function(value) {
- frappe.query_report.set_filter_value('tax_id', value["tax_id"]);
- });
- }
- },
- {
- "fieldname":"supplier_group",
- "label": __("Supplier Group"),
- "fieldtype": "Link",
- "options": "Supplier Group"
- },
- {
- "fieldname":"report_date",
- "label": __("As on Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.get_today()
- },
- {
"fieldname":"ageing_based_on",
"label": __("Ageing Based On"),
"fieldtype": "Select",
@@ -48,7 +18,10 @@
"default": "Posting Date"
},
{
- "fieldtype": "Break",
+ "fieldname":"report_date",
+ "label": __("As on Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today()
},
{
"fieldname":"range1",
@@ -72,6 +45,34 @@
"reqd": 1
},
{
+ "fieldname":"finance_book",
+ "label": __("Finance Book"),
+ "fieldtype": "Link",
+ "options": "Finance Book"
+ },
+ {
+ "fieldname":"supplier",
+ "label": __("Supplier"),
+ "fieldtype": "Link",
+ "options": "Supplier",
+ on_change: () => {
+ var supplier = frappe.query_report.get_filter_value('supplier');
+ if (supplier) {
+ frappe.db.get_value('Supplier', supplier, "tax_id", function(value) {
+ frappe.query_report.set_filter_value('tax_id', value["tax_id"]);
+ });
+ } else {
+ frappe.query_report.set_filter_value('tax_id', "");
+ }
+ }
+ },
+ {
+ "fieldname":"supplier_group",
+ "label": __("Supplier Group"),
+ "fieldtype": "Link",
+ "options": "Supplier Group"
+ },
+ {
"fieldname":"tax_id",
"label": __("Tax Id"),
"fieldtype": "Data",
diff --git a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
index 77b099f..7823cac 100644
--- a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
+++ b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
@@ -11,24 +11,6 @@
"default": frappe.defaults.get_user_default("Company")
},
{
- "fieldname":"supplier",
- "label": __("Supplier"),
- "fieldtype": "Link",
- "options": "Supplier"
- },
- {
- "fieldname":"supplier_group",
- "label": __("Supplier Group"),
- "fieldtype": "Link",
- "options": "Supplier Group"
- },
- {
- "fieldname":"report_date",
- "label": __("Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.get_today()
- },
- {
"fieldname":"ageing_based_on",
"label": __("Ageing Based On"),
"fieldtype": "Select",
@@ -36,7 +18,10 @@
"default": "Posting Date"
},
{
- "fieldtype": "Break",
+ "fieldname":"report_date",
+ "label": __("Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today()
},
{
"fieldname":"range1",
@@ -58,6 +43,24 @@
"fieldtype": "Int",
"default": "90",
"reqd": 1
+ },
+ {
+ "fieldname":"finance_book",
+ "label": __("Finance Book"),
+ "fieldtype": "Link",
+ "options": "Finance Book"
+ },
+ {
+ "fieldname":"supplier",
+ "label": __("Supplier"),
+ "fieldtype": "Link",
+ "options": "Supplier"
+ },
+ {
+ "fieldname":"supplier_group",
+ "label": __("Supplier Group"),
+ "fieldtype": "Link",
+ "options": "Supplier Group"
}
],
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.html b/erpnext/accounts/report/accounts_receivable/accounts_receivable.html
index 4930207..fd462a6 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.html
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.html
@@ -1,24 +1,14 @@
-{% if(filters.show_pdc_in_print) { %}
- <style>
- @media screen {
- .print-format {
- padding: 4mm;
- font-size: 8.0pt !important;
- font-family: Tahoma, sans-serif;
- }
- }
- </style>
-{% } %}
<style>
- .print-format {
- padding: 4mm;
- font-size: 8.0pt !important;
- font-family: Tahoma, sans-serif;
- }
+.print-format {
+ padding: 4mm;
+ font-size: 8.0pt !important;
+}
+.print-format td {
+ vertical-align:middle !important;
+}
</style>
-
-<h2 class="text-center">{%= __(report.report_name) %}</h2>
+<h2 class="text-center" style="margin-top:0">{%= __(report.report_name) %}</h2>
<h4 class="text-center">
{% if (filters.customer_name) { %}
{%= filters.customer_name %}
@@ -36,7 +26,20 @@
{%= __("Until") %}
{%= frappe.datetime.str_to_user(filters.report_date) %}
</h5>
-<hr>
+
+<div class="clearfix">
+ <div class="pull-left">
+ {% if(filters.payment_terms) { %}
+ <strong>{%= __("Payment Terms") %}:</strong> {%= filters.payment_terms %}
+ {% } %}
+ </div>
+ <div class="pull-right">
+ {% if(filters.credit_limit) { %}
+ <strong>{%= __("Credit Limit") %}:</strong> {%= format_currency(filters.credit_limit) %}
+ {% } %}
+ </div>
+</div>
+
{% if(filters.show_pdc_in_print) { %}
{% var balance_row = data.slice(-1).pop();
var range1 = report.columns[11].label;
@@ -104,17 +107,21 @@
<thead>
<tr>
{% if(report.report_name === "Accounts Receivable" || report.report_name === "Accounts Payable") { %}
- <th style="width: 18%">{%= __("Date") %}</th>
- <th style="width: 17%">{%= __("Reference") %}</th>
+ <th style="width: 7%">{%= __("Date") %}</th>
+ <th style="width: 7%">{%= __("Age (Days)") %}</th>
+ <th style="width: 13%">{%= __("Reference") %}</th>
+ {% if(report.report_name === "Accounts Receivable") { %}
+ <th style="width: 10%">{%= __("Sales Person") %}</th>
+ {% } %}
{% if(!filters.show_pdc_in_print) { %}
<th style="width: 20%">{%= (filters.customer || filters.supplier) ? __("Remarks"): __("Party") %}</th>
{% } %}
- <th style="width: 10%">{%= __("Invoiced Amount") %}</th>
+ <th style="width: 10%; text-align: right">{%= __("Invoiced Amount") %}</th>
{% if(!filters.show_pdc_in_print) { %}
- <th style="width: 10%">{%= __("Paid Amount") %}</th>
- <th style="width: 10%">{%= report.report_name === "Accounts Receivable" ? __('Credit Note') : __('Debit Note') %}</th>
+ <th style="width: 10%; text-align: right">{%= __("Paid Amount") %}</th>
+ <th style="width: 10%; text-align: right">{%= report.report_name === "Accounts Receivable" ? __('Credit Note') : __('Debit Note') %}</th>
{% } %}
- <th style="width: 15%">{%= __("Outstanding Amount") %}</th>
+ <th style="width: 15%; text-align: right">{%= __("Outstanding Amount") %}</th>
{% if(filters.show_pdc_in_print) { %}
{% if(report.report_name === "Accounts Receivable") { %}
<th style="width: 10%">{%= __("Customer LPO No.") %}</th>
@@ -139,6 +146,7 @@
{% if(report.report_name === "Accounts Receivable" || report.report_name === "Accounts Payable") { %}
{% if(data[i][__("Customer")] || data[i][__("Supplier")]) { %}
<td>{%= frappe.datetime.str_to_user(data[i]["posting_date"]) %}</td>
+ <td style="text-align: right">{%= data[i][__("Age (Days)")] %}</td>
<td>
{% if(!filters.show_pdc_in_print) { %}
{%= data[i]["voucher_type"] %}
@@ -146,6 +154,11 @@
{% } %}
{%= data[i]["voucher_no"] %}
</td>
+
+ {% if(report.report_name === "Accounts Receivable") { %}
+ <td>{%= data[i]["sales_person"] %}</td>
+ {% } %}
+
{% if(!filters.show_pdc_in_print) { %}
<td>
{% if(!(filters.customer || filters.supplier)) { %}
@@ -156,10 +169,15 @@
<br> {%= data[i][__("Supplier Name")] %}
{% } %}
{% } %}
- <br>{%= __("Remarks") %}:
- {%= data[i][__("Remarks")] %}
+ <div>
+ {% if data[i][__("Remarks")] %}
+ {%= __("Remarks") %}:
+ {%= data[i][__("Remarks")] %}
+ {% } %}
+ </div>
</td>
{% } %}
+
<td style="text-align: right">
{%= format_currency(data[i]["invoiced_amount"], data[i]["currency"]) %}</td>
@@ -187,7 +205,11 @@
{% if(!filters.show_pdc_in_print) { %}
<td></td>
{% } %}
- <td><b>{%= __("Total") %}</b></td>
+ {% if(report.report_name === "Accounts Receivable") { %}
+ <td></td>
+ {% } %}
+ <td></td>
+ <td style="text-align: right"><b>{%= __("Total") %}</b></td>
<td style="text-align: right">
{%= format_currency(data[i]["invoiced_amount"], data[i]["currency"] ) %}</td>
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
index fda4960..b1bdce9 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
@@ -11,6 +11,40 @@
"default": frappe.defaults.get_user_default("Company")
},
{
+ "fieldname":"ageing_based_on",
+ "label": __("Ageing Based On"),
+ "fieldtype": "Select",
+ "options": 'Posting Date\nDue Date',
+ "default": "Posting Date"
+ },
+ {
+ "fieldname":"report_date",
+ "label": __("As on Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname":"range1",
+ "label": __("Ageing Range 1"),
+ "fieldtype": "Int",
+ "default": "30",
+ "reqd": 1
+ },
+ {
+ "fieldname":"range2",
+ "label": __("Ageing Range 2"),
+ "fieldtype": "Int",
+ "default": "60",
+ "reqd": 1
+ },
+ {
+ "fieldname":"range3",
+ "label": __("Ageing Range 3"),
+ "fieldtype": "Int",
+ "default": "90",
+ "reqd": 1
+ },
+ {
"fieldname":"finance_book",
"label": __("Finance Book"),
"fieldtype": "Link",
@@ -23,10 +57,19 @@
"options": "Customer",
on_change: () => {
var customer = frappe.query_report.get_filter_value('customer');
- frappe.db.get_value('Customer', customer, ["tax_id", "customer_name"], function(value) {
- frappe.query_report.set_filter_value('tax_id', value["tax_id"]);
- frappe.query_report.set_filter_value('customer_name', value["customer_name"]);
- });
+ if (customer) {
+ frappe.db.get_value('Customer', customer, ["tax_id", "customer_name", "credit_limit", "payment_terms"], function(value) {
+ frappe.query_report.set_filter_value('tax_id', value["tax_id"]);
+ frappe.query_report.set_filter_value('customer_name', value["customer_name"]);
+ frappe.query_report.set_filter_value('credit_limit', value["credit_limit"]);
+ frappe.query_report.set_filter_value('payment_terms', value["payment_terms"]);
+ });
+ } else {
+ frappe.query_report.set_filter_value('tax_id', "");
+ frappe.query_report.set_filter_value('customer_name', "");
+ frappe.query_report.set_filter_value('credit_limit', "");
+ frappe.query_report.set_filter_value('payment_terms', "");
+ }
}
},
{
@@ -60,43 +103,6 @@
"options": "Sales Person"
},
{
- "fieldtype": "Break",
- },
- {
- "fieldname":"report_date",
- "label": __("As on Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.get_today()
- },
- {
- "fieldname":"ageing_based_on",
- "label": __("Ageing Based On"),
- "fieldtype": "Select",
- "options": 'Posting Date\nDue Date',
- "default": "Posting Date"
- },
- {
- "fieldname":"range1",
- "label": __("Ageing Range 1"),
- "fieldtype": "Int",
- "default": "30",
- "reqd": 1
- },
- {
- "fieldname":"range2",
- "label": __("Ageing Range 2"),
- "fieldtype": "Int",
- "default": "60",
- "reqd": 1
- },
- {
- "fieldname":"range3",
- "label": __("Ageing Range 3"),
- "fieldtype": "Int",
- "default": "90",
- "reqd": 1
- },
- {
"fieldname":"show_pdc_in_print",
"label": __("Show PDC in Print"),
"fieldtype": "Check",
@@ -112,6 +118,18 @@
"label": __("Customer Name"),
"fieldtype": "Data",
"hidden": 1
+ },
+ {
+ "fieldname":"payment_terms",
+ "label": __("Payment Tems"),
+ "fieldtype": "Data",
+ "hidden": 1
+ },
+ {
+ "fieldname":"credit_limit",
+ "label": __("Credit Limit"),
+ "fieldtype": "Currency",
+ "hidden": 1
}
],
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 572f81d..8e05a08 100755
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -125,17 +125,22 @@
}]
if args.get('party_type') == 'Customer':
- columns.append({
- "label": _("Customer LPO"),
- "fieldtype": "Data",
- "fieldname": "po_no",
- "width": 100,
- })
- columns += [_("Delivery Note") + ":Data:100"]
- if args.get("party_type") == "Customer":
columns += [
+ {
+ "label": _("Customer LPO"),
+ "fieldtype": "Data",
+ "fieldname": "po_no",
+ "width": 100,
+ },
+ _("Delivery Note") + ":Data:100",
_("Territory") + ":Link/Territory:80",
- _("Customer Group") + ":Link/Customer Group:120"
+ _("Customer Group") + ":Link/Customer Group:120",
+ {
+ "label": _("Sales Person"),
+ "fieldtype": "Data",
+ "fieldname": "sales_person",
+ "width": 120,
+ }
]
if args.get("party_type") == "Supplier":
columns += [_("Supplier Group") + ":Link/Supplier Group:80"]
@@ -238,7 +243,8 @@
# customer territory / supplier group
if args.get("party_type") == "Customer":
- row += [self.get_territory(gle.party), self.get_customer_group(gle.party)]
+ row += [self.get_territory(gle.party), self.get_customer_group(gle.party),
+ voucher_details.get(gle.voucher_no, {}).get("sales_person")]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_group(gle.party)]
@@ -395,9 +401,14 @@
values.append(self.filters.get("sales_partner"))
if self.filters.get("sales_person"):
- conditions.append("""party in (select parent
- from `tabSales Team` where sales_person=%s and parenttype = 'Customer')""")
- values.append(self.filters.get("sales_person"))
+ lft, rgt = frappe.db.get_value("Sales Person",
+ self.filters.get("sales_person"), ["lft", "rgt"])
+
+ conditions.append("""exists(select name from `tabSales Team` steam where
+ steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1})
+ and ((steam.parent = voucher_no and steam.parenttype = voucher_type)
+ or (steam.parent = against_voucher and steam.parenttype = against_voucher_type)
+ or (steam.parent = party and steam.parenttype = 'Customer')))""".format(lft, rgt))
if party_type_field=="supplier":
if self.filters.get("supplier_group"):
@@ -550,8 +561,12 @@
voucher_details = frappe._dict()
if party_type == "Customer":
- for si in frappe.db.sql("""select name, due_date, po_no
- from `tabSales Invoice` where docstatus=1 and name in (%s)
+ for si in frappe.db.sql("""
+ select inv.name, inv.due_date, inv.po_no, GROUP_CONCAT(steam.sales_person SEPARATOR ', ') as sales_person
+ from `tabSales Invoice` inv
+ left join `tabSales Team` steam on steam.parent = inv.name and steam.parenttype = 'Sales Invoice'
+ where inv.docstatus=1 and inv.name in (%s)
+ group by inv.name
""" %(','.join(['%s'] *len(voucher_nos))), (tuple(voucher_nos)), as_dict=1):
si['delivery_note'] = dn_details.get(si.name)
voucher_details.setdefault(si.name, si)
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
index 96e5d18..a6f1457 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
@@ -11,25 +11,11 @@
"default": frappe.defaults.get_user_default("Company")
},
{
- "fieldname":"customer",
- "label": __("Customer"),
- "fieldtype": "Link",
- "options": "Customer"
- },
- {
- "fieldname":"customer_group",
- "label": __("Customer Group"),
- "fieldtype": "Link",
- "options": "Customer Group"
- },
- {
- "fieldname":"payment_terms_template",
- "label": __("Payment Terms Template"),
- "fieldtype": "Link",
- "options": "Payment Terms Template"
- },
- {
- "fieldtype": "Break",
+ "fieldname":"ageing_based_on",
+ "label": __("Ageing Based On"),
+ "fieldtype": "Select",
+ "options": 'Posting Date\nDue Date',
+ "default": "Posting Date"
},
{
"fieldname":"report_date",
@@ -38,13 +24,6 @@
"default": frappe.datetime.get_today()
},
{
- "fieldname":"ageing_based_on",
- "label": __("Ageing Based On"),
- "fieldtype": "Select",
- "options": 'Posting Date\nDue Date',
- "default": "Posting Date"
- },
- {
"fieldname":"range1",
"label": __("Ageing Range 1"),
"fieldtype": "Int",
@@ -64,6 +43,48 @@
"fieldtype": "Int",
"default": "90",
"reqd": 1
+ },
+ {
+ "fieldname":"finance_book",
+ "label": __("Finance Book"),
+ "fieldtype": "Link",
+ "options": "Finance Book"
+ },
+ {
+ "fieldname":"customer",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer"
+ },
+ {
+ "fieldname":"customer_group",
+ "label": __("Customer Group"),
+ "fieldtype": "Link",
+ "options": "Customer Group"
+ },
+ {
+ "fieldname":"payment_terms_template",
+ "label": __("Payment Terms Template"),
+ "fieldtype": "Link",
+ "options": "Payment Terms Template"
+ },
+ {
+ "fieldname":"territory",
+ "label": __("Territory"),
+ "fieldtype": "Link",
+ "options": "Territory"
+ },
+ {
+ "fieldname":"sales_partner",
+ "label": __("Sales Partner"),
+ "fieldtype": "Link",
+ "options": "Sales Partner"
+ },
+ {
+ "fieldname":"sales_person",
+ "label": __("Sales Person"),
+ "fieldtype": "Link",
+ "options": "Sales Person"
}
],
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
index 7bf9972..190031a 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
@@ -97,6 +97,12 @@
"fieldtype": "Link",
"options": "Customer Group",
"width": 80
+ },
+ {
+ "label": _("Sales Person"),
+ "fieldtype": "Data",
+ "fieldname": "sales_person",
+ "width": 120,
}]
if args.get("party_type") == "Supplier":
@@ -135,7 +141,7 @@
]
if args.get("party_type") == "Customer":
- row += [self.get_territory(party), self.get_customer_group(party)]
+ row += [self.get_territory(party), self.get_customer_group(party), ", ".join(set(party_dict.sales_person))]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_group(party)]
@@ -156,15 +162,19 @@
"range1": 0,
"range2": 0,
"range3": 0,
- "range4": 0
+ "range4": 0,
+ "sales_person": []
})
)
for k in list(party_total[d.party]):
- if k != "currency":
+ if k not in ["currency", "sales_person"]:
party_total[d.party][k] += flt(d.get(k, 0))
party_total[d.party].currency = d.currency
+ if d.sales_person:
+ party_total[d.party].sales_person.append(d.sales_person)
+
return party_total
def get_voucherwise_data(self, party_naming_by, args):
@@ -181,12 +191,13 @@
cols += ["bill_no", "bill_date"]
cols += ["invoiced_amt", "paid_amt", "credit_amt",
- "outstanding_amt", "age", "range1", "range2", "range3", "range4", "currency"]
+ "outstanding_amt", "age", "range1", "range2", "range3", "range4", "currency", "pdc/lc_date", "pdc/lc_ref",
+ "pdc/lc_amount", "remaining_balance"]
if args.get("party_type") == "Supplier":
cols += ["supplier_group", "remarks"]
if args.get("party_type") == "Customer":
- cols += ["territory", "customer_group", "remarks"]
+ cols += ["po_no", "do_no", "territory", "customer_group", "sales_person", "remarks"]
return self.make_data_dict(cols, voucherwise_data)