[report] payment collection with ageing and payment made with ageing merged to payment period based on invoice date
diff --git a/accounts/page/accounts_home/accounts_home.js b/accounts/page/accounts_home/accounts_home.js
index 40ee326..69c7da1 100644
--- a/accounts/page/accounts_home/accounts_home.js
+++ b/accounts/page/accounts_home/accounts_home.js
@@ -241,13 +241,8 @@
doctype: "Journal Voucher"
},
{
- "label":wn._("Payment Collection With Ageing"),
- route: "query-report/Payment Collection With Ageing",
- doctype: "Journal Voucher"
- },
- {
- "label":wn._("Payment Made With Ageing"),
- route: "query-report/Payment Made With Ageing",
+ "label":wn._("Payment Period Based On Invoice Date"),
+ route: "query-report/Payment Period Based On Invoice Date",
doctype: "Journal Voucher"
},
{
diff --git a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.js b/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.js
deleted file mode 100644
index 6773a0e..0000000
--- a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.js
+++ /dev/null
@@ -1,45 +0,0 @@
-// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
-// License: GNU General Public License v3. See license.txt
-
-wn.query_reports["Payment Collection With Ageing"] = {
- "filters": [
- {
- "fieldname": "from_date",
- "label": wn._("From Date"),
- "fieldtype": "Date",
- "default": wn.defaults.get_user_default("year_start_date"),
- "width": "80"
- },
- {
- "fieldname":"to_date",
- "label": wn._("To Date"),
- "fieldtype": "Date",
- "default": get_today()
- },
- {
- "fieldname":"account",
- "label": wn._("Customer Account"),
- "fieldtype": "Link",
- "options": "Account",
- "get_query": function() {
- var company = wn.query_report.filters_by_name.company.get_value();
- return {
- "query": "accounts.utils.get_account_list",
- "filters": {
- "is_pl_account": "No",
- "debit_or_credit": "Debit",
- "company": company,
- "master_type": "Customer"
- }
- }
- }
- },
- {
- "fieldname":"company",
- "label": wn._("Company"),
- "fieldtype": "Link",
- "options": "Company",
- "default": wn.defaults.get_default("company")
- },
- ]
-}
\ No newline at end of file
diff --git a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.py b/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.py
deleted file mode 100644
index 3529aee..0000000
--- a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.py
+++ /dev/null
@@ -1,77 +0,0 @@
-# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
-# License: GNU General Public License v3. See license.txt
-
-from __future__ import unicode_literals
-import webnotes
-from webnotes import msgprint, _
-from accounts.report.accounts_receivable.accounts_receivable import get_ageing_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(d.posting_date, against_invoice_date, d.credit or -1*d.debit)
- else:
- row += ["", "", "", "", ""]
-
- data.append(row)
-
- return columns, data
-
-def get_columns():
- return ["Journal Voucher:Link/Journal Voucher:140", "Account:Link/Account:140",
- "Posting Date:Date:100", "Against Invoice:Link/Sales Invoice:130",
- "Against Invoice Posting Date:Date:130", "Debit:Currency:120", "Credit:Currency:120",
- "Reference No::100", "Reference Date:Date:100", "Remarks::150", "Age:Int:40",
- "0-30:Currency:100", "30-60:Currency:100", "60-90:Currency:100", "90-Above:Currency:100"
- ]
-
-def get_conditions(filters):
- conditions = ""
-
- customer_accounts = []
- if filters.get("account"):
- customer_accounts = [filters["account"]]
- else:
- cond = filters.get("company") and (" and company = '%s'" % filters["company"]) or ""
- customer_accounts = webnotes.conn.sql_list("""select name from `tabAccount`
- where ifnull(master_type, '') = 'Customer' and docstatus < 2 %s""" % cond)
-
- if customer_accounts:
- conditions += " and jvd.account in (%s)" % (", ".join(['%s']*len(customer_accounts)))
- else:
- msgprint(_("No Customer Accounts found. Customer Accounts are identified based on \
- 'Master Type' value in account record."), raise_exception=1)
-
- if filters.get("from_date"): conditions += " and jv.posting_date >= '%s'" % filters["from_date"]
- if filters.get("to_date"): conditions += " and jv.posting_date <= '%s'" % filters["to_date"]
-
- return conditions, customer_accounts
-
-def get_entries(filters):
- conditions, customer_accounts = get_conditions(filters)
- entries = webnotes.conn.sql("""select jv.name, jvd.account, jv.posting_date,
- jvd.against_invoice, jvd.debit, jvd.credit, jv.cheque_no, jv.cheque_date, jv.remark
- from `tabJournal Voucher Detail` jvd, `tabJournal Voucher` jv
- where jvd.parent = jv.name and jv.docstatus=1 %s order by jv.name DESC""" %
- (conditions), tuple(customer_accounts), as_dict=1)
-
- return entries
-
-def get_si_posting_date_map():
- si_posting_date_map = {}
- for t in webnotes.conn.sql("""select name, posting_date from `tabSales Invoice`"""):
- si_posting_date_map[t[0]] = t[1]
-
- return si_posting_date_map
diff --git a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.txt b/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.txt
deleted file mode 100644
index 3405d19..0000000
--- a/accounts/report/payment_collection_with_ageing/payment_collection_with_ageing.txt
+++ /dev/null
@@ -1,22 +0,0 @@
-[
- {
- "creation": "2013-05-02 12:09:51",
- "docstatus": 0,
- "modified": "2013-05-02 12:09:51",
- "modified_by": "Administrator",
- "owner": "Administrator"
- },
- {
- "add_total_row": 1,
- "doctype": "Report",
- "is_standard": "Yes",
- "name": "__common__",
- "ref_doctype": "Journal Voucher",
- "report_name": "Payment Collection With Ageing",
- "report_type": "Script Report"
- },
- {
- "doctype": "Report",
- "name": "Payment Collection With Ageing"
- }
-]
\ No newline at end of file
diff --git a/accounts/report/payment_made_with_ageing/__init__.py b/accounts/report/payment_made_with_ageing/__init__.py
deleted file mode 100644
index e69de29..0000000
--- a/accounts/report/payment_made_with_ageing/__init__.py
+++ /dev/null
diff --git a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.py b/accounts/report/payment_made_with_ageing/payment_made_with_ageing.py
deleted file mode 100644
index a70ce1b..0000000
--- a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.py
+++ /dev/null
@@ -1,76 +0,0 @@
-# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
-# License: GNU General Public License v3. See license.txt
-
-from __future__ import unicode_literals
-import webnotes
-from webnotes import msgprint, _
-from accounts.report.accounts_receivable.accounts_receivable import get_ageing_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(d.posting_date, against_voucher_date, d.debit or -1*d.credit)
- else:
- row += ["", "", "", "", ""]
-
- data.append(row)
-
- return columns, data
-
-def get_columns():
- return ["Journal Voucher:Link/Journal Voucher:140", "Account:Link/Account:140",
- "Posting Date:Date:100", "Against Invoice:Link/Purchase Invoice:130",
- "Against Invoice Posting Date:Date:130", "Debit:Currency:120", "Credit:Currency:120",
- "Reference No::100", "Reference Date:Date:100", "Remarks::150", "Age:Int:40",
- "0-30:Currency:100", "30-60:Currency:100", "60-90:Currency:100", "90-Above:Currency:100"
- ]
-
-def get_conditions(filters):
- conditions = ""
- supplier_accounts = []
- if filters.get("account"):
- supplier_accounts = [filters["account"]]
- else:
- cond = filters.get("company") and (" and company = '%s'" % filters["company"]) or ""
- supplier_accounts = webnotes.conn.sql_list("""select name from `tabAccount`
- where ifnull(master_type, '') = 'Supplier' and docstatus < 2 %s""" % cond)
-
- if supplier_accounts:
- conditions += " and jvd.account in (%s)" % (", ".join(['%s']*len(supplier_accounts)))
- else:
- msgprint(_("No Supplier Accounts found. Supplier Accounts are identified based on \
- 'Master Type' value in account record."), raise_exception=1)
-
- if filters.get("from_date"): conditions += " and jv.posting_date >= '%s'" % filters["from_date"]
- if filters.get("to_date"): conditions += " and jv.posting_date <= '%s'" % filters["to_date"]
-
- return conditions, supplier_accounts
-
-def get_entries(filters):
- conditions, supplier_accounts = get_conditions(filters)
- entries = webnotes.conn.sql("""select jv.name, jvd.account, jv.posting_date,
- jvd.against_voucher, jvd.debit, jvd.credit, jv.cheque_no, jv.cheque_date, jv.remark
- from `tabJournal Voucher Detail` jvd, `tabJournal Voucher` jv
- where jvd.parent = jv.name and jv.docstatus=1 %s order by jv.name DESC""" %
- (conditions), tuple(supplier_accounts), as_dict=1)
-
- return entries
-
-def get_pi_posting_date_map():
- pi_posting_date_map = {}
- for t in webnotes.conn.sql("""select name, posting_date from `tabPurchase Invoice`"""):
- pi_posting_date_map[t[0]] = t[1]
-
- return pi_posting_date_map
diff --git a/accounts/report/payment_collection_with_ageing/__init__.py b/accounts/report/payment_period_based_on_invoice_date/__init__.py
similarity index 100%
rename from accounts/report/payment_collection_with_ageing/__init__.py
rename to accounts/report/payment_period_based_on_invoice_date/__init__.py
diff --git a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.js b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
similarity index 77%
rename from accounts/report/payment_made_with_ageing/payment_made_with_ageing.js
rename to accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
index 2d27b8e..57d8931 100644
--- a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.js
+++ b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
@@ -1,7 +1,7 @@
// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
// License: GNU General Public License v3. See license.txt
-wn.query_reports["Payment Made With Ageing"] = {
+wn.query_reports["Payment Period Based On Invoice Date"] = {
"filters": [
{
fieldname: "from_date",
@@ -16,8 +16,15 @@
default: get_today()
},
{
+ fieldname:"payment_type",
+ label: wn._("Payment Type"),
+ fieldtype: "Select",
+ options: "Incoming\nOutgoing",
+ default: "Incoming"
+ },
+ {
fieldname:"account",
- label: wn._("Supplier Account"),
+ label: wn._("Account"),
fieldtype: "Link",
options: "Account",
get_query: function() {
@@ -25,9 +32,7 @@
query: "accounts.utils.get_account_list",
filters: {
is_pl_account: "No",
- debit_or_credit: "Credit",
- company: wn.query_report.filters_by_name.company.get_value(),
- master_type: "Supplier"
+ company: wn.query_report.filters_by_name.company.get_value()
}
}
}
diff --git a/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
new file mode 100644
index 0000000..1273360
--- /dev/null
+++ b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
@@ -0,0 +1,95 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import webnotes
+from webnotes import msgprint, _
+from accounts.report.accounts_receivable.accounts_receivable import get_ageing_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 get_columns():
+ return ["Journal Voucher:Link/Journal Voucher:140", "Account:Link/Account:140",
+ "Posting Date:Date:100", "Against Invoice:Link/Purchase Invoice:130",
+ "Against Invoice Posting Date:Date:130", "Debit:Currency:120", "Credit:Currency:120",
+ "Reference No::100", "Reference Date:Date:100", "Remarks::150", "Age:Int:40",
+ "0-30:Currency:100", "30-60:Currency:100", "60-90:Currency:100", "90-Above:Currency:100"
+ ]
+
+def get_conditions(filters):
+ conditions = ""
+ party_accounts = []
+
+ if filters.get("account"):
+ party_accounts = [filters["account"]]
+ else:
+ cond = filters.get("company") and (" and company = '%s'" % filters["company"]) or ""
+
+ if filters.get("payment_type") == "Incoming":
+ cond += " and master_type = 'Customer'"
+ else:
+ cond += " and master_type = 'Supplier'"
+
+ party_accounts = webnotes.conn.sql_list("""select name from `tabAccount`
+ where ifnull(master_name, '')!='' and docstatus < 2 %s""" % cond)
+
+ if party_accounts:
+ conditions += " and jvd.account in (%s)" % (", ".join(['%s']*len(party_accounts)))
+ else:
+ msgprint(_("No Customer or Supplier Accounts found. Accounts are identified based on \
+ 'Master Type' value in account record."), raise_exception=1)
+
+ if filters.get("from_date"): conditions += " and jv.posting_date >= '%s'" % filters["from_date"]
+ if filters.get("to_date"): conditions += " and jv.posting_date <= '%s'" % filters["to_date"]
+
+ return conditions, party_accounts
+
+def get_entries(filters):
+ conditions, party_accounts = get_conditions(filters)
+ entries = webnotes.conn.sql("""select jv.name, jvd.account, jv.posting_date,
+ jvd.against_voucher, jvd.against_invoice, jvd.debit, jvd.credit,
+ jv.cheque_no, jv.cheque_date, jv.remark
+ from `tabJournal Voucher Detail` jvd, `tabJournal Voucher` jv
+ where jvd.parent = jv.name and jv.docstatus=1 %s order by jv.name DESC""" %
+ (conditions), tuple(party_accounts), as_dict=1)
+
+ return entries
+
+def get_invoice_posting_date_map(filters):
+ invoice_posting_date_map = {}
+ if filters.get("payment_type") == "Incoming":
+ for t in webnotes.conn.sql("""select name, posting_date from `tabSales Invoice`"""):
+ invoice_posting_date_map[t[0]] = t[1]
+ else:
+ for t in webnotes.conn.sql("""select name, posting_date from `tabPurchase Invoice`"""):
+ invoice_posting_date_map[t[0]] = t[1]
+
+ return invoice_posting_date_map
\ No newline at end of file
diff --git a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.txt b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.txt
similarity index 61%
rename from accounts/report/payment_made_with_ageing/payment_made_with_ageing.txt
rename to accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.txt
index c5c85da..4080602 100644
--- a/accounts/report/payment_made_with_ageing/payment_made_with_ageing.txt
+++ b/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-05-02 12:10:21",
+ "creation": "2013-12-02 17:06:37",
"docstatus": 0,
- "modified": "2013-05-02 12:10:21",
+ "modified": "2013-12-02 17:06:39",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -12,11 +12,11 @@
"is_standard": "Yes",
"name": "__common__",
"ref_doctype": "Journal Voucher",
- "report_name": "Payment Made With Ageing",
+ "report_name": "Payment Period Based On Invoice Date",
"report_type": "Script Report"
},
{
"doctype": "Report",
- "name": "Payment Made With Ageing"
+ "name": "Payment Period Based On Invoice Date"
}
]
\ No newline at end of file
diff --git a/patches/patch_list.py b/patches/patch_list.py
index 52277a8..34427e6 100644
--- a/patches/patch_list.py
+++ b/patches/patch_list.py
@@ -255,4 +255,6 @@
"patches.1311.p05_website_brand_html",
"patches.1311.p06_fix_report_columns",
"execute:webnotes.delete_doc('DocType', 'Documentation Tool')",
+ "execute:webnotes.delete_doc('Report', 'Payment Collection With Ageing')",
+ "execute:webnotes.delete_doc('Report', 'Payment Made With Ageing')",
]
\ No newline at end of file