<?php

function get_approved_and_calculated_ot_employees($cutoff_date) {
    $sql = "SELECT DISTINCT a.employee_code 
            FROM ".TB_PREF."attendance a 
                JOIN ".TB_PREF."shifts s 
                    ON a.shift_id = s.id 
                JOIN ".TB_PREF."list_values ots 
                    ON a.ot_status_code = ots.value_code 
                        AND ots.list_code = 'ATTENDANCE_STATUS'    
                LEFT JOIN ".TB_PREF."list_values rt 
                    ON a.rate_table_id = rt.id   
            WHERE a.ot_status_code IN ('A', 'L') 
                AND a.calculated_ot_time > '00:00:00' 
                AND a.entry_date <= ".db_escape($cutoff_date)." 
            ORDER BY a.employee_code ";
	
    return db_query($sql, "get_approved_and_calculated_ot_employees");	
}

function get_approved_and_calculated_employee_ot_records($employee_code, $cutoff_date) {
    $sql = "SELECT a.*, 
                sn.value_name shift_name,
                ots.value_name ot_status_name, 
                rt.value_name rate_table_name,
                s.table1_id 
            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."list_values ots 
                    ON a.ot_status_code = ots.value_code 
                        AND ots.list_code = 'ATTENDANCE_STATUS'    
                LEFT JOIN ".TB_PREF."list_values rt 
                    ON a.rate_table_id = rt.id   
            WHERE a.ot_status_code IN ('A', 'L', 'C') 
                AND a.calculated_ot_time > '00:00:00' 
                AND a.entry_date <= ".db_escape($cutoff_date)." 
                AND a.employee_code = ".db_escape($employee_code)." 
            ORDER BY a.from_date_time1, a.id";
	
    return db_query($sql, "get_approved_and_calculated_employee_ot_records");	
}

function update_approved_or_calculated_attendance_ot_status_code($rec_id, $status_code) {  
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');

    $sql = "UPDATE ".TB_PREF."attendance 
            SET ot_status_code = ".db_escape($status_code).",   
                ot_approved_by = ".db_escape($approved_by).",
                ot_approved_on = ".db_escape($approved_on)."
            WHERE id = ".db_escape($rec_id)." 
                AND ot_status_code IN ('A','L')";

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

?>