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

function add_shift($shift_list_value_id, $from_date, $shift_name, $day_start, $start_time1, $duration1, $split_shift, $start_time2, $duration2, $ot_start_gap, $qualifying_hrs_for_ot, 
                   $table1_id, $table2_id, $no_ot, $inactive) {
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
	$sql = "INSERT INTO ".TB_PREF."shifts (
                    shift_list_value_id, from_date, 
                    shift_name,day_start,
                    start_time1,duration1,
                    split_shift,
                    start_time2,duration2,
                    ot_start_gap, qualifying_hrs_for_ot, 
                    table1_id, table2_id, 
                    no_ot, inactive,
                    created_by, created_on) 
                VALUES ("
                    .db_escape($shift_list_value_id).", ".db_escape(date2sql($from_date)).", "
                    .db_escape($shift_name).", ".db_escape($day_start).", "
                    .db_escape($start_time1).", ".db_escape($duration1). ", "
                    .db_escape($split_shift).", "
                    .db_escape($start_time2).", ".db_escape($duration2). ", "
                    .db_escape($ot_start_gap).", ".db_escape($qualifying_hrs_for_ot). ", "
                    .db_escape($table1_id).", ".db_escape($table2_id).", " 
                    .db_escape($no_ot).", ".db_escape($inactive).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Shift could not be added");
}

function update_shift($rec_id, $from_date, $shift_name, $day_start, $start_time1, $duration1, $split_shift, $start_time2, $duration2, $ot_start_gap, $qualifying_hrs_for_ot, 
                      $table1_id, $table2_id, $no_ot, $inactive) {

    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."shifts 
                SET from_date = ".db_escape(date2sql($from_date)).",
                    shift_name = ".db_escape($shift_name).",
                    day_start = ".db_escape($day_start).",
                    start_time1 = ".db_escape($start_time1).",
                    duration1 = ".db_escape($duration1).",
                    split_shift = ".db_escape($split_shift).",
                    start_time2 = ".db_escape($start_time2).",
                    duration2 = ".db_escape($duration2).",
                    ot_start_gap = ".db_escape($ot_start_gap).",
                    qualifying_hrs_for_ot = ".db_escape($qualifying_hrs_for_ot).",
                    table1_id = ".db_escape($table1_id).",
                    table2_id = ".db_escape($table2_id).",
                    no_ot = ".db_escape($no_ot).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($rec_id);

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

function get_shift_details($selected_id)
{
    $sql = "SELECT * 
            FROM 
                ".TB_PREF."shifts  
            WHERE id = ".db_escape($selected_id);
  

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

function get_shifts_by_shift_list_value_id_date($shift_list_value_id, $from_date, $id=0) {
    $sql = "SELECT * 
            FROM ".TB_PREF."shifts  
            WHERE shift_list_value_id = ".db_escape($shift_list_value_id)." 
                AND from_date = ".db_escape(date2sql($from_date));
    
    if ($id > 0) {
        $sql .= " AND id <> ".db_escape($id);
    }
  
    $result = db_query($sql, "get_shifts_by_shift_list_value_id_date");
    return db_fetch($result);
}

function get_defined_shifts_by_shift_list_value_id_and_date($shift_list_value_id, $from_date) {
    $sql = "SELECT * 
            FROM ".TB_PREF."shifts  
            WHERE shift_list_value_id = ".db_escape($shift_list_value_id)." 
                AND from_date <= ".db_escape(date2sql($from_date))." 
            ORDER BY from_date DESC ";
  
    return db_query($sql, "get_defined_shifts_by_shift_list_value_id_and_date");
}

function get_shift_records($shift_list_value_id) {
    $sql = "SELECT s.*, 
                lv1.value_name table1_name,
                lv2.value_name table2_name  
            FROM ".TB_PREF."shifts s, "
                .TB_PREF."list_values lv1, "
                .TB_PREF."list_values lv2
            WHERE s.table1_id = lv1.id 
            	AND s.table2_id = lv2.id 
            	AND s.shift_list_value_id = ".db_escape($shift_list_value_id);
    
    return db_query($sql, "Shift could not be retrieved");
}

function get_shifts_by_date($date) {
    $sql = "SELECT DISTINCT sft.shift_list_value_id, lst.value_name shift  
            FROM ".TB_PREF."shifts sft 
                JOIN ".TB_PREF."list_values lst 
                    ON sft.shift_list_value_id = lst.id 
            WHERE from_date <= ".db_escape($date)." 
            ORDER BY from_date DESC ";
  
    return db_query($sql, "get_shifts_by_date");
}

function get_defined_shifts_by_shift_id_and_date($shift_id, $from_date) {
    $sql = "SELECT * 
            FROM ".TB_PREF."shifts  
            WHERE shift_list_value_id = ".db_escape($shift_id)." 
                AND from_date <= ".db_escape(date2sql($from_date))." 
            ORDER BY from_date DESC ";
  
    return db_query($sql, "get_defined_shifts_by_shift_id_and_date");
}


function delete_shift($selected_id)
{
    $sql = "DELETE FROM 
                ".TB_PREF."shifts  
            WHERE id = ".db_escape($selected_id);
  

    db_query($sql, "delete_shift");

}
