fix: Remove duplicate leave ledger entry (#21871)
* fix: Remove duplicate leave ledger entry
* fix: Remove duplicate leave ledger entry
diff --git a/erpnext/patches/v12_0/remove_duplicate_leave_ledger_entries.py b/erpnext/patches/v12_0/remove_duplicate_leave_ledger_entries.py
index 6353304..24286dc 100644
--- a/erpnext/patches/v12_0/remove_duplicate_leave_ledger_entries.py
+++ b/erpnext/patches/v12_0/remove_duplicate_leave_ledger_entries.py
@@ -6,6 +6,7 @@
def execute():
"""Delete duplicate leave ledger entries of type allocation created."""
+ frappe.reload_doc('hr', 'doctype', 'leave_ledger_entry')
if not frappe.db.a_row_exists("Leave Ledger Entry"):
return
@@ -14,31 +15,32 @@
def get_duplicate_records():
"""Fetch all but one duplicate records from the list of expired leave allocation."""
- return frappe.db.sql_list("""
- WITH duplicate_records AS
- (SELECT
- name, transaction_name, is_carry_forward,
- ROW_NUMBER() over(partition by transaction_name order by creation)as row
- FROM `tabLeave Ledger Entry` l
- WHERE (EXISTS
- (SELECT name
- FROM `tabLeave Ledger Entry`
- WHERE
- transaction_name = l.transaction_name
- AND transaction_type = 'Leave Allocation'
- AND name <> l.name
- AND employee = l.employee
- AND docstatus = 1
- AND leave_type = l.leave_type
- AND is_carry_forward=l.is_carry_forward
- AND to_date = l.to_date
- AND from_date = l.from_date
- AND is_expired = 1
- )))
- SELECT name FROM duplicate_records WHERE row > 1
+ return frappe.db.sql("""
+ SELECT name, employee, transaction_name, leave_type, is_carry_forward, from_date, to_date
+ FROM `tabLeave Ledger Entry`
+ WHERE
+ transaction_type = 'Leave Allocation'
+ AND docstatus = 1
+ AND is_expired = 1
+ GROUP BY
+ employee, transaction_name, leave_type, is_carry_forward, from_date, to_date
+ HAVING
+ count(name) > 1
+ ORDER BY
+ creation
""")
def delete_duplicate_ledger_entries(duplicate_records_list):
"""Delete duplicate leave ledger entries."""
if not duplicate_records_list: return
- frappe.db.sql('''DELETE FROM `tabLeave Ledger Entry` WHERE name in %s''', ((tuple(duplicate_records_list)), ))
\ No newline at end of file
+ for d in duplicate_records_list:
+ frappe.db.sql('''
+ DELETE FROM `tabLeave Ledger Entry`
+ WHERE name != %s
+ AND employee = %s
+ AND transaction_name = %s
+ AND leave_type = %s
+ AND is_carry_forward = %s
+ AND from_date = %s
+ AND to_date = %s
+ ''', tuple(d))
\ No newline at end of file