Patch to update due_date in GLE, Journal Entry and Payment Entry (#12093)
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 2b6452d..9a7ec7c 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -463,7 +463,6 @@
erpnext.patches.v9_0.set_pos_profile_name
erpnext.patches.v9_0.remove_non_existing_warehouse_from_stock_settings
execute:frappe.delete_doc_if_exists("DocType", "Program Fee")
-erpnext.patches.v8_10.update_gl_due_date_for_pi_and_si
erpnext.patches.v8_10.change_default_customer_credit_days
erpnext.patches.v9_0.update_employee_loan_details
erpnext.patches.v9_2.delete_healthcare_domain_default_items
@@ -484,3 +483,6 @@
erpnext.patches.v10_0.copy_projects_renamed_fields
erpnext.patches.v10_0.enabled_regional_print_format_based_on_country
erpnext.patches.v10_0.update_asset_calculate_depreciation
+erpnext.patches.v10_0.enabled_regional_print_format_based_on_country
+erpnext.patches.v10_0.update_asset_calculate_depreciation
+erpnext.patches.v10_0.update_due_date_in_gle_and_payment_entries
diff --git a/erpnext/patches/v10_0/update_due_date_in_gle_and_payment_entries.py b/erpnext/patches/v10_0/update_due_date_in_gle_and_payment_entries.py
new file mode 100644
index 0000000..268b843
--- /dev/null
+++ b/erpnext/patches/v10_0/update_due_date_in_gle_and_payment_entries.py
@@ -0,0 +1,100 @@
+from __future__ import unicode_literals
+import frappe
+from frappe.utils import update_progress_bar
+
+def execute():
+ frappe.reload_doc("accounts", "doctype", "gl_entry")
+ frappe.reload_doc("accounts", "doctype", "payment_entry_reference")
+ frappe.reload_doc("accounts", "doctype", "journal_entry_account")
+
+ print "Updating Due Date in GL Entry, Journal Entry and Payment Entry"
+ for doctype in ("Sales Invoice", "Purchase Invoice"):
+ invoice_due_dates = frappe.db.sql("""select name, due_date from `tab{0}`
+ where docstatus=1 order by name""".format(doctype))
+
+ # update gle
+ count = 0
+ total_count = len(invoice_due_dates)
+ batch_size = 1000
+
+ while(count < total_count):
+ update_progress_bar("Based on {0}".format(doctype), count, total_count)
+ sub_set = invoice_due_dates[count:count+batch_size]
+ invoices = [d[0] for d in sub_set]
+
+ update_gl_entries(doctype, invoices, sub_set)
+ update_payment_entries(doctype, invoices, sub_set)
+
+ count += batch_size
+
+def update_gl_entries(doctype, invoices, invoice_due_dates):
+ when_then = get_when_then_for_gle(doctype, invoice_due_dates)
+
+ frappe.db.sql("""
+ UPDATE `tabGL Entry`
+ SET due_date = CASE
+ %s
+ ELSE `due_date` END
+ WHERE
+ (
+ (voucher_type = %s and voucher_no in (%s))
+ or (voucher_type in ('Journal Entry', 'Payment Entry')
+ and against_voucher in (%s))
+ )
+ and ifnull(party, '') != ''
+ and ifnull(due_date, '') = ''
+ """ % (when_then, '%s', ', '.join(['%s']*len(invoices)), ', '.join(['%s']*len(invoices))),
+ tuple([doctype] + invoices + invoices))
+
+def get_when_then_for_gle(doctype, data):
+ cond = ""
+ for d in data:
+ cond += """
+ WHEN (
+ (voucher_type = '{voucher_type}' and voucher_no = '{voucher_no}')
+ or (voucher_type in ('Journal Entry', 'Payment Entry')
+ and against_voucher = '{voucher_no}')
+ ) THEN '{date}'
+ """.format(voucher_type=doctype, voucher_no=frappe.db.escape(d[0]), date=d[1])
+
+ return cond
+
+def update_payment_entries(ref_doctype, invoices, invoice_due_dates):
+ for d in (
+ ("Payment Entry Reference", "reference_doctype", "due_date"),
+ ("Journal Entry Account", "reference_type", "reference_due_date")):
+
+ when_then = get_when_then_for_payment_entries(ref_doctype, d[1], invoice_due_dates)
+
+ frappe.db.sql("""
+ UPDATE `tab{doctype}`
+ SET {due_date_field} = CASE
+ {when_then}
+ ELSE `{due_date_field}` END
+ WHERE
+ {ref_doctype_fieldname} = '{ref_doctype}'
+ and reference_name in ({reference_names})
+ and ifnull({due_date_field}, '') = ''
+ """.format(
+ doctype = d[0],
+ due_date_field = d[2],
+ when_then = when_then,
+ ref_doctype_fieldname = d[1],
+ ref_doctype = ref_doctype,
+ reference_names = ', '.join(['%s']*len(invoices))
+ ), tuple(invoices))
+
+def get_when_then_for_payment_entries(ref_doctype, ref_doctype_fieldname, data):
+ cond = ""
+ for d in data:
+ cond += """
+ WHEN {ref_doctype_fieldname} = '{ref_doctype}'
+ and reference_name = '{voucher_no}'
+ THEN '{date}'
+ """.format(
+ ref_doctype_fieldname=ref_doctype_fieldname,
+ ref_doctype=ref_doctype,
+ voucher_no=frappe.db.escape(d[0]),
+ date=d[1])
+
+ return cond
\ No newline at end of file
diff --git a/erpnext/patches/v8_10/update_gl_due_date_for_pi_and_si.py b/erpnext/patches/v8_10/update_gl_due_date_for_pi_and_si.py
deleted file mode 100644
index 8596e66..0000000
--- a/erpnext/patches/v8_10/update_gl_due_date_for_pi_and_si.py
+++ /dev/null
@@ -1,138 +0,0 @@
-from __future__ import unicode_literals
-import frappe
-
-# This will update existing GL Entries by saving its linked Purchase/Sales Invoice's
-# Journal Entry's due date as the due date for the GL Entry
-
-
-def execute():
- frappe.reload_doc("accounts", "doctype", "gl_entry")
-
- kwargs = get_query_kwargs()
-
- for kwarg in kwargs:
- for batch in get_result_in_batches(**kwarg):
- voucher_num_col = kwarg.get('voucher_num_col', 'voucher_no')
- voucher_type = kwarg.get('use_voucher_type') or kwarg.get('voucher_type')
- conditions, names = build_conditions(batch, voucher_type, voucher_num_col)
- if conditions and names:
- start = 'UPDATE `tabGL Entry` SET `due_date` = CASE '
- cond = ' '.join(conditions)
- else_cond = ' ELSE `due_date` END WHERE '
-
- frappe.db.sql(
- start + cond + else_cond + voucher_num_col + ' IN %s',
- values=(names,)
- )
-
-
-def get_result_in_batches(**kwargs):
- """A simple generator to yield slices of GL Entry records"""
- while True:
- batch = get_gle_batch(**kwargs)
- if batch:
- yield batch
- else:
- return
-
-
-def get_gle_batch(**kwargs):
- """Returns a slice of records in GL Entry"""
- doctype = kwargs.get('doctype')
- fields = kwargs.get('fields')
- limit_start = kwargs.get('limit_start')
- limit_page_length = kwargs.get('limit_page_length')
- filters = kwargs.get('filters')
- or_filters = kwargs.get('or_filters')
-
- results = frappe.get_list(
- doctype, fields=fields, limit_start=limit_start, limit_page_length=limit_page_length,
- filters=filters, or_filters=or_filters
- )
-
- return results
-
-
-def build_conditions(query_results, voucher_type, voucher_num_col):
- """
- builds the string to be used is sql CASE statement. Returns the a tuple of
- the string for the CASE statement and a tuple of applicable voucher names
- """
- conditions = []
- invoice_names = []
-
- for result in query_results:
- voucher_no = result.get(voucher_num_col)
- if voucher_no:
- invoice_names.append("%s" % (voucher_no,))
-
- # get invoice details
- invoice_details = frappe.get_list(
- voucher_type, fields=['name', 'due_date'], filters={'name': ('in', invoice_names)}
- )
-
- if invoice_details:
- for d in invoice_details:
- conditions.append('WHEN `{voucher_no}`="{number}" THEN "{date}"'.format(
- number=d.name, date=d.due_date, voucher_no=voucher_num_col))
-
- return conditions, invoice_names
-
-
-def get_query_kwargs():
- pi_kwargs = dict(
- voucher_type='Purchase Invoice', doctype='GL Entry', fields=['voucher_no'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Purchase Invoice', 'credit': ('!=', '0')
- }
- )
-
- si_kwargs = dict(
- voucher_type='Sales Invoice', doctype='GL Entry', fields=['voucher_no'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Sales Invoice', 'debit': ('!=', '0')
- }
- )
-
- journal_kwargs_si = dict(
- voucher_type='Journal Entry', doctype='GL Entry', fields=['against_voucher'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Journal Entry', 'against_voucher_type': 'Sales Invoice'
- },
- voucher_num_col='against_voucher', use_voucher_type='Sales Invoice',
- )
-
- journal_kwargs_pi = dict(
- voucher_type='Journal Entry', doctype='GL Entry', fields=['against_voucher'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Journal Entry', 'against_voucher_type': 'Purchase Invoice'
- },
- voucher_num_col='against_voucher', use_voucher_type='Purchase Invoice',
- )
-
- payment_entry_kwargs_pi = dict(
- voucher_type='Payment Entry', doctype='GL Entry', fields=['against_voucher'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Payment Entry', 'against_voucher_type': 'Purchase Invoice'
- },
- voucher_num_col='against_voucher', use_voucher_type='Purchase Invoice',
- )
-
- payment_entry_kwargs_si = dict(
- voucher_type='Payment Entry', doctype='GL Entry', fields=['against_voucher'],
- limit_start=0, limit_page_length=5, filters={
- "ifnull(due_date, '')": ('=', ''), "ifnull(party, '')": ('!=', ''),
- 'voucher_type': 'Payment Entry', 'against_voucher_type': 'Sales Invoice'
- },
- voucher_num_col='against_voucher', use_voucher_type='Sales Invoice',
- )
-
- return [
- pi_kwargs, si_kwargs, journal_kwargs_pi, journal_kwargs_si,
- payment_entry_kwargs_pi, payment_entry_kwargs_si
- ]