<?php 

function get_job_gate_pass_details($jo_id, $from_, $to_, $job_completed, $printed, $filtr_gp_no=0, $temp_print=0) {
    $from = date2sql($from_);
    $to = date2sql($to_);
    
        $sql = "SELECT jb.*, gp.id rec_id, gp.driver_name, gp.comment, vh.long_description vehicle_name, gp.temp_print  
                FROM ".TB_PREF."job_orders jb  
                    LEFT JOIN ".TB_PREF."asset_master vh 
                        ON jb.vehicle_id = vh.asset_id 
                    LEFT JOIN ".TB_PREF."job_gate_passes gp 
                        ON jb.id = gp.jo_id 
                WHERE 1 = 1";

                if ($jo_id > 0 || $filtr_gp_no > 0) {
                    if ($jo_id > 0) {
                        $sql .=" AND jb.id = ".db_escape($jo_id);
                    }
                    if ($filtr_gp_no > 0) {
                        $sql .=" AND gp.id = ".db_escape($filtr_gp_no);
                    } 
                    
                } else { 
                    
                    $sql .= " AND jb.date_ >= ".db_escape($from)." AND jb.date_ <= ".db_escape($to);
                    
                    if ($job_completed == 'Yes') {
                        $sql .= " AND jb.job_completed = 1";
                    } elseif ($job_completed == 'No') {
                        $sql .= " AND jb.job_completed = 0";
                    }
                    
                    if ($printed == 'Printed') {
                        $sql .= " AND gp.id > 0";
                    } elseif ($printed == 'Not Printed') {
                        $sql .= " AND gp.id IS NULL";
                    }
                    
                    if ($temp_print == 'Yes') {
                        $sql .= " AND gp.temp_print = 1";
                    } elseif ($temp_print == 'No') {
                        $sql .= " AND gp.temp_print = 0";
                    }
                }
                
                $sql .= " ORDER BY jb.id DESC";

	return db_query($sql, "get_job_gate_pass_details");
}

function get_job_gate_pass_by_jo_id($jo_id) {
    $sql = "SELECT jb.*, gp.id rec_id, gp.driver_name, gp.comment, us.real_name, gp.temp_print 
            FROM ".TB_PREF."job_orders jb  
                LEFT JOIN ".TB_PREF."job_gate_passes gp 
                    ON jb.id = gp.jo_id 
                LEFT JOIN ".TB_PREF."users us 
                    ON gp.printed_by = us.id  
            WHERE jb.id = ".db_escape($jo_id);

	$result = db_query($sql, "get_job_gate_pass_by_jo_id");
        return db_fetch($result);
}

function get_job_gate_pass_record_by_jo_id($jo_id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_gate_passes 
            WHERE jo_id = ".db_escape($jo_id);
    
        $result = db_query($sql, "get_job_gate_pass_record_by_jo_id");
        return db_fetch($result);   
}

function update_job_gate_pass($id, $driver_name, $comment, $temp_print=0) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."job_gate_passes  
            SET driver_name = ".db_escape($driver_name).",
                comment = ".db_escape($comment).",";
    
    if ($temp_print != 0) {
        $sql .= " temp_print = ".db_escape($temp_print).", ";
    }
    
    $sql .= " printed_by = ".db_escape($printed_by).", 
                printed_on = ".db_escape($printed_on)."  
            WHERE id = ".db_escape($id);

	db_query($sql, "update_job_gate_pass");
}

function add_job_gate_pass($jo_id, $driver_name, $comment, $temp_print=0) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."job_gate_passes  
                    (jo_id, driver_name, comment, 
                     temp_print, printed_by, printed_on) 
                VALUES(".db_escape($jo_id).", ".db_escape($driver_name).", ".db_escape($comment).", 
                       ".db_escape($temp_print).", ".db_escape($printed_by).", ".db_escape($printed_on).")";
    
        db_query($sql, "add_job_gate_pass");
        return db_insert_id();
}

?>