item-wise price list rate, editable report
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
index 7361040..1dda6c7 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
@@ -19,10 +19,9 @@
webnotes.conn.get_value("Account", self.doc.account, "debit_or_credit").lower() or ""
def get_voucher_details(self):
-
total_amount = webnotes.conn.sql("""select sum(%s) from `tabGL Entry`
where voucher_type = %s and voucher_no = %s
- and account = %s and ifnull(is_cancelled, 'No') = 'No'""" %
+ and account = %s""" %
(self.doc.account_type, '%s', '%s', '%s'),
(self.doc.voucher_type, self.doc.voucher_no, self.doc.account))
diff --git a/accounts/report/gross_profit/gross_profit.py b/accounts/report/gross_profit/gross_profit.py
index 79e389e..d9c20d5 100644
--- a/accounts/report/gross_profit/gross_profit.py
+++ b/accounts/report/gross_profit/gross_profit.py
@@ -52,8 +52,7 @@
query = """select item_code, voucher_type, voucher_no,
voucher_detail_no, posting_date, posting_time, stock_value,
warehouse, actual_qty as qty
- from `tabStock Ledger Entry`
- where ifnull(`is_cancelled`, 'No') = No'"""
+ from `tabStock Ledger Entry`"""
if filters.get("company"):
query += """ and company=%(company)s"""
diff --git a/accounts/utils.py b/accounts/utils.py
index 2e28254..5c6c16b 100644
--- a/accounts/utils.py
+++ b/accounts/utils.py
@@ -268,17 +268,15 @@
if not posting_date: posting_date = nowdate()
- account_warehouse_map = {}
difference = {}
- warehouse_account = webnotes.conn.sql("""select name, account from tabWarehouse
- where account in (%s)""" % ', '.join(['%s']*len(account_list)), account_list, as_dict=1)
-
- for wh in warehouse_account:
- account_warehouse_map.setdefault(wh.account, []).append(wh.name)
+
+ account_warehouse = dict(webnotes.conn.sql("""select name, master_name from tabAccount
+ where account_type = 'Warehouse' and ifnull(master_name, '') != ''
+ and name in (%s)""" % ', '.join(['%s']*len(account_list)), account_list))
- for account, warehouse_list in account_warehouse_map.items():
+ for account, warehouse in account_warehouse.items():
account_balance = get_balance_on(account, posting_date)
- stock_value = get_stock_balance_on(warehouse_list, posting_date)
+ stock_value = get_stock_balance_on(warehouse, posting_date)
if abs(flt(stock_value) - flt(account_balance)) > 0.005:
difference.setdefault(account, flt(stock_value) - flt(account_balance))
diff --git a/stock/report/item_wise_price_list/__init__.py b/setup/report/__init__.py
similarity index 100%
copy from stock/report/item_wise_price_list/__init__.py
copy to setup/report/__init__.py
diff --git a/stock/report/item_wise_price_list/__init__.py b/setup/report/item_wise_price_list/__init__.py
similarity index 100%
rename from stock/report/item_wise_price_list/__init__.py
rename to setup/report/item_wise_price_list/__init__.py
diff --git a/setup/report/item_wise_price_list/item_wise_price_list.txt b/setup/report/item_wise_price_list/item_wise_price_list.txt
new file mode 100644
index 0000000..44118c8
--- /dev/null
+++ b/setup/report/item_wise_price_list/item_wise_price_list.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-09-25 10:29:04",
+ "docstatus": 0,
+ "modified": "2013-09-25 10:29:04",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "json": "{\"filters\":[[\"Item Price\",\"item_code\",\"like\",\"%\"],[\"Price List\",\"price_list_name\",\"like\",\"%\"]],\"columns\":[[\"item_code\",\"Item Price\"],[\"price_list_name\",\"Price List\"],[\"currency\",\"Price List\"],[\"ref_rate\",\"Item Price\"],[\"buying_or_selling\",\"Price List\"],[\"name\",\"Price List\"]],\"sort_by\":\"Price List.modified\",\"sort_order\":\"desc\",\"sort_by_next\":\"\",\"sort_order_next\":\"desc\"}",
+ "name": "__common__",
+ "ref_doctype": "Price List",
+ "report_name": "Item-Wise Price List",
+ "report_type": "Report Builder"
+ },
+ {
+ "doctype": "Report",
+ "name": "Item-Wise Price List"
+ }
+]
\ No newline at end of file
diff --git a/stock/report/item_wise_price_list/__init__.py b/setup/report/item_wise_price_list_rate/__init__.py
similarity index 100%
copy from stock/report/item_wise_price_list/__init__.py
copy to setup/report/item_wise_price_list_rate/__init__.py
diff --git a/setup/report/item_wise_price_list_rate/item_wise_price_list_rate.txt b/setup/report/item_wise_price_list_rate/item_wise_price_list_rate.txt
new file mode 100644
index 0000000..08b1bef
--- /dev/null
+++ b/setup/report/item_wise_price_list_rate/item_wise_price_list_rate.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-09-25 10:21:15",
+ "docstatus": 0,
+ "modified": "2013-09-25 10:24:57",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "json": "{\"filters\":[[\"Item Price\",\"item_code\",\"like\",\"%\"],[\"Price List\",\"price_list_name\",\"like\",\"%\"]],\"columns\":[[\"item_code\",\"Item Price\"],[\"price_list_name\",\"Price List\"],[\"currency\",\"Price List\"],[\"ref_rate\",\"Item Price\"],[\"buying_or_selling\",\"Price List\"],[\"name\",\"Price List\"]],\"sort_by\":\"Price List.modified\",\"sort_order\":\"desc\",\"sort_by_next\":\"\",\"sort_order_next\":\"desc\"}",
+ "name": "__common__",
+ "ref_doctype": "Price List",
+ "report_name": "Item-wise Price List Rate",
+ "report_type": "Report Builder"
+ },
+ {
+ "doctype": "Report",
+ "name": "Item-wise Price List Rate"
+ }
+]
\ No newline at end of file
diff --git a/stock/doctype/item/item.py b/stock/doctype/item/item.py
index 7d6ecff..aedb71c 100644
--- a/stock/doctype/item/item.py
+++ b/stock/doctype/item/item.py
@@ -67,7 +67,7 @@
if not self.doc.fields.get("__islocal"):
matched=True
ref_uom = webnotes.conn.get_value("Stock Ledger Entry",
- {"item_code": self.doc.name, "is_cancelled": "No"}, "stock_uom")
+ {"item_code": self.doc.name}, "stock_uom")
if ref_uom:
if cstr(ref_uom) != cstr(self.doc.stock_uom):
matched = False
diff --git a/stock/doctype/purchase_receipt/test_purchase_receipt.py b/stock/doctype/purchase_receipt/test_purchase_receipt.py
index b4c7cc6..010c29b 100644
--- a/stock/doctype/purchase_receipt/test_purchase_receipt.py
+++ b/stock/doctype/purchase_receipt/test_purchase_receipt.py
@@ -7,8 +7,6 @@
import webnotes
import webnotes.defaults
from webnotes.utils import cint
-from accounts.utils import get_stock_and_account_difference
-
class TestPurchaseReceipt(unittest.TestCase):
def test_make_purchase_invoice(self):
diff --git a/stock/doctype/serial_no/test_serial_no.py b/stock/doctype/serial_no/test_serial_no.py
index e452cd3..de82449 100644
--- a/stock/doctype/serial_no/test_serial_no.py
+++ b/stock/doctype/serial_no/test_serial_no.py
@@ -6,7 +6,6 @@
from __future__ import unicode_literals
import webnotes, unittest
-from accounts.utils import get_stock_and_account_difference
test_dependencies = ["Item"]
test_records = []
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.py b/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 38247bb..465edc4 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -22,7 +22,6 @@
def on_submit(self):
self.insert_stock_ledger_entries()
- self.set_stock_value_difference()
self.make_gl_entries()
def on_cancel(self):
@@ -275,26 +274,6 @@
"posting_time": self.doc.posting_time
})
- def set_stock_value_difference(self):
- """stock_value_difference is the increment in the stock value"""
- from stock.utils import get_buying_amount
-
- item_list = [d.item_code for d in self.entries]
- warehouse_list = [d.warehouse for d in self.entries]
- if not (item_list and warehouse_list):
- webnotes.throw(_("Invalid Item or Warehouse Data"))
-
- stock_ledger_entries = self.get_stock_ledger_entries(item_list, warehouse_list)
-
- stock_value_difference = {}
- for d in self.entries:
- diff = get_buying_amount(self.doc.doctype, self.doc.name, d.voucher_detail_no,
- stock_ledger_entries.get((d.item_code, d.warehouse), []))
- stock_value_difference.setdefault(d.warehouse, 0.0)
- stock_value_difference[d.warehouse] -= diff
-
- webnotes.conn.set(self.doc, "stock_value_difference", json.dumps(stock_value_difference))
-
def get_gl_entries_for_stock(self, warehouse_account=None):
if not self.doc.cost_center:
msgprint(_("Please enter Cost Center"), raise_exception=1)
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.txt b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
index e5b1b74..c9959d3 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.txt
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
@@ -2,7 +2,7 @@
{
"creation": "2013-03-28 10:35:31",
"docstatus": 0,
- "modified": "2013-08-29 16:46:33",
+ "modified": "2013-09-24 15:35:12",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -156,15 +156,6 @@
"read_only": 1
},
{
- "doctype": "DocField",
- "fieldname": "stock_value_difference",
- "fieldtype": "Long Text",
- "hidden": 1,
- "in_list_view": 1,
- "label": "Stock Value Difference",
- "print_hide": 1
- },
- {
"doctype": "DocPerm"
}
]
\ No newline at end of file
diff --git a/stock/page/stock_home/stock_home.js b/stock/page/stock_home/stock_home.js
index 1cab754..63db608 100644
--- a/stock/page/stock_home/stock_home.js
+++ b/stock/page/stock_home/stock_home.js
@@ -201,9 +201,9 @@
doctype: "Serial No"
},
{
- "label":wn._("Item-Wise Price List"),
- route: "query-report/Item-Wise Price List",
- doctype: "Item"
+ "label":wn._("Item-wise Price List Rate"),
+ route: "Report/Price List/Item-Wise Price List",
+ doctype: "Price List"
},
{
"label":wn._("Purchase In Transit"),
diff --git a/stock/report/item_wise_price_list/item_wise_price_list.txt b/stock/report/item_wise_price_list/item_wise_price_list.txt
deleted file mode 100644
index b3d5717..0000000
--- a/stock/report/item_wise_price_list/item_wise_price_list.txt
+++ /dev/null
@@ -1,22 +0,0 @@
-[
- {
- "creation": "2013-02-22 18:01:55",
- "docstatus": 0,
- "modified": "2013-09-10 15:50:26",
- "modified_by": "Administrator",
- "owner": "Administrator"
- },
- {
- "doctype": "Report",
- "is_standard": "Yes",
- "name": "__common__",
- "query": "select\n item.name as \"ID:Link/Item:120\", \n item.item_name as \"Item Name::120\", \n item_price.parent as \"Price List::80\",\n price_list.currency as \"Currency::40\", \n item_price.ref_rate as \"Rate:Float:80\",\n item.description as \"Description::160\",\n item.item_group as \"Item Group:Link/Item Group:100\",\n item.brand as \"Brand::100\"\nfrom `tabItem` item, `tabItem Price` item_price, `tabPrice List` price_list\nwhere\n item_price.item_code = item.name and\n item_price.parent = price_list.name",
- "ref_doctype": "Item",
- "report_name": "Item-Wise Price List",
- "report_type": "Query Report"
- },
- {
- "doctype": "Report",
- "name": "Item-Wise Price List"
- }
-]
\ No newline at end of file
diff --git a/stock/utils.py b/stock/utils.py
index 17149fa..8836c6c 100644
--- a/stock/utils.py
+++ b/stock/utils.py
@@ -10,26 +10,24 @@
class UserNotAllowedForWarehouse(webnotes.ValidationError): pass
-def get_stock_balance_on(warehouse_list, posting_date=None):
+def get_stock_balance_on(warehouse, posting_date=None):
if not posting_date: posting_date = nowdate()
stock_ledger_entries = webnotes.conn.sql("""
SELECT
- item_code, warehouse, stock_value
+ item_code, stock_value
FROM
`tabStock Ledger Entry`
WHERE
- warehouse in (%s)
- AND posting_date <= %s
+ warehouse=%s AND posting_date <= %s
ORDER BY timestamp(posting_date, posting_time) DESC, name DESC
- """ % (', '.join(['%s']*len(warehouse_list)), '%s'),
- tuple(warehouse_list + [posting_date]), as_dict=1)
+ """, (warehouse, posting_date), as_dict=1)
sle_map = {}
for sle in stock_ledger_entries:
- sle_map.setdefault(sle.warehouse, {}).setdefault(sle.item_code, flt(sle.stock_value))
+ sle_map.setdefault(sle.item_code, flt(sle.stock_value))
- return sum([sum(item_dict.values()) for item_dict in sle_map.values()])
+ return sum(sle_map.values())
def get_latest_stock_balance():
bin_map = {}