[removed merge conflict]
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/manufacturing/doctype/bom/bom.py b/manufacturing/doctype/bom/bom.py
index 5a1d47f..5f64148 100644
--- a/manufacturing/doctype/bom/bom.py
+++ b/manufacturing/doctype/bom/bom.py
@@ -266,22 +266,27 @@
for b in boms:
if b[0] == self.doc.name:
msgprint("""Recursion Occured => '%s' cannot be '%s' of '%s'.
- """ % (cstr(b), cstr(d[2]), self.doc.name), raise_exception = 1)
+ """ % (cstr(b[0]), cstr(d[2]), self.doc.name), raise_exception = 1)
if b[0]:
bom_list.append(b[0])
- def update_cost_and_exploded_items(self):
- bom_list = self.traverse_tree()
+ def update_cost_and_exploded_items(self, bom_list=[]):
+ bom_list = self.traverse_tree(bom_list)
for bom in bom_list:
bom_obj = get_obj("BOM", bom, with_children=1)
bom_obj.on_update()
- def traverse_tree(self):
+ return bom_list
+
+ def traverse_tree(self, bom_list=[]):
def _get_children(bom_no):
return [cstr(d[0]) for d in webnotes.conn.sql("""select bom_no from `tabBOM Item`
where parent = %s and ifnull(bom_no, '') != ''""", bom_no)]
- bom_list, count = [self.doc.name], 0
+ count = 0
+ if self.doc.name not in bom_list:
+ bom_list.append(self.doc.name)
+
while(count < len(bom_list)):
for child_bom in _get_children(bom_list[count]):
if child_bom not in bom_list:
@@ -325,52 +330,50 @@
def get_exploded_items(self):
""" Get all raw materials including items from child bom"""
- self.cur_exploded_items = []
+ self.cur_exploded_items = {}
for d in getlist(self.doclist, 'bom_materials'):
if d.bom_no:
self.get_child_exploded_items(d.bom_no, d.qty)
else:
- self.cur_exploded_items.append({
+ self.add_to_cur_exploded_items(webnotes._dict({
'item_code' : d.item_code,
'description' : d.description,
'stock_uom' : d.stock_uom,
'qty' : flt(d.qty),
- 'rate' : flt(d.rate),
- 'amount' : flt(d.amount),
- 'parent_bom' : d.parent,
- 'mat_detail_no' : d.name,
- 'qty_consumed_per_unit' : flt(d.qty_consumed_per_unit)
- })
+ 'rate' : flt(d.rate),
+ }))
+
+ def add_to_cur_exploded_items(self, args):
+ if self.cur_exploded_items.get(args.item_code):
+ self.cur_exploded_items[args.item_code]["qty"] += args.qty
+ else:
+ self.cur_exploded_items[args.item_code] = args
def get_child_exploded_items(self, bom_no, qty):
""" Add all items from Flat BOM of child BOM"""
child_fb_items = sql("""select item_code, description, stock_uom, qty, rate,
- amount, parent_bom, mat_detail_no, qty_consumed_per_unit
- from `tabBOM Explosion Item` where parent = '%s' and docstatus = 1""" %
- bom_no, as_dict = 1)
+ qty_consumed_per_unit from `tabBOM Explosion Item`
+ where parent = %s and docstatus = 1""", bom_no, as_dict = 1)
+
for d in child_fb_items:
- self.cur_exploded_items.append({
+ self.add_to_cur_exploded_items(webnotes._dict({
'item_code' : d['item_code'],
'description' : d['description'],
'stock_uom' : d['stock_uom'],
'qty' : flt(d['qty_consumed_per_unit'])*qty,
- 'rate' : flt(d['rate']),
- 'amount' : flt(d['amount']),
- 'parent_bom' : d['parent_bom'],
- 'mat_detail_no' : d['mat_detail_no'],
- 'qty_consumed_per_unit' : flt(d['qty_consumed_per_unit'])*qty/flt(self.doc.quantity)
-
- })
+ 'rate' : flt(d['rate']),
+ }))
def add_exploded_items(self):
"Add items to Flat BOM table"
self.doclist = self.doc.clear_table(self.doclist, 'flat_bom_details', 1)
for d in self.cur_exploded_items:
- ch = addchild(self.doc, 'flat_bom_details', 'BOM Explosion Item',
- self.doclist)
- for i in d.keys():
- ch.fields[i] = d[i]
+ ch = addchild(self.doc, 'flat_bom_details', 'BOM Explosion Item', self.doclist)
+ for i in self.cur_exploded_items[d].keys():
+ ch.fields[i] = self.cur_exploded_items[d][i]
+ ch.amount = flt(ch.qty) * flt(ch.rate)
+ ch.qty_consumed_per_unit = flt(ch.qty) / flt(self.doc.quantity)
ch.docstatus = self.doc.docstatus
ch.save(1)
diff --git a/manufacturing/doctype/bom/test_bom.py b/manufacturing/doctype/bom/test_bom.py
index e742c0c..cb91e78 100644
--- a/manufacturing/doctype/bom/test_bom.py
+++ b/manufacturing/doctype/bom/test_bom.py
@@ -48,134 +48,4 @@
"stock_uom": "No."
}
]
-]
-
-
-
-# import webnotes.model
-# from webnotes.utils import nowdate, flt
-# from accounts.utils import get_fiscal_year
-# from webnotes.model.doclist import DocList
-# import copy
-#
-# company = webnotes.conn.get_default("company")
-#
-#
-# def load_data():
-#
-# # create default warehouse
-# if not webnotes.conn.exists("Warehouse", "Default Warehouse"):
-# webnotes.insert({"doctype": "Warehouse",
-# "warehouse_name": "Default Warehouse",
-# "warehouse_type": "Stores"})
-#
-# # create UOM: Nos.
-# if not webnotes.conn.exists("UOM", "Nos"):
-# webnotes.insert({"doctype": "UOM", "uom_name": "Nos"})
-#
-# from webnotes.tests import insert_test_data
-# # create item groups and items
-# insert_test_data("Item Group",
-# sort_fn=lambda ig: (ig[0].get('parent_item_group'), ig[0].get('name')))
-# insert_test_data("Item")
-#
-# base_bom_fg = [
-# {"doctype": "BOM", "item": "Android Jack D", "quantity": 1,
-# "is_active": "Yes", "is_default": 1, "uom": "Nos"},
-# {"doctype": "BOM Operation", "operation_no": 1, "parentfield": "bom_operations",
-# "opn_description": "Development", "hour_rate": 10, "time_in_mins": 90},
-# {"doctype": "BOM Item", "item_code": "Home Desktop 300", "operation_no": 1,
-# "qty": 2, "rate": 20, "stock_uom": "Nos", "parentfield": "bom_materials"},
-# {"doctype": "BOM Item", "item_code": "Home Desktop 100", "operation_no": 1,
-# "qty": 1, "rate": 300, "stock_uom": "Nos", "parentfield": "bom_materials"},
-# {"doctype": "BOM Item", "item_code": "Nebula 7", "operation_no": 1,
-# "qty": 5, "stock_uom": "Nos", "parentfield": "bom_materials"},
-# ]
-#
-# base_bom_child = [
-# {"doctype": "BOM", "item": "Nebula 7", "quantity": 5,
-# "is_active": "Yes", "is_default": 1, "uom": "Nos"},
-# {"doctype": "BOM Operation", "operation_no": 1, "parentfield": "bom_operations",
-# "opn_description": "Development"},
-# {"doctype": "BOM Item", "item_code": "Android Jack S", "operation_no": 1,
-# "qty": 10, "stock_uom": "Nos", "parentfield": "bom_materials"}
-# ]
-#
-# base_bom_grandchild = [
-# {"doctype": "BOM", "item": "Android Jack S", "quantity": 1,
-# "is_active": "Yes", "is_default": 1, "uom": "Nos"},
-# {"doctype": "BOM Operation", "operation_no": 1, "parentfield": "bom_operations",
-# "opn_description": "Development"},
-# {"doctype": "BOM Item", "item_code": "Home Desktop 300", "operation_no": 1,
-# "qty": 3, "rate": 10, "stock_uom": "Nos", "parentfield": "bom_materials"}
-# ]
-#
-#
-# class TestPurchaseReceipt(unittest.TestCase):
-# def setUp(self):
-# webnotes.conn.begin()
-# load_data()
-#
-# def test_bom_validation(self):
-# # show throw error bacause bom no missing for sub-assembly item
-# bom_fg = copy.deepcopy(base_bom_fg)
-# self.assertRaises(webnotes.ValidationError, webnotes.insert, DocList(bom_fg))
-#
-# # main item is not a manufacturing item
-# bom_fg = copy.deepcopy(base_bom_fg)
-# bom_fg[0]["item"] = "Home Desktop 200"
-# bom_fg.pop(4)
-# self.assertRaises(webnotes.ValidationError, webnotes.insert, DocList(bom_fg))
-#
-# # operation no mentioed in material table not matching with operation table
-# bom_fg = copy.deepcopy(base_bom_fg)
-# bom_fg.pop(4)
-# bom_fg[2]["operation_no"] = 2
-# self.assertRaises(webnotes.ValidationError, webnotes.insert, DocList(bom_fg))
-#
-#
-# def test_bom(self):
-# gc_wrapper = webnotes.insert(DocList(base_bom_grandchild))
-# gc_wrapper.submit()
-#
-# bom_child = copy.deepcopy(base_bom_child)
-# bom_child[2]["bom_no"] = gc_wrapper.doc.name
-# child_wrapper = webnotes.insert(DocList(bom_child))
-# child_wrapper.submit()
-#
-# bom_fg = copy.deepcopy(base_bom_fg)
-# bom_fg[4]["bom_no"] = child_wrapper.doc.name
-# fg_wrapper = webnotes.insert(DocList(bom_fg))
-# fg_wrapper.load_from_db()
-#
-# self.check_bom_cost(fg_wrapper)
-#
-# self.check_flat_bom(fg_wrapper, child_wrapper, gc_wrapper)
-#
-# def check_bom_cost(self, fg_wrapper):
-# expected_values = {
-# "operating_cost": 15,
-# "raw_material_cost": 640,
-# "total_cost": 655
-# }
-#
-# for key in expected_values:
-# self.assertEqual(flt(expected_values[key]), flt(fg_wrapper.doc.fields.get(key)))
-#
-# def check_flat_bom(self, fg_wrapper, child_wrapper, gc_wrapper):
-# expected_flat_bom_items = {
-# ("Home Desktop 300", fg_wrapper.doc.name): (2, 20),
-# ("Home Desktop 100", fg_wrapper.doc.name): (1, 300),
-# ("Home Desktop 300", gc_wrapper.doc.name): (30, 10)
-# }
-#
-# self.assertEqual(len(fg_wrapper.doclist.get({"parentfield": "flat_bom_details"})), 3)
-#
-# for key, val in expected_flat_bom_items.items():
-# flat_bom = fg_wrapper.doclist.get({"parentfield": "flat_bom_details",
-# "item_code": key[0], "parent_bom": key[1]})[0]
-# self.assertEqual(val, (flat_bom.qty, flat_bom.rate))
-#
-#
-# def tearDown(self):
-# webnotes.conn.rollback()
\ No newline at end of file
+]
\ No newline at end of file
diff --git a/manufacturing/doctype/bom_explosion_item/bom_explosion_item.txt b/manufacturing/doctype/bom_explosion_item/bom_explosion_item.txt
index 07aad7d..3808cdf 100644
--- a/manufacturing/doctype/bom_explosion_item/bom_explosion_item.txt
+++ b/manufacturing/doctype/bom_explosion_item/bom_explosion_item.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-02-22 01:27:48",
+ "creation": "2013-03-07 11:42:57",
"docstatus": 0,
- "modified": "2013-03-07 07:03:18",
+ "modified": "2013-06-04 13:13:28",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -82,25 +82,6 @@
},
{
"doctype": "DocField",
- "fieldname": "parent_bom",
- "fieldtype": "Link",
- "hidden": 0,
- "label": "Parent BOM",
- "oldfieldname": "parent_bom",
- "oldfieldtype": "Link",
- "options": "BOM",
- "print_width": "250px",
- "width": "250px"
- },
- {
- "doctype": "DocField",
- "fieldname": "mat_detail_no",
- "fieldtype": "Data",
- "hidden": 1,
- "label": "Mat Detail No"
- },
- {
- "doctype": "DocField",
"fieldname": "qty_consumed_per_unit",
"fieldtype": "Float",
"hidden": 0,
diff --git a/manufacturing/doctype/bom_replace_tool/bom_replace_tool.py b/manufacturing/doctype/bom_replace_tool/bom_replace_tool.py
index 4c9c42d..e69c487 100644
--- a/manufacturing/doctype/bom_replace_tool/bom_replace_tool.py
+++ b/manufacturing/doctype/bom_replace_tool/bom_replace_tool.py
@@ -29,9 +29,10 @@
self.validate_bom()
self.update_new_bom()
bom_list = self.get_parent_boms()
+ updated_bom = []
for bom in bom_list:
bom_obj = get_obj("BOM", bom, with_children=1)
- bom_obj.update_cost_and_exploded_items()
+ updated_bom = bom_obj.update_cost_and_exploded_items(updated_bom)
webnotes.msgprint(_("BOM replaced"))
diff --git a/manufacturing/doctype/production_planning_tool/production_planning_tool.py b/manufacturing/doctype/production_planning_tool/production_planning_tool.py
index d4e41ac..ed7f7bf 100644
--- a/manufacturing/doctype/production_planning_tool/production_planning_tool.py
+++ b/manufacturing/doctype/production_planning_tool/production_planning_tool.py
@@ -241,40 +241,30 @@
def get_raw_materials(self, bom_dict):
""" Get raw materials considering sub-assembly items
{
- "item_code": [qty_required, description, stock_uom]
+ "item_code": [qty_required, description, stock_uom, min_order_qty]
}
"""
for bom in bom_dict:
if self.doc.use_multi_level_bom:
# get all raw materials with sub assembly childs
- fl_bom_items = sql("""
- select
- item_code,ifnull(sum(qty_consumed_per_unit),0)*%s as qty,
- description, stock_uom, min_order_qty
- from
- (
- select distinct fb.name, fb.description, fb.item_code,
- fb.qty_consumed_per_unit, fb.stock_uom, it.min_order_qty
- from `tabBOM Explosion Item` fb,`tabItem` it
- where it.name = fb.item_code
- and ifnull(it.is_pro_applicable, 'No') = 'No'
- and ifnull(it.is_sub_contracted_item, 'No') = 'No'
- and fb.docstatus<2 and fb.parent=%s
- ) a
- group by item_code,stock_uom
- """ , (flt(bom_dict[bom]), bom))
+ fl_bom_items = sql("""select fb.item_code,
+ ifnull(sum(fb.qty_consumed_per_unit), 0)*%s as qty,
+ fb.description, fb.stock_uom, it.min_order_qty
+ from `tabBOM Explosion Item` fb,`tabItem` it
+ where it.name = fb.item_code and ifnull(it.is_pro_applicable, 'No') = 'No'
+ and ifnull(it.is_sub_contracted_item, 'No') = 'No'
+ and fb.docstatus<2 and fb.parent=%s
+ group by item_code, stock_uom""", (flt(bom_dict[bom]), bom))
else:
# Get all raw materials considering SA items as raw materials,
# so no childs of SA items
- fl_bom_items = sql("""
- select bom_item.item_code,
+ fl_bom_items = sql("""select bom_item.item_code,
ifnull(sum(bom_item.qty_consumed_per_unit), 0) * %s,
bom_item.description, bom_item.stock_uom, item.min_order_qty
from `tabBOM Item` bom_item, tabItem item
where bom_item.parent = %s and bom_item.docstatus < 2
- and bom_item.item_code = item.name
- group by item_code
- """, (flt(bom_dict[bom]), bom))
+ and bom_item.item_code = item.name
+ group by item_code""", (flt(bom_dict[bom]), bom))
self.make_items_dict(fl_bom_items)
def make_items_dict(self, item_list):
diff --git a/patches/june_2013/__init__.py b/patches/june_2013/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/patches/june_2013/__init__.py
diff --git a/patches/june_2013/p01_update_bom_exploded_items.py b/patches/june_2013/p01_update_bom_exploded_items.py
new file mode 100644
index 0000000..eff0931
--- /dev/null
+++ b/patches/june_2013/p01_update_bom_exploded_items.py
@@ -0,0 +1,29 @@
+# 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():
+ updated_bom = []
+ for bom in webnotes.conn.sql("select name from tabBOM where docstatus < 2"):
+ if bom[0] not in updated_bom:
+ try:
+ bom_obj = webnotes.get_obj("BOM", bom[0], with_children=1)
+ updated_bom = bom_obj.update_cost_and_exploded_items(updated_bom)
+ webnotes.conn.commit()
+ except:
+ pass
\ No newline at end of file
diff --git a/patches/patch_list.py b/patches/patch_list.py
index ea61a04..d8b84da 100644
--- a/patches/patch_list.py
+++ b/patches/patch_list.py
@@ -252,4 +252,6 @@
"patches.may_2013.p03_update_support_ticket",
"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
diff --git a/stock/doctype/stock_entry/stock_entry.py b/stock/doctype/stock_entry/stock_entry.py
index bce0f62..522a14a 100644
--- a/stock/doctype/stock_entry/stock_entry.py
+++ b/stock/doctype/stock_entry/stock_entry.py
@@ -506,17 +506,13 @@
if self.doc.use_multi_level_bom:
# get all raw materials with sub assembly childs
- fl_bom_sa_child_item = sql("""select
- item_code,ifnull(sum(qty_consumed_per_unit),0)*%s as qty,
- description,stock_uom
- from ( select distinct fb.name, fb.description, fb.item_code,
- fb.qty_consumed_per_unit, fb.stock_uom
- from `tabBOM Explosion Item` fb,`tabItem` it
- where it.name = fb.item_code and ifnull(it.is_pro_applicable, 'No') = 'No'
- and ifnull(it.is_sub_contracted_item, 'No') = 'No' and fb.docstatus<2
- and fb.parent=%s
- ) a
- group by item_code, stock_uom""" , (qty, self.doc.bom_no), as_dict=1)
+ fl_bom_sa_child_item = sql("""select fb.item_code,
+ ifnull(sum(fb.qty_consumed_per_unit),0)*%s as qty, fb.description, fb.stock_uom
+ from `tabBOM Explosion Item` fb,`tabItem` it
+ where it.name = fb.item_code and ifnull(it.is_pro_applicable, 'No') = 'No'
+ and ifnull(it.is_sub_contracted_item, 'No') = 'No' and fb.docstatus < 2
+ and fb.parent=%s group by item_code, stock_uom""",
+ (qty, self.doc.bom_no), as_dict=1)
if fl_bom_sa_child_item:
_make_items_dict(fl_bom_sa_child_item)
@@ -524,10 +520,10 @@
# Get all raw materials considering multi level BOM,
# if multi level bom consider childs of Sub-Assembly items
fl_bom_sa_items = sql("""select item_code,
- ifnull(sum(qty_consumed_per_unit), 0) * '%s' as qty,
+ ifnull(sum(qty_consumed_per_unit), 0) *%s as qty,
description, stock_uom from `tabBOM Item`
- where parent = '%s' and docstatus < 2
- group by item_code""" % (qty, self.doc.bom_no), as_dict=1)
+ where parent = %s and docstatus < 2
+ group by item_code""", (qty, self.doc.bom_no), as_dict=1)
if fl_bom_sa_items:
_make_items_dict(fl_bom_sa_items)
diff --git a/stock/page/stock_home/stock_home.js b/stock/page/stock_home/stock_home.js
index 7e67cab..d8c63aa 100644
--- a/stock/page/stock_home/stock_home.js
+++ b/stock/page/stock_home/stock_home.js
@@ -206,6 +206,19 @@
route: "query-report/Requested Items To Be Transferred",
},
{
+ "label":wn._("Batch-Wise Balance History"),
+ route: "query-report/Batch-Wise Balance History",
+ },
+ {
+ "label":wn._("Warehouse-Wise Stock Balance"),
+ route: "query-report/Warehouse-Wise Stock Balance",
+ },
+ {
+ "label":wn._("Item Prices"),
+ route: "query-report/Item Prices",
+
+ },
+ {
"label":wn._("Itemwise Recommended Reorder Level"),
route: "query-report/Itemwise Recommended Reorder Level",
doctype: "Item"
diff --git a/stock/report/batch_wise_balance_history/__init__.py b/stock/report/batch_wise_balance_history/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/__init__.py
diff --git a/stock/report/batch_wise_balance_history/batch_wise_balance_history.js b/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
new file mode 100644
index 0000000..0ba1938
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
@@ -0,0 +1,39 @@
+wn.query_reports["Batch-Wise Balance History"] = {
+ "filters": [
+ {
+ "fieldname":"item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": "80"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": "Warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": "80"
+ },
+ {
+ "fieldname":"batch_no",
+ "label": "Batch",
+ "fieldtype": "Link",
+ "options": "Batch",
+ "width": "80"
+ },
+ {
+ "fieldname":"from_date",
+ "label": "From Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": sys_defaults.year_start_date,
+ },
+ {
+ "fieldname":"to_date",
+ "label": "To Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": wn.datetime.get_today()
+ }
+ ]
+}
\ No newline at end of file
diff --git a/stock/report/batch_wise_balance_history/batch_wise_balance_history.py b/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
new file mode 100644
index 0000000..ca3e775
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
@@ -0,0 +1,109 @@
+# 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(filters)
+ item_map = get_item_details(filters)
+ iwb_map = get_item_warehouse_batch_map(filters)
+
+ data = []
+ for item in sorted(iwb_map):
+ for wh in sorted(iwb_map[item]):
+ for batch in sorted(iwb_map[item][wh]):
+ qty_dict = iwb_map[item][wh][batch]
+ data.append([item, item_map[item]["item_name"],
+ item_map[item]["description"], wh, batch,
+ qty_dict.opening_qty, qty_dict.in_qty,
+ qty_dict.out_qty, qty_dict.bal_qty
+ ])
+
+ return columns, data
+
+def get_columns(filters):
+ """return columns based on filters"""
+
+ columns = ["Item:Link/Item:100"] + ["Item Name::150"] + ["Description::150"] + \
+ ["Warehouse:Link/Warehouse:100"] + ["Batch:Link/Batch:100"] + ["Opening Qty::90"] + \
+ ["In Qty::80"] + ["Out Qty::80"] + ["Balance Qty::90"]
+
+ return columns
+
+def get_conditions(filters):
+ conditions = ""
+ if filters.get("item_code"):
+ conditions += " and item_code='%s'" % filters["item_code"]
+
+ if filters.get("warehouse"):
+ conditions += " and warehouse='%s'" % filters["warehouse"]
+
+ if filters.get("batch_no"):
+ conditions += " and batch_no='%s'" % filters["batch_no"]
+
+ if not filters.get("from_date"):
+ webnotes.msgprint("Please enter From Date", raise_exception=1)
+
+ if filters.get("to_date"):
+ conditions += " and posting_date <= '%s'" % filters["to_date"]
+ else:
+ webnotes.msgprint("Please enter To Date", raise_exception=1)
+
+ return conditions
+
+#get all details
+def get_stock_ledger_entries(filters):
+ conditions = get_conditions(filters)
+ return webnotes.conn.sql("""select item_code, batch_no, warehouse,
+ posting_date, actual_qty
+ from `tabStock Ledger Entry`
+ where ifnull(is_cancelled, 'No') = 'No' %s order by item_code, warehouse""" %
+ conditions, as_dict=1)
+
+def get_item_warehouse_batch_map(filters):
+ sle = get_stock_ledger_entries(filters)
+ iwb_map = {}
+
+ for d in sle:
+ iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, {})\
+ .setdefault(d.batch_no, webnotes._dict({
+ "opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
+ }))
+ qty_dict = iwb_map[d.item_code][d.warehouse][d.batch_no]
+ if d.posting_date < filters["from_date"]:
+ qty_dict.opening_qty += flt(d.actual_qty)
+ elif d.posting_date >= filters["from_date"] and d.posting_date <= filters["to_date"]:
+ if flt(d.actual_qty) > 0:
+ qty_dict.in_qty += flt(d.actual_qty)
+ else:
+ qty_dict.out_qty += abs(flt(d.actual_qty))
+
+ qty_dict.bal_qty += flt(d.actual_qty)
+
+ return iwb_map
+
+def get_item_details(filters):
+ if filters.get("item_code"):
+ conditions = " and name = '%s'" % filters["item_code"]
+ item_map = {}
+ for d in webnotes.conn.sql("select name, item_name, description from tabItem", as_dict=1):
+ item_map.setdefault(d.name, d)
+
+ return item_map
\ No newline at end of file
diff --git a/stock/report/batch_wise_balance_history/batch_wise_balance_history.txt b/stock/report/batch_wise_balance_history/batch_wise_balance_history.txt
new file mode 100644
index 0000000..9e795b9
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-04 11:03:47",
+ "docstatus": 0,
+ "modified": "2013-06-04 19:32:27",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Batch-Wise Balance History",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Batch-Wise Balance History"
+ }
+]
\ No newline at end of file
diff --git a/stock/report/item_prices/__init__.py b/stock/report/item_prices/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/item_prices/__init__.py
diff --git a/stock/report/item_prices/item_prices.py b/stock/report/item_prices/item_prices.py
new file mode 100644
index 0000000..ea0be47
--- /dev/null
+++ b/stock/report/item_prices/item_prices.py
@@ -0,0 +1,106 @@
+# 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(filters)
+ item_map = get_item_details()
+ pl = get_price_list()
+ bom_rate = get_item_bom_rate()
+ val_rate_map = get_valuation_rate()
+
+ data = []
+ for item in sorted(item_map):
+ data.append([item, item_map[item]["item_name"],
+ item_map[item]["description"], item_map[item]["stock_uom"],
+ flt(item_map[item]["last_purchase_rate"]), val_rate_map.get(item, 0),
+ pl.get(item, {}).get("selling"), pl.get(item, {}).get("buying"),
+ bom_rate.get(item, 0), flt(item_map[item]["standard_rate"])
+ ])
+
+ return columns, data
+
+def get_columns(filters):
+ """return columns based on filters"""
+
+ columns = ["Item:Link/Item:100", "Item Name::150", "Description::150", "UOM:Link/UOM:80",
+ "Last Purchase Rate:Currency:90", "Valuation Rate:Currency:80", "Sales Price List::80",
+ "Purchase Price List::80", "BOM Rate:Currency:90", "Standard Rate:Currency:100"]
+
+ return columns
+
+def get_item_details():
+ """returns all items details"""
+
+ item_map = {}
+
+ for i in webnotes.conn.sql("select name, item_name, description, \
+ stock_uom, standard_rate, last_purchase_rate from tabItem \
+ order by item_code", as_dict=1):
+ item_map.setdefault(i.name, i)
+
+ return item_map
+
+def get_price_list():
+ """Get selling & buying price list of every item"""
+
+ rate = {}
+
+ price_list = webnotes.conn.sql("""select parent, selling, buying,
+ concat(price_list_name, " - ", ref_currency, " ", ref_rate) as price
+ from `tabItem Price` where docstatus<2""", as_dict=1)
+
+ for j in price_list:
+ if j.selling:
+ rate.setdefault(j.parent, {}).setdefault("selling", []).append(j.price)
+ if j.buying:
+ rate.setdefault(j.parent, {}).setdefault("buying", []).append(j.price)
+
+ item_rate_map = {}
+
+ for item in rate:
+ item_rate_map.setdefault(item, {}).setdefault("selling",
+ ", ".join(rate[item].get("selling", [])))
+ item_rate_map[item]["buying"] = ", ".join(rate[item].get("buying", []))
+
+ return item_rate_map
+
+def get_item_bom_rate():
+ """Get BOM rate of an item from BOM"""
+
+ bom_map = {}
+
+ for b in webnotes.conn.sql("""select item, (total_cost/quantity) as bom_rate
+ from `tabBOM` where is_active=1 and is_default=1""", as_dict=1):
+ bom_map.setdefault(b.item, flt(b.bom_rate))
+
+ return bom_map
+
+def get_valuation_rate():
+ """Get an average valuation rate of an item from all warehouses"""
+
+ val_rate_map = {}
+
+ for d in webnotes.conn.sql("""select item_code, avg(valuation_rate) as val_rate
+ from tabBin group by item_code""", as_dict=1):
+ val_rate_map.setdefault(d.item_code, d.val_rate)
+
+ return val_rate_map
\ No newline at end of file
diff --git a/stock/report/item_prices/item_prices.txt b/stock/report/item_prices/item_prices.txt
new file mode 100644
index 0000000..4c49ca1
--- /dev/null
+++ b/stock/report/item_prices/item_prices.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-05 11:43:30",
+ "docstatus": 0,
+ "modified": "2013-06-05 11:43:30",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Item Prices",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Item Prices"
+ }
+]
\ No newline at end of file
diff --git a/stock/report/warehouse_wise_stock_balance/__init__.py b/stock/report/warehouse_wise_stock_balance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/__init__.py
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js
new file mode 100644
index 0000000..5e1eb3a
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js
@@ -0,0 +1,32 @@
+wn.query_reports["Warehouse-Wise Stock Balance"] = {
+ "filters": [
+ {
+ "fieldname":"item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": "80"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": "Warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": "80"
+ },
+ {
+ "fieldname":"from_date",
+ "label": "From Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": sys_defaults.year_start_date,
+ },
+ {
+ "fieldname":"to_date",
+ "label": "To Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": wn.datetime.get_today()
+ }
+ ]
+}
\ No newline at end of file
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
new file mode 100644
index 0000000..324bbe3
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
@@ -0,0 +1,104 @@
+# 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(filters)
+ item_map = get_item_details(filters)
+ iwb_map = get_item_warehouse_map(filters)
+
+ data = []
+ for item in sorted(iwb_map):
+ for wh in sorted(iwb_map[item]):
+ qty_dict = iwb_map[item][wh]
+ data.append([item, item_map[item]["item_name"],
+ item_map[item]["description"], wh,
+ qty_dict.opening_qty, qty_dict.in_qty,
+ qty_dict.out_qty, qty_dict.bal_qty
+ ])
+
+ return columns, data
+
+def get_columns(filters):
+ """return columns based on filters"""
+
+ columns = ["Item:Link/Item:100"] + ["Item Name::150"] + ["Description::150"] + \
+ ["Warehouse:Link/Warehouse:100"] + ["Opening Qty::90"] + \
+ ["In Qty::80"] + ["Out Qty::80"] + ["Balance Qty::90"]
+
+ return columns
+
+def get_conditions(filters):
+ conditions = ""
+ if filters.get("item_code"):
+ conditions += " and item_code='%s'" % filters["item_code"]
+
+ if filters.get("warehouse"):
+ conditions += " and warehouse='%s'" % filters["warehouse"]
+
+ if not filters.get("from_date"):
+ webnotes.msgprint("Please enter From Date", raise_exception=1)
+
+ if filters.get("to_date"):
+ conditions += " and posting_date <= '%s'" % filters["to_date"]
+ else:
+ webnotes.msgprint("Please enter To Date", raise_exception=1)
+
+ return conditions
+
+#get all details
+def get_stock_ledger_entries(filters):
+ conditions = get_conditions(filters)
+ return webnotes.conn.sql("""select item_code, warehouse,
+ posting_date, actual_qty
+ from `tabStock Ledger Entry`
+ where ifnull(is_cancelled, 'No') = 'No' %s order by item_code, warehouse""" %
+ conditions, as_dict=1)
+
+def get_item_warehouse_map(filters):
+ sle = get_stock_ledger_entries(filters)
+ iwb_map = {}
+
+ for d in sle:
+ iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, webnotes._dict({\
+ "opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
+ }))
+ qty_dict = iwb_map[d.item_code][d.warehouse]
+ if d.posting_date < filters["from_date"]:
+ qty_dict.opening_qty += flt(d.actual_qty)
+ elif d.posting_date >= filters["from_date"] and d.posting_date <= filters["to_date"]:
+ if flt(d.actual_qty) > 0:
+ qty_dict.in_qty += flt(d.actual_qty)
+ else:
+ qty_dict.out_qty += abs(flt(d.actual_qty))
+
+ qty_dict.bal_qty += flt(d.actual_qty)
+
+ return iwb_map
+
+def get_item_details(filters):
+ if filters.get("item_code"):
+ conditions = " and name = '%s'" % filters["item_code"]
+ item_map = {}
+ for d in webnotes.conn.sql("select name, item_name, description from tabItem", as_dict=1):
+ item_map.setdefault(d.name, d)
+
+ return item_map
\ No newline at end of file
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt
new file mode 100644
index 0000000..2513587
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-05 11:00:31",
+ "docstatus": 0,
+ "modified": "2013-06-05 11:00:31",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Warehouse-Wise Stock Balance",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Warehouse-Wise Stock Balance"
+ }
+]
\ No newline at end of file