Fetch Sum Total of Used Leaves

Frappe Framework Customization
,
1

Good Day all

I am trying to fetch the Sum Total of all Used Leave Days from Leave Application Doctype of only 1 Employee.
What I had done so far is create a Child table to manually summon all the Leave Apps and calculate the Leave Days
image

Here’s the Filter Code for the child table:

frappe.ui.form.on('Custom Doctype Name', {
	refresh(frm) {
        frm.set_query('child_docfield_leave_app','child_table_field_name', function(doc, cdt, cdn) {
            return {
                filters: {
                    'employee': frm.doc.employee,
                    'status': "Approved",
                    'leave_type': 'Annual Leave'
                }
            }
        });
	}
});

Here’s the calculation Code:

frappe.ui.form.on('Child Table Doctype', {
	child_docfield_days: function(frm, cdt, cdn) {
		// your code here
        var d = locals[cdt][cdn];
        var tableTotal = 0;
        frm.doc.child_table_field_name.forEach(function(d) {
            tableTotal += d.child_docfield_days;
        });
        frm.set_value("docfield_used_leaves", tableTotal);
        refresh_field("docfield_used_leaves");
	}
    ,child_table_field_name_remove: function(frm, cdt, cdn){
        var d = locals[cdt][cdn];
        var tableTotal = 0;
        frm.doc.child_table_field_name.forEach(function(d) {
            tableTotal += d.child_docfield_days;
        });
        frm.set_value("docfield_used_leaves", tableTotal);
        refresh_field("docfield_used_leaves");
}
});

I need help with fetching the Total Sum of approved Total Leave Days linked to the Employee Automatically, regardless of whether to insert them into the Child Table or to only fetch the Total Sum.

Note: I’m open to any coding language to provide a solution to my issue.

Thank you :slight_smile:

2

Hi,

Please check the two function below to calculate taken leave for specific period:

def get_leaves_for_period(
	employee: str,
	leave_type: str,
	from_date: datetime.date,
	to_date: datetime.date,
	skip_expired_leaves: bool = True,
) -> float:
	leave_entries = get_leave_entries(employee, leave_type, from_date, to_date)
	leave_days = 0

	for leave_entry in leave_entries:
		inclusive_period = leave_entry.from_date >= getdate(
			from_date
		) and leave_entry.to_date <= getdate(to_date)

		if inclusive_period and leave_entry.transaction_type == "Leave Encashment":
			leave_days += leave_entry.leaves

		elif (
			inclusive_period
			and leave_entry.transaction_type == "Leave Allocation"
			and leave_entry.is_expired
			and not skip_expired_leaves
		):
			leave_days += leave_entry.leaves

		elif leave_entry.transaction_type == "Leave Application":
			if leave_entry.from_date < getdate(from_date):
				leave_entry.from_date = from_date
			if leave_entry.to_date > getdate(to_date):
				leave_entry.to_date = to_date

			half_day = 0
			half_day_date = None
			# fetch half day date for leaves with half days
			if leave_entry.leaves % 1:
				half_day = 1
				half_day_date = frappe.db.get_value(
					"Leave Application", {"name": leave_entry.transaction_name}, ["half_day_date"]
				)

			leave_days += (
				get_number_of_leave_days(
					employee,
					leave_type,
					leave_entry.from_date,
					leave_entry.to_date,
					half_day,
					half_day_date,
					holiday_list=leave_entry.holiday_list,
				)
				* -1
			)

	return leave_days

def get_leave_entries(employee, leave_type, from_date, to_date):
	"""Returns leave entries between from_date and to_date."""
	return frappe.db.sql(
		"""
		SELECT
			employee, leave_type, from_date, to_date, leaves, transaction_name, transaction_type, holiday_list,
			is_carry_forward, is_expired
		FROM `tabLeave Ledger Entry`
		WHERE employee=%(employee)s AND leave_type=%(leave_type)s
			AND docstatus=1
			AND (leaves<0
				OR is_expired=1)
			AND (from_date between %(from_date)s AND %(to_date)s
				OR to_date between %(from_date)s AND %(to_date)s
				OR (from_date < %(from_date)s AND to_date > %(to_date)s))
	""",
		{"from_date": from_date, "to_date": to_date, "employee": employee, "leave_type": leave_type},
		as_dict=1,
	)