<?php
/**********************************************************************
***********************************************************************/


function get_attendance_records($selected_id, $from_date_time, $to_date_time, $employee_code, $session)
{
    $sql = "SELECT 
                a.*
            FROM 
                ".TB_PREF."attendance a  
            WHERE 
                a.to_date_time$session > ".db_escape($from_date_time)." AND 
                a.from_date_time$session < ".db_escape($to_date_time);
    
    if(strlen($employee_code) > 0)
        $sql .= " AND a.employee_code = ".db_escape($employee_code) ;
    
    
    if($selected_id > 0)
        $sql .= " AND id <> ".$selected_id;

    return db_query($sql, "Overlapping attendance records could not be retrieved");
}


function get_overlapping_attendance_records($selected_id, $from_date_time, $to_date_time, $employee_code, $session)
{
    $sql = "SELECT 
                a.*
            FROM 
                ".TB_PREF."attendance a  
            WHERE 
                a.to_date_time$session > ".db_escape($from_date_time)." AND 
                a.from_date_time$session < ".db_escape($to_date_time);
    
    if(strlen($employee_code) > 0){
        $sql .= " AND a.employee_code = ".db_escape($employee_code) ;
    }
    
    
    if($selected_id > 0){
        $sql .= " AND id <> ".$selected_id;
    }

    return db_query($sql, "Overlapping attendance records could not be retrieved");
}


function add_attendance($cal)
{       $employee_code = $cal['employee_code']; 
        $task_id = $cal['task_id']; 
        $shift_id = $cal['shift_id'];
        $from_date_time1 = $cal['from_date_time1'];
        $to_date_time1 = $cal['to_date_time1'];
        $out_of_office_time1 = $cal['out_of_office_time1'];
        $counted_duration1 = $cal['counted_duration1'];
        $split_shift = $cal['split_shift'];
        $calculated_deficit_time = $cal['calculated_deficit_time'];
        $deducted_deficit_time = $cal['deducted_deficit_time'];
        $calculated_ot_time = $cal['calculated_ot_time'];
        $granted_ot_time = $cal['granted_ot_time'];
        $rate_table_id = $cal['rate_table_id'];
        $comment = $cal['comment'];
        $ot_status_code = $cal['ot_status_code'];
        $deficit_status_code = $cal['deficit_status_code'];
        $work_time_name = $cal['work_time_name'];

    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $entry_date = substr($from_date_time1,0,10);
        
    $sql = "INSERT INTO ".TB_PREF."attendance(employee_code, task_id,
                        shift_id, 
                        entry_date,
                        from_date_time1, to_date_time1, 
                        out_of_office_time1, counted_duration1,
                        split_shift,
                        calculated_deficit_time, deducted_deficit_time,
                        calculated_ot_time, granted_ot_time, 
                        rate_table_id,
                        comment, 
                        ot_status_code, deficit_status_code, 
                        work_time_name,
                        created_by, created_on) 
                VALUES (".db_escape($employee_code).", ".db_escape($task_id).", "
                        .db_escape($shift_id).", "
                        .db_escape($entry_date).", "
                        .db_escape($from_date_time1).", ".db_escape($to_date_time1).", "
                        .db_escape($out_of_office_time1).",".db_escape($counted_duration1).","
                        .db_escape($split_shift).", "
                        .db_escape($calculated_deficit_time).", ".db_escape($deducted_deficit_time).", "
                        .db_escape($calculated_ot_time).", ".db_escape($granted_ot_time).", "
                        .db_escape($rate_table_id).", "
                        .db_escape($comment).", "
                        .db_escape($ot_status_code).", ".db_escape($deficit_status_code).", "
                        .db_escape($work_time_name).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "Attendance record could not be added");
    
    return db_insert_id();         
    
}

