Merge branch 'develop' into refactor/stock/remaining-reports
diff --git a/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json b/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json
index dff883a..a0ea433 100644
--- a/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json
+++ b/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json
@@ -202,6 +202,7 @@
"fieldname": "reference_type",
"fieldtype": "Select",
"label": "Reference Type",
+ "no_copy": 1,
"options": "\nSales Invoice\nPurchase Invoice\nJournal Entry\nSales Order\nPurchase Order\nExpense Claim\nAsset\nLoan\nPayroll Entry\nEmployee Advance\nExchange Rate Revaluation\nInvoice Discounting\nFees\nFull and Final Statement"
},
{
@@ -209,13 +210,15 @@
"fieldtype": "Dynamic Link",
"in_list_view": 1,
"label": "Reference Name",
+ "no_copy": 1,
"options": "reference_type"
},
{
"depends_on": "eval:doc.reference_type&&!in_list(doc.reference_type, ['Expense Claim', 'Asset', 'Employee Loan', 'Employee Advance'])",
"fieldname": "reference_due_date",
"fieldtype": "Select",
- "label": "Reference Due Date"
+ "label": "Reference Due Date",
+ "no_copy": 1
},
{
"fieldname": "project",
@@ -274,19 +277,22 @@
"fieldname": "reference_detail_no",
"fieldtype": "Data",
"hidden": 1,
- "label": "Reference Detail No"
+ "label": "Reference Detail No",
+ "no_copy": 1
}
],
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2021-08-30 21:27:32.200299",
+ "modified": "2022-10-13 17:07:17.999191",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Journal Entry Account",
+ "naming_rule": "Random",
"owner": "Administrator",
"permissions": [],
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 7f245fd..9487489 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -995,7 +995,9 @@
if self.payment_type in ("Receive", "Pay") and self.party:
for d in self.get("references"):
if d.allocated_amount and d.reference_doctype in frappe.get_hooks("advance_payment_doctypes"):
- frappe.get_doc(d.reference_doctype, d.reference_name).set_total_advance_paid()
+ frappe.get_doc(
+ d.reference_doctype, d.reference_name, for_update=True
+ ).set_total_advance_paid()
def on_recurring(self, reference_doc, auto_repeat_doc):
self.reference_no = reference_doc.name
diff --git a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
index 9af3188..826d71b 100644
--- a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
+++ b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
@@ -268,6 +268,18 @@
return item_details
+def update_pricing_rule_uom(pricing_rule, args):
+ child_doc = {"Item Code": "items", "Item Group": "item_groups", "Brand": "brands"}.get(
+ pricing_rule.apply_on
+ )
+
+ apply_on_field = frappe.scrub(pricing_rule.apply_on)
+
+ for row in pricing_rule.get(child_doc):
+ if row.get(apply_on_field) == args.get(apply_on_field):
+ pricing_rule.uom = row.uom
+
+
def get_pricing_rule_for_item(args, price_list_rate=0, doc=None, for_validate=False):
from erpnext.accounts.doctype.pricing_rule.utils import (
get_applied_pricing_rules,
@@ -324,6 +336,7 @@
if isinstance(pricing_rule, str):
pricing_rule = frappe.get_cached_doc("Pricing Rule", pricing_rule)
+ update_pricing_rule_uom(pricing_rule, args)
pricing_rule.apply_rule_on_other_items = get_pricing_rule_items(pricing_rule) or []
if pricing_rule.get("suggestion"):
@@ -440,12 +453,15 @@
if pricing_rule.currency == args.currency:
pricing_rule_rate = pricing_rule.rate
+ # TODO https://github.com/frappe/erpnext/pull/23636 solve this in some other way.
if pricing_rule_rate:
+ is_blank_uom = pricing_rule.get("uom") != args.get("uom")
# Override already set price list rate (from item price)
# if pricing_rule_rate > 0
item_details.update(
{
- "price_list_rate": pricing_rule_rate * args.get("conversion_factor", 1),
+ "price_list_rate": pricing_rule_rate
+ * (args.get("conversion_factor", 1) if is_blank_uom else 1),
}
)
item_details.update({"discount_percentage": 0.0})
diff --git a/erpnext/accounts/doctype/pricing_rule/test_pricing_rule.py b/erpnext/accounts/doctype/pricing_rule/test_pricing_rule.py
index 0a9db6b..fbe5678 100644
--- a/erpnext/accounts/doctype/pricing_rule/test_pricing_rule.py
+++ b/erpnext/accounts/doctype/pricing_rule/test_pricing_rule.py
@@ -595,6 +595,121 @@
frappe.get_doc("Item Price", {"item_code": "Water Flask"}).delete()
item.delete()
+ def test_item_price_with_blank_uom_pricing_rule(self):
+ properties = {
+ "item_code": "Item Blank UOM",
+ "stock_uom": "Nos",
+ "sales_uom": "Box",
+ "uoms": [dict(uom="Box", conversion_factor=10)],
+ }
+ item = make_item(properties=properties)
+
+ make_item_price("Item Blank UOM", "_Test Price List", 100)
+
+ pricing_rule_record = {
+ "doctype": "Pricing Rule",
+ "title": "_Test Item Blank UOM Rule",
+ "apply_on": "Item Code",
+ "items": [
+ {
+ "item_code": "Item Blank UOM",
+ }
+ ],
+ "selling": 1,
+ "currency": "INR",
+ "rate_or_discount": "Rate",
+ "rate": 101,
+ "company": "_Test Company",
+ }
+ rule = frappe.get_doc(pricing_rule_record)
+ rule.insert()
+
+ si = create_sales_invoice(
+ do_not_save=True, item_code="Item Blank UOM", uom="Box", conversion_factor=10
+ )
+ si.selling_price_list = "_Test Price List"
+ si.save()
+
+ # If UOM is blank consider it as stock UOM and apply pricing_rule on all UOM.
+ # rate is 101, Selling UOM is Box that have conversion_factor of 10 so 101 * 10 = 1010
+ self.assertEqual(si.items[0].price_list_rate, 1010)
+ self.assertEqual(si.items[0].rate, 1010)
+
+ si.delete()
+
+ si = create_sales_invoice(do_not_save=True, item_code="Item Blank UOM", uom="Nos")
+ si.selling_price_list = "_Test Price List"
+ si.save()
+
+ # UOM is blank so consider it as stock UOM and apply pricing_rule on all UOM.
+ # rate is 101, Selling UOM is Nos that have conversion_factor of 1 so 101 * 1 = 101
+ self.assertEqual(si.items[0].price_list_rate, 101)
+ self.assertEqual(si.items[0].rate, 101)
+
+ si.delete()
+ rule.delete()
+ frappe.get_doc("Item Price", {"item_code": "Item Blank UOM"}).delete()
+
+ item.delete()
+
+ def test_item_price_with_selling_uom_pricing_rule(self):
+ properties = {
+ "item_code": "Item UOM other than Stock",
+ "stock_uom": "Nos",
+ "sales_uom": "Box",
+ "uoms": [dict(uom="Box", conversion_factor=10)],
+ }
+ item = make_item(properties=properties)
+
+ make_item_price("Item UOM other than Stock", "_Test Price List", 100)
+
+ pricing_rule_record = {
+ "doctype": "Pricing Rule",
+ "title": "_Test Item UOM other than Stock Rule",
+ "apply_on": "Item Code",
+ "items": [
+ {
+ "item_code": "Item UOM other than Stock",
+ "uom": "Box",
+ }
+ ],
+ "selling": 1,
+ "currency": "INR",
+ "rate_or_discount": "Rate",
+ "rate": 101,
+ "company": "_Test Company",
+ }
+ rule = frappe.get_doc(pricing_rule_record)
+ rule.insert()
+
+ si = create_sales_invoice(
+ do_not_save=True, item_code="Item UOM other than Stock", uom="Box", conversion_factor=10
+ )
+ si.selling_price_list = "_Test Price List"
+ si.save()
+
+ # UOM is Box so apply pricing_rule only on Box UOM.
+ # Selling UOM is Box and as both UOM are same no need to multiply by conversion_factor.
+ self.assertEqual(si.items[0].price_list_rate, 101)
+ self.assertEqual(si.items[0].rate, 101)
+
+ si.delete()
+
+ si = create_sales_invoice(do_not_save=True, item_code="Item UOM other than Stock", uom="Nos")
+ si.selling_price_list = "_Test Price List"
+ si.save()
+
+ # UOM is Box so pricing_rule won't apply as selling_uom is Nos.
+ # As Pricing Rule is not applied price of 100 will be fetched from Item Price List.
+ self.assertEqual(si.items[0].price_list_rate, 100)
+ self.assertEqual(si.items[0].rate, 100)
+
+ si.delete()
+ rule.delete()
+ frappe.get_doc("Item Price", {"item_code": "Item UOM other than Stock"}).delete()
+
+ item.delete()
+
def test_pricing_rule_for_different_currency(self):
make_item("Test Sanitizer Item")
diff --git a/erpnext/accounts/doctype/pricing_rule/utils.py b/erpnext/accounts/doctype/pricing_rule/utils.py
index 1f29d73..4c78d72 100644
--- a/erpnext/accounts/doctype/pricing_rule/utils.py
+++ b/erpnext/accounts/doctype/pricing_rule/utils.py
@@ -111,6 +111,12 @@
)
if apply_on_field == "item_code":
+ if args.get("uom", None):
+ item_conditions += (
+ " and ({child_doc}.uom='{item_uom}' or IFNULL({child_doc}.uom, '')='')".format(
+ child_doc=child_doc, item_uom=args.get("uom")
+ )
+ )
if "variant_of" not in args:
args.variant_of = frappe.get_cached_value("Item", args.item_code, "variant_of")
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
index ec861a2..c3a9855 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
@@ -31,7 +31,7 @@
super.onload();
// Ignore linked advances
- this.frm.ignore_doctypes_on_cancel_all = ['Journal Entry', 'Payment Entry'];
+ this.frm.ignore_doctypes_on_cancel_all = ['Journal Entry', 'Payment Entry', 'Purchase Invoice'];
if(!this.frm.doc.__islocal) {
// show credit_to in print format
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 5dbe7eb..3d74b8f 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -1415,6 +1415,7 @@
"Stock Ledger Entry",
"Repost Item Valuation",
"Payment Ledger Entry",
+ "Purchase Invoice",
)
self.update_advance_tax_references(cancel=1)
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index afd5a59..0c03c55 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -2017,6 +2017,9 @@
update_address(
target_doc, "shipping_address", "shipping_address_display", source_doc.customer_address
)
+ update_address(
+ target_doc, "billing_address", "billing_address_display", source_doc.customer_address
+ )
if currency:
target_doc.currency = currency
diff --git a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
index 301d3e1..1ba7824 100644
--- a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
@@ -3228,6 +3228,22 @@
self.assertTrue(return_si.docstatus == 1)
+ def test_sales_invoice_with_payable_tax_account(self):
+ si = create_sales_invoice(do_not_submit=True)
+ si.append(
+ "taxes",
+ {
+ "charge_type": "Actual",
+ "account_head": "Creditors - _TC",
+ "description": "Test",
+ "cost_center": "Main - _TC",
+ "tax_amount": 10,
+ "total": 10,
+ "dont_recompute_tax": 0,
+ },
+ )
+ self.assertRaises(frappe.ValidationError, si.submit)
+
def get_sales_invoice_for_e_invoice():
si = make_sales_invoice_for_ewaybill()
@@ -3320,7 +3336,7 @@
"asset": args.asset or None,
"cost_center": args.cost_center or "_Test Cost Center - _TC",
"serial_no": args.serial_no,
- "conversion_factor": 1,
+ "conversion_factor": args.get("conversion_factor", 1),
"incoming_rate": args.incoming_rate or 0,
"batch_no": args.batch_no or None,
},
diff --git a/erpnext/accounts/doctype/subscription_plan/subscription_plan.py b/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
index a95e0a9..f3acdc5 100644
--- a/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
+++ b/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
@@ -3,6 +3,7 @@
import frappe
+from dateutil import relativedelta
from frappe import _
from frappe.model.document import Document
from frappe.utils import date_diff, flt, get_first_day, get_last_day, getdate
@@ -49,7 +50,7 @@
start_date = getdate(start_date)
end_date = getdate(end_date)
- no_of_months = (end_date.year - start_date.year) * 12 + (end_date.month - start_date.month) + 1
+ no_of_months = relativedelta.relativedelta(end_date, start_date).months + 1
cost = plan.cost * no_of_months
# Adjust cost if start or end date is not month start or end
diff --git a/erpnext/accounts/report/payment_ledger/__init__.py b/erpnext/accounts/report/payment_ledger/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/payment_ledger/__init__.py
diff --git a/erpnext/accounts/report/payment_ledger/payment_ledger.js b/erpnext/accounts/report/payment_ledger/payment_ledger.js
new file mode 100644
index 0000000..9779844
--- /dev/null
+++ b/erpnext/accounts/report/payment_ledger/payment_ledger.js
@@ -0,0 +1,59 @@
+// Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+function get_filters() {
+ let filters = [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"period_start_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1)
+ },
+ {
+ "fieldname":"period_end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname":"account",
+ "label": __("Account"),
+ "fieldtype": "MultiSelectList",
+ "options": "Account",
+ get_data: function(txt) {
+ return frappe.db.get_link_options('Account', txt, {
+ company: frappe.query_report.get_filter_value("company")
+ });
+ }
+ },
+ {
+ "fieldname":"voucher_no",
+ "label": __("Voucher No"),
+ "fieldtype": "Data",
+ "width": 100,
+ },
+ {
+ "fieldname":"against_voucher_no",
+ "label": __("Against Voucher No"),
+ "fieldtype": "Data",
+ "width": 100,
+ },
+
+ ]
+ return filters;
+}
+
+frappe.query_reports["Payment Ledger"] = {
+ "filters": get_filters()
+};
diff --git a/erpnext/accounts/report/payment_ledger/payment_ledger.json b/erpnext/accounts/report/payment_ledger/payment_ledger.json
new file mode 100644
index 0000000..716329f
--- /dev/null
+++ b/erpnext/accounts/report/payment_ledger/payment_ledger.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2022-06-06 08:50:43.933708",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2022-06-06 08:50:43.933708",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Payment Ledger",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Payment Ledger Entry",
+ "report_name": "Payment Ledger",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/payment_ledger/payment_ledger.py b/erpnext/accounts/report/payment_ledger/payment_ledger.py
new file mode 100644
index 0000000..e470c27
--- /dev/null
+++ b/erpnext/accounts/report/payment_ledger/payment_ledger.py
@@ -0,0 +1,222 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from collections import OrderedDict
+
+import frappe
+from frappe import _, qb
+from frappe.query_builder import Criterion
+
+
+class PaymentLedger(object):
+ def __init__(self, filters=None):
+ self.filters = filters
+ self.columns, self.data = [], []
+ self.voucher_dict = OrderedDict()
+ self.voucher_amount = []
+ self.ple = qb.DocType("Payment Ledger Entry")
+
+ def init_voucher_dict(self):
+
+ if self.voucher_amount:
+ s = set()
+ # build a set of unique vouchers
+ for ple in self.voucher_amount:
+ key = (ple.voucher_type, ple.voucher_no, ple.party)
+ s.add(key)
+
+ # for each unique vouchers, initialize +/- list
+ for key in s:
+ self.voucher_dict[key] = frappe._dict(increase=list(), decrease=list())
+
+ # for each ple, using against voucher and amount, assign it to +/- list
+ # group by against voucher
+ for ple in self.voucher_amount:
+ against_key = (ple.against_voucher_type, ple.against_voucher_no, ple.party)
+ target = None
+ if self.voucher_dict.get(against_key):
+ if ple.amount > 0:
+ target = self.voucher_dict.get(against_key).increase
+ else:
+ target = self.voucher_dict.get(against_key).decrease
+
+ # this if condition will lose unassigned ple entries(against_voucher doc doesn't have ple)
+ # need to somehow include the stray entries as well.
+ if target is not None:
+ entry = frappe._dict(
+ company=ple.company,
+ account=ple.account,
+ party_type=ple.party_type,
+ party=ple.party,
+ voucher_type=ple.voucher_type,
+ voucher_no=ple.voucher_no,
+ against_voucher_type=ple.against_voucher_type,
+ against_voucher_no=ple.against_voucher_no,
+ amount=ple.amount,
+ currency=ple.account_currency,
+ )
+
+ if self.filters.include_account_currency:
+ entry["amount_in_account_currency"] = ple.amount_in_account_currency
+
+ target.append(entry)
+
+ def build_data(self):
+ self.data.clear()
+
+ for value in self.voucher_dict.values():
+ voucher_data = []
+ if value.increase != []:
+ voucher_data.extend(value.increase)
+ if value.decrease != []:
+ voucher_data.extend(value.decrease)
+
+ if voucher_data:
+ # balance row
+ total = 0
+ total_in_account_currency = 0
+
+ for x in voucher_data:
+ total += x.amount
+ if self.filters.include_account_currency:
+ total_in_account_currency += x.amount_in_account_currency
+
+ entry = frappe._dict(
+ against_voucher_no="Outstanding:",
+ amount=total,
+ currency=voucher_data[0].currency,
+ )
+
+ if self.filters.include_account_currency:
+ entry["amount_in_account_currency"] = total_in_account_currency
+
+ voucher_data.append(entry)
+
+ # empty row
+ voucher_data.append(frappe._dict())
+ self.data.extend(voucher_data)
+
+ def build_conditions(self):
+ self.conditions = []
+
+ if self.filters.company:
+ self.conditions.append(self.ple.company == self.filters.company)
+
+ if self.filters.account:
+ self.conditions.append(self.ple.account.isin(self.filters.account))
+
+ if self.filters.period_start_date:
+ self.conditions.append(self.ple.posting_date.gte(self.filters.period_start_date))
+
+ if self.filters.period_end_date:
+ self.conditions.append(self.ple.posting_date.lte(self.filters.period_end_date))
+
+ if self.filters.voucher_no:
+ self.conditions.append(self.ple.voucher_no == self.filters.voucher_no)
+
+ if self.filters.against_voucher_no:
+ self.conditions.append(self.ple.against_voucher_no == self.filters.against_voucher_no)
+
+ def get_data(self):
+ ple = self.ple
+
+ self.build_conditions()
+
+ # fetch data from table
+ self.voucher_amount = (
+ qb.from_(ple)
+ .select(ple.star)
+ .where(ple.delinked == 0)
+ .where(Criterion.all(self.conditions))
+ .run(as_dict=True)
+ )
+
+ def get_columns(self):
+ options = None
+ self.columns.append(
+ dict(label=_("Company"), fieldname="company", fieldtype="data", options=options, width="100")
+ )
+
+ self.columns.append(
+ dict(label=_("Account"), fieldname="account", fieldtype="data", options=options, width="100")
+ )
+
+ self.columns.append(
+ dict(
+ label=_("Party Type"), fieldname="party_type", fieldtype="data", options=options, width="100"
+ )
+ )
+ self.columns.append(
+ dict(label=_("Party"), fieldname="party", fieldtype="data", options=options, width="100")
+ )
+ self.columns.append(
+ dict(
+ label=_("Voucher Type"),
+ fieldname="voucher_type",
+ fieldtype="data",
+ options=options,
+ width="100",
+ )
+ )
+ self.columns.append(
+ dict(
+ label=_("Voucher No"), fieldname="voucher_no", fieldtype="data", options=options, width="100"
+ )
+ )
+ self.columns.append(
+ dict(
+ label=_("Against Voucher Type"),
+ fieldname="against_voucher_type",
+ fieldtype="data",
+ options=options,
+ width="100",
+ )
+ )
+ self.columns.append(
+ dict(
+ label=_("Against Voucher No"),
+ fieldname="against_voucher_no",
+ fieldtype="data",
+ options=options,
+ width="100",
+ )
+ )
+ self.columns.append(
+ dict(
+ label=_("Amount"),
+ fieldname="amount",
+ fieldtype="Currency",
+ options="Company:company:default_currency",
+ width="100",
+ )
+ )
+
+ if self.filters.include_account_currency:
+ self.columns.append(
+ dict(
+ label=_("Amount in Account Currency"),
+ fieldname="amount_in_account_currency",
+ fieldtype="Currency",
+ options="currency",
+ width="100",
+ )
+ )
+ self.columns.append(
+ dict(label=_("Currency"), fieldname="currency", fieldtype="Currency", hidden=True)
+ )
+
+ def run(self):
+ self.get_columns()
+ self.get_data()
+
+ # initialize dictionary and group using against voucher
+ self.init_voucher_dict()
+
+ # convert dictionary to list and add balance rows
+ self.build_data()
+
+ return self.columns, self.data
+
+
+def execute(filters=None):
+ return PaymentLedger(filters).run()
diff --git a/erpnext/accounts/report/payment_ledger/test_payment_ledger.py b/erpnext/accounts/report/payment_ledger/test_payment_ledger.py
new file mode 100644
index 0000000..5ae9b87
--- /dev/null
+++ b/erpnext/accounts/report/payment_ledger/test_payment_ledger.py
@@ -0,0 +1,65 @@
+import unittest
+
+import frappe
+from frappe import qb
+from frappe.tests.utils import FrappeTestCase
+
+from erpnext.accounts.doctype.payment_entry.payment_entry import get_payment_entry
+from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.accounts.report.payment_ledger.payment_ledger import execute
+
+
+class TestPaymentLedger(FrappeTestCase):
+ def setUp(self):
+ self.create_company()
+ self.cleanup()
+
+ def cleanup(self):
+ doctypes = []
+ doctypes.append(qb.DocType("GL Entry"))
+ doctypes.append(qb.DocType("Payment Ledger Entry"))
+ doctypes.append(qb.DocType("Sales Invoice"))
+ doctypes.append(qb.DocType("Payment Entry"))
+
+ for doctype in doctypes:
+ qb.from_(doctype).delete().where(doctype.company == self.company).run()
+
+ def create_company(self):
+ name = "Test Payment Ledger"
+ company = None
+ if frappe.db.exists("Company", name):
+ company = frappe.get_doc("Company", name)
+ else:
+ company = frappe.get_doc(
+ {
+ "doctype": "Company",
+ "company_name": name,
+ "country": "India",
+ "default_currency": "INR",
+ "create_chart_of_accounts_based_on": "Standard Template",
+ "chart_of_accounts": "Standard",
+ }
+ )
+ company = company.save()
+ self.company = company.name
+ self.cost_center = company.cost_center
+ self.warehouse = "All Warehouses" + " - " + company.abbr
+ self.income_account = company.default_income_account
+ self.expense_account = company.default_expense_account
+ self.debit_to = company.default_receivable_account
+
+ def test_unpaid_invoice_outstanding(self):
+ sinv = create_sales_invoice(
+ company=self.company,
+ debit_to=self.debit_to,
+ expense_account=self.expense_account,
+ cost_center=self.cost_center,
+ income_account=self.income_account,
+ warehouse=self.warehouse,
+ )
+ pe = get_payment_entry(sinv.doctype, sinv.name).save().submit()
+
+ filters = frappe._dict({"company": self.company})
+ columns, data = execute(filters=filters)
+ outstanding = [x for x in data if x.get("against_voucher_no") == "Outstanding:"]
+ self.assertEqual(outstanding[0].get("amount"), 0)
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 02dcd68..d7bf991 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -460,10 +460,6 @@
frappe.flags.ignore_party_validation = False
- if entry.voucher_type in ("Payment Entry", "Journal Entry"):
- if hasattr(doc, "update_expense_claim"):
- doc.update_expense_claim()
-
def check_if_advance_entry_modified(args):
"""
@@ -1372,9 +1368,8 @@
frappe.delete_doc("Desktop Icon", icon)
-def create_payment_ledger_entry(
- gl_entries, cancel=0, adv_adj=0, update_outstanding="Yes", from_repost=0
-):
+def get_payment_ledger_entries(gl_entries, cancel=0):
+ ple_map = []
if gl_entries:
ple = None
@@ -1414,44 +1409,57 @@
dr_or_cr *= -1
dr_or_cr_account_currency *= -1
- ple = frappe.get_doc(
- {
- "doctype": "Payment Ledger Entry",
- "posting_date": gle.posting_date,
- "company": gle.company,
- "account_type": account_type,
- "account": gle.account,
- "party_type": gle.party_type,
- "party": gle.party,
- "cost_center": gle.cost_center,
- "finance_book": gle.finance_book,
- "due_date": gle.due_date,
- "voucher_type": gle.voucher_type,
- "voucher_no": gle.voucher_no,
- "against_voucher_type": gle.against_voucher_type
- if gle.against_voucher_type
- else gle.voucher_type,
- "against_voucher_no": gle.against_voucher if gle.against_voucher else gle.voucher_no,
- "account_currency": gle.account_currency,
- "amount": dr_or_cr,
- "amount_in_account_currency": dr_or_cr_account_currency,
- "delinked": True if cancel else False,
- "remarks": gle.remarks,
- }
+ ple = frappe._dict(
+ doctype="Payment Ledger Entry",
+ posting_date=gle.posting_date,
+ company=gle.company,
+ account_type=account_type,
+ account=gle.account,
+ party_type=gle.party_type,
+ party=gle.party,
+ cost_center=gle.cost_center,
+ finance_book=gle.finance_book,
+ due_date=gle.due_date,
+ voucher_type=gle.voucher_type,
+ voucher_no=gle.voucher_no,
+ against_voucher_type=gle.against_voucher_type
+ if gle.against_voucher_type
+ else gle.voucher_type,
+ against_voucher_no=gle.against_voucher if gle.against_voucher else gle.voucher_no,
+ account_currency=gle.account_currency,
+ amount=dr_or_cr,
+ amount_in_account_currency=dr_or_cr_account_currency,
+ delinked=True if cancel else False,
+ remarks=gle.remarks,
)
dimensions_and_defaults = get_dimensions()
if dimensions_and_defaults:
for dimension in dimensions_and_defaults[0]:
- ple.set(dimension.fieldname, gle.get(dimension.fieldname))
+ ple[dimension.fieldname] = gle.get(dimension.fieldname)
- if cancel:
- delink_original_entry(ple)
- ple.flags.ignore_permissions = 1
- ple.flags.adv_adj = adv_adj
- ple.flags.from_repost = from_repost
- ple.flags.update_outstanding = update_outstanding
- ple.submit()
+ ple_map.append(ple)
+ return ple_map
+
+
+def create_payment_ledger_entry(
+ gl_entries, cancel=0, adv_adj=0, update_outstanding="Yes", from_repost=0
+):
+ if gl_entries:
+ ple_map = get_payment_ledger_entries(gl_entries, cancel=cancel)
+
+ for entry in ple_map:
+
+ ple = frappe.get_doc(entry)
+
+ if cancel:
+ delink_original_entry(ple)
+
+ ple.flags.ignore_permissions = 1
+ ple.flags.adv_adj = adv_adj
+ ple.flags.from_repost = from_repost
+ ple.flags.update_outstanding = update_outstanding
+ ple.submit()
def update_voucher_outstanding(voucher_type, voucher_no, account, party_type, party):
@@ -1471,7 +1479,12 @@
# on cancellation outstanding can be an empty list
voucher_outstanding = ple_query.get_voucher_outstandings(vouchers, common_filter=common_filter)
- if voucher_type in ["Sales Invoice", "Purchase Invoice", "Fees"] and voucher_outstanding:
+ if (
+ voucher_type in ["Sales Invoice", "Purchase Invoice", "Fees"]
+ and party_type
+ and party
+ and voucher_outstanding
+ ):
outstanding = voucher_outstanding[0]
ref_doc = frappe.get_doc(voucher_type, voucher_no)
diff --git a/erpnext/buying/doctype/purchase_order/purchase_order.py b/erpnext/buying/doctype/purchase_order/purchase_order.py
index bcedd4d..c224b61 100644
--- a/erpnext/buying/doctype/purchase_order/purchase_order.py
+++ b/erpnext/buying/doctype/purchase_order/purchase_order.py
@@ -18,7 +18,7 @@
from erpnext.accounts.doctype.tax_withholding_category.tax_withholding_category import (
get_party_tax_withholding_details,
)
-from erpnext.accounts.party import get_party_account_currency
+from erpnext.accounts.party import get_party_account, get_party_account_currency
from erpnext.buying.utils import check_on_hold_or_closed_status, validate_for_items
from erpnext.controllers.buying_controller import BuyingController
from erpnext.setup.doctype.item_group.item_group import get_item_group_defaults
@@ -558,6 +558,7 @@
target.set_advances()
target.set_payment_schedule()
+ target.credit_to = get_party_account("Supplier", source.supplier, source.company)
def update_item(obj, target, source_parent):
target.amount = flt(obj.amount) - flt(obj.billed_amt)
diff --git a/erpnext/buying/report/procurement_tracker/procurement_tracker.py b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
index d70ac46..71019e8 100644
--- a/erpnext/buying/report/procurement_tracker/procurement_tracker.py
+++ b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
@@ -127,32 +127,27 @@
return columns
-def get_conditions(filters):
- conditions = ""
-
+def apply_filters_on_query(filters, parent, child, query):
if filters.get("company"):
- conditions += " AND parent.company=%s" % frappe.db.escape(filters.get("company"))
+ query = query.where(parent.company == filters.get("company"))
if filters.get("cost_center") or filters.get("project"):
- conditions += """
- AND (child.`cost_center`=%s OR child.`project`=%s)
- """ % (
- frappe.db.escape(filters.get("cost_center")),
- frappe.db.escape(filters.get("project")),
+ query = query.where(
+ (child.cost_center == filters.get("cost_center")) | (child.project == filters.get("project"))
)
if filters.get("from_date"):
- conditions += " AND parent.transaction_date>='%s'" % filters.get("from_date")
+ query = query.where(parent.transaction_date >= filters.get("from_date"))
if filters.get("to_date"):
- conditions += " AND parent.transaction_date<='%s'" % filters.get("to_date")
- return conditions
+ query = query.where(parent.transaction_date <= filters.get("to_date"))
+
+ return query
def get_data(filters):
- conditions = get_conditions(filters)
- purchase_order_entry = get_po_entries(conditions)
- mr_records, procurement_record_against_mr = get_mapped_mr_details(conditions)
+ purchase_order_entry = get_po_entries(filters)
+ mr_records, procurement_record_against_mr = get_mapped_mr_details(filters)
pr_records = get_mapped_pr_records()
pi_records = get_mapped_pi_records()
@@ -187,11 +182,15 @@
return procurement_record
-def get_mapped_mr_details(conditions):
+def get_mapped_mr_details(filters):
mr_records = {}
- mr_details = frappe.db.sql(
- """
- SELECT
+ parent = frappe.qb.DocType("Material Request")
+ child = frappe.qb.DocType("Material Request Item")
+
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(
parent.transaction_date,
parent.per_ordered,
parent.owner,
@@ -203,18 +202,13 @@
child.uom,
parent.status,
child.project,
- child.cost_center
- FROM `tabMaterial Request` parent, `tabMaterial Request Item` child
- WHERE
- parent.per_ordered>=0
- AND parent.name=child.parent
- AND parent.docstatus=1
- {conditions}
- """.format(
- conditions=conditions
- ),
- as_dict=1,
- ) # nosec
+ child.cost_center,
+ )
+ .where((parent.per_ordered >= 0) & (parent.name == child.parent) & (parent.docstatus == 1))
+ )
+ query = apply_filters_on_query(filters, parent, child, query)
+
+ mr_details = query.run(as_dict=True)
procurement_record_against_mr = []
for record in mr_details:
@@ -241,46 +235,49 @@
def get_mapped_pi_records():
- return frappe._dict(
- frappe.db.sql(
- """
- SELECT
- pi_item.po_detail,
- pi_item.base_amount
- FROM `tabPurchase Invoice Item` as pi_item
- INNER JOIN `tabPurchase Order` as po
- ON pi_item.`purchase_order` = po.`name`
- WHERE
- pi_item.docstatus = 1
- AND po.status not in ('Closed','Completed','Cancelled')
- AND pi_item.po_detail IS NOT NULL
- """
+ po = frappe.qb.DocType("Purchase Order")
+ pi_item = frappe.qb.DocType("Purchase Invoice Item")
+ pi_records = (
+ frappe.qb.from_(pi_item)
+ .inner_join(po)
+ .on(pi_item.purchase_order == po.name)
+ .select(pi_item.po_detail, pi_item.base_amount)
+ .where(
+ (pi_item.docstatus == 1)
+ & (po.status.notin(("Closed", "Completed", "Cancelled")))
+ & (pi_item.po_detail.isnotnull())
)
- )
+ ).run()
+
+ return frappe._dict(pi_records)
def get_mapped_pr_records():
- return frappe._dict(
- frappe.db.sql(
- """
- SELECT
- pr_item.purchase_order_item,
- pr.posting_date
- FROM `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
- WHERE
- pr.docstatus=1
- AND pr.name=pr_item.parent
- AND pr_item.purchase_order_item IS NOT NULL
- AND pr.status not in ('Closed','Completed','Cancelled')
- """
+ pr = frappe.qb.DocType("Purchase Receipt")
+ pr_item = frappe.qb.DocType("Purchase Receipt Item")
+ pr_records = (
+ frappe.qb.from_(pr)
+ .from_(pr_item)
+ .select(pr_item.purchase_order_item, pr.posting_date)
+ .where(
+ (pr.docstatus == 1)
+ & (pr.name == pr_item.parent)
+ & (pr_item.purchase_order_item.isnotnull())
+ & (pr.status.notin(("Closed", "Completed", "Cancelled")))
)
- )
+ ).run()
+
+ return frappe._dict(pr_records)
-def get_po_entries(conditions):
- return frappe.db.sql(
- """
- SELECT
+def get_po_entries(filters):
+ parent = frappe.qb.DocType("Purchase Order")
+ child = frappe.qb.DocType("Purchase Order Item")
+
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(
child.name,
child.parent,
child.cost_center,
@@ -297,17 +294,15 @@
parent.transaction_date,
parent.supplier,
parent.status,
- parent.owner
- FROM `tabPurchase Order` parent, `tabPurchase Order Item` child
- WHERE
- parent.docstatus = 1
- AND parent.name = child.parent
- AND parent.status not in ('Closed','Completed','Cancelled')
- {conditions}
- GROUP BY
- parent.name, child.item_code
- """.format(
- conditions=conditions
- ),
- as_dict=1,
- ) # nosec
+ parent.owner,
+ )
+ .where(
+ (parent.docstatus == 1)
+ & (parent.name == child.parent)
+ & (parent.status.notin(("Closed", "Completed", "Cancelled")))
+ )
+ .groupby(parent.name, child.item_code)
+ )
+ query = apply_filters_on_query(filters, parent, child, query)
+
+ return query.run(as_dict=True)
diff --git a/erpnext/buying/report/purchase_order_analysis/purchase_order_analysis.py b/erpnext/buying/report/purchase_order_analysis/purchase_order_analysis.py
index a5c4649..e10c0e2 100644
--- a/erpnext/buying/report/purchase_order_analysis/purchase_order_analysis.py
+++ b/erpnext/buying/report/purchase_order_analysis/purchase_order_analysis.py
@@ -6,6 +6,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull
from frappe.utils import date_diff, flt, getdate
@@ -16,9 +17,7 @@
validate_filters(filters)
columns = get_columns(filters)
- conditions = get_conditions(filters)
-
- data = get_data(conditions, filters)
+ data = get_data(filters)
if not data:
return [], [], None, []
@@ -37,60 +36,61 @@
frappe.throw(_("To Date cannot be before From Date."))
-def get_conditions(filters):
- conditions = ""
- if filters.get("from_date") and filters.get("to_date"):
- conditions += " and po.transaction_date between %(from_date)s and %(to_date)s"
+def get_data(filters):
+ po = frappe.qb.DocType("Purchase Order")
+ po_item = frappe.qb.DocType("Purchase Order Item")
+ pi_item = frappe.qb.DocType("Purchase Invoice Item")
- for field in ["company", "name"]:
+ query = (
+ frappe.qb.from_(po)
+ .from_(po_item)
+ .left_join(pi_item)
+ .on(pi_item.po_detail == po_item.name)
+ .select(
+ po.transaction_date.as_("date"),
+ po_item.schedule_date.as_("required_date"),
+ po_item.project,
+ po.name.as_("purchase_order"),
+ po.status,
+ po.supplier,
+ po_item.item_code,
+ po_item.qty,
+ po_item.received_qty,
+ (po_item.qty - po_item.received_qty).as_("pending_qty"),
+ IfNull(pi_item.qty, 0).as_("billed_qty"),
+ po_item.base_amount.as_("amount"),
+ (po_item.received_qty * po_item.base_rate).as_("received_qty_amount"),
+ (po_item.billed_amt * IfNull(po.conversion_rate, 1)).as_("billed_amount"),
+ (po_item.base_amount - (po_item.billed_amt * IfNull(po.conversion_rate, 1))).as_(
+ "pending_amount"
+ ),
+ po.set_warehouse.as_("warehouse"),
+ po.company,
+ po_item.name,
+ )
+ .where(
+ (po_item.parent == po.name) & (po.status.notin(("Stopped", "Closed"))) & (po.docstatus == 1)
+ )
+ .groupby(po_item.name)
+ .orderby(po.transaction_date)
+ )
+
+ for field in ("company", "name"):
if filters.get(field):
- conditions += f" and po.{field} = %({field})s"
+ query = query.where(po[field] == filters.get(field))
+
+ if filters.get("from_date") and filters.get("to_date"):
+ query = query.where(
+ po.transaction_date.between(filters.get("from_date"), filters.get("to_date"))
+ )
if filters.get("status"):
- conditions += " and po.status in %(status)s"
+ query = query.where(po.status.isin(filters.get("status")))
if filters.get("project"):
- conditions += " and poi.project = %(project)s"
+ query = query.where(po_item.project == filters.get("project"))
- return conditions
-
-
-def get_data(conditions, filters):
- data = frappe.db.sql(
- """
- SELECT
- po.transaction_date as date,
- poi.schedule_date as required_date,
- poi.project,
- po.name as purchase_order,
- po.status, po.supplier, poi.item_code,
- poi.qty, poi.received_qty,
- (poi.qty - poi.received_qty) AS pending_qty,
- IFNULL(pii.qty, 0) as billed_qty,
- poi.base_amount as amount,
- (poi.received_qty * poi.base_rate) as received_qty_amount,
- (poi.billed_amt * IFNULL(po.conversion_rate, 1)) as billed_amount,
- (poi.base_amount - (poi.billed_amt * IFNULL(po.conversion_rate, 1))) as pending_amount,
- po.set_warehouse as warehouse,
- po.company, poi.name
- FROM
- `tabPurchase Order` po,
- `tabPurchase Order Item` poi
- LEFT JOIN `tabPurchase Invoice Item` pii
- ON pii.po_detail = poi.name
- WHERE
- poi.parent = po.name
- and po.status not in ('Stopped', 'Closed')
- and po.docstatus = 1
- {0}
- GROUP BY poi.name
- ORDER BY po.transaction_date ASC
- """.format(
- conditions
- ),
- filters,
- as_dict=1,
- )
+ data = query.run(as_dict=True)
return data
diff --git a/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py b/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
index 3013b6d..a728290 100644
--- a/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
+++ b/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
@@ -16,8 +16,7 @@
return [], []
columns = get_columns(filters)
- conditions = get_conditions(filters)
- supplier_quotation_data = get_data(filters, conditions)
+ supplier_quotation_data = get_data(filters)
data, chart_data = prepare_data(supplier_quotation_data, filters)
message = get_message()
@@ -25,50 +24,51 @@
return columns, data, message, chart_data
-def get_conditions(filters):
- conditions = ""
+def get_data(filters):
+ sq = frappe.qb.DocType("Supplier Quotation")
+ sq_item = frappe.qb.DocType("Supplier Quotation Item")
+
+ query = (
+ frappe.qb.from_(sq_item)
+ .from_(sq)
+ .select(
+ sq_item.parent,
+ sq_item.item_code,
+ sq_item.qty,
+ sq_item.stock_qty,
+ sq_item.amount,
+ sq_item.uom,
+ sq_item.stock_uom,
+ sq_item.request_for_quotation,
+ sq_item.lead_time_days,
+ sq.supplier.as_("supplier_name"),
+ sq.valid_till,
+ )
+ .where(
+ (sq_item.parent == sq.name)
+ & (sq_item.docstatus < 2)
+ & (sq.company == filters.get("company"))
+ & (sq.transaction_date.between(filters.get("from_date"), filters.get("to_date")))
+ )
+ .orderby(sq.transaction_date, sq_item.item_code)
+ )
+
if filters.get("item_code"):
- conditions += " AND sqi.item_code = %(item_code)s"
+ query = query.where(sq_item.item_code == filters.get("item_code"))
if filters.get("supplier_quotation"):
- conditions += " AND sqi.parent in %(supplier_quotation)s"
+ query = query.where(sq_item.parent.isin(filters.get("supplier_quotation")))
if filters.get("request_for_quotation"):
- conditions += " AND sqi.request_for_quotation = %(request_for_quotation)s"
+ query = query.where(sq_item.request_for_quotation == filters.get("request_for_quotation"))
if filters.get("supplier"):
- conditions += " AND sq.supplier in %(supplier)s"
+ query = query.where(sq.supplier.isin(filters.get("supplier")))
if not filters.get("include_expired"):
- conditions += " AND sq.status != 'Expired'"
+ query = query.where(sq.status != "Expired")
- return conditions
-
-
-def get_data(filters, conditions):
- supplier_quotation_data = frappe.db.sql(
- """
- SELECT
- sqi.parent, sqi.item_code,
- sqi.qty, sqi.stock_qty, sqi.amount,
- sqi.uom, sqi.stock_uom,
- sqi.request_for_quotation,
- sqi.lead_time_days, sq.supplier as supplier_name, sq.valid_till
- FROM
- `tabSupplier Quotation Item` sqi,
- `tabSupplier Quotation` sq
- WHERE
- sqi.parent = sq.name
- AND sqi.docstatus < 2
- AND sq.company = %(company)s
- AND sq.transaction_date between %(from_date)s and %(to_date)s
- {0}
- order by sq.transaction_date, sqi.item_code""".format(
- conditions
- ),
- filters,
- as_dict=1,
- )
+ supplier_quotation_data = query.run(as_dict=True)
return supplier_quotation_data
diff --git a/erpnext/crm/doctype/lead/lead.json b/erpnext/crm/doctype/lead/lead.json
index 99c00ad..8f8a086 100644
--- a/erpnext/crm/doctype/lead/lead.json
+++ b/erpnext/crm/doctype/lead/lead.json
@@ -375,7 +375,7 @@
"depends_on": "eval:!doc.__islocal",
"fieldname": "notes_tab",
"fieldtype": "Tab Break",
- "label": "Notes"
+ "label": "Comments"
},
{
"collapsible": 1,
@@ -506,7 +506,7 @@
{
"fieldname": "dashboard_tab",
"fieldtype": "Tab Break",
- "label": "Dashboard",
+ "label": "Connections",
"show_dashboard": 1
}
],
@@ -514,7 +514,7 @@
"idx": 5,
"image_field": "image",
"links": [],
- "modified": "2022-08-09 18:26:17.101521",
+ "modified": "2022-10-13 12:42:04.277879",
"modified_by": "Administrator",
"module": "CRM",
"name": "Lead",
diff --git a/erpnext/crm/doctype/opportunity/opportunity.json b/erpnext/crm/doctype/opportunity/opportunity.json
index fed0c7c..07641d2 100644
--- a/erpnext/crm/doctype/opportunity/opportunity.json
+++ b/erpnext/crm/doctype/opportunity/opportunity.json
@@ -544,14 +544,14 @@
"depends_on": "eval:!doc.__islocal",
"fieldname": "dashboard_tab",
"fieldtype": "Tab Break",
- "label": "Dashboard",
+ "label": "Connections",
"show_dashboard": 1
},
{
"depends_on": "eval:!doc.__islocal",
"fieldname": "notes_tab",
"fieldtype": "Tab Break",
- "label": "Notes"
+ "label": "Comments"
},
{
"fieldname": "notes_html",
@@ -622,7 +622,7 @@
"icon": "fa fa-info-sign",
"idx": 195,
"links": [],
- "modified": "2022-08-09 18:26:37.235964",
+ "modified": "2022-10-13 12:42:21.545636",
"modified_by": "Administrator",
"module": "CRM",
"name": "Opportunity",
diff --git a/erpnext/crm/doctype/prospect/prospect.json b/erpnext/crm/doctype/prospect/prospect.json
index 820a6c7..d32311b 100644
--- a/erpnext/crm/doctype/prospect/prospect.json
+++ b/erpnext/crm/doctype/prospect/prospect.json
@@ -128,7 +128,7 @@
"depends_on": "eval:!doc.__islocal",
"fieldname": "notes_section",
"fieldtype": "Tab Break",
- "label": "Notes"
+ "label": "Comments"
},
{
"depends_on": "eval: !doc.__islocal",
@@ -218,7 +218,7 @@
],
"index_web_pages_for_search": 1,
"links": [],
- "modified": "2022-08-09 18:26:56.950185",
+ "modified": "2022-10-13 12:29:33.674561",
"modified_by": "Administrator",
"module": "CRM",
"name": "Prospect",
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index ff84991..580838e 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -385,6 +385,7 @@
if self.docstatus == 2:
return
+ self.flags.cost_updated = False
existing_bom_cost = self.total_cost
if self.docstatus == 1:
@@ -407,7 +408,11 @@
frappe.get_doc("BOM", bom).update_cost(from_child_bom=True)
if not from_child_bom:
- frappe.msgprint(_("Cost Updated"), alert=True)
+ msg = "Cost Updated"
+ if not self.flags.cost_updated:
+ msg = "No changes in cost found"
+
+ frappe.msgprint(_(msg), alert=True)
def update_parent_cost(self):
if self.total_cost:
@@ -593,11 +598,16 @@
# not via doc event, table is not regenerated and needs updation
self.calculate_exploded_cost()
+ old_cost = self.total_cost
+
self.total_cost = self.operating_cost + self.raw_material_cost - self.scrap_material_cost
self.base_total_cost = (
self.base_operating_cost + self.base_raw_material_cost - self.base_scrap_material_cost
)
+ if self.total_cost != old_cost:
+ self.flags.cost_updated = True
+
def calculate_op_cost(self, update_hour_rate=False):
"""Update workstation rate and calculates totals"""
self.operating_cost = 0
diff --git a/erpnext/manufacturing/doctype/bom/test_bom.py b/erpnext/manufacturing/doctype/bom/test_bom.py
index 27f3cc9..e34ac12 100644
--- a/erpnext/manufacturing/doctype/bom/test_bom.py
+++ b/erpnext/manufacturing/doctype/bom/test_bom.py
@@ -9,7 +9,10 @@
from frappe.tests.utils import FrappeTestCase
from frappe.utils import cstr, flt
-from erpnext.controllers.tests.test_subcontracting_controller import set_backflush_based_on
+from erpnext.controllers.tests.test_subcontracting_controller import (
+ make_stock_in_entry,
+ set_backflush_based_on,
+)
from erpnext.manufacturing.doctype.bom.bom import BOMRecursionError, item_query, make_variant_bom
from erpnext.manufacturing.doctype.bom_update_log.test_bom_update_log import (
update_cost_in_all_boms_in_test,
@@ -639,6 +642,28 @@
bom.submit()
self.assertEqual(bom.exploded_items[0].rate, bom.items[0].base_rate)
+ def test_bom_cost_update_flag(self):
+ rm_item = make_item(
+ properties={"is_stock_item": 1, "valuation_rate": 99, "last_purchase_rate": 89}
+ ).name
+ fg_item = make_item(properties={"is_stock_item": 1}).name
+
+ from erpnext.manufacturing.doctype.production_plan.test_production_plan import make_bom
+
+ bom = make_bom(item=fg_item, raw_materials=[rm_item])
+
+ create_stock_reconciliation(
+ item_code=rm_item, warehouse="_Test Warehouse - _TC", qty=100, rate=600
+ )
+
+ bom.load_from_db()
+ bom.update_cost()
+ self.assertTrue(bom.flags.cost_updated)
+
+ bom.load_from_db()
+ bom.update_cost()
+ self.assertFalse(bom.flags.cost_updated)
+
def get_default_bom(item_code="_Test FG Item 2"):
return frappe.db.get_value("BOM", {"item": item_code, "is_active": 1, "is_default": 1})
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.py b/erpnext/manufacturing/doctype/job_card/job_card.py
index ed45106..fb94e8a 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/job_card.py
@@ -133,7 +133,7 @@
(%(from_time)s <= jctl.from_time and %(to_time)s >= jctl.to_time) {0}
)
and jctl.name != %(name)s and jc.name != %(parent)s and jc.docstatus < 2 {1}
- order by jctl.to_time desc limit 1""".format(
+ order by jctl.to_time desc""".format(
extra_cond, validate_overlap_for
),
{
diff --git a/erpnext/manufacturing/doctype/job_card/test_job_card.py b/erpnext/manufacturing/doctype/job_card/test_job_card.py
index ac71141..4d2dab7 100644
--- a/erpnext/manufacturing/doctype/job_card/test_job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/test_job_card.py
@@ -136,6 +136,45 @@
)
self.assertRaises(OverlapError, jc2.save)
+ def test_job_card_overlap_with_capacity(self):
+ wo2 = make_wo_order_test_record(item="_Test FG Item 2", qty=2)
+
+ workstation = make_workstation(workstation_name=random_string(5)).name
+ frappe.db.set_value("Workstation", workstation, "production_capacity", 1)
+
+ jc1 = frappe.get_last_doc("Job Card", {"work_order": self.work_order.name})
+ jc2 = frappe.get_last_doc("Job Card", {"work_order": wo2.name})
+
+ jc1.workstation = workstation
+ jc1.append(
+ "time_logs",
+ {"from_time": "2021-01-01 00:00:00", "to_time": "2021-01-01 08:00:00", "completed_qty": 1},
+ )
+ jc1.save()
+
+ jc2.workstation = workstation
+
+ # add a new entry in same time slice
+ jc2.append(
+ "time_logs",
+ {"from_time": "2021-01-01 00:01:00", "to_time": "2021-01-01 06:00:00", "completed_qty": 1},
+ )
+ self.assertRaises(OverlapError, jc2.save)
+
+ frappe.db.set_value("Workstation", workstation, "production_capacity", 2)
+ jc2.load_from_db()
+
+ jc2.workstation = workstation
+
+ # add a new entry in same time slice
+ jc2.append(
+ "time_logs",
+ {"from_time": "2021-01-01 00:01:00", "to_time": "2021-01-01 06:00:00", "completed_qty": 1},
+ )
+
+ jc2.save()
+ self.assertTrue(jc2.name)
+
def test_job_card_multiple_materials_transfer(self):
"Test transferring RMs separately against Job Card with multiple RMs."
self.transfer_material_against = "Job Card"
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 4bb4dcc..000ee07 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -27,6 +27,7 @@
from erpnext.manufacturing.doctype.bom.bom import validate_bom_no
from erpnext.manufacturing.doctype.work_order.work_order import get_item_details
from erpnext.setup.doctype.item_group.item_group import get_item_group_defaults
+from erpnext.stock.get_item_details import get_conversion_factor
from erpnext.utilities.transaction_base import validate_uom_is_integer
@@ -648,13 +649,23 @@
else:
material_request = material_request_map[key]
+ conversion_factor = 1.0
+ if (
+ material_request_type == "Purchase"
+ and item_doc.purchase_uom
+ and item_doc.purchase_uom != item_doc.stock_uom
+ ):
+ conversion_factor = (
+ get_conversion_factor(item_doc.name, item_doc.purchase_uom).get("conversion_factor") or 1.0
+ )
+
# add item
material_request.append(
"items",
{
"item_code": item.item_code,
"from_warehouse": item.from_warehouse,
- "qty": item.quantity,
+ "qty": item.quantity / conversion_factor,
"schedule_date": schedule_date,
"warehouse": item.warehouse,
"sales_order": item.sales_order,
diff --git a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
index 60e6398..c4ab0f8 100644
--- a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
@@ -806,6 +806,35 @@
self.assertEqual(pln.status, "Completed")
self.assertEqual(pln.po_items[0].produced_qty, 5)
+ def test_material_request_item_for_purchase_uom(self):
+ from erpnext.stock.doctype.item.test_item import make_item
+
+ fg_item = make_item(properties={"is_stock_item": 1, "stock_uom": "_Test UOM 1"}).name
+ bom_item = make_item(
+ properties={"is_stock_item": 1, "stock_uom": "_Test UOM 1", "purchase_uom": "Nos"}
+ ).name
+
+ if not frappe.db.exists("UOM Conversion Detail", {"parent": bom_item, "uom": "Nos"}):
+ doc = frappe.get_doc("Item", bom_item)
+ doc.append("uoms", {"uom": "Nos", "conversion_factor": 10})
+ doc.save()
+
+ make_bom(item=fg_item, raw_materials=[bom_item], source_warehouse="_Test Warehouse - _TC")
+
+ pln = create_production_plan(
+ item_code=fg_item, planned_qty=10, ignore_existing_ordered_qty=1, stock_uom="_Test UOM 1"
+ )
+
+ pln.make_material_request()
+ for row in frappe.get_all(
+ "Material Request Item",
+ filters={"production_plan": pln.name},
+ fields=["item_code", "uom", "qty"],
+ ):
+ self.assertEqual(row.item_code, bom_item)
+ self.assertEqual(row.uom, "Nos")
+ self.assertEqual(row.qty, 1)
+
def create_production_plan(**args):
"""
diff --git a/erpnext/public/js/controllers/transaction.js b/erpnext/public/js/controllers/transaction.js
index c17610b..7fecb18 100644
--- a/erpnext/public/js/controllers/transaction.js
+++ b/erpnext/public/js/controllers/transaction.js
@@ -426,6 +426,7 @@
if(!this.validate_company_and_party()) {
this.frm.fields_dict["items"].grid.grid_rows[item.idx - 1].remove();
} else {
+ item.pricing_rules = ''
return this.frm.call({
method: "erpnext.stock.get_item_details.get_item_details",
child: item,
@@ -1045,6 +1046,7 @@
uom(doc, cdt, cdn) {
var me = this;
var item = frappe.get_doc(cdt, cdn);
+ item.pricing_rules = ''
if(item.item_code && item.uom) {
return this.frm.call({
method: "erpnext.stock.get_item_details.get_conversion_factor",
@@ -1121,6 +1123,7 @@
qty(doc, cdt, cdn) {
let item = frappe.get_doc(cdt, cdn);
+ item.pricing_rules = ''
this.conversion_factor(doc, cdt, cdn, true);
this.calculate_stock_uom_rate(doc, cdt, cdn);
this.apply_pricing_rule(item, true);
diff --git a/erpnext/public/js/financial_statements.js b/erpnext/public/js/financial_statements.js
index 1a309ba..b0082bd 100644
--- a/erpnext/public/js/financial_statements.js
+++ b/erpnext/public/js/financial_statements.js
@@ -28,7 +28,7 @@
},
"open_general_ledger": function(data) {
if (!data.account) return;
- var project = $.grep(frappe.query_report.filters, function(e){ return e.df.fieldname == 'project'; })
+ let project = $.grep(frappe.query_report.filters, function(e){ return e.df.fieldname == 'project'; });
frappe.route_options = {
"account": data.account,
@@ -37,7 +37,16 @@
"to_date": data.to_date || data.year_end_date,
"project": (project && project.length > 0) ? project[0].$input.val() : ""
};
- frappe.set_route("query-report", "General Ledger");
+
+ let report = "General Ledger";
+
+ if (["Payable", "Receivable"].includes(data.account_type)) {
+ report = data.account_type == "Payable" ? "Accounts Payable" : "Accounts Receivable";
+ frappe.route_options["party_account"] = data.account;
+ frappe.route_options["report_date"] = data.year_end_date;
+ }
+
+ frappe.set_route("query-report", report);
},
"tree": true,
"name_field": "account",
diff --git a/erpnext/selling/doctype/quotation/quotation.js b/erpnext/selling/doctype/quotation/quotation.js
index 70ae085..6b42e4d 100644
--- a/erpnext/selling/doctype/quotation/quotation.js
+++ b/erpnext/selling/doctype/quotation/quotation.js
@@ -84,11 +84,12 @@
}
}
- if(doc.docstatus == 1 && !(['Lost', 'Ordered']).includes(doc.status)) {
- if(!doc.valid_till || frappe.datetime.get_diff(doc.valid_till, frappe.datetime.get_today()) >= 0) {
- cur_frm.add_custom_button(__('Sales Order'),
- cur_frm.cscript['Make Sales Order'], __('Create'));
- }
+ if (doc.docstatus == 1 && !["Lost", "Ordered"].includes(doc.status)) {
+ this.frm.add_custom_button(
+ __("Sales Order"),
+ this.frm.cscript["Make Sales Order"],
+ __("Create")
+ );
if(doc.status!=="Ordered") {
this.frm.add_custom_button(__('Set as Lost'), () => {
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index 25806d6..f0e9e4b 100755
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -18,6 +18,7 @@
update_linked_doc,
validate_inter_company_party,
)
+from erpnext.accounts.party import get_party_account
from erpnext.controllers.selling_controller import SellingController
from erpnext.manufacturing.doctype.production_plan.production_plan import (
get_items_for_material_requests,
@@ -727,6 +728,8 @@
if source.loyalty_points and source.order_type == "Shopping Cart":
target.redeem_loyalty_points = 1
+ target.debit_to = get_party_account("Customer", source.customer, source.company)
+
def update_item(source, target, source_parent):
target.amount = flt(source.amount) - flt(source.billed_amt)
target.base_amount = target.amount * flt(source_parent.conversion_rate)
diff --git a/erpnext/stock/doctype/delivery_note/delivery_note.py b/erpnext/stock/doctype/delivery_note/delivery_note.py
index 36d5a6c..9dd28dc 100644
--- a/erpnext/stock/doctype/delivery_note/delivery_note.py
+++ b/erpnext/stock/doctype/delivery_note/delivery_note.py
@@ -842,6 +842,9 @@
update_address(
target_doc, "shipping_address", "shipping_address_display", source_doc.customer_address
)
+ update_address(
+ target_doc, "billing_address", "billing_address_display", source_doc.customer_address
+ )
update_taxes(
target_doc,
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index ab784ca..6e06d23 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -9,6 +9,7 @@
from frappe.contacts.address_and_contact import load_address_and_contact
from frappe.utils import cint, flt
from frappe.utils.nestedset import NestedSet
+from pypika.terms import ExistsCriterion
from erpnext.stock import get_warehouse_account
@@ -266,3 +267,23 @@
frappe.throw(_("Warehouse not found against the account {0}").format(account))
return warehouses
+
+
+# Will be use for frappe.qb
+def apply_warehouse_filter(query, sle, filters):
+ if warehouse := filters.get("warehouse"):
+ warehouse_table = frappe.qb.DocType("Warehouse")
+
+ lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
+ chilren_subquery = (
+ frappe.qb.from_(warehouse_table)
+ .select(warehouse_table.name)
+ .where(
+ (warehouse_table.lft >= lft)
+ & (warehouse_table.rgt <= rgt)
+ & (warehouse_table.name == sle.warehouse)
+ )
+ )
+ query = query.where(ExistsCriterion(chilren_subquery))
+
+ return query
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..0d57938 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
@@ -5,6 +5,9 @@
import frappe
from frappe import _
from frappe.utils import cint, flt, getdate
+from pypika import functions as fn
+
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
def execute(filters=None):
@@ -64,37 +67,40 @@
return columns
-def get_conditions(filters):
- conditions = ""
- 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:
- 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,
+ if not filters.get("from_date"):
+ frappe.throw(_("'From Date' is required"))
+ if not filters.get("to_date"):
+ frappe.throw(_("'To Date' is required"))
+
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ sle.warehouse,
+ sle.batch_no,
+ sle.posting_date,
+ fn.Sum(sle.actual_qty).as_("actual_qty"),
+ )
+ .where(
+ (sle.docstatus < 2)
+ & (sle.is_cancelled == 0)
+ & (fn.IfNull(sle.batch_no, "") != "")
+ & (sle.posting_date <= filters["to_date"])
+ )
+ .groupby(sle.voucher_no, sle.batch_no, sle.item_code, sle.warehouse)
+ .orderby(sle.item_code, sle.warehouse)
)
+ query = apply_warehouse_filter(query, sle, filters)
+ for field in ["item_code", "batch_no", "company"]:
+ if filters.get(field):
+ query = query.where(sle[field] == filters.get(field))
+
+ return query.run(as_dict=True)
+
def get_item_warehouse_batch_map(filters, float_precision):
sle = get_stock_ledger_entries(filters)
@@ -127,7 +133,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_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 679d234..0fc642e 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -10,10 +10,10 @@
from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, date_diff, flt, getdate
from frappe.utils.nestedset import get_descendants_of
-from pypika.terms import ExistsCriterion
import erpnext
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
from erpnext.stock.utils import add_additional_uom_columns, is_reposting_item_valuation_in_progress
@@ -270,18 +270,8 @@
if company := filters.get("company"):
query = query.where(sle.company == company)
- if warehouse := filters.get("warehouse"):
- lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
- chilren_subquery = (
- frappe.qb.from_(warehouse_table)
- .select(warehouse_table.name)
- .where(
- (warehouse_table.lft >= lft)
- & (warehouse_table.rgt <= rgt)
- & (warehouse_table.name == sle.warehouse)
- )
- )
- query = query.where(ExistsCriterion(chilren_subquery))
+ if filters.get("warehouse"):
+ query = apply_warehouse_filter(query, sle, filters)
elif warehouse_type := filters.get("warehouse_type"):
query = (
query.join(warehouse_table)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index b836e9c..af7f20f 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -6,11 +6,11 @@
from frappe import _
from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, flt
-from pypika.terms import ExistsCriterion
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import get_stock_balance_for
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.utils import (
is_reposting_item_valuation_in_progress,
update_included_uom_in_report,
@@ -295,20 +295,7 @@
if filters.get(field):
query = query.where(sle[field] == filters.get(field))
- if warehouse := filters.get("warehouse"):
- lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
-
- warehouse_table = frappe.qb.DocType("Warehouse")
- chilren_subquery = (
- frappe.qb.from_(warehouse_table)
- .select(warehouse_table.name)
- .where(
- (warehouse_table.lft >= lft)
- & (warehouse_table.rgt <= rgt)
- & (warehouse_table.name == sle.warehouse)
- )
- )
- query = query.where(ExistsCriterion(chilren_subquery))
+ query = apply_warehouse_filter(query, sle, filters)
return query.run(as_dict=True)
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):
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 9fb3be5..b8c5187 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -13,6 +13,8 @@
import erpnext
from erpnext.stock.valuation import FIFOValuation, LIFOValuation
+BarcodeScanResult = Dict[str, Optional[str]]
+
class InvalidWarehouseCompany(frappe.ValidationError):
pass
@@ -552,7 +554,16 @@
@frappe.whitelist()
-def scan_barcode(search_value: str) -> Dict[str, Optional[str]]:
+def scan_barcode(search_value: str) -> BarcodeScanResult:
+ def set_cache(data: BarcodeScanResult):
+ frappe.cache().set_value(f"erpnext:barcode_scan:{search_value}", data, expires_in_sec=120)
+
+ def get_cache() -> Optional[BarcodeScanResult]:
+ if data := frappe.cache().get_value(f"erpnext:barcode_scan:{search_value}"):
+ return data
+
+ if scan_data := get_cache():
+ return scan_data
# search barcode no
barcode_data = frappe.db.get_value(
@@ -562,7 +573,9 @@
as_dict=True,
)
if barcode_data:
- return _update_item_info(barcode_data)
+ _update_item_info(barcode_data)
+ set_cache(barcode_data)
+ return barcode_data
# search serial no
serial_no_data = frappe.db.get_value(
@@ -572,7 +585,9 @@
as_dict=True,
)
if serial_no_data:
- return _update_item_info(serial_no_data)
+ _update_item_info(serial_no_data)
+ set_cache(serial_no_data)
+ return serial_no_data
# search batch no
batch_no_data = frappe.db.get_value(
@@ -582,7 +597,9 @@
as_dict=True,
)
if batch_no_data:
- return _update_item_info(batch_no_data)
+ _update_item_info(batch_no_data)
+ set_cache(batch_no_data)
+ return batch_no_data
return {}
diff --git a/erpnext/support/doctype/service_level_agreement/test_service_level_agreement.py b/erpnext/support/doctype/service_level_agreement/test_service_level_agreement.py
index 4e00138..472f6bc 100644
--- a/erpnext/support/doctype/service_level_agreement/test_service_level_agreement.py
+++ b/erpnext/support/doctype/service_level_agreement/test_service_level_agreement.py
@@ -15,8 +15,30 @@
class TestServiceLevelAgreement(unittest.TestCase):
def setUp(self):
+ self.create_company()
frappe.db.set_value("Support Settings", None, "track_service_level_agreement", 1)
- frappe.db.sql("delete from `tabLead`")
+ lead = frappe.qb.DocType("Lead")
+ frappe.qb.from_(lead).delete().where(lead.company == self.company).run()
+
+ def create_company(self):
+ name = "_Test Support SLA"
+ company = None
+ if frappe.db.exists("Company", name):
+ company = frappe.get_doc("Company", name)
+ else:
+ company = frappe.get_doc(
+ {
+ "doctype": "Company",
+ "company_name": name,
+ "country": "India",
+ "default_currency": "INR",
+ "create_chart_of_accounts_based_on": "Standard Template",
+ "chart_of_accounts": "Standard",
+ }
+ )
+ company = company.save()
+
+ self.company = company.name
def test_service_level_agreement(self):
# Default Service Level Agreement
@@ -205,7 +227,7 @@
# make lead with default SLA
creation = datetime.datetime(2019, 3, 4, 12, 0)
- lead = make_lead(creation=creation, index=1)
+ lead = make_lead(creation=creation, index=1, company=self.company)
self.assertEqual(lead.service_level_agreement, lead_sla.name)
self.assertEqual(lead.response_by, datetime.datetime(2019, 3, 4, 16, 0))
@@ -233,7 +255,7 @@
)
creation = datetime.datetime(2020, 3, 4, 4, 0)
- lead = make_lead(creation, index=2)
+ lead = make_lead(creation, index=2, company=self.company)
frappe.flags.current_time = datetime.datetime(2020, 3, 4, 4, 15)
lead.reload()
@@ -267,7 +289,7 @@
)
creation = datetime.datetime(2019, 3, 4, 12, 0)
- lead = make_lead(creation=creation, index=1)
+ lead = make_lead(creation=creation, index=1, company=self.company)
self.assertEqual(lead.response_by, datetime.datetime(2019, 3, 4, 16, 0))
# failed with response time only
@@ -294,7 +316,7 @@
# fulfilled with response time only
creation = datetime.datetime(2019, 3, 4, 12, 0)
- lead = make_lead(creation=creation, index=2)
+ lead = make_lead(creation=creation, index=2, company=self.company)
self.assertEqual(lead.service_level_agreement, lead_sla.name)
self.assertEqual(lead.response_by, datetime.datetime(2019, 3, 4, 16, 0))
@@ -321,7 +343,7 @@
apply_sla_for_resolution=0,
)
creation = datetime.datetime(2019, 3, 4, 12, 0)
- lead = make_lead(creation=creation, index=4)
+ lead = make_lead(creation=creation, index=4, company=self.company)
applied_sla = frappe.db.get_value("Lead", lead.name, "service_level_agreement")
self.assertFalse(applied_sla)
@@ -611,7 +633,7 @@
return frappe.get_doc("DocType", "Test SLA on Custom Dt")
-def make_lead(creation=None, index=0):
+def make_lead(creation=None, index=0, company=None):
return frappe.get_doc(
{
"doctype": "Lead",
@@ -621,5 +643,6 @@
"creation": creation,
"service_level_agreement_creation": creation,
"priority": "Medium",
+ "company": company,
}
).insert(ignore_permissions=True)