fix: fetch queries
diff --git a/erpnext/patches/v12_0/generate_leave_ledger_entries.py b/erpnext/patches/v12_0/generate_leave_ledger_entries.py
index b8e82ef..8b1ca8c 100644
--- a/erpnext/patches/v12_0/generate_leave_ledger_entries.py
+++ b/erpnext/patches/v12_0/generate_leave_ledger_entries.py
@@ -49,17 +49,26 @@
def get_allocation_records():
return frappe.db.sql("""
+ WITH allocation_values AS (
+ SELECT
+ DISTINCT name,
+ employee,
+ leave_type,
+ new_leaves_allocated,
+ carry_forwarded_leaves,
+ from_date,
+ to_date,
+ carry_forward,
+ RANK() OVER(
+ PARTITION BY employee, leave_type
+ ORDER BY to_date DESC
+ ) as allocation
+ FROM `tabLeave Allocation`
+ )
SELECT
- DISTINCT name,
- employee,
- leave_type,
- new_leaves_allocated,
- carry_forwarded_leaves,
- from_date,
- to_date,
- carry_forward,
- RANK() OVER(PARTITION BY employee, leave_type ORDER BY to_date DESC) as allocation
- FROM `tabLeave Allocation`
+ *
+ FROM
+ `allocation_values`
WHERE
allocation=1
""", as_dict=1)
@@ -67,7 +76,7 @@
def get_leaves_application_records(allocation_list):
leave_applications = []
for allocation in allocation_list:
- leave_applications.append(frappe.db.sql("""
+ leave_applications += frappe.db.sql("""
SELECT
DISTINCT name,
employee,
@@ -78,15 +87,15 @@
FROM `tabLeave Application`
WHERE
from_date >= %s
- leave_type = %s
- employee = %s
- """, (allocation.from_date, allocation.leave_type, allocation.employee)))
+ AND leave_type = %s
+ AND employee = %s
+ """, (allocation.from_date, allocation.leave_type, allocation.employee))
return leave_applications
def get_leave_encashment_records(allocation_list):
leave_encashments = []
for allocation in allocation_list:
- leave_encashments.append(frappe.db.sql("""
+ leave_encashments += frappe.db.sql("""
SELECT
DISTINCT name,
employee,
@@ -97,6 +106,7 @@
FROM `tabLeave Encashment`
WHERE
leave_type = %s
- employee = %s
- """, (allocation.leave_type, allocation.employee)))
+ AND employee = %s
+ AND encashment_date >= %s
+ """, (allocation.leave_type, allocation.employee, allocation.from_date))
return leave_encashments
\ No newline at end of file