Merge pull request #34258 from deepeshgarg007/finance_book_read_only
chore: Make finance book read only
diff --git a/erpnext/controllers/sales_and_purchase_return.py b/erpnext/controllers/sales_and_purchase_return.py
index 9fcb769..15c270e 100644
--- a/erpnext/controllers/sales_and_purchase_return.py
+++ b/erpnext/controllers/sales_and_purchase_return.py
@@ -131,7 +131,7 @@
)
elif ref.serial_no:
- if not d.serial_no:
+ if d.qty and not d.serial_no:
frappe.throw(_("Row # {0}: Serial No is mandatory").format(d.idx))
else:
serial_nos = get_serial_nos(d.serial_no)
@@ -400,6 +400,16 @@
if serial_nos:
target_doc.serial_no = "\n".join(serial_nos)
+ if source_doc.get("rejected_serial_no"):
+ returned_serial_nos = get_returned_serial_nos(
+ source_doc, source_parent, serial_no_field="rejected_serial_no"
+ )
+ rejected_serial_nos = list(
+ set(get_serial_nos(source_doc.rejected_serial_no)) - set(returned_serial_nos)
+ )
+ if rejected_serial_nos:
+ target_doc.rejected_serial_no = "\n".join(rejected_serial_nos)
+
if doctype in ["Purchase Receipt", "Subcontracting Receipt"]:
returned_qty_map = get_returned_qty_map_for_row(
source_parent.name, source_parent.supplier, source_doc.name, doctype
@@ -610,7 +620,7 @@
return filters
-def get_returned_serial_nos(child_doc, parent_doc):
+def get_returned_serial_nos(child_doc, parent_doc, serial_no_field="serial_no"):
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
return_ref_field = frappe.scrub(child_doc.doctype)
@@ -619,7 +629,7 @@
serial_nos = []
- fields = ["`{0}`.`serial_no`".format("tab" + child_doc.doctype)]
+ fields = [f"`{'tab' + child_doc.doctype}`.`{serial_no_field}`"]
filters = [
[parent_doc.doctype, "return_against", "=", parent_doc.name],
@@ -629,6 +639,6 @@
]
for row in frappe.get_all(parent_doc.doctype, fields=fields, filters=filters):
- serial_nos.extend(get_serial_nos(row.serial_no))
+ serial_nos.extend(get_serial_nos(row.get(serial_no_field)))
return serial_nos
diff --git a/erpnext/stock/doctype/quality_inspection/quality_inspection.py b/erpnext/stock/doctype/quality_inspection/quality_inspection.py
index 2a9f091..9673c81 100644
--- a/erpnext/stock/doctype/quality_inspection/quality_inspection.py
+++ b/erpnext/stock/doctype/quality_inspection/quality_inspection.py
@@ -6,7 +6,7 @@
from frappe import _
from frappe.model.document import Document
from frappe.model.mapper import get_mapped_doc
-from frappe.utils import cint, cstr, flt
+from frappe.utils import cint, cstr, flt, get_number_format_info
from erpnext.stock.doctype.quality_inspection_template.quality_inspection_template import (
get_template_details,
@@ -156,7 +156,9 @@
for i in range(1, 11):
reading_value = reading.get("reading_" + str(i))
if reading_value is not None and reading_value.strip():
- result = flt(reading.get("min_value")) <= flt(reading_value) <= flt(reading.get("max_value"))
+ result = (
+ flt(reading.get("min_value")) <= parse_float(reading_value) <= flt(reading.get("max_value"))
+ )
if not result:
return False
return True
@@ -196,7 +198,7 @@
# numeric readings
for i in range(1, 11):
field = "reading_" + str(i)
- data[field] = flt(reading.get(field))
+ data[field] = parse_float(reading.get(field))
data["mean"] = self.calculate_mean(reading)
return data
@@ -210,7 +212,7 @@
for i in range(1, 11):
reading_value = reading.get("reading_" + str(i))
if reading_value is not None and reading_value.strip():
- readings_list.append(flt(reading_value))
+ readings_list.append(parse_float(reading_value))
actual_mean = mean(readings_list) if readings_list else 0
return actual_mean
@@ -324,3 +326,19 @@
)
return doc
+
+
+def parse_float(num: str) -> float:
+ """Since reading_# fields are `Data` field they might contain number which
+ is representation in user's prefered number format instead of machine
+ readable format. This function converts them to machine readable format."""
+
+ number_format = frappe.db.get_default("number_format") or "#,###.##"
+ decimal_str, comma_str, _number_format_precision = get_number_format_info(number_format)
+
+ if decimal_str == "," and comma_str == ".":
+ num = num.replace(",", "#$")
+ num = num.replace(".", ",")
+ num = num.replace("#$", ".")
+
+ return flt(num)
diff --git a/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py b/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
index 4f19643..9d2e139 100644
--- a/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
+++ b/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
@@ -2,7 +2,7 @@
# See license.txt
import frappe
-from frappe.tests.utils import FrappeTestCase
+from frappe.tests.utils import FrappeTestCase, change_settings
from frappe.utils import nowdate
from erpnext.controllers.stock_controller import (
@@ -216,6 +216,40 @@
qa.save()
self.assertEqual(qa.status, "Accepted")
+ @change_settings("System Settings", {"number_format": "#.###,##"})
+ def test_diff_number_format(self):
+ self.assertEqual(frappe.db.get_default("number_format"), "#.###,##") # sanity check
+
+ # Test QI based on acceptance values (Non formula)
+ dn = create_delivery_note(item_code="_Test Item with QA", do_not_submit=True)
+ readings = [
+ {
+ "specification": "Iron Content", # numeric reading
+ "min_value": 60,
+ "max_value": 100,
+ "reading_1": "70,000",
+ },
+ {
+ "specification": "Iron Content", # numeric reading
+ "min_value": 60,
+ "max_value": 100,
+ "reading_1": "1.100,00",
+ },
+ ]
+
+ qa = create_quality_inspection(
+ reference_type="Delivery Note", reference_name=dn.name, readings=readings, do_not_save=True
+ )
+
+ qa.save()
+
+ # status must be auto set as per formula
+ self.assertEqual(qa.readings[0].status, "Accepted")
+ self.assertEqual(qa.readings[1].status, "Rejected")
+
+ qa.delete()
+ dn.delete()
+
def create_quality_inspection(**args):
args = frappe._dict(args)
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index b53f429..489ec6e 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -8,6 +8,7 @@
from frappe import _, throw
from frappe.model import child_table_fields, default_fields
from frappe.model.meta import get_field_precision
+from frappe.query_builder.functions import CombineDatetime, IfNull, Sum
from frappe.utils import add_days, add_months, cint, cstr, flt, getdate
from erpnext import get_company_currency
@@ -526,12 +527,8 @@
itemwise_barcode = {}
for item in items_list:
- barcodes = frappe.db.sql(
- """
- select barcode from `tabItem Barcode` where parent = %s
- """,
- item.item_code,
- as_dict=1,
+ barcodes = frappe.db.get_all(
+ "Item Barcode", filters={"parent": item.item_code}, fields="barcode"
)
for barcode in barcodes:
@@ -891,34 +888,36 @@
:param item_code: str, Item Doctype field item_code
"""
- args["item_code"] = item_code
-
- conditions = """where item_code=%(item_code)s
- and price_list=%(price_list)s
- and ifnull(uom, '') in ('', %(uom)s)"""
-
- conditions += "and ifnull(batch_no, '') in ('', %(batch_no)s)"
+ ip = frappe.qb.DocType("Item Price")
+ query = (
+ frappe.qb.from_(ip)
+ .select(ip.name, ip.price_list_rate, ip.uom)
+ .where(
+ (ip.item_code == item_code)
+ & (ip.price_list == args.get("price_list"))
+ & (IfNull(ip.uom, "").isin(["", args.get("uom")]))
+ & (IfNull(ip.batch_no, "").isin(["", args.get("batch_no")]))
+ )
+ .orderby(ip.valid_from, order=frappe.qb.desc)
+ .orderby(IfNull(ip.batch_no, ""), order=frappe.qb.desc)
+ .orderby(ip.uom, order=frappe.qb.desc)
+ )
if not ignore_party:
if args.get("customer"):
- conditions += " and customer=%(customer)s"
+ query = query.where(ip.customer == args.get("customer"))
elif args.get("supplier"):
- conditions += " and supplier=%(supplier)s"
+ query = query.where(ip.supplier == args.get("supplier"))
else:
- conditions += "and (customer is null or customer = '') and (supplier is null or supplier = '')"
+ query = query.where((IfNull(ip.customer, "") == "") & (IfNull(ip.supplier, "") == ""))
if args.get("transaction_date"):
- conditions += """ and %(transaction_date)s between
- ifnull(valid_from, '2000-01-01') and ifnull(valid_upto, '2500-12-31')"""
+ query = query.where(
+ (IfNull(ip.valid_from, "2000-01-01") <= args["transaction_date"])
+ & (IfNull(ip.valid_upto, "2500-12-31") >= args["transaction_date"])
+ )
- return frappe.db.sql(
- """ select name, price_list_rate, uom
- from `tabItem Price` {conditions}
- order by valid_from desc, ifnull(batch_no, '') desc, uom desc """.format(
- conditions=conditions
- ),
- args,
- )
+ return query.run()
def get_price_list_rate_for(args, item_code):
@@ -1091,91 +1090,68 @@
if not user:
user = frappe.session["user"]
- condition = "pfu.user = %(user)s AND pfu.default=1"
- if user and company:
- condition = "pfu.user = %(user)s AND pf.company = %(company)s AND pfu.default=1"
+ pf = frappe.qb.DocType("POS Profile")
+ pfu = frappe.qb.DocType("POS Profile User")
- pos_profile = frappe.db.sql(
- """SELECT pf.*
- FROM
- `tabPOS Profile` pf LEFT JOIN `tabPOS Profile User` pfu
- ON
- pf.name = pfu.parent
- WHERE
- {cond} AND pf.disabled = 0
- """.format(
- cond=condition
- ),
- {"user": user, "company": company},
- as_dict=1,
+ query = (
+ frappe.qb.from_(pf)
+ .left_join(pfu)
+ .on(pf.name == pfu.parent)
+ .select(pf.star)
+ .where((pfu.user == user) & (pfu.default == 1))
)
+ if company:
+ query = query.where(pf.company == company)
+
+ pos_profile = query.run(as_dict=True)
+
if not pos_profile and company:
- pos_profile = frappe.db.sql(
- """SELECT pf.*
- FROM
- `tabPOS Profile` pf LEFT JOIN `tabPOS Profile User` pfu
- ON
- pf.name = pfu.parent
- WHERE
- pf.company = %(company)s AND pf.disabled = 0
- """,
- {"company": company},
- as_dict=1,
- )
+ pos_profile = (
+ frappe.qb.from_(pf)
+ .left_join(pfu)
+ .on(pf.name == pfu.parent)
+ .select(pf.star)
+ .where((pf.company == company) & (pf.disabled == 0))
+ ).run(as_dict=True)
return pos_profile and pos_profile[0] or None
def get_serial_nos_by_fifo(args, sales_order=None):
if frappe.db.get_single_value("Stock Settings", "automatically_set_serial_nos_based_on_fifo"):
- return "\n".join(
- frappe.db.sql_list(
- """select name from `tabSerial No`
- where item_code=%(item_code)s and warehouse=%(warehouse)s and
- sales_order=IF(%(sales_order)s IS NULL, sales_order, %(sales_order)s)
- order by timestamp(purchase_date, purchase_time)
- asc limit %(qty)s""",
- {
- "item_code": args.item_code,
- "warehouse": args.warehouse,
- "qty": abs(cint(args.stock_qty)),
- "sales_order": sales_order,
- },
- )
+ sn = frappe.qb.DocType("Serial No")
+ query = (
+ frappe.qb.from_(sn)
+ .select(sn.name)
+ .where((sn.item_code == args.item_code) & (sn.warehouse == args.warehouse))
+ .orderby(CombineDatetime(sn.purchase_date, sn.purchase_time))
+ .limit(abs(cint(args.stock_qty)))
)
+ if sales_order:
+ query = query.where(sn.sales_order == sales_order)
+ if args.batch_no:
+ query = query.where(sn.batch_no == args.batch_no)
-def get_serial_no_batchwise(args, sales_order=None):
- if frappe.db.get_single_value("Stock Settings", "automatically_set_serial_nos_based_on_fifo"):
- return "\n".join(
- frappe.db.sql_list(
- """select name from `tabSerial No`
- where item_code=%(item_code)s and warehouse=%(warehouse)s and
- sales_order=IF(%(sales_order)s IS NULL, sales_order, %(sales_order)s)
- and batch_no=IF(%(batch_no)s IS NULL, batch_no, %(batch_no)s) order
- by timestamp(purchase_date, purchase_time) asc limit %(qty)s""",
- {
- "item_code": args.item_code,
- "warehouse": args.warehouse,
- "batch_no": args.batch_no,
- "qty": abs(cint(args.stock_qty)),
- "sales_order": sales_order,
- },
- )
- )
+ serial_nos = query.run(as_list=True)
+ serial_nos = [s[0] for s in serial_nos]
+
+ return "\n".join(serial_nos)
@frappe.whitelist()
def get_conversion_factor(item_code, uom):
variant_of = frappe.db.get_value("Item", item_code, "variant_of", cache=True)
filters = {"parent": item_code, "uom": uom}
+
if variant_of:
filters["parent"] = ("in", (item_code, variant_of))
conversion_factor = frappe.db.get_value("UOM Conversion Detail", filters, "conversion_factor")
if not conversion_factor:
stock_uom = frappe.db.get_value("Item", item_code, "stock_uom")
conversion_factor = get_uom_conv_factor(uom, stock_uom)
+
return {"conversion_factor": conversion_factor or 1.0}
@@ -1217,12 +1193,16 @@
def get_company_total_stock(item_code, company):
- return frappe.db.sql(
- """SELECT sum(actual_qty) from
- (`tabBin` INNER JOIN `tabWarehouse` ON `tabBin`.warehouse = `tabWarehouse`.name)
- WHERE `tabWarehouse`.company = %s and `tabBin`.item_code = %s""",
- (company, item_code),
- )[0][0]
+ bin = frappe.qb.DocType("Bin")
+ wh = frappe.qb.DocType("Warehouse")
+
+ return (
+ frappe.qb.from_(bin)
+ .inner_join(wh)
+ .on(bin.warehouse == wh.name)
+ .select(Sum(bin.actual_qty))
+ .where((wh.company == company) & (bin.item_code == item_code))
+ ).run()[0][0]
@frappe.whitelist()
@@ -1231,6 +1211,7 @@
{"item_code": item_code, "warehouse": warehouse, "stock_qty": stock_qty, "serial_no": serial_no}
)
serial_no = get_serial_no(args)
+
return {"serial_no": serial_no}
@@ -1250,6 +1231,7 @@
bin_details_and_serial_nos.update(
get_serial_no_details(item_code, warehouse, stock_qty, serial_no)
)
+
return bin_details_and_serial_nos
@@ -1264,6 +1246,7 @@
)
serial_no = get_serial_no(args)
batch_qty_and_serial_no.update({"serial_no": serial_no})
+
return batch_qty_and_serial_no
@@ -1336,7 +1319,6 @@
def apply_price_list_on_item(args):
item_doc = frappe.db.get_value("Item", args.item_code, ["name", "variant_of"], as_dict=1)
item_details = get_price_list_rate(args, item_doc)
-
item_details.update(get_pricing_rule_for_item(args))
return item_details
@@ -1420,12 +1402,12 @@
) or {"valuation_rate": 0}
elif not item.get("is_stock_item"):
- valuation_rate = frappe.db.sql(
- """select sum(base_net_amount) / sum(qty*conversion_factor)
- from `tabPurchase Invoice Item`
- where item_code = %s and docstatus=1""",
- item_code,
- )
+ pi_item = frappe.qb.DocType("Purchase Invoice Item")
+ valuation_rate = (
+ frappe.qb.from_(pi_item)
+ .select((Sum(pi_item.base_net_amount) / Sum(pi_item.qty * pi_item.conversion_factor)))
+ .where((pi_item.docstatus == 1) & (pi_item.item_code == item_code))
+ ).run()
if valuation_rate:
return {"valuation_rate": valuation_rate[0][0] or 0.0}
@@ -1451,7 +1433,7 @@
if args.get("warehouse") and args.get("stock_qty") and args.get("item_code"):
has_serial_no = frappe.get_value("Item", {"item_code": args.item_code}, "has_serial_no")
if args.get("batch_no") and has_serial_no == 1:
- return get_serial_no_batchwise(args, sales_order)
+ return get_serial_nos_by_fifo(args, sales_order)
elif has_serial_no == 1:
args = json.dumps(
{
@@ -1483,31 +1465,35 @@
args = frappe._dict(json.loads(args))
blanket_order_details = None
- condition = ""
- if args.item_code:
- if args.customer and args.doctype == "Sales Order":
- condition = " and bo.customer=%(customer)s"
- elif args.supplier and args.doctype == "Purchase Order":
- condition = " and bo.supplier=%(supplier)s"
- if args.blanket_order:
- condition += " and bo.name =%(blanket_order)s"
- if args.transaction_date:
- condition += " and bo.to_date>=%(transaction_date)s"
- blanket_order_details = frappe.db.sql(
- """
- select boi.rate as blanket_order_rate, bo.name as blanket_order
- from `tabBlanket Order` bo, `tabBlanket Order Item` boi
- where bo.company=%(company)s and boi.item_code=%(item_code)s
- and bo.docstatus=1 and bo.name = boi.parent {0}
- """.format(
- condition
- ),
- args,
- as_dict=True,
+ if args.item_code:
+ bo = frappe.qb.DocType("Blanket Order")
+ bo_item = frappe.qb.DocType("Blanket Order Item")
+
+ query = (
+ frappe.qb.from_(bo)
+ .from_(bo_item)
+ .select(bo_item.rate.as_("blanket_order_rate"), bo.name.as_("blanket_order"))
+ .where(
+ (bo.company == args.company)
+ & (bo_item.item_code == args.item_code)
+ & (bo.docstatus == 1)
+ & (bo.name == bo_item.parent)
+ )
)
+ if args.customer and args.doctype == "Sales Order":
+ query = query.where(bo.customer == args.customer)
+ elif args.supplier and args.doctype == "Purchase Order":
+ query = query.where(bo.supplier == args.supplier)
+ if args.blanket_order:
+ query = query.where(bo.name == args.blanket_order)
+ if args.transaction_date:
+ query = query.where(bo.to_date >= args.transaction_date)
+
+ blanket_order_details = query.run(as_dict=True)
blanket_order_details = blanket_order_details[0] if blanket_order_details else ""
+
return blanket_order_details
@@ -1517,10 +1503,10 @@
if get_reserved_qty_for_so(args.get("against_sales_order"), args.get("item_code")):
reserved_so = args.get("against_sales_order")
elif args.get("against_sales_invoice"):
- sales_order = frappe.db.sql(
- """select sales_order from `tabSales Invoice Item` where
- parent=%s and item_code=%s""",
- (args.get("against_sales_invoice"), args.get("item_code")),
+ sales_order = frappe.db.get_all(
+ "Sales Invoice Item",
+ filters={"parent": args.get("against_sales_invoice"), "item_code": args.get("item_code")},
+ fields="sales_order",
)
if sales_order and sales_order[0]:
if get_reserved_qty_for_so(sales_order[0][0], args.get("item_code")):
@@ -1532,13 +1518,14 @@
def get_reserved_qty_for_so(sales_order, item_code):
- reserved_qty = frappe.db.sql(
- """select sum(qty) from `tabSales Order Item`
- where parent=%s and item_code=%s and ensure_delivery_based_on_produced_serial_no=1
- """,
- (sales_order, item_code),
+ reserved_qty = frappe.db.get_value(
+ "Sales Order Item",
+ filters={
+ "parent": sales_order,
+ "item_code": item_code,
+ "ensure_delivery_based_on_produced_serial_no": 1,
+ },
+ fieldname="sum(qty)",
)
- if reserved_qty and reserved_qty[0][0]:
- return reserved_qty[0][0]
- else:
- return 0
+
+ return reserved_qty or 0