Merge pull request #32744 from ruthra-kumar/so_terms_report_enhancement
refactor: additional filters on Payment Terms status report
diff --git a/erpnext/accounts/doctype/bank_clearance/bank_clearance.js b/erpnext/accounts/doctype/bank_clearance/bank_clearance.js
index 63cc465..7e57c2f 100644
--- a/erpnext/accounts/doctype/bank_clearance/bank_clearance.js
+++ b/erpnext/accounts/doctype/bank_clearance/bank_clearance.js
@@ -4,6 +4,23 @@
frappe.ui.form.on("Bank Clearance", {
setup: function(frm) {
frm.add_fetch("account", "account_currency", "account_currency");
+
+ frm.set_query("account", function() {
+ return {
+ "filters": {
+ "account_type": ["in",["Bank","Cash"]],
+ "is_group": 0,
+ }
+ };
+ });
+
+ frm.set_query("bank_account", function () {
+ return {
+ filters: {
+ 'is_company_account': 1
+ },
+ };
+ });
},
onload: function(frm) {
@@ -12,14 +29,7 @@
locals[":Company"][frappe.defaults.get_user_default("Company")]["default_bank_account"]: "";
frm.set_value("account", default_bank_account);
- frm.set_query("account", function() {
- return {
- "filters": {
- "account_type": ["in",["Bank","Cash"]],
- "is_group": 0
- }
- };
- });
+
frm.set_value("from_date", frappe.datetime.month_start());
frm.set_value("to_date", frappe.datetime.month_end());
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.js b/erpnext/accounts/doctype/journal_entry/journal_entry.js
index 763e2e6..a5ff7f1 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.js
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.js
@@ -312,8 +312,7 @@
}
}
- get_outstanding(doctype, docname, company, child, due_date) {
- var me = this;
+ get_outstanding(doctype, docname, company, child) {
var args = {
"doctype": doctype,
"docname": docname,
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 52690e1..de012b2 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -1210,6 +1210,7 @@
args = json.loads(args)
company_currency = erpnext.get_company_currency(args.get("company"))
+ due_date = None
if args.get("doctype") == "Journal Entry":
condition = " and party=%(party)s" if args.get("party") else ""
@@ -1234,10 +1235,12 @@
invoice = frappe.db.get_value(
args["doctype"],
args["docname"],
- ["outstanding_amount", "conversion_rate", scrub(party_type)],
+ ["outstanding_amount", "conversion_rate", scrub(party_type), "due_date"],
as_dict=1,
)
+ due_date = invoice.get("due_date")
+
exchange_rate = (
invoice.conversion_rate if (args.get("account_currency") != company_currency) else 1
)
@@ -1260,6 +1263,7 @@
"exchange_rate": exchange_rate,
"party_type": party_type,
"party": invoice.get(scrub(party_type)),
+ "reference_due_date": due_date,
}
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 a0ea433..47ad19e 100644
--- a/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json
+++ b/erpnext/accounts/doctype/journal_entry_account/journal_entry_account.json
@@ -216,7 +216,7 @@
{
"depends_on": "eval:doc.reference_type&&!in_list(doc.reference_type, ['Expense Claim', 'Asset', 'Employee Loan', 'Employee Advance'])",
"fieldname": "reference_due_date",
- "fieldtype": "Select",
+ "fieldtype": "Date",
"label": "Reference Due Date",
"no_copy": 1
},
@@ -284,7 +284,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-10-13 17:07:17.999191",
+ "modified": "2022-10-26 20:03:10.906259",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Journal Entry Account",
diff --git a/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.js b/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.js
index 7eb5c42..1f41661 100644
--- a/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.js
+++ b/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.js
@@ -22,13 +22,13 @@
}
if (data.user != frappe.session.user) return;
if (data.count == data.total) {
- setTimeout((title) => {
+ setTimeout(() => {
frm.doc.import_in_progress = false;
frm.clear_table("invoices");
frm.refresh_fields();
frm.page.clear_indicator();
- frm.dashboard.hide_progress(title);
- frappe.msgprint(__("Opening {0} Invoice created", [frm.doc.invoice_type]));
+ frm.dashboard.hide_progress();
+ frappe.msgprint(__("Opening {0} Invoices created", [frm.doc.invoice_type]));
}, 1500, data.title);
return;
}
@@ -51,13 +51,6 @@
method: "make_invoices",
freeze: 1,
freeze_message: __("Creating {0} Invoice", [frm.doc.invoice_type]),
- callback: function(r) {
- if (r.message.length == 1) {
- frappe.msgprint(__("{0} Invoice created successfully.", [frm.doc.invoice_type]));
- } else if (r.message.length < 50) {
- frappe.msgprint(__("{0} Invoices created successfully.", [frm.doc.invoice_type]));
- }
- }
});
});
diff --git a/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.py b/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.py
index f7df1ff..57fe405 100644
--- a/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.py
+++ b/erpnext/accounts/doctype/opening_invoice_creation_tool/opening_invoice_creation_tool.py
@@ -255,8 +255,6 @@
def publish(index, total, doctype):
- if total < 50:
- return
frappe.publish_realtime(
"opening_invoice_creation_progress",
dict(
diff --git a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.js b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.js
index 7dd77fb..7dd5ef3 100644
--- a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.js
+++ b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.js
@@ -9,6 +9,7 @@
refresh: function(frm){
if(!frm.doc.__islocal) {
frm.add_custom_button(__('Send Emails'), function(){
+ if (frm.is_dirty()) frappe.throw(__("Please save before proceeding."))
frappe.call({
method: "erpnext.accounts.doctype.process_statement_of_accounts.process_statement_of_accounts.send_emails",
args: {
@@ -25,7 +26,8 @@
});
});
frm.add_custom_button(__('Download'), function(){
- var url = frappe.urllib.get_full_url(
+ if (frm.is_dirty()) frappe.throw(__("Please save before proceeding."))
+ let url = frappe.urllib.get_full_url(
'/api/method/erpnext.accounts.doctype.process_statement_of_accounts.process_statement_of_accounts.download_statements?'
+ 'document_name='+encodeURIComponent(frm.doc.name))
$.ajax({
diff --git a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.json b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.json
index a26267b..83e6370 100644
--- a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.json
+++ b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.json
@@ -27,6 +27,7 @@
"customers",
"preferences",
"orientation",
+ "include_break",
"include_ageing",
"ageing_based_on",
"section_break_14",
@@ -284,10 +285,16 @@
"fieldtype": "Link",
"label": "Terms and Conditions",
"options": "Terms and Conditions"
+ },
+ {
+ "default": "1",
+ "fieldname": "include_break",
+ "fieldtype": "Check",
+ "label": "Page Break After Each SoA"
}
],
"links": [],
- "modified": "2021-09-06 21:00:45.732505",
+ "modified": "2022-10-17 17:47:08.662475",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Process Statement Of Accounts",
@@ -321,5 +328,6 @@
],
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
index 01f716d..c6b0c57 100644
--- a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
+++ b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
@@ -6,6 +6,7 @@
import frappe
from frappe import _
+from frappe.desk.reportview import get_match_cond
from frappe.model.document import Document
from frappe.utils import add_days, add_months, format_date, getdate, today
from frappe.utils.jinja import validate_template
@@ -128,7 +129,8 @@
if not bool(statement_dict):
return False
elif consolidated:
- result = "".join(list(statement_dict.values()))
+ delimiter = '<div style="page-break-before: always;"></div>' if doc.include_break else ""
+ result = delimiter.join(list(statement_dict.values()))
return get_pdf(result, {"orientation": doc.orientation})
else:
for customer, statement_html in statement_dict.items():
@@ -240,8 +242,6 @@
if int(primary_mandatory):
if primary_email == "":
continue
- elif (billing_email == "") and (primary_email == ""):
- continue
customer_list.append(
{"name": customer.name, "primary_email": primary_email, "billing_email": billing_email}
@@ -273,8 +273,12 @@
link.link_doctype='Customer'
and link.link_name=%s
and contact.is_billing_contact=1
+ {mcond}
ORDER BY
- contact.creation desc""",
+ contact.creation desc
+ """.format(
+ mcond=get_match_cond("Contact")
+ ),
customer_name,
)
@@ -313,6 +317,8 @@
attachments = [{"fname": customer + ".pdf", "fcontent": report_pdf}]
recipients, cc = get_recipients_and_cc(customer, doc)
+ if not recipients:
+ continue
context = get_context(customer, doc)
subject = frappe.render_template(doc.subject, context)
message = frappe.render_template(doc.body, context)
diff --git a/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json b/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json
index 9de9036..a8f6f80 100644
--- a/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json
+++ b/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json
@@ -214,6 +214,7 @@
"reqd": 1
},
{
+ "default": "1",
"depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
@@ -820,6 +821,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "section_break_26",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -871,7 +873,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-10-12 03:37:29.032732",
+ "modified": "2022-10-26 16:05:37.304788",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Purchase Invoice Item",
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_item/sales_invoice_item.json b/erpnext/accounts/doctype/sales_invoice_item/sales_invoice_item.json
index a307a6c..7f1a1ec 100644
--- a/erpnext/accounts/doctype/sales_invoice_item/sales_invoice_item.json
+++ b/erpnext/accounts/doctype/sales_invoice_item/sales_invoice_item.json
@@ -247,6 +247,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "discount_and_margin",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -876,7 +877,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-10-10 20:57:38.340026",
+ "modified": "2022-10-26 11:38:36.119339",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Sales Invoice Item",
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/accounts_payable/accounts_payable.js b/erpnext/accounts/report/accounts_payable/accounts_payable.js
index 7cf14e6..e1a30a4 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.js
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.js
@@ -51,6 +51,8 @@
} else {
frappe.query_report.set_filter_value('tax_id', "");
}
+
+ frappe.query_report.refresh();
}
},
{
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 8557c03..f2ee1eb 100755
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -748,7 +748,7 @@
self.add_accounting_dimensions_filters()
- def get_cost_center_conditions(self, conditions):
+ def get_cost_center_conditions(self):
lft, rgt = frappe.db.get_value("Cost Center", self.filters.cost_center, ["lft", "rgt"])
cost_center_list = [
center.name
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.html b/erpnext/accounts/report/general_ledger/general_ledger.html
index 378fa37..c04f518 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.html
+++ b/erpnext/accounts/report/general_ledger/general_ledger.html
@@ -52,22 +52,22 @@
{% } %}
</td>
<td style="text-align: right">
- {%= format_currency(data[i].debit, filters.presentation_currency) %}</td>
+ {%= format_currency(data[i].debit, filters.presentation_currency || data[i].account_currency) %}</td>
<td style="text-align: right">
- {%= format_currency(data[i].credit, filters.presentation_currency) %}</td>
+ {%= format_currency(data[i].credit, filters.presentation_currency || data[i].account_currency) %}</td>
{% } else { %}
<td></td>
<td></td>
<td><b>{%= frappe.format(data[i].account, {fieldtype: "Link"}) || " " %}</b></td>
<td style="text-align: right">
- {%= data[i].account && format_currency(data[i].debit, filters.presentation_currency) %}
+ {%= data[i].account && format_currency(data[i].debit, filters.presentation_currency || data[i].account_currency) %}
</td>
<td style="text-align: right">
- {%= data[i].account && format_currency(data[i].credit, filters.presentation_currency) %}
+ {%= data[i].account && format_currency(data[i].credit, filters.presentation_currency || data[i].account_currency) %}
</td>
{% } %}
<td style="text-align: right">
- {%= format_currency(data[i].balance, filters.presentation_currency) %}
+ {%= format_currency(data[i].balance, filters.presentation_currency || data[i].account_currency) %}
</td>
</tr>
{% } %}
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/assets/doctype/asset_value_adjustment/asset_value_adjustment.py b/erpnext/assets/doctype/asset_value_adjustment/asset_value_adjustment.py
index 59ab6a9..84aa8fa 100644
--- a/erpnext/assets/doctype/asset_value_adjustment/asset_value_adjustment.py
+++ b/erpnext/assets/doctype/asset_value_adjustment/asset_value_adjustment.py
@@ -61,7 +61,9 @@
je.naming_series = depreciation_series
je.posting_date = self.date
je.company = self.company
- je.remark = "Depreciation Entry against {0} worth {1}".format(self.asset, self.difference_amount)
+ je.remark = _("Depreciation Entry against {0} worth {1}").format(
+ self.asset, self.difference_amount
+ )
je.finance_book = self.finance_book
credit_entry = {
diff --git a/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json b/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json
index 82e92e8..b8203bd 100644
--- a/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json
+++ b/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json
@@ -777,6 +777,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "discount_and_margin_section",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -894,7 +895,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-09-07 11:12:38.634976",
+ "modified": "2022-10-26 16:47:41.364387",
"modified_by": "Administrator",
"module": "Buying",
"name": "Purchase Order Item",
diff --git a/erpnext/buying/doctype/supplier/test_supplier.py b/erpnext/buying/doctype/supplier/test_supplier.py
index 5572268..e2dbf21 100644
--- a/erpnext/buying/doctype/supplier/test_supplier.py
+++ b/erpnext/buying/doctype/supplier/test_supplier.py
@@ -3,6 +3,7 @@
import frappe
+from frappe.custom.doctype.property_setter.property_setter import make_property_setter
from frappe.test_runner import make_test_records
from erpnext.accounts.party import get_due_date
@@ -152,6 +153,40 @@
# Rollback
address.delete()
+ def test_serach_fields_for_supplier(self):
+ from erpnext.controllers.queries import supplier_query
+
+ supplier_name = create_supplier(supplier_name="Test Supplier 1").name
+
+ make_property_setter(
+ "Supplier", None, "search_fields", "supplier_group", "Data", for_doctype="Doctype"
+ )
+
+ data = supplier_query(
+ "Supplier", supplier_name, "name", 0, 20, filters={"name": supplier_name}, as_dict=True
+ )
+
+ self.assertEqual(data[0].name, supplier_name)
+ self.assertEqual(data[0].supplier_group, "Services")
+ self.assertTrue("supplier_type" not in data[0])
+
+ make_property_setter(
+ "Supplier",
+ None,
+ "search_fields",
+ "supplier_group, supplier_type",
+ "Data",
+ for_doctype="Doctype",
+ )
+ data = supplier_query(
+ "Supplier", supplier_name, "name", 0, 20, filters={"name": supplier_name}, as_dict=True
+ )
+
+ self.assertEqual(data[0].name, supplier_name)
+ self.assertEqual(data[0].supplier_group, "Services")
+ self.assertEqual(data[0].supplier_type, "Company")
+ self.assertTrue("supplier_type" in data[0])
+
def create_supplier(**args):
args = frappe._dict(args)
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 8eae0a0..3bdc017 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -78,18 +78,16 @@
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
-def customer_query(doctype, txt, searchfield, start, page_len, filters):
+def customer_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
doctype = "Customer"
conditions = []
cust_master_name = frappe.defaults.get_user_default("cust_master_name")
- if cust_master_name == "Customer Name":
- fields = ["name", "customer_group", "territory"]
- else:
- fields = ["name", "customer_name", "customer_group", "territory"]
+ fields = ["name"]
+ if cust_master_name != "Customer Name":
+ fields = ["customer_name"]
fields = get_fields(doctype, fields)
-
searchfields = frappe.get_meta(doctype).get_search_fields()
searchfields = " or ".join(field + " like %(txt)s" for field in searchfields)
@@ -112,20 +110,20 @@
}
),
{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
+ as_dict=as_dict,
)
# searches for supplier
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
-def supplier_query(doctype, txt, searchfield, start, page_len, filters):
+def supplier_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
doctype = "Supplier"
supp_master_name = frappe.defaults.get_user_default("supp_master_name")
- if supp_master_name == "Supplier Name":
- fields = ["name", "supplier_group"]
- else:
- fields = ["name", "supplier_name", "supplier_group"]
+ fields = ["name"]
+ if supp_master_name != "Supplier Name":
+ fields = ["supplier_name"]
fields = get_fields(doctype, fields)
@@ -145,6 +143,7 @@
**{"field": ", ".join(fields), "key": searchfield, "mcond": get_match_cond(doctype)}
),
{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
+ as_dict=as_dict,
)
diff --git a/erpnext/loan_management/doctype/loan/loan.js b/erpnext/loan_management/doctype/loan/loan.js
index 38328e6..20e2b0b 100644
--- a/erpnext/loan_management/doctype/loan/loan.js
+++ b/erpnext/loan_management/doctype/loan/loan.js
@@ -61,6 +61,10 @@
},
refresh: function (frm) {
+ if (frm.doc.repayment_schedule_type == "Pro-rated calendar months") {
+ frm.set_df_property("repayment_start_date", "label", "Interest Calculation Start Date");
+ }
+
if (frm.doc.docstatus == 1) {
if (["Disbursed", "Partially Disbursed"].includes(frm.doc.status) && (!frm.doc.repay_from_salary)) {
frm.add_custom_button(__('Request Loan Closure'), function() {
@@ -103,6 +107,14 @@
frm.trigger("toggle_fields");
},
+ repayment_schedule_type: function(frm) {
+ if (frm.doc.repayment_schedule_type == "Pro-rated calendar months") {
+ frm.set_df_property("repayment_start_date", "label", "Interest Calculation Start Date");
+ } else {
+ frm.set_df_property("repayment_start_date", "label", "Repayment Start Date");
+ }
+ },
+
loan_type: function(frm) {
frm.toggle_reqd("repayment_method", frm.doc.is_term_loan);
frm.toggle_display("repayment_method", frm.doc.is_term_loan);
diff --git a/erpnext/loan_management/doctype/loan/loan.json b/erpnext/loan_management/doctype/loan/loan.json
index 47488f4..dc8b03e 100644
--- a/erpnext/loan_management/doctype/loan/loan.json
+++ b/erpnext/loan_management/doctype/loan/loan.json
@@ -18,6 +18,7 @@
"status",
"section_break_8",
"loan_type",
+ "repayment_schedule_type",
"loan_amount",
"rate_of_interest",
"is_secured_loan",
@@ -158,7 +159,8 @@
"depends_on": "is_term_loan",
"fieldname": "repayment_start_date",
"fieldtype": "Date",
- "label": "Repayment Start Date"
+ "label": "Repayment Start Date",
+ "mandatory_depends_on": "is_term_loan"
},
{
"fieldname": "column_break_11",
@@ -402,12 +404,20 @@
"fieldname": "is_npa",
"fieldtype": "Check",
"label": "Is NPA"
+ },
+ {
+ "depends_on": "is_term_loan",
+ "fetch_from": "loan_type.repayment_schedule_type",
+ "fieldname": "repayment_schedule_type",
+ "fieldtype": "Data",
+ "label": "Repayment Schedule Type",
+ "read_only": 1
}
],
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2022-07-12 11:50:31.957360",
+ "modified": "2022-09-30 10:36:47.902903",
"modified_by": "Administrator",
"module": "Loan Management",
"name": "Loan",
diff --git a/erpnext/loan_management/doctype/loan/loan.py b/erpnext/loan_management/doctype/loan/loan.py
index d84eef6..0c9c97f 100644
--- a/erpnext/loan_management/doctype/loan/loan.py
+++ b/erpnext/loan_management/doctype/loan/loan.py
@@ -7,7 +7,16 @@
import frappe
from frappe import _
-from frappe.utils import add_months, flt, get_last_day, getdate, now_datetime, nowdate
+from frappe.utils import (
+ add_days,
+ add_months,
+ date_diff,
+ flt,
+ get_last_day,
+ getdate,
+ now_datetime,
+ nowdate,
+)
import erpnext
from erpnext.accounts.doctype.journal_entry.journal_entry import get_payment_entry
@@ -107,30 +116,81 @@
if not self.repayment_start_date:
frappe.throw(_("Repayment Start Date is mandatory for term loans"))
+ schedule_type_details = frappe.db.get_value(
+ "Loan Type", self.loan_type, ["repayment_schedule_type", "repayment_date_on"], as_dict=1
+ )
+
self.repayment_schedule = []
payment_date = self.repayment_start_date
balance_amount = self.loan_amount
- while balance_amount > 0:
- interest_amount = flt(balance_amount * flt(self.rate_of_interest) / (12 * 100))
- principal_amount = self.monthly_repayment_amount - interest_amount
- balance_amount = flt(balance_amount + interest_amount - self.monthly_repayment_amount)
- if balance_amount < 0:
- principal_amount += balance_amount
- balance_amount = 0.0
- total_payment = principal_amount + interest_amount
- self.append(
- "repayment_schedule",
- {
- "payment_date": payment_date,
- "principal_amount": principal_amount,
- "interest_amount": interest_amount,
- "total_payment": total_payment,
- "balance_loan_amount": balance_amount,
- },
+ while balance_amount > 0:
+ interest_amount, principal_amount, balance_amount, total_payment = self.get_amounts(
+ payment_date,
+ balance_amount,
+ schedule_type_details.repayment_schedule_type,
+ schedule_type_details.repayment_date_on,
)
- next_payment_date = add_single_month(payment_date)
- payment_date = next_payment_date
+
+ if schedule_type_details.repayment_schedule_type == "Pro-rated calendar months":
+ next_payment_date = get_last_day(payment_date)
+ if schedule_type_details.repayment_date_on == "Start of the next month":
+ next_payment_date = add_days(next_payment_date, 1)
+
+ payment_date = next_payment_date
+
+ self.add_repayment_schedule_row(
+ payment_date, principal_amount, interest_amount, total_payment, balance_amount
+ )
+
+ if (
+ schedule_type_details.repayment_schedule_type == "Monthly as per repayment start date"
+ or schedule_type_details.repayment_date_on == "End of the current month"
+ ):
+ next_payment_date = add_single_month(payment_date)
+ payment_date = next_payment_date
+
+ def get_amounts(self, payment_date, balance_amount, schedule_type, repayment_date_on):
+ if schedule_type == "Monthly as per repayment start date":
+ days = 1
+ months = 12
+ else:
+ expected_payment_date = get_last_day(payment_date)
+ if repayment_date_on == "Start of the next month":
+ expected_payment_date = add_days(expected_payment_date, 1)
+
+ if expected_payment_date == payment_date:
+ # using 30 days for calculating interest for all full months
+ days = 30
+ months = 365
+ else:
+ days = date_diff(get_last_day(payment_date), payment_date)
+ months = 365
+
+ interest_amount = flt(balance_amount * flt(self.rate_of_interest) * days / (months * 100))
+ principal_amount = self.monthly_repayment_amount - interest_amount
+ balance_amount = flt(balance_amount + interest_amount - self.monthly_repayment_amount)
+ if balance_amount < 0:
+ principal_amount += balance_amount
+ balance_amount = 0.0
+
+ total_payment = principal_amount + interest_amount
+
+ return interest_amount, principal_amount, balance_amount, total_payment
+
+ def add_repayment_schedule_row(
+ self, payment_date, principal_amount, interest_amount, total_payment, balance_loan_amount
+ ):
+ self.append(
+ "repayment_schedule",
+ {
+ "payment_date": payment_date,
+ "principal_amount": principal_amount,
+ "interest_amount": interest_amount,
+ "total_payment": total_payment,
+ "balance_loan_amount": balance_loan_amount,
+ },
+ )
def set_repayment_period(self):
if self.repayment_method == "Repay Fixed Amount per Period":
diff --git a/erpnext/loan_management/doctype/loan/test_loan.py b/erpnext/loan_management/doctype/loan/test_loan.py
index da05c8e..388e65d 100644
--- a/erpnext/loan_management/doctype/loan/test_loan.py
+++ b/erpnext/loan_management/doctype/loan/test_loan.py
@@ -4,7 +4,16 @@
import unittest
import frappe
-from frappe.utils import add_days, add_months, add_to_date, date_diff, flt, get_datetime, nowdate
+from frappe.utils import (
+ add_days,
+ add_months,
+ add_to_date,
+ date_diff,
+ flt,
+ format_date,
+ get_datetime,
+ nowdate,
+)
from erpnext.loan_management.doctype.loan.loan import (
make_loan_write_off,
@@ -47,6 +56,51 @@
loan_account="Loan Account - _TC",
interest_income_account="Interest Income Account - _TC",
penalty_income_account="Penalty Income Account - _TC",
+ repayment_schedule_type="Monthly as per repayment start date",
+ )
+
+ create_loan_type(
+ "Term Loan Type 1",
+ 12000,
+ 7.5,
+ is_term_loan=1,
+ mode_of_payment="Cash",
+ disbursement_account="Disbursement Account - _TC",
+ payment_account="Payment Account - _TC",
+ loan_account="Loan Account - _TC",
+ interest_income_account="Interest Income Account - _TC",
+ penalty_income_account="Penalty Income Account - _TC",
+ repayment_schedule_type="Monthly as per repayment start date",
+ )
+
+ create_loan_type(
+ "Term Loan Type 2",
+ 12000,
+ 7.5,
+ is_term_loan=1,
+ mode_of_payment="Cash",
+ disbursement_account="Disbursement Account - _TC",
+ payment_account="Payment Account - _TC",
+ loan_account="Loan Account - _TC",
+ interest_income_account="Interest Income Account - _TC",
+ penalty_income_account="Penalty Income Account - _TC",
+ repayment_schedule_type="Pro-rated calendar months",
+ repayment_date_on="Start of the next month",
+ )
+
+ create_loan_type(
+ "Term Loan Type 3",
+ 12000,
+ 7.5,
+ is_term_loan=1,
+ mode_of_payment="Cash",
+ disbursement_account="Disbursement Account - _TC",
+ payment_account="Payment Account - _TC",
+ loan_account="Loan Account - _TC",
+ interest_income_account="Interest Income Account - _TC",
+ penalty_income_account="Penalty Income Account - _TC",
+ repayment_schedule_type="Pro-rated calendar months",
+ repayment_date_on="End of the current month",
)
create_loan_type(
@@ -62,6 +116,7 @@
"Loan Account - _TC",
"Interest Income Account - _TC",
"Penalty Income Account - _TC",
+ repayment_schedule_type="Monthly as per repayment start date",
)
create_loan_type(
@@ -902,6 +957,69 @@
amounts = calculate_amounts(loan.name, add_days(last_date, 5))
self.assertEqual(flt(amounts["pending_principal_amount"], 0), 0)
+ def test_term_loan_schedule_types(self):
+ loan = create_loan(
+ self.applicant1,
+ "Term Loan Type 1",
+ 12000,
+ "Repay Over Number of Periods",
+ 12,
+ repayment_start_date="2022-10-17",
+ )
+
+ # Check for first, second and last installment date
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[0].payment_date, "dd-MM-yyyy"), "17-10-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[1].payment_date, "dd-MM-yyyy"), "17-11-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[-1].payment_date, "dd-MM-yyyy"), "17-09-2023"
+ )
+
+ loan.loan_type = "Term Loan Type 2"
+ loan.save()
+
+ # Check for first, second and last installment date
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[0].payment_date, "dd-MM-yyyy"), "01-11-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[1].payment_date, "dd-MM-yyyy"), "01-12-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[-1].payment_date, "dd-MM-yyyy"), "01-10-2023"
+ )
+
+ loan.loan_type = "Term Loan Type 3"
+ loan.save()
+
+ # Check for first, second and last installment date
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[0].payment_date, "dd-MM-yyyy"), "31-10-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[1].payment_date, "dd-MM-yyyy"), "30-11-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[-1].payment_date, "dd-MM-yyyy"), "30-09-2023"
+ )
+
+ loan.repayment_method = "Repay Fixed Amount per Period"
+ loan.monthly_repayment_amount = 1042
+ loan.save()
+
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[0].payment_date, "dd-MM-yyyy"), "31-10-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[1].payment_date, "dd-MM-yyyy"), "30-11-2022"
+ )
+ self.assertEqual(
+ format_date(loan.get("repayment_schedule")[-1].payment_date, "dd-MM-yyyy"), "30-09-2023"
+ )
+
def create_loan_scenario_for_penalty(doc):
pledge = [{"loan_security": "Test Security 1", "qty": 4000.00}]
@@ -1033,6 +1151,8 @@
penalty_income_account=None,
repayment_method=None,
repayment_periods=None,
+ repayment_schedule_type=None,
+ repayment_date_on=None,
):
if not frappe.db.exists("Loan Type", loan_name):
@@ -1042,6 +1162,7 @@
"company": "_Test Company",
"loan_name": loan_name,
"is_term_loan": is_term_loan,
+ "repayment_schedule_type": "Monthly as per repayment start date",
"maximum_loan_amount": maximum_loan_amount,
"rate_of_interest": rate_of_interest,
"penalty_interest_rate": penalty_interest_rate,
@@ -1056,8 +1177,14 @@
"repayment_periods": repayment_periods,
"write_off_amount": 100,
}
- ).insert()
+ )
+ if loan_type.is_term_loan:
+ loan_type.repayment_schedule_type = repayment_schedule_type
+ if loan_type.repayment_schedule_type != "Monthly as per repayment start date":
+ loan_type.repayment_date_on = repayment_date_on
+
+ loan_type.insert()
loan_type.submit()
diff --git a/erpnext/loan_management/doctype/loan_type/loan_type.json b/erpnext/loan_management/doctype/loan_type/loan_type.json
index 00337e4..5cc9464 100644
--- a/erpnext/loan_management/doctype/loan_type/loan_type.json
+++ b/erpnext/loan_management/doctype/loan_type/loan_type.json
@@ -16,6 +16,8 @@
"company",
"is_term_loan",
"disabled",
+ "repayment_schedule_type",
+ "repayment_date_on",
"description",
"account_details_section",
"mode_of_payment",
@@ -157,12 +159,30 @@
"label": "Disbursement Account",
"options": "Account",
"reqd": 1
+ },
+ {
+ "depends_on": "is_term_loan",
+ "description": "The schedule type that will be used for generating the term loan schedules (will affect the payment date and monthly repayment amount)",
+ "fieldname": "repayment_schedule_type",
+ "fieldtype": "Select",
+ "label": "Repayment Schedule Type",
+ "mandatory_depends_on": "is_term_loan",
+ "options": "\nMonthly as per repayment start date\nPro-rated calendar months"
+ },
+ {
+ "depends_on": "eval:doc.repayment_schedule_type == \"Pro-rated calendar months\"",
+ "description": "Select whether the repayment date should be the end of the current month or start of the upcoming month",
+ "fieldname": "repayment_date_on",
+ "fieldtype": "Select",
+ "label": "Repayment Date On",
+ "mandatory_depends_on": "eval:doc.repayment_schedule_type == \"Pro-rated calendar months\"",
+ "options": "\nStart of the next month\nEnd of the current month"
}
],
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2022-01-25 16:23:57.009349",
+ "modified": "2022-10-22 17:43:03.954201",
"modified_by": "Administrator",
"module": "Loan Management",
"name": "Loan Type",
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/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/patches.txt b/erpnext/patches.txt
index fc63f12..6a8c21f 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -315,4 +315,5 @@
erpnext.patches.v14_0.create_accounting_dimensions_in_subcontracting_doctypes
erpnext.patches.v14_0.fix_subcontracting_receipt_gl_entries
erpnext.patches.v14_0.migrate_remarks_from_gl_to_payment_ledger
+erpnext.patches.v13_0.update_schedule_type_in_loans
erpnext.patches.v14_0.create_accounting_dimensions_for_asset_capitalization
diff --git a/erpnext/patches/v13_0/update_schedule_type_in_loans.py b/erpnext/patches/v13_0/update_schedule_type_in_loans.py
new file mode 100644
index 0000000..e5b5f64
--- /dev/null
+++ b/erpnext/patches/v13_0/update_schedule_type_in_loans.py
@@ -0,0 +1,14 @@
+import frappe
+
+
+def execute():
+ loan = frappe.qb.DocType("Loan")
+ loan_type = frappe.qb.DocType("Loan Type")
+
+ frappe.qb.update(loan_type).set(
+ loan_type.repayment_schedule_type, "Monthly as per repayment start date"
+ ).where(loan_type.is_term_loan == 1).run()
+
+ frappe.qb.update(loan).set(
+ loan.repayment_schedule_type, "Monthly as per repayment start date"
+ ).where(loan.is_term_loan == 1).run()
diff --git a/erpnext/public/js/utils/party.js b/erpnext/public/js/utils/party.js
index 58594b0..644adff 100644
--- a/erpnext/public/js/utils/party.js
+++ b/erpnext/public/js/utils/party.js
@@ -242,20 +242,29 @@
});
};
-erpnext.utils.get_contact_details = function(frm) {
+erpnext.utils.get_contact_details = function (frm) {
if (frm.updating_party_details) return;
if (frm.doc["contact_person"]) {
frappe.call({
method: "frappe.contacts.doctype.contact.contact.get_contact_details",
- args: {contact: frm.doc.contact_person },
- callback: function(r) {
- if (r.message)
- frm.set_value(r.message);
- }
- })
+ args: { contact: frm.doc.contact_person },
+ callback: function (r) {
+ if (r.message) frm.set_value(r.message);
+ },
+ });
+ } else {
+ frm.set_value({
+ contact_person: "",
+ contact_display: "",
+ contact_email: "",
+ contact_mobile: "",
+ contact_phone: "",
+ contact_designation: "",
+ contact_department: "",
+ });
}
-}
+};
erpnext.utils.validate_mandatory = function(frm, label, value, trigger_on) {
if (!value) {
diff --git a/erpnext/selling/doctype/customer/test_customer.py b/erpnext/selling/doctype/customer/test_customer.py
index 7dc3fab..691adcc 100644
--- a/erpnext/selling/doctype/customer/test_customer.py
+++ b/erpnext/selling/doctype/customer/test_customer.py
@@ -3,6 +3,7 @@
import frappe
+from frappe.custom.doctype.property_setter.property_setter import make_property_setter
from frappe.test_runner import make_test_records
from frappe.tests.utils import FrappeTestCase
from frappe.utils import flt
@@ -341,6 +342,33 @@
due_date = get_due_date("2017-01-22", "Customer", "_Test Customer")
self.assertEqual(due_date, "2017-01-22")
+ def test_serach_fields_for_customer(self):
+ from erpnext.controllers.queries import customer_query
+
+ make_property_setter(
+ "Customer", None, "search_fields", "customer_group", "Data", for_doctype="Doctype"
+ )
+
+ data = customer_query(
+ "Customer", "_Test Customer", "", 0, 20, filters={"name": "_Test Customer"}, as_dict=True
+ )
+
+ self.assertEqual(data[0].name, "_Test Customer")
+ self.assertEqual(data[0].customer_group, "_Test Customer Group")
+ self.assertTrue("territory" not in data[0])
+
+ make_property_setter(
+ "Customer", None, "search_fields", "customer_group, territory", "Data", for_doctype="Doctype"
+ )
+ data = customer_query(
+ "Customer", "_Test Customer", "", 0, 20, filters={"name": "_Test Customer"}, as_dict=True
+ )
+
+ self.assertEqual(data[0].name, "_Test Customer")
+ self.assertEqual(data[0].customer_group, "_Test Customer Group")
+ self.assertEqual(data[0].territory, "_Test Territory")
+ self.assertTrue("territory" in data[0])
+
def get_customer_dict(customer_name):
return {
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index f0e9e4b..1f3419f 100755
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -627,6 +627,7 @@
"field_map": {
"name": "sales_order",
"base_grand_total": "estimated_costing",
+ "net_total": "total_sales_amount",
},
},
},
diff --git a/erpnext/selling/doctype/sales_order_item/sales_order_item.json b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
index 2cf836f..ea0b25f 100644
--- a/erpnext/selling/doctype/sales_order_item/sales_order_item.json
+++ b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
@@ -272,6 +272,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "discount_and_margin",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -842,7 +843,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-09-06 13:24:18.065312",
+ "modified": "2022-10-26 16:05:02.712705",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Order Item",
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/delivery_note_item/delivery_note_item.json b/erpnext/stock/doctype/delivery_note_item/delivery_note_item.json
index 0a5cbab..77c3253 100644
--- a/erpnext/stock/doctype/delivery_note_item/delivery_note_item.json
+++ b/erpnext/stock/doctype/delivery_note_item/delivery_note_item.json
@@ -261,6 +261,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "discount_and_margin",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -814,7 +815,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-10-12 03:36:05.344847",
+ "modified": "2022-10-26 16:05:17.720768",
"modified_by": "Administrator",
"module": "Stock",
"name": "Delivery Note Item",
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt_dashboard.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt_dashboard.py
index 06ba936..b3ae7b5 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt_dashboard.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt_dashboard.py
@@ -12,13 +12,17 @@
"Purchase Receipt": "return_against",
},
"internal_links": {
+ "Material Request": ["items", "material_request"],
"Purchase Order": ["items", "purchase_order"],
"Project": ["items", "project"],
"Quality Inspection": ["items", "quality_inspection"],
},
"transactions": [
{"label": _("Related"), "items": ["Purchase Invoice", "Landed Cost Voucher", "Asset"]},
- {"label": _("Reference"), "items": ["Purchase Order", "Quality Inspection", "Project"]},
+ {
+ "label": _("Reference"),
+ "items": ["Material Request", "Purchase Order", "Quality Inspection", "Project"],
+ },
{"label": _("Returns"), "items": ["Purchase Receipt"]},
{"label": _("Subscription"), "items": ["Auto Repeat"]},
],
diff --git a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
index 772736e..474ee92 100644
--- a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
+++ b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
@@ -919,6 +919,7 @@
},
{
"collapsible": 1,
+ "collapsible_depends_on": "eval: doc.margin_type || doc.discount_amount",
"fieldname": "discount_and_margin_section",
"fieldtype": "Section Break",
"label": "Discount and Margin"
@@ -1000,7 +1001,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-10-12 03:37:59.516609",
+ "modified": "2022-10-26 16:06:02.524435",
"modified_by": "Administrator",
"module": "Stock",
"name": "Purchase Receipt Item",
diff --git a/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py b/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
index f308e9e..a6fc049 100644
--- a/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
+++ b/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
@@ -3,6 +3,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Abs, Sum
from frappe.utils import flt, getdate
@@ -11,8 +12,6 @@
filters = {}
float_precision = frappe.db.get_default("float_precision")
- condition = get_condition(filters)
-
avg_daily_outgoing = 0
diff = ((getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days) + 1
if diff <= 0:
@@ -20,8 +19,8 @@
columns = get_columns()
items = get_item_info(filters)
- consumed_item_map = get_consumed_items(condition)
- delivered_item_map = get_delivered_items(condition)
+ consumed_item_map = get_consumed_items(filters)
+ delivered_item_map = get_delivered_items(filters)
data = []
for item in items:
@@ -71,76 +70,86 @@
def get_item_info(filters):
from erpnext.stock.report.stock_ledger.stock_ledger import get_item_group_condition
- conditions = [get_item_group_condition(filters.get("item_group"))]
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- conditions.append("is_stock_item = 1")
-
- return frappe.db.sql(
- """select name, item_name, description, brand, item_group,
- safety_stock, lead_time_days from `tabItem` item where {}""".format(
- " and ".join(conditions)
- ),
- filters,
- as_dict=1,
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(item)
+ .select(
+ item.name,
+ item.item_name,
+ item.description,
+ item.brand,
+ item.item_group,
+ item.safety_stock,
+ item.lead_time_days,
+ )
+ .where(item.is_stock_item == 1)
)
+ if brand := filters.get("brand"):
+ query = query.where(item.brand == brand)
-def get_consumed_items(condition):
+ if conditions := get_item_group_condition(filters.get("item_group"), item):
+ query = query.where(conditions)
+
+ return query.run(as_dict=True)
+
+
+def get_consumed_items(filters):
purpose_to_exclude = [
"Material Transfer for Manufacture",
"Material Transfer",
"Send to Subcontractor",
]
- condition += """
- and (
- purpose is NULL
- or purpose not in ({})
+ se = frappe.qb.DocType("Stock Entry")
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .left_join(se)
+ .on(sle.voucher_no == se.name)
+ .select(sle.item_code, Abs(Sum(sle.actual_qty)).as_("consumed_qty"))
+ .where(
+ (sle.actual_qty < 0)
+ & (sle.is_cancelled == 0)
+ & (sle.voucher_type.notin(["Delivery Note", "Sales Invoice"]))
+ & ((se.purpose.isnull()) | (se.purpose.notin(purpose_to_exclude)))
)
- """.format(
- ", ".join(f"'{p}'" for p in purpose_to_exclude)
+ .groupby(sle.item_code)
)
- condition = condition.replace("posting_date", "sle.posting_date")
+ query = get_filtered_query(filters, sle, query)
- consumed_items = frappe.db.sql(
- """
- select item_code, abs(sum(actual_qty)) as consumed_qty
- from `tabStock Ledger Entry` as sle left join `tabStock Entry` as se
- on sle.voucher_no = se.name
- where
- actual_qty < 0
- and is_cancelled = 0
- and voucher_type not in ('Delivery Note', 'Sales Invoice')
- %s
- group by item_code"""
- % condition,
- as_dict=1,
- )
+ consumed_items = query.run(as_dict=True)
consumed_items_map = {item.item_code: item.consumed_qty for item in consumed_items}
return consumed_items_map
-def get_delivered_items(condition):
- dn_items = frappe.db.sql(
- """select dn_item.item_code, sum(dn_item.stock_qty) as dn_qty
- from `tabDelivery Note` dn, `tabDelivery Note Item` dn_item
- where dn.name = dn_item.parent and dn.docstatus = 1 %s
- group by dn_item.item_code"""
- % (condition),
- as_dict=1,
+def get_delivered_items(filters):
+ parent = frappe.qb.DocType("Delivery Note")
+ child = frappe.qb.DocType("Delivery Note Item")
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(child.item_code, Sum(child.stock_qty).as_("dn_qty"))
+ .where((parent.name == child.parent) & (parent.docstatus == 1))
+ .groupby(child.item_code)
)
+ query = get_filtered_query(filters, parent, query)
- si_items = frappe.db.sql(
- """select si_item.item_code, sum(si_item.stock_qty) as si_qty
- from `tabSales Invoice` si, `tabSales Invoice Item` si_item
- where si.name = si_item.parent and si.docstatus = 1 and
- si.update_stock = 1 %s
- group by si_item.item_code"""
- % (condition),
- as_dict=1,
+ dn_items = query.run(as_dict=True)
+
+ parent = frappe.qb.DocType("Sales Invoice")
+ child = frappe.qb.DocType("Sales Invoice Item")
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(child.item_code, Sum(child.stock_qty).as_("si_qty"))
+ .where((parent.name == child.parent) & (parent.docstatus == 1) & (parent.update_stock == 1))
+ .groupby(child.item_code)
)
+ query = get_filtered_query(filters, parent, query)
+
+ si_items = query.run(as_dict=True)
dn_item_map = {}
for item in dn_items:
@@ -152,13 +161,10 @@
return dn_item_map
-def get_condition(filters):
- conditions = ""
+def get_filtered_query(filters, table, query):
if filters.get("from_date") and filters.get("to_date"):
- conditions += " and posting_date between '%s' and '%s'" % (
- filters["from_date"],
- filters["to_date"],
- )
+ query = query.where(table.posting_date.between(filters["from_date"], filters["to_date"]))
else:
- frappe.throw(_("From and To dates required"))
- return conditions
+ frappe.throw(_("From and To dates are required"))
+
+ return query
diff --git a/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py b/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
index 854875a..9e75201 100644
--- a/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
+++ b/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
@@ -4,7 +4,9 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull
from frappe.utils import flt
+from pypika.terms import ExistsCriterion
from erpnext.stock.report.stock_ledger.stock_ledger import get_item_group_condition
@@ -123,43 +125,65 @@
pb_details = frappe._dict()
item_details = frappe._dict()
- conditions = get_parent_item_conditions(filters)
- parent_item_details = frappe.db.sql(
- """
- select item.name as item_code, item.item_name, pb.description, item.item_group, item.brand, item.stock_uom
- from `tabItem` item
- inner join `tabProduct Bundle` pb on pb.new_item_code = item.name
- where ifnull(item.disabled, 0) = 0 {0}
- """.format(
- conditions
- ),
- filters,
- as_dict=1,
- ) # nosec
+ item = frappe.qb.DocType("Item")
+ pb = frappe.qb.DocType("Product Bundle")
+
+ query = (
+ frappe.qb.from_(item)
+ .inner_join(pb)
+ .on(pb.new_item_code == item.name)
+ .select(
+ item.name.as_("item_code"),
+ item.item_name,
+ pb.description,
+ item.item_group,
+ item.brand,
+ item.stock_uom,
+ )
+ .where(IfNull(item.disabled, 0) == 0)
+ )
+
+ if item_code := filters.get("item_code"):
+ query = query.where(item.item_code == item_code)
+ else:
+ if brand := filters.get("brand"):
+ query = query.where(item.brand == brand)
+ if item_group := filters.get("item_group"):
+ if conditions := get_item_group_condition(item_group, item):
+ query = query.where(conditions)
+
+ parent_item_details = query.run(as_dict=True)
parent_items = []
for d in parent_item_details:
parent_items.append(d.item_code)
item_details[d.item_code] = d
+ child_item_details = []
if parent_items:
- child_item_details = frappe.db.sql(
- """
- select
- pb.new_item_code as parent_item, pbi.item_code, item.item_name, pbi.description, item.item_group, item.brand,
- item.stock_uom, pbi.uom, pbi.qty
- from `tabProduct Bundle Item` pbi
- inner join `tabProduct Bundle` pb on pb.name = pbi.parent
- inner join `tabItem` item on item.name = pbi.item_code
- where pb.new_item_code in ({0})
- """.format(
- ", ".join(["%s"] * len(parent_items))
- ),
- parent_items,
- as_dict=1,
- ) # nosec
- else:
- child_item_details = []
+ item = frappe.qb.DocType("Item")
+ pb = frappe.qb.DocType("Product Bundle")
+ pbi = frappe.qb.DocType("Product Bundle Item")
+
+ child_item_details = (
+ frappe.qb.from_(pbi)
+ .inner_join(pb)
+ .on(pb.name == pbi.parent)
+ .inner_join(item)
+ .on(item.name == pbi.item_code)
+ .select(
+ pb.new_item_code.as_("parent_item"),
+ pbi.item_code,
+ item.item_name,
+ pbi.description,
+ item.item_group,
+ item.brand,
+ item.stock_uom,
+ pbi.uom,
+ pbi.qty,
+ )
+ .where(pb.new_item_code.isin(parent_items))
+ ).run(as_dict=1)
child_items = set()
for d in child_item_details:
@@ -184,58 +208,42 @@
if not items:
return []
- item_conditions_sql = " and sle.item_code in ({})".format(
- ", ".join(frappe.db.escape(i) for i in items)
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ sle2 = frappe.qb.DocType("Stock Ledger Entry")
+
+ query = (
+ frappe.qb.from_(sle)
+ .force_index("posting_sort_index")
+ .left_join(sle2)
+ .on(
+ (sle.item_code == sle2.item_code)
+ & (sle.warehouse == sle2.warehouse)
+ & (sle.posting_date < sle2.posting_date)
+ & (sle.posting_time < sle2.posting_time)
+ & (sle.name < sle2.name)
+ )
+ .select(sle.item_code, sle.warehouse, sle.qty_after_transaction, sle.company)
+ .where((sle2.name.isnull()) & (sle.docstatus < 2) & (sle.item_code.isin(items)))
)
- conditions = get_sle_conditions(filters)
-
- return frappe.db.sql(
- """
- select
- sle.item_code, sle.warehouse, sle.qty_after_transaction, sle.company
- from
- `tabStock Ledger Entry` sle force index (posting_sort_index)
- left join `tabStock Ledger Entry` sle2 on
- sle.item_code = sle2.item_code and sle.warehouse = sle2.warehouse
- and (sle.posting_date, sle.posting_time, sle.name) < (sle2.posting_date, sle2.posting_time, sle2.name)
- where sle2.name is null and sle.docstatus < 2 %s %s"""
- % (item_conditions_sql, conditions),
- as_dict=1,
- ) # nosec
-
-
-def get_parent_item_conditions(filters):
- conditions = []
-
- if filters.get("item_code"):
- conditions.append("item.item_code = %(item_code)s")
+ if date := filters.get("date"):
+ query = query.where(sle.posting_date <= date)
else:
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- if filters.get("item_group"):
- conditions.append(get_item_group_condition(filters.get("item_group")))
-
- conditions = " and ".join(conditions)
- return "and {0}".format(conditions) if conditions else ""
-
-
-def get_sle_conditions(filters):
- conditions = ""
- if not filters.get("date"):
frappe.throw(_("'Date' is required"))
- conditions += " and sle.posting_date <= %s" % frappe.db.escape(filters.get("date"))
-
if filters.get("warehouse"):
warehouse_details = frappe.db.get_value(
"Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1
)
- if warehouse_details:
- conditions += (
- " and exists (select name from `tabWarehouse` wh \
- where wh.lft >= %s and wh.rgt <= %s and sle.warehouse = wh.name)"
- % (warehouse_details.lft, warehouse_details.rgt)
- ) # nosec
- return conditions
+ if warehouse_details:
+ 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))
+ )
+ )
+
+ return query.run(as_dict=True)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index a951197..af7f20f 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -305,20 +305,25 @@
def get_items(filters):
+ item = frappe.qb.DocType("Item")
+ query = frappe.qb.from_(item).select(item.name)
conditions = []
- if filters.get("item_code"):
- conditions.append("item.name=%(item_code)s")
+
+ if item_code := filters.get("item_code"):
+ conditions.append(item.name == item_code)
else:
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- if filters.get("item_group"):
- conditions.append(get_item_group_condition(filters.get("item_group")))
+ if brand := filters.get("brand"):
+ conditions.append(item.brand == brand)
+ if item_group := filters.get("item_group"):
+ if condition := get_item_group_condition(item_group, item):
+ conditions.append(condition)
items = []
if conditions:
- items = frappe.db.sql_list(
- """select name from `tabItem` item where {}""".format(" and ".join(conditions)), filters
- )
+ for condition in conditions:
+ query = query.where(condition)
+ items = [r[0] for r in query.run()]
+
return items
@@ -330,29 +335,22 @@
if not items:
return item_details
- cf_field = cf_join = ""
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(item)
+ .select(item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom)
+ .where(item.name.isin(items))
+ )
+
if include_uom:
- cf_field = ", ucd.conversion_factor"
- cf_join = (
- "left join `tabUOM Conversion Detail` ucd on ucd.parent=item.name and ucd.uom=%s"
- % frappe.db.escape(include_uom)
+ ucd = frappe.qb.DocType("UOM Conversion Detail")
+ query = (
+ query.left_join(ucd)
+ .on((ucd.parent == item.name) & (ucd.uom == include_uom))
+ .select(ucd.conversion_factor)
)
- res = 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.name in ({item_codes})
- """.format(
- cf_field=cf_field, cf_join=cf_join, item_codes=",".join(["%s"] * len(items))
- ),
- items,
- as_dict=1,
- )
+ res = query.run(as_dict=True)
for item in res:
item_details.setdefault(item.name, item)
@@ -427,16 +425,28 @@
return ""
-def get_item_group_condition(item_group):
+def get_item_group_condition(item_group, item_table=None):
item_group_details = frappe.db.get_value("Item Group", item_group, ["lft", "rgt"], as_dict=1)
if item_group_details:
- return (
- "item.item_group in (select ig.name from `tabItem Group` ig \
- where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)"
- % (item_group_details.lft, item_group_details.rgt)
- )
-
- return ""
+ if item_table:
+ ig = frappe.qb.DocType("Item Group")
+ return item_table.item_group.isin(
+ (
+ frappe.qb.from_(ig)
+ .select(ig.name)
+ .where(
+ (ig.lft >= item_group_details.lft)
+ & (ig.rgt <= item_group_details.rgt)
+ & (item_table.item_group == ig.name)
+ )
+ )
+ )
+ else:
+ return (
+ "item.item_group in (select ig.name from `tabItem Group` ig \
+ where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)"
+ % (item_group_details.lft, item_group_details.rgt)
+ )
def check_inventory_dimension_filters_applied(filters) -> bool:
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 {}