# Copyright (c) 2026, Jey Soft and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	columns = get_columns()
	data = get_data(filters)
	return columns, data


def get_columns():
	return [
		{
			"label": _("Soyad"),
			"fieldname": "last_name",
			"fieldtype": "Data",
			"width": 140,
		},
		{
			"label": _("Ad"),
			"fieldname": "first_name",
			"fieldtype": "Data",
			"width": 120,
		},
		{
			"label": _("FİN"),
			"fieldname": "fin",
			"fieldtype": "Data",
			"width": 110,
		},
		{
			"label": _("İşçinin faktiki işə cəlb olunduğu iş günlərinin sayı"),
			"fieldname": "days_present",
			"fieldtype": "Int",
			"width": 160,
		},
	]


def get_data(filters):
	if not filters:
		filters = {}

	return frappe.db.sql("""
		SELECT
			e.last_name,
			e.first_name,
			e.passport_number AS fin,
			COUNT(*) AS days_present
		FROM `tabAttendance` a
		LEFT JOIN `tabEmployee` e ON e.name = a.employee
		WHERE
			a.docstatus = 1
			AND a.status IN ('Present', 'Half Day')
			AND a.company = %(company)s
			AND a.attendance_date >= %(from_date)s
			AND a.attendance_date <= %(to_date)s
		GROUP BY a.employee
		ORDER BY e.last_name ASC, e.first_name ASC
	""", filters, as_dict=1)
