# Copyright (c) 2024, Company 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": _("SSN"),
			"fieldname": "ssn",
			"fieldtype": "Data",
			"width": 130
		},
	{
			"label": _("Davamsızlığın səbəbi"),
			"fieldname": "absent_reason",
			"fieldtype": "Data",
			"width": 180
		},
		{
			"label": _("İşçinin işləmədiyi iş günlərinin sayı"),
			"fieldname": "days_absent",
			"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,
			e.ssn,
			a.absent_reason,
			COUNT(*) AS days_absent
		FROM `tabAttendance` a
		LEFT JOIN `tabEmployee` e ON e.name = a.employee
		WHERE
			a.docstatus = 1
			AND a.status != 'Present'
			AND a.company = %(company)s
			AND a.attendance_date >= %(from_date)s
			AND a.attendance_date <= %(to_date)s
		GROUP BY a.employee, a.absent_reason
		ORDER BY e.last_name ASC, e.first_name ASC
	""", filters, as_dict=1)
