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

function add_employee_shift($employee_code, 
                        $start_date, $shift_list_value_id, $week_start_day, 
                        $saturday_work_time_id, $sunday_work_time_id,
                        $continuous_work, $task_id)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
	$sql = "INSERT INTO ".TB_PREF."employee_shifts(
                        employee_code, start_date, 
                        shift_list_value_id, week_start_day,
                        saturday_work_time_id, sunday_work_time_id,
                        continuous_work, task_id,
                        created_by, created_on) 
                VALUES ("
                        .db_escape($employee_code).", ".db_escape($start_date).", "
                        .db_escape($shift_list_value_id).", ".db_escape($week_start_day).", "
                        .db_escape($saturday_work_time_id).", ".db_escape($sunday_work_time_id).", "
                        .db_escape($continuous_work).", ".db_escape($task_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

	db_query($sql,"Employee shift could not be added");
}

function update_employee_shift($selected_id, $employee_code, 
                                $start_date, $shift_list_value_id, $week_start_day, 
                                $saturday_work_time_id, $sunday_work_time_id,
                                $continuous_work, $task_id)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."employee_shifts 
                SET 
                    start_date =".db_escape($start_date).",
                    shift_list_value_id=".db_escape($shift_list_value_id).",
                    week_start_day=".db_escape($week_start_day).",
                    saturday_work_time_id=".db_escape($saturday_work_time_id).",
                    sunday_work_time_id=".db_escape($sunday_work_time_id).",
                    continuous_work=".db_escape($continuous_work).",
                    task_id=".db_escape($task_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
		WHERE id=".db_escape($selected_id)." AND 
                    employee_code = ".  db_escape($employee_code);
        
	db_query($sql,"Employee shift could not be updated");
}

function delete_employee_shift($selected_id)
{
        $sql = "DELETE FROM ".TB_PREF."employee_shifts WHERE id=".db_escape($selected_id);
        
	db_query($sql,"delete_employee_shift");
}


function get_employee_shifts($employee_code) {
    $sql = "SELECT es.*, s.value_name shift_name, lv.value_name description 
            FROM ".TB_PREF."employee_shifts es 
                INNER JOIN ".TB_PREF."list_values s 
                    ON es.shift_list_value_id = s.id    
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON es.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."list_values lv 
                    ON pt.type_id = lv.id 
            WHERE es.employee_code  = ".db_escape($employee_code)." 
            ORDER BY start_date";
                    
    return db_query($sql, "Employee shifts could not be retrieved");
}


function get_employee_shift_details($selected_id)
{
   $sql = "SELECT * FROM ".TB_PREF."employee_shifts WHERE id  = ".$selected_id;
                    
    $result =  db_query($sql, "Employee shift details could not be retrieved");
    
    return db_fetch($result);
}

function get_applicable_shift_details($employee_code, $from_date) 
{
    $sql = "SELECT s.* 
            FROM ".TB_PREF."employee_shifts es  
                JOIN ".TB_PREF."shifts s 
                    ON s.shift_list_value_id = es.shift_list_value_id 
            WHERE es.start_date <= ".db_escape($from_date)." 
                AND es.employee_code = ".db_escape($employee_code)." 
            ORDER BY es.start_date DESC LIMIT 1 ";
    
    $sql = "SELECT s.* 
            FROM ".TB_PREF."employee_shifts es  
                JOIN ".TB_PREF."shifts s 
                    ON s.shift_list_value_id = es.shift_list_value_id 
            WHERE es.start_date <= ".db_escape($from_date)." 
                AND s.from_date <= ".db_escape($from_date)." 
                AND es.employee_code = ".db_escape($employee_code)." 
            ORDER BY es.start_date DESC, s.from_date DESC LIMIT 1 ";
    
    $result = db_query($sql, "get_applicable_shift_details");  
    return db_fetch($result);
}

function get_employee_shifts_by_date($employee_code, $start_date, $id=0) {
    $sql = "SELECT s.* 
            FROM ".TB_PREF."employee_shifts es  
                JOIN ".TB_PREF."list_values s 
                    ON s.id = es.shift_list_value_id 
            WHERE es.start_date = ".db_escape(date2sql($start_date))." 
                AND es.employee_code = ".db_escape($employee_code);
    
    if ($id > 0) {
        $sql .= " AND es.id <> ".db_escape($id);
    }

    $sql .= " ORDER BY es.start_date DESC LIMIT 1";
    
    $result = db_query($sql, "get_employee_shifts_by_date");  
    return db_fetch($result);
}

function get_employee_shift_by_employee_shift_date($employee_code, $shift_list_value_id, $date) {
    $sql = "SELECT s.*, shi.start_time1, duration1 
            FROM ".TB_PREF."employee_shifts es  
                JOIN ".TB_PREF."list_values s 
                    ON s.id = es.shift_list_value_id 
                JOIN ".TB_PREF."".TB_PREF."shifts shi 
                    ON shi.shift_list_value_id = es.shift_list_value_id 
            WHERE es.employee_code = ".db_escape($employee_code)." 
                AND es.shift_list_value_id = ".db_escape($shift_list_value_id)." 
                AND es.start_date <= ".db_escape($date)." 
                AND shi.from_date <= ".db_escape($date);
    
    $sql .= " ORDER BY es.start_date DESC LIMIT 1";
    
    $result = db_query($sql, "get_employee_shift_by_employee_shift_date");  
    return db_fetch($result);
}

