<?php 

function get_gate_pass_details($trans_type_id, $from_, $to_, $printed='All', $filtr_gp_no=0, $filtr_trans_no=0) {
    $from = date2sql($from_);
    $to = date2sql($to_);
    
    $other_filters = '';
    
    $select = 'gp.id rec_id, gp.vehicle_id, gp.vehicle_no, gp.driver_name, gp.comment, vh.description vehicle_name ';
    
        switch ($trans_type_id) {
            case ST_REQTRANSFER:
                $sql = " SELECT trn.*, $trans_type_id type, trn.mrt_no trans_no, trn.created_on trans_date, trn.comments memo, $select
                         FROM ".TB_PREF."material_requisition_transfers trn ";
                
                    $trans_no = 'mrt_no';
                    $trans_date = 'tran_date';

                break;
            case ST_PR_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.pri_no trans_no, trn.pri_date trans_date, trn.reference ref, trn.comment memo, $select
                         FROM ".TB_PREF."purchase_requisition_issues trn ";
                
                    $trans_no = 'pri_no';
                    $trans_date = 'pri_date';

                break;
            case ST_MATERIAL_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.im_no trans_no, trn.im_date trans_date, trn.reference ref, trn.comment memo, $select 
                         FROM ".TB_PREF."issue_materials trn ";

                    $trans_no = 'im_no';
                    $trans_date = 'im_date';
                    
                break;
            case ST_LOCTRANSFER:
                $sql = " SELECT trn.*, $trans_type_id type, trn.id trans_no, trn.tran_date trans_date, trn.reference ref, trn.comments memo, $select 
                         FROM ".TB_PREF."stock_transfers trn ";

                    $trans_no = 'id';
                    $trans_date = 'tran_date';
                    
                break;
            case ST_INVENTORY_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.id trans_no, trn.ai_date trans_date, trn.reference ref, trn.comments memo, $select 
                         FROM ".TB_PREF."stock_adjustments trn ";

                    $trans_no = 'id';
                    $trans_date = 'ai_date';
                    
                break;
            case ST_DAILY_ITEM_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.trans_no, trn.tran_date trans_date, trn.reference ref, trn.comments memo, $select 
                         FROM ".TB_PREF."daily_item_issues trn ";

                    $trans_no = 'trans_no';
                    $trans_date = 'tran_date';
                    $other_filters = " AND trn.approval_status = ".db_escape(AS_APPROVED)."  
                                       AND trn.type = ".db_escape(ST_DAILY_ITEM_ISSUE);
                    
                break;
            case ST_DAILY_TOOL_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.trans_no, trn.tran_date trans_date, trn.reference ref, trn.comments memo, $select 
                         FROM ".TB_PREF."daily_tool_issues trn ";

                    $trans_no = 'trans_no';
                    $trans_date = 'tran_date';
                    $other_filters = " AND trn.approval_status = ".db_escape(AS_APPROVED)."  
                                       AND trn.type = ".db_escape(ST_DAILY_TOOL_ISSUE);
                    
                break;
            case ST_RAW_MATERIAL_ISSUE:
                $sql = " SELECT trn.*, $trans_type_id type, trn.trans_no, trn.tran_date trans_date, trn.reference ref, trn.comments memo, $select 
                         FROM ".TB_PREF."raw_material_issues trn ";

                    $trans_no = 'trans_no';
                    $trans_date = 'tran_date';
                    $other_filters = " AND trn.approval_status = ".db_escape(AS_APPROVED)."  
                                       AND trn.type = ".db_escape(ST_RAW_MATERIAL_ISSUE);
                    
                break;
            case ST_ASSET_TRANSFER:
                $sql = " SELECT trn.*, trn.type, trn.trans_no, trn.tran_date trans_date, trn.reference ref, trn.comment memo, $select 
                         FROM ".TB_PREF."asset_transfers trn ";

                    $trans_no = 'trans_no';
                    $trans_date = 'tran_date';
                    $other_filters = " AND trn.type = ".db_escape(ST_ASSET_TRANSFER);
                                      
                break;
            case ST_ASSET_REQUISITION_TRANSFER:
                $sql = " SELECT trn.*, trn.type, trn.trans_no, trn.tran_date trans_date, trn.reference ref, trn.comment memo, $select 
                         FROM ".TB_PREF."asset_transfers trn ";

                    $trans_no = 'trans_no';
                    $trans_date = 'tran_date';
                    $other_filters = " AND trn.type = ".db_escape(ST_ASSET_REQUISITION_TRANSFER);

                break;
        }
                    
            $sql .= " LEFT JOIN ".TB_PREF."gate_passes gp 
                        ON trn.$trans_no = gp.trans_no  
                            AND gp.type = ".db_escape($trans_type_id)." 
                      LEFT JOIN ".TB_PREF."vehicles vh 
                        ON gp.vehicle_id = vh.id 
        
                WHERE trn.$trans_date >= ".db_escape($from)."  
                    AND trn.$trans_date <= ".db_escape($to)." 
                    $other_filters ";

                if ($trans_type_id > 0 && $trans_type_id == ST_REQTRANSFER) {
                    $sql .=" AND trn.type = ".db_escape($trans_type_id);
                }        

                if ($printed == 'Printed') {
                    $sql .= " AND gp.id > 0 ";
                } elseif ($printed == 'Not Printed') {
                    $sql .= " AND gp.id IS NULL";
                }
                if ($filtr_gp_no > 0) {
                    $sql .=" AND gp.id = ".db_escape($filtr_gp_no);
                }        
                if ($filtr_trans_no > 0) {
                    $sql .=" AND gp.trans_no = ".db_escape($filtr_trans_no);
                }   
                
                $sql .= " ORDER BY trn.$trans_date, trn.$trans_no";

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

function get_gate_pass_record($trans_no, $type) {
    $sql = "SELECT * 
            FROM ".TB_PREF."gate_passes 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        $result = db_query($sql, "get_gate_pass_record");
        return db_fetch($result);   
}

function update_gate_pass($id, $vehicle_id, $vehicle_no, $driver_name, $comment) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."gate_passes  
            SET vehicle_id = ".db_escape($vehicle_id).",
                vehicle_no = ".db_escape($vehicle_no).",
                driver_name = ".db_escape($driver_name).",
                comment = ".db_escape($comment).",
                printed_by = ".db_escape($printed_by).",
                printed_on = ".db_escape($printed_on)."  
            WHERE id = ".db_escape($id);

	db_query($sql, "update_gate_pass");
}

function add_gate_pass($trans_type, $trans_no, $vehicle_id, $vehicle_no, $driver_name, $comment) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."gate_passes  
                    (type, trans_no,  
                     vehicle_id, vehicle_no,
                     driver_name, comment, 
                     printed_by, printed_on) 
                VALUES(".db_escape($trans_type).", ".db_escape($trans_no).",
                       ".db_escape($vehicle_id).", ".db_escape($vehicle_no).", 
                       ".db_escape($driver_name).", ".db_escape($comment).", 
                       ".db_escape($printed_by).", ".db_escape($printed_on).")";
    
        db_query($sql, "add_gate_pass");
        return db_insert_id();
}

?>