<?php

function get_unapproved_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 ('N', 'U', 'A', 'L') 
                    AND a.calculated_ot_time > '00:00:00' 
                    AND a.entry_date <= ".db_escape($cutoff_date)." 
            ORDER BY a.employee_code ";
	
    $result = db_query($sql, "get_unapproved_ot_employees");	
    return $result;		
}

function get_unapproved_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,
                    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."list_values ots 
                    ON a.ot_status_code = ots.value_code 
                        AND ots.list_code = 'ATTENDANCE_STATUS'    
                JOIN ".TB_PREF."employee_attendances_header hd
                    ON a.entry_date = hd.date 
                LEFT JOIN ".TB_PREF."list_values rt 
                    ON a.rate_table_id = rt.id  
            WHERE a.ot_status_code IN ('N', 'U', 'A', 'L') 
                    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";
	
    $result = db_query($sql, "get_unapproved_employee_ot_records");	
    return $result;		
}

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

        $sql = "UPDATE ".TB_PREF."attendance a 
                    JOIN ".TB_PREF."employee_attendances_header hd
                        ON a.entry_date = hd.date 
                SET a.granted_ot_time = ".db_escape(time2sql($granted_ot_time)).", 
                    a.rate_table_id = ".db_escape($rate_table_id).", 
                    a.ot_status_code = ".db_escape($status_code).",   
                    a.ot_approved_by = ".db_escape($approved_by).",
                    a.ot_approved_on = ".db_escape($approved_on)."
                WHERE a.id = ".db_escape($rec_id)." 
                    AND a.entry_date = ".db_escape($entry_date)." 
                    AND a.ot_status_code IN ('N','U')
                    AND hd.attendance_status_code IN ('A')";

        db_query($sql, "update_approved_attendance_ot");

        return db_num_affected_rows();
}

?>