function update_attendance($rec_id, $cal)
{        
        $task_id = $cal['task_id'];
        $shift_id = $cal['shift_id'];
        $from_date_time1 = $cal['from_date_time1'];
        $to_date_time1 = $cal['to_date_time1'];
        $out_of_office_time1 = $cal['out_of_office_time1'];
        $counted_duration1 = $cal['counted_duration1'];
        $split_shift = $cal['split_shift'];
        $calculated_deficit_time = $cal['calculated_deficit_time'];
        $deducted_deficit_time = $cal['deducted_deficit_time'];
        $calculated_ot_time = $cal['calculated_ot_time'];
        $granted_ot_time = $cal['granted_ot_time'];
        $rate_table_id = $cal['rate_table_id'];
        $comment = $cal['comment'];
        $ot_status_code = $cal['ot_status_code'];
        $deficit_status_code = $cal['deficit_status_code'];
        $work_time_name = $cal['work_time_name'];

    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
        
    $sql = "UPDATE ".TB_PREF."attendance 
            SET task_id = ".db_escape($task_id).", 
                shift_id = ".db_escape($shift_id).", 
                from_date_time1 = ".db_escape($from_date_time1).", 
                to_date_time1 = ".db_escape($to_date_time1).", 
                out_of_office_time1 = ".db_escape($out_of_office_time1).",
                counted_duration1 = ".db_escape($counted_duration1).", 
                split_shift = ".db_escape($split_shift).", 
                calculated_deficit_time = ".db_escape($calculated_deficit_time).", 
                deducted_deficit_time = ".db_escape($deducted_deficit_time).", 
                calculated_ot_time = ".db_escape($calculated_ot_time).",
                granted_ot_time = ".db_escape($granted_ot_time).", 
                rate_table_id = ".db_escape($rate_table_id).", 
                comment = ".db_escape($comment).", 
                ot_status_code = ".db_escape($ot_status_code).", 
                deficit_status_code = ".db_escape($deficit_status_code).", 
                work_time_name = ".db_escape($work_time_name).", 
                updated_by = ".db_escape($updated_by).", 
                updated_on = ".db_escape($updated_on)." 
            WHERE id = ".$rec_id;
    
    db_query($sql, "Attendance record could not be updated"); 
    
    return db_num_affected_rows();
    
}

function get_shift_schedule_details($employee_code, $from_date_time1, $shift_id = 0)
{
    $sql = "SELECT * FROM ".TB_PREF."employee_shifts 
                WHERE start_date <= ".db_escape($from_date_time1)." AND 
                        employee_code = ".db_escape($employee_code);
    if($shift_id > 0){
        $sql .= " AND shift_id = ".db_escape($shift_id);
    }
    
    $sql .= " ORDER BY start_date DESC LIMIT 1 ";
    
    $result = db_query($sql, "Shift schedule shift record could not be retrieved");  
    
    $row = db_fetch($result);
    
    if($row){
        return $row;
    }
    else{
        return get_shift_schedule_defaults();
    }
}

function get_shift_schedule_defaults()
{
    $a;
    
    $sql = "SELECT parameter, value FROM ".TB_PREF."module_settings WHERE module = 'payroll'";
    $result = db_query($sql);
    
    while($pref = db_fetch_assoc($result)) {
			$a[$pref['parameter']] = $pref['value'];
    }
    
    $a['start_date'] = '1970-01-01';
    $a['shift_schedule_id'] = 0;
    $a['employee_code'] = '';
    $a['id'] = 0;
    $a['task_id'] = 0;
    
    return $a;
}


function get_holiday_time_details($date)
{  
	$sql = "SELECT h.ot_type, h.holiday_name, h.holiday_time_code, lv.value_name holiday_time_name 
                FROM ".TB_PREF."holidays h,
                    ".TB_PREF."list_values lv
		WHERE h.holiday_time_code = lv.value_code AND 
                        lv.list_code = 'HOLIDAY_TIME' AND 
                        holiday_date =".db_escape($date);
	
	$result = db_query($sql,"Could not retrive holiday time details");	
        
        return db_fetch($result);
}

function get_period_attendance_records($from_date, $to_date, $employee_code, $search_days = '')
{
    $sql1 = "SELECT 
                c.id cid, c.date, c.work_time_code, 
                a.*,
                lv.value_name calendar_work_time_name,
                ots.value_code ot_status_code, ots.value_name ot_status_name, 
                dfs.value_code deficit_status_code, dfs.value_name deficit_status_name,
                u.real_name,
                s.shift_name, 
                s.start_time1 shift_start_time1, s.duration1 shift_duration1, 
                s.split_shift,
                s.start_time2 shift_start_time2, s.duration2 shift_duration2, 
                s.ot_start_gap, s.qualifying_hrs_for_ot,
                rt.value_name rate_table_name,
                h.holiday_name,
                h.ot_type holiday_ot_type,
                h.holiday_time_code,
                ht.value_name holiday_time_name ";
    
    $sql2 = " FROM
                ".TB_PREF."work_time_calendars c 
                INNER JOIN ".TB_PREF."list_values lv on 
                        c.work_time_code = lv.value_code AND
                        lv.list_code = 'WORKING_TIME' AND 
                        c.date >= ".  db_escape($from_date)." AND 
                        c.date <= ".  db_escape($to_date)."  
                LEFT JOIN ".TB_PREF."attendance a ON 
                        c.date = DATE_FORMAT(a.from_date_time1,'%Y-%m-%d') AND 
                        a.employee_code = ".db_escape($employee_code)."  
                LEFT 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 dfs on
                        a.deficit_status_code = dfs.value_code AND dfs.list_code = 'ATTENDANCE_STATUS'
                LEFT JOIN ".TB_PREF."users u on
                        a.created_by = u.id  
                LEFT JOIN ".TB_PREF."shifts s on 
                        a.shift_id = s.id 
                LEFT JOIN ".TB_PREF."list_values rt on
                        a.rate_table_id = rt.id
                LEFT JOIN ".TB_PREF."holidays h ON 
                        c.date = DATE_FORMAT(h.holiday_date,'%Y-%m-%d') 
                LEFT JOIN ".TB_PREF."list_values ht on
                        h.holiday_time_code = ht.value_code AND ht.list_code = 'HOLIDAY_TIME'";
    
    if($search_days == 'E'){
        $sql2 .= " WHERE a.id > 0 ";
    }
    elseif($search_days == 'N'){
        $sql2 .= " WHERE ISNULL(a.id) ";
    }
     
    $sql2 .= " ORDER BY
                        c.date, a.from_date_time1";
    
    return db_query($sql1.$sql2, "Attendance records could not be retrieved");
}

function get_attendance_record($id)
{
    $sql = "SELECT 
                a.*, em.last_name 
            FROM 
                ".TB_PREF."attendance a,
                ".TB_PREF."employee_master em  
            WHERE 
                a.employee_code = em.employee_code AND 
                a.id = ".$id ;

    $result = db_query($sql, "Attendance record could not be retrieved");
    
    return db_fetch($result);
}


function get_unapproved_attendance_records()
{
    $sql = "SELECT 
                v_al.*, 
                em.first_name, em.last_name,
                ls.value_name leave_status_name,
                u.real_name 
            FROM 
                ".TB_PREF."vw_attendance_with_leaves v_al,
                ".TB_PREF."list_values ls ,
                ".TB_PREF."employee_master em,
                ".TB_PREF."users u    
            WHERE 
                em.employee_code = v_al.employee_code AND 
                v_al.status_code = ls.value_code AND  ls.list_code = 'LEAVE_STATUS' AND 
                u.id = v_al.created_by 
            ORDER BY
                v_al.employee_code, v_al.from_date_time" ;

    return db_query($sql, "Attendance records could not be retrieved");
}
   

function delete_attendance_record($selected_id)
{
    $sql = "DELETE FROM ".TB_PREF."attendance WHERE id = ".db_escape($selected_id); 
    
    db_query($sql, "Attendance record could not be deleted");
    
}


function get_view_attendance_record($id)
{
    $sql = "SELECT 
                c.id , c.date, c.work_time_code, 
                a.id aid, a.employee_code, a.shift_id, 
                a.from_date_time1 , a.to_date_time1 , a.counted_duration1, 
                a.from_date_time2 , a.to_date_time2 , a.counted_duration2,
                a.granted_ot_time, a.rate_table_id,
                a.deducted_deficit_time,
                a.comment, a.created_on,
                lv.value_name work_time_name,
                ots.value_code ot_status_code, ots.value_name ot_status_name, 
                dfs.value_code deficit_status_code, dfs.value_name deficit_status_name,
                u.real_name,
                s.shift_name, 
                s.start_time1 shift_start_time1, s.duration1 shift_duration1, 
                s.split_shift,
                s.start_time2 shift_start_time2, s.duration2 shift_duration2, 
                s.ot_start_gap, s.qualifying_hrs_for_ot,
                rt.value_name rate_table_name 
            FROM
                ".TB_PREF."work_time_calendars c 
                INNER JOIN ".TB_PREF."list_values lv on 
                        c.work_time_code = lv.value_code AND
                        lv.list_code = 'WORKING_TIME'  
                INNER JOIN ".TB_PREF."attendance a ON 
                        c.date = DATE_FORMAT(a.from_date_time1,'%Y-%m-%d') AND 
                        a.id = ".db_escape($id)."  
                LEFT 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 dfs on
                        a.deficit_status_code = dfs.value_code AND dfs.list_code = 'ATTENDANCE_STATUS'              
                LEFT JOIN ".TB_PREF."users u on
                        a.created_by = u.id  
                LEFT JOIN ".TB_PREF."shifts s on 
                        a.shift_id = s.id 
                LEFT JOIN ".TB_PREF."list_values rt on
                        a.rate_table_id = rt.id
                ORDER BY
                        c.date, a.from_date_time1";
    
    $result = db_query($sql, "Attendance record could not be retrieved");
    
    return db_fetch($result);
}





?>