Merge branch 'develop' into refactor/stock/reports
diff --git a/erpnext/stock/report/batch_item_expiry_status/batch_item_expiry_status.py b/erpnext/stock/report/batch_item_expiry_status/batch_item_expiry_status.py
index 3d9b046..ef7d6e6 100644
--- a/erpnext/stock/report/batch_item_expiry_status/batch_item_expiry_status.py
+++ b/erpnext/stock/report/batch_item_expiry_status/batch_item_expiry_status.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull
from frappe.utils import cint, getdate
@@ -54,31 +55,28 @@
return columns
-def get_conditions(filters):
- conditions = ""
+def get_stock_ledger_entries(filters):
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
- if filters.get("to_date"):
- conditions += " and posting_date <= '%s'" % filters["to_date"]
- else:
+ if not filters.get("to_date"):
frappe.throw(_("'To Date' is required"))
- return conditions
-
-
-def get_stock_ledger_entries(filters):
- conditions = get_conditions(filters)
- return frappe.db.sql(
- """select item_code, batch_no, warehouse,
- posting_date, actual_qty
- from `tabStock Ledger Entry`
- where is_cancelled = 0
- and docstatus < 2 and ifnull(batch_no, '') != '' %s order by item_code, warehouse"""
- % conditions,
- as_dict=1,
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .select(sle.item_code, sle.batch_no, sle.warehouse, sle.posting_date, sle.actual_qty)
+ .where(
+ (sle.is_cancelled == 0)
+ & (sle.docstatus < 2)
+ & (IfNull(sle.batch_no, "") != "")
+ & (sle.posting_date <= filters["to_date"])
+ )
+ .orderby(sle.item_code, sle.warehouse)
)
+ return query.run(as_dict=True)
+
def get_item_warehouse_batch_map(filters, float_precision):
sle = get_stock_ledger_entries(filters)
@@ -112,7 +110,7 @@
def get_item_details(filters):
item_map = {}
- for d in frappe.db.sql("select name, item_name, description from tabItem", as_dict=1):
+ for d in (frappe.qb.from_("Item").select("name", "item_name", "description")).run(as_dict=True):
item_map.setdefault(d.name, d)
return item_map
diff --git a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
index 8a13300..2f54325 100644
--- a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
+++ b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import cint, flt, getdate
@@ -64,37 +65,35 @@
return columns
-def get_conditions(filters):
- conditions = ""
+# get all details
+def get_stock_ledger_entries(filters):
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
- if filters.get("to_date"):
- conditions += " and posting_date <= '%s'" % filters["to_date"]
- else:
+ if not filters.get("to_date"):
frappe.throw(_("'To Date' is required"))
- for field in ["item_code", "warehouse", "batch_no", "company"]:
- if filters.get(field):
- conditions += " and {0} = {1}".format(field, frappe.db.escape(filters.get(field)))
-
- return conditions
-
-
-# get all details
-def get_stock_ledger_entries(filters):
- conditions = get_conditions(filters)
- return frappe.db.sql(
- """
- select item_code, batch_no, warehouse, posting_date, sum(actual_qty) as actual_qty
- from `tabStock Ledger Entry`
- where is_cancelled = 0 and docstatus < 2 and ifnull(batch_no, '') != '' %s
- group by voucher_no, batch_no, item_code, warehouse
- order by item_code, warehouse"""
- % conditions,
- as_dict=1,
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ sle.batch_no,
+ sle.warehouse,
+ sle.posting_date,
+ Sum(sle.actual_qty).as_("actual_qty"),
+ )
+ .where((sle.is_cancelled == 0) & (sle.docstatus < 2) & (IfNull(sle.batch_no, "") != ""))
+ .groupby(sle.voucher_no, sle.batch_no, sle.item_code, sle.warehouse)
+ .orderby(sle.item_code, sle.warehouse)
)
+ for field in ("item_code", "warehouse", "batch_no", "company"):
+ if filters.get(field):
+ query = query.where(sle[field] == filters[field])
+
+ return query.run(as_dict=True)
+
def get_item_warehouse_batch_map(filters, float_precision):
sle = get_stock_ledger_entries(filters)
@@ -127,7 +126,9 @@
def get_item_details(filters):
item_map = {}
- for d in frappe.db.sql("select name, item_name, description, stock_uom from tabItem", as_dict=1):
+ for d in (frappe.qb.from_("Item").select("name", "item_name", "description", "stock_uom")).run(
+ as_dict=1
+ ):
item_map.setdefault(d.name, d)
return item_map
diff --git a/erpnext/stock/report/delayed_item_report/delayed_item_report.py b/erpnext/stock/report/delayed_item_report/delayed_item_report.py
index 9df24d6..546a834 100644
--- a/erpnext/stock/report/delayed_item_report/delayed_item_report.py
+++ b/erpnext/stock/report/delayed_item_report/delayed_item_report.py
@@ -21,56 +21,54 @@
return self.get_columns(), self.get_data() or []
def get_data(self, consolidated=False):
- conditions = ""
-
doctype = self.filters.get("based_on")
- child_doc = "%s Item" % doctype
+ sales_order_field = "sales_order" if doctype == "Sales Invoice" else "against_sales_order"
+
+ parent = frappe.qb.DocType(doctype)
+ child = frappe.qb.DocType(f"{doctype} Item")
+
+ query = (
+ frappe.qb.from_(child)
+ .from_(parent)
+ .select(
+ child.item_code,
+ child.item_name,
+ child.item_group,
+ child.qty,
+ child.rate,
+ child.amount,
+ child.so_detail,
+ child[sales_order_field].as_("sales_order"),
+ parent.shipping_address_name,
+ parent.po_no,
+ parent.customer,
+ parent.posting_date,
+ parent.name,
+ parent.grand_total,
+ )
+ .where(
+ (child.parent == parent.name)
+ & (parent.docstatus == 1)
+ & (parent.posting_date.between(self.filters.get("from_date"), self.filters.get("to_date")))
+ & (child[sales_order_field].notnull())
+ & (child[sales_order_field] != "")
+ )
+ )
if doctype == "Sales Invoice":
- conditions = " and `tabSales Invoice`.update_stock = 1 and `tabSales Invoice`.is_pos = 0"
+ query = query.where((parent.update_stock == 1) & (parent.is_pos == 0))
if self.filters.get("item_group"):
- conditions += " and `tab%s`.item_group = %s" % (
- child_doc,
- frappe.db.escape(self.filters.get("item_group")),
- )
-
- for field in ["customer", "customer_group", "company"]:
- if self.filters.get(field):
- conditions += " and `tab%s`.%s = %s" % (
- doctype,
- field,
- frappe.db.escape(self.filters.get(field)),
- )
-
- sales_order_field = "against_sales_order"
- if doctype == "Sales Invoice":
- sales_order_field = "sales_order"
+ query = query.where(child.item_group == self.filters.get("item_group"))
if self.filters.get("sales_order"):
- conditions = " and `tab%s`.%s = '%s'" % (
- child_doc,
- sales_order_field,
- self.filters.get("sales_order"),
- )
+ query = query.where(child[sales_order_field] == self.filters.get("sales_order"))
- self.transactions = frappe.db.sql(
- """ SELECT `tab{child_doc}`.item_code, `tab{child_doc}`.item_name,
- `tab{child_doc}`.item_group, `tab{child_doc}`.qty, `tab{child_doc}`.rate, `tab{child_doc}`.amount,
- `tab{child_doc}`.so_detail, `tab{child_doc}`.{so_field} as sales_order,
- `tab{doctype}`.shipping_address_name, `tab{doctype}`.po_no, `tab{doctype}`.customer,
- `tab{doctype}`.posting_date, `tab{doctype}`.name, `tab{doctype}`.grand_total
- FROM `tab{child_doc}`, `tab{doctype}`
- WHERE
- `tab{child_doc}`.parent = `tab{doctype}`.name and `tab{doctype}`.docstatus = 1 and
- `tab{doctype}`.posting_date between %(from_date)s and %(to_date)s and
- `tab{child_doc}`.{so_field} is not null and `tab{child_doc}`.{so_field} != '' {cond}
- """.format(
- cond=conditions, doctype=doctype, child_doc=child_doc, so_field=sales_order_field
- ),
- {"from_date": self.filters.get("from_date"), "to_date": self.filters.get("to_date")},
- as_dict=1,
- )
+ for field in ("customer", "customer_group", "company"):
+ if self.filters.get(field):
+ query = query.where(parent[field] == self.filters.get(field))
+
+ self.transactions = query.run(as_dict=True)
if self.transactions:
self.filter_transactions_data(consolidated)
diff --git a/erpnext/stock/report/item_prices/item_prices.py b/erpnext/stock/report/item_prices/item_prices.py
index 87f1a42..ab47b4a 100644
--- a/erpnext/stock/report/item_prices/item_prices.py
+++ b/erpnext/stock/report/item_prices/item_prices.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import flt
@@ -12,8 +13,7 @@
filters = {}
columns = get_columns(filters)
- conditions = get_condition(filters)
- item_map = get_item_details(conditions)
+ item_map = get_item_details(filters)
pl = get_price_list()
last_purchase_rate = get_last_purchase_rate()
bom_rate = get_item_bom_rate()
@@ -63,18 +63,24 @@
return columns
-def get_item_details(conditions):
+def get_item_details(filters):
"""returns all items details"""
item_map = {}
- for i in frappe.db.sql(
- """select name, item_group, item_name, description,
- brand, stock_uom from tabItem %s
- order by item_code, item_group"""
- % (conditions),
- as_dict=1,
- ):
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(item)
+ .select(item.name, item.item_group, item.item_name, item.description, item.brand, item.stock_uom)
+ .orderby(item.item_code, item.item_group)
+ )
+
+ if filters.get("items") == "Enabled Items only":
+ query = query.where(item.disabled == 0)
+ elif filters.get("items") == "Disabled Items only":
+ query = query.where(item.disabled == 1)
+
+ for i in query.run(as_dict=True):
item_map.setdefault(i.name, i)
return item_map
@@ -85,19 +91,38 @@
rate = {}
- price_list = frappe.db.sql(
- """select ip.item_code, ip.buying, ip.selling,
- concat(ifnull(cu.symbol,ip.currency), " ", round(ip.price_list_rate,2), " - ", ip.price_list) as price
- from `tabItem Price` ip, `tabPrice List` pl, `tabCurrency` cu
- where ip.price_list=pl.name and pl.currency=cu.name and pl.enabled=1""",
- as_dict=1,
- )
+ ip = frappe.qb.DocType("Item Price")
+ pl = frappe.qb.DocType("Price List")
+ cu = frappe.qb.DocType("Currency")
- for j in price_list:
- if j.price:
- rate.setdefault(j.item_code, {}).setdefault("Buying" if j.buying else "Selling", []).append(
- j.price
+ price_list = (
+ frappe.qb.from_(ip)
+ .from_(pl)
+ .from_(cu)
+ .select(
+ ip.item_code,
+ ip.buying,
+ ip.selling,
+ (IfNull(cu.symbol, ip.currency)).as_("currency"),
+ ip.price_list_rate,
+ ip.price_list,
+ )
+ .where((ip.price_list == pl.name) & (pl.currency == cu.name) & (pl.enabled == 1))
+ ).run(as_dict=True)
+
+ for d in price_list:
+ d.update(
+ {"price": "{0} {1} - {2}".format(d.currency, round(d.price_list_rate, 2), d.price_list)}
+ )
+ d.pop("currency")
+ d.pop("price_list_rate")
+ d.pop("price_list")
+
+ if d.price:
+ rate.setdefault(d.item_code, {}).setdefault("Buying" if d.buying else "Selling", []).append(
+ d.price
)
+
item_rate_map = {}
for item in rate:
@@ -112,30 +137,39 @@
def get_last_purchase_rate():
item_last_purchase_rate_map = {}
- query = """select * from (
- (select
- po_item.item_code,
- po.transaction_date as posting_date,
- po_item.base_rate
- from `tabPurchase Order` po, `tabPurchase Order Item` po_item
- where po.name = po_item.parent and po.docstatus = 1)
- union
- (select
- pr_item.item_code,
- pr.posting_date,
- pr_item.base_rate
- from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
- where pr.name = pr_item.parent and pr.docstatus = 1)
- union
- (select
- pi_item.item_code,
- pi.posting_date,
- pi_item.base_rate
- from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item
- where pi.name = pi_item.parent and pi.docstatus = 1 and pi.update_stock = 1)
- ) result order by result.item_code asc, result.posting_date asc"""
+ po = frappe.qb.DocType("Purchase Order")
+ pr = frappe.qb.DocType("Purchase Receipt")
+ pi = frappe.qb.DocType("Purchase Invoice")
+ po_item = frappe.qb.DocType("Purchase Order Item")
+ pr_item = frappe.qb.DocType("Purchase Receipt Item")
+ pi_item = frappe.qb.DocType("Purchase Invoice Item")
- for d in frappe.db.sql(query, as_dict=1):
+ query = (
+ frappe.qb.from_(
+ (
+ frappe.qb.from_(po)
+ .from_(po_item)
+ .select(po_item.item_code, po.transaction_date.as_("posting_date"), po_item.base_rate)
+ .where((po.name == po_item.parent) & (po.docstatus == 1))
+ )
+ + (
+ frappe.qb.from_(pr)
+ .from_(pr_item)
+ .select(pr_item.item_code, pr.posting_date, pr_item.base_rate)
+ .where((pr.name == pr_item.parent) & (pr.docstatus == 1))
+ )
+ + (
+ frappe.qb.from_(pi)
+ .from_(pi_item)
+ .select(pi_item.item_code, pi.posting_date, pi_item.base_rate)
+ .where((pi.name == pi_item.parent) & (pi.docstatus == 1) & (pi.update_stock == 1))
+ )
+ )
+ .select("*")
+ .orderby("item_code", "posting_date")
+ )
+
+ for d in query.run(as_dict=True):
item_last_purchase_rate_map[d.item_code] = d.base_rate
return item_last_purchase_rate_map
@@ -146,12 +180,15 @@
item_bom_map = {}
- for b in frappe.db.sql(
- """select item, (total_cost/quantity) as bom_rate
- from `tabBOM` where is_active=1 and is_default=1""",
- as_dict=1,
- ):
- item_bom_map.setdefault(b.item, flt(b.bom_rate))
+ bom = frappe.qb.DocType("BOM")
+ bom_data = (
+ frappe.qb.from_(bom)
+ .select(bom.item, (bom.total_cost / bom.quantity).as_("bom_rate"))
+ .where((bom.is_active == 1) & (bom.is_default == 1))
+ ).run(as_dict=True)
+
+ for d in bom_data:
+ item_bom_map.setdefault(d.item, flt(d.bom_rate))
return item_bom_map
@@ -161,25 +198,17 @@
item_val_rate_map = {}
- for d in frappe.db.sql(
- """select item_code,
- sum(actual_qty*valuation_rate)/sum(actual_qty) as val_rate
- from tabBin where actual_qty > 0 group by item_code""",
- as_dict=1,
- ):
+ bin = frappe.qb.DocType("Bin")
+ bin_data = (
+ frappe.qb.from_(bin)
+ .select(
+ bin.item_code, Sum(bin.actual_qty * bin.valuation_rate) / Sum(bin.actual_qty).as_("val_rate")
+ )
+ .where(bin.actual_qty > 0)
+ .groupby(bin.item_code)
+ ).run(as_dict=True)
+
+ for d in bin_data:
item_val_rate_map.setdefault(d.item_code, d.val_rate)
return item_val_rate_map
-
-
-def get_condition(filters):
- """Get Filter Items"""
-
- if filters.get("items") == "Enabled Items only":
- conditions = " where disabled=0 "
- elif filters.get("items") == "Disabled Items only":
- conditions = " where disabled=1 "
- else:
- conditions = ""
-
- return conditions
diff --git a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
index 49e797d..f477d8f 100644
--- a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
+++ b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
@@ -5,6 +5,7 @@
import frappe
from frappe import _
from frappe.utils import flt, today
+from pypika.terms import ExistsCriterion
from erpnext.accounts.doctype.pos_invoice.pos_invoice import get_pos_reserved_qty
from erpnext.stock.utils import (
@@ -218,10 +219,26 @@
def get_bin_list(filters):
- conditions = []
+ bin = frappe.qb.DocType("Bin")
+ query = (
+ frappe.qb.from_(bin)
+ .select(
+ bin.item_code,
+ bin.warehouse,
+ bin.actual_qty,
+ bin.planned_qty,
+ bin.indented_qty,
+ bin.ordered_qty,
+ bin.reserved_qty,
+ bin.reserved_qty_for_production,
+ bin.reserved_qty_for_sub_contract,
+ bin.projected_qty,
+ )
+ .orderby(bin.item_code, bin.warehouse)
+ )
if filters.item_code:
- conditions.append("item_code = '%s' " % filters.item_code)
+ query = query.where(bin.item_code == filters.item_code)
if filters.warehouse:
warehouse_details = frappe.db.get_value(
@@ -229,21 +246,20 @@
)
if warehouse_details:
- conditions.append(
- " exists (select name from `tabWarehouse` wh \
- where wh.lft >= %s and wh.rgt <= %s and bin.warehouse = wh.name)"
- % (warehouse_details.lft, warehouse_details.rgt)
+ wh = frappe.qb.DocType("Warehouse")
+ query = query.where(
+ ExistsCriterion(
+ frappe.qb.from_(wh)
+ .select(wh.name)
+ .where(
+ (wh.lft >= warehouse_details.lft)
+ & (wh.rgt <= warehouse_details.rgt)
+ & (bin.warehouse == wh.name)
+ )
+ )
)
- bin_list = frappe.db.sql(
- """select item_code, warehouse, actual_qty, planned_qty, indented_qty,
- ordered_qty, reserved_qty, reserved_qty_for_production, reserved_qty_for_sub_contract, projected_qty
- from tabBin bin {conditions} order by item_code, warehouse
- """.format(
- conditions=" where " + " and ".join(conditions) if conditions else ""
- ),
- as_dict=1,
- )
+ bin_list = query.run(as_dict=True)
return bin_list
@@ -251,45 +267,43 @@
def get_item_map(item_code, include_uom):
"""Optimization: get only the item doc and re_order_levels table"""
- condition = ""
- if item_code:
- condition = "and item_code = {0}".format(frappe.db.escape(item_code, percent=False))
+ bin = frappe.qb.DocType("Bin")
+ item = frappe.qb.DocType("Item")
- cf_field = cf_join = ""
- if include_uom:
- cf_field = ", ucd.conversion_factor"
- cf_join = (
- "left join `tabUOM Conversion Detail` ucd on ucd.parent=item.name and ucd.uom=%(include_uom)s"
+ query = (
+ frappe.qb.from_(item)
+ .select(item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom)
+ .where(
+ (item.is_stock_item == 1)
+ & (item.disabled == 0)
+ & (
+ (item.end_of_life > today()) | (item.end_of_life.isnull()) | (item.end_of_life == "0000-00-00")
+ )
+ & (ExistsCriterion(frappe.qb.from_(bin).select(bin.name).where(bin.item_code == item.name)))
)
-
- items = frappe.db.sql(
- """
- select item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom{cf_field}
- from `tabItem` item
- {cf_join}
- where item.is_stock_item = 1
- and item.disabled=0
- {condition}
- and (item.end_of_life > %(today)s or item.end_of_life is null or item.end_of_life='0000-00-00')
- and exists (select name from `tabBin` bin where bin.item_code=item.name)""".format(
- cf_field=cf_field, cf_join=cf_join, condition=condition
- ),
- {"today": today(), "include_uom": include_uom},
- as_dict=True,
)
- condition = ""
if item_code:
- condition = "where parent={0}".format(frappe.db.escape(item_code, percent=False))
+ query = query.where(item.item_code == item_code)
+
+ if include_uom:
+ ucd = frappe.qb.DocType("UOM Conversion Detail")
+ query = query.left_join(ucd).on((ucd.parent == item.name) & (ucd.uom == include_uom))
+
+ items = query.run(as_dict=True)
+
+ ir = frappe.qb.DocType("Item Reorder")
+ query = frappe.qb.from_(ir).select("*")
+
+ if item_code:
+ query = query.where(ir.parent == item_code)
reorder_levels = frappe._dict()
- for ir in frappe.db.sql(
- """select * from `tabItem Reorder` {condition}""".format(condition=condition), as_dict=1
- ):
- if ir.parent not in reorder_levels:
- reorder_levels[ir.parent] = []
+ for d in query.run(as_dict=True):
+ if d.parent not in reorder_levels:
+ reorder_levels[d.parent] = []
- reorder_levels[ir.parent].append(ir)
+ reorder_levels[d.parent].append(d)
item_map = frappe._dict()
for item in items:
diff --git a/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py b/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
index 5430fe6..8c76908 100644
--- a/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
+++ b/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull
from frappe.utils import flt
@@ -70,31 +71,33 @@
return columns
-def get_conditions(filters):
- conditions = ""
- values = []
+def get_consumed_details(filters):
+ item = frappe.qb.DocType("Item")
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+
+ query = (
+ frappe.qb.from_(sle)
+ .from_(item)
+ .select(
+ sle.item_code,
+ item.item_name,
+ item.description,
+ item.stock_uom,
+ sle.actual_qty,
+ sle.stock_value_difference,
+ sle.voucher_no,
+ sle.voucher_type,
+ )
+ .where((sle.is_cancelled == 0) & (sle.item_code == item.name) & (sle.actual_qty < 0))
+ )
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")]
+ query = query.where(
+ (sle.posting_date >= filters.get("from_date")) & (sle.posting_date <= filters.get("to_date"))
+ )
- return conditions, values
-
-
-def get_consumed_details(filters):
- conditions, values = get_conditions(filters)
consumed_details = {}
-
- for d in frappe.db.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.is_cancelled = 0 and sle.item_code=i.name and sle.actual_qty < 0 %s"""
- % conditions,
- values,
- as_dict=1,
- ):
+ for d in query.run(as_dict=True):
consumed_details.setdefault(d.item_code, []).append(d)
return consumed_details
@@ -104,24 +107,54 @@
item_supplier_map = {}
supplier = filters.get("supplier")
- for d in frappe.db.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
- is_stock_item=1 and name=pri.item_code)""",
- as_dict=1,
- ):
+ item = frappe.qb.DocType("Item")
+ pr = frappe.qb.DocType("Purchase Receipt")
+ pr_item = frappe.qb.DocType("Purchase Receipt Item")
+
+ query = (
+ frappe.qb.from_(pr)
+ .from_(pr_item)
+ .select(pr.supplier, pr_item.item_code)
+ .where(
+ (pr.name == pr_item.parent)
+ & (pr.docstatus == 1)
+ & (
+ pr_item.item_code
+ == (
+ frappe.qb.from_(item)
+ .select(item.name)
+ .where((item.is_stock_item == 1) & (item.name == pr_item.item_code))
+ )
+ )
+ )
+ )
+
+ for d in query.run(as_dict=True):
item_supplier_map.setdefault(d.item_code, []).append(d.supplier)
- for d in frappe.db.sql(
- """select pr.supplier, pri.item_code from
- `tabPurchase Invoice` pr, `tabPurchase Invoice Item` pri
- where pr.name=pri.parent and pr.docstatus=1 and
- ifnull(pr.update_stock, 0) = 1 and pri.item_code=(select name from `tabItem`
- where is_stock_item=1 and name=pri.item_code)""",
- as_dict=1,
- ):
+ pi = frappe.qb.DocType("Purchase Invoice")
+ pi_item = frappe.qb.DocType("Purchase Invoice Item")
+
+ query = (
+ frappe.qb.from_(pi)
+ .from_(pi_item)
+ .select(pi.supplier, pi_item.item_code)
+ .where(
+ (pi.name == pi_item.parent)
+ & (pi.docstatus == 1)
+ & (IfNull(pi.update_stock, 0) == 1)
+ & (
+ pi_item.item_code
+ == (
+ frappe.qb.from_(item)
+ .select(item.name)
+ .where((item.is_stock_item == 1) & (item.name == pi_item.item_code))
+ )
+ )
+ )
+ )
+
+ for d in query.run(as_dict=True):
if d.item_code not in item_supplier_map:
item_supplier_map.setdefault(d.item_code, []).append(d.supplier)
@@ -138,7 +171,11 @@
def get_material_transfer_vouchers():
- return frappe.db.sql_list(
- """select name from `tabStock Entry` where
- purpose='Material Transfer' and docstatus=1"""
+ se = frappe.qb.DocType("Stock Entry")
+ query = (
+ frappe.qb.from_(se)
+ .select(se.name)
+ .where((se.purpose == "Material Transfer") & (se.docstatus == 1))
)
+
+ return [r[0] for r in query.run()]
diff --git a/erpnext/stock/report/total_stock_summary/total_stock_summary.py b/erpnext/stock/report/total_stock_summary/total_stock_summary.py
index 21529da..c3155bd 100644
--- a/erpnext/stock/report/total_stock_summary/total_stock_summary.py
+++ b/erpnext/stock/report/total_stock_summary/total_stock_summary.py
@@ -4,60 +4,58 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Sum
def execute(filters=None):
if not filters:
filters = {}
- columns = get_columns()
+ columns = get_columns(filters)
stock = get_total_stock(filters)
return columns, stock
-def get_columns():
+def get_columns(filters):
columns = [
- _("Company") + ":Link/Company:250",
- _("Warehouse") + ":Link/Warehouse:150",
_("Item") + ":Link/Item:150",
_("Description") + "::300",
_("Current Qty") + ":Float:100",
]
+ if filters.get("group_by") == "Warehouse":
+ columns.insert(0, _("Warehouse") + ":Link/Warehouse:150")
+ else:
+ columns.insert(0, _("Company") + ":Link/Company:250")
+
return columns
def get_total_stock(filters):
- conditions = ""
- columns = ""
+ bin = frappe.qb.DocType("Bin")
+ item = frappe.qb.DocType("Item")
+ wh = frappe.qb.DocType("Warehouse")
+
+ query = (
+ frappe.qb.from_(bin)
+ .inner_join(item)
+ .on(bin.item_code == item.item_code)
+ .inner_join(wh)
+ .on(wh.name == bin.warehouse)
+ .where(bin.actual_qty != 0)
+ )
if filters.get("group_by") == "Warehouse":
if filters.get("company"):
- conditions += " AND warehouse.company = %s" % frappe.db.escape(
- filters.get("company"), percent=False
- )
+ query = query.where(wh.company == filters.get("company"))
- conditions += " GROUP BY ledger.warehouse, item.item_code"
- columns += "'' as company, ledger.warehouse"
+ query = query.select(bin.warehouse).groupby(bin.warehouse)
else:
- conditions += " GROUP BY warehouse.company, item.item_code"
- columns += " warehouse.company, '' as warehouse"
+ query = query.select(wh.company).groupby(wh.company)
- return frappe.db.sql(
- """
- SELECT
- %s,
- item.item_code,
- item.description,
- sum(ledger.actual_qty) as actual_qty
- FROM
- `tabBin` AS ledger
- INNER JOIN `tabItem` AS item
- ON ledger.item_code = item.item_code
- INNER JOIN `tabWarehouse` warehouse
- ON warehouse.name = ledger.warehouse
- WHERE
- ledger.actual_qty != 0 %s"""
- % (columns, conditions)
- )
+ query = query.select(
+ item.item_code, item.description, Sum(bin.actual_qty).as_("actual_qty")
+ ).groupby(item.item_code)
+
+ return query.run()
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
index a54373f..eedf1a0 100644
--- a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Count
from frappe.utils import flt
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
@@ -98,7 +99,7 @@
def validate_filters(filters):
if not (filters.get("item_code") or filters.get("warehouse")):
- sle_count = flt(frappe.db.sql("""select count(name) from `tabStock Ledger Entry`""")[0][0])
+ sle_count = flt(frappe.qb.from_("Stock Ledger Entry").select(Count("name")).run()[0][0])
if sle_count > 500000:
frappe.throw(_("Please set filter based on Item or Warehouse"))
if not filters.get("company"):
@@ -108,25 +109,16 @@
def get_warehouse_list(filters):
from frappe.core.doctype.user_permission.user_permission import get_permitted_documents
- condition = ""
- user_permitted_warehouse = get_permitted_documents("Warehouse")
- value = ()
- if user_permitted_warehouse:
- condition = "and name in %s"
- value = set(user_permitted_warehouse)
- elif not user_permitted_warehouse and filters.get("warehouse"):
- condition = "and name = %s"
- value = filters.get("warehouse")
+ wh = frappe.qb.DocType("Warehouse")
+ query = frappe.qb.from_(wh).select(wh.name).where(wh.is_group == 0)
- return frappe.db.sql(
- """select name
- from `tabWarehouse` where is_group = 0
- {condition}""".format(
- condition=condition
- ),
- value,
- as_dict=1,
- )
+ user_permitted_warehouse = get_permitted_documents("Warehouse")
+ if user_permitted_warehouse:
+ query = query.where(wh.name.isin(set(user_permitted_warehouse)))
+ elif filters.get("warehouse"):
+ query = query.where(wh.name == filters.get("warehouse"))
+
+ return query.run(as_dict=True)
def add_warehouse_column(columns, warehouse_list):