Merge pull request #30312 from alyf-de/refactor-sales-analytics
refactor: sales analytics
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index 9d1452b..6f7a0b2 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -32,6 +32,8 @@
name will be changed using autoname options (in a scheduled job)
"""
self.name = frappe.generate_hash(txt="", length=10)
+ if self.meta.autoname == "hash":
+ self.to_rename = 0
def validate(self):
self.flags.ignore_submit_comment = True
@@ -134,7 +136,7 @@
def check_pl_account(self):
if self.is_opening=='Yes' and \
- frappe.db.get_value("Account", self.account, "report_type")=="Profit and Loss":
+ frappe.db.get_value("Account", self.account, "report_type")=="Profit and Loss" and not self.is_cancelled:
frappe.throw(_("{0} {1}: 'Profit and Loss' type account {2} not allowed in Opening Entry")
.format(self.voucher_type, self.voucher_no, self.account))
diff --git a/erpnext/accounts/report/cash_flow/custom_cash_flow.py b/erpnext/accounts/report/cash_flow/custom_cash_flow.py
index 45d147e..20f7fcf 100644
--- a/erpnext/accounts/report/cash_flow/custom_cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/custom_cash_flow.py
@@ -4,7 +4,8 @@
import frappe
from frappe import _
-from frappe.utils import add_to_date
+from frappe.query_builder.functions import Sum
+from frappe.utils import add_to_date, get_date_str
from erpnext.accounts.report.financial_statements import get_columns, get_data, get_period_list
from erpnext.accounts.report.profit_and_loss_statement.profit_and_loss_statement import (
@@ -28,15 +29,22 @@
def get_accounts_in_mappers(mapping_names):
- return frappe.db.sql('''
- select cfma.name, cfm.label, cfm.is_working_capital, cfm.is_income_tax_liability,
- cfm.is_income_tax_expense, cfm.is_finance_cost, cfm.is_finance_cost_adjustment
- from `tabCash Flow Mapping Accounts` cfma
- join `tabCash Flow Mapping` cfm on cfma.parent=cfm.name
- where cfma.parent in (%s)
- order by cfm.is_working_capital
- ''', (', '.join('"%s"' % d for d in mapping_names)))
+ cfm = frappe.qb.DocType('Cash Flow Mapping')
+ cfma = frappe.qb.DocType('Cash Flow Mapping Accounts')
+ result = (
+ frappe.qb
+ .select(
+ cfma.name, cfm.label, cfm.is_working_capital,
+ cfm.is_income_tax_liability, cfm.is_income_tax_expense,
+ cfm.is_finance_cost, cfm.is_finance_cost_adjustment, cfma.account
+ )
+ .from_(cfm)
+ .join(cfma)
+ .on(cfm.name == cfma.parent)
+ .where(cfma.parent.isin(mapping_names))
+ ).run()
+ return result
def setup_mappers(mappers):
cash_flow_accounts = []
@@ -57,31 +65,31 @@
account_types = [
dict(
- name=account[0], label=account[1], is_working_capital=account[2],
+ name=account[0], account_name=account[7], label=account[1], is_working_capital=account[2],
is_income_tax_liability=account[3], is_income_tax_expense=account[4]
) for account in accounts if not account[3]]
finance_costs_adjustments = [
dict(
- name=account[0], label=account[1], is_finance_cost=account[5],
+ name=account[0], account_name=account[7], label=account[1], is_finance_cost=account[5],
is_finance_cost_adjustment=account[6]
) for account in accounts if account[6]]
tax_liabilities = [
dict(
- name=account[0], label=account[1], is_income_tax_liability=account[3],
+ name=account[0], account_name=account[7], label=account[1], is_income_tax_liability=account[3],
is_income_tax_expense=account[4]
) for account in accounts if account[3]]
tax_expenses = [
dict(
- name=account[0], label=account[1], is_income_tax_liability=account[3],
+ name=account[0], account_name=account[7], label=account[1], is_income_tax_liability=account[3],
is_income_tax_expense=account[4]
) for account in accounts if account[4]]
finance_costs = [
dict(
- name=account[0], label=account[1], is_finance_cost=account[5])
+ name=account[0], account_name=account[7], label=account[1], is_finance_cost=account[5])
for account in accounts if account[5]]
account_types_labels = sorted(
@@ -124,27 +132,27 @@
)
for label in account_types_labels:
- names = [d['name'] for d in account_types if d['label'] == label[0]]
+ names = [d['account_name'] for d in account_types if d['label'] == label[0]]
m = dict(label=label[0], names=names, is_working_capital=label[1])
mapping['account_types'].append(m)
for label in fc_adjustment_labels:
- names = [d['name'] for d in finance_costs_adjustments if d['label'] == label[0]]
+ names = [d['account_name'] for d in finance_costs_adjustments if d['label'] == label[0]]
m = dict(label=label[0], names=names)
mapping['finance_costs_adjustments'].append(m)
for label in unique_liability_labels:
- names = [d['name'] for d in tax_liabilities if d['label'] == label[0]]
+ names = [d['account_name'] for d in tax_liabilities if d['label'] == label[0]]
m = dict(label=label[0], names=names, tax_liability=label[1], tax_expense=label[2])
mapping['tax_liabilities'].append(m)
for label in unique_expense_labels:
- names = [d['name'] for d in tax_expenses if d['label'] == label[0]]
+ names = [d['account_name'] for d in tax_expenses if d['label'] == label[0]]
m = dict(label=label[0], names=names, tax_liability=label[1], tax_expense=label[2])
mapping['tax_expenses'].append(m)
for label in unique_finance_costs_labels:
- names = [d['name'] for d in finance_costs if d['label'] == label[0]]
+ names = [d['account_name'] for d in finance_costs if d['label'] == label[0]]
m = dict(label=label[0], names=names, is_finance_cost=label[1])
mapping['finance_costs'].append(m)
@@ -371,14 +379,30 @@
def _get_account_type_based_data(filters, account_names, period_list, accumulated_values, opening_balances=0):
+ if not account_names or not account_names[0] or not type(account_names[0]) == str:
+ # only proceed if account_names is a list of account names
+ return {}
+
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
+ GLEntry = frappe.qb.DocType('GL Entry')
+ Account = frappe.qb.DocType('Account')
+
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
- accounts = ', '.join('"%s"' % d for d in account_names)
+
+ account_subquery = (
+ frappe.qb.from_(Account)
+ .where(
+ (Account.name.isin(account_names)) |
+ (Account.parent_account.isin(account_names))
+ )
+ .select(Account.name)
+ .as_("account_subquery")
+ )
if opening_balances:
date_info = dict(date=start_date)
@@ -395,32 +419,31 @@
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
- gl_sum = frappe.db.sql_list("""
- select sum(credit) - sum(debit)
- from `tabGL Entry`
- where company=%s and posting_date >= %s and posting_date <= %s
- and voucher_type != 'Period Closing Voucher'
- and account in ( SELECT name FROM tabAccount WHERE name IN (%s)
- OR parent_account IN (%s))
- """, (company, start, end, accounts, accounts))
- else:
- gl_sum = frappe.db.sql_list("""
- select sum(credit) - sum(debit)
- from `tabGL Entry`
- where company=%s and posting_date >= %s and posting_date <= %s
- and voucher_type != 'Period Closing Voucher'
- and account in ( SELECT name FROM tabAccount WHERE name IN (%s)
- OR parent_account IN (%s))
- """, (company, start_date if accumulated_values else period['from_date'],
- period['to_date'], accounts, accounts))
+ start, end = get_date_str(start), get_date_str(end)
- if gl_sum and gl_sum[0]:
- amount = gl_sum[0]
else:
- amount = 0
+ start, end = start_date if accumulated_values else period['from_date'], period['to_date']
+ start, end = get_date_str(start), get_date_str(end)
- total += amount
- data.setdefault(period["key"], amount)
+ result = (
+ frappe.qb.from_(GLEntry)
+ .select(Sum(GLEntry.credit) - Sum(GLEntry.debit))
+ .where(
+ (GLEntry.company == company) &
+ (GLEntry.posting_date >= start) &
+ (GLEntry.posting_date <= end) &
+ (GLEntry.voucher_type != 'Period Closing Voucher') &
+ (GLEntry.account.isin(account_subquery))
+ )
+ ).run()
+
+ if result and result[0]:
+ gl_sum = result[0][0]
+ else:
+ gl_sum = 0
+
+ total += gl_sum
+ data.setdefault(period["key"], gl_sum)
data["total"] = total
return data
diff --git a/erpnext/hr/doctype/leave_policy_assignment/leave_policy_assignment.py b/erpnext/hr/doctype/leave_policy_assignment/leave_policy_assignment.py
index c11a821..ae5ac7b 100644
--- a/erpnext/hr/doctype/leave_policy_assignment/leave_policy_assignment.py
+++ b/erpnext/hr/doctype/leave_policy_assignment/leave_policy_assignment.py
@@ -8,13 +8,14 @@
import frappe
from frappe import _, bold
from frappe.model.document import Document
-from frappe.utils import date_diff, flt, formatdate, get_last_day, getdate
+from frappe.utils import date_diff, flt, formatdate, get_last_day, get_link_to_form, getdate
class LeavePolicyAssignment(Document):
def validate(self):
- self.validate_policy_assignment_overlap()
self.set_dates()
+ self.validate_policy_assignment_overlap()
+ self.warn_about_carry_forwarding()
def on_submit(self):
self.grant_leave_alloc_for_employee()
@@ -38,6 +39,20 @@
frappe.throw(_("Leave Policy: {0} already assigned for Employee {1} for period {2} to {3}")
.format(bold(self.leave_policy), bold(self.employee), bold(formatdate(self.effective_from)), bold(formatdate(self.effective_to))))
+ def warn_about_carry_forwarding(self):
+ if not self.carry_forward:
+ return
+
+ leave_types = get_leave_type_details()
+ leave_policy = frappe.get_doc("Leave Policy", self.leave_policy)
+
+ for policy in leave_policy.leave_policy_details:
+ leave_type = leave_types.get(policy.leave_type)
+ if not leave_type.is_carry_forward:
+ msg = _("Leaves for the Leave Type {0} won't be carry-forwarded since carry-forwarding is disabled.").format(
+ frappe.bold(get_link_to_form("Leave Type", leave_type.name)))
+ frappe.msgprint(msg, indicator="orange", alert=True)
+
@frappe.whitelist()
def grant_leave_alloc_for_employee(self):
if self.leaves_allocated:
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 48cd753..2b6e696 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -1018,21 +1018,21 @@
required_qty = item.get("quantity")
# get available material by transferring to production warehouse
for d in locations:
- if required_qty <=0: return
+ if required_qty <= 0:
+ return
new_dict = copy.deepcopy(item)
quantity = required_qty if d.get("qty") > required_qty else d.get("qty")
- if required_qty > 0:
- new_dict.update({
- "quantity": quantity,
- "material_request_type": "Material Transfer",
- "uom": new_dict.get("stock_uom"), # internal transfer should be in stock UOM
- "from_warehouse": d.get("warehouse")
- })
+ new_dict.update({
+ "quantity": quantity,
+ "material_request_type": "Material Transfer",
+ "uom": new_dict.get("stock_uom"), # internal transfer should be in stock UOM
+ "from_warehouse": d.get("warehouse")
+ })
- required_qty -= quantity
- new_mr_items.append(new_dict)
+ required_qty -= quantity
+ new_mr_items.append(new_dict)
# raise purchase request for remaining qty
if required_qty:
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 16d8c73..3199912 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -333,6 +333,7 @@
erpnext.patches.v13_0.delete_bank_reconciliation_detail
erpnext.patches.v13_0.enable_provisional_accounting
erpnext.patches.v13_0.non_profit_deprecation_warning
+erpnext.patches.v13_0.enable_ksa_vat_docs #1
[post_model_sync]
erpnext.patches.v14_0.rename_ongoing_status_in_sla_documents
diff --git a/erpnext/patches/v13_0/enable_ksa_vat_docs.py b/erpnext/patches/v13_0/enable_ksa_vat_docs.py
new file mode 100644
index 0000000..3f48262
--- /dev/null
+++ b/erpnext/patches/v13_0/enable_ksa_vat_docs.py
@@ -0,0 +1,12 @@
+import frappe
+
+from erpnext.regional.saudi_arabia.setup import add_permissions, add_print_formats
+
+
+def execute():
+ company = frappe.get_all('Company', filters = {'country': 'Saudi Arabia'})
+ if not company:
+ return
+
+ add_print_formats()
+ add_permissions()
\ No newline at end of file
diff --git a/erpnext/stock/doctype/item/item.py b/erpnext/stock/doctype/item/item.py
index 8ede955..3abeecd 100644
--- a/erpnext/stock/doctype/item/item.py
+++ b/erpnext/stock/doctype/item/item.py
@@ -107,6 +107,7 @@
self.validate_variant_attributes()
self.validate_variant_based_on_change()
self.validate_fixed_asset()
+ self.clear_retain_sample()
self.validate_retain_sample()
self.validate_uom_conversion_factor()
self.validate_customer_provided_part()
@@ -209,6 +210,13 @@
frappe.throw(_("{0} Retain Sample is based on batch, please check Has Batch No to retain sample of item").format(
self.item_code))
+ def clear_retain_sample(self):
+ if not self.has_batch_no:
+ self.retain_sample = None
+
+ if not self.retain_sample:
+ self.sample_quantity = None
+
def add_default_uom_in_conversion_factor_table(self):
if not self.is_new() and self.has_value_changed("stock_uom"):
self.uoms = []
diff --git a/erpnext/stock/doctype/item/test_item.py b/erpnext/stock/doctype/item/test_item.py
index 669cabc..112420f 100644
--- a/erpnext/stock/doctype/item/test_item.py
+++ b/erpnext/stock/doctype/item/test_item.py
@@ -656,6 +656,19 @@
make_stock_entry(qty=1, item_code=item.name, target="_Test Warehouse - _TC", posting_date = add_days(today(), 5))
self.consume_item_code_with_differet_stock_transactions(item_code=item.name)
+ @change_settings("Stock Settings", {"sample_retention_warehouse": "_Test Warehouse - _TC"})
+ def test_retain_sample(self):
+ item = make_item("_TestRetainSample", {'has_batch_no': 1, 'retain_sample': 1, 'sample_quantity': 1})
+
+ self.assertEqual(item.has_batch_no, 1)
+ self.assertEqual(item.retain_sample, 1)
+ self.assertEqual(item.sample_quantity, 1)
+
+ item.has_batch_no = None
+ item.save()
+ self.assertEqual(item.retain_sample, None)
+ self.assertEqual(item.sample_quantity, None)
+ item.delete()
def consume_item_code_with_differet_stock_transactions(self, item_code, warehouse="_Test Warehouse - _TC"):
from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index c538307..2f59304 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -26,6 +26,8 @@
name will be changed using autoname options (in a scheduled job)
"""
self.name = frappe.generate_hash(txt="", length=10)
+ if self.meta.autoname == "hash":
+ self.to_rename = 0
def validate(self):
self.flags.ignore_submit_comment = True
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 2352235..fc57995 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
@@ -7,9 +7,11 @@
import frappe
from frappe.core.page.permission_manager.permission_manager import reset
+from frappe.custom.doctype.property_setter.property_setter import make_property_setter
from frappe.tests.utils import FrappeTestCase
from frappe.utils import add_days, today
+from erpnext.accounts.doctype.gl_entry.gl_entry import rename_gle_sle_docs
from erpnext.stock.doctype.delivery_note.test_delivery_note import create_delivery_note
from erpnext.stock.doctype.item.test_item import make_item
from erpnext.stock.doctype.landed_cost_voucher.test_landed_cost_voucher import (
@@ -939,3 +941,62 @@
# Used to isolate valuation sensitive
# tests to prevent future tests from failing.
return str(uuid4())[:8].upper()
+
+
+class TestDeferredNaming(FrappeTestCase):
+
+ @classmethod
+ def setUpClass(cls) -> None:
+ super().setUpClass()
+ cls.gle_autoname = frappe.get_meta("GL Entry").autoname
+ cls.sle_autoname = frappe.get_meta("Stock Ledger Entry").autoname
+
+ def setUp(self) -> None:
+ self.item = make_item().name
+ self.warehouse = "Stores - TCP1"
+ self.company = "_Test Company with perpetual inventory"
+
+ def tearDown(self) -> None:
+ make_property_setter(doctype="GL Entry", for_doctype=True,
+ property="autoname", value=self.gle_autoname, property_type="Data", fieldname=None)
+ make_property_setter(doctype="Stock Ledger Entry", for_doctype=True,
+ property="autoname", value=self.sle_autoname, property_type="Data", fieldname=None)
+
+ # since deferred naming autocommits, commit all changes to avoid flake
+ frappe.db.commit() # nosemgrep
+
+ @staticmethod
+ def get_gle_sles(se):
+ filters = {"voucher_type": se.doctype, "voucher_no": se.name}
+ gle = set(frappe.get_list("GL Entry", filters, pluck="name"))
+ sle = set(frappe.get_list("Stock Ledger Entry", filters, pluck="name"))
+ return gle, sle
+
+ def test_deferred_naming(self):
+ se = make_stock_entry(item_code=self.item, to_warehouse=self.warehouse,
+ qty=10, rate=100, company=self.company)
+
+ gle, sle = self.get_gle_sles(se)
+ rename_gle_sle_docs()
+ renamed_gle, renamed_sle = self.get_gle_sles(se)
+
+ self.assertFalse(gle & renamed_gle, msg="GLEs not renamed")
+ self.assertFalse(sle & renamed_sle, msg="SLEs not renamed")
+ se.cancel()
+
+ def test_hash_naming(self):
+ # disable naming series
+ for doctype in ("GL Entry", "Stock Ledger Entry"):
+ make_property_setter(doctype=doctype, for_doctype=True,
+ property="autoname", value="hash", property_type="Data", fieldname=None)
+
+ se = make_stock_entry(item_code=self.item, to_warehouse=self.warehouse,
+ qty=10, rate=100, company=self.company)
+
+ gle, sle = self.get_gle_sles(se)
+ rename_gle_sle_docs()
+ renamed_gle, renamed_sle = self.get_gle_sles(se)
+
+ self.assertEqual(gle, renamed_gle, msg="GLEs are renamed while using hash naming")
+ self.assertEqual(sle, renamed_sle, msg="SLEs are renamed while using hash naming")
+ se.cancel()