Merge pull request #1119 from akhileshdarjee/supplier-report
[report] item-wise consumption in buying module
diff --git a/buying/page/buying_home/buying_home.js b/buying/page/buying_home/buying_home.js
index 5c5668e..811ed6f 100644
--- a/buying/page/buying_home/buying_home.js
+++ b/buying/page/buying_home/buying_home.js
@@ -155,6 +155,11 @@
route: "query-report/Supplier Addresses and Contacts",
doctype: "Supplier"
},
+ {
+ "label":wn._("Supplier-Wise Sales Analytics"),
+ route: "query-report/Supplier-Wise Sales Analytics",
+ doctype: "Stock Ledger Entry"
+ }
]
}
]
diff --git a/stock/report/supplier_wise_sales_analytics/__init__.py b/stock/report/supplier_wise_sales_analytics/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/supplier_wise_sales_analytics/__init__.py
diff --git a/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.js b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.js
new file mode 100644
index 0000000..4384b70
--- /dev/null
+++ b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.js
@@ -0,0 +1,28 @@
+// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+// License: GNU General Public License v3. See license.txt
+
+wn.query_reports["Supplier-Wise Sales Analytics"] = {
+ "filters": [
+ {
+ "fieldname":"supplier",
+ "label": wn._("Supplier"),
+ "fieldtype": "Link",
+ "options": "Supplier",
+ "width": "80"
+ },
+ {
+ "fieldname":"from_date",
+ "label": wn._("From Date"),
+ "fieldtype": "Date",
+ "width": "80",
+ "default": wn.datetime.month_start()
+ },
+ {
+ "fieldname":"to_date",
+ "label": wn._("To Date"),
+ "fieldtype": "Date",
+ "width": "80",
+ "default": wn.datetime.month_end()
+ },
+ ]
+}
\ No newline at end of file
diff --git a/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
new file mode 100644
index 0000000..254bd90
--- /dev/null
+++ b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.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.utils import flt
+
+def execute(filters=None):
+ columns = get_columns(filters)
+ consumed_details = get_consumed_details(filters)
+ supplier_details = get_suppliers_details(filters)
+ material_transfer_vouchers = get_material_transfer_vouchers()
+ data = []
+
+ for item_code, suppliers in supplier_details.items():
+ consumed_qty = consumed_amount = delivered_qty = delivered_amount = 0.0
+ total_qty = total_amount = 0.0
+ if consumed_details.get(item_code):
+ for cd in consumed_details.get(item_code):
+
+ if (cd.voucher_no not in material_transfer_vouchers):
+ if cd.voucher_type=="Delivery Note":
+ delivered_qty += abs(flt(cd.actual_qty))
+ delivered_amount += abs(flt(cd.stock_value_difference))
+ elif cd.voucher_type!="Delivery Note":
+ consumed_qty += abs(flt(cd.actual_qty))
+ consumed_amount += abs(flt(cd.stock_value_difference))
+
+ if consumed_qty or consumed_amount or delivered_qty or delivered_amount:
+ total_qty += delivered_qty + consumed_qty
+ total_amount += delivered_amount + consumed_amount
+
+ row = [cd.item_code, cd.item_name, cd.description, cd.stock_uom, \
+ consumed_qty, consumed_amount, delivered_qty, delivered_amount, \
+ total_qty, total_amount, list(set(suppliers))]
+ data.append(row)
+
+ return columns, data
+
+def get_columns(filters):
+ """return columns based on filters"""
+
+ columns = ["Item:Link/Item:100"] + ["Item Name::100"] + \
+ ["Description::150"] + ["UOM:Link/UOM:90"] + \
+ ["Consumed Qty:Float:110"] + ["Consumed Amount:Currency:130"] + \
+ ["Delivered Qty:Float:110"] + ["Delivered Amount:Currency:130"] + \
+ ["Total Qty:Float:110"] + ["Total Amount:Currency:130"] + \
+ ["Supplier(s)::250"]
+
+ return columns
+
+def get_conditions(filters):
+ conditions = ""
+ values = []
+
+ if filters.get('from_date') and filters.get('to_date'):
+ conditions = "and sle.posting_date>=%s and sle.posting_date<=%s"
+ values = [filters.get('from_date'), filters.get('to_date')]
+
+ return conditions, values
+
+def get_consumed_details(filters):
+ conditions, values = get_conditions(filters)
+ consumed_details = {}
+
+ for d in webnotes.conn.sql("""select sle.item_code, i.item_name, i.description,
+ i.stock_uom, sle.actual_qty, sle.stock_value_difference,
+ sle.voucher_no, sle.voucher_type
+ from `tabStock Ledger Entry` sle, `tabItem` i
+ where sle.item_code=i.name and sle.actual_qty < 0 %s""" % conditions, values, as_dict=1):
+ consumed_details.setdefault(d.item_code, []).append(d)
+
+ return consumed_details
+
+def get_suppliers_details(filters):
+ item_supplier_map = {}
+ supplier = filters.get('supplier')
+
+ for d in webnotes.conn.sql("""select pr.supplier, pri.item_code from
+ `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pri
+ where pr.name=pri.parent and pr.docstatus=1 and
+ pri.item_code=(select name from `tabItem` where
+ ifnull(is_stock_item, 'Yes')='Yes' and name=pri.item_code)""", as_dict=1):
+ item_supplier_map.setdefault(d.item_code, []).append(d.supplier)
+
+ if supplier:
+ for item_code, suppliers in item_supplier_map.items():
+ if supplier not in suppliers:
+ del item_supplier_map[item_code]
+
+ return item_supplier_map
+
+def get_material_transfer_vouchers():
+ return webnotes.conn.sql_list("""select name from `tabStock Entry` where
+ purpose='Material Transfer' and docstatus=1""")
\ No newline at end of file
diff --git a/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.txt b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.txt
new file mode 100644
index 0000000..936292c
--- /dev/null
+++ b/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-11-29 15:45:39",
+ "docstatus": 0,
+ "modified": "2013-11-29 15:45:40",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "add_total_row": 1,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Supplier-Wise Sales Analytics",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Supplier-Wise Sales Analytics"
+ }
+]
\ No newline at end of file