Merge pull request #449 from akhileshdarjee/master
Completed 'Item Prices' Report
diff --git a/accounts/page/accounts_home/accounts_home.js b/accounts/page/accounts_home/accounts_home.js
index 0300902..7f623d7 100644
--- a/accounts/page/accounts_home/accounts_home.js
+++ b/accounts/page/accounts_home/accounts_home.js
@@ -252,6 +252,11 @@
route: "query-report/Item-wise Sales Register",
doctype: "Sales Invoice"
},
+ {
+ "label":wn._("Item-wise Purchase Register"),
+ route: "query-report/Item-wise Purchase Register",
+ doctype: "Purchase Invoice"
+ },
]
}
]
diff --git a/accounts/report/item_wise_purchase_register/__init__.py b/accounts/report/item_wise_purchase_register/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/accounts/report/item_wise_purchase_register/__init__.py
diff --git a/accounts/report/item_wise_purchase_register/item_wise_purchase_register.js b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.js
new file mode 100644
index 0000000..8323a1a
--- /dev/null
+++ b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.js
@@ -0,0 +1,39 @@
+wn.query_reports["Item-wise Purchase Register"] = {
+ "filters": [
+ {
+ "fieldname":"from_date",
+ "label": "From Date",
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_start_date"),
+ "width": "80"
+ },
+ {
+ "fieldname":"to_date",
+ "label": "To Date",
+ "fieldtype": "Date",
+ "default": get_today()
+ },
+ {
+ "fieldname": "item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ },
+ {
+ "fieldname":"account",
+ "label": "Account",
+ "fieldtype": "Link",
+ "options": "Account",
+ "get_query": function() {
+ return {
+ "query": "accounts.utils.get_account_list",
+ "filters": {
+ "is_pl_account": "No",
+ "debit_or_credit": "Credit",
+ "master_type": "Supplier"
+ }
+ }
+ }
+ }
+ ]
+}
\ No newline at end of file
diff --git a/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
new file mode 100644
index 0000000..ad9d795
--- /dev/null
+++ b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
@@ -0,0 +1,74 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+from __future__ import unicode_literals
+import webnotes
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ columns = get_columns()
+ item_list = get_items(filters)
+ aii_account_map = get_aii_accounts()
+ webnotes.errprint(aii_account_map)
+ data = []
+ for d in item_list:
+ expense_head = d.expense_head or aii_account_map.get(d.company)
+ data.append([d.item_code, d.item_name, d.item_group, d.name, d.posting_date, d.supplier,
+ d.credit_to, d.project_name, d.company, d.purchase_order, d.purchase_receipt,
+ expense_head, d.qty, d.rate, d.amount])
+
+ return columns, data
+
+
+def get_columns():
+ return ["Item Code:Link/Item:120", "Item Name::120", "Item Group:Link/Item Group:100",
+ "Invoice:Link/Purchase Invoice:120", "Posting Date:Date:80", "Supplier:Link/Customer:120",
+ "Supplier Account:Link/Account:120", "Project:Link/Project:80", "Company:Link/Company:100",
+ "Purchase Order:Link/Purchase Order:100", "Purchase Receipt:Link/Purchase Receipt:100",
+ "Expense Account:Link/Account:140", "Qty:Float:120", "Rate:Currency:120",
+ "Amount:Currency:120"]
+
+
+def get_conditions(filters):
+ conditions = ""
+
+ if filters.get("account"): conditions += " and pi.credit_to = %(account)s"
+
+ if filters.get("item_code"): conditions += " and pi_item.item_code = %(item_code)s"
+
+ if filters.get("from_date"): conditions += " and pi.posting_date>=%(from_date)s"
+ if filters.get("to_date"): conditions += " and pi.posting_date<=%(to_date)s"
+
+ return conditions
+
+def get_items(filters):
+ conditions = get_conditions(filters)
+ return webnotes.conn.sql("""select pi.name, pi.posting_date, pi.credit_to, pi.company,
+ pi.supplier, pi.remarks, pi_item.item_code, pi_item.item_name, pi_item.item_group,
+ pi_item.project_name, pi_item.purchase_order, pi_item.purchase_receipt,
+ pi_item.expense_head, pi_item.qty, pi_item.rate, pi_item.amount
+ from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item
+ where pi.name = pi_item.parent and pi.docstatus = 1 %s
+ order by pi.posting_date desc, pi_item.item_code desc""" % conditions, filters, as_dict=1)
+
+def get_aii_accounts():
+ aii_account_map = {}
+ for d in webnotes.conn.sql("select name, stock_received_but_not_billed from tabCompany",
+ as_dict=1):
+ aii_account_map.setdefault(d.name, d.stock_received_but_not_billed)
+
+ return aii_account_map
\ No newline at end of file
diff --git a/accounts/report/item_wise_purchase_register/item_wise_purchase_register.txt b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.txt
new file mode 100644
index 0000000..7ded5ff
--- /dev/null
+++ b/accounts/report/item_wise_purchase_register/item_wise_purchase_register.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-06-05 15:37:30",
+ "docstatus": 0,
+ "modified": "2013-06-05 15:37:30",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "add_total_row": 1,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Purchase Invoice",
+ "report_name": "Item-wise Purchase Register",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Item-wise Purchase Register"
+ }
+]
\ No newline at end of file
diff --git a/accounts/report/item_wise_sales_register/__init__.py b/accounts/report/item_wise_sales_register/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/accounts/report/item_wise_sales_register/__init__.py
diff --git a/accounts/report/item_wise_sales_register/item_wise_sales_register.js b/accounts/report/item_wise_sales_register/item_wise_sales_register.js
new file mode 100644
index 0000000..b9ce959
--- /dev/null
+++ b/accounts/report/item_wise_sales_register/item_wise_sales_register.js
@@ -0,0 +1,39 @@
+wn.query_reports["Item-wise Sales Register"] = {
+ "filters": [
+ {
+ "fieldname":"from_date",
+ "label": "From Date",
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_start_date"),
+ "width": "80"
+ },
+ {
+ "fieldname":"to_date",
+ "label": "To Date",
+ "fieldtype": "Date",
+ "default": get_today()
+ },
+ {
+ "fieldname": "item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ },
+ {
+ "fieldname":"account",
+ "label": "Account",
+ "fieldtype": "Link",
+ "options": "Account",
+ "get_query": function() {
+ return {
+ "query": "accounts.utils.get_account_list",
+ "filters": {
+ "is_pl_account": "No",
+ "debit_or_credit": "Debit",
+ "master_type": "Customer"
+ }
+ }
+ }
+ }
+ ]
+}
\ No newline at end of file
diff --git a/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/accounts/report/item_wise_sales_register/item_wise_sales_register.py
new file mode 100644
index 0000000..f6e26af
--- /dev/null
+++ b/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -0,0 +1,67 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+from __future__ import unicode_literals
+import webnotes
+from webnotes.utils import flt
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ columns = get_columns()
+ item_list = get_items(filters)
+
+ data = []
+ for d in item_list:
+ data.append([d.item_code, d.item_name, d.item_group, d.name, d.posting_date, d.customer,
+ d.debit_to, d.territory, d.project_name, d.company, d.sales_order, d.delivery_note,
+ d.income_account, d.qty, d.basic_rate, d.amount])
+
+ return columns, data
+
+
+def get_columns():
+ return [
+ "Item Code:Link/Item:120", "Item Name::120", "Item Group:Link/Item Group:100",
+ "Invoice:Link/Sales Invoice:120", "Posting Date:Date:80", "Customer:Link/Customer:120",
+ "Customer Account:Link/Account:120", "Territory:Link/Territory:80",
+ "Project:Link/Project:80", "Company:Link/Company:100", "Sales Order:Link/Sales Order:100",
+ "Delivery Note:Link/Delivery Note:100", "Income Account:Link/Account:140",
+ "Qty:Float:120", "Rate:Currency:120", "Amount:Currency:120"
+ ]
+
+
+def get_conditions(filters):
+ conditions = ""
+
+ if filters.get("account"): conditions += " and si.debit_to = %(account)s"
+
+ if filters.get("item_code"): conditions += " and si_item.item_code = %(item_code)s"
+
+ if filters.get("from_date"): conditions += " and si.posting_date>=%(from_date)s"
+ if filters.get("to_date"): conditions += " and si.posting_date<=%(to_date)s"
+
+ return conditions
+
+def get_items(filters):
+ conditions = get_conditions(filters)
+ return webnotes.conn.sql("""select si.name, si.posting_date, si.debit_to, si.project_name,
+ si.customer, si.remarks, si.territory, si_item.item_code, si_item.item_name,
+ si_item.item_group, si_item.sales_order, si_item.delivery_note, si_item.income_account,
+ si_item.qty, si_item.basic_rate, si_item.amount
+ from `tabSales Invoice` si, `tabSales Invoice Item` si_item
+ where si.name = si_item.parent and si.docstatus = 1 %s
+ order by si.posting_date desc, si_item.item_code desc""" % conditions, filters, as_dict=1)
\ No newline at end of file
diff --git a/accounts/report/item_wise_sales_register/item_wise_sales_register.txt b/accounts/report/item_wise_sales_register/item_wise_sales_register.txt
new file mode 100644
index 0000000..fb0555d
--- /dev/null
+++ b/accounts/report/item_wise_sales_register/item_wise_sales_register.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-05-13 17:50:55",
+ "docstatus": 0,
+ "modified": "2013-05-13 17:50:55",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "add_total_row": 1,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Sales Invoice",
+ "report_name": "Item-wise Sales Register",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Item-wise Sales Register"
+ }
+]
\ No newline at end of file
diff --git a/patches/patch_list.py b/patches/patch_list.py
index 6a39672..d8b84da 100644
--- a/patches/patch_list.py
+++ b/patches/patch_list.py
@@ -253,4 +253,5 @@
"patches.may_2013.p04_reorder_level",
"patches.may_2013.p05_update_cancelled_gl_entries",
"patches.june_2013.p01_update_bom_exploded_items",
+ "execute:webnotes.delete_doc('DocType', 'System Console')",
]
\ No newline at end of file