<?php 

function get_employees_payroll_work_time_deficit_details($period_start_date, $period_end_date) {
    $sql = "SELECT DISTINCT employee_code 
            FROM ".TB_PREF."attendance 
            WHERE calculated_deficit_time <> '00:00:00' 
                AND entry_date >= ".db_escape($period_start_date)." 
                AND entry_date <= ".db_escape($period_end_date)." 
            ORDER BY employee_code";
    
        return db_query($sql, "get_employees_payroll_work_time_deficit_details");	
}

function get_payroll_work_time_deficit_details($period_start_date, $period_end_date, $employee_code) {
    $sql = "SELECT a.*, 
                sn.value_name shift_name, 
                s.start_time1, 
                s.duration1,
                wd.deducted_time,
                wd.comment,
                wd.id df_smmary_id,
                hd.attendance_status_code header_attendance_status_code 
            FROM ".TB_PREF."attendance a 
                JOIN ".TB_PREF."shifts s 
                    ON a.shift_id = s.id 
                JOIN ".TB_PREF."list_values sn  
                    ON sn.id = s.shift_list_value_id     
                JOIN ".TB_PREF."employee_attendances_header hd   
                    ON a.entry_date = hd.date      
                LEFT JOIN ".TB_PREF."payroll_worktime_deficit_summary wd 
                    ON a.employee_code = wd.employee_code 
                        AND a.deficit_payroll_run_id = wd.payroll_run_id 
            WHERE a.calculated_deficit_time <> '00:00:00' 
                AND a.entry_date >= ".db_escape($period_start_date)." 
                AND a.entry_date <= ".db_escape($period_end_date)." 
                AND a.employee_code = ".db_escape($employee_code)." 
            ORDER BY a.employee_code, a.from_date_time1, a.id";
	
        return db_query($sql, "get_payroll_work_time_deficit_details");	
}

function add_payroll_worktime_deficit_summary($payroll_run_id, $employee_code, $component, $calculated_time, $deducted_time, $comment, $status_code) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');

        $sql = "INSERT INTO ".TB_PREF."payroll_worktime_deficit_summary (
                    payroll_run_id, employee_code, component, 
                    calculated_time, deducted_time, comment, 
                    status_code, approved_by, approved_on) 
                VALUES ("
                    .db_escape($payroll_run_id).", ".db_escape($employee_code).", ".db_escape($component).", "
                    .db_escape($calculated_time). ", ".db_escape($deducted_time).", ".db_escape($comment).", "
                    .db_escape($status_code).", ".db_escape($updated_by).", ".db_escape($updated_on).")";

            db_query($sql, "add_payroll_worktime_deficit_summary");
            return db_insert_id(); 
}

function update_payroll_worktime_deficit_summary($id, $status_code, $calculated_time, $deducted_time, $comment) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."payroll_worktime_deficit_summary   
                    SET 
                    status_code = ".db_escape($status_code).",
                    calculated_time = ".db_escape($calculated_time).",
                    deducted_time = ".db_escape($deducted_time).",
                    comment = ".db_escape($comment).",
                    approved_by = ".db_escape($approved_by).",
                    approved_on = ".db_escape($approved_on)."
                WHERE id = ".db_escape($id)." 
                    AND status_code IN ('U')"; 
        
            db_query($sql, "update_payroll_worktime_deficit_summary");
            return db_num_affected_rows(); 
}

function delete_payroll_worktime_deficit_summary($id) {
    $sql = "DELETE FROM ".TB_PREF."payroll_worktime_deficit_summary   
            WHERE id = ".db_escape($id)." 
                AND status_code IN ('U')";

            db_query($sql, "delete_payroll_worktime_deficit_summary");
            return db_num_affected_rows(); 
}

function get_payroll_worktime_deficit_summary_record_by_employee_payroll_id($employee_code, $payroll_run_id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."payroll_worktime_deficit_summary  
            WHERE employee_code = ".db_escape($employee_code)." 
                AND payroll_run_id = ".db_escape($payroll_run_id); 
    
        $result = db_query($sql, "get_payroll_worktime_deficit_summary_record_by_employee_payroll_id");
        return db_fetch($result); 
}

function update_employee_attendance_deficit_details($id, $status_code, $deficit_payroll_run_id) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."attendance  
                    SET 
                    deficit_status_code = ".db_escape($status_code).",
                    deficit_payroll_run_id = ".db_escape($deficit_payroll_run_id).",
                    deficit_approved_by = ".db_escape($approved_by).",
                    deficit_approved_on = ".db_escape($approved_on)."
                WHERE id = ".db_escape($id);

            db_query($sql, "update_employee_attendance_deficit_details");
            return db_num_affected_rows(); 
}

// can add more filters if want
function get_work_time_deficit_records_by_filters($id=0, $date=null, $payroll_nun_id=0, $employee_code=null, $fetch=false) {
    $sql = "SELECT df.*, pr.id payroll_run_id 
            FROM ".TB_PREF."payroll_worktime_deficit_summary df                    
                JOIN ".TB_PREF."payroll_runs pr 
                    ON df.payroll_run_id = pr.id 
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND df.id = ".db_escape($id);
        }
        
        if ($date !== null) {
            $sql .= " AND pr.period_start_date <= ".db_escape($date)." 
                      AND pr.period_end_date >= ".db_escape($date);
        }
    
        if ($payroll_nun_id > 0) {
            $sql .= " AND pr.id = ".db_escape($payroll_nun_id);
        }
        
        if ($employee_code !== null) {
            $sql .= " AND df.employee_code = ".db_escape($employee_code);
        }
        
        $result = db_query($sql, "get_work_time_deficit_records_by_filters");
        
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result); 
            }
}

?>