Merge branch 'develop' into cancel_pi_cancelled_asset
diff --git a/erpnext/accounts/doctype/account/account_tree.js b/erpnext/accounts/doctype/account/account_tree.js
index 8ae90ce..d537adf 100644
--- a/erpnext/accounts/doctype/account/account_tree.js
+++ b/erpnext/accounts/doctype/account/account_tree.js
@@ -56,36 +56,41 @@
accounts = nodes;
}
- const get_balances = frappe.call({
- method: 'erpnext.accounts.utils.get_account_balances',
- args: {
- accounts: accounts,
- company: cur_tree.args.company
- },
- });
+ frappe.db.get_single_value("Accounts Settings", "show_balance_in_coa").then((value) => {
+ if(value) {
- get_balances.then(r => {
- if (!r.message || r.message.length == 0) return;
+ const get_balances = frappe.call({
+ method: 'erpnext.accounts.utils.get_account_balances',
+ args: {
+ accounts: accounts,
+ company: cur_tree.args.company
+ },
+ });
- for (let account of r.message) {
+ get_balances.then(r => {
+ if (!r.message || r.message.length == 0) return;
- const node = cur_tree.nodes && cur_tree.nodes[account.value];
- if (!node || node.is_root) continue;
+ for (let account of r.message) {
- // show Dr if positive since balance is calculated as debit - credit else show Cr
- const balance = account.balance_in_account_currency || account.balance;
- const dr_or_cr = balance > 0 ? "Dr": "Cr";
- const format = (value, currency) => format_currency(Math.abs(value), currency);
+ const node = cur_tree.nodes && cur_tree.nodes[account.value];
+ if (!node || node.is_root) continue;
- if (account.balance!==undefined) {
- node.parent && node.parent.find('.balance-area').remove();
- $('<span class="balance-area pull-right">'
- + (account.balance_in_account_currency ?
- (format(account.balance_in_account_currency, account.account_currency) + " / ") : "")
- + format(account.balance, account.company_currency)
- + " " + dr_or_cr
- + '</span>').insertBefore(node.$ul);
- }
+ // show Dr if positive since balance is calculated as debit - credit else show Cr
+ const balance = account.balance_in_account_currency || account.balance;
+ const dr_or_cr = balance > 0 ? "Dr": "Cr";
+ const format = (value, currency) => format_currency(Math.abs(value), currency);
+
+ if (account.balance!==undefined) {
+ node.parent && node.parent.find('.balance-area').remove();
+ $('<span class="balance-area pull-right">'
+ + (account.balance_in_account_currency ?
+ (format(account.balance_in_account_currency, account.account_currency) + " / ") : "")
+ + format(account.balance, account.company_currency)
+ + " " + dr_or_cr
+ + '</span>').insertBefore(node.$ul);
+ }
+ }
+ });
}
});
},
diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
index 1e2e2ac..3f985b6 100644
--- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
+++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json
@@ -56,7 +56,9 @@
"acc_frozen_upto",
"column_break_25",
"frozen_accounts_modifier",
- "report_settings_sb"
+ "report_settings_sb",
+ "tab_break_dpet",
+ "show_balance_in_coa"
],
"fields": [
{
@@ -347,6 +349,17 @@
"fieldname": "allow_multi_currency_invoices_against_single_party_account",
"fieldtype": "Check",
"label": "Allow multi-currency invoices against single party account "
+ },
+ {
+ "fieldname": "tab_break_dpet",
+ "fieldtype": "Tab Break",
+ "label": "Chart Of Accounts"
+ },
+ {
+ "default": "1",
+ "fieldname": "show_balance_in_coa",
+ "fieldtype": "Check",
+ "label": "Show Balances in Chart Of Accounts"
}
],
"icon": "icon-cog",
@@ -354,7 +367,7 @@
"index_web_pages_for_search": 1,
"issingle": 1,
"links": [],
- "modified": "2022-11-27 21:49:52.538655",
+ "modified": "2023-01-02 12:07:42.434214",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Accounts Settings",
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.js b/erpnext/accounts/report/gross_profit/gross_profit.js
index 615804e..e89d429 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.js
+++ b/erpnext/accounts/report/gross_profit/gross_profit.js
@@ -50,6 +50,20 @@
"fieldtype": "Link",
"options": "Sales Person"
},
+ {
+ "fieldname": "warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "get_query": function () {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: [
+ ["Warehouse", "company", "=", company]
+ ]
+ };
+ },
+ },
],
"tree": true,
"name_field": "parent",
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index 25e7891..e23265b 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -775,6 +775,13 @@
if self.filters.get("item_code"):
conditions += " and `tabSales Invoice Item`.item_code = %(item_code)s"
+ if self.filters.get("warehouse"):
+ warehouse_details = frappe.db.get_value(
+ "Warehouse", self.filters.get("warehouse"), ["lft", "rgt"], as_dict=1
+ )
+ if warehouse_details:
+ conditions += f" and `tabSales Invoice Item`.warehouse in (select name from `tabWarehouse` wh where wh.lft >= {warehouse_details.lft} and wh.rgt <= {warehouse_details.rgt} and warehouse = wh.name)"
+
self.si_list = frappe.db.sql(
"""
select
diff --git a/erpnext/controllers/sales_and_purchase_return.py b/erpnext/controllers/sales_and_purchase_return.py
index 8bd0998..9fcb769 100644
--- a/erpnext/controllers/sales_and_purchase_return.py
+++ b/erpnext/controllers/sales_and_purchase_return.py
@@ -305,7 +305,7 @@
fields += ["sum(abs(`tab{0}`.received_stock_qty)) as received_stock_qty".format(child_doctype)]
# Used retrun against and supplier and is_retrun because there is an index added for it
- data = frappe.db.get_list(
+ data = frappe.get_all(
doctype,
fields=fields,
filters=[
diff --git a/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py b/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
index e15aa4a..853a99a 100644
--- a/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
+++ b/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
@@ -2,7 +2,8 @@
from frappe import qb
from frappe.query_builder import Case, CustomFunction
from frappe.query_builder.custom import ConstantColumn
-from frappe.query_builder.functions import IfNull
+from frappe.query_builder.functions import Count, IfNull
+from frappe.utils import flt
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
get_dimensions,
@@ -17,9 +18,9 @@
make_dimension_in_accounting_doctypes(dimension, ["Payment Ledger Entry"])
-def generate_name_for_payment_ledger_entries(gl_entries):
- for index, entry in enumerate(gl_entries, 1):
- entry.name = index
+def generate_name_for_payment_ledger_entries(gl_entries, start):
+ for index, entry in enumerate(gl_entries, 0):
+ entry.name = start + index
def get_columns():
@@ -81,6 +82,14 @@
def execute():
+ """
+ Description:
+ Migrate records from `tabGL Entry` to `tabPayment Ledger Entry`.
+ Patch is non-resumable. if patch failed or is terminatted abnormally, clear 'tabPayment Ledger Entry' table manually before re-running. Re-running is safe only during V13->V14 update.
+
+ Note: Post successful migration to V14, re-running is NOT-SAFE and SHOULD NOT be attempted.
+ """
+
if frappe.reload_doc("accounts", "doctype", "payment_ledger_entry"):
# create accounting dimension fields in Payment Ledger
create_accounting_dimension_fields()
@@ -89,52 +98,90 @@
account = qb.DocType("Account")
ifelse = CustomFunction("IF", ["condition", "then", "else"])
- gl_entries = (
- qb.from_(gl)
- .inner_join(account)
- .on((gl.account == account.name) & (account.account_type.isin(["Receivable", "Payable"])))
- .select(
- gl.star,
- ConstantColumn(1).as_("docstatus"),
- account.account_type.as_("account_type"),
- IfNull(
- ifelse(gl.against_voucher_type == "", None, gl.against_voucher_type), gl.voucher_type
- ).as_("against_voucher_type"),
- IfNull(ifelse(gl.against_voucher == "", None, gl.against_voucher), gl.voucher_no).as_(
- "against_voucher_no"
- ),
- # convert debit/credit to amount
- Case()
- .when(account.account_type == "Receivable", gl.debit - gl.credit)
- .else_(gl.credit - gl.debit)
- .as_("amount"),
- # convert debit/credit in account currency to amount in account currency
- Case()
- .when(
- account.account_type == "Receivable",
- gl.debit_in_account_currency - gl.credit_in_account_currency,
- )
- .else_(gl.credit_in_account_currency - gl.debit_in_account_currency)
- .as_("amount_in_account_currency"),
- )
- .where(gl.is_cancelled == 0)
- .orderby(gl.creation)
- .run(as_dict=True)
+ # Get Records Count
+ accounts = (
+ qb.from_(account)
+ .select(account.name)
+ .where((account.account_type == "Receivable") | (account.account_type == "Payable"))
+ .orderby(account.name)
)
+ un_processed = (
+ qb.from_(gl)
+ .select(Count(gl.name))
+ .where((gl.is_cancelled == 0) & (gl.account.isin(accounts)))
+ .run()
+ )[0][0]
- # primary key(name) for payment ledger records
- generate_name_for_payment_ledger_entries(gl_entries)
+ if un_processed:
+ print(f"Migrating {un_processed} GL Entries to Payment Ledger")
- # split data into chunks
- chunk_size = 1000
- try:
- for i in range(0, len(gl_entries), chunk_size):
- insert_query = build_insert_query()
- insert_chunk_into_payment_ledger(insert_query, gl_entries[i : i + chunk_size])
- frappe.db.commit()
- except Exception as err:
- frappe.db.rollback()
- ple = qb.DocType("Payment Ledger Entry")
- qb.from_(ple).delete().where(ple.docstatus >= 0).run()
- frappe.db.commit()
- raise err
+ processed = 0
+ last_update_percent = 0
+ batch_size = 5000
+ last_name = None
+
+ while True:
+ if last_name:
+ where_clause = gl.name.gt(last_name) & (gl.is_cancelled == 0)
+ else:
+ where_clause = gl.is_cancelled == 0
+
+ gl_entries = (
+ qb.from_(gl)
+ .inner_join(account)
+ .on((gl.account == account.name) & (account.account_type.isin(["Receivable", "Payable"])))
+ .select(
+ gl.star,
+ ConstantColumn(1).as_("docstatus"),
+ account.account_type.as_("account_type"),
+ IfNull(
+ ifelse(gl.against_voucher_type == "", None, gl.against_voucher_type), gl.voucher_type
+ ).as_("against_voucher_type"),
+ IfNull(ifelse(gl.against_voucher == "", None, gl.against_voucher), gl.voucher_no).as_(
+ "against_voucher_no"
+ ),
+ # convert debit/credit to amount
+ Case()
+ .when(account.account_type == "Receivable", gl.debit - gl.credit)
+ .else_(gl.credit - gl.debit)
+ .as_("amount"),
+ # convert debit/credit in account currency to amount in account currency
+ Case()
+ .when(
+ account.account_type == "Receivable",
+ gl.debit_in_account_currency - gl.credit_in_account_currency,
+ )
+ .else_(gl.credit_in_account_currency - gl.debit_in_account_currency)
+ .as_("amount_in_account_currency"),
+ )
+ .where(where_clause)
+ .orderby(gl.name)
+ .limit(batch_size)
+ .run(as_dict=True)
+ )
+
+ if gl_entries:
+ last_name = gl_entries[-1].name
+
+ # primary key(name) for payment ledger records
+ generate_name_for_payment_ledger_entries(gl_entries, processed)
+
+ try:
+ insert_query = build_insert_query()
+ insert_chunk_into_payment_ledger(insert_query, gl_entries)
+ frappe.db.commit()
+
+ processed += len(gl_entries)
+
+ # Progress message
+ percent = flt((processed / un_processed) * 100, 2)
+ if percent - last_update_percent > 1:
+ print(f"{percent}% ({processed}) records processed")
+ last_update_percent = percent
+
+ except Exception as err:
+ print("Migration Failed. Clear `tabPayment Ledger Entry` table before re-running")
+ raise err
+ else:
+ break
+ print(f"{processed} records have been sucessfully migrated")
diff --git a/erpnext/patches/v14_0/migrate_remarks_from_gl_to_payment_ledger.py b/erpnext/patches/v14_0/migrate_remarks_from_gl_to_payment_ledger.py
index fd2a2a3..9d216c4 100644
--- a/erpnext/patches/v14_0/migrate_remarks_from_gl_to_payment_ledger.py
+++ b/erpnext/patches/v14_0/migrate_remarks_from_gl_to_payment_ledger.py
@@ -1,81 +1,98 @@
import frappe
from frappe import qb
-from frappe.utils import create_batch
-
-
-def remove_duplicate_entries(pl_entries):
- unique_vouchers = set()
- for x in pl_entries:
- unique_vouchers.add(
- (x.company, x.account, x.party_type, x.party, x.voucher_type, x.voucher_no, x.gle_remarks)
- )
-
- entries = []
- for x in unique_vouchers:
- entries.append(
- frappe._dict(
- company=x[0],
- account=x[1],
- party_type=x[2],
- party=x[3],
- voucher_type=x[4],
- voucher_no=x[5],
- gle_remarks=x[6],
- )
- )
- return entries
+from frappe.query_builder import CustomFunction
+from frappe.query_builder.functions import Count, IfNull
+from frappe.utils import flt
def execute():
+ """
+ Migrate 'remarks' field from 'tabGL Entry' to 'tabPayment Ledger Entry'
+ """
+
if frappe.reload_doc("accounts", "doctype", "payment_ledger_entry"):
gle = qb.DocType("GL Entry")
ple = qb.DocType("Payment Ledger Entry")
- # get ple and their remarks from GL Entry
- pl_entries = (
- qb.from_(ple)
- .left_join(gle)
- .on(
- (ple.account == gle.account)
- & (ple.party_type == gle.party_type)
- & (ple.party == gle.party)
- & (ple.voucher_type == gle.voucher_type)
- & (ple.voucher_no == gle.voucher_no)
- & (ple.company == gle.company)
- )
- .select(
- ple.company,
- ple.account,
- ple.party_type,
- ple.party,
- ple.voucher_type,
- ple.voucher_no,
- gle.remarks.as_("gle_remarks"),
- )
- .where((ple.delinked == 0) & (gle.is_cancelled == 0))
- .run(as_dict=True)
- )
+ # Get empty PLE records
+ un_processed = (
+ qb.from_(ple).select(Count(ple.name)).where((ple.remarks.isnull()) & (ple.delinked == 0)).run()
+ )[0][0]
- pl_entries = remove_duplicate_entries(pl_entries)
+ if un_processed:
+ print(f"Remarks for {un_processed} Payment Ledger records will be updated from GL Entry")
- if pl_entries:
- # split into multiple batches, update and commit for each batch
+ ifelse = CustomFunction("IF", ["condition", "then", "else"])
+
+ processed = 0
+ last_percent_update = 0
batch_size = 1000
- for batch in create_batch(pl_entries, batch_size):
- for entry in batch:
- query = (
- qb.update(ple)
- .set(ple.remarks, entry.gle_remarks)
- .where(
- (ple.company == entry.company)
- & (ple.account == entry.account)
- & (ple.party_type == entry.party_type)
- & (ple.party == entry.party)
- & (ple.voucher_type == entry.voucher_type)
- & (ple.voucher_no == entry.voucher_no)
- )
- )
- query.run()
+ last_name = None
- frappe.db.commit()
+ while True:
+ if last_name:
+ where_clause = (ple.name.gt(last_name)) & (ple.remarks.isnull()) & (ple.delinked == 0)
+ else:
+ where_clause = (ple.remarks.isnull()) & (ple.delinked == 0)
+
+ # results are deterministic
+ names = (
+ qb.from_(ple).select(ple.name).where(where_clause).orderby(ple.name).limit(batch_size).run()
+ )
+
+ if names:
+ last_name = names[-1][0]
+
+ pl_entries = (
+ qb.from_(ple)
+ .left_join(gle)
+ .on(
+ (ple.account == gle.account)
+ & (ple.party_type == gle.party_type)
+ & (ple.party == gle.party)
+ & (ple.voucher_type == gle.voucher_type)
+ & (ple.voucher_no == gle.voucher_no)
+ & (
+ ple.against_voucher_type
+ == IfNull(
+ ifelse(gle.against_voucher_type == "", None, gle.against_voucher_type), gle.voucher_type
+ )
+ )
+ & (
+ ple.against_voucher_no
+ == IfNull(ifelse(gle.against_voucher == "", None, gle.against_voucher), gle.voucher_no)
+ )
+ & (ple.company == gle.company)
+ & (
+ ((ple.account_type == "Receivable") & (ple.amount == (gle.debit - gle.credit)))
+ | (ple.account_type == "Payable") & (ple.amount == (gle.credit - gle.debit))
+ )
+ & (gle.remarks.notnull())
+ & (gle.is_cancelled == 0)
+ )
+ .select(ple.name)
+ .distinct()
+ .select(
+ gle.remarks.as_("gle_remarks"),
+ )
+ .where(ple.name.isin(names))
+ .run(as_dict=True)
+ )
+
+ if pl_entries:
+ for entry in pl_entries:
+ query = qb.update(ple).set(ple.remarks, entry.gle_remarks).where((ple.name == entry.name))
+ query.run()
+
+ frappe.db.commit()
+
+ processed += len(pl_entries)
+ percentage = flt((processed / un_processed) * 100, 2)
+ if percentage - last_percent_update > 1:
+ print(f"{percentage}% ({processed}) PLE records updated")
+ last_percent_update = percentage
+
+ else:
+ break
+ print("Remarks succesfully migrated")
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 9f409d4..72654e6 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -159,13 +159,18 @@
last_sle_qty = (
frappe.qb.from_(sle)
.select(sle.qty_after_transaction)
- .where((sle.item_code == args.get("item_code")) & (sle.warehouse == args.get("warehouse")))
+ .where(
+ (sle.item_code == args.get("item_code"))
+ & (sle.warehouse == args.get("warehouse"))
+ & (sle.is_cancelled == 0)
+ )
.orderby(CombineDatetime(sle.posting_date, sle.posting_time), order=Order.desc)
.orderby(sle.creation, order=Order.desc)
.limit(1)
.run()
)
+ actual_qty = 0.0
if last_sle_qty:
actual_qty = last_sle_qty[0][0]
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index 3739cb8..af0d148 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -888,7 +888,7 @@
# Update Billing % based on pending accepted qty
total_amount, total_billed_amount = 0, 0
for item in pr_doc.items:
- return_data = frappe.db.get_list(
+ return_data = frappe.get_all(
"Purchase Receipt",
fields=["sum(abs(`tabPurchase Receipt Item`.qty)) as qty"],
filters=[
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 5d75bfd..d8b12ed 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -1179,7 +1179,7 @@
def get_sle_by_voucher_detail_no(voucher_detail_no, excluded_sle=None):
return frappe.db.get_value(
"Stock Ledger Entry",
- {"voucher_detail_no": voucher_detail_no, "name": ["!=", excluded_sle]},
+ {"voucher_detail_no": voucher_detail_no, "name": ["!=", excluded_sle], "is_cancelled": 0},
[
"item_code",
"warehouse",