Merge branch 'develop' into subcontracting
diff --git a/.github/stale.yml b/.github/stale.yml
index fbf6447..da15d32 100644
--- a/.github/stale.yml
+++ b/.github/stale.yml
@@ -24,14 +24,4 @@
:) Also, even if it is closed, you can always reopen the PR when you're
ready. Thank you for contributing.
-issues:
- daysUntilStale: 90
- daysUntilClose: 7
- exemptLabels:
- - valid
- - to-validate
- - QA
- markComment: >
- This issue has been automatically marked as inactive because it has not had
- recent activity and it wasn't validated by maintainer team. It will be
- closed within a week if no further activity occurs.
+only: pulls
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 0d086de..5c8ab64 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -544,7 +544,16 @@
from_repost=from_repost,
)
elif self.docstatus == 2:
+ provisional_entries = [a for a in gl_entries if a.voucher_type == "Purchase Receipt"]
make_reverse_gl_entries(voucher_type=self.doctype, voucher_no=self.name)
+ if provisional_entries:
+ for entry in provisional_entries:
+ frappe.db.set_value(
+ "GL Entry",
+ {"voucher_type": "Purchase Receipt", "voucher_detail_no": entry.voucher_detail_no},
+ "is_cancelled",
+ 1,
+ )
if update_outstanding == "No":
update_outstanding_amt(
@@ -1126,7 +1135,7 @@
# Stock ledger value is not matching with the warehouse amount
if (
self.update_stock
- and voucher_wise_stock_value.get(item.name)
+ and voucher_wise_stock_value.get((item.name, item.warehouse))
and warehouse_debit_amount
!= flt(voucher_wise_stock_value.get((item.name, item.warehouse)), net_amt_precision)
):
diff --git a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
index bd4e93a..02ea3b6 100644
--- a/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/test_purchase_invoice.py
@@ -27,12 +27,13 @@
make_purchase_receipt,
)
from erpnext.stock.doctype.stock_entry.test_stock_entry import get_qty_after_transaction
+from erpnext.stock.tests.test_utils import StockTestMixin
test_dependencies = ["Item", "Cost Center", "Payment Term", "Payment Terms Template"]
test_ignore = ["Serial No"]
-class TestPurchaseInvoice(unittest.TestCase):
+class TestPurchaseInvoice(unittest.TestCase, StockTestMixin):
@classmethod
def setUpClass(self):
unlink_payment_on_cancel_of_invoice()
@@ -662,6 +663,80 @@
self.assertEqual(expected_values[gle.account][0], gle.debit)
self.assertEqual(expected_values[gle.account][1], gle.credit)
+ def test_standalone_return_using_pi(self):
+ from erpnext.stock.doctype.stock_entry.test_stock_entry import make_stock_entry
+
+ item = self.make_item().name
+ company = "_Test Company with perpetual inventory"
+ warehouse = "Stores - TCP1"
+
+ make_stock_entry(item_code=item, target=warehouse, qty=50, rate=120)
+
+ return_pi = make_purchase_invoice(
+ is_return=1,
+ item=item,
+ qty=-10,
+ update_stock=1,
+ rate=100,
+ company=company,
+ warehouse=warehouse,
+ cost_center="Main - TCP1",
+ )
+
+ # assert that stock consumption is with actual rate
+ self.assertGLEs(
+ return_pi,
+ [{"credit": 1200, "debit": 0}],
+ gle_filters={"account": "Stock In Hand - TCP1"},
+ )
+
+ # assert loss booked in COGS
+ self.assertGLEs(
+ return_pi,
+ [{"credit": 0, "debit": 200}],
+ gle_filters={"account": "Cost of Goods Sold - TCP1"},
+ )
+
+ def test_return_with_lcv(self):
+ from erpnext.controllers.sales_and_purchase_return import make_return_doc
+ from erpnext.stock.doctype.landed_cost_voucher.test_landed_cost_voucher import (
+ create_landed_cost_voucher,
+ )
+
+ item = self.make_item().name
+ company = "_Test Company with perpetual inventory"
+ warehouse = "Stores - TCP1"
+ cost_center = "Main - TCP1"
+
+ pi = make_purchase_invoice(
+ item=item,
+ company=company,
+ warehouse=warehouse,
+ cost_center=cost_center,
+ update_stock=1,
+ qty=10,
+ rate=100,
+ )
+
+ # Create landed cost voucher - will increase valuation of received item by 10
+ create_landed_cost_voucher("Purchase Invoice", pi.name, pi.company, charges=100)
+ return_pi = make_return_doc(pi.doctype, pi.name)
+ return_pi.save().submit()
+
+ # assert that stock consumption is with actual in rate
+ self.assertGLEs(
+ return_pi,
+ [{"credit": 1100, "debit": 0}],
+ gle_filters={"account": "Stock In Hand - TCP1"},
+ )
+
+ # assert loss booked in COGS
+ self.assertGLEs(
+ return_pi,
+ [{"credit": 0, "debit": 100}],
+ gle_filters={"account": "Cost of Goods Sold - TCP1"},
+ )
+
def test_multi_currency_gle(self):
pi = make_purchase_invoice(
supplier="_Test Supplier USD",
@@ -1471,6 +1546,18 @@
check_gl_entries(self, pr.name, expected_gle_for_purchase_receipt, pr.posting_date)
+ # Cancel purchase invoice to check reverse provisional entry cancellation
+ pi.cancel()
+
+ expected_gle_for_purchase_receipt_post_pi_cancel = [
+ ["Provision Account - _TC", 0, 250, pi.posting_date],
+ ["_Test Account Cost for Goods Sold - _TC", 250, 0, pi.posting_date],
+ ]
+
+ check_gl_entries(
+ self, pr.name, expected_gle_for_purchase_receipt_post_pi_cancel, pr.posting_date
+ )
+
company.enable_provisional_accounting_for_non_stock_items = 0
company.save()
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
index 2e7213f..ac70666 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -443,12 +443,6 @@
] # nosec
-def get_deducted_taxes():
- return frappe.db.sql_list(
- "select name from `tabPurchase Taxes and Charges` where add_deduct_tax = 'Deduct'"
- )
-
-
def get_tax_accounts(
item_list,
columns,
@@ -462,6 +456,7 @@
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
+ add_deduct_tax = "charge_type"
tax_amount_precision = (
get_field_precision(
@@ -477,13 +472,13 @@
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0"
+ add_deduct_tax = "add_deduct_tax"
- deducted_tax = get_deducted_taxes()
tax_details = frappe.db.sql(
"""
select
name, parent, description, item_wise_tax_detail,
- charge_type, base_tax_amount_after_discount_amount
+ charge_type, {add_deduct_tax}, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
@@ -491,12 +486,22 @@
and parent in (%s)
%s
order by description
- """
+ """.format(
+ add_deduct_tax=add_deduct_tax
+ )
% (tax_doctype, "%s", ", ".join(["%s"] * len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)),
)
- for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
+ for (
+ name,
+ parent,
+ description,
+ item_wise_tax_detail,
+ charge_type,
+ add_deduct_tax,
+ tax_amount,
+ ) in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
@@ -529,7 +534,9 @@
if item_tax_amount:
tax_value = flt(item_tax_amount, tax_amount_precision)
tax_value = (
- tax_value * -1 if (doctype == "Purchase Invoice" and name in deducted_tax) else tax_value
+ tax_value * -1
+ if (doctype == "Purchase Invoice" and add_deduct_tax == "Deduct")
+ else tax_value
)
itemised_tax.setdefault(d.name, {})[description] = frappe._dict(
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index 34b3f03..33bd3c7 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -346,9 +346,13 @@
def get_conditions(filters):
conditions = ""
+ accounting_dimensions = get_accounting_dimensions(as_list=False) or []
+ accounting_dimensions_list = [d.fieldname for d in accounting_dimensions]
+
if filters.get("company"):
conditions += " and company=%(company)s"
- if filters.get("customer"):
+
+ if filters.get("customer") and "customer" not in accounting_dimensions_list:
conditions += " and customer = %(customer)s"
if filters.get("from_date"):
@@ -359,32 +363,18 @@
if filters.get("owner"):
conditions += " and owner = %(owner)s"
- if filters.get("mode_of_payment"):
- conditions += """ and exists(select name from `tabSales Invoice Payment`
- where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
+ def get_sales_invoice_item_field_condition(field, table="Sales Invoice Item") -> str:
+ if not filters.get(field) or field in accounting_dimensions_list:
+ return ""
+ return f""" and exists(select name from `tab{table}`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tab{table}`.{field}, '') = %({field})s)"""
- if filters.get("cost_center"):
- conditions += """ and exists(select name from `tabSales Invoice Item`
- where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)"""
-
- if filters.get("warehouse"):
- conditions += """ and exists(select name from `tabSales Invoice Item`
- where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
-
- if filters.get("brand"):
- conditions += """ and exists(select name from `tabSales Invoice Item`
- where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)"""
-
- if filters.get("item_group"):
- conditions += """ and exists(select name from `tabSales Invoice Item`
- where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
-
- accounting_dimensions = get_accounting_dimensions(as_list=False)
+ conditions += get_sales_invoice_item_field_condition("mode_of_payments", "Sales Invoice Payment")
+ conditions += get_sales_invoice_item_field_condition("cost_center")
+ conditions += get_sales_invoice_item_field_condition("warehouse")
+ conditions += get_sales_invoice_item_field_condition("brand")
+ conditions += get_sales_invoice_item_field_condition("item_group")
if accounting_dimensions:
common_condition = """
diff --git a/erpnext/accounts/test/test_reports.py b/erpnext/accounts/test/test_reports.py
index 19fe74f..3f06c30 100644
--- a/erpnext/accounts/test/test_reports.py
+++ b/erpnext/accounts/test/test_reports.py
@@ -28,6 +28,7 @@
("Item-wise Sales Register", {}),
("Item-wise Purchase Register", {}),
("Sales Register", {}),
+ ("Sales Register", {"item_group": "All Item Groups"}),
("Purchase Register", {}),
(
"Tax Detail",
diff --git a/erpnext/controllers/sales_and_purchase_return.py b/erpnext/controllers/sales_and_purchase_return.py
index 2eb93d1..04a0dfa 100644
--- a/erpnext/controllers/sales_and_purchase_return.py
+++ b/erpnext/controllers/sales_and_purchase_return.py
@@ -320,7 +320,7 @@
return data[0]
-def make_return_doc(doctype, source_name, target_doc=None):
+def make_return_doc(doctype: str, source_name: str, target_doc=None):
from frappe.model.mapper import get_mapped_doc
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
diff --git a/erpnext/e_commerce/redisearch_utils.py b/erpnext/e_commerce/redisearch_utils.py
index 61b4b9e..1f649c7 100644
--- a/erpnext/e_commerce/redisearch_utils.py
+++ b/erpnext/e_commerce/redisearch_utils.py
@@ -38,7 +38,7 @@
out = cache.execute_command("MODULE LIST")
parsed_output = " ".join(
- (" ".join([s.decode() for s in o if not isinstance(s, int)]) for o in out)
+ (" ".join([frappe.as_unicode(s) for s in o if not isinstance(s, int)]) for o in out)
)
return "search" in parsed_output
except Exception:
diff --git a/erpnext/hr/doctype/leave_application/leave_application.js b/erpnext/hr/doctype/leave_application/leave_application.js
index 85997a4..ee00e67 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.js
+++ b/erpnext/hr/doctype/leave_application/leave_application.js
@@ -173,7 +173,7 @@
date: frm.doc.from_date,
to_date: frm.doc.to_date,
leave_type: frm.doc.leave_type,
- consider_all_leaves_in_the_allocation_period: true
+ consider_all_leaves_in_the_allocation_period: 1
},
callback: function (r) {
if (!r.exc && r.message) {
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index cd6b168..43c2bb3 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -88,7 +88,7 @@
share_doc_with_approver(self, self.leave_approver)
def on_submit(self):
- if self.status == "Open":
+ if self.status in ["Open", "Cancelled"]:
frappe.throw(
_("Only Leave Applications with status 'Approved' and 'Rejected' can be submitted")
)
@@ -757,22 +757,6 @@
leave_allocation = {}
for d in allocation_records:
allocation = allocation_records.get(d, frappe._dict())
-
- total_allocated_leaves = (
- frappe.db.get_value(
- "Leave Allocation",
- {
- "from_date": ("<=", date),
- "to_date": (">=", date),
- "employee": employee,
- "leave_type": allocation.leave_type,
- "docstatus": 1,
- },
- "SUM(total_leaves_allocated)",
- )
- or 0
- )
-
remaining_leaves = get_leave_balance_on(
employee, d, date, to_date=allocation.to_date, consider_all_leaves_in_the_allocation_period=True
)
@@ -782,10 +766,11 @@
leaves_pending = get_leaves_pending_approval_for_period(
employee, d, allocation.from_date, end_date
)
+ expired_leaves = allocation.total_leaves_allocated - (remaining_leaves + leaves_taken)
leave_allocation[d] = {
- "total_leaves": total_allocated_leaves,
- "expired_leaves": total_allocated_leaves - (remaining_leaves + leaves_taken),
+ "total_leaves": allocation.total_leaves_allocated,
+ "expired_leaves": expired_leaves if expired_leaves > 0 else 0,
"leaves_taken": leaves_taken,
"leaves_pending_approval": leaves_pending,
"remaining_leaves": remaining_leaves,
@@ -830,7 +815,7 @@
allocation_records = get_leave_allocation_records(employee, date, leave_type)
allocation = allocation_records.get(leave_type, frappe._dict())
- end_date = allocation.to_date if consider_all_leaves_in_the_allocation_period else date
+ end_date = allocation.to_date if cint(consider_all_leaves_in_the_allocation_period) else date
cf_expiry = get_allocation_expiry_for_cf_leaves(employee, leave_type, to_date, date)
leaves_taken = get_leaves_for_period(employee, leave_type, allocation.from_date, end_date)
@@ -1117,7 +1102,7 @@
WHERE
from_date <= %(end)s AND to_date >= %(start)s <= to_date
AND docstatus < 2
- AND status != 'Rejected'
+ AND status in ('Approved', 'Open')
"""
if conditions:
@@ -1201,24 +1186,33 @@
def get_approved_leaves_for_period(employee, leave_type, from_date, to_date):
- query = """
- select employee, leave_type, from_date, to_date, total_leave_days
- from `tabLeave Application`
- where employee=%(employee)s
- and docstatus=1
- and (from_date between %(from_date)s and %(to_date)s
- or to_date between %(from_date)s and %(to_date)s
- or (from_date < %(from_date)s and to_date > %(to_date)s))
- """
- if leave_type:
- query += "and leave_type=%(leave_type)s"
-
- leave_applications = frappe.db.sql(
- query,
- {"from_date": from_date, "to_date": to_date, "employee": employee, "leave_type": leave_type},
- as_dict=1,
+ LeaveApplication = frappe.qb.DocType("Leave Application")
+ query = (
+ frappe.qb.from_(LeaveApplication)
+ .select(
+ LeaveApplication.employee,
+ LeaveApplication.leave_type,
+ LeaveApplication.from_date,
+ LeaveApplication.to_date,
+ LeaveApplication.total_leave_days,
+ )
+ .where(
+ (LeaveApplication.employee == employee)
+ & (LeaveApplication.docstatus == 1)
+ & (LeaveApplication.status == "Approved")
+ & (
+ (LeaveApplication.from_date.between(from_date, to_date))
+ | (LeaveApplication.to_date.between(from_date, to_date))
+ | ((LeaveApplication.from_date < from_date) & (LeaveApplication.to_date > to_date))
+ )
+ )
)
+ if leave_type:
+ query = query.where(LeaveApplication.leave_type == leave_type)
+
+ leave_applications = query.run(as_dict=True)
+
leave_days = 0
for leave_app in leave_applications:
if leave_app.from_date >= getdate(from_date) and leave_app.to_date <= getdate(to_date):
diff --git a/erpnext/hr/doctype/leave_application/leave_application_list.js b/erpnext/hr/doctype/leave_application/leave_application_list.js
index a3c03b1..157271a 100644
--- a/erpnext/hr/doctype/leave_application/leave_application_list.js
+++ b/erpnext/hr/doctype/leave_application/leave_application_list.js
@@ -1,13 +1,14 @@
-frappe.listview_settings['Leave Application'] = {
+frappe.listview_settings["Leave Application"] = {
add_fields: ["leave_type", "employee", "employee_name", "total_leave_days", "from_date", "to_date"],
has_indicator_for_draft: 1,
get_indicator: function (doc) {
- if (doc.status === "Approved") {
- return [__("Approved"), "green", "status,=,Approved"];
- } else if (doc.status === "Rejected") {
- return [__("Rejected"), "red", "status,=,Rejected"];
- } else {
- return [__("Open"), "red", "status,=,Open"];
- }
+ let status_color = {
+ "Approved": "green",
+ "Rejected": "red",
+ "Open": "orange",
+ "Cancelled": "red",
+ "Submitted": "blue"
+ };
+ return [__(doc.status), status_color[doc.status], "status,=," + doc.status];
}
};
diff --git a/erpnext/hr/doctype/leave_application/test_leave_application.py b/erpnext/hr/doctype/leave_application/test_leave_application.py
index 7506c61..27c5410 100644
--- a/erpnext/hr/doctype/leave_application/test_leave_application.py
+++ b/erpnext/hr/doctype/leave_application/test_leave_application.py
@@ -76,7 +76,14 @@
class TestLeaveApplication(unittest.TestCase):
def setUp(self):
- for dt in ["Leave Application", "Leave Allocation", "Salary Slip", "Leave Ledger Entry"]:
+ for dt in [
+ "Leave Application",
+ "Leave Allocation",
+ "Salary Slip",
+ "Leave Ledger Entry",
+ "Leave Period",
+ "Leave Policy Assignment",
+ ]:
frappe.db.delete(dt)
frappe.set_user("Administrator")
@@ -702,59 +709,24 @@
self.assertEqual(details.leave_balance, 30)
def test_earned_leaves_creation(self):
-
- frappe.db.sql("""delete from `tabLeave Period`""")
- frappe.db.sql("""delete from `tabLeave Policy Assignment`""")
- frappe.db.sql("""delete from `tabLeave Allocation`""")
- frappe.db.sql("""delete from `tabLeave Ledger Entry`""")
+ from erpnext.hr.utils import allocate_earned_leaves
leave_period = get_leave_period()
employee = get_employee()
leave_type = "Test Earned Leave Type"
- frappe.delete_doc_if_exists("Leave Type", "Test Earned Leave Type", force=1)
- frappe.get_doc(
- dict(
- leave_type_name=leave_type,
- doctype="Leave Type",
- is_earned_leave=1,
- earned_leave_frequency="Monthly",
- rounding=0.5,
- max_leaves_allowed=6,
- )
- ).insert()
+ make_policy_assignment(employee, leave_type, leave_period)
- leave_policy = frappe.get_doc(
- {
- "doctype": "Leave Policy",
- "title": "Test Leave Policy",
- "leave_policy_details": [{"leave_type": leave_type, "annual_allocation": 6}],
- }
- ).insert()
-
- data = {
- "assignment_based_on": "Leave Period",
- "leave_policy": leave_policy.name,
- "leave_period": leave_period.name,
- }
-
- leave_policy_assignments = create_assignment_for_multiple_employees(
- [employee.name], frappe._dict(data)
- )
-
- from erpnext.hr.utils import allocate_earned_leaves
-
- i = 0
- while i < 14:
+ for i in range(0, 14):
allocate_earned_leaves()
- i += 1
+
self.assertEqual(get_leave_balance_on(employee.name, leave_type, nowdate()), 6)
# validate earned leaves creation without maximum leaves
frappe.db.set_value("Leave Type", leave_type, "max_leaves_allowed", 0)
- i = 0
- while i < 6:
+
+ for i in range(0, 6):
allocate_earned_leaves()
- i += 1
+
self.assertEqual(get_leave_balance_on(employee.name, leave_type, nowdate()), 9)
# test to not consider current leave in leave balance while submitting
@@ -971,6 +943,54 @@
self.assertEqual(leave_allocation["remaining_leaves"], 26)
@set_holiday_list("Salary Slip Test Holiday List", "_Test Company")
+ def test_get_earned_leave_details_for_dashboard(self):
+ from erpnext.hr.utils import allocate_earned_leaves
+
+ leave_period = get_leave_period()
+ employee = get_employee()
+ leave_type = "Test Earned Leave Type"
+ leave_policy_assignments = make_policy_assignment(employee, leave_type, leave_period)
+ allocation = frappe.db.get_value(
+ "Leave Allocation",
+ {"leave_policy_assignment": leave_policy_assignments[0]},
+ "name",
+ )
+ allocation = frappe.get_doc("Leave Allocation", allocation)
+ allocation.new_leaves_allocated = 2
+ allocation.save()
+
+ for i in range(0, 6):
+ allocate_earned_leaves()
+
+ first_sunday = get_first_sunday(self.holiday_list)
+ make_leave_application(
+ employee.name, add_days(first_sunday, 1), add_days(first_sunday, 1), leave_type
+ )
+
+ details = get_leave_details(employee.name, allocation.from_date)
+ leave_allocation = details["leave_allocation"][leave_type]
+ expected = {
+ "total_leaves": 2.0,
+ "expired_leaves": 0.0,
+ "leaves_taken": 1.0,
+ "leaves_pending_approval": 0.0,
+ "remaining_leaves": 1.0,
+ }
+ self.assertEqual(leave_allocation, expected)
+
+ details = get_leave_details(employee.name, getdate())
+ leave_allocation = details["leave_allocation"][leave_type]
+
+ expected = {
+ "total_leaves": 5.0,
+ "expired_leaves": 0.0,
+ "leaves_taken": 1.0,
+ "leaves_pending_approval": 0.0,
+ "remaining_leaves": 4.0,
+ }
+ self.assertEqual(leave_allocation, expected)
+
+ @set_holiday_list("Salary Slip Test Holiday List", "_Test Company")
def test_get_leave_allocation_records(self):
employee = get_employee()
leave_type = create_leave_type(
@@ -1100,3 +1120,36 @@
)[0][0]
return first_sunday
+
+
+def make_policy_assignment(employee, leave_type, leave_period):
+ frappe.delete_doc_if_exists("Leave Type", leave_type, force=1)
+ frappe.get_doc(
+ dict(
+ leave_type_name=leave_type,
+ doctype="Leave Type",
+ is_earned_leave=1,
+ earned_leave_frequency="Monthly",
+ rounding=0.5,
+ max_leaves_allowed=6,
+ )
+ ).insert()
+
+ leave_policy = frappe.get_doc(
+ {
+ "doctype": "Leave Policy",
+ "title": "Test Leave Policy",
+ "leave_policy_details": [{"leave_type": leave_type, "annual_allocation": 6}],
+ }
+ ).insert()
+
+ data = {
+ "assignment_based_on": "Leave Period",
+ "leave_policy": leave_policy.name,
+ "leave_period": leave_period.name,
+ }
+
+ leave_policy_assignments = create_assignment_for_multiple_employees(
+ [employee.name], frappe._dict(data)
+ )
+ return leave_policy_assignments
diff --git a/erpnext/loan_management/doctype/loan/loan.js b/erpnext/loan_management/doctype/loan/loan.js
index 940a1bb..38328e6 100644
--- a/erpnext/loan_management/doctype/loan/loan.js
+++ b/erpnext/loan_management/doctype/loan/loan.js
@@ -93,6 +93,12 @@
frm.trigger("make_loan_refund");
},__('Create'));
}
+
+ if (frm.doc.status == "Loan Closure Requested" && frm.doc.is_term_loan && !frm.doc.is_secured_loan) {
+ frm.add_custom_button(__('Close Loan'), function() {
+ frm.trigger("close_unsecured_term_loan");
+ },__('Status'));
+ }
}
frm.trigger("toggle_fields");
},
@@ -174,6 +180,18 @@
})
},
+ close_unsecured_term_loan: function(frm) {
+ frappe.call({
+ args: {
+ "loan": frm.doc.name
+ },
+ method: "erpnext.loan_management.doctype.loan.loan.close_unsecured_term_loan",
+ callback: function () {
+ frm.refresh();
+ }
+ })
+ },
+
request_loan_closure: function(frm) {
frappe.confirm(__("Do you really want to close this loan"),
function() {
diff --git a/erpnext/loan_management/doctype/loan/loan.py b/erpnext/loan_management/doctype/loan/loan.py
index 3b76ba4..ac8b362 100644
--- a/erpnext/loan_management/doctype/loan/loan.py
+++ b/erpnext/loan_management/doctype/loan/loan.py
@@ -60,11 +60,11 @@
)
def validate_cost_center(self):
- if not self.cost_center and self.rate_of_interest != 0:
+ if not self.cost_center and self.rate_of_interest != 0.0:
self.cost_center = frappe.db.get_value("Company", self.company, "cost_center")
- if not self.cost_center:
- frappe.throw(_("Cost center is mandatory for loans having rate of interest greater than 0"))
+ if not self.cost_center:
+ frappe.throw(_("Cost center is mandatory for loans having rate of interest greater than 0"))
def on_submit(self):
self.link_loan_security_pledge()
@@ -342,6 +342,22 @@
return loan.as_dict()
+@frappe.whitelist()
+def close_unsecured_term_loan(loan):
+ loan_details = frappe.db.get_value(
+ "Loan", {"name": loan}, ["status", "is_term_loan", "is_secured_loan"], as_dict=1
+ )
+
+ if (
+ loan_details.status == "Loan Closure Requested"
+ and loan_details.is_term_loan
+ and not loan_details.is_secured_loan
+ ):
+ frappe.db.set_value("Loan", loan, "status", "Closed")
+ else:
+ frappe.throw(_("Cannot close this loan until full repayment"))
+
+
def close_loan(loan, total_amount_paid):
frappe.db.set_value("Loan", loan, "total_amount_paid", total_amount_paid)
frappe.db.set_value("Loan", loan, "status", "Closed")
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.py b/erpnext/manufacturing/doctype/job_card/job_card.py
index 0a9fd8a..0199a5c 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/job_card.py
@@ -621,7 +621,7 @@
self.set_status(update_status)
def set_status(self, update_status=False):
- if self.status == "On Hold":
+ if self.status == "On Hold" and self.docstatus == 0:
return
self.status = {0: "Open", 1: "Submitted", 2: "Cancelled"}[self.docstatus or 0]
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 8594ebb..5a98463 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -373,3 +373,4 @@
erpnext.patches.v13_0.set_per_billed_in_return_delivery_note
execute:frappe.delete_doc("DocType", "Naming Series")
erpnext.patches.v13_0.set_payroll_entry_status
+erpnext.patches.v13_0.job_card_status_on_hold
diff --git a/erpnext/patches/v13_0/job_card_status_on_hold.py b/erpnext/patches/v13_0/job_card_status_on_hold.py
new file mode 100644
index 0000000..8c67c3c
--- /dev/null
+++ b/erpnext/patches/v13_0/job_card_status_on_hold.py
@@ -0,0 +1,19 @@
+import frappe
+
+
+def execute():
+ job_cards = frappe.get_all(
+ "Job Card",
+ {"status": "On Hold", "docstatus": ("!=", 0)},
+ pluck="name",
+ )
+
+ for idx, job_card in enumerate(job_cards):
+ try:
+ doc = frappe.get_doc("Job Card", job_card)
+ doc.set_status()
+ doc.db_set("status", doc.status, update_modified=False)
+ if idx % 100 == 0:
+ frappe.db.commit()
+ except Exception:
+ continue
diff --git a/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py b/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
index 0acd447..8df1bb6 100644
--- a/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
+++ b/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
@@ -5,7 +5,7 @@
import frappe
from frappe import _
from frappe.model.document import Document
-from frappe.utils import add_days, cint, cstr, date_diff, getdate, rounded
+from frappe.utils import add_days, cstr, date_diff, flt, getdate, rounded
from erpnext.hr.utils import (
get_holiday_dates_for_employee,
@@ -27,11 +27,14 @@
validate_active_employee(self.employee)
self.validate_duplicate_on_payroll_period()
if not self.max_benefits:
- self.max_benefits = get_max_benefits_remaining(self.employee, self.date, self.payroll_period)
+ self.max_benefits = flt(
+ get_max_benefits_remaining(self.employee, self.date, self.payroll_period),
+ self.precision("max_benefits"),
+ )
if self.max_benefits and self.max_benefits > 0:
self.validate_max_benefit_for_component()
self.validate_prev_benefit_claim()
- if self.remaining_benefit > 0:
+ if self.remaining_benefit and self.remaining_benefit > 0:
self.validate_remaining_benefit_amount()
else:
frappe.throw(
@@ -110,7 +113,7 @@
max_benefit_amount = 0
for employee_benefit in self.employee_benefits:
self.validate_max_benefit(employee_benefit.earning_component)
- max_benefit_amount += employee_benefit.amount
+ max_benefit_amount += flt(employee_benefit.amount)
if max_benefit_amount > self.max_benefits:
frappe.throw(
_("Maximum benefit amount of employee {0} exceeds {1}").format(
@@ -125,7 +128,8 @@
benefit_amount = 0
for employee_benefit in self.employee_benefits:
if employee_benefit.earning_component == earning_component_name:
- benefit_amount += employee_benefit.amount
+ benefit_amount += flt(employee_benefit.amount)
+
prev_sal_slip_flexi_amount = get_sal_slip_total_benefit_given(
self.employee, frappe.get_doc("Payroll Period", self.payroll_period), earning_component_name
)
@@ -207,26 +211,47 @@
def calculate_lwp(employee, start_date, holidays, working_days):
lwp = 0
holidays = "','".join(holidays)
+
for d in range(working_days):
- dt = add_days(cstr(getdate(start_date)), d)
- leave = frappe.db.sql(
- """
- select t1.name, t1.half_day
- from `tabLeave Application` t1, `tabLeave Type` t2
- where t2.name = t1.leave_type
- and t2.is_lwp = 1
- and t1.docstatus = 1
- and t1.employee = %(employee)s
- and CASE WHEN t2.include_holiday != 1 THEN %(dt)s not in ('{0}') and %(dt)s between from_date and to_date
- WHEN t2.include_holiday THEN %(dt)s between from_date and to_date
- END
- """.format(
- holidays
- ),
- {"employee": employee, "dt": dt},
+ date = add_days(cstr(getdate(start_date)), d)
+
+ LeaveApplication = frappe.qb.DocType("Leave Application")
+ LeaveType = frappe.qb.DocType("Leave Type")
+
+ is_half_day = (
+ frappe.qb.terms.Case()
+ .when(
+ (
+ (LeaveApplication.half_day_date == date)
+ | (LeaveApplication.from_date == LeaveApplication.to_date)
+ ),
+ LeaveApplication.half_day,
+ )
+ .else_(0)
+ ).as_("is_half_day")
+
+ query = (
+ frappe.qb.from_(LeaveApplication)
+ .inner_join(LeaveType)
+ .on((LeaveType.name == LeaveApplication.leave_type))
+ .select(LeaveApplication.name, is_half_day)
+ .where(
+ (LeaveType.is_lwp == 1)
+ & (LeaveApplication.docstatus == 1)
+ & (LeaveApplication.status == "Approved")
+ & (LeaveApplication.employee == employee)
+ & ((LeaveApplication.from_date <= date) & (date <= LeaveApplication.to_date))
+ )
)
- if leave:
- lwp = cint(leave[0][1]) and (lwp + 0.5) or (lwp + 1)
+
+ # if it's a holiday only include if leave type has "include holiday" enabled
+ if date in holidays:
+ query = query.where((LeaveType.include_holiday == "1"))
+ leaves = query.run(as_dict=True)
+
+ if leaves:
+ lwp += 0.5 if leaves[0].is_half_day else 1
+
return lwp
diff --git a/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py b/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
index 02149ad..de8f9b6 100644
--- a/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
+++ b/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
@@ -3,6 +3,82 @@
import unittest
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days, date_diff, get_year_ending, get_year_start, getdate
-class TestEmployeeBenefitApplication(unittest.TestCase):
- pass
+from erpnext.hr.doctype.employee.test_employee import make_employee
+from erpnext.hr.doctype.holiday_list.test_holiday_list import set_holiday_list
+from erpnext.hr.doctype.leave_application.test_leave_application import get_first_sunday
+from erpnext.hr.utils import get_holiday_dates_for_employee
+from erpnext.payroll.doctype.employee_benefit_application.employee_benefit_application import (
+ calculate_lwp,
+)
+from erpnext.payroll.doctype.employee_tax_exemption_declaration.test_employee_tax_exemption_declaration import (
+ create_payroll_period,
+)
+from erpnext.payroll.doctype.salary_slip.test_salary_slip import (
+ make_holiday_list,
+ make_leave_application,
+)
+from erpnext.payroll.doctype.salary_structure.salary_structure import make_salary_slip
+from erpnext.payroll.doctype.salary_structure.test_salary_structure import make_salary_structure
+
+
+class TestEmployeeBenefitApplication(FrappeTestCase):
+ def setUp(self):
+ date = getdate()
+ make_holiday_list(from_date=get_year_start(date), to_date=get_year_ending(date))
+
+ @set_holiday_list("Salary Slip Test Holiday List", "_Test Company")
+ def test_employee_benefit_application(self):
+ payroll_period = create_payroll_period(name="_Test Payroll Period 1", company="_Test Company")
+ employee = make_employee("test_employee_benefits@salary.com", company="_Test Company")
+ first_sunday = get_first_sunday("Salary Slip Test Holiday List")
+
+ leave_application = make_leave_application(
+ employee,
+ add_days(first_sunday, 1),
+ add_days(first_sunday, 3),
+ "Leave Without Pay",
+ half_day=1,
+ half_day_date=add_days(first_sunday, 1),
+ submit=True,
+ )
+
+ frappe.db.set_value("Leave Type", "Leave Without Pay", "include_holiday", 0)
+ salary_structure = make_salary_structure(
+ "Test Employee Benefits",
+ "Monthly",
+ other_details={"max_benefits": 100000},
+ include_flexi_benefits=True,
+ employee=employee,
+ payroll_period=payroll_period,
+ )
+ salary_slip = make_salary_slip(salary_structure.name, employee=employee, posting_date=getdate())
+ salary_slip.insert()
+ salary_slip.submit()
+
+ application = make_employee_benefit_application(
+ employee, payroll_period.name, date=leave_application.to_date
+ )
+ self.assertEqual(application.employee_benefits[0].max_benefit_amount, 15000)
+
+ holidays = get_holiday_dates_for_employee(employee, payroll_period.start_date, application.date)
+ working_days = date_diff(application.date, payroll_period.start_date) + 1
+ lwp = calculate_lwp(employee, payroll_period.start_date, holidays, working_days)
+ self.assertEqual(lwp, 2.5)
+
+
+def make_employee_benefit_application(employee, payroll_period, date):
+ frappe.db.delete("Employee Benefit Application")
+
+ return frappe.get_doc(
+ {
+ "doctype": "Employee Benefit Application",
+ "employee": employee,
+ "date": date,
+ "payroll_period": payroll_period,
+ "employee_benefits": [{"earning_component": "Medical Allowance", "amount": 1500}],
+ }
+ ).insert()
diff --git a/erpnext/payroll/doctype/salary_slip/salary_slip.py b/erpnext/payroll/doctype/salary_slip/salary_slip.py
index 4c5fea1..6a35985 100644
--- a/erpnext/payroll/doctype/salary_slip/salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/salary_slip.py
@@ -465,37 +465,14 @@
)
for d in range(working_days):
- dt = add_days(cstr(getdate(self.start_date)), d)
- leave = frappe.db.sql(
- """
- SELECT t1.name,
- CASE WHEN (t1.half_day_date = %(dt)s or t1.to_date = t1.from_date)
- THEN t1.half_day else 0 END,
- t2.is_ppl,
- t2.fraction_of_daily_salary_per_leave
- FROM `tabLeave Application` t1, `tabLeave Type` t2
- WHERE t2.name = t1.leave_type
- AND (t2.is_lwp = 1 or t2.is_ppl = 1)
- AND t1.docstatus = 1
- AND t1.employee = %(employee)s
- AND ifnull(t1.salary_slip, '') = ''
- AND CASE
- WHEN t2.include_holiday != 1
- THEN %(dt)s not in ('{0}') and %(dt)s between from_date and to_date
- WHEN t2.include_holiday
- THEN %(dt)s between from_date and to_date
- END
- """.format(
- holidays
- ),
- {"employee": self.employee, "dt": dt},
- )
+ date = add_days(cstr(getdate(self.start_date)), d)
+ leave = get_lwp_or_ppl_for_date(date, self.employee, holidays)
if leave:
equivalent_lwp_count = 0
- is_half_day_leave = cint(leave[0][1])
- is_partially_paid_leave = cint(leave[0][2])
- fraction_of_daily_salary_per_leave = flt(leave[0][3])
+ is_half_day_leave = cint(leave[0].is_half_day)
+ is_partially_paid_leave = cint(leave[0].is_ppl)
+ fraction_of_daily_salary_per_leave = flt(leave[0].fraction_of_daily_salary_per_leave)
equivalent_lwp_count = (1 - daily_wages_fraction_for_half_day) if is_half_day_leave else 1
@@ -1742,3 +1719,46 @@
except Exception as e:
frappe.throw(_("Error in formula or condition: {0} in Income Tax Slab").format(e))
raise
+
+
+def get_lwp_or_ppl_for_date(date, employee, holidays):
+ LeaveApplication = frappe.qb.DocType("Leave Application")
+ LeaveType = frappe.qb.DocType("Leave Type")
+
+ is_half_day = (
+ frappe.qb.terms.Case()
+ .when(
+ (
+ (LeaveApplication.half_day_date == date)
+ | (LeaveApplication.from_date == LeaveApplication.to_date)
+ ),
+ LeaveApplication.half_day,
+ )
+ .else_(0)
+ ).as_("is_half_day")
+
+ query = (
+ frappe.qb.from_(LeaveApplication)
+ .inner_join(LeaveType)
+ .on((LeaveType.name == LeaveApplication.leave_type))
+ .select(
+ LeaveApplication.name,
+ LeaveType.is_ppl,
+ LeaveType.fraction_of_daily_salary_per_leave,
+ (is_half_day),
+ )
+ .where(
+ (((LeaveType.is_lwp == 1) | (LeaveType.is_ppl == 1)))
+ & (LeaveApplication.docstatus == 1)
+ & (LeaveApplication.status == "Approved")
+ & (LeaveApplication.employee == employee)
+ & ((LeaveApplication.salary_slip.isnull()) | (LeaveApplication.salary_slip == ""))
+ & ((LeaveApplication.from_date <= date) & (date <= LeaveApplication.to_date))
+ )
+ )
+
+ # if it's a holiday only include if leave type has "include holiday" enabled
+ if date in holidays:
+ query = query.where((LeaveType.include_holiday == "1"))
+
+ return query.run(as_dict=True)
diff --git a/erpnext/payroll/doctype/salary_slip/test_salary_slip.py b/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
index 5e3814b..a8b6bb5 100644
--- a/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
@@ -49,7 +49,7 @@
"Payroll Settings", {"payroll_based_on": "Attendance", "daily_wages_fraction_for_half_day": 0.75}
)
def test_payment_days_based_on_attendance(self):
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
emp_id = make_employee("test_payment_days_based_on_attendance@salary.com")
frappe.db.set_value("Employee", emp_id, {"relieving_date": None, "status": "Active"})
@@ -128,7 +128,7 @@
},
)
def test_payment_days_for_mid_joinee_including_holidays(self):
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -196,7 +196,7 @@
# tests mid month joining and relieving along with unmarked days
from erpnext.hr.doctype.holiday_list.holiday_list import is_holiday
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -236,7 +236,7 @@
def test_payment_days_for_mid_joinee_excluding_holidays(self):
from erpnext.hr.doctype.holiday_list.holiday_list import is_holiday
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -267,7 +267,7 @@
@change_settings("Payroll Settings", {"payroll_based_on": "Leave"})
def test_payment_days_based_on_leave_application(self):
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
emp_id = make_employee("test_payment_days_based_on_leave_application@salary.com")
frappe.db.set_value("Employee", emp_id, {"relieving_date": None, "status": "Active"})
@@ -366,7 +366,7 @@
salary_slip.submit()
salary_slip.reload()
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
days_in_month = no_of_days[0]
no_of_holidays = no_of_days[1]
@@ -441,7 +441,7 @@
@change_settings("Payroll Settings", {"include_holidays_in_total_working_days": 1})
def test_salary_slip_with_holidays_included(self):
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
make_employee("test_salary_slip_with_holidays_included@salary.com")
frappe.db.set_value(
"Employee",
@@ -473,7 +473,7 @@
@change_settings("Payroll Settings", {"include_holidays_in_total_working_days": 0})
def test_salary_slip_with_holidays_excluded(self):
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
make_employee("test_salary_slip_with_holidays_excluded@salary.com")
frappe.db.set_value(
"Employee",
@@ -510,7 +510,7 @@
create_salary_structure_assignment,
)
- no_of_days = self.get_no_of_days()
+ no_of_days = get_no_of_days()
# set joinng date in the same month
employee = make_employee("test_payment_days@salary.com")
@@ -984,17 +984,18 @@
activity_type.wage_rate = 25
activity_type.save()
- def get_no_of_days(self):
- no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month)
- no_of_holidays_in_month = len(
- [
- 1
- for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month)
- if i[6] != 0
- ]
- )
- return [no_of_days_in_month[1], no_of_holidays_in_month]
+def get_no_of_days():
+ no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month)
+ no_of_holidays_in_month = len(
+ [
+ 1
+ for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month)
+ if i[6] != 0
+ ]
+ )
+
+ return [no_of_days_in_month[1], no_of_holidays_in_month]
def make_employee_salary_slip(user, payroll_frequency, salary_structure=None, posting_date=None):
@@ -1136,6 +1137,7 @@
"pay_against_benefit_claim": 0,
"type": "Earning",
"max_benefit_amount": 15000,
+ "depends_on_payment_days": 1,
},
]
)
diff --git a/erpnext/regional/india/e_invoice/utils.py b/erpnext/regional/india/e_invoice/utils.py
index 9add09b..5eb14a5 100644
--- a/erpnext/regional/india/e_invoice/utils.py
+++ b/erpnext/regional/india/e_invoice/utils.py
@@ -55,6 +55,9 @@
return False
invalid_company = not frappe.db.get_value("E Invoice User", {"company": doc.get("company")})
+ invalid_company_gstin = not frappe.db.get_value(
+ "E Invoice User", {"gstin": doc.get("company_gstin")}
+ )
invalid_supply_type = doc.get("gst_category") not in [
"Registered Regular",
"Registered Composition",
@@ -71,6 +74,7 @@
if (
invalid_company
+ or invalid_company_gstin
or invalid_supply_type
or company_transaction
or no_taxes_applied
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 dcfb10a..cc61594 100644
--- a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
@@ -1,11 +1,13 @@
# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt
-
import copy
+from collections import OrderedDict
import frappe
-from frappe import _
+from frappe import _, qb
+from frappe.query_builder import CustomFunction
+from frappe.query_builder.functions import Max
from frappe.utils import date_diff, flt, getdate
@@ -18,11 +20,12 @@
columns = get_columns(filters)
conditions = get_conditions(filters)
data = get_data(conditions, filters)
+ so_elapsed_time = get_so_elapsed_time(data)
if not data:
return [], [], None, []
- data, chart_data = prepare_data(data, filters)
+ data, chart_data = prepare_data(data, so_elapsed_time, filters)
return columns, data, None, chart_data
@@ -65,7 +68,6 @@
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,
- IF((SELECT pending_qty) = 0, (TO_SECONDS(Max(dn.posting_date))-TO_SECONDS(so.transaction_date)), 0) as time_taken_to_deliver,
IFNULL(SUM(sii.qty), 0) as billed_qty,
soi.base_amount as amount,
(soi.delivered_qty * soi.base_rate) as delivered_qty_amount,
@@ -76,13 +78,9 @@
soi.description as description
FROM
`tabSales Order` so,
- (`tabSales Order Item` soi
+ `tabSales Order Item` soi
LEFT JOIN `tabSales Invoice Item` sii
- ON sii.so_detail = soi.name and sii.docstatus = 1)
- LEFT JOIN `tabDelivery Note Item` dni
- on dni.so_detail = soi.name
- LEFT JOIN `tabDelivery Note` dn
- on dni.parent = dn.name and dn.docstatus = 1
+ ON sii.so_detail = soi.name and sii.docstatus = 1
WHERE
soi.parent = so.name
and so.status not in ('Stopped', 'Closed', 'On Hold')
@@ -100,7 +98,48 @@
return data
-def prepare_data(data, filters):
+def get_so_elapsed_time(data):
+ """
+ query SO's elapsed time till latest delivery note
+ """
+ so_elapsed_time = OrderedDict()
+ if data:
+ sales_orders = [x.sales_order for x in data]
+
+ so = qb.DocType("Sales Order")
+ soi = qb.DocType("Sales Order Item")
+ dn = qb.DocType("Delivery Note")
+ dni = qb.DocType("Delivery Note Item")
+
+ to_seconds = CustomFunction("TO_SECONDS", ["date"])
+
+ query = (
+ qb.from_(so)
+ .inner_join(soi)
+ .on(soi.parent == so.name)
+ .left_join(dni)
+ .on(dni.so_detail == soi.name)
+ .left_join(dn)
+ .on(dni.parent == dn.name)
+ .select(
+ so.name.as_("sales_order"),
+ soi.item_code.as_("so_item_code"),
+ (to_seconds(Max(dn.posting_date)) - to_seconds(so.transaction_date)).as_("elapsed_seconds"),
+ )
+ .where((so.name.isin(sales_orders)) & (dn.docstatus == 1))
+ .orderby(so.name, soi.name)
+ .groupby(soi.name)
+ )
+ dn_elapsed_time = query.run(as_dict=True)
+
+ for e in dn_elapsed_time:
+ key = (e.sales_order, e.so_item_code)
+ so_elapsed_time[key] = e.elapsed_seconds
+
+ return so_elapsed_time
+
+
+def prepare_data(data, so_elapsed_time, filters):
completed, pending = 0, 0
if filters.get("group_by_so"):
@@ -115,6 +154,13 @@
row["qty_to_bill"] = flt(row["qty"]) - flt(row["billed_qty"])
row["delay"] = 0 if row["delay"] and row["delay"] < 0 else row["delay"]
+
+ row["time_taken_to_deliver"] = (
+ so_elapsed_time.get((row.sales_order, row.item_code))
+ if row["status"] in ("To Bill", "Completed")
+ else 0
+ )
+
if filters.get("group_by_so"):
so_name = row["sales_order"]
diff --git a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
index 25cbb73..241f435 100644
--- a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
@@ -11,7 +11,7 @@
class TestSalesOrderAnalysis(FrappeTestCase):
- def create_sales_order(self, transaction_date):
+ def create_sales_order(self, transaction_date, do_not_save=False, do_not_submit=False):
item = create_item(item_code="_Test Excavator", is_stock_item=0)
so = make_sales_order(
transaction_date=transaction_date,
@@ -24,25 +24,31 @@
so.taxes_and_charges = ""
so.taxes = ""
so.items[0].delivery_date = add_days(transaction_date, 15)
- so.save()
- so.submit()
+ if not do_not_save:
+ so.save()
+ if not do_not_submit:
+ so.submit()
return item, so
- def create_sales_invoice(self, so):
+ def create_sales_invoice(self, so, do_not_save=False, do_not_submit=False):
sinv = make_sales_invoice(so.name)
sinv.posting_date = so.transaction_date
sinv.taxes_and_charges = ""
sinv.taxes = ""
- sinv.insert()
- sinv.submit()
+ if not do_not_save:
+ sinv.save()
+ if not do_not_submit:
+ sinv.submit()
return sinv
- def create_delivery_note(self, so):
+ def create_delivery_note(self, so, do_not_save=False, do_not_submit=False):
dn = make_delivery_note(so.name)
dn.set_posting_time = True
dn.posting_date = add_days(so.transaction_date, 1)
- dn.save()
- dn.submit()
+ if not do_not_save:
+ dn.save()
+ if not do_not_submit:
+ dn.submit()
return dn
def test_01_so_to_deliver_and_bill(self):
@@ -164,3 +170,85 @@
)
# SO's from first 4 test cases should be in output
self.assertEqual(len(data), 4)
+
+ def test_06_so_pending_delivery_with_multiple_delivery_notes(self):
+ transaction_date = "2021-06-01"
+ item, so = self.create_sales_order(transaction_date)
+
+ # bill 2 items
+ sinv1 = self.create_sales_invoice(so, do_not_save=True)
+ sinv1.items[0].qty = 2
+ sinv1 = sinv1.save().submit()
+ # deliver 2 items
+ dn1 = self.create_delivery_note(so, do_not_save=True)
+ dn1.items[0].qty = 2
+ dn1 = dn1.save().submit()
+
+ # bill 2 items
+ sinv2 = self.create_sales_invoice(so, do_not_save=True)
+ sinv2.items[0].qty = 2
+ sinv2 = sinv2.save().submit()
+ # deliver 1 item
+ dn2 = self.create_delivery_note(so, do_not_save=True)
+ dn2.items[0].qty = 1
+ dn2 = dn2.save().submit()
+
+ columns, data, message, chart = execute(
+ {
+ "company": "_Test Company",
+ "from_date": "2021-06-01",
+ "to_date": "2021-06-30",
+ "sales_order": [so.name],
+ }
+ )
+ expected_value = {
+ "status": "To Deliver and Bill",
+ "sales_order": so.name,
+ "delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+ "qty": 10,
+ "delivered_qty": 3,
+ "pending_qty": 7,
+ "qty_to_bill": 6,
+ "billed_qty": 4,
+ "time_taken_to_deliver": 0,
+ }
+ self.assertEqual(len(data), 1)
+ for key, val in expected_value.items():
+ with self.subTest(key=key, val=val):
+ self.assertEqual(data[0][key], val)
+
+ def test_07_so_delivered_with_multiple_delivery_notes(self):
+ transaction_date = "2021-06-01"
+ item, so = self.create_sales_order(transaction_date)
+
+ dn1 = self.create_delivery_note(so, do_not_save=True)
+ dn1.items[0].qty = 5
+ dn1 = dn1.save().submit()
+
+ dn2 = self.create_delivery_note(so, do_not_save=True)
+ dn2.items[0].qty = 5
+ dn2 = dn2.save().submit()
+
+ columns, data, message, chart = execute(
+ {
+ "company": "_Test Company",
+ "from_date": "2021-06-01",
+ "to_date": "2021-06-30",
+ "sales_order": [so.name],
+ }
+ )
+ expected_value = {
+ "status": "To Bill",
+ "sales_order": so.name,
+ "delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+ "qty": 10,
+ "delivered_qty": 10,
+ "pending_qty": 0,
+ "qty_to_bill": 10,
+ "billed_qty": 0,
+ "time_taken_to_deliver": 86400,
+ }
+ self.assertEqual(len(data), 1)
+ for key, val in expected_value.items():
+ with self.subTest(key=key, val=val):
+ self.assertEqual(data[0][key], val)
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry_utils.py b/erpnext/stock/doctype/stock_entry/stock_entry_utils.py
index c5c0cef..41a3b89 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry_utils.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry_utils.py
@@ -2,11 +2,37 @@
# See license.txt
+from typing import TYPE_CHECKING, Optional, overload
+
import frappe
from frappe.utils import cint, flt
import erpnext
+if TYPE_CHECKING:
+ from erpnext.stock.doctype.stock_entry.stock_entry import StockEntry
+
+
+@overload
+def make_stock_entry(
+ *,
+ item_code: str,
+ qty: float,
+ company: Optional[str] = None,
+ from_warehouse: Optional[str] = None,
+ to_warehouse: Optional[str] = None,
+ rate: Optional[float] = None,
+ serial_no: Optional[str] = None,
+ batch_no: Optional[str] = None,
+ posting_date: Optional[str] = None,
+ posting_time: Optional[str] = None,
+ purpose: Optional[str] = None,
+ do_not_save: bool = False,
+ do_not_submit: bool = False,
+ inspection_required: bool = False,
+) -> "StockEntry":
+ ...
+
@frappe.whitelist()
def make_stock_entry(**args):
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 b9e1d23..83d3669 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
@@ -24,9 +24,10 @@
create_stock_reconciliation,
)
from erpnext.stock.stock_ledger import get_previous_sle
+from erpnext.stock.tests.test_utils import StockTestMixin
-class TestStockLedgerEntry(FrappeTestCase):
+class TestStockLedgerEntry(FrappeTestCase, StockTestMixin):
def setUp(self):
items = create_items()
reset("Stock Entry")
@@ -486,30 +487,6 @@
"Incorrect 'Incoming Rate' values fetched for DN items",
)
- def assertSLEs(self, doc, expected_sles, sle_filters=None):
- """Compare sorted SLEs, useful for vouchers that create multiple SLEs for same line"""
-
- filters = {"voucher_no": doc.name, "voucher_type": doc.doctype, "is_cancelled": 0}
- if sle_filters:
- filters.update(sle_filters)
- sles = frappe.get_all(
- "Stock Ledger Entry",
- fields=["*"],
- filters=filters,
- order_by="timestamp(posting_date, posting_time), creation",
- )
-
- for exp_sle, act_sle in zip(expected_sles, sles):
- for k, v in exp_sle.items():
- act_value = act_sle[k]
- if k == "stock_queue":
- act_value = json.loads(act_value)
- if act_value and act_value[0][0] == 0:
- # ignore empty fifo bins
- continue
-
- self.assertEqual(v, act_value, msg=f"{k} doesn't match \n{exp_sle}\n{act_sle}")
-
def test_batchwise_item_valuation_stock_reco(self):
item, warehouses, batches = setup_item_valuation_test()
state = {"stock_value": 0.0, "qty": 0.0}
diff --git a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index 9088eb8..191c03f 100644
--- a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -10,7 +10,7 @@
from frappe.utils import add_days, cstr, flt, nowdate, nowtime, random_string
from erpnext.accounts.utils import get_stock_and_account_balance
-from erpnext.stock.doctype.item.test_item import create_item, make_item
+from erpnext.stock.doctype.item.test_item import create_item
from erpnext.stock.doctype.purchase_receipt.test_purchase_receipt import make_purchase_receipt
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import (
@@ -19,10 +19,11 @@
)
from erpnext.stock.doctype.warehouse.test_warehouse import create_warehouse
from erpnext.stock.stock_ledger import get_previous_sle, update_entries_after
+from erpnext.stock.tests.test_utils import StockTestMixin
from erpnext.stock.utils import get_incoming_rate, get_stock_value_on, get_valuation_method
-class TestStockReconciliation(FrappeTestCase):
+class TestStockReconciliation(FrappeTestCase, StockTestMixin):
@classmethod
def setUpClass(cls):
create_batch_or_serial_no_items()
@@ -40,7 +41,7 @@
self._test_reco_sle_gle("Moving Average")
def _test_reco_sle_gle(self, valuation_method):
- item_code = make_item(properties={"valuation_method": valuation_method}).name
+ item_code = self.make_item(properties={"valuation_method": valuation_method}).name
se1, se2, se3 = insert_existing_sle(warehouse="Stores - TCP1", item_code=item_code)
company = frappe.db.get_value("Warehouse", "Stores - TCP1", "company")
@@ -392,7 +393,7 @@
SR4 | Reco | 0 | 6 (posting date: today-1) [backdated]
PR3 | PR | 1 | 7 (posting date: today) # can't post future PR
"""
- item_code = make_item().name
+ item_code = self.make_item().name
warehouse = "_Test Warehouse - _TC"
frappe.flags.dont_execute_stock_reposts = True
@@ -458,7 +459,7 @@
from erpnext.stock.doctype.delivery_note.test_delivery_note import create_delivery_note
from erpnext.stock.stock_ledger import NegativeStockError
- item_code = make_item().name
+ item_code = self.make_item().name
warehouse = "_Test Warehouse - _TC"
pr1 = make_purchase_receipt(
@@ -506,7 +507,7 @@
from erpnext.stock.doctype.delivery_note.test_delivery_note import create_delivery_note
from erpnext.stock.stock_ledger import NegativeStockError
- item_code = make_item().name
+ item_code = self.make_item().name
warehouse = "_Test Warehouse - _TC"
sr = create_stock_reconciliation(
@@ -549,7 +550,7 @@
# repost will make this test useless, qty should update in realtime without reposts
frappe.flags.dont_execute_stock_reposts = True
- item_code = make_item().name
+ item_code = self.make_item().name
warehouse = "_Test Warehouse - _TC"
sr = create_stock_reconciliation(
diff --git a/erpnext/stock/tests/test_utils.py b/erpnext/stock/tests/test_utils.py
index 9ee0c9f..b046dbd 100644
--- a/erpnext/stock/tests/test_utils.py
+++ b/erpnext/stock/tests/test_utils.py
@@ -1,16 +1,67 @@
+import json
+
import frappe
from frappe.tests.utils import FrappeTestCase
-from erpnext.stock.doctype.item.test_item import make_item
from erpnext.stock.utils import scan_barcode
-class TestStockUtilities(FrappeTestCase):
+class StockTestMixin:
+ """Mixin to simplfy stock ledger tests, useful for all stock transactions."""
+
+ def make_item(self, item_code=None, properties=None, *args, **kwargs):
+ from erpnext.stock.doctype.item.test_item import make_item
+
+ return make_item(item_code, properties, *args, **kwargs)
+
+ def assertSLEs(self, doc, expected_sles, sle_filters=None):
+ """Compare sorted SLEs, useful for vouchers that create multiple SLEs for same line"""
+
+ filters = {"voucher_no": doc.name, "voucher_type": doc.doctype, "is_cancelled": 0}
+ if sle_filters:
+ filters.update(sle_filters)
+ sles = frappe.get_all(
+ "Stock Ledger Entry",
+ fields=["*"],
+ filters=filters,
+ order_by="timestamp(posting_date, posting_time), creation",
+ )
+
+ for exp_sle, act_sle in zip(expected_sles, sles):
+ for k, v in exp_sle.items():
+ act_value = act_sle[k]
+ if k == "stock_queue":
+ act_value = json.loads(act_value)
+ if act_value and act_value[0][0] == 0:
+ # ignore empty fifo bins
+ continue
+
+ self.assertEqual(v, act_value, msg=f"{k} doesn't match \n{exp_sle}\n{act_sle}")
+
+ def assertGLEs(self, doc, expected_gles, gle_filters=None, order_by=None):
+ filters = {"voucher_no": doc.name, "voucher_type": doc.doctype, "is_cancelled": 0}
+
+ if gle_filters:
+ filters.update(gle_filters)
+ actual_gles = frappe.get_all(
+ "GL Entry",
+ fields=["*"],
+ filters=filters,
+ order_by=order_by or "posting_date, creation",
+ )
+
+ for exp_gle, act_gle in zip(expected_gles, actual_gles):
+ for k, exp_value in exp_gle.items():
+ act_value = act_gle[k]
+ self.assertEqual(exp_value, act_value, msg=f"{k} doesn't match \n{exp_gle}\n{act_gle}")
+
+
+class TestStockUtilities(FrappeTestCase, StockTestMixin):
def test_barcode_scanning(self):
- simple_item = make_item(properties={"barcodes": [{"barcode": "12399"}]})
+ simple_item = self.make_item(properties={"barcodes": [{"barcode": "12399"}]})
self.assertEqual(scan_barcode("12399")["item_code"], simple_item.name)
- batch_item = make_item(properties={"has_batch_no": 1, "create_new_batch": 1})
+ batch_item = self.make_item(properties={"has_batch_no": 1, "create_new_batch": 1})
batch = frappe.get_doc(doctype="Batch", item=batch_item.name).insert()
batch_scan = scan_barcode(batch.name)
@@ -19,7 +70,7 @@
self.assertEqual(batch_scan["has_batch_no"], 1)
self.assertEqual(batch_scan["has_serial_no"], 0)
- serial_item = make_item(properties={"has_serial_no": 1})
+ serial_item = self.make_item(properties={"has_serial_no": 1})
serial = frappe.get_doc(
doctype="Serial No", item_code=serial_item.name, serial_no=frappe.generate_hash()
).insert()