Merge branch 'develop' into item-group-filters-listing
diff --git a/erpnext/buying/report/requested_items_to_order_and_receive/requested_items_to_order_and_receive.py b/erpnext/buying/report/requested_items_to_order_and_receive/requested_items_to_order_and_receive.py
index f98e5f1..60a8f92 100644
--- a/erpnext/buying/report/requested_items_to_order_and_receive/requested_items_to_order_and_receive.py
+++ b/erpnext/buying/report/requested_items_to_order_and_receive/requested_items_to_order_and_receive.py
@@ -6,6 +6,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Coalesce, Sum
from frappe.utils import date_diff, flt, getdate
@@ -16,12 +17,9 @@
validate_filters(filters)
columns = get_columns(filters)
- conditions = get_conditions(filters)
+ data = get_data(filters)
- #get queried data
- data = get_data(filters, conditions)
-
- #prepare data for report and chart views
+ # prepare data for report and chart views
data, chart_data = prepare_data(data, filters)
return columns, data, None, chart_data
@@ -34,53 +32,70 @@
elif date_diff(to_date, from_date) < 0:
frappe.throw(_("To Date cannot be before From Date."))
-def get_conditions(filters):
- conditions = ''
+def get_data(filters):
+ mr = frappe.qb.DocType("Material Request")
+ mr_item = frappe.qb.DocType("Material Request Item")
+ query = (
+ frappe.qb.from_(mr)
+ .join(mr_item).on(mr_item.parent == mr.name)
+ .select(
+ mr.name.as_("material_request"),
+ mr.transaction_date.as_("date"),
+ mr_item.schedule_date.as_("required_date"),
+ mr_item.item_code.as_("item_code"),
+ Sum(Coalesce(mr_item.stock_qty, 0)).as_("qty"),
+ Coalesce(mr_item.stock_uom, '').as_("uom"),
+ Sum(Coalesce(mr_item.ordered_qty, 0)).as_("ordered_qty"),
+ Sum(Coalesce(mr_item.received_qty, 0)).as_("received_qty"),
+ (
+ Sum(Coalesce(mr_item.stock_qty, 0)) - Sum(Coalesce(mr_item.received_qty, 0))
+ ).as_("qty_to_receive"),
+ Sum(Coalesce(mr_item.received_qty, 0)).as_("received_qty"),
+ (
+ Sum(Coalesce(mr_item.stock_qty, 0)) - Sum(Coalesce(mr_item.ordered_qty, 0))
+ ).as_("qty_to_order"),
+ mr_item.item_name,
+ mr_item.description,
+ mr.company
+ ).where(
+ (mr.material_request_type == "Purchase")
+ & (mr.docstatus == 1)
+ & (mr.status != "Stopped")
+ & (mr.per_received < 100)
+ )
+ )
+
+ query = get_conditions(filters, query, mr, mr_item) # add conditional conditions
+
+ query = (
+ query.groupby(
+ mr.name, mr_item.item_code
+ ).orderby(
+ mr.transaction_date, mr.schedule_date
+ )
+ )
+ data = query.run(as_dict=True)
+ return data
+
+def get_conditions(filters, query, mr, mr_item):
if filters.get("from_date") and filters.get("to_date"):
- conditions += " and mr.transaction_date between '{0}' and '{1}'".format(filters.get("from_date"),filters.get("to_date"))
-
+ query = (
+ query.where(
+ (mr.transaction_date >= filters.get("from_date"))
+ & (mr.transaction_date <= filters.get("to_date"))
+ )
+ )
if filters.get("company"):
- conditions += " and mr.company = '{0}'".format(filters.get("company"))
+ query = query.where(mr.company == filters.get("company"))
if filters.get("material_request"):
- conditions += " and mr.name = '{0}'".format(filters.get("material_request"))
+ query = query.where(mr.name == filters.get("material_request"))
if filters.get("item_code"):
- conditions += " and mr_item.item_code = '{0}'".format(filters.get("item_code"))
+ query = query.where(mr_item.item_code == filters.get("item_code"))
- return conditions
-
-def get_data(filters, conditions):
- data = frappe.db.sql("""
- select
- mr.name as material_request,
- mr.transaction_date as date,
- mr_item.schedule_date as required_date,
- mr_item.item_code as item_code,
- sum(ifnull(mr_item.stock_qty, 0)) as qty,
- ifnull(mr_item.stock_uom, '') as uom,
- sum(ifnull(mr_item.ordered_qty, 0)) as ordered_qty,
- sum(ifnull(mr_item.received_qty, 0)) as received_qty,
- (sum(ifnull(mr_item.stock_qty, 0)) - sum(ifnull(mr_item.received_qty, 0))) as qty_to_receive,
- (sum(ifnull(mr_item.stock_qty, 0)) - sum(ifnull(mr_item.ordered_qty, 0))) as qty_to_order,
- mr_item.item_name as item_name,
- mr_item.description as "description",
- mr.company as company
- from
- `tabMaterial Request` mr, `tabMaterial Request Item` mr_item
- where
- mr_item.parent = mr.name
- and mr.material_request_type = "Purchase"
- and mr.docstatus = 1
- and mr.status != "Stopped"
- {conditions}
- group by mr.name, mr_item.item_code
- having
- sum(ifnull(mr_item.ordered_qty, 0)) < sum(ifnull(mr_item.stock_qty, 0))
- order by mr.transaction_date, mr.schedule_date""".format(conditions=conditions), as_dict=1)
-
- return data
+ return query
def update_qty_columns(row_to_update, data_row):
fields = ["qty", "ordered_qty", "received_qty", "qty_to_receive", "qty_to_order"]
diff --git a/erpnext/buying/report/requested_items_to_order_and_receive/test_requested_items_to_order_and_receive.py b/erpnext/buying/report/requested_items_to_order_and_receive/test_requested_items_to_order_and_receive.py
new file mode 100644
index 0000000..f3c751c
--- /dev/null
+++ b/erpnext/buying/report/requested_items_to_order_and_receive/test_requested_items_to_order_and_receive.py
@@ -0,0 +1,69 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days, today
+
+from erpnext.buying.doctype.purchase_order.purchase_order import make_purchase_receipt
+from erpnext.buying.report.requested_items_to_order_and_receive.requested_items_to_order_and_receive import (
+ get_data,
+)
+from erpnext.stock.doctype.item.test_item import create_item
+from erpnext.stock.doctype.material_request.material_request import make_purchase_order
+
+
+class TestRequestedItemsToOrderAndReceive(FrappeTestCase):
+ def setUp(self) -> None:
+ create_item("Test MR Report Item")
+ self.setup_material_request() # to order and receive
+ self.setup_material_request(order=True) # to receive (ordered)
+ self.setup_material_request(order=True, receive=True) # complete (ordered & received)
+
+ self.filters = frappe._dict(
+ company="_Test Company", from_date=today(), to_date=add_days(today(), 30),
+ item_code="Test MR Report Item"
+ )
+
+ def tearDown(self) -> None:
+ frappe.db.rollback()
+
+ def test_date_range(self):
+ data = get_data(self.filters)
+ self.assertEqual(len(data), 2) # MRs today should be fetched
+
+ self.filters.from_date = add_days(today(), 1)
+ data = get_data(self.filters)
+ self.assertEqual(len(data), 0) # MRs today should not be fetched as from date is tomorrow
+
+ def test_ordered_received_material_requests(self):
+ data = get_data(self.filters)
+
+ # from the 3 MRs made, only 2 (to receive) should be fetched
+ self.assertEqual(len(data), 2)
+ self.assertEqual(data[0].ordered_qty, 0.0)
+ self.assertEqual(data[1].ordered_qty, 57.0)
+
+ def setup_material_request(self, order=False, receive=False):
+ po = None
+ test_records = frappe.get_test_records('Material Request')
+
+ mr = frappe.copy_doc(test_records[0])
+ mr.transaction_date = today()
+ mr.schedule_date = add_days(today(), 1)
+ for row in mr.items:
+ row.item_code = "Test MR Report Item"
+ row.item_name = "Test MR Report Item"
+ row.description = "Test MR Report Item"
+ row.uom = "Nos"
+ row.schedule_date = add_days(today(), 1)
+ mr.submit()
+
+ if order or receive:
+ po = make_purchase_order(mr.name)
+ po.supplier = "_Test Supplier"
+ po.submit()
+ if receive:
+ pr = make_purchase_receipt(po.name)
+ pr.submit()
+
diff --git a/erpnext/regional/india/e_invoice/einv_item_template.json b/erpnext/regional/india/e_invoice/einv_item_template.json
index 78e5651..2c04c6d 100644
--- a/erpnext/regional/india/e_invoice/einv_item_template.json
+++ b/erpnext/regional/india/e_invoice/einv_item_template.json
@@ -23,9 +23,5 @@
"StateCesAmt": "{item.state_cess_amount}",
"StateCesNonAdvlAmt": "{item.state_cess_nadv_amount}",
"OthChrg": "{item.other_charges}",
- "TotItemVal": "{item.total_value}",
- "BchDtls": {{
- "Nm": "{item.batch_no}",
- "ExpDt": "{item.batch_expiry_date}"
- }}
+ "TotItemVal": "{item.total_value}"
}}
\ No newline at end of file
diff --git a/erpnext/regional/india/e_invoice/utils.py b/erpnext/regional/india/e_invoice/utils.py
index e3f7e90..64c75c4 100644
--- a/erpnext/regional/india/e_invoice/utils.py
+++ b/erpnext/regional/india/e_invoice/utils.py
@@ -214,8 +214,6 @@
item.taxable_value = abs(item.taxable_value)
item.discount_amount = 0
- item.batch_expiry_date = frappe.db.get_value('Batch', d.batch_no, 'expiry_date') if d.batch_no else None
- item.batch_expiry_date = format_date(item.batch_expiry_date, 'dd/mm/yyyy') if item.batch_expiry_date else None
item.is_service_item = 'Y' if item.gst_hsn_code and item.gst_hsn_code[:2] == "99" else 'N'
item.serial_no = ""
diff --git a/erpnext/stock/doctype/material_request/test_material_request.py b/erpnext/stock/doctype/material_request/test_material_request.py
index 1cda781..866f3ab 100644
--- a/erpnext/stock/doctype/material_request/test_material_request.py
+++ b/erpnext/stock/doctype/material_request/test_material_request.py
@@ -626,13 +626,13 @@
mr.schedule_date = today()
if not frappe.db.get_value('UOM Conversion Detail',
- {'parent': item.item_code, 'uom': 'Kg'}):
- item_doc = frappe.get_doc('Item', item.item_code)
- item_doc.append('uoms', {
- 'uom': 'Kg',
- 'conversion_factor': 5
- })
- item_doc.save(ignore_permissions=True)
+ {'parent': item.item_code, 'uom': 'Kg'}):
+ item_doc = frappe.get_doc('Item', item.item_code)
+ item_doc.append('uoms', {
+ 'uom': 'Kg',
+ 'conversion_factor': 5
+ })
+ item_doc.save(ignore_permissions=True)
item.uom = 'Kg'
for item in mr.items:
diff --git a/erpnext/stock/report/stock_ageing/test_stock_ageing.py b/erpnext/stock/report/stock_ageing/test_stock_ageing.py
index 2630805..ca963b7 100644
--- a/erpnext/stock/report/stock_ageing/test_stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/test_stock_ageing.py
@@ -1,4 +1,4 @@
-# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and Contributors
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
# See license.txt
import frappe