Merge branch 'develop' into subcontracting
diff --git a/erpnext/accounts/README.md b/erpnext/accounts/README.md
index da1f201..15f7039 100644
--- a/erpnext/accounts/README.md
+++ b/erpnext/accounts/README.md
@@ -10,4 +10,42 @@
- Sales Invoice (Itemised)
- Purchase Invoice (Itemised)
-All accounting entries are stored in the `General Ledger`
\ No newline at end of file
+All accounting entries are stored in the `General Ledger`
+
+## Payment Ledger
+Transactions on Receivable and Payable Account types will also be stored in `Payment Ledger`. This is so that payment reconciliation process only requires update on this ledger.
+
+### Key Fields
+| Field | Description |
+|----------------------|----------------------------------|
+| `account_type` | Receivable/Payable |
+| `account` | Accounting head |
+| `party` | Party Name |
+| `voucher_no` | Voucher No |
+| `against_voucher_no` | Linked voucher(secondary effect) |
+| `amount` | can be +ve/-ve |
+
+### Design
+`debit` and `credit` have been replaced with `account_type` and `amount`. `against_voucher_no` is populated for all entries. So, outstanding amount can be calculated by summing up amount only using `against_voucher_no`.
+
+Ex:
+1. Consider an invoice for ₹100 and a partial payment of ₹80 against that invoice. Payment Ledger will have following entries.
+
+| voucher_no | against_voucher_no | amount |
+|------------|--------------------|--------|
+| SINV-01 | SINV-01 | 100 |
+| PAY-01 | SINV-01 | -80 |
+
+
+2. Reconcile a Credit Note against an invoice using a Journal Entry
+
+An invoice for ₹100 partially reconciled against a credit of ₹70 using a Journal Entry. Payment Ledger will have the following entries.
+
+| voucher_no | against_voucher_no | amount |
+|------------|--------------------|--------|
+| SINV-01 | SINV-01 | 100 |
+| | | |
+| CR-NOTE-01 | CR-NOTE-01 | -70 |
+| | | |
+| JE-01 | CR-NOTE-01 | +70 |
+| JE-01 | SINV-01 | -70 |
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index e5fa57d..9f71656 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -58,16 +58,20 @@
validate_balance_type(self.account, adv_adj)
validate_frozen_account(self.account, adv_adj)
- # Update outstanding amt on against voucher
- if (
- self.against_voucher_type in ["Journal Entry", "Sales Invoice", "Purchase Invoice", "Fees"]
- and self.against_voucher
- and self.flags.update_outstanding == "Yes"
- and not frappe.flags.is_reverse_depr_entry
- ):
- update_outstanding_amt(
- self.account, self.party_type, self.party, self.against_voucher_type, self.against_voucher
- )
+ if frappe.db.get_value("Account", self.account, "account_type") not in [
+ "Receivable",
+ "Payable",
+ ]:
+ # Update outstanding amt on against voucher
+ if (
+ self.against_voucher_type in ["Journal Entry", "Sales Invoice", "Purchase Invoice", "Fees"]
+ and self.against_voucher
+ and self.flags.update_outstanding == "Yes"
+ and not frappe.flags.is_reverse_depr_entry
+ ):
+ update_outstanding_amt(
+ self.account, self.party_type, self.party, self.against_voucher_type, self.against_voucher
+ )
def check_mandatory(self):
mandatory = ["account", "voucher_type", "voucher_no", "company"]
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 8f0fe51..787efd2 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -416,7 +416,7 @@
against_entries = frappe.db.sql(
"""select * from `tabJournal Entry Account`
where account = %s and docstatus = 1 and parent = %s
- and (reference_type is null or reference_type in ("", "Sales Order", "Purchase Order"))
+ and (reference_type is null or reference_type in ('', 'Sales Order', 'Purchase Order'))
""",
(d.account, d.reference_name),
as_dict=True,
@@ -800,9 +800,7 @@
self.total_amount_in_words = money_in_words(amt, currency)
- def make_gl_entries(self, cancel=0, adv_adj=0):
- from erpnext.accounts.general_ledger import make_gl_entries
-
+ def build_gl_map(self):
gl_map = []
for d in self.get("accounts"):
if d.debit or d.credit:
@@ -838,7 +836,12 @@
item=d,
)
)
+ return gl_map
+ def make_gl_entries(self, cancel=0, adv_adj=0):
+ from erpnext.accounts.general_ledger import make_gl_entries
+
+ gl_map = self.build_gl_map()
if self.voucher_type in ("Deferred Revenue", "Deferred Expense"):
update_outstanding = "No"
else:
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index a10a810..d8af9db 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -6,7 +6,7 @@
from functools import reduce
import frappe
-from frappe import ValidationError, _, scrub, throw
+from frappe import ValidationError, _, qb, scrub, throw
from frappe.utils import cint, comma_or, flt, getdate, nowdate
import erpnext
@@ -785,7 +785,7 @@
self.set("remarks", "\n".join(remarks))
- def make_gl_entries(self, cancel=0, adv_adj=0):
+ def build_gl_map(self):
if self.payment_type in ("Receive", "Pay") and not self.get("party_account_field"):
self.setup_party_account_field()
@@ -794,7 +794,10 @@
self.add_bank_gl_entries(gl_entries)
self.add_deductions_gl_entries(gl_entries)
self.add_tax_gl_entries(gl_entries)
+ return gl_entries
+ def make_gl_entries(self, cancel=0, adv_adj=0):
+ gl_entries = self.build_gl_map()
gl_entries = process_gl_map(gl_entries)
make_gl_entries(gl_entries, cancel=cancel, adv_adj=adv_adj)
@@ -1195,6 +1198,9 @@
if args.get("party_type") == "Member":
return
+ ple = qb.DocType("Payment Ledger Entry")
+ common_filter = []
+
# confirm that Supplier is not blocked
if args.get("party_type") == "Supplier":
supplier_status = get_supplier_block_status(args["party"])
@@ -1216,10 +1222,13 @@
condition = " and voucher_type={0} and voucher_no={1}".format(
frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"])
)
+ common_filter.append(ple.voucher_type == args["voucher_type"])
+ common_filter.append(ple.voucher_no == args["voucher_no"])
# Add cost center condition
if args.get("cost_center"):
condition += " and cost_center='%s'" % args.get("cost_center")
+ common_filter.append(ple.cost_center == args.get("cost_center"))
date_fields_dict = {
"posting_date": ["from_posting_date", "to_posting_date"],
@@ -1231,16 +1240,19 @@
condition += " and {0} between '{1}' and '{2}'".format(
fieldname, args.get(date_fields[0]), args.get(date_fields[1])
)
+ common_filter.append(ple[fieldname][args.get(date_fields[0]) : args.get(date_fields[1])])
if args.get("company"):
condition += " and company = {0}".format(frappe.db.escape(args.get("company")))
+ common_filter.append(ple.company == args.get("company"))
outstanding_invoices = get_outstanding_invoices(
args.get("party_type"),
args.get("party"),
args.get("party_account"),
- filters=args,
- condition=condition,
+ common_filter=common_filter,
+ min_outstanding=args.get("outstanding_amt_greater_than"),
+ max_outstanding=args.get("outstanding_amt_less_than"),
)
outstanding_invoices = split_invoices_based_on_payment_terms(outstanding_invoices)
@@ -1444,7 +1456,7 @@
voucher_type = "Sales Invoice" if party_type == "Customer" else "Purchase Invoice"
supplier_condition = ""
if voucher_type == "Purchase Invoice":
- supplier_condition = "and (release_date is null or release_date <= CURDATE())"
+ supplier_condition = "and (release_date is null or release_date <= CURRENT_DATE)"
if party_account_currency == company_currency:
grand_total_field = "base_grand_total"
rounded_total_field = "base_rounded_total"
diff --git a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
index a8211c8..9aa1a18 100644
--- a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
@@ -4,6 +4,7 @@
import unittest
import frappe
+from frappe.tests.utils import FrappeTestCase
from frappe.utils import flt, nowdate
from erpnext.accounts.doctype.payment_entry.payment_entry import (
@@ -24,7 +25,10 @@
test_dependencies = ["Item"]
-class TestPaymentEntry(unittest.TestCase):
+class TestPaymentEntry(FrappeTestCase):
+ def tearDown(self):
+ frappe.db.rollback()
+
def test_payment_entry_against_order(self):
so = make_sales_order()
pe = get_payment_entry("Sales Order", so.name, bank_account="_Test Cash - _TC")
diff --git a/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.py b/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.py
index 43e19f4..52df923 100644
--- a/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.py
+++ b/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.py
@@ -6,6 +6,19 @@
from frappe import _
from frappe.model.document import Document
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
+ get_checks_for_pl_and_bs_accounts,
+)
+from erpnext.accounts.doctype.accounting_dimension_filter.accounting_dimension_filter import (
+ get_dimension_filter_map,
+)
+from erpnext.accounts.doctype.gl_entry.gl_entry import (
+ validate_balance_type,
+ validate_frozen_account,
+)
+from erpnext.accounts.utils import update_voucher_outstanding
+from erpnext.exceptions import InvalidAccountDimensionError, MandatoryAccountDimensionError
+
class PaymentLedgerEntry(Document):
def validate_account(self):
@@ -18,5 +31,119 @@
if not valid_account:
frappe.throw(_("{0} account is not of type {1}").format(self.account, self.account_type))
+ def validate_account_details(self):
+ """Account must be ledger, active and not freezed"""
+
+ ret = frappe.db.sql(
+ """select is_group, docstatus, company
+ from tabAccount where name=%s""",
+ self.account,
+ as_dict=1,
+ )[0]
+
+ if ret.is_group == 1:
+ frappe.throw(
+ _(
+ """{0} {1}: Account {2} is a Group Account and group accounts cannot be used in transactions"""
+ ).format(self.voucher_type, self.voucher_no, self.account)
+ )
+
+ if ret.docstatus == 2:
+ frappe.throw(
+ _("{0} {1}: Account {2} is inactive").format(self.voucher_type, self.voucher_no, self.account)
+ )
+
+ if ret.company != self.company:
+ frappe.throw(
+ _("{0} {1}: Account {2} does not belong to Company {3}").format(
+ self.voucher_type, self.voucher_no, self.account, self.company
+ )
+ )
+
+ def validate_allowed_dimensions(self):
+ dimension_filter_map = get_dimension_filter_map()
+ for key, value in dimension_filter_map.items():
+ dimension = key[0]
+ account = key[1]
+
+ if self.account == account:
+ if value["is_mandatory"] and not self.get(dimension):
+ frappe.throw(
+ _("{0} is mandatory for account {1}").format(
+ frappe.bold(frappe.unscrub(dimension)), frappe.bold(self.account)
+ ),
+ MandatoryAccountDimensionError,
+ )
+
+ if value["allow_or_restrict"] == "Allow":
+ if self.get(dimension) and self.get(dimension) not in value["allowed_dimensions"]:
+ frappe.throw(
+ _("Invalid value {0} for {1} against account {2}").format(
+ frappe.bold(self.get(dimension)),
+ frappe.bold(frappe.unscrub(dimension)),
+ frappe.bold(self.account),
+ ),
+ InvalidAccountDimensionError,
+ )
+ else:
+ if self.get(dimension) and self.get(dimension) in value["allowed_dimensions"]:
+ frappe.throw(
+ _("Invalid value {0} for {1} against account {2}").format(
+ frappe.bold(self.get(dimension)),
+ frappe.bold(frappe.unscrub(dimension)),
+ frappe.bold(self.account),
+ ),
+ InvalidAccountDimensionError,
+ )
+
+ def validate_dimensions_for_pl_and_bs(self):
+ account_type = frappe.db.get_value("Account", self.account, "report_type")
+
+ for dimension in get_checks_for_pl_and_bs_accounts():
+ if (
+ account_type == "Profit and Loss"
+ and self.company == dimension.company
+ and dimension.mandatory_for_pl
+ and not dimension.disabled
+ ):
+ if not self.get(dimension.fieldname):
+ frappe.throw(
+ _("Accounting Dimension <b>{0}</b> is required for 'Profit and Loss' account {1}.").format(
+ dimension.label, self.account
+ )
+ )
+
+ if (
+ account_type == "Balance Sheet"
+ and self.company == dimension.company
+ and dimension.mandatory_for_bs
+ and not dimension.disabled
+ ):
+ if not self.get(dimension.fieldname):
+ frappe.throw(
+ _("Accounting Dimension <b>{0}</b> is required for 'Balance Sheet' account {1}.").format(
+ dimension.label, self.account
+ )
+ )
+
def validate(self):
self.validate_account()
+
+ def on_update(self):
+ adv_adj = self.flags.adv_adj
+ if not self.flags.from_repost:
+ self.validate_account_details()
+ self.validate_dimensions_for_pl_and_bs()
+ self.validate_allowed_dimensions()
+ validate_balance_type(self.account, adv_adj)
+ validate_frozen_account(self.account, adv_adj)
+
+ # update outstanding amount
+ if (
+ self.against_voucher_type in ["Journal Entry", "Sales Invoice", "Purchase Invoice", "Fees"]
+ and self.flags.update_outstanding == "Yes"
+ and not frappe.flags.is_reverse_depr_entry
+ ):
+ update_voucher_outstanding(
+ self.against_voucher_type, self.against_voucher_no, self.account, self.party_type, self.party
+ )
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index e5b942f..5b2b526 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -3,16 +3,26 @@
import frappe
-from frappe import _, msgprint
+from frappe import _, msgprint, qb
from frappe.model.document import Document
+from frappe.query_builder.custom import ConstantColumn
+from frappe.query_builder.functions import IfNull
from frappe.utils import flt, getdate, nowdate, today
import erpnext
-from erpnext.accounts.utils import get_outstanding_invoices, reconcile_against_document
+from erpnext.accounts.utils import (
+ QueryPaymentLedger,
+ get_outstanding_invoices,
+ reconcile_against_document,
+)
from erpnext.controllers.accounts_controller import get_advance_payment_entries
class PaymentReconciliation(Document):
+ def __init__(self, *args, **kwargs):
+ super(PaymentReconciliation, self).__init__(*args, **kwargs)
+ self.common_filter_conditions = []
+
@frappe.whitelist()
def get_unreconciled_entries(self):
self.get_nonreconciled_payment_entries()
@@ -108,54 +118,58 @@
return list(journal_entries)
def get_dr_or_cr_notes(self):
- condition = self.get_conditions(get_return_invoices=True)
- dr_or_cr = (
- "credit_in_account_currency"
- if erpnext.get_party_account_type(self.party_type) == "Receivable"
- else "debit_in_account_currency"
- )
- reconciled_dr_or_cr = (
- "debit_in_account_currency"
- if dr_or_cr == "credit_in_account_currency"
- else "credit_in_account_currency"
- )
+ self.build_qb_filter_conditions(get_return_invoices=True)
+ ple = qb.DocType("Payment Ledger Entry")
voucher_type = "Sales Invoice" if self.party_type == "Customer" else "Purchase Invoice"
- return frappe.db.sql(
- """ SELECT doc.name as reference_name, %(voucher_type)s as reference_type,
- (sum(gl.{dr_or_cr}) - sum(gl.{reconciled_dr_or_cr})) as amount, doc.posting_date,
- account_currency as currency
- FROM `tab{doc}` doc, `tabGL Entry` gl
- WHERE
- (doc.name = gl.against_voucher or doc.name = gl.voucher_no)
- and doc.{party_type_field} = %(party)s
- and doc.is_return = 1 and ifnull(doc.return_against, "") = ""
- and gl.against_voucher_type = %(voucher_type)s
- and doc.docstatus = 1 and gl.party = %(party)s
- and gl.party_type = %(party_type)s and gl.account = %(account)s
- and gl.is_cancelled = 0 {condition}
- GROUP BY doc.name
- Having
- amount > 0
- ORDER BY doc.posting_date
- """.format(
- doc=voucher_type,
- dr_or_cr=dr_or_cr,
- reconciled_dr_or_cr=reconciled_dr_or_cr,
- party_type_field=frappe.scrub(self.party_type),
- condition=condition or "",
- ),
- {
- "party": self.party,
- "party_type": self.party_type,
- "voucher_type": voucher_type,
- "account": self.receivable_payable_account,
- },
- as_dict=1,
+ if erpnext.get_party_account_type(self.party_type) == "Receivable":
+ self.common_filter_conditions.append(ple.account_type == "Receivable")
+ else:
+ self.common_filter_conditions.append(ple.account_type == "Payable")
+ self.common_filter_conditions.append(ple.account == self.receivable_payable_account)
+
+ # get return invoices
+ doc = qb.DocType(voucher_type)
+ return_invoices = (
+ qb.from_(doc)
+ .select(ConstantColumn(voucher_type).as_("voucher_type"), doc.name.as_("voucher_no"))
+ .where(
+ (doc.docstatus == 1)
+ & (doc[frappe.scrub(self.party_type)] == self.party)
+ & (doc.is_return == 1)
+ & (IfNull(doc.return_against, "") == "")
+ )
+ .run(as_dict=True)
)
+ outstanding_dr_or_cr = []
+ if return_invoices:
+ ple_query = QueryPaymentLedger()
+ return_outstanding = ple_query.get_voucher_outstandings(
+ vouchers=return_invoices,
+ common_filter=self.common_filter_conditions,
+ min_outstanding=-(self.minimum_payment_amount) if self.minimum_payment_amount else None,
+ max_outstanding=-(self.maximum_payment_amount) if self.maximum_payment_amount else None,
+ get_payments=True,
+ )
+
+ for inv in return_outstanding:
+ if inv.outstanding != 0:
+ outstanding_dr_or_cr.append(
+ frappe._dict(
+ {
+ "reference_type": inv.voucher_type,
+ "reference_name": inv.voucher_no,
+ "amount": -(inv.outstanding),
+ "posting_date": inv.posting_date,
+ "currency": inv.currency,
+ }
+ )
+ )
+ return outstanding_dr_or_cr
+
def add_payment_entries(self, non_reconciled_payments):
self.set("payments", [])
@@ -166,10 +180,15 @@
def get_invoice_entries(self):
# Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against
- condition = self.get_conditions(get_invoices=True)
+ self.build_qb_filter_conditions(get_invoices=True)
non_reconciled_invoices = get_outstanding_invoices(
- self.party_type, self.party, self.receivable_payable_account, condition=condition
+ self.party_type,
+ self.party,
+ self.receivable_payable_account,
+ common_filter=self.common_filter_conditions,
+ min_outstanding=self.minimum_invoice_amount if self.minimum_invoice_amount else None,
+ max_outstanding=self.maximum_invoice_amount if self.maximum_invoice_amount else None,
)
if self.invoice_limit:
@@ -329,89 +348,56 @@
if not invoices_to_reconcile:
frappe.throw(_("No records found in Allocation table"))
- def get_conditions(self, get_invoices=False, get_payments=False, get_return_invoices=False):
- condition = " and company = '{0}' ".format(self.company)
+ def build_qb_filter_conditions(self, get_invoices=False, get_return_invoices=False):
+ self.common_filter_conditions.clear()
+ ple = qb.DocType("Payment Ledger Entry")
- if self.get("cost_center") and (get_invoices or get_payments or get_return_invoices):
- condition = " and cost_center = '{0}' ".format(self.cost_center)
+ self.common_filter_conditions.append(ple.company == self.company)
+
+ if self.get("cost_center") and (get_invoices or get_return_invoices):
+ self.common_filter_conditions.append(ple.cost_center == self.cost_center)
if get_invoices:
- condition += (
- " and posting_date >= {0}".format(frappe.db.escape(self.from_invoice_date))
- if self.from_invoice_date
- else ""
- )
- condition += (
- " and posting_date <= {0}".format(frappe.db.escape(self.to_invoice_date))
- if self.to_invoice_date
- else ""
- )
- dr_or_cr = (
- "debit_in_account_currency"
- if erpnext.get_party_account_type(self.party_type) == "Receivable"
- else "credit_in_account_currency"
- )
-
- if self.minimum_invoice_amount:
- condition += " and {dr_or_cr} >= {amount}".format(
- dr_or_cr=dr_or_cr, amount=flt(self.minimum_invoice_amount)
- )
- if self.maximum_invoice_amount:
- condition += " and {dr_or_cr} <= {amount}".format(
- dr_or_cr=dr_or_cr, amount=flt(self.maximum_invoice_amount)
- )
+ if self.from_invoice_date:
+ self.common_filter_conditions.append(ple.posting_date.gte(self.from_invoice_date))
+ if self.to_invoice_date:
+ self.common_filter_conditions.append(ple.posting_date.lte(self.to_invoice_date))
elif get_return_invoices:
- condition = " and doc.company = '{0}' ".format(self.company)
- condition += (
- " and doc.posting_date >= {0}".format(frappe.db.escape(self.from_payment_date))
- if self.from_payment_date
- else ""
- )
- condition += (
- " and doc.posting_date <= {0}".format(frappe.db.escape(self.to_payment_date))
- if self.to_payment_date
- else ""
- )
- dr_or_cr = (
- "debit_in_account_currency"
- if erpnext.get_party_account_type(self.party_type) == "Receivable"
- else "credit_in_account_currency"
- )
+ if self.from_payment_date:
+ self.common_filter_conditions.append(ple.posting_date.gte(self.from_payment_date))
+ if self.to_payment_date:
+ self.common_filter_conditions.append(ple.posting_date.lte(self.to_payment_date))
- if self.minimum_invoice_amount:
- condition += " and gl.{dr_or_cr} >= {amount}".format(
- dr_or_cr=dr_or_cr, amount=flt(self.minimum_payment_amount)
- )
- if self.maximum_invoice_amount:
- condition += " and gl.{dr_or_cr} <= {amount}".format(
- dr_or_cr=dr_or_cr, amount=flt(self.maximum_payment_amount)
- )
+ def get_conditions(self, get_payments=False):
+ condition = " and company = '{0}' ".format(self.company)
- else:
- condition += (
- " and posting_date >= {0}".format(frappe.db.escape(self.from_payment_date))
- if self.from_payment_date
- else ""
- )
- condition += (
- " and posting_date <= {0}".format(frappe.db.escape(self.to_payment_date))
- if self.to_payment_date
- else ""
- )
+ if self.get("cost_center") and get_payments:
+ condition = " and cost_center = '{0}' ".format(self.cost_center)
- if self.minimum_payment_amount:
- condition += (
- " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount))
- if get_payments
- else " and total_debit >= {0}".format(flt(self.minimum_payment_amount))
- )
- if self.maximum_payment_amount:
- condition += (
- " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount))
- if get_payments
- else " and total_debit <= {0}".format(flt(self.maximum_payment_amount))
- )
+ condition += (
+ " and posting_date >= {0}".format(frappe.db.escape(self.from_payment_date))
+ if self.from_payment_date
+ else ""
+ )
+ condition += (
+ " and posting_date <= {0}".format(frappe.db.escape(self.to_payment_date))
+ if self.to_payment_date
+ else ""
+ )
+
+ if self.minimum_payment_amount:
+ condition += (
+ " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount))
+ if get_payments
+ else " and total_debit >= {0}".format(flt(self.minimum_payment_amount))
+ )
+ if self.maximum_payment_amount:
+ condition += (
+ " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount))
+ if get_payments
+ else " and total_debit <= {0}".format(flt(self.maximum_payment_amount))
+ )
return condition
diff --git a/erpnext/accounts/doctype/payment_reconciliation/test_payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/test_payment_reconciliation.py
index d2374b7..575ac74 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/test_payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/test_payment_reconciliation.py
@@ -4,93 +4,453 @@
import unittest
import frappe
-from frappe.utils import add_days, getdate
+from frappe import qb
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days, nowdate
+from erpnext.accounts.doctype.payment_entry.test_payment_entry import create_payment_entry
from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.accounts.party import get_party_account
+from erpnext.stock.doctype.item.test_item import create_item
-class TestPaymentReconciliation(unittest.TestCase):
- @classmethod
- def setUpClass(cls):
- make_customer()
- make_invoice_and_payment()
+class TestPaymentReconciliation(FrappeTestCase):
+ def setUp(self):
+ self.create_company()
+ self.create_item()
+ self.create_customer()
+ self.clear_old_entries()
- def test_payment_reconciliation(self):
- payment_reco = frappe.get_doc("Payment Reconciliation")
- payment_reco.company = "_Test Company"
- payment_reco.party_type = "Customer"
- payment_reco.party = "_Test Payment Reco Customer"
- payment_reco.receivable_payable_account = "Debtors - _TC"
- payment_reco.from_invoice_date = add_days(getdate(), -1)
- payment_reco.to_invoice_date = getdate()
- payment_reco.from_payment_date = add_days(getdate(), -1)
- payment_reco.to_payment_date = getdate()
- payment_reco.maximum_invoice_amount = 1000
- payment_reco.maximum_payment_amount = 1000
- payment_reco.invoice_limit = 10
- payment_reco.payment_limit = 10
- payment_reco.bank_cash_account = "_Test Bank - _TC"
- payment_reco.cost_center = "_Test Cost Center - _TC"
- payment_reco.get_unreconciled_entries()
+ def tearDown(self):
+ frappe.db.rollback()
- self.assertEqual(len(payment_reco.get("invoices")), 1)
- self.assertEqual(len(payment_reco.get("payments")), 1)
+ def create_company(self):
+ company = None
+ if frappe.db.exists("Company", "_Test Payment Reconciliation"):
+ company = frappe.get_doc("Company", "_Test Payment Reconciliation")
+ else:
+ company = frappe.get_doc(
+ {
+ "doctype": "Company",
+ "company_name": "_Test Payment Reconciliation",
+ "country": "India",
+ "default_currency": "INR",
+ "create_chart_of_accounts_based_on": "Standard Template",
+ "chart_of_accounts": "Standard",
+ }
+ )
+ company = company.save()
- payment_entry = payment_reco.get("payments")[0].reference_name
- invoice = payment_reco.get("invoices")[0].invoice_number
+ self.company = company.name
+ self.cost_center = company.cost_center
+ self.warehouse = "All Warehouses - _PR"
+ self.income_account = "Sales - _PR"
+ self.expense_account = "Cost of Goods Sold - _PR"
+ self.debit_to = "Debtors - _PR"
+ self.creditors = "Creditors - _PR"
- payment_reco.allocate_entries(
- {
- "payments": [payment_reco.get("payments")[0].as_dict()],
- "invoices": [payment_reco.get("invoices")[0].as_dict()],
- }
+ # create bank account
+ if frappe.db.exists("Account", "HDFC - _PR"):
+ self.bank = "HDFC - _PR"
+ else:
+ bank_acc = frappe.get_doc(
+ {
+ "doctype": "Account",
+ "account_name": "HDFC",
+ "parent_account": "Bank Accounts - _PR",
+ "company": self.company,
+ }
+ )
+ bank_acc.save()
+ self.bank = bank_acc.name
+
+ def create_item(self):
+ item = create_item(
+ item_code="_Test PR Item", is_stock_item=0, company=self.company, warehouse=self.warehouse
)
- payment_reco.reconcile()
+ self.item = item if isinstance(item, str) else item.item_code
- payment_entry_doc = frappe.get_doc("Payment Entry", payment_entry)
- self.assertEqual(payment_entry_doc.get("references")[0].reference_name, invoice)
+ def create_customer(self):
+ if frappe.db.exists("Customer", "_Test PR Customer"):
+ self.customer = "_Test PR Customer"
+ else:
+ customer = frappe.new_doc("Customer")
+ customer.customer_name = "_Test PR Customer"
+ customer.type = "Individual"
+ customer.save()
+ self.customer = customer.name
+ if frappe.db.exists("Customer", "_Test PR Customer 2"):
+ self.customer2 = "_Test PR Customer 2"
+ else:
+ customer = frappe.new_doc("Customer")
+ customer.customer_name = "_Test PR Customer 2"
+ customer.type = "Individual"
+ customer.save()
+ self.customer2 = customer.name
-def make_customer():
- if not frappe.db.get_value("Customer", "_Test Payment Reco Customer"):
- frappe.get_doc(
- {
- "doctype": "Customer",
- "customer_name": "_Test Payment Reco Customer",
- "customer_type": "Individual",
- "customer_group": "_Test Customer Group",
- "territory": "_Test Territory",
- }
- ).insert()
+ 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 create_payment_entry(self, amount=100, posting_date=nowdate()):
+ """
+ Helper function to populate default values in payment entry
+ """
+ payment = create_payment_entry(
+ company=self.company,
+ payment_type="Receive",
+ party_type="Customer",
+ party=self.customer,
+ paid_from=self.debit_to,
+ paid_to=self.bank,
+ paid_amount=amount,
+ )
+ payment.posting_date = posting_date
+ return payment
-def make_invoice_and_payment():
- si = create_sales_invoice(
- customer="_Test Payment Reco Customer", qty=1, rate=690, do_not_save=True
- )
- si.cost_center = "_Test Cost Center - _TC"
- si.save()
- si.submit()
+ def clear_old_entries(self):
+ doctype_list = [
+ "GL Entry",
+ "Payment Ledger Entry",
+ "Sales Invoice",
+ "Purchase Invoice",
+ "Payment Entry",
+ "Journal Entry",
+ ]
+ for doctype in doctype_list:
+ qb.from_(qb.DocType(doctype)).delete().where(qb.DocType(doctype).company == self.company).run()
- pe = frappe.get_doc(
- {
- "doctype": "Payment Entry",
- "payment_type": "Receive",
- "party_type": "Customer",
- "party": "_Test Payment Reco Customer",
- "company": "_Test Company",
- "paid_from_account_currency": "INR",
- "paid_to_account_currency": "INR",
- "source_exchange_rate": 1,
- "target_exchange_rate": 1,
- "reference_no": "1",
- "reference_date": getdate(),
- "received_amount": 690,
- "paid_amount": 690,
- "paid_from": "Debtors - _TC",
- "paid_to": "_Test Bank - _TC",
- "cost_center": "_Test Cost Center - _TC",
- }
- )
- pe.insert()
- pe.submit()
+ def create_payment_reconciliation(self):
+ pr = frappe.new_doc("Payment Reconciliation")
+ pr.company = self.company
+ pr.party_type = "Customer"
+ pr.party = self.customer
+ pr.receivable_payable_account = get_party_account(pr.party_type, pr.party, pr.company)
+ pr.from_invoice_date = pr.to_invoice_date = pr.from_payment_date = pr.to_payment_date = nowdate()
+ return pr
+
+ def create_journal_entry(
+ self, acc1=None, acc2=None, amount=0, posting_date=None, cost_center=None
+ ):
+ je = frappe.new_doc("Journal Entry")
+ je.posting_date = posting_date or nowdate()
+ je.company = self.company
+ je.user_remark = "test"
+ if not cost_center:
+ cost_center = self.cost_center
+ je.set(
+ "accounts",
+ [
+ {
+ "account": acc1,
+ "cost_center": cost_center,
+ "debit_in_account_currency": amount if amount > 0 else 0,
+ "credit_in_account_currency": abs(amount) if amount < 0 else 0,
+ },
+ {
+ "account": acc2,
+ "cost_center": cost_center,
+ "credit_in_account_currency": amount if amount > 0 else 0,
+ "debit_in_account_currency": abs(amount) if amount < 0 else 0,
+ },
+ ],
+ )
+ return je
+
+ def test_filter_min_max(self):
+ # check filter condition minimum and maximum amount
+ self.create_sales_invoice(qty=1, rate=300)
+ self.create_sales_invoice(qty=1, rate=400)
+ self.create_sales_invoice(qty=1, rate=500)
+ self.create_payment_entry(amount=300).save().submit()
+ self.create_payment_entry(amount=400).save().submit()
+ self.create_payment_entry(amount=500).save().submit()
+
+ pr = self.create_payment_reconciliation()
+ pr.minimum_invoice_amount = 400
+ pr.maximum_invoice_amount = 500
+ pr.minimum_payment_amount = 300
+ pr.maximum_payment_amount = 600
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.get("invoices")), 2)
+ self.assertEqual(len(pr.get("payments")), 3)
+
+ pr.minimum_invoice_amount = 300
+ pr.maximum_invoice_amount = 600
+ pr.minimum_payment_amount = 400
+ pr.maximum_payment_amount = 500
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.get("invoices")), 3)
+ self.assertEqual(len(pr.get("payments")), 2)
+
+ pr.minimum_invoice_amount = (
+ pr.maximum_invoice_amount
+ ) = pr.minimum_payment_amount = pr.maximum_payment_amount = 0
+ pr.get_unreconciled_entries()
+ self.assertEqual(len(pr.get("invoices")), 3)
+ self.assertEqual(len(pr.get("payments")), 3)
+
+ def test_filter_posting_date(self):
+ # check filter condition using transaction date
+ date1 = nowdate()
+ date2 = add_days(nowdate(), -1)
+ amount = 100
+ self.create_sales_invoice(qty=1, rate=amount, posting_date=date1)
+ si2 = self.create_sales_invoice(
+ qty=1, rate=amount, posting_date=date2, do_not_save=True, do_not_submit=True
+ )
+ si2.set_posting_time = 1
+ si2.posting_date = date2
+ si2.save().submit()
+ self.create_payment_entry(amount=amount, posting_date=date1).save().submit()
+ self.create_payment_entry(amount=amount, posting_date=date2).save().submit()
+
+ pr = self.create_payment_reconciliation()
+ pr.from_invoice_date = pr.to_invoice_date = date1
+ pr.from_payment_date = pr.to_payment_date = date1
+
+ pr.get_unreconciled_entries()
+ # assert only si and pe are fetched
+ self.assertEqual(len(pr.get("invoices")), 1)
+ self.assertEqual(len(pr.get("payments")), 1)
+
+ pr.from_invoice_date = date2
+ pr.to_invoice_date = date1
+ pr.from_payment_date = date2
+ pr.to_payment_date = date1
+
+ pr.get_unreconciled_entries()
+ # assert only si and pe are fetched
+ self.assertEqual(len(pr.get("invoices")), 2)
+ self.assertEqual(len(pr.get("payments")), 2)
+
+ def test_filter_invoice_limit(self):
+ # check filter condition - invoice limit
+ transaction_date = nowdate()
+ rate = 100
+ invoices = []
+ payments = []
+ for i in range(5):
+ invoices.append(self.create_sales_invoice(qty=1, rate=rate, posting_date=transaction_date))
+ pe = self.create_payment_entry(amount=rate, posting_date=transaction_date).save().submit()
+ payments.append(pe)
+
+ pr = self.create_payment_reconciliation()
+ pr.from_invoice_date = pr.to_invoice_date = transaction_date
+ pr.from_payment_date = pr.to_payment_date = transaction_date
+ pr.invoice_limit = 2
+ pr.payment_limit = 3
+ pr.get_unreconciled_entries()
+
+ self.assertEqual(len(pr.get("invoices")), 2)
+ self.assertEqual(len(pr.get("payments")), 3)
+
+ def test_payment_against_invoice(self):
+ si = self.create_sales_invoice(qty=1, rate=200)
+ pe = self.create_payment_entry(amount=55).save().submit()
+ # second payment entry
+ self.create_payment_entry(amount=35).save().submit()
+
+ pr = self.create_payment_reconciliation()
+
+ # reconcile multiple payments against invoice
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ si.reload()
+ self.assertEqual(si.status, "Partly Paid")
+ # check PR tool output post reconciliation
+ self.assertEqual(len(pr.get("invoices")), 1)
+ self.assertEqual(pr.get("invoices")[0].get("outstanding_amount"), 110)
+ self.assertEqual(pr.get("payments"), [])
+
+ # cancel one PE
+ pe.reload()
+ pe.cancel()
+ pr.get_unreconciled_entries()
+ # check PR tool output
+ self.assertEqual(len(pr.get("invoices")), 1)
+ self.assertEqual(len(pr.get("payments")), 0)
+ self.assertEqual(pr.get("invoices")[0].get("outstanding_amount"), 165)
+
+ def test_payment_against_journal(self):
+ transaction_date = nowdate()
+
+ sales = "Sales - _PR"
+ amount = 921
+ # debit debtors account to record an invoice
+ je = self.create_journal_entry(self.debit_to, sales, amount, transaction_date)
+ je.accounts[0].party_type = "Customer"
+ je.accounts[0].party = self.customer
+ je.save()
+ je.submit()
+
+ self.create_payment_entry(amount=amount, posting_date=transaction_date).save().submit()
+
+ pr = self.create_payment_reconciliation()
+ pr.minimum_invoice_amount = pr.maximum_invoice_amount = amount
+ pr.from_invoice_date = pr.to_invoice_date = transaction_date
+ pr.from_payment_date = pr.to_payment_date = transaction_date
+
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ # check PR tool output
+ self.assertEqual(len(pr.get("invoices")), 0)
+ self.assertEqual(len(pr.get("payments")), 0)
+
+ def test_journal_against_invoice(self):
+ transaction_date = nowdate()
+ amount = 100
+ si = self.create_sales_invoice(qty=1, rate=amount, posting_date=transaction_date)
+
+ # credit debtors account to record a payment
+ je = self.create_journal_entry(self.bank, self.debit_to, amount, transaction_date)
+ je.accounts[1].party_type = "Customer"
+ je.accounts[1].party = self.customer
+ je.save()
+ je.submit()
+
+ pr = self.create_payment_reconciliation()
+
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ # assert outstanding
+ si.reload()
+ self.assertEqual(si.status, "Paid")
+ self.assertEqual(si.outstanding_amount, 0)
+
+ # check PR tool output
+ self.assertEqual(len(pr.get("invoices")), 0)
+ self.assertEqual(len(pr.get("payments")), 0)
+
+ def test_journal_against_journal(self):
+ transaction_date = nowdate()
+ sales = "Sales - _PR"
+ amount = 100
+
+ # debit debtors account to simulate a invoice
+ je1 = self.create_journal_entry(self.debit_to, sales, amount, transaction_date)
+ je1.accounts[0].party_type = "Customer"
+ je1.accounts[0].party = self.customer
+ je1.save()
+ je1.submit()
+
+ # credit debtors account to simulate a payment
+ je2 = self.create_journal_entry(self.bank, self.debit_to, amount, transaction_date)
+ je2.accounts[1].party_type = "Customer"
+ je2.accounts[1].party = self.customer
+ je2.save()
+ je2.submit()
+
+ pr = self.create_payment_reconciliation()
+
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ self.assertEqual(pr.get("invoices"), [])
+ self.assertEqual(pr.get("payments"), [])
+
+ def test_cr_note_against_invoice(self):
+ transaction_date = nowdate()
+ amount = 100
+
+ si = self.create_sales_invoice(qty=1, rate=amount, posting_date=transaction_date)
+
+ cr_note = self.create_sales_invoice(
+ qty=-1, rate=amount, posting_date=transaction_date, do_not_save=True, do_not_submit=True
+ )
+ cr_note.is_return = 1
+ cr_note = cr_note.save().submit()
+
+ pr = self.create_payment_reconciliation()
+
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ pr.get_unreconciled_entries()
+ # check reconciliation tool output
+ # reconciled invoice and credit note shouldn't show up in selection
+ self.assertEqual(pr.get("invoices"), [])
+ self.assertEqual(pr.get("payments"), [])
+
+ # assert outstanding
+ si.reload()
+ self.assertEqual(si.status, "Paid")
+ self.assertEqual(si.outstanding_amount, 0)
+
+ def test_cr_note_partial_against_invoice(self):
+ transaction_date = nowdate()
+ amount = 100
+ allocated_amount = 80
+
+ si = self.create_sales_invoice(qty=1, rate=amount, posting_date=transaction_date)
+
+ cr_note = self.create_sales_invoice(
+ qty=-1, rate=amount, posting_date=transaction_date, do_not_save=True, do_not_submit=True
+ )
+ cr_note.is_return = 1
+ cr_note = cr_note.save().submit()
+
+ pr = self.create_payment_reconciliation()
+
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.allocation[0].allocated_amount = allocated_amount
+ pr.reconcile()
+
+ # assert outstanding
+ si.reload()
+ self.assertEqual(si.status, "Partly Paid")
+ self.assertEqual(si.outstanding_amount, 20)
+
+ pr.get_unreconciled_entries()
+ # check reconciliation tool output
+ self.assertEqual(len(pr.get("invoices")), 1)
+ self.assertEqual(len(pr.get("payments")), 1)
+ self.assertEqual(pr.get("invoices")[0].outstanding_amount, 20)
+ self.assertEqual(pr.get("payments")[0].amount, 20)
diff --git a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
index 2438f4b..98e0a9b 100644
--- a/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
+++ b/erpnext/accounts/doctype/pricing_rule/pricing_rule.py
@@ -36,8 +36,12 @@
def validate_duplicate_apply_on(self):
if self.apply_on != "Transaction":
- field = apply_on_dict.get(self.apply_on)
- values = [d.get(frappe.scrub(self.apply_on)) for d in self.get(field) if field]
+ apply_on_table = apply_on_dict.get(self.apply_on)
+ if not apply_on_table:
+ return
+
+ apply_on_field = frappe.scrub(self.apply_on)
+ values = [d.get(apply_on_field) for d in self.get(apply_on_table) if d.get(apply_on_field)]
if len(values) != len(set(values)):
frappe.throw(_("Duplicate {0} found in the table").format(self.apply_on))
diff --git a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
index 02ea3b6..d9484f3 100644
--- a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
@@ -1561,6 +1561,26 @@
company.enable_provisional_accounting_for_non_stock_items = 0
company.save()
+ def test_item_less_defaults(self):
+
+ pi = frappe.new_doc("Purchase Invoice")
+ pi.supplier = "_Test Supplier"
+ pi.company = "_Test Company"
+ pi.append(
+ "items",
+ {
+ "item_name": "Opening item",
+ "qty": 1,
+ "uom": "Tonne",
+ "stock_uom": "Kg",
+ "rate": 1000,
+ "expense_account": "Stock Received But Not Billed - _TC",
+ },
+ )
+
+ pi.save()
+ self.assertEqual(pi.items[0].conversion_factor, 1000)
+
def check_gl_entries(doc, voucher_no, expected_gle, posting_date):
gl_entries = frappe.db.sql(
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 387b2cb..7fa2fe2 100644
--- a/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json
+++ b/erpnext/accounts/doctype/purchase_invoice_item/purchase_invoice_item.json
@@ -195,6 +195,7 @@
"label": "Rejected Qty"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -214,6 +215,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "UOM Conversion Factor",
@@ -222,6 +224,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Accepted Qty in Stock UOM",
@@ -874,7 +877,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-06-15 16:02:15.196835",
+ "modified": "2022-06-17 05:31:10.520171",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Purchase Invoice Item",
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 b3ba119..b417c7d 100644
--- a/erpnext/accounts/doctype/sales_invoice_item/sales_invoice_item.json
+++ b/erpnext/accounts/doctype/sales_invoice_item/sales_invoice_item.json
@@ -182,6 +182,7 @@
"oldfieldtype": "Currency"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -200,6 +201,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "UOM Conversion Factor",
@@ -207,6 +209,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Qty as per Stock UOM",
@@ -843,7 +846,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-03-23 08:18:04.928287",
+ "modified": "2022-06-17 05:33:15.335912",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Sales Invoice Item",
diff --git a/erpnext/accounts/doctype/subscription/subscription.py b/erpnext/accounts/doctype/subscription/subscription.py
index 2243b19..9dab4e9 100644
--- a/erpnext/accounts/doctype/subscription/subscription.py
+++ b/erpnext/accounts/doctype/subscription/subscription.py
@@ -145,13 +145,14 @@
You shouldn't need to call this directly. Use `get_billing_cycle` instead.
"""
plan_names = [plan.plan for plan in self.plans]
- billing_info = frappe.db.sql(
- "select distinct `billing_interval`, `billing_interval_count` "
- "from `tabSubscription Plan` "
- "where name in %s",
- (plan_names,),
- as_dict=1,
- )
+
+ subscription_plan = frappe.qb.DocType("Subscription Plan")
+ billing_info = (
+ frappe.qb.from_(subscription_plan)
+ .select(subscription_plan.billing_interval, subscription_plan.billing_interval_count)
+ .distinct()
+ .where(subscription_plan.name.isin(plan_names))
+ ).run(as_dict=1)
return billing_info
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index b0513f1..8146804 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -35,7 +35,13 @@
validate_disabled_accounts(gl_map)
gl_map = process_gl_map(gl_map, merge_entries)
if gl_map and len(gl_map) > 1:
- create_payment_ledger_entry(gl_map)
+ create_payment_ledger_entry(
+ gl_map,
+ cancel=0,
+ adv_adj=adv_adj,
+ update_outstanding=update_outstanding,
+ from_repost=from_repost,
+ )
save_entries(gl_map, adv_adj, update_outstanding, from_repost)
# Post GL Map proccess there may no be any GL Entries
elif gl_map:
@@ -482,6 +488,9 @@
if gl_entries:
create_payment_ledger_entry(gl_entries, cancel=1)
+ create_payment_ledger_entry(
+ gl_entries, cancel=1, adv_adj=adv_adj, update_outstanding=update_outstanding
+ )
validate_accounting_period(gl_entries)
check_freezing_date(gl_entries[0]["posting_date"], adv_adj)
set_as_cancel(gl_entries[0]["voucher_type"], gl_entries[0]["voucher_no"])
diff --git a/erpnext/accounts/module_onboarding/accounts/accounts.json b/erpnext/accounts/module_onboarding/accounts/accounts.json
index b9040e3..9916d16 100644
--- a/erpnext/accounts/module_onboarding/accounts/accounts.json
+++ b/erpnext/accounts/module_onboarding/accounts/accounts.json
@@ -13,7 +13,7 @@
"documentation_url": "https://docs.erpnext.com/docs/user/manual/en/accounts",
"idx": 0,
"is_complete": 0,
- "modified": "2022-06-07 14:29:21.352132",
+ "modified": "2022-06-14 17:38:24.967834",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Accounts",
diff --git a/erpnext/accounts/onboarding_step/setup_taxes/setup_taxes.json b/erpnext/accounts/onboarding_step/setup_taxes/setup_taxes.json
index b6e9f5c..e323f6c 100644
--- a/erpnext/accounts/onboarding_step/setup_taxes/setup_taxes.json
+++ b/erpnext/accounts/onboarding_step/setup_taxes/setup_taxes.json
@@ -2,14 +2,14 @@
"action": "Create Entry",
"action_label": "Manage Sales Tax Templates",
"creation": "2020-05-13 19:29:43.844463",
- "description": "# Setting up Taxes\n\nERPNext lets you configure your taxes so that they are automatically applied in your buying and selling transactions. You can configure them globally or even on Items. ERPNext taxes are pre-configured for most regions.\n\n[Checkout pre-configured taxes](/app/sales-taxes-and-charges-template)\n",
+ "description": "# Setting up Taxes\n\nERPNext lets you configure your taxes so that they are automatically applied in your buying and selling transactions. You can configure them globally or even on Items. ERPNext taxes are pre-configured for most regions.",
"docstatus": 0,
"doctype": "Onboarding Step",
"idx": 0,
"is_complete": 0,
"is_single": 0,
"is_skipped": 0,
- "modified": "2022-06-07 14:27:15.906286",
+ "modified": "2022-06-14 17:37:56.694261",
"modified_by": "Administrator",
"name": "Setup Taxes",
"owner": "Administrator",
diff --git a/erpnext/accounts/report/inactive_sales_items/inactive_sales_items.py b/erpnext/accounts/report/inactive_sales_items/inactive_sales_items.py
index 1a00399..230b18c 100644
--- a/erpnext/accounts/report/inactive_sales_items/inactive_sales_items.py
+++ b/erpnext/accounts/report/inactive_sales_items/inactive_sales_items.py
@@ -100,7 +100,7 @@
sales_data = frappe.db.sql(
"""
select s.territory, s.customer, si.item_group, si.item_code, si.qty, {date_field} as last_order_date,
- DATEDIFF(CURDATE(), {date_field}) as days_since_last_order
+ DATEDIFF(CURRENT_DATE, {date_field}) as days_since_last_order
from `tab{doctype}` s, `tab{doctype} Item` si
where s.name = si.parent and s.docstatus = 1
order by days_since_last_order """.format( # nosec
diff --git a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
index 4eef307..0577214 100644
--- a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
+++ b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
@@ -179,7 +179,7 @@
def get_mode_of_payments(filters):
mode_of_payments = {}
invoice_list = get_invoices(filters)
- invoice_list_names = ",".join('"' + invoice["name"] + '"' for invoice in invoice_list)
+ invoice_list_names = ",".join("'" + invoice["name"] + "'" for invoice in invoice_list)
if invoice_list:
inv_mop = frappe.db.sql(
"""select a.owner,a.posting_date, ifnull(b.mode_of_payment, '') as mode_of_payment
@@ -200,7 +200,7 @@
from `tabJournal Entry` a, `tabJournal Entry Account` b
where a.name = b.parent
and a.docstatus = 1
- and b.reference_type = "Sales Invoice"
+ and b.reference_type = 'Sales Invoice'
and b.reference_name in ({invoice_list_names})
""".format(
invoice_list_names=invoice_list_names
@@ -228,7 +228,7 @@
def get_mode_of_payment_details(filters):
mode_of_payment_details = {}
invoice_list = get_invoices(filters)
- invoice_list_names = ",".join('"' + invoice["name"] + '"' for invoice in invoice_list)
+ invoice_list_names = ",".join("'" + invoice["name"] + "'" for invoice in invoice_list)
if invoice_list:
inv_mop_detail = frappe.db.sql(
"""
@@ -259,7 +259,7 @@
from `tabJournal Entry` a, `tabJournal Entry Account` b
where a.name = b.parent
and a.docstatus = 1
- and b.reference_type = "Sales Invoice"
+ and b.reference_type = 'Sales Invoice'
and b.reference_name in ({invoice_list_names})
group by a.owner, a.posting_date, mode_of_payment
) t
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 2d86dea..8daff9d 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -2,7 +2,6 @@
# License: GNU General Public License v3. See license.txt
-import itertools
from json import loads
from typing import TYPE_CHECKING, List, Optional, Tuple
@@ -10,8 +9,20 @@
import frappe.defaults
from frappe import _, qb, throw
from frappe.model.meta import get_field_precision
+from frappe.query_builder import AliasedQuery, Criterion, Table
+from frappe.query_builder.functions import Sum
from frappe.query_builder.utils import DocType
-from frappe.utils import cint, cstr, flt, formatdate, get_number_format_info, getdate, now, nowdate
+from frappe.utils import (
+ cint,
+ create_batch,
+ cstr,
+ flt,
+ formatdate,
+ get_number_format_info,
+ getdate,
+ now,
+ nowdate,
+)
from pypika import Order
from pypika.terms import ExistsCriterion
@@ -428,7 +439,8 @@
# cancel advance entry
doc = frappe.get_doc(voucher_type, voucher_no)
frappe.flags.ignore_party_validation = True
- doc.make_gl_entries(cancel=1, adv_adj=1)
+ gl_map = doc.build_gl_map()
+ create_payment_ledger_entry(gl_map, cancel=1, adv_adj=1)
for entry in entries:
check_if_advance_entry_modified(entry)
@@ -443,7 +455,9 @@
doc.save(ignore_permissions=True)
# re-submit advance entry
doc = frappe.get_doc(entry.voucher_type, entry.voucher_no)
- doc.make_gl_entries(cancel=0, adv_adj=1)
+ gl_map = doc.build_gl_map()
+ create_payment_ledger_entry(gl_map, cancel=0, adv_adj=1)
+
frappe.flags.ignore_party_validation = False
if entry.voucher_type in ("Payment Entry", "Journal Entry"):
@@ -466,7 +480,7 @@
select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2
where t1.name = t2.parent and t2.account = %(account)s
and t2.party_type = %(party_type)s and t2.party = %(party)s
- and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order"))
+ and (t2.reference_type is null or t2.reference_type in ('', 'Sales Order', 'Purchase Order'))
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.docstatus=1 """.format(
dr_or_cr=args.get("dr_or_cr")
@@ -486,7 +500,7 @@
t1.name = t2.parent and t1.docstatus = 1
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s
- and t2.reference_doctype in ("", "Sales Order", "Purchase Order")
+ and t2.reference_doctype in ('', 'Sales Order', 'Purchase Order')
and t2.allocated_amount = %(unreconciled_amount)s
""".format(
party_account_field
@@ -807,7 +821,11 @@
return held_invoices
-def get_outstanding_invoices(party_type, party, account, condition=None, filters=None):
+def get_outstanding_invoices(
+ party_type, party, account, common_filter=None, min_outstanding=None, max_outstanding=None
+):
+
+ ple = qb.DocType("Payment Ledger Entry")
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2
@@ -820,76 +838,30 @@
else:
party_account_type = erpnext.get_party_account_type(party_type)
- if party_account_type == "Receivable":
- dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
- payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
- else:
- dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
- payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
-
held_invoices = get_held_invoices(party_type, party)
- invoice_list = frappe.db.sql(
- """
- select
- voucher_no, voucher_type, posting_date, due_date,
- ifnull(sum({dr_or_cr}), 0) as invoice_amount,
- account_currency as currency
- from
- `tabGL Entry`
- where
- party_type = %(party_type)s and party = %(party)s
- and account = %(account)s and {dr_or_cr} > 0
- and is_cancelled=0
- {condition}
- and ((voucher_type = 'Journal Entry'
- and (against_voucher = '' or against_voucher is null))
- or (voucher_type not in ('Journal Entry', 'Payment Entry')))
- group by voucher_type, voucher_no
- order by posting_date, name""".format(
- dr_or_cr=dr_or_cr, condition=condition or ""
- ),
- {
- "party_type": party_type,
- "party": party,
- "account": account,
- },
- as_dict=True,
- )
+ common_filter = common_filter or []
+ common_filter.append(ple.account_type == party_account_type)
+ common_filter.append(ple.account == account)
+ common_filter.append(ple.party_type == party_type)
+ common_filter.append(ple.party == party)
- payment_entries = frappe.db.sql(
- """
- select against_voucher_type, against_voucher,
- ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
- from `tabGL Entry`
- where party_type = %(party_type)s and party = %(party)s
- and account = %(account)s
- and {payment_dr_or_cr} > 0
- and against_voucher is not null and against_voucher != ''
- and is_cancelled=0
- group by against_voucher_type, against_voucher
- """.format(
- payment_dr_or_cr=payment_dr_or_cr
- ),
- {"party_type": party_type, "party": party, "account": account},
- as_dict=True,
+ ple_query = QueryPaymentLedger()
+ invoice_list = ple_query.get_voucher_outstandings(
+ common_filter=common_filter,
+ min_outstanding=min_outstanding,
+ max_outstanding=max_outstanding,
+ get_invoices=True,
)
- pe_map = frappe._dict()
- for d in payment_entries:
- pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount)
-
for d in invoice_list:
- payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
- outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
+ payment_amount = d.invoice_amount - d.outstanding
+ outstanding_amount = d.outstanding
if outstanding_amount > 0.5 / (10**precision):
if (
- filters
- and filters.get("outstanding_amt_greater_than")
- and not (
- outstanding_amount >= filters.get("outstanding_amt_greater_than")
- and outstanding_amount <= filters.get("outstanding_amt_less_than")
- )
+ min_outstanding
+ and max_outstanding
+ and not (outstanding_amount >= min_outstanding and outstanding_amount <= max_outstanding)
):
continue
@@ -1149,9 +1121,7 @@
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit")) or 2
- stock_vouchers_iterator = iter(stock_vouchers)
-
- while stock_vouchers_chunk := list(itertools.islice(stock_vouchers_iterator, GL_REPOSTING_CHUNK)):
+ for stock_vouchers_chunk in create_batch(stock_vouchers, GL_REPOSTING_CHUNK):
gle = get_voucherwise_gl_entries(stock_vouchers_chunk, posting_date)
for voucher_type, voucher_no in stock_vouchers_chunk:
@@ -1168,12 +1138,14 @@
voucher_obj.make_gl_entries(gl_entries=expected_gle, from_repost=True)
else:
_delete_gl_entries(voucher_type, voucher_no)
- frappe.db.commit()
+
+ if not frappe.flags.in_test:
+ frappe.db.commit()
if repost_doc:
repost_doc.db_set(
"gl_reposting_index",
- cint(repost_doc.gl_reposting_index) + GL_REPOSTING_CHUNK,
+ cint(repost_doc.gl_reposting_index) + len(stock_vouchers_chunk),
)
@@ -1380,7 +1352,9 @@
frappe.delete_doc("Desktop Icon", icon)
-def create_payment_ledger_entry(gl_entries, cancel=0):
+def create_payment_ledger_entry(
+ gl_entries, cancel=0, adv_adj=0, update_outstanding="Yes", from_repost=0
+):
if gl_entries:
ple = None
@@ -1453,9 +1427,42 @@
if cancel:
delink_original_entry(ple)
ple.flags.ignore_permissions = 1
+ ple.flags.adv_adj = adv_adj
+ ple.flags.from_repost = from_repost
+ ple.flags.update_outstanding = update_outstanding
ple.submit()
+def update_voucher_outstanding(voucher_type, voucher_no, account, party_type, party):
+ ple = frappe.qb.DocType("Payment Ledger Entry")
+ vouchers = [frappe._dict({"voucher_type": voucher_type, "voucher_no": voucher_no})]
+ common_filter = []
+ if account:
+ common_filter.append(ple.account == account)
+
+ if party_type:
+ common_filter.append(ple.party_type == party_type)
+
+ if party:
+ common_filter.append(ple.party == party)
+
+ ple_query = QueryPaymentLedger()
+
+ # on cancellation outstanding can be an empty list
+ voucher_outstanding = ple_query.get_voucher_outstandings(vouchers, common_filter=common_filter)
+ if voucher_type in ["Sales Invoice", "Purchase Invoice", "Fees"] and voucher_outstanding:
+ outstanding = voucher_outstanding[0]
+ ref_doc = frappe.get_doc(voucher_type, voucher_no)
+
+ # Didn't use db_set for optimisation purpose
+ ref_doc.outstanding_amount = outstanding["outstanding_in_account_currency"]
+ frappe.db.set_value(
+ voucher_type, voucher_no, "outstanding_amount", outstanding["outstanding_in_account_currency"]
+ )
+
+ ref_doc.set_status(update=True)
+
+
def delink_original_entry(pl_entry):
if pl_entry:
ple = qb.DocType("Payment Ledger Entry")
@@ -1477,3 +1484,196 @@
)
)
query.run()
+
+
+class QueryPaymentLedger(object):
+ """
+ Helper Class for Querying Payment Ledger Entry
+ """
+
+ def __init__(self):
+ self.ple = qb.DocType("Payment Ledger Entry")
+
+ # query result
+ self.voucher_outstandings = []
+
+ # query filters
+ self.vouchers = []
+ self.common_filter = []
+ self.min_outstanding = None
+ self.max_outstanding = None
+
+ def reset(self):
+ # clear filters
+ self.vouchers.clear()
+ self.common_filter.clear()
+ self.min_outstanding = self.max_outstanding = None
+
+ # clear result
+ self.voucher_outstandings.clear()
+
+ def query_for_outstanding(self):
+ """
+ Database query to fetch voucher amount and voucher outstanding using Common Table Expression
+ """
+
+ ple = self.ple
+
+ filter_on_voucher_no = []
+ filter_on_against_voucher_no = []
+ if self.vouchers:
+ voucher_types = set([x.voucher_type for x in self.vouchers])
+ voucher_nos = set([x.voucher_no for x in self.vouchers])
+
+ filter_on_voucher_no.append(ple.voucher_type.isin(voucher_types))
+ filter_on_voucher_no.append(ple.voucher_no.isin(voucher_nos))
+
+ filter_on_against_voucher_no.append(ple.against_voucher_type.isin(voucher_types))
+ filter_on_against_voucher_no.append(ple.against_voucher_no.isin(voucher_nos))
+
+ # build outstanding amount filter
+ filter_on_outstanding_amount = []
+ if self.min_outstanding:
+ if self.min_outstanding > 0:
+ filter_on_outstanding_amount.append(
+ Table("outstanding").amount_in_account_currency >= self.min_outstanding
+ )
+ else:
+ filter_on_outstanding_amount.append(
+ Table("outstanding").amount_in_account_currency <= self.min_outstanding
+ )
+ if self.max_outstanding:
+ if self.max_outstanding > 0:
+ filter_on_outstanding_amount.append(
+ Table("outstanding").amount_in_account_currency <= self.max_outstanding
+ )
+ else:
+ filter_on_outstanding_amount.append(
+ Table("outstanding").amount_in_account_currency >= self.max_outstanding
+ )
+
+ # build query for voucher amount
+ query_voucher_amount = (
+ qb.from_(ple)
+ .select(
+ ple.account,
+ ple.voucher_type,
+ ple.voucher_no,
+ ple.party_type,
+ ple.party,
+ ple.posting_date,
+ ple.due_date,
+ ple.account_currency.as_("currency"),
+ Sum(ple.amount).as_("amount"),
+ Sum(ple.amount_in_account_currency).as_("amount_in_account_currency"),
+ )
+ .where(ple.delinked == 0)
+ .where(Criterion.all(filter_on_voucher_no))
+ .where(Criterion.all(self.common_filter))
+ .groupby(ple.voucher_type, ple.voucher_no, ple.party_type, ple.party)
+ )
+
+ # build query for voucher outstanding
+ query_voucher_outstanding = (
+ qb.from_(ple)
+ .select(
+ ple.account,
+ ple.against_voucher_type.as_("voucher_type"),
+ ple.against_voucher_no.as_("voucher_no"),
+ ple.party_type,
+ ple.party,
+ ple.posting_date,
+ ple.due_date,
+ ple.account_currency.as_("currency"),
+ Sum(ple.amount).as_("amount"),
+ Sum(ple.amount_in_account_currency).as_("amount_in_account_currency"),
+ )
+ .where(ple.delinked == 0)
+ .where(Criterion.all(filter_on_against_voucher_no))
+ .where(Criterion.all(self.common_filter))
+ .groupby(ple.against_voucher_type, ple.against_voucher_no, ple.party_type, ple.party)
+ )
+
+ # build CTE for combining voucher amount and outstanding
+ self.cte_query_voucher_amount_and_outstanding = (
+ qb.with_(query_voucher_amount, "vouchers")
+ .with_(query_voucher_outstanding, "outstanding")
+ .from_(AliasedQuery("vouchers"))
+ .left_join(AliasedQuery("outstanding"))
+ .on(
+ (AliasedQuery("vouchers").account == AliasedQuery("outstanding").account)
+ & (AliasedQuery("vouchers").voucher_type == AliasedQuery("outstanding").voucher_type)
+ & (AliasedQuery("vouchers").voucher_no == AliasedQuery("outstanding").voucher_no)
+ & (AliasedQuery("vouchers").party_type == AliasedQuery("outstanding").party_type)
+ & (AliasedQuery("vouchers").party == AliasedQuery("outstanding").party)
+ )
+ .select(
+ Table("vouchers").account,
+ Table("vouchers").voucher_type,
+ Table("vouchers").voucher_no,
+ Table("vouchers").party_type,
+ Table("vouchers").party,
+ Table("vouchers").posting_date,
+ Table("vouchers").amount.as_("invoice_amount"),
+ Table("vouchers").amount_in_account_currency.as_("invoice_amount_in_account_currency"),
+ Table("outstanding").amount.as_("outstanding"),
+ Table("outstanding").amount_in_account_currency.as_("outstanding_in_account_currency"),
+ (Table("vouchers").amount - Table("outstanding").amount).as_("paid_amount"),
+ (
+ Table("vouchers").amount_in_account_currency - Table("outstanding").amount_in_account_currency
+ ).as_("paid_amount_in_account_currency"),
+ Table("vouchers").due_date,
+ Table("vouchers").currency,
+ )
+ .where(Criterion.all(filter_on_outstanding_amount))
+ )
+
+ # build CTE filter
+ # only fetch invoices
+ if self.get_invoices:
+ self.cte_query_voucher_amount_and_outstanding = (
+ self.cte_query_voucher_amount_and_outstanding.having(
+ qb.Field("outstanding_in_account_currency") > 0
+ )
+ )
+ # only fetch payments
+ elif self.get_payments:
+ self.cte_query_voucher_amount_and_outstanding = (
+ self.cte_query_voucher_amount_and_outstanding.having(
+ qb.Field("outstanding_in_account_currency") < 0
+ )
+ )
+
+ # execute SQL
+ self.voucher_outstandings = self.cte_query_voucher_amount_and_outstanding.run(as_dict=True)
+
+ def get_voucher_outstandings(
+ self,
+ vouchers=None,
+ common_filter=None,
+ min_outstanding=None,
+ max_outstanding=None,
+ get_payments=False,
+ get_invoices=False,
+ ):
+ """
+ Fetch voucher amount and outstanding amount from Payment Ledger using Database CTE
+
+ vouchers - dict of vouchers to get
+ common_filter - array of criterions
+ min_outstanding - filter on minimum total outstanding amount
+ max_outstanding - filter on maximum total outstanding amount
+ get_invoices - only fetch vouchers(ledger entries with +ve outstanding)
+ get_payments - only fetch payments(ledger entries with -ve outstanding)
+ """
+
+ self.reset()
+ self.vouchers = vouchers
+ self.common_filter = common_filter or []
+ self.min_outstanding = min_outstanding
+ self.max_outstanding = max_outstanding
+ self.get_payments = get_payments
+ self.get_invoices = get_invoices
+ self.query_for_outstanding()
+
+ return self.voucher_outstandings
diff --git a/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py b/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
index e603d34..0028d84 100644
--- a/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
+++ b/erpnext/assets/doctype/asset_maintenance/asset_maintenance.py
@@ -47,17 +47,19 @@
team_member = frappe.db.get_value("User", assign_to_member, "email")
args = {
"doctype": "Asset Maintenance",
- "assign_to": [team_member],
+ "assign_to": team_member,
"name": asset_maintenance_name,
"description": maintenance_task,
"date": next_due_date,
}
if not frappe.db.sql(
"""select owner from `tabToDo`
- where reference_type=%(doctype)s and reference_name=%(name)s and status="Open"
+ where reference_type=%(doctype)s and reference_name=%(name)s and status='Open'
and owner=%(assign_to)s""",
args,
):
+ # assign_to function expects a list
+ args["assign_to"] = [args["assign_to"]]
assign_to.add(args)
diff --git a/erpnext/buying/doctype/purchase_order/test_purchase_order.py b/erpnext/buying/doctype/purchase_order/test_purchase_order.py
index 0bebafe..bd7e4e8 100644
--- a/erpnext/buying/doctype/purchase_order/test_purchase_order.py
+++ b/erpnext/buying/doctype/purchase_order/test_purchase_order.py
@@ -326,7 +326,7 @@
else:
# update valid from
frappe.db.sql(
- """UPDATE `tabItem Tax` set valid_from = CURDATE()
+ """UPDATE `tabItem Tax` set valid_from = CURRENT_DATE
where parent = %(item)s and item_tax_template = %(tax)s""",
{"item": item, "tax": tax_template},
)
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 4794104..1a98453 100644
--- a/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json
+++ b/erpnext/buying/doctype/purchase_order_item/purchase_order_item.json
@@ -215,6 +215,7 @@
"width": "60px"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -244,6 +245,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "UOM Conversion Factor",
@@ -597,6 +599,7 @@
"label": "Billed, Received & Returned"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Qty in Stock UOM",
@@ -871,7 +874,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-06-16 06:00:01.624317",
+ "modified": "2022-06-17 05:29:40.602349",
"modified_by": "Administrator",
"module": "Buying",
"name": "Purchase Order Item",
diff --git a/erpnext/buying/report/procurement_tracker/procurement_tracker.py b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
index e0b02ee..d70ac46 100644
--- a/erpnext/buying/report/procurement_tracker/procurement_tracker.py
+++ b/erpnext/buying/report/procurement_tracker/procurement_tracker.py
@@ -252,7 +252,7 @@
ON pi_item.`purchase_order` = po.`name`
WHERE
pi_item.docstatus = 1
- AND po.status not in ("Closed","Completed","Cancelled")
+ AND po.status not in ('Closed','Completed','Cancelled')
AND pi_item.po_detail IS NOT NULL
"""
)
@@ -271,7 +271,7 @@
pr.docstatus=1
AND pr.name=pr_item.parent
AND pr_item.purchase_order_item IS NOT NULL
- AND pr.status not in ("Closed","Completed","Cancelled")
+ AND pr.status not in ('Closed','Completed','Cancelled')
"""
)
)
@@ -302,7 +302,7 @@
WHERE
parent.docstatus = 1
AND parent.name = child.parent
- AND parent.status not in ("Closed","Completed","Cancelled")
+ AND parent.status not in ('Closed','Completed','Cancelled')
{conditions}
GROUP BY
parent.name, child.item_code
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index e83e0c2..bbeeed4 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -46,6 +46,7 @@
from erpnext.controllers.sales_and_purchase_return import validate_return
from erpnext.exceptions import InvalidCurrency
from erpnext.setup.utils import get_exchange_rate
+from erpnext.stock.doctype.item.item import get_uom_conv_factor
from erpnext.stock.doctype.packed_item.packed_item import make_packing_list
from erpnext.stock.get_item_details import (
_get_item_tax_template,
@@ -548,6 +549,15 @@
if ret.get("pricing_rules"):
self.apply_pricing_rule_on_items(item, ret)
self.set_pricing_rule_details(item, ret)
+ else:
+ # Transactions line item without item code
+
+ uom = item.get("uom")
+ stock_uom = item.get("stock_uom")
+ if bool(uom) != bool(stock_uom): # xor
+ item.stock_uom = item.uom = uom or stock_uom
+
+ item.conversion_factor = get_uom_conv_factor(item.get("uom"), item.get("stock_uom"))
if self.doctype == "Purchase Invoice":
self.set_expense_account(for_validate)
@@ -2039,7 +2049,7 @@
journal_entries = frappe.db.sql(
"""
select
- "Journal Entry" as reference_type, t1.name as reference_name,
+ 'Journal Entry' as reference_type, t1.name as reference_name,
t1.remark as remarks, t2.{0} as amount, t2.name as reference_row,
t2.reference_name as against_order, t2.exchange_rate
from
@@ -2094,7 +2104,7 @@
payment_entries_against_order = frappe.db.sql(
"""
select
- "Payment Entry" as reference_type, t1.name as reference_name,
+ 'Payment Entry' as reference_type, t1.name as reference_name,
t1.remarks, t2.allocated_amount as amount, t2.name as reference_row,
t2.reference_name as against_order, t1.posting_date,
t1.{0} as currency, t1.{4} as exchange_rate
@@ -2114,7 +2124,7 @@
if include_unallocated:
unallocated_payment_entries = frappe.db.sql(
"""
- select "Payment Entry" as reference_type, name as reference_name, posting_date,
+ select 'Payment Entry' as reference_type, name as reference_name, posting_date,
remarks, unallocated_amount as amount, {2} as exchange_rate, {3} as currency
from `tabPayment Entry`
where
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 1497b18..243ebb6 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -29,8 +29,8 @@
or employee_name like %(txt)s)
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, employee_name) > 0 then locate(%(_txt)s, employee_name) else 99999 end),
idx desc,
name, employee_name
limit %(page_len)s offset %(start)s""".format(
@@ -60,9 +60,9 @@
or company_name like %(txt)s)
{mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, lead_name), locate(%(_txt)s, lead_name), 99999),
- if(locate(%(_txt)s, company_name), locate(%(_txt)s, company_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, lead_name) > 0 then locate(%(_txt)s, lead_name) else 99999 end),
+ (case when locate(%(_txt)s, company_name) > 0 then locate(%(_txt)s, company_name) else 99999 end),
idx desc,
name, lead_name
limit %(page_len)s offset %(start)s""".format(
@@ -96,8 +96,8 @@
and ({scond}) and disabled=0
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, customer_name) > 0 then locate(%(_txt)s, customer_name) else 99999 end),
idx desc,
name, customer_name
limit %(page_len)s offset %(start)s""".format(
@@ -130,11 +130,11 @@
where docstatus < 2
and ({key} like %(txt)s
or supplier_name like %(txt)s) and disabled=0
- and (on_hold = 0 or (on_hold = 1 and CURDATE() > release_date))
+ and (on_hold = 0 or (on_hold = 1 and CURRENT_DATE > release_date))
{mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, supplier_name), locate(%(_txt)s, supplier_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, supplier_name) > 0 then locate(%(_txt)s, supplier_name) else 99999 end),
idx desc,
name, supplier_name
limit %(page_len)s offset %(start)s""".format(
@@ -305,15 +305,15 @@
return frappe.db.sql(
"""select {fields}
- from tabBOM
- where tabBOM.docstatus=1
- and tabBOM.is_active=1
- and tabBOM.`{key}` like %(txt)s
+ from `tabBOM`
+ where `tabBOM`.docstatus=1
+ and `tabBOM`.is_active=1
+ and `tabBOM`.`{key}` like %(txt)s
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
idx desc, name
- limit %(start)s, %(page_len)s """.format(
+ limit %(page_len)s offset %(start)s""".format(
fields=", ".join(fields),
fcond=get_filters_cond(doctype, filters, conditions).replace("%", "%%"),
mcond=get_match_cond(doctype).replace("%", "%%"),
@@ -340,16 +340,16 @@
fields = get_fields("Project", ["name", "project_name"])
searchfields = frappe.get_meta("Project").get_search_fields()
- searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
+ searchfields = " or ".join(["`tabProject`." + field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
"""select {fields} from `tabProject`
where
- `tabProject`.status not in ("Completed", "Cancelled")
+ `tabProject`.status not in ('Completed', 'Cancelled')
and {cond} {scond} {match_cond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- idx desc,
+ (case when locate(%(_txt)s, `tabProject`.name) > 0 then locate(%(_txt)s, `tabProject`.name) else 99999 end),
+ `tabProject`.idx desc,
`tabProject`.name asc
limit {page_len} offset {start}""".format(
fields=", ".join(["`tabProject`.{0}".format(f) for f in fields]),
@@ -374,7 +374,7 @@
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1
- and status not in ("Stopped", "Closed") %(fcond)s
+ and status not in ('Stopped', 'Closed') %(fcond)s
and (
(`tabDelivery Note`.is_return = 0 and `tabDelivery Note`.per_billed < 100)
or (`tabDelivery Note`.grand_total = 0 and `tabDelivery Note`.per_billed < 100)
@@ -654,7 +654,7 @@
filter_dict = get_doctype_wise_filters(filters)
query = """select `tabWarehouse`.name,
- CONCAT_WS(" : ", "Actual Qty", ifnull(round(`tabBin`.actual_qty, 2), 0 )) actual_qty
+ CONCAT_WS(' : ', 'Actual Qty', ifnull(round(`tabBin`.actual_qty, 2), 0 )) actual_qty
from `tabWarehouse` left join `tabBin`
on `tabBin`.warehouse = `tabWarehouse`.name {bin_conditions}
where
@@ -691,7 +691,7 @@
def get_batch_numbers(doctype, txt, searchfield, start, page_len, filters):
query = """select batch_id from `tabBatch`
where disabled = 0
- and (expiry_date >= CURDATE() or expiry_date IS NULL)
+ and (expiry_date >= CURRENT_DATE or expiry_date IS NULL)
and name like {txt}""".format(
txt=frappe.db.escape("%{0}%".format(txt))
)
diff --git a/erpnext/controllers/status_updater.py b/erpnext/controllers/status_updater.py
index 517e080..197d2ba 100644
--- a/erpnext/controllers/status_updater.py
+++ b/erpnext/controllers/status_updater.py
@@ -352,9 +352,9 @@
for args in self.status_updater:
# condition to include current record (if submit or no if cancel)
if self.docstatus == 1:
- args["cond"] = ' or parent="%s"' % self.name.replace('"', '"')
+ args["cond"] = " or parent='%s'" % self.name.replace('"', '"')
else:
- args["cond"] = ' and parent!="%s"' % self.name.replace('"', '"')
+ args["cond"] = " and parent!='%s'" % self.name.replace('"', '"')
self._update_children(args, update_modified)
@@ -384,7 +384,7 @@
args["second_source_condition"] = frappe.db.sql(
""" select ifnull((select sum(%(second_source_field)s)
from `tab%(second_source_dt)s`
- where `%(second_join_field)s`="%(detail_id)s"
+ where `%(second_join_field)s`='%(detail_id)s'
and (`tab%(second_source_dt)s`.docstatus=1)
%(second_source_extra_cond)s), 0) """
% args
@@ -398,7 +398,7 @@
frappe.db.sql(
"""
(select ifnull(sum(%(source_field)s), 0)
- from `tab%(source_dt)s` where `%(join_field)s`="%(detail_id)s"
+ from `tab%(source_dt)s` where `%(join_field)s`='%(detail_id)s'
and (docstatus=1 %(cond)s) %(extra_cond)s)
"""
% args
@@ -443,9 +443,9 @@
"""update `tab%(target_parent_dt)s`
set %(target_parent_field)s = round(
ifnull((select
- ifnull(sum(if(abs(%(target_ref_field)s) > abs(%(target_field)s), abs(%(target_field)s), abs(%(target_ref_field)s))), 0)
+ ifnull(sum(case when abs(%(target_ref_field)s) > abs(%(target_field)s) then abs(%(target_field)s) else abs(%(target_ref_field)s) end), 0)
/ sum(abs(%(target_ref_field)s)) * 100
- from `tab%(target_dt)s` where parent="%(name)s" having sum(abs(%(target_ref_field)s)) > 0), 0), 6)
+ from `tab%(target_dt)s` where parent='%(name)s' having sum(abs(%(target_ref_field)s)) > 0), 0), 6)
%(update_modified)s
where name='%(name)s'"""
% args
@@ -455,9 +455,9 @@
if args.get("status_field"):
frappe.db.sql(
"""update `tab%(target_parent_dt)s`
- set %(status_field)s = if(%(target_parent_field)s<0.001,
- 'Not %(keyword)s', if(%(target_parent_field)s>=99.999999,
- 'Fully %(keyword)s', 'Partly %(keyword)s'))
+ set %(status_field)s = (case when %(target_parent_field)s<0.001 then 'Not %(keyword)s'
+ else case when %(target_parent_field)s>=99.999999 then 'Fully %(keyword)s'
+ else 'Partly %(keyword)s' end end)
where name='%(name)s'"""
% args
)
diff --git a/erpnext/crm/doctype/opportunity/opportunity.py b/erpnext/crm/doctype/opportunity/opportunity.py
index b590177..c70a4f6 100644
--- a/erpnext/crm/doctype/opportunity/opportunity.py
+++ b/erpnext/crm/doctype/opportunity/opportunity.py
@@ -8,7 +8,8 @@
from frappe import _
from frappe.email.inbox import link_communication_to_document
from frappe.model.mapper import get_mapped_doc
-from frappe.query_builder import DocType
+from frappe.query_builder import DocType, Interval
+from frappe.query_builder.functions import Now
from frappe.utils import cint, flt, get_fullname
from erpnext.crm.utils import add_link_in_communication, copy_comments
@@ -398,15 +399,17 @@
frappe.db.get_single_value("CRM Settings", "close_opportunity_after_days") or 15
)
- opportunities = frappe.db.sql(
- """ select name from tabOpportunity where status='Replied' and
- modified<DATE_SUB(CURDATE(), INTERVAL %s DAY) """,
- (auto_close_after_days),
- as_dict=True,
- )
+ table = frappe.qb.DocType("Opportunity")
+ opportunities = (
+ frappe.qb.from_(table)
+ .select(table.name)
+ .where(
+ (table.modified < (Now() - Interval(days=auto_close_after_days))) & (table.status == "Replied")
+ )
+ ).run(pluck=True)
for opportunity in opportunities:
- doc = frappe.get_doc("Opportunity", opportunity.get("name"))
+ doc = frappe.get_doc("Opportunity", opportunity)
doc.status = "Closed"
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
diff --git a/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py b/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
index 17e332c..b526624 100644
--- a/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
+++ b/erpnext/erpnext_integrations/doctype/mpesa_settings/test_mpesa_settings.py
@@ -23,7 +23,7 @@
def tearDown(self):
frappe.db.sql("delete from `tabMpesa Settings`")
- frappe.db.sql('delete from `tabIntegration Request` where integration_request_service = "Mpesa"')
+ frappe.db.sql("delete from `tabIntegration Request` where integration_request_service = 'Mpesa'")
def test_creation_of_payment_gateway(self):
mode_of_payment = create_mode_of_payment("Mpesa-_Test", payment_type="Phone")
diff --git a/erpnext/hr/doctype/employee_advance/test_employee_advance.py b/erpnext/hr/doctype/employee_advance/test_employee_advance.py
index 44d68c9..81a0876 100644
--- a/erpnext/hr/doctype/employee_advance/test_employee_advance.py
+++ b/erpnext/hr/doctype/employee_advance/test_employee_advance.py
@@ -216,7 +216,7 @@
def make_employee_advance(employee_name, args=None):
doc = frappe.new_doc("Employee Advance")
doc.employee = employee_name
- doc.company = "_Test company"
+ doc.company = "_Test Company"
doc.purpose = "For site visit"
doc.currency = erpnext.get_company_currency("_Test company")
doc.exchange_rate = 1
diff --git a/erpnext/hr/doctype/exit_interview/exit_interview.py b/erpnext/hr/doctype/exit_interview/exit_interview.py
index 8317310..ce4355b 100644
--- a/erpnext/hr/doctype/exit_interview/exit_interview.py
+++ b/erpnext/hr/doctype/exit_interview/exit_interview.py
@@ -88,7 +88,7 @@
reference_doctype=interview.doctype,
reference_name=interview.name,
)
- interview.db_set("questionnaire_email_sent", True)
+ interview.db_set("questionnaire_email_sent", 1)
interview.notify_update()
email_success.append(email)
else:
diff --git a/erpnext/hr/doctype/job_offer/test_job_offer.py b/erpnext/hr/doctype/job_offer/test_job_offer.py
index 7d8ef11..9c4cb36 100644
--- a/erpnext/hr/doctype/job_offer/test_job_offer.py
+++ b/erpnext/hr/doctype/job_offer/test_job_offer.py
@@ -49,7 +49,7 @@
frappe.db.set_value("HR Settings", None, "check_vacancies", 1)
def tearDown(self):
- frappe.db.sql("DELETE FROM `tabJob Offer` WHERE 1")
+ frappe.db.sql("DELETE FROM `tabJob Offer`")
def create_job_offer(**args):
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index 43c2bb3..d49d1bd 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -399,7 +399,7 @@
select
name, leave_type, posting_date, from_date, to_date, total_leave_days, half_day_date
from `tabLeave Application`
- where employee = %(employee)s and docstatus < 2 and status in ("Open", "Approved")
+ where employee = %(employee)s and docstatus < 2 and status in ('Open', 'Approved')
and to_date >= %(from_date)s and from_date <= %(to_date)s
and name != %(name)s""",
{
@@ -439,7 +439,7 @@
"""select count(name) from `tabLeave Application`
where employee = %(employee)s
and docstatus < 2
- and status in ("Open", "Approved")
+ and status in ('Open', 'Approved')
and half_day = 1
and half_day_date = %(half_day_date)s
and name != %(name)s""",
@@ -456,7 +456,7 @@
def validate_attendance(self):
attendance = frappe.db.sql(
"""select name from `tabAttendance` where employee = %s and (attendance_date between %s and %s)
- and status = "Present" and docstatus = 1""",
+ and status = 'Present' and docstatus = 1""",
(self.employee, self.from_date, self.to_date),
)
if attendance:
diff --git a/erpnext/hr/doctype/leave_application/test_leave_application.py b/erpnext/hr/doctype/leave_application/test_leave_application.py
index 27c5410..1b9505e 100644
--- a/erpnext/hr/doctype/leave_application/test_leave_application.py
+++ b/erpnext/hr/doctype/leave_application/test_leave_application.py
@@ -108,7 +108,7 @@
def _clear_roles(self):
frappe.db.sql(
"""delete from `tabHas Role` where parent in
- ("test@example.com", "test1@example.com", "test2@example.com")"""
+ ('test@example.com', 'test1@example.com', 'test2@example.com')"""
)
def _clear_applications(self):
diff --git a/erpnext/hr/report/employee_exits/employee_exits.py b/erpnext/hr/report/employee_exits/employee_exits.py
index 9cd9ff0..80b9ec1 100644
--- a/erpnext/hr/report/employee_exits/employee_exits.py
+++ b/erpnext/hr/report/employee_exits/employee_exits.py
@@ -5,6 +5,7 @@
from frappe import _
from frappe.query_builder import Order
from frappe.utils import getdate
+from pypika import functions as fn
def execute(filters=None):
@@ -110,7 +111,7 @@
)
.distinct()
.where(
- ((employee.relieving_date.isnotnull()) | (employee.relieving_date != ""))
+ (fn.Coalesce(fn.Cast(employee.relieving_date, "char"), "") != "")
& ((interview.name.isnull()) | ((interview.name.isnotnull()) & (interview.docstatus != 2)))
& ((fnf.name.isnull()) | ((fnf.name.isnotnull()) & (fnf.docstatus != 2)))
)
diff --git a/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py b/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
index da6dace..e546810 100644
--- a/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
+++ b/erpnext/hr/report/vehicle_expenses/test_vehicle_expenses.py
@@ -20,7 +20,7 @@
frappe.db.sql("delete from `tabVehicle Log`")
employee_id = frappe.db.sql(
- '''select name from `tabEmployee` where name="testdriver@example.com"'''
+ """select name from `tabEmployee` where name='testdriver@example.com' """
)
self.employee_id = employee_id[0][0] if employee_id else None
if not self.employee_id:
diff --git a/erpnext/hr/utils.py b/erpnext/hr/utils.py
index 3f4e31b..db69147 100644
--- a/erpnext/hr/utils.py
+++ b/erpnext/hr/utils.py
@@ -458,7 +458,7 @@
def get_sal_slip_total_benefit_given(employee, payroll_period, component=False):
total_given_benefit_amount = 0
query = """
- select sum(sd.amount) as 'total_amount'
+ select sum(sd.amount) as total_amount
from `tabSalary Slip` ss, `tabSalary Detail` sd
where ss.employee=%(employee)s
and ss.docstatus = 1 and ss.name = sd.parent
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index 631548b..4c88eca 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -1305,7 +1305,7 @@
if not field in searchfields
]
- query_filters = {"disabled": 0, "ifnull(end_of_life, '5050-50-50')": (">", today())}
+ query_filters = {"disabled": 0, "end_of_life": (">", today())}
or_cond_filters = {}
if txt:
diff --git a/erpnext/manufacturing/doctype/bom_update_batch/bom_update_batch.json b/erpnext/manufacturing/doctype/bom_update_batch/bom_update_batch.json
index 83b54d3..b867d2a 100644
--- a/erpnext/manufacturing/doctype/bom_update_batch/bom_update_batch.json
+++ b/erpnext/manufacturing/doctype/bom_update_batch/bom_update_batch.json
@@ -1,6 +1,6 @@
{
"actions": [],
- "autoname": "autoincrement",
+ "autoname": "hash",
"creation": "2022-05-31 17:34:39.825537",
"doctype": "DocType",
"engine": "InnoDB",
@@ -46,10 +46,9 @@
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "BOM Update Batch",
- "naming_rule": "Autoincrement",
"owner": "Administrator",
"permissions": [],
"sort_field": "modified",
"sort_order": "DESC",
"states": []
-}
\ No newline at end of file
+}
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 132e1eb..489ea94 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -846,7 +846,7 @@
FROM
`tabBOM Item` bom_item
JOIN `tabBOM` bom ON bom.name = bom_item.parent
- JOIN tabItem item ON bom_item.item_code = item.name
+ JOIN `tabItem` item ON bom_item.item_code = item.name
LEFT JOIN `tabItem Default` item_default
ON item.name = item_default.parent and item_default.company = %(company)s
LEFT JOIN `tabUOM Conversion Detail` item_uom
@@ -976,7 +976,7 @@
select distinct so.name, so.transaction_date, so.customer, so.base_grand_total
from `tabSales Order` so, `tabSales Order Item` so_item
where so_item.parent = so.name
- and so.docstatus = 1 and so.status not in ("Stopped", "Closed")
+ and so.docstatus = 1 and so.status not in ('Stopped', 'Closed')
and so.company = %(company)s
and so_item.qty > so_item.work_order_qty {so_filter} {item_filter}
and (exists (select name from `tabBOM` bom where {bom_item}
diff --git a/erpnext/manufacturing/doctype/work_order/work_order.py b/erpnext/manufacturing/doctype/work_order/work_order.py
index 2802310..7b86253 100644
--- a/erpnext/manufacturing/doctype/work_order/work_order.py
+++ b/erpnext/manufacturing/doctype/work_order/work_order.py
@@ -939,7 +939,7 @@
from `tabStock Entry` entry, `tabStock Entry Detail` detail
where
entry.work_order = %(name)s
- and entry.purpose = "Material Transfer for Manufacture"
+ and entry.purpose = 'Material Transfer for Manufacture'
and entry.docstatus = 1
and detail.parent = entry.name
and (detail.item_code = %(item)s or detail.original_item = %(item)s)""",
diff --git a/erpnext/manufacturing/workspace/manufacturing/manufacturing.json b/erpnext/manufacturing/workspace/manufacturing/manufacturing.json
index 9829a96..549f5af 100644
--- a/erpnext/manufacturing/workspace/manufacturing/manufacturing.json
+++ b/erpnext/manufacturing/workspace/manufacturing/manufacturing.json
@@ -1,6 +1,6 @@
{
"charts": [],
- "content": "[{\"type\":\"header\",\"data\":{\"text\":\"<span class=\\\"h4\\\"><b>Your Shortcuts</b></span>\",\"col\":12}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Item\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"BOM\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Work Order\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Production Plan\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Forecasting\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Work Order Summary\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"BOM Stock Report\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Production Planning Report\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Dashboard\",\"col\":3}},{\"type\":\"spacer\",\"data\":{\"col\":12}},{\"type\":\"header\",\"data\":{\"text\":\"<span class=\\\"h4\\\"><b>Reports & Masters</b></span>\",\"col\":12}},{\"type\":\"card\",\"data\":{\"card_name\":\"Production\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Bill of Materials\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Reports\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Tools\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Settings\",\"col\":4}}]",
+ "content": "[{\"type\":\"header\",\"data\":{\"text\":\"<span class=\\\"h4\\\"><b>Your Shortcuts</b></span>\",\"col\":12}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"BOM\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Production Plan\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Work Order\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Job Card\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Forecasting\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"BOM Stock Report\",\"col\":3}},{\"type\":\"shortcut\",\"data\":{\"shortcut_name\":\"Production Planning Report\",\"col\":3}},{\"type\":\"spacer\",\"data\":{\"col\":12}},{\"type\":\"header\",\"data\":{\"text\":\"<span class=\\\"h4\\\"><b>Reports & Masters</b></span>\",\"col\":12}},{\"type\":\"card\",\"data\":{\"card_name\":\"Production\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Bill of Materials\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Reports\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Tools\",\"col\":4}},{\"type\":\"card\",\"data\":{\"card_name\":\"Settings\",\"col\":4}}]",
"creation": "2020-03-02 17:11:37.032604",
"docstatus": 0,
"doctype": "Workspace",
@@ -402,7 +402,7 @@
"type": "Link"
}
],
- "modified": "2022-05-31 22:08:19.408223",
+ "modified": "2022-06-15 15:18:57.062935",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Manufacturing",
@@ -415,39 +415,35 @@
"sequence_id": 17.0,
"shortcuts": [
{
- "color": "Green",
- "format": "{} Active",
- "label": "Item",
- "link_to": "Item",
- "restrict_to_domain": "Manufacturing",
- "stats_filter": "{\n \"disabled\": 0\n}",
- "type": "DocType"
- },
- {
- "color": "Green",
- "format": "{} Active",
+ "color": "Grey",
+ "doc_view": "List",
"label": "BOM",
"link_to": "BOM",
- "restrict_to_domain": "Manufacturing",
- "stats_filter": "{\n \"is_active\": 1\n}",
+ "stats_filter": "{\"is_active\":[\"=\",1]}",
"type": "DocType"
},
{
- "color": "Yellow",
- "format": "{} Open",
- "label": "Work Order",
- "link_to": "Work Order",
- "restrict_to_domain": "Manufacturing",
- "stats_filter": "{ \n \"status\": [\"in\", \n [\"Draft\", \"Not Started\", \"In Process\"]\n ]\n}",
- "type": "DocType"
- },
- {
- "color": "Yellow",
- "format": "{} Open",
+ "color": "Grey",
+ "doc_view": "List",
"label": "Production Plan",
"link_to": "Production Plan",
- "restrict_to_domain": "Manufacturing",
- "stats_filter": "{ \n \"status\": [\"not in\", [\"Completed\"]]\n}",
+ "stats_filter": "{\"status\":[\"not in\",[\"Closed\",\"Cancelled\",\"Completed\"]]}",
+ "type": "DocType"
+ },
+ {
+ "color": "Grey",
+ "doc_view": "List",
+ "label": "Work Order",
+ "link_to": "Work Order",
+ "stats_filter": "{\"status\":[\"not in\",[\"Closed\",\"Cancelled\",\"Completed\"]]}",
+ "type": "DocType"
+ },
+ {
+ "color": "Grey",
+ "doc_view": "List",
+ "label": "Job Card",
+ "link_to": "Job Card",
+ "stats_filter": "{\"status\":[\"not in\",[\"Cancelled\",\"Completed\",null]]}",
"type": "DocType"
},
{
@@ -456,12 +452,6 @@
"type": "Report"
},
{
- "label": "Work Order Summary",
- "link_to": "Work Order Summary",
- "restrict_to_domain": "Manufacturing",
- "type": "Report"
- },
- {
"label": "BOM Stock Report",
"link_to": "BOM Stock Report",
"type": "Report"
@@ -470,12 +460,6 @@
"label": "Production Planning Report",
"link_to": "Production Planning Report",
"type": "Report"
- },
- {
- "label": "Dashboard",
- "link_to": "Manufacturing",
- "restrict_to_domain": "Manufacturing",
- "type": "Dashboard"
}
],
"title": "Manufacturing"
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 51038a5..5654299 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -339,7 +339,7 @@
erpnext.patches.v14_0.delete_healthcare_doctypes
erpnext.patches.v14_0.delete_hub_doctypes
erpnext.patches.v14_0.delete_hospitality_doctypes # 20-01-2022
-erpnext.patches.v14_0.delete_agriculture_doctypes
+erpnext.patches.v14_0.delete_agriculture_doctypes # 15-06-2022
erpnext.patches.v14_0.delete_education_doctypes
erpnext.patches.v14_0.delete_datev_doctypes
erpnext.patches.v14_0.rearrange_company_fields
@@ -375,3 +375,4 @@
erpnext.patches.v13_0.set_payroll_entry_status
erpnext.patches.v13_0.job_card_status_on_hold
erpnext.patches.v14_0.copy_is_subcontracted_value_to_is_old_subcontracting_flow
+erpnext.patches.v14_0.migrate_gl_to_payment_ledger
diff --git a/erpnext/patches/v12_0/update_healthcare_refactored_changes.py b/erpnext/patches/v12_0/update_healthcare_refactored_changes.py
deleted file mode 100644
index 5ca0d5d..0000000
--- a/erpnext/patches/v12_0/update_healthcare_refactored_changes.py
+++ /dev/null
@@ -1,131 +0,0 @@
-import frappe
-from frappe.model.utils.rename_field import rename_field
-from frappe.modules import get_doctype_module, scrub
-
-field_rename_map = {
- "Healthcare Settings": [
- ["patient_master_name", "patient_name_by"],
- ["max_visit", "max_visits"],
- ["reg_sms", "send_registration_msg"],
- ["reg_msg", "registration_msg"],
- ["app_con", "send_appointment_confirmation"],
- ["app_con_msg", "appointment_confirmation_msg"],
- ["no_con", "avoid_confirmation"],
- ["app_rem", "send_appointment_reminder"],
- ["app_rem_msg", "appointment_reminder_msg"],
- ["rem_before", "remind_before"],
- ["manage_customer", "link_customer_to_patient"],
- ["create_test_on_si_submit", "create_lab_test_on_si_submit"],
- ["require_sample_collection", "create_sample_collection_for_lab_test"],
- ["require_test_result_approval", "lab_test_approval_required"],
- ["manage_appointment_invoice_automatically", "automate_appointment_invoicing"],
- ],
- "Drug Prescription": [["use_interval", "usage_interval"], ["in_every", "interval_uom"]],
- "Lab Test Template": [
- ["sample_quantity", "sample_qty"],
- ["sample_collection_details", "sample_details"],
- ],
- "Sample Collection": [
- ["sample_quantity", "sample_qty"],
- ["sample_collection_details", "sample_details"],
- ],
- "Fee Validity": [["max_visit", "max_visits"]],
-}
-
-
-def execute():
- for dn in field_rename_map:
- if frappe.db.exists("DocType", dn):
- if dn == "Healthcare Settings":
- frappe.reload_doctype("Healthcare Settings")
- else:
- frappe.reload_doc(get_doctype_module(dn), "doctype", scrub(dn))
-
- for dt, field_list in field_rename_map.items():
- if frappe.db.exists("DocType", dt):
- for field in field_list:
- if dt == "Healthcare Settings":
- rename_field(dt, field[0], field[1])
- elif frappe.db.has_column(dt, field[0]):
- rename_field(dt, field[0], field[1])
-
- # first name mandatory in Patient
- if frappe.db.exists("DocType", "Patient"):
- patients = frappe.db.sql("select name, patient_name from `tabPatient`", as_dict=1)
- frappe.reload_doc("healthcare", "doctype", "patient")
- for entry in patients:
- name = entry.patient_name.split(" ")
- frappe.db.set_value("Patient", entry.name, "first_name", name[0])
-
- # mark Healthcare Practitioner status as Disabled
- if frappe.db.exists("DocType", "Healthcare Practitioner"):
- practitioners = frappe.db.sql(
- "select name from `tabHealthcare Practitioner` where 'active'= 0", as_dict=1
- )
- practitioners_lst = [p.name for p in practitioners]
- frappe.reload_doc("healthcare", "doctype", "healthcare_practitioner")
- if practitioners_lst:
- frappe.db.sql(
- "update `tabHealthcare Practitioner` set status = 'Disabled' where name IN %(practitioners)s"
- "",
- {"practitioners": practitioners_lst},
- )
-
- # set Clinical Procedure status
- if frappe.db.exists("DocType", "Clinical Procedure"):
- frappe.reload_doc("healthcare", "doctype", "clinical_procedure")
- frappe.db.sql(
- """
- UPDATE
- `tabClinical Procedure`
- SET
- docstatus = (CASE WHEN status = 'Cancelled' THEN 2
- WHEN status = 'Draft' THEN 0
- ELSE 1
- END)
- """
- )
-
- # set complaints and diagnosis in table multiselect in Patient Encounter
- if frappe.db.exists("DocType", "Patient Encounter"):
- field_list = [["visit_department", "medical_department"], ["type", "appointment_type"]]
- encounter_details = frappe.db.sql(
- """select symptoms, diagnosis, name from `tabPatient Encounter`""", as_dict=True
- )
- frappe.reload_doc("healthcare", "doctype", "patient_encounter")
- frappe.reload_doc("healthcare", "doctype", "patient_encounter_symptom")
- frappe.reload_doc("healthcare", "doctype", "patient_encounter_diagnosis")
-
- for field in field_list:
- if frappe.db.has_column(dt, field[0]):
- rename_field(dt, field[0], field[1])
-
- for entry in encounter_details:
- doc = frappe.get_doc("Patient Encounter", entry.name)
- symptoms = entry.symptoms.split("\n") if entry.symptoms else []
- for symptom in symptoms:
- if not frappe.db.exists("Complaint", symptom):
- frappe.get_doc({"doctype": "Complaint", "complaints": symptom}).insert()
- row = doc.append("symptoms", {"complaint": symptom})
- row.db_update()
-
- diagnosis = entry.diagnosis.split("\n") if entry.diagnosis else []
- for d in diagnosis:
- if not frappe.db.exists("Diagnosis", d):
- frappe.get_doc({"doctype": "Diagnosis", "diagnosis": d}).insert()
- row = doc.append("diagnosis", {"diagnosis": d})
- row.db_update()
- doc.db_update()
-
- if frappe.db.exists("DocType", "Fee Validity"):
- # update fee validity status
- frappe.db.sql(
- """
- UPDATE
- `tabFee Validity`
- SET
- status = (CASE WHEN visited >= max_visits THEN 'Completed'
- ELSE 'Pending'
- END)
- """
- )
diff --git a/erpnext/patches/v13_0/healthcare_lab_module_rename_doctypes.py b/erpnext/patches/v13_0/healthcare_lab_module_rename_doctypes.py
deleted file mode 100644
index 30b84ac..0000000
--- a/erpnext/patches/v13_0/healthcare_lab_module_rename_doctypes.py
+++ /dev/null
@@ -1,94 +0,0 @@
-import frappe
-from frappe.model.utils.rename_field import rename_field
-
-
-def execute():
- if frappe.db.exists("DocType", "Lab Test") and frappe.db.exists("DocType", "Lab Test Template"):
- # rename child doctypes
- doctypes = {
- "Lab Test Groups": "Lab Test Group Template",
- "Normal Test Items": "Normal Test Result",
- "Sensitivity Test Items": "Sensitivity Test Result",
- "Special Test Items": "Descriptive Test Result",
- "Special Test Template": "Descriptive Test Template",
- }
-
- frappe.reload_doc("healthcare", "doctype", "lab_test")
- frappe.reload_doc("healthcare", "doctype", "lab_test_template")
-
- for old_dt, new_dt in doctypes.items():
- frappe.flags.link_fields = {}
- should_rename = frappe.db.table_exists(old_dt) and not frappe.db.table_exists(new_dt)
- if should_rename:
- frappe.reload_doc("healthcare", "doctype", frappe.scrub(old_dt))
- frappe.rename_doc("DocType", old_dt, new_dt, force=True)
- frappe.reload_doc("healthcare", "doctype", frappe.scrub(new_dt))
- frappe.delete_doc_if_exists("DocType", old_dt)
-
- parent_fields = {
- "Lab Test Group Template": "lab_test_groups",
- "Descriptive Test Template": "descriptive_test_templates",
- "Normal Test Result": "normal_test_items",
- "Sensitivity Test Result": "sensitivity_test_items",
- "Descriptive Test Result": "descriptive_test_items",
- }
-
- for doctype, parentfield in parent_fields.items():
- frappe.db.sql(
- """
- UPDATE `tab{0}`
- SET parentfield = %(parentfield)s
- """.format(
- doctype
- ),
- {"parentfield": parentfield},
- )
-
- # copy renamed child table fields (fields were already renamed in old doctype json, hence sql)
- rename_fields = {
- "lab_test_name": "test_name",
- "lab_test_event": "test_event",
- "lab_test_uom": "test_uom",
- "lab_test_comment": "test_comment",
- }
-
- for new, old in rename_fields.items():
- if frappe.db.has_column("Normal Test Result", old):
- frappe.db.sql("""UPDATE `tabNormal Test Result` SET {} = {}""".format(new, old))
-
- if frappe.db.has_column("Normal Test Template", "test_event"):
- frappe.db.sql("""UPDATE `tabNormal Test Template` SET lab_test_event = test_event""")
-
- if frappe.db.has_column("Normal Test Template", "test_uom"):
- frappe.db.sql("""UPDATE `tabNormal Test Template` SET lab_test_uom = test_uom""")
-
- if frappe.db.has_column("Descriptive Test Result", "test_particulars"):
- frappe.db.sql(
- """UPDATE `tabDescriptive Test Result` SET lab_test_particulars = test_particulars"""
- )
-
- rename_fields = {
- "lab_test_template": "test_template",
- "lab_test_description": "test_description",
- "lab_test_rate": "test_rate",
- }
-
- for new, old in rename_fields.items():
- if frappe.db.has_column("Lab Test Group Template", old):
- frappe.db.sql("""UPDATE `tabLab Test Group Template` SET {} = {}""".format(new, old))
-
- # rename field
- frappe.reload_doc("healthcare", "doctype", "lab_test")
- if frappe.db.has_column("Lab Test", "special_toggle"):
- rename_field("Lab Test", "special_toggle", "descriptive_toggle")
-
- if frappe.db.exists("DocType", "Lab Test Group Template"):
- # fix select field option
- frappe.reload_doc("healthcare", "doctype", "lab_test_group_template")
- frappe.db.sql(
- """
- UPDATE `tabLab Test Group Template`
- SET template_or_new_line = 'Add New Line'
- WHERE template_or_new_line = 'Add new line'
- """
- )
diff --git a/erpnext/patches/v13_0/print_uom_after_quantity_patch.py b/erpnext/patches/v13_0/print_uom_after_quantity_patch.py
deleted file mode 100644
index a16f909..0000000
--- a/erpnext/patches/v13_0/print_uom_after_quantity_patch.py
+++ /dev/null
@@ -1,9 +0,0 @@
-# Copyright (c) 2019, Frappe and Contributors
-# License: GNU General Public License v3. See license.txt
-
-
-from erpnext.setup.install import create_print_uom_after_qty_custom_field
-
-
-def execute():
- create_print_uom_after_qty_custom_field()
diff --git a/erpnext/patches/v13_0/rename_discharge_date_in_ip_record.py b/erpnext/patches/v13_0/rename_discharge_date_in_ip_record.py
deleted file mode 100644
index 3bd717d..0000000
--- a/erpnext/patches/v13_0/rename_discharge_date_in_ip_record.py
+++ /dev/null
@@ -1,8 +0,0 @@
-import frappe
-from frappe.model.utils.rename_field import rename_field
-
-
-def execute():
- frappe.reload_doc("Healthcare", "doctype", "Inpatient Record")
- if frappe.db.has_column("Inpatient Record", "discharge_date"):
- rename_field("Inpatient Record", "discharge_date", "discharge_datetime")
diff --git a/erpnext/patches/v13_0/set_company_field_in_healthcare_doctypes.py b/erpnext/patches/v13_0/set_company_field_in_healthcare_doctypes.py
deleted file mode 100644
index bc2d1b9..0000000
--- a/erpnext/patches/v13_0/set_company_field_in_healthcare_doctypes.py
+++ /dev/null
@@ -1,25 +0,0 @@
-import frappe
-
-
-def execute():
- company = frappe.db.get_single_value("Global Defaults", "default_company")
- doctypes = [
- "Clinical Procedure",
- "Inpatient Record",
- "Lab Test",
- "Sample Collection",
- "Patient Appointment",
- "Patient Encounter",
- "Vital Signs",
- "Therapy Session",
- "Therapy Plan",
- "Patient Assessment",
- ]
- for entry in doctypes:
- if frappe.db.exists("DocType", entry):
- frappe.reload_doc("Healthcare", "doctype", entry)
- frappe.db.sql(
- "update `tab{dt}` set company = {company} where ifnull(company, '') = ''".format(
- dt=entry, company=frappe.db.escape(company)
- )
- )
diff --git a/erpnext/patches/v14_0/delete_agriculture_doctypes.py b/erpnext/patches/v14_0/delete_agriculture_doctypes.py
index e0b12a2..8ec0c33 100644
--- a/erpnext/patches/v14_0/delete_agriculture_doctypes.py
+++ b/erpnext/patches/v14_0/delete_agriculture_doctypes.py
@@ -2,6 +2,9 @@
def execute():
+ if "agriculture" in frappe.get_installed_apps():
+ return
+
frappe.delete_doc("Module Def", "Agriculture", ignore_missing=True, force=True)
frappe.delete_doc("Workspace", "Agriculture", ignore_missing=True, force=True)
@@ -19,3 +22,5 @@
doctypes = frappe.get_all("DocType", {"module": "agriculture", "custom": 0}, pluck="name")
for doctype in doctypes:
frappe.delete_doc("DocType", doctype, ignore_missing=True)
+
+ frappe.delete_doc("Module Def", "Agriculture", ignore_missing=True, force=True)
diff --git a/erpnext/payroll/doctype/payroll_entry/payroll_entry.py b/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
index 1524fb7..86a8c12 100644
--- a/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
+++ b/erpnext/payroll/doctype/payroll_entry/payroll_entry.py
@@ -674,7 +674,7 @@
def get_joining_relieving_condition(start_date, end_date):
cond = """
- and ifnull(t1.date_of_joining, '0000-00-00') <= '%(end_date)s'
+ and ifnull(t1.date_of_joining, '1900-01-01') <= '%(end_date)s'
and ifnull(t1.relieving_date, '2199-12-31') >= '%(start_date)s'
""" % {
"start_date": start_date,
@@ -1035,8 +1035,8 @@
{emp_cond}
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, employee_name) > 0 then locate(%(_txt)s, employee_name) else 99999 end),
idx desc,
name, employee_name
limit %(page_len)s offset %(start)s""".format(
diff --git a/erpnext/payroll/doctype/salary_slip/salary_slip.py b/erpnext/payroll/doctype/salary_slip/salary_slip.py
index 6a35985..e1ccc11 100644
--- a/erpnext/payroll/doctype/salary_slip/salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/salary_slip.py
@@ -508,7 +508,7 @@
SELECT attendance_date, status, leave_type
FROM `tabAttendance`
WHERE
- status in ("Absent", "Half Day", "On leave")
+ status in ('Absent', 'Half Day', 'On leave')
AND employee = %s
AND docstatus = 1
AND attendance_date between %s and %s
diff --git a/erpnext/projects/doctype/project/project.py b/erpnext/projects/doctype/project/project.py
index c613fe6..7aa56de 100644
--- a/erpnext/projects/doctype/project/project.py
+++ b/erpnext/projects/doctype/project/project.py
@@ -387,8 +387,8 @@
or full_name like %(txt)s)
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, full_name), locate(%(_txt)s, full_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, full_name) > 0 then locate(%(_txt)s, full_name) else 99999 end)
idx desc,
name, full_name
limit %(page_len)s offset %(start)s""".format(
diff --git a/erpnext/projects/doctype/project_update/project_update.py b/erpnext/projects/doctype/project_update/project_update.py
index 5a29fb6..175f787 100644
--- a/erpnext/projects/doctype/project_update/project_update.py
+++ b/erpnext/projects/doctype/project_update/project_update.py
@@ -28,7 +28,7 @@
for drafts in draft:
number_of_drafts = drafts[0]
update = frappe.db.sql(
- """SELECT name,date,time,progress,progress_details FROM `tabProject Update` WHERE `tabProject Update`.project = %s AND date = DATE_ADD(CURDATE(), INTERVAL -1 DAY);""",
+ """SELECT name,date,time,progress,progress_details FROM `tabProject Update` WHERE `tabProject Update`.project = %s AND date = DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY);""",
project_name,
)
email_sending(project_name, frequency, date_start, date_end, progress, number_of_drafts, update)
@@ -39,7 +39,7 @@
):
holiday = frappe.db.sql(
- """SELECT holiday_date FROM `tabHoliday` where holiday_date = CURDATE();"""
+ """SELECT holiday_date FROM `tabHoliday` where holiday_date = CURRENT_DATE;"""
)
msg = (
"<p>Project Name: "
diff --git a/erpnext/projects/report/project_profitability/project_profitability.py b/erpnext/projects/report/project_profitability/project_profitability.py
index abbbaf5..aa955bc 100644
--- a/erpnext/projects/report/project_profitability/project_profitability.py
+++ b/erpnext/projects/report/project_profitability/project_profitability.py
@@ -39,17 +39,17 @@
FROM
(SELECT
si.customer_name,si.base_grand_total,
- si.name as voucher_no,tabTimesheet.employee,
- tabTimesheet.title as employee_name,tabTimesheet.parent_project as project,
- tabTimesheet.start_date,tabTimesheet.end_date,
- tabTimesheet.total_billed_hours,tabTimesheet.name as timesheet,
+ si.name as voucher_no,`tabTimesheet`.employee,
+ `tabTimesheet`.title as employee_name,`tabTimesheet`.parent_project as project,
+ `tabTimesheet`.start_date,`tabTimesheet`.end_date,
+ `tabTimesheet`.total_billed_hours,`tabTimesheet`.name as timesheet,
ss.base_gross_pay,ss.total_working_days,
- tabTimesheet.total_billed_hours/(ss.total_working_days * {0}) as utilization
+ `tabTimesheet`.total_billed_hours/(ss.total_working_days * {0}) as utilization
FROM
- `tabSalary Slip Timesheet` as sst join `tabTimesheet` on tabTimesheet.name = sst.time_sheet
- join `tabSales Invoice Timesheet` as sit on sit.time_sheet = tabTimesheet.name
- join `tabSales Invoice` as si on si.name = sit.parent and si.status != "Cancelled"
- join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != "Cancelled" """.format(
+ `tabSalary Slip Timesheet` as sst join `tabTimesheet` on `tabTimesheet`.name = sst.time_sheet
+ join `tabSales Invoice Timesheet` as sit on sit.time_sheet = `tabTimesheet`.name
+ join `tabSales Invoice` as si on si.name = sit.parent and si.status != 'Cancelled'
+ join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != 'Cancelled' """.format(
standard_working_hours
)
if conditions:
@@ -72,23 +72,25 @@
conditions = []
if filters.get("company"):
- conditions.append("tabTimesheet.company={0}".format(frappe.db.escape(filters.get("company"))))
+ conditions.append("`tabTimesheet`.company={0}".format(frappe.db.escape(filters.get("company"))))
if filters.get("start_date"):
- conditions.append("tabTimesheet.start_date>='{0}'".format(filters.get("start_date")))
+ conditions.append("`tabTimesheet`.start_date>='{0}'".format(filters.get("start_date")))
if filters.get("end_date"):
- conditions.append("tabTimesheet.end_date<='{0}'".format(filters.get("end_date")))
+ conditions.append("`tabTimesheet`.end_date<='{0}'".format(filters.get("end_date")))
if filters.get("customer_name"):
conditions.append("si.customer_name={0}".format(frappe.db.escape(filters.get("customer_name"))))
if filters.get("employee"):
- conditions.append("tabTimesheet.employee={0}".format(frappe.db.escape(filters.get("employee"))))
+ conditions.append(
+ "`tabTimesheet`.employee={0}".format(frappe.db.escape(filters.get("employee")))
+ )
if filters.get("project"):
conditions.append(
- "tabTimesheet.parent_project={0}".format(frappe.db.escape(filters.get("project")))
+ "`tabTimesheet`.parent_project={0}".format(frappe.db.escape(filters.get("project")))
)
conditions = " and ".join(conditions)
diff --git a/erpnext/public/js/controllers/transaction.js b/erpnext/public/js/controllers/transaction.js
index d86ff1c..3e36b88 100644
--- a/erpnext/public/js/controllers/transaction.js
+++ b/erpnext/public/js/controllers/transaction.js
@@ -453,7 +453,6 @@
is_pos: cint(me.frm.doc.is_pos),
is_return: cint(me.frm.doc.is_return),
is_subcontracted: me.frm.doc.is_subcontracted,
- transaction_date: me.frm.doc.transaction_date || me.frm.doc.posting_date,
ignore_pricing_rule: me.frm.doc.ignore_pricing_rule,
doctype: me.frm.doc.doctype,
name: me.frm.doc.name,
diff --git a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
index 5ceb2c0..1d4f96b 100644
--- a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
+++ b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
@@ -83,7 +83,7 @@
("gst_hsn_code", " and gst_hsn_code=%(gst_hsn_code)s"),
("company_gstin", " and company_gstin=%(company_gstin)s"),
("from_date", " and posting_date >= %(from_date)s"),
- ("to_date", "and posting_date <= %(to_date)s"),
+ ("to_date", " and posting_date <= %(to_date)s"),
):
if filters.get(opts[0]):
conditions += opts[1]
diff --git a/erpnext/regional/report/irs_1099/irs_1099.py b/erpnext/regional/report/irs_1099/irs_1099.py
index 0f578be..66ade1f 100644
--- a/erpnext/regional/report/irs_1099/irs_1099.py
+++ b/erpnext/regional/report/irs_1099/irs_1099.py
@@ -47,7 +47,7 @@
s.name = gl.party
AND s.irs_1099 = 1
AND gl.fiscal_year = %(fiscal_year)s
- AND gl.party_type = "Supplier"
+ AND gl.party_type = 'Supplier'
AND gl.company = %(company)s
{conditions}
diff --git a/erpnext/regional/report/vat_audit_report/vat_audit_report.py b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
index 70f2c0a..3d486ce 100644
--- a/erpnext/regional/report/vat_audit_report/vat_audit_report.py
+++ b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
@@ -65,7 +65,7 @@
`tab{doctype}`
WHERE
docstatus = 1 {where_conditions}
- and is_opening = "No"
+ and is_opening = 'No'
ORDER BY
posting_date DESC
""".format(
diff --git a/erpnext/selling/doctype/quotation/quotation.json b/erpnext/selling/doctype/quotation/quotation.json
index 5dfd8f2..bb2f95d 100644
--- a/erpnext/selling/doctype/quotation/quotation.json
+++ b/erpnext/selling/doctype/quotation/quotation.json
@@ -296,7 +296,7 @@
"read_only": 1
},
{
- "depends_on": "eval:doc.quotaion_to=='Customer' && doc.party_name",
+ "depends_on": "eval:doc.quotation_to=='Customer' && doc.party_name",
"fieldname": "col_break98",
"fieldtype": "Column Break",
"width": "50%"
@@ -316,7 +316,7 @@
"read_only": 1
},
{
- "depends_on": "eval:doc.quotaion_to=='Customer' && doc.party_name",
+ "depends_on": "eval:doc.quotation_to=='Customer' && doc.party_name",
"fieldname": "customer_group",
"fieldtype": "Link",
"hidden": 1,
@@ -1084,4 +1084,4 @@
"states": [],
"timeline_field": "party_name",
"title_field": "title"
-}
\ No newline at end of file
+}
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index d5fd946..4fa4515 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -127,7 +127,7 @@
@frappe.whitelist()
def declare_enquiry_lost(self, lost_reasons_list, competitors, detailed_reason=None):
- if not self.has_sales_order():
+ if not (self.is_fully_ordered() or self.is_partially_ordered()):
get_lost_reasons = frappe.get_list("Quotation Lost Reason", fields=["name"])
lost_reasons_lst = [reason.get("name") for reason in get_lost_reasons]
frappe.db.set(self, "status", "Lost")
@@ -267,7 +267,7 @@
def set_expired_status():
# filter out submitted non expired quotations whose validity has been ended
- cond = "qo.docstatus = 1 and qo.status != 'Expired' and qo.valid_till < %s"
+ cond = "`tabQuotation`.docstatus = 1 and `tabQuotation`.status != 'Expired' and `tabQuotation`.valid_till < %s"
# check if those QUO have SO against it
so_against_quo = """
SELECT
@@ -275,13 +275,18 @@
WHERE
so_item.docstatus = 1 and so.docstatus = 1
and so_item.parent = so.name
- and so_item.prevdoc_docname = qo.name"""
+ and so_item.prevdoc_docname = `tabQuotation`.name"""
# if not exists any SO, set status as Expired
- frappe.db.sql(
- """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and not exists({so_against_quo})""".format(
- cond=cond, so_against_quo=so_against_quo
- ),
+ frappe.db.multisql(
+ {
+ "mariadb": """UPDATE `tabQuotation` SET `tabQuotation`.status = 'Expired' WHERE {cond} and not exists({so_against_quo})""".format(
+ cond=cond, so_against_quo=so_against_quo
+ ),
+ "postgres": """UPDATE `tabQuotation` SET status = 'Expired' FROM `tabSales Order`, `tabSales Order Item` WHERE {cond} and not exists({so_against_quo})""".format(
+ cond=cond, so_against_quo=so_against_quo
+ ),
+ },
(nowdate()),
)
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index 7522e92..8c03cb5 100755
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -25,6 +25,7 @@
from erpnext.selling.doctype.customer.customer import check_credit_limit
from erpnext.setup.doctype.item_group.item_group import get_item_group_defaults
from erpnext.stock.doctype.item.item import get_item_defaults
+from erpnext.stock.get_item_details import get_default_bom
from erpnext.stock.stock_balance import get_reserved_qty, update_bin_qty
form_grid_templates = {"items": "templates/form_grid/item_grid.html"}
@@ -423,8 +424,9 @@
for table in [self.items, self.packed_items]:
for i in table:
- bom = get_default_bom_item(i.item_code)
+ bom = get_default_bom(i.item_code)
stock_qty = i.qty if i.doctype == "Packed Item" else i.stock_qty
+
if not for_raw_material_request:
total_work_order_qty = flt(
frappe.db.sql(
@@ -438,32 +440,19 @@
pending_qty = stock_qty
if pending_qty and i.item_code not in product_bundle_parents:
- if bom:
- items.append(
- dict(
- name=i.name,
- item_code=i.item_code,
- description=i.description,
- bom=bom,
- warehouse=i.warehouse,
- pending_qty=pending_qty,
- required_qty=pending_qty if for_raw_material_request else 0,
- sales_order_item=i.name,
- )
+ items.append(
+ dict(
+ name=i.name,
+ item_code=i.item_code,
+ description=i.description,
+ bom=bom or "",
+ warehouse=i.warehouse,
+ pending_qty=pending_qty,
+ required_qty=pending_qty if for_raw_material_request else 0,
+ sales_order_item=i.name,
)
- else:
- items.append(
- dict(
- name=i.name,
- item_code=i.item_code,
- description=i.description,
- bom="",
- warehouse=i.warehouse,
- pending_qty=pending_qty,
- required_qty=pending_qty if for_raw_material_request else 0,
- sales_order_item=i.name,
- )
- )
+ )
+
return items
def on_recurring(self, reference_doc, auto_repeat_doc):
@@ -1167,13 +1156,6 @@
so.update_status(status)
-def get_default_bom_item(item_code):
- bom = frappe.get_all("BOM", dict(item=item_code, is_active=True), order_by="is_default desc")
- bom = bom[0].name if bom else None
-
- return bom
-
-
@frappe.whitelist()
def make_raw_material_request(items, company, sales_order, project=None):
if not frappe.has_permission("Sales Order", "write"):
diff --git a/erpnext/selling/doctype/sales_order/test_sales_order.py b/erpnext/selling/doctype/sales_order/test_sales_order.py
index 96308f0..e5e317c 100644
--- a/erpnext/selling/doctype/sales_order/test_sales_order.py
+++ b/erpnext/selling/doctype/sales_order/test_sales_order.py
@@ -329,7 +329,7 @@
def test_sales_order_on_hold(self):
so = make_sales_order(item_code="_Test Product Bundle Item")
- so.db_set("Status", "On Hold")
+ so.db_set("status", "On Hold")
si = make_sales_invoice(so.name)
self.assertRaises(frappe.ValidationError, create_dn_against_so, so.name)
self.assertRaises(frappe.ValidationError, si.submit)
@@ -644,7 +644,7 @@
else:
# update valid from
frappe.db.sql(
- """UPDATE `tabItem Tax` set valid_from = CURDATE()
+ """UPDATE `tabItem Tax` set valid_from = CURRENT_DATE
where parent = %(item)s and item_tax_template = %(tax)s""",
{"item": item, "tax": tax_template},
)
@@ -1380,6 +1380,59 @@
except Exception:
self.fail("Can not cancel sales order with linked cancelled payment entry")
+ def test_work_order_pop_up_from_sales_order(self):
+ "Test `get_work_order_items` in Sales Order picks the right BOM for items to manufacture."
+
+ from erpnext.controllers.item_variant import create_variant
+ from erpnext.manufacturing.doctype.production_plan.test_production_plan import make_bom
+
+ make_item( # template item
+ "Test-WO-Tshirt",
+ {
+ "has_variant": 1,
+ "variant_based_on": "Item Attribute",
+ "attributes": [{"attribute": "Test Colour"}],
+ },
+ )
+ make_item("Test-RM-Cotton") # RM for BOM
+
+ for colour in (
+ "Red",
+ "Green",
+ ):
+ variant = create_variant("Test-WO-Tshirt", {"Test Colour": colour})
+ variant.save()
+
+ template_bom = make_bom(item="Test-WO-Tshirt", rate=100, raw_materials=["Test-RM-Cotton"])
+ red_var_bom = make_bom(item="Test-WO-Tshirt-R", rate=100, raw_materials=["Test-RM-Cotton"])
+
+ so = make_sales_order(
+ **{
+ "item_list": [
+ {
+ "item_code": "Test-WO-Tshirt-R",
+ "qty": 1,
+ "rate": 1000,
+ "warehouse": "_Test Warehouse - _TC",
+ },
+ {
+ "item_code": "Test-WO-Tshirt-G",
+ "qty": 1,
+ "rate": 1000,
+ "warehouse": "_Test Warehouse - _TC",
+ },
+ ]
+ }
+ )
+ wo_items = so.get_work_order_items()
+
+ self.assertEqual(wo_items[0].get("item_code"), "Test-WO-Tshirt-R")
+ self.assertEqual(wo_items[0].get("bom"), red_var_bom.name)
+
+ # Must pick Template Item BOM for Test-WO-Tshirt-G as it has no BOM
+ self.assertEqual(wo_items[1].get("item_code"), "Test-WO-Tshirt-G")
+ self.assertEqual(wo_items[1].get("bom"), template_bom.name)
+
def test_request_for_raw_materials(self):
item = make_item(
"_Test Finished Item",
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 3797856..3187999 100644
--- a/erpnext/selling/doctype/sales_order_item/sales_order_item.json
+++ b/erpnext/selling/doctype/sales_order_item/sales_order_item.json
@@ -23,7 +23,6 @@
"quantity_and_rate",
"qty",
"stock_uom",
- "picked_qty",
"col_break2",
"uom",
"conversion_factor",
@@ -87,6 +86,7 @@
"delivered_qty",
"produced_qty",
"returned_qty",
+ "picked_qty",
"shopping_cart_section",
"additional_notes",
"section_break_63",
@@ -198,6 +198,7 @@
"width": "100px"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -220,6 +221,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "UOM Conversion Factor",
@@ -228,6 +230,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Qty as per Stock UOM",
@@ -811,7 +814,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-04-27 03:15:34.366563",
+ "modified": "2022-06-17 05:27:41.603006",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Order Item",
diff --git a/erpnext/selling/report/inactive_customers/inactive_customers.py b/erpnext/selling/report/inactive_customers/inactive_customers.py
index 1b337fc..a166085 100644
--- a/erpnext/selling/report/inactive_customers/inactive_customers.py
+++ b/erpnext/selling/report/inactive_customers/inactive_customers.py
@@ -31,13 +31,13 @@
def get_sales_details(doctype):
cond = """sum(so.base_net_total) as 'total_order_considered',
max(so.posting_date) as 'last_order_date',
- DATEDIFF(CURDATE(), max(so.posting_date)) as 'days_since_last_order' """
+ DATEDIFF(CURRENT_DATE, max(so.posting_date)) as 'days_since_last_order' """
if doctype == "Sales Order":
cond = """sum(if(so.status = "Stopped",
so.base_net_total * so.per_delivered/100,
so.base_net_total)) as 'total_order_considered',
max(so.transaction_date) as 'last_order_date',
- DATEDIFF(CURDATE(), max(so.transaction_date)) as 'days_since_last_order'"""
+ DATEDIFF(CURRENT_DATE, max(so.transaction_date)) as 'days_since_last_order'"""
return frappe.db.sql(
"""select
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
index cc1055c..928ed80 100644
--- a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
@@ -65,7 +65,7 @@
WHERE
so.docstatus = 1
and so.name = so_item.parent
- and so.status not in ("Closed","Completed","Cancelled")
+ and so.status not in ('Closed','Completed','Cancelled')
GROUP BY
so.name,so_item.item_code
""",
diff --git a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
index cc61594..720aa41 100644
--- a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
@@ -64,7 +64,7 @@
soi.delivery_date as delivery_date,
so.name as sales_order,
so.status, so.customer, soi.item_code,
- DATEDIFF(CURDATE(), soi.delivery_date) as delay_days,
+ DATEDIFF(CURRENT_DATE, soi.delivery_date) as delay_days,
IF(so.status in ('Completed','To Bill'), 0, (SELECT delay_days)) as delay,
soi.qty, soi.delivered_qty,
(soi.qty - soi.delivered_qty) AS pending_qty,
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index 9bde6e2..9ffd6df 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -464,7 +464,7 @@
# reset default company
frappe.db.sql(
- """update `tabSingles` set value=""
+ """update `tabSingles` set value=''
where doctype='Global Defaults' and field='default_company'
and value=%s""",
self.name,
@@ -472,7 +472,7 @@
# reset default company
frappe.db.sql(
- """update `tabSingles` set value=""
+ """update `tabSingles` set value=''
where doctype='Chart of Accounts Importer' and field='company'
and value=%s""",
self.name,
diff --git a/erpnext/setup/doctype/email_digest/email_digest.py b/erpnext/setup/doctype/email_digest/email_digest.py
index cdfea77..4fc20e6 100644
--- a/erpnext/setup/doctype/email_digest/email_digest.py
+++ b/erpnext/setup/doctype/email_digest/email_digest.py
@@ -198,7 +198,7 @@
todo_list = frappe.db.sql(
"""select *
- from `tabToDo` where (owner=%s or assigned_by=%s) and status="Open"
+ from `tabToDo` where (owner=%s or assigned_by=%s) and status='Open'
order by field(priority, 'High', 'Medium', 'Low') asc, date asc limit 20""",
(user_id, user_id),
as_dict=True,
@@ -854,7 +854,7 @@
sql_po = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
- where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
+ where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and CURRENT_DATE > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.parent DESC,
`tabPurchase Order Item`.schedule_date DESC""".format(
fields=fields_po
@@ -862,7 +862,7 @@
sql_poi = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
- where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
+ where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and CURRENT_DATE > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.idx""".format(
fields=fields_poi
)
diff --git a/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py b/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
index 78b3939..7c478bb 100644
--- a/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
+++ b/erpnext/setup/doctype/transaction_deletion_record/transaction_deletion_record.py
@@ -42,7 +42,7 @@
def delete_bins(self):
frappe.db.sql(
- """delete from tabBin where warehouse in
+ """delete from `tabBin` where warehouse in
(select name from tabWarehouse where company=%s)""",
self.company,
)
@@ -64,7 +64,7 @@
addresses = ["%s" % frappe.db.escape(addr) for addr in addresses]
frappe.db.sql(
- """delete from tabAddress where name in ({addresses}) and
+ """delete from `tabAddress` where name in ({addresses}) and
name not in (select distinct dl1.parent from `tabDynamic Link` dl1
inner join `tabDynamic Link` dl2 on dl1.parent=dl2.parent
and dl1.link_doctype<>dl2.link_doctype)""".format(
@@ -80,7 +80,7 @@
)
frappe.db.sql(
- """update tabCustomer set lead_name=NULL where lead_name in ({leads})""".format(
+ """update `tabCustomer` set lead_name=NULL where lead_name in ({leads})""".format(
leads=",".join(leads)
)
)
@@ -178,7 +178,7 @@
else:
last = 0
- frappe.db.sql("""update tabSeries set current = %s where name=%s""", (last, prefix))
+ frappe.db.sql("""update `tabSeries` set current = %s where name=%s""", (last, prefix))
def delete_version_log(self, doctype, company_fieldname):
frappe.db.sql(
diff --git a/erpnext/stock/doctype/batch/batch.py b/erpnext/stock/doctype/batch/batch.py
index 559883f..52854a0 100644
--- a/erpnext/stock/doctype/batch/batch.py
+++ b/erpnext/stock/doctype/batch/batch.py
@@ -335,7 +335,7 @@
on (`tabBatch`.batch_id = `tabStock Ledger Entry`.batch_no )
where `tabStock Ledger Entry`.item_code = %s and `tabStock Ledger Entry`.warehouse = %s
and `tabStock Ledger Entry`.is_cancelled = 0
- and (`tabBatch`.expiry_date >= CURDATE() or `tabBatch`.expiry_date IS NULL) {0}
+ and (`tabBatch`.expiry_date >= CURRENT_DATE or `tabBatch`.expiry_date IS NULL) {0}
group by batch_id
order by `tabBatch`.expiry_date ASC, `tabBatch`.creation ASC
""".format(
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 2d7abc8..2de4842 100644
--- a/erpnext/stock/doctype/delivery_note_item/delivery_note_item.json
+++ b/erpnext/stock/doctype/delivery_note_item/delivery_note_item.json
@@ -184,6 +184,7 @@
"width": "100px"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -209,6 +210,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "UOM Conversion Factor",
@@ -217,6 +219,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Qty in Stock UOM",
@@ -780,7 +783,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-05-02 12:09:39.610075",
+ "modified": "2022-06-17 05:25:47.711177",
"modified_by": "Administrator",
"module": "Stock",
"name": "Delivery Note Item",
diff --git a/erpnext/stock/doctype/delivery_trip/delivery_trip.py b/erpnext/stock/doctype/delivery_trip/delivery_trip.py
index 73b250d..ff95c50 100644
--- a/erpnext/stock/doctype/delivery_trip/delivery_trip.py
+++ b/erpnext/stock/doctype/delivery_trip/delivery_trip.py
@@ -263,9 +263,9 @@
FROM
`tabDynamic Link` dl
WHERE
- dl.link_doctype="Customer"
+ dl.link_doctype='Customer'
AND dl.link_name=%s
- AND dl.parenttype = "Contact"
+ AND dl.parenttype = 'Contact'
""",
(name),
as_dict=1,
@@ -289,9 +289,9 @@
FROM
`tabDynamic Link` dl
WHERE
- dl.link_doctype="Customer"
+ dl.link_doctype='Customer'
AND dl.link_name=%s
- AND dl.parenttype = "Address"
+ AND dl.parenttype = 'Address'
""",
(name),
as_dict=1,
@@ -388,7 +388,7 @@
if email_recipients:
frappe.msgprint(_("Email sent to {0}").format(", ".join(email_recipients)))
- delivery_trip.db_set("email_notification_sent", True)
+ delivery_trip.db_set("email_notification_sent", 1)
else:
frappe.msgprint(_("No contacts with email IDs found."))
diff --git a/erpnext/stock/doctype/item/item.json b/erpnext/stock/doctype/item/item.json
index 2f6d4fb..76cb31d 100644
--- a/erpnext/stock/doctype/item/item.json
+++ b/erpnext/stock/doctype/item/item.json
@@ -14,7 +14,6 @@
"details",
"naming_series",
"item_code",
- "variant_of",
"item_name",
"item_group",
"stock_uom",
@@ -22,6 +21,7 @@
"disabled",
"allow_alternative_item",
"is_stock_item",
+ "has_variants",
"include_item_in_manufacturing",
"opening_stock",
"valuation_rate",
@@ -66,7 +66,7 @@
"has_serial_no",
"serial_no_series",
"variants_section",
- "has_variants",
+ "variant_of",
"variant_based_on",
"attributes",
"accounting",
@@ -112,8 +112,8 @@
"quality_inspection_template",
"inspection_required_before_delivery",
"manufacturing",
- "default_bom",
"is_sub_contracted_item",
+ "default_bom",
"column_break_74",
"customer_code",
"default_item_manufacturer",
@@ -479,7 +479,7 @@
"collapsible_depends_on": "attributes",
"depends_on": "eval:!doc.is_fixed_asset",
"fieldname": "variants_section",
- "fieldtype": "Section Break",
+ "fieldtype": "Tab Break",
"label": "Variants"
},
{
@@ -504,7 +504,8 @@
"fieldname": "attributes",
"fieldtype": "Table",
"hidden": 1,
- "label": "Attributes",
+ "label": "Variant Attributes",
+ "mandatory_depends_on": "has_variants",
"options": "Item Variant Attribute"
},
{
@@ -909,7 +910,7 @@
"image_field": "image",
"index_web_pages_for_search": 1,
"links": [],
- "modified": "2022-06-08 11:35:20.094546",
+ "modified": "2022-06-15 09:02:06.177691",
"modified_by": "Administrator",
"module": "Stock",
"name": "Item",
diff --git a/erpnext/stock/doctype/item/item.py b/erpnext/stock/doctype/item/item.py
index b2f5fb7..87fa72d 100644
--- a/erpnext/stock/doctype/item/item.py
+++ b/erpnext/stock/doctype/item/item.py
@@ -1155,7 +1155,7 @@
bin_list = frappe.db.sql(
"""
- select * from tabBin where item_code = %s
+ select * from `tabBin` where item_code = %s
and (reserved_qty > 0 or ordered_qty > 0 or indented_qty > 0 or planned_qty > 0)
and stock_uom != %s
""",
@@ -1171,7 +1171,7 @@
)
# No SLE or documents against item. Bin UOM can be changed safely.
- frappe.db.sql("""update tabBin set stock_uom=%s where item_code=%s""", (stock_uom, item))
+ frappe.db.sql("""update `tabBin` set stock_uom=%s where item_code=%s""", (stock_uom, item))
def get_item_defaults(item_code, company):
diff --git a/erpnext/stock/doctype/item/test_item.py b/erpnext/stock/doctype/item/test_item.py
index aa0a549..3366c73 100644
--- a/erpnext/stock/doctype/item/test_item.py
+++ b/erpnext/stock/doctype/item/test_item.py
@@ -381,8 +381,8 @@
frappe.delete_doc_if_exists("Item Attribute", "Test Item Length")
frappe.db.sql(
- '''delete from `tabItem Variant Attribute`
- where attribute="Test Item Length"'''
+ """delete from `tabItem Variant Attribute`
+ where attribute='Test Item Length' """
)
frappe.flags.attribute_values = None
@@ -800,6 +800,7 @@
item_code,
is_stock_item=1,
valuation_rate=0,
+ stock_uom="Nos",
warehouse="_Test Warehouse - _TC",
is_customer_provided_item=None,
customer=None,
@@ -815,6 +816,7 @@
item.item_name = item_code
item.description = item_code
item.item_group = "All Item Groups"
+ item.stock_uom = stock_uom
item.is_stock_item = is_stock_item
item.is_fixed_asset = is_fixed_asset
item.asset_category = asset_category
diff --git a/erpnext/stock/doctype/landed_cost_voucher/test_landed_cost_voucher.py b/erpnext/stock/doctype/landed_cost_voucher/test_landed_cost_voucher.py
index 1af9953..1ba8011 100644
--- a/erpnext/stock/doctype/landed_cost_voucher/test_landed_cost_voucher.py
+++ b/erpnext/stock/doctype/landed_cost_voucher/test_landed_cost_voucher.py
@@ -24,7 +24,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
get_multiple_items=True,
get_taxes_and_charges=True,
)
@@ -195,7 +195,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
get_multiple_items=True,
get_taxes_and_charges=True,
do_not_submit=True,
@@ -280,7 +280,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
do_not_save=True,
)
pr.items[0].cost_center = "Main - TCP1"
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index 7dc3ba0..d31d695 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -699,7 +699,7 @@
AND `company` = %(company)s
AND `name` like %(txt)s
ORDER BY
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), name
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end) name
LIMIT
%(start)s, %(page_length)s""",
{
diff --git a/erpnext/stock/doctype/purchase_receipt/test_purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/test_purchase_receipt.py
index f500187..d0d115d 100644
--- a/erpnext/stock/doctype/purchase_receipt/test_purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/test_purchase_receipt.py
@@ -272,7 +272,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
get_multiple_items=True,
get_taxes_and_charges=True,
)
@@ -346,13 +346,13 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
)
return_pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
is_return=1,
return_against=pr.name,
qty=-2,
@@ -433,13 +433,13 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
)
return_pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
is_return=1,
return_against=pr.name,
qty=-5,
@@ -475,7 +475,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
qty=2,
rejected_qty=2,
rejected_warehouse=rejected_warehouse,
@@ -484,7 +484,7 @@
return_pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
is_return=1,
return_against=pr.name,
qty=-2,
@@ -811,7 +811,7 @@
cost_center=cost_center,
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
)
stock_in_hand_account = get_inventory_account(pr.company, pr.get("items")[0].warehouse)
@@ -835,7 +835,7 @@
pr = make_purchase_receipt(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
- supplier_warehouse="Work in Progress - TCP1",
+ supplier_warehouse="Work In Progress - TCP1",
)
stock_in_hand_account = get_inventory_account(pr.company, pr.get("items")[0].warehouse)
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 f0de04b..c97dbee 100644
--- a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
+++ b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
@@ -252,6 +252,7 @@
"width": "100px"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -265,6 +266,7 @@
"width": "100px"
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "Conversion Factor",
@@ -547,6 +549,7 @@
"read_only": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_qty",
"fieldtype": "Float",
"label": "Accepted Qty in Stock UOM",
@@ -881,7 +884,7 @@
"fieldtype": "Column Break"
},
{
- "depends_on": "returned_qty",
+ "depends_on": "doc.returned_qty",
"fieldname": "returned_qty",
"fieldtype": "Float",
"label": "Returned Qty in Stock UOM",
@@ -890,6 +893,7 @@
"read_only": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "received_stock_qty",
"fieldtype": "Float",
"label": "Received Qty in Stock UOM",
@@ -997,7 +1001,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-04-11 13:07:32.061402",
+ "modified": "2022-06-17 05:32:16.483178",
"modified_by": "Administrator",
"module": "Stock",
"name": "Purchase Receipt Item",
diff --git a/erpnext/stock/doctype/repost_item_valuation/repost_item_valuation.py b/erpnext/stock/doctype/repost_item_valuation/repost_item_valuation.py
index ea24b47..b1017d2 100644
--- a/erpnext/stock/doctype/repost_item_valuation/repost_item_valuation.py
+++ b/erpnext/stock/doctype/repost_item_valuation/repost_item_valuation.py
@@ -87,6 +87,7 @@
self.current_index = 0
self.distinct_item_and_warehouse = None
self.items_to_be_repost = None
+ self.gl_reposting_index = 0
self.db_update()
def deduplicate_similar_repost(self):
diff --git a/erpnext/stock/doctype/repost_item_valuation/test_repost_item_valuation.py b/erpnext/stock/doctype/repost_item_valuation/test_repost_item_valuation.py
index 3c74619..edd2553 100644
--- a/erpnext/stock/doctype/repost_item_valuation/test_repost_item_valuation.py
+++ b/erpnext/stock/doctype/repost_item_valuation/test_repost_item_valuation.py
@@ -7,7 +7,7 @@
import frappe
from frappe.tests.utils import FrappeTestCase
from frappe.utils import nowdate
-from frappe.utils.data import today
+from frappe.utils.data import add_to_date, today
from erpnext.accounts.utils import repost_gle_for_stock_vouchers
from erpnext.controllers.stock_controller import create_item_wise_repost_entries
@@ -17,10 +17,11 @@
in_configured_timeslot,
)
from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry
+from erpnext.stock.tests.test_utils import StockTestMixin
from erpnext.stock.utils import PendingRepostingError
-class TestRepostItemValuation(FrappeTestCase):
+class TestRepostItemValuation(FrappeTestCase, StockTestMixin):
def tearDown(self):
frappe.flags.dont_execute_stock_reposts = False
@@ -225,3 +226,49 @@
repost_gle_for_stock_vouchers(stock_vouchers=vouchers, posting_date=posting_date, repost_doc=doc)
self.assertNotIn(call("gl_reposting_index", 1), doc.db_set.mock_calls)
+
+ def test_gl_complete_gl_reposting(self):
+ from erpnext.accounts import utils
+
+ # lower numbers to simplify test
+ orig_chunk_size = utils.GL_REPOSTING_CHUNK
+ utils.GL_REPOSTING_CHUNK = 2
+ self.addCleanup(setattr, utils, "GL_REPOSTING_CHUNK", orig_chunk_size)
+
+ item = self.make_item().name
+
+ company = "_Test Company with perpetual inventory"
+
+ for _ in range(10):
+ make_stock_entry(item=item, company=company, qty=1, rate=10, target="Stores - TCP1")
+
+ # consume
+ consumption = make_stock_entry(item=item, company=company, qty=1, source="Stores - TCP1")
+
+ self.assertGLEs(
+ consumption,
+ [{"credit": 10, "debit": 0}],
+ gle_filters={"account": "Stock In Hand - TCP1"},
+ )
+
+ # backdated receipt
+ backdated_receipt = make_stock_entry(
+ item=item,
+ company=company,
+ qty=1,
+ rate=50,
+ target="Stores - TCP1",
+ posting_date=add_to_date(today(), days=-1),
+ )
+ self.assertGLEs(
+ backdated_receipt,
+ [{"credit": 0, "debit": 50}],
+ gle_filters={"account": "Stock In Hand - TCP1"},
+ )
+
+ # check that original consumption GLe is updated
+ self.assertGLEs(
+ consumption,
+ [{"credit": 50, "debit": 0}],
+ gle_filters={"account": "Stock In Hand - TCP1"},
+ )
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index d3f15e7..a476a8a 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -613,7 +613,7 @@
)
+ "<br><br>"
+ _("Available quantity is {0}, you need {1}").format(
- frappe.bold(d.actual_qty), frappe.bold(d.transfer_qty)
+ frappe.bold(flt(d.actual_qty, d.precision("actual_qty"))), frappe.bold(d.transfer_qty)
),
NegativeStockError,
title=_("Insufficient Stock"),
diff --git a/erpnext/stock/doctype/stock_entry_detail/stock_entry_detail.json b/erpnext/stock/doctype/stock_entry_detail/stock_entry_detail.json
index efde46d..5fe11a2 100644
--- a/erpnext/stock/doctype/stock_entry_detail/stock_entry_detail.json
+++ b/erpnext/stock/doctype/stock_entry_detail/stock_entry_detail.json
@@ -234,6 +234,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "Conversion Factor",
@@ -243,6 +244,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "stock_uom",
"fieldtype": "Link",
"label": "Stock UOM",
@@ -254,6 +256,7 @@
"reqd": 1
},
{
+ "depends_on": "eval:doc.uom != doc.stock_uom",
"fieldname": "transfer_qty",
"fieldtype": "Float",
"label": "Qty as per Stock UOM",
@@ -566,7 +569,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-02-26 00:51:24.963653",
+ "modified": "2022-06-17 05:06:33.621264",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Entry Detail",
diff --git a/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
index 83d3669..1410da5 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
@@ -42,6 +42,9 @@
"delete from `tabBin` where item_code in (%s)" % (", ".join(["%s"] * len(items))), items
)
+ def tearDown(self):
+ frappe.db.rollback()
+
def test_item_cost_reposting(self):
company = "_Test Company"
@@ -1175,6 +1178,93 @@
)
self.assertEqual(abs(sles[0].stock_value_difference), sles[1].stock_value_difference)
+ @change_settings("System Settings", {"float_precision": 4})
+ def test_negative_qty_with_precision(self):
+ "Test if system precision is respected while validating negative qty."
+ from erpnext.stock.doctype.item.test_item import create_item
+ from erpnext.stock.utils import get_stock_balance
+
+ item_code = "ItemPrecisionTest"
+ warehouse = "_Test Warehouse - _TC"
+ create_item(item_code, is_stock_item=1, stock_uom="Kg")
+
+ create_stock_reconciliation(item_code=item_code, warehouse=warehouse, qty=559.8327, rate=100)
+
+ make_stock_entry(item_code=item_code, source=warehouse, qty=470.84, rate=100)
+ self.assertEqual(get_stock_balance(item_code, warehouse), 88.9927)
+
+ settings = frappe.get_doc("System Settings")
+ settings.float_precision = 3
+ settings.save()
+
+ # To deliver 100 qty we fall short of 11.0073 qty (11.007 with precision 3)
+ # Stock up with 11.007 (balance in db becomes 99.9997, on UI it will show as 100)
+ make_stock_entry(item_code=item_code, target=warehouse, qty=11.007, rate=100)
+ self.assertEqual(get_stock_balance(item_code, warehouse), 99.9997)
+
+ # See if delivery note goes through
+ # Negative qty error should not be raised as 99.9997 is 100 with precision 3 (system precision)
+ dn = create_delivery_note(
+ item_code=item_code,
+ qty=100,
+ rate=150,
+ warehouse=warehouse,
+ company="_Test Company",
+ expense_account="Cost of Goods Sold - _TC",
+ cost_center="Main - _TC",
+ do_not_submit=True,
+ )
+ dn.submit()
+
+ self.assertEqual(flt(get_stock_balance(item_code, warehouse), 3), 0.000)
+
+ @change_settings("System Settings", {"float_precision": 4})
+ def test_future_negative_qty_with_precision(self):
+ """
+ Ledger:
+ | Voucher | Qty | Balance
+ -------------------
+ | Reco | 559.8327| 559.8327
+ | SE | -470.84 | [Backdated] (new bal: 88.9927)
+ | SE | 11.007 | 570.8397 (new bal: 99.9997)
+ | DN | -100 | 470.8397 (new bal: -0.0003)
+
+ Check if future negative qty is asserted as per precision 3.
+ -0.0003 should be considered as 0.000
+ """
+ from erpnext.stock.doctype.item.test_item import create_item
+
+ item_code = "ItemPrecisionTest"
+ warehouse = "_Test Warehouse - _TC"
+ create_item(item_code, is_stock_item=1, stock_uom="Kg")
+
+ create_stock_reconciliation(
+ item_code=item_code,
+ warehouse=warehouse,
+ qty=559.8327,
+ rate=100,
+ posting_date=add_days(today(), -2),
+ )
+ make_stock_entry(item_code=item_code, target=warehouse, qty=11.007, rate=100)
+ create_delivery_note(
+ item_code=item_code,
+ qty=100,
+ rate=150,
+ warehouse=warehouse,
+ company="_Test Company",
+ expense_account="Cost of Goods Sold - _TC",
+ cost_center="Main - _TC",
+ )
+
+ settings = frappe.get_doc("System Settings")
+ settings.float_precision = 3
+ settings.save()
+
+ # Make backdated SE and make sure SE goes through as per precision (no negative qty error)
+ make_stock_entry(
+ item_code=item_code, source=warehouse, qty=470.84, rate=100, posting_date=add_days(today(), -1)
+ )
+
def create_repack_entry(**args):
args = frappe._dict(args)
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index bd60cf0..23e0f1e 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -611,7 +611,7 @@
select
i.name as item_code, i.item_name, bin.warehouse as warehouse, i.has_serial_no, i.has_batch_no
from
- tabBin bin, tabItem i
+ `tabBin` bin, `tabItem` i
where
i.name = bin.item_code
and IFNULL(i.disabled, 0) = 0
@@ -629,7 +629,7 @@
select
i.name as item_code, i.item_name, id.default_warehouse as warehouse, i.has_serial_no, i.has_batch_no
from
- tabItem i, `tabItem Default` id
+ `tabItem` i, `tabItem Default` id
where
i.name = id.parent
and exists(
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index df16643..ab784ca 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -161,8 +161,7 @@
fields = ["name as value", "is_group as expandable"]
filters = [
- ["docstatus", "<", "2"],
- ['ifnull(`parent_warehouse`, "")', "=", parent],
+ ["ifnull(`parent_warehouse`, '')", "=", parent],
["company", "in", (company, None, "")],
]
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index c23548c..e83182f 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -63,18 +63,16 @@
item = frappe.get_cached_doc("Item", args.item_code)
validate_item_details(args, item)
- out = get_basic_details(args, item, overwrite_warehouse)
-
if isinstance(doc, str):
doc = json.loads(doc)
- if doc and doc.get("doctype") == "Purchase Invoice":
- args["bill_date"] = doc.get("bill_date")
-
if doc:
- args["posting_date"] = doc.get("posting_date")
- args["transaction_date"] = doc.get("transaction_date")
+ args["transaction_date"] = doc.get("transaction_date") or doc.get("posting_date")
+ if doc.get("doctype") == "Purchase Invoice":
+ args["bill_date"] = doc.get("bill_date")
+
+ out = get_basic_details(args, item, overwrite_warehouse)
get_item_tax_template(args, item, out)
out["item_tax_rate"] = get_item_tax_map(
args.company,
@@ -601,9 +599,7 @@
if tax.valid_from or tax.maximum_net_rate:
# In purchase Invoice first preference will be given to supplier invoice date
# if supplier date is not present then posting date
- validation_date = (
- args.get("transaction_date") or args.get("bill_date") or args.get("posting_date")
- )
+ validation_date = args.get("bill_date") or args.get("transaction_date")
if getdate(tax.valid_from) <= getdate(validation_date) and is_within_valid_range(args, tax):
taxes_with_validity.append(tax)
@@ -896,14 +892,10 @@
conditions += """ and %(transaction_date)s between
ifnull(valid_from, '2000-01-01') and ifnull(valid_upto, '2500-12-31')"""
- if args.get("posting_date"):
- conditions += """ and %(posting_date)s between
- ifnull(valid_from, '2000-01-01') and ifnull(valid_upto, '2500-12-31')"""
-
return frappe.db.sql(
""" select name, price_list_rate, uom
from `tabItem Price` {conditions}
- order by valid_from desc, batch_no desc, uom desc """.format(
+ order by valid_from desc, ifnull(batch_no, '') desc, uom desc """.format(
conditions=conditions
),
args,
@@ -926,7 +918,6 @@
"supplier": args.get("supplier"),
"uom": args.get("uom"),
"transaction_date": args.get("transaction_date"),
- "posting_date": args.get("posting_date"),
"batch_no": args.get("batch_no"),
}
@@ -1357,12 +1348,22 @@
@frappe.whitelist()
def get_default_bom(item_code=None):
- if item_code:
- bom = frappe.db.get_value(
- "BOM", {"docstatus": 1, "is_default": 1, "is_active": 1, "item": item_code}
+ def _get_bom(item):
+ bom = frappe.get_all(
+ "BOM", dict(item=item, is_active=True, is_default=True, docstatus=1), limit=1
)
- if bom:
- return bom
+ return bom[0].name if bom else None
+
+ if not item_code:
+ return
+
+ bom_name = _get_bom(item_code)
+
+ template_item = frappe.db.get_value("Item", item_code, "variant_of")
+ if not bom_name and template_item:
+ bom_name = _get_bom(template_item)
+
+ return bom_name
@frappe.whitelist()
diff --git a/erpnext/stock/reorder_item.py b/erpnext/stock/reorder_item.py
index f19c75f..136c78f 100644
--- a/erpnext/stock/reorder_item.py
+++ b/erpnext/stock/reorder_item.py
@@ -105,7 +105,7 @@
for item_code, warehouse, projected_qty in frappe.db.sql(
"""select item_code, warehouse, projected_qty
from tabBin where item_code in ({0})
- and (warehouse != "" and warehouse is not null)""".format(
+ and (warehouse != '' and warehouse is not null)""".format(
", ".join(["%s"] * len(items_to_consider))
),
items_to_consider,
diff --git a/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py b/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
index bcc2139..b68db35 100644
--- a/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
+++ b/erpnext/stock/report/incorrect_balance_qty_after_transaction/incorrect_balance_qty_after_transaction.py
@@ -73,7 +73,7 @@
"Stock Ledger Entry",
fields=fields,
filters=filters,
- order_by="timestamp(posting_date, posting_time) asc, creation asc",
+ order_by="posting_date asc, posting_time asc, creation asc",
)
diff --git a/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py b/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
index 78c6961..39d84a7 100644
--- a/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
+++ b/erpnext/stock/report/incorrect_serial_no_valuation/incorrect_serial_no_valuation.py
@@ -106,7 +106,7 @@
"Stock Ledger Entry",
fields=fields,
filters=filters,
- order_by="timestamp(posting_date, posting_time) asc, creation asc",
+ order_by="posting_date asc, posting_time asc, creation asc",
)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 409e238..ef1642e 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -238,7 +238,7 @@
sl_entries = frappe.db.sql(
"""
SELECT
- concat_ws(" ", posting_date, posting_time) AS date,
+ concat_ws(' ', posting_date, posting_time) AS date,
item_code,
warehouse,
actual_qty,
diff --git a/erpnext/stock/stock_balance.py b/erpnext/stock/stock_balance.py
index e05d1c3..14cedd2 100644
--- a/erpnext/stock/stock_balance.py
+++ b/erpnext/stock/stock_balance.py
@@ -118,7 +118,7 @@
select qty, parent_detail_docname, parent, name
from `tabPacked Item` dnpi_in
where item_code = %s and warehouse = %s
- and parenttype="Sales Order"
+ and parenttype='Sales Order'
and item_code != parent_item
and exists (select * from `tabSales Order` so
where name = dnpi_in.parent and docstatus = 1 and status != 'Closed')
@@ -194,7 +194,7 @@
planned_qty = frappe.db.sql(
"""
select sum(qty - produced_qty) from `tabWork Order`
- where production_item = %s and fg_warehouse = %s and status not in ("Stopped", "Completed", "Closed")
+ where production_item = %s and fg_warehouse = %s and status not in ('Stopped', 'Completed', 'Closed')
and docstatus=1 and qty > produced_qty""",
(item_code, warehouse),
)
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 8d82c73..c2e700a 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -1,4 +1,4 @@
-# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
import copy
@@ -370,7 +370,7 @@
self.args["name"] = self.args.sle_id
self.company = frappe.get_cached_value("Warehouse", self.args.warehouse, "company")
- self.get_precision()
+ self.set_precision()
self.valuation_method = get_valuation_method(self.item_code)
self.new_items_found = False
@@ -381,10 +381,10 @@
self.initialize_previous_data(self.args)
self.build()
- def get_precision(self):
- company_base_currency = frappe.get_cached_value("Company", self.company, "default_currency")
- self.precision = get_field_precision(
- frappe.get_meta("Stock Ledger Entry").get_field("stock_value"), currency=company_base_currency
+ def set_precision(self):
+ self.flt_precision = cint(frappe.db.get_default("float_precision")) or 2
+ self.currency_precision = get_field_precision(
+ frappe.get_meta("Stock Ledger Entry").get_field("stock_value")
)
def initialize_previous_data(self, args):
@@ -581,7 +581,7 @@
self.update_queue_values(sle)
# rounding as per precision
- self.wh_data.stock_value = flt(self.wh_data.stock_value, self.precision)
+ self.wh_data.stock_value = flt(self.wh_data.stock_value, self.currency_precision)
if not self.wh_data.qty_after_transaction:
self.wh_data.stock_value = 0.0
stock_value_difference = self.wh_data.stock_value - self.wh_data.prev_stock_value
@@ -605,6 +605,7 @@
will not consider cancelled entries
"""
diff = self.wh_data.qty_after_transaction + flt(sle.actual_qty)
+ diff = flt(diff, self.flt_precision) # respect system precision
if diff < 0 and abs(diff) > 0.0001:
# negative stock!
@@ -1420,7 +1421,8 @@
return
neg_sle = get_future_sle_with_negative_qty(args)
- if neg_sle:
+
+ if is_negative_with_precision(neg_sle):
message = _(
"{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction."
).format(
@@ -1438,7 +1440,7 @@
return
neg_batch_sle = get_future_sle_with_negative_batch_qty(args)
- if neg_batch_sle:
+ if is_negative_with_precision(neg_batch_sle, is_batch=True):
message = _(
"{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction."
).format(
@@ -1452,6 +1454,22 @@
frappe.throw(message, NegativeStockError, title=_("Insufficient Stock for Batch"))
+def is_negative_with_precision(neg_sle, is_batch=False):
+ """
+ Returns whether system precision rounded qty is insufficient.
+ E.g: -0.0003 in precision 3 (0.000) is sufficient for the user.
+ """
+
+ if not neg_sle:
+ return False
+
+ field = "cumulative_total" if is_batch else "qty_after_transaction"
+ precision = cint(frappe.db.get_default("float_precision")) or 2
+ qty_deficit = flt(neg_sle[0][field], precision)
+
+ return qty_deficit < 0 and abs(qty_deficit) > 0.0001
+
+
def get_future_sle_with_negative_qty(args):
return frappe.db.sql(
"""
diff --git a/erpnext/stock/tests/test_utils.py b/erpnext/stock/tests/test_utils.py
index b046dbd..4e93ac9 100644
--- a/erpnext/stock/tests/test_utils.py
+++ b/erpnext/stock/tests/test_utils.py
@@ -26,6 +26,7 @@
filters=filters,
order_by="timestamp(posting_date, posting_time), creation",
)
+ self.assertGreaterEqual(len(sles), len(expected_sles))
for exp_sle, act_sle in zip(expected_sles, sles):
for k, v in exp_sle.items():
@@ -49,7 +50,7 @@
filters=filters,
order_by=order_by or "posting_date, creation",
)
-
+ self.assertGreaterEqual(len(actual_gles), len(expected_gles))
for exp_gle, act_gle in zip(expected_gles, actual_gles):
for k, exp_value in exp_gle.items():
act_value = act_gle[k]
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 6d8fdaa..9fb3be5 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -499,7 +499,7 @@
def get_incoming_outgoing_rate_for_cancel(item_code, voucher_type, voucher_no, voucher_detail_no):
outgoing_rate = frappe.db.sql(
- """SELECT abs(stock_value_difference / actual_qty)
+ """SELECT CASE WHEN actual_qty = 0 THEN 0 ELSE abs(stock_value_difference / actual_qty) END
FROM `tabStock Ledger Entry`
WHERE voucher_type = %s and voucher_no = %s
and item_code = %s and voucher_detail_no = %s
diff --git a/erpnext/support/doctype/issue/issue.py b/erpnext/support/doctype/issue/issue.py
index 08a06b1..7f3e0cf 100644
--- a/erpnext/support/doctype/issue/issue.py
+++ b/erpnext/support/doctype/issue/issue.py
@@ -11,6 +11,8 @@
from frappe.email.inbox import link_communication_to_document
from frappe.model.document import Document
from frappe.model.mapper import get_mapped_doc
+from frappe.query_builder import Interval
+from frappe.query_builder.functions import Now
from frappe.utils import date_diff, get_datetime, now_datetime, time_diff_in_seconds
from frappe.utils.user import is_website_user
@@ -190,15 +192,17 @@
frappe.db.get_value("Support Settings", "Support Settings", "close_issue_after_days") or 7
)
- issues = frappe.db.sql(
- """ select name from tabIssue where status='Replied' and
- modified<DATE_SUB(CURDATE(), INTERVAL %s DAY) """,
- (auto_close_after_days),
- as_dict=True,
- )
+ table = frappe.qb.DocType("Issue")
+ issues = (
+ frappe.qb.from_(table)
+ .select(table.name)
+ .where(
+ (table.modified < (Now() - Interval(days=auto_close_after_days))) & (table.status == "Replied")
+ )
+ ).run(pluck=True)
for issue in issues:
- doc = frappe.get_doc("Issue", issue.get("name"))
+ doc = frappe.get_doc("Issue", issue)
doc.status = "Closed"
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
diff --git a/erpnext/tests/test_init.py b/erpnext/tests/test_init.py
index 4d5fced..18ce93a 100644
--- a/erpnext/tests/test_init.py
+++ b/erpnext/tests/test_init.py
@@ -45,3 +45,8 @@
from frappe.tests.test_translate import verify_translation_files
verify_translation_files("erpnext")
+
+ def test_patches(self):
+ from frappe.tests.test_patches import check_patch_files
+
+ check_patch_files("erpnext")
diff --git a/sponsors.md b/sponsors.md
index 125b358..57adc8d 100644
--- a/sponsors.md
+++ b/sponsors.md
@@ -61,5 +61,13 @@
Bulk edit via export-import in Bank Reconciliation <a href="https://github.com/frappe/erpnext/issues/1938">#4356</a>
</td>
</tr>
+ <tr>
+ <td style="width: 30%">
+ <a href="https://www.sapconinstruments.com/">Sapcon Instruments Pvt Ltd</a>
+ </td>
+ <td>
+ Level wise BOM Cost Updation and Performance Enhancement <a href="https://github.com/frappe/erpnext/pull/31072">#31072</a>
+ </td>
+ </tr>
</tbody>
</table>