Merge branch 'develop' into patch-9
diff --git a/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.js b/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.js
index febf85c..99cc0a7 100644
--- a/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.js
+++ b/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.js
@@ -43,20 +43,13 @@
reference_docname: function(frm) {
if (frm.doc.reference_docname && frm.doc.reference_doctype) {
- let fields_to_fetch = ["grand_total"];
let party_field = frm.doc.reference_doctype == "Sales Order" ? "customer" : "supplier";
- if (frm.doc.reference_doctype == "Sales Order") {
- fields_to_fetch.push("project");
- }
-
- fields_to_fetch.push(party_field);
frappe.call({
- method: "erpnext.accounts.doctype.bank_guarantee.bank_guarantee.get_vouchar_detials",
+ method: "erpnext.accounts.doctype.bank_guarantee.bank_guarantee.get_voucher_details",
args: {
- "column_list": fields_to_fetch,
- "doctype": frm.doc.reference_doctype,
- "docname": frm.doc.reference_docname
+ "bank_guarantee_type": frm.doc.bg_type,
+ "reference_name": frm.doc.reference_docname
},
callback: function(r) {
if (r.message) {
diff --git a/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.py b/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.py
index 9144a29..02eb599 100644
--- a/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.py
+++ b/erpnext/accounts/doctype/bank_guarantee/bank_guarantee.py
@@ -2,11 +2,8 @@
# For license information, please see license.txt
-import json
-
import frappe
from frappe import _
-from frappe.desk.search import sanitize_searchfield
from frappe.model.document import Document
@@ -25,14 +22,18 @@
@frappe.whitelist()
-def get_vouchar_detials(column_list, doctype, docname):
- column_list = json.loads(column_list)
- for col in column_list:
- sanitize_searchfield(col)
- return frappe.db.sql(
- """ select {columns} from `tab{doctype}` where name=%s""".format(
- columns=", ".join(column_list), doctype=doctype
- ),
- docname,
- as_dict=1,
- )[0]
+def get_voucher_details(bank_guarantee_type: str, reference_name: str):
+ if not isinstance(reference_name, str):
+ raise TypeError("reference_name must be a string")
+
+ fields_to_fetch = ["grand_total"]
+
+ if bank_guarantee_type == "Receiving":
+ doctype = "Sales Order"
+ fields_to_fetch.append("customer")
+ fields_to_fetch.append("project")
+ else:
+ doctype = "Purchase Order"
+ fields_to_fetch.append("supplier")
+
+ return frappe.db.get_value(doctype, reference_name, fields_to_fetch, as_dict=True)
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 9ebcadd..9a31aaf 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -1463,6 +1463,7 @@
def update_billing_status_in_pr(self, update_modified=True):
updated_pr = []
+ po_details = []
for d in self.get("items"):
if d.pr_detail:
billed_amt = frappe.db.sql(
@@ -1480,7 +1481,10 @@
)
updated_pr.append(d.purchase_receipt)
elif d.po_detail:
- updated_pr += update_billed_amount_based_on_po(d.po_detail, update_modified)
+ po_details.append(d.po_detail)
+
+ if po_details:
+ updated_pr += update_billed_amount_based_on_po(po_details, update_modified)
for pr in set(updated_pr):
from erpnext.stock.doctype.purchase_receipt.purchase_receipt import update_billing_percentage
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index f0106be..dacc809 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -3,7 +3,8 @@
import frappe
-from frappe import _, scrub
+from frappe import _, qb, scrub
+from frappe.query_builder import Order
from frappe.utils import cint, flt, formatdate
from erpnext.controllers.queries import get_match_cond
@@ -398,6 +399,7 @@
self.average_buying_rate = {}
self.filters = frappe._dict(filters)
self.load_invoice_items()
+ self.get_delivery_notes()
if filters.group_by == "Invoice":
self.group_items_by_invoice()
@@ -591,6 +593,21 @@
return flt(buying_amount, self.currency_precision)
+ def calculate_buying_amount_from_sle(self, row, my_sle, parenttype, parent, item_row, item_code):
+ for i, sle in enumerate(my_sle):
+ # find the stock valution rate from stock ledger entry
+ if (
+ sle.voucher_type == parenttype
+ and parent == sle.voucher_no
+ and sle.voucher_detail_no == item_row
+ ):
+ previous_stock_value = len(my_sle) > i + 1 and flt(my_sle[i + 1].stock_value) or 0.0
+
+ if previous_stock_value:
+ return abs(previous_stock_value - flt(sle.stock_value)) * flt(row.qty) / abs(flt(sle.qty))
+ else:
+ return flt(row.qty) * self.get_average_buying_rate(row, item_code)
+
def get_buying_amount(self, row, item_code):
# IMP NOTE
# stock_ledger_entries should already be filtered by item_code and warehouse and
@@ -607,19 +624,22 @@
if row.dn_detail:
parenttype, parent = "Delivery Note", row.delivery_note
- for i, sle in enumerate(my_sle):
- # find the stock valution rate from stock ledger entry
- if (
- sle.voucher_type == parenttype
- and parent == sle.voucher_no
- and sle.voucher_detail_no == row.item_row
- ):
- previous_stock_value = len(my_sle) > i + 1 and flt(my_sle[i + 1].stock_value) or 0.0
-
- if previous_stock_value:
- return abs(previous_stock_value - flt(sle.stock_value)) * flt(row.qty) / abs(flt(sle.qty))
- else:
- return flt(row.qty) * self.get_average_buying_rate(row, item_code)
+ return self.calculate_buying_amount_from_sle(
+ row, my_sle, parenttype, parent, row.item_row, item_code
+ )
+ elif self.delivery_notes.get((row.parent, row.item_code), None):
+ # check if Invoice has delivery notes
+ dn = self.delivery_notes.get((row.parent, row.item_code))
+ parenttype, parent, item_row, warehouse = (
+ "Delivery Note",
+ dn["delivery_note"],
+ dn["item_row"],
+ dn["warehouse"],
+ )
+ my_sle = self.sle.get((item_code, warehouse))
+ return self.calculate_buying_amount_from_sle(
+ row, my_sle, parenttype, parent, item_row, item_code
+ )
else:
return flt(row.qty) * self.get_average_buying_rate(row, item_code)
@@ -753,6 +773,29 @@
as_dict=1,
)
+ def get_delivery_notes(self):
+ self.delivery_notes = frappe._dict({})
+ if self.si_list:
+ invoices = [x.parent for x in self.si_list]
+ dni = qb.DocType("Delivery Note Item")
+ delivery_notes = (
+ qb.from_(dni)
+ .select(
+ dni.against_sales_invoice.as_("sales_invoice"),
+ dni.item_code,
+ dni.warehouse,
+ dni.parent.as_("delivery_note"),
+ dni.name.as_("item_row"),
+ )
+ .where((dni.docstatus == 1) & (dni.against_sales_invoice.isin(invoices)))
+ .groupby(dni.against_sales_invoice, dni.item_code)
+ .orderby(dni.creation, order=Order.desc)
+ .run(as_dict=True)
+ )
+
+ for entry in delivery_notes:
+ self.delivery_notes[(entry.sales_invoice, entry.item_code)] = entry
+
def group_items_by_invoice(self):
"""
Turns list of Sales Invoice Items to a tree of Sales Invoices with their Items as children.
diff --git a/erpnext/accounts/report/gross_profit/test_gross_profit.py b/erpnext/accounts/report/gross_profit/test_gross_profit.py
new file mode 100644
index 0000000..0ea6b5c
--- /dev/null
+++ b/erpnext/accounts/report/gross_profit/test_gross_profit.py
@@ -0,0 +1,209 @@
+import frappe
+from frappe import qb
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days, flt, nowdate
+
+from erpnext.accounts.doctype.sales_invoice.sales_invoice import make_delivery_note
+from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.accounts.report.gross_profit.gross_profit import execute
+from erpnext.stock.doctype.item.test_item import create_item
+from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry
+
+
+class TestGrossProfit(FrappeTestCase):
+ def setUp(self):
+ self.create_company()
+ self.create_item()
+ self.create_customer()
+ self.create_sales_invoice()
+ self.clear_old_entries()
+
+ def tearDown(self):
+ frappe.db.rollback()
+
+ def create_company(self):
+ company_name = "_Test Gross Profit"
+ abbr = "_GP"
+ if frappe.db.exists("Company", company_name):
+ company = frappe.get_doc("Company", company_name)
+ else:
+ company = frappe.get_doc(
+ {
+ "doctype": "Company",
+ "company_name": company_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 = "Stores - " + abbr
+ self.income_account = "Sales - " + abbr
+ self.expense_account = "Cost of Goods Sold - " + abbr
+ self.debit_to = "Debtors - " + abbr
+ self.creditors = "Creditors - " + abbr
+
+ def create_item(self):
+ item = create_item(
+ item_code="_Test GP Item", is_stock_item=1, company=self.company, warehouse=self.warehouse
+ )
+ self.item = item if isinstance(item, str) else item.item_code
+
+ def create_customer(self):
+ name = "_Test GP Customer"
+ if frappe.db.exists("Customer", name):
+ self.customer = name
+ else:
+ customer = frappe.new_doc("Customer")
+ customer.customer_name = name
+ customer.type = "Individual"
+ customer.save()
+ self.customer = customer.name
+
+ def create_sales_invoice(
+ self, qty=1, rate=100, posting_date=nowdate(), do_not_save=False, do_not_submit=False
+ ):
+ """
+ Helper function to populate default values in sales invoice
+ """
+ sinv = create_sales_invoice(
+ qty=qty,
+ rate=rate,
+ company=self.company,
+ customer=self.customer,
+ item_code=self.item,
+ item_name=self.item,
+ cost_center=self.cost_center,
+ warehouse=self.warehouse,
+ debit_to=self.debit_to,
+ parent_cost_center=self.cost_center,
+ update_stock=0,
+ currency="INR",
+ is_pos=0,
+ is_return=0,
+ return_against=None,
+ income_account=self.income_account,
+ expense_account=self.expense_account,
+ do_not_save=do_not_save,
+ do_not_submit=do_not_submit,
+ )
+ return sinv
+
+ def clear_old_entries(self):
+ doctype_list = [
+ "Sales Invoice",
+ "GL Entry",
+ "Payment Ledger Entry",
+ "Stock Entry",
+ "Stock Ledger Entry",
+ "Delivery Note",
+ ]
+ for doctype in doctype_list:
+ qb.from_(qb.DocType(doctype)).delete().where(qb.DocType(doctype).company == self.company).run()
+
+ def test_invoice_without_only_delivery_note(self):
+ """
+ Test buying amount for Invoice without `update_stock` flag set but has Delivery Note
+ """
+ se = make_stock_entry(
+ company=self.company,
+ item_code=self.item,
+ target=self.warehouse,
+ qty=1,
+ basic_rate=100,
+ do_not_submit=True,
+ )
+ item = se.items[0]
+ se.append(
+ "items",
+ {
+ "item_code": item.item_code,
+ "s_warehouse": item.s_warehouse,
+ "t_warehouse": item.t_warehouse,
+ "qty": 1,
+ "basic_rate": 200,
+ "conversion_factor": item.conversion_factor or 1.0,
+ "transfer_qty": flt(item.qty) * (flt(item.conversion_factor) or 1.0),
+ "serial_no": item.serial_no,
+ "batch_no": item.batch_no,
+ "cost_center": item.cost_center,
+ "expense_account": item.expense_account,
+ },
+ )
+ se = se.save().submit()
+
+ sinv = create_sales_invoice(
+ qty=1,
+ rate=100,
+ company=self.company,
+ customer=self.customer,
+ item_code=self.item,
+ item_name=self.item,
+ cost_center=self.cost_center,
+ warehouse=self.warehouse,
+ debit_to=self.debit_to,
+ parent_cost_center=self.cost_center,
+ update_stock=0,
+ currency="INR",
+ income_account=self.income_account,
+ expense_account=self.expense_account,
+ )
+
+ filters = frappe._dict(
+ company=self.company, from_date=nowdate(), to_date=nowdate(), group_by="Invoice"
+ )
+
+ columns, data = execute(filters=filters)
+
+ # Without Delivery Note, buying rate should be 150
+ expected_entry_without_dn = {
+ "parent_invoice": sinv.name,
+ "currency": "INR",
+ "sales_invoice": self.item,
+ "customer": self.customer,
+ "posting_date": frappe.utils.datetime.date.fromisoformat(nowdate()),
+ "item_code": self.item,
+ "item_name": self.item,
+ "warehouse": "Stores - _GP",
+ "qty": 1.0,
+ "avg._selling_rate": 100.0,
+ "valuation_rate": 150.0,
+ "selling_amount": 100.0,
+ "buying_amount": 150.0,
+ "gross_profit": -50.0,
+ "gross_profit_%": -50.0,
+ }
+ gp_entry = [x for x in data if x.parent_invoice == sinv.name]
+ self.assertDictContainsSubset(expected_entry_without_dn, gp_entry[0])
+
+ # make delivery note
+ dn = make_delivery_note(sinv.name)
+ dn.items[0].qty = 1
+ dn = dn.save().submit()
+
+ columns, data = execute(filters=filters)
+
+ # Without Delivery Note, buying rate should be 100
+ expected_entry_with_dn = {
+ "parent_invoice": sinv.name,
+ "currency": "INR",
+ "sales_invoice": self.item,
+ "customer": self.customer,
+ "posting_date": frappe.utils.datetime.date.fromisoformat(nowdate()),
+ "item_code": self.item,
+ "item_name": self.item,
+ "warehouse": "Stores - _GP",
+ "qty": 1.0,
+ "avg._selling_rate": 100.0,
+ "valuation_rate": 100.0,
+ "selling_amount": 100.0,
+ "buying_amount": 100.0,
+ "gross_profit": 0.0,
+ "gross_profit_%": 0.0,
+ }
+ gp_entry = [x for x in data if x.parent_invoice == sinv.name]
+ self.assertDictContainsSubset(expected_entry_with_dn, gp_entry[0])
diff --git a/erpnext/loan_management/doctype/loan_repayment/loan_repayment.py b/erpnext/loan_management/doctype/loan_repayment/loan_repayment.py
index e9fa2ad..8a185f8 100644
--- a/erpnext/loan_management/doctype/loan_repayment/loan_repayment.py
+++ b/erpnext/loan_management/doctype/loan_repayment/loan_repayment.py
@@ -576,8 +576,8 @@
loan_doc = frappe.get_doc("Loan", loan)
next_accrual_date = None
accrued_entries = 0
- last_repayment_amount = 0
- last_balance_amount = 0
+ last_repayment_amount = None
+ last_balance_amount = None
for term in reversed(loan_doc.get("repayment_schedule")):
if not term.is_accrued:
@@ -585,9 +585,9 @@
loan_doc.remove(term)
else:
accrued_entries += 1
- if not last_repayment_amount:
+ if last_repayment_amount is None:
last_repayment_amount = term.total_payment
- if not last_balance_amount:
+ if last_balance_amount is None:
last_balance_amount = term.balance_loan_amount
loan_doc.save()
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index f85c478..673fcb5 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -6,7 +6,9 @@
from frappe import _, throw
from frappe.desk.notifications import clear_doctype_notifications
from frappe.model.mapper import get_mapped_doc
+from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, flt, getdate, nowdate
+from pypika import functions as fn
import erpnext
from erpnext.accounts.utils import get_account_currency
@@ -750,48 +752,38 @@
def update_billing_status(self, update_modified=True):
updated_pr = [self.name]
+ po_details = []
for d in self.get("items"):
if d.get("purchase_invoice") and d.get("purchase_invoice_item"):
d.db_set("billed_amt", d.amount, update_modified=update_modified)
elif d.purchase_order_item:
- updated_pr += update_billed_amount_based_on_po(d.purchase_order_item, update_modified)
+ po_details.append(d.purchase_order_item)
+
+ if po_details:
+ updated_pr += update_billed_amount_based_on_po(po_details, update_modified)
for pr in set(updated_pr):
- pr_doc = self if (pr == self.name) else frappe.get_doc("Purchase Receipt", pr)
+ pr_doc = self if (pr == self.name) else frappe.get_cached_doc("Purchase Receipt", pr)
update_billing_percentage(pr_doc, update_modified=update_modified)
self.load_from_db()
-def update_billed_amount_based_on_po(po_detail, update_modified=True):
- # Billed against Sales Order directly
- billed_against_po = frappe.db.sql(
- """select sum(amount) from `tabPurchase Invoice Item`
- where po_detail=%s and (pr_detail is null or pr_detail = '') and docstatus=1""",
- po_detail,
- )
- billed_against_po = billed_against_po and billed_against_po[0][0] or 0
+def update_billed_amount_based_on_po(po_details, update_modified=True):
+ po_billed_amt_details = get_billed_amount_against_po(po_details)
- # Get all Purchase Receipt Item rows against the Purchase Order Item row
- pr_details = frappe.db.sql(
- """select pr_item.name, pr_item.amount, pr_item.parent
- from `tabPurchase Receipt Item` pr_item, `tabPurchase Receipt` pr
- where pr.name=pr_item.parent and pr_item.purchase_order_item=%s
- and pr.docstatus=1 and pr.is_return = 0
- order by pr.posting_date asc, pr.posting_time asc, pr.name asc""",
- po_detail,
- as_dict=1,
- )
+ # Get all Purchase Receipt Item rows against the Purchase Order Items
+ pr_details = get_purchase_receipts_against_po_details(po_details)
+
+ pr_items = [pr_detail.name for pr_detail in pr_details]
+ pr_items_billed_amount = get_billed_amount_against_pr(pr_items)
updated_pr = []
for pr_item in pr_details:
+ billed_against_po = flt(po_billed_amt_details.get(pr_item.purchase_order_item))
+
# Get billed amount directly against Purchase Receipt
- billed_amt_agianst_pr = frappe.db.sql(
- """select sum(amount) from `tabPurchase Invoice Item`
- where pr_detail=%s and docstatus=1""",
- pr_item.name,
- )
- billed_amt_agianst_pr = billed_amt_agianst_pr and billed_amt_agianst_pr[0][0] or 0
+ billed_amt_agianst_pr = flt(pr_items_billed_amount.get(pr_item.name, 0))
# Distribute billed amount directly against PO between PRs based on FIFO
if billed_against_po and billed_amt_agianst_pr < pr_item.amount:
@@ -803,19 +795,90 @@
billed_amt_agianst_pr += billed_against_po
billed_against_po = 0
- frappe.db.set_value(
- "Purchase Receipt Item",
- pr_item.name,
- "billed_amt",
- billed_amt_agianst_pr,
- update_modified=update_modified,
- )
+ po_billed_amt_details[pr_item.purchase_order_item] = billed_against_po
- updated_pr.append(pr_item.parent)
+ if pr_item.billed_amt != billed_amt_agianst_pr:
+ frappe.db.set_value(
+ "Purchase Receipt Item",
+ pr_item.name,
+ "billed_amt",
+ billed_amt_agianst_pr,
+ update_modified=update_modified,
+ )
+
+ updated_pr.append(pr_item.parent)
return updated_pr
+def get_purchase_receipts_against_po_details(po_details):
+ # Get Purchase Receipts against Purchase Order Items
+
+ purchase_receipt = frappe.qb.DocType("Purchase Receipt")
+ purchase_receipt_item = frappe.qb.DocType("Purchase Receipt Item")
+
+ query = (
+ frappe.qb.from_(purchase_receipt)
+ .inner_join(purchase_receipt_item)
+ .on(purchase_receipt.name == purchase_receipt_item.parent)
+ .select(
+ purchase_receipt_item.name,
+ purchase_receipt_item.parent,
+ purchase_receipt_item.amount,
+ purchase_receipt_item.billed_amt,
+ purchase_receipt_item.purchase_order_item,
+ )
+ .where(
+ (purchase_receipt_item.purchase_order_item.isin(po_details))
+ & (purchase_receipt.docstatus == 1)
+ & (purchase_receipt.is_return == 0)
+ )
+ .orderby(CombineDatetime(purchase_receipt.posting_date, purchase_receipt.posting_time))
+ .orderby(purchase_receipt.name)
+ )
+
+ return query.run(as_dict=True)
+
+
+def get_billed_amount_against_pr(pr_items):
+ # Get billed amount directly against Purchase Receipt
+
+ if not pr_items:
+ return {}
+
+ purchase_invoice_item = frappe.qb.DocType("Purchase Invoice Item")
+
+ query = (
+ frappe.qb.from_(purchase_invoice_item)
+ .select(fn.Sum(purchase_invoice_item.amount).as_("billed_amt"), purchase_invoice_item.pr_detail)
+ .where((purchase_invoice_item.pr_detail.isin(pr_items)) & (purchase_invoice_item.docstatus == 1))
+ .groupby(purchase_invoice_item.pr_detail)
+ ).run(as_dict=1)
+
+ return {d.pr_detail: flt(d.billed_amt) for d in query}
+
+
+def get_billed_amount_against_po(po_items):
+ # Get billed amount directly against Purchase Order
+ if not po_items:
+ return {}
+
+ purchase_invoice_item = frappe.qb.DocType("Purchase Invoice Item")
+
+ query = (
+ frappe.qb.from_(purchase_invoice_item)
+ .select(fn.Sum(purchase_invoice_item.amount).as_("billed_amt"), purchase_invoice_item.po_detail)
+ .where(
+ (purchase_invoice_item.po_detail.isin(po_items))
+ & (purchase_invoice_item.docstatus == 1)
+ & (purchase_invoice_item.pr_detail.isnull())
+ )
+ .groupby(purchase_invoice_item.po_detail)
+ ).run(as_dict=1)
+
+ return {d.po_detail: flt(d.billed_amt) for d in query}
+
+
def update_billing_percentage(pr_doc, update_modified=True):
# Reload as billed amount was set in db directly
pr_doc.load_from_db()
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index e83182f..108611c 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -330,6 +330,9 @@
else:
args.uom = item.stock_uom
+ # Set stock UOM in args, so that it can be used while fetching item price
+ args.stock_uom = item.stock_uom
+
if args.get("batch_no") and item.name != frappe.get_cached_value(
"Batch", args.get("batch_no"), "item"
):