<?php

function get_job_material_requisition_headers($jo_id) {
    $sql = "SELECT sa.id,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                sa.approval_status, 
                sa.source_trans_no, 
                sa.source_type  
            FROM ".TB_PREF."job_material_requisitions sa  
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
            WHERE sa.type = ".db_escape(ST_JOB_MATERIAL_REQ)." 
                AND sa.jo_id = ".db_escape($jo_id)." 
            ORDER BY sa.id";
    
    return db_query($sql, "get_job_material_requisition_headers");
}

function write_job_material_requisition_header($type, &$trans_no, &$id, $reference, $project_id, $location, $comments, $date, $jo_id=0, $source_type=-1, $source_trans_no=0, 
                                               &$row_state=0) {
    global $Refs;
     
        if ($id > 0) {
            $af_row = update_job_material_requisition_header($id, $reference, $location, $comments, $date, $row_state);
                if (! ($af_row > 0)) {
                    return 0;
                }
            $row_state = db_new_row_state($row_state);  
        } else {
            $trans_no = get_next_trans_no($type);
            $id = insert_job_material_requisition_header($type, $trans_no, $reference, $project_id, $location, $comments, $date, $jo_id, $source_type, $source_trans_no);
        }
    
        $Refs->save($type, $trans_no, $reference);

        if (! ($id > 0)) {
            return 0;
        }

        return $id;
}

function write_job_material_requisition_header_by_type_trans_no($type, $trans_no, $reference, $project_id, $location, $comments, $date, $source_id=0) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_job_material_requisition_header_by_type_trans_no($type, $trans_no, $reference, $location, $comments, $date);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $inser_id = insert_job_material_requisition_header($type, $trans_no, $reference, $project_id, $location, $comments, $date, $source_id);
                if (! ($inser_id > 0)) {
                    return 0;
                }
        }
    
        $Refs->save($type, $trans_no, $reference);

        return $trans_no;
}
     
function insert_job_material_requisition_header($type, $trans_no, $reference, $project_id, $location, $comments, $date, $jo_id=0, $source_type=-1, $source_trans_no=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."job_material_requisitions
                      ( type, trans_no, reference, project_id, 
                        loc_code, comments, tran_date, jo_id, 
                        source_type, source_trans_no, created_by, created_on)         
                VALUES( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($reference).", ".db_escape($project_id).", 
                        ".db_escape($location).", ".db_escape($comments).", ".db_escape(date2sql($date)).", ".db_escape($jo_id).", 
                        ".db_escape($source_type).", ".db_escape($source_trans_no).", ".db_escape($created_by).", ".db_escape($created_on).")";

            db_query($sql, 'insert_job_material_requisition_header');
            return db_insert_id();        
}

function update_job_material_requisition_header($id, $reference, $location, $comments, $date, $row_state=0) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_requisitions
                SET reference = ".db_escape($reference).",
                    loc_code = ".db_escape($location).",                          
                    tran_date = ".db_escape(date2sql($date)).",
                    comments = ".db_escape($comments).", 
                    row_state = ".db_new_row_state($row_state).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($id)."   
                    AND row_state = ".db_escape($row_state);  

            db_query($sql, "update_job_material_requisition_header");
            return db_num_affected_rows();
}

function update_job_material_requisition_header_by_type_trans_no($type, $trans_no, $reference, $location, $comments, $date) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_requisitions
                SET reference = ".db_escape($reference).",
                    loc_code = ".db_escape($location).",                          
                    tran_date = ".db_escape(date2sql($date)).",
                    comments = ".db_escape($comments).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE type = ".db_escape($type)."     
                    AND trans_no = ".db_escape($trans_no);    

            db_query($sql, "update_job_material_requisition_header_by_type_trans_no");
            return db_num_affected_rows();
}

function write_job_material_requisition_line($trans_no, $line_id, $type, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, 
                                             $standard_cost, $project_site_id, $line_comment, $task_id, $source_type=0, $source_trans_no=0, $source_line_id=0) {
    if ($line_id > 0) {
        return update_job_material_requisition_line($line_id, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, $standard_cost, 
                                                    $project_site_id, $line_comment, $task_id, $source_type, $source_trans_no, $source_line_id);
    } else {
        return insert_job_material_requisition_line($trans_no, $type, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, 
                                                    $standard_cost, $project_site_id, $line_comment, $task_id, $source_type, $source_trans_no, $source_line_id);
    }
}

function update_job_material_requisition_line($line_id, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, $standard_cost, 
                                              $project_site_id, $line_comment, $task_id, $source_type=0, $source_trans_no=0, $source_line_id=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_requisition_details
                SET stock_id = ".db_escape($stock_id).", 
                    item_type = ".db_escape($item_type).", 
                    item_description = ".db_escape($item_description).", 
                    tran_unit = ".db_escape($tran_unit).",
                    tran_qty = ".db_escape($tran_qty).",
                    qty = ".db_escape($qty).",
                    conversion_factor = ".db_escape($conversion_factor).",
                    standard_cost = ".db_escape($standard_cost).",
                    project_site_id = ".db_escape($project_site_id).",
                    line_comment = ".db_escape($line_comment).",
                    task_id = ".db_escape($task_id).",
                    source_type = ".db_escape($source_type).", 
                    source_trans_no = ".db_escape($source_trans_no).", 
                    source_line_id = ".db_escape($source_line_id).", 
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)."  
                WHERE trans_id = ".db_escape($line_id);

            db_query($sql, 'update_job_material_requisition_line');
            return db_num_affected_rows();
}
        
function insert_job_material_requisition_line($trans_no, $type, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, $standard_cost, 
                                              $project_site_id, $line_comment, $task_id, $source_type=0, $source_trans_no=0, $source_line_id=0, $trans_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        if ($trans_id > 0) {
            $sql = "INSERT INTO ".TB_PREF."job_material_requisition_details
                          ( trans_id, trans_no, type, stock_id, tran_unit, 
                            item_type, item_description, tran_qty, qty, 
                            conversion_factor, task_id, standard_cost, project_site_id, 
                            line_comment, source_type, source_trans_no, source_line_id, 
                            created_by, created_on)        
                    VALUE ( ".db_escape($trans_id).",".db_escape($trans_no).", ".db_escape($type).", ".db_escape($stock_id).", ".db_escape($tran_unit).", 
                            ".db_escape($item_type).", ".db_escape($item_description).", ".db_escape($tran_qty).", ".db_escape($qty).", 
                            ".db_escape($conversion_factor).", ".db_escape($task_id).", ".db_escape($standard_cost).", ".db_escape($project_site_id).", 
                            ".db_escape($line_comment).", ".db_escape($source_type).", ".db_escape($source_trans_no).", ".db_escape($source_line_id).", 
                            ".db_escape($created_by).", ".db_escape($created_on).")";    
        } else {

            $sql = "INSERT INTO ".TB_PREF."job_material_requisition_details
                          ( trans_no, type, stock_id, tran_unit, 
                            item_type, item_description, tran_qty, qty, 
                            conversion_factor, task_id, standard_cost, project_site_id, 
                            line_comment, source_type, source_trans_no, source_line_id, 
                            created_by, created_on)        
                    VALUE ( ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($stock_id).", ".db_escape($tran_unit).", 
                            ".db_escape($item_type).", ".db_escape($item_description).", ".db_escape($tran_qty).", ".db_escape($qty).", 
                            ".db_escape($conversion_factor).", ".db_escape($task_id).", ".db_escape($standard_cost).", ".db_escape($project_site_id).", 
                            ".db_escape($line_comment).", ".db_escape($source_type).", ".db_escape($source_trans_no).", ".db_escape($source_line_id).", 
                            ".db_escape($created_by).", ".db_escape($created_on).")";    
        }

            db_query($sql, 'insert_job_material_requisition_line');
            return db_insert_id();  
}

function get_job_material_requisition_header_record($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_requisitions    
            WHERE id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_material_requisition_header_record");
        return db_fetch($result);
}

function get_job_material_requisition_header($id) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name,  
                l2.location_name to_location_name,
                dep.value_name department_name 
            FROM ".TB_PREF."job_material_requisitions sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
                LEFT JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
                LEFT JOIN ".TB_PREF."list_values dep  
                    ON sa.department_id = dep.id         
                        AND dep.list_code = 'DEPARTMENTS'    
            WHERE sa.id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_material_requisition_header");
        return db_fetch($result);
}

function get_job_material_requisition_header_type_trans_no($type, $trans_no) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name,  
                l2.location_name to_location_name 
            FROM ".TB_PREF."job_material_requisitions sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
                LEFT JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
            WHERE sa.type = ".db_escape($type)." 
                AND sa.trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_job_material_requisition_header_type_trans_no");
        return db_fetch($result);
}

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

function delete_job_material_requisition_detail_lines($type, $trans_no) {
    $sql = "DELETE FROM ".TB_PREF."job_material_requisition_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
	db_query($sql, "delete_job_material_requisition_detail_lines");
        return db_num_affected_rows();
}

function delete_job_material_requisition_details_record($selected_id) {
    $sql = "DELETE 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE trans_id = ".db_escape($selected_id);
    
        db_query($sql, 'delete_job_material_requisition_details_record');
        return db_num_affected_rows();
}

function get_job_material_requisition_lines($trans_no, $type) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_requisition_details  
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        return db_query($sql, "get_job_material_requisition_lines");
}

function get_job_material_requisition_details($trans_no, $type) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description 
            FROM ".TB_PREF."job_material_requisition_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type);
    
        return db_query($sql, "get_job_material_requisition_details");
}

function get_job_material_requisition_details_record($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_requisition_details  
            WHERE trans_id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_material_requisition_details_record");
        return db_fetch($result);
}

function get_job_material_requisition_detail_line_details($trans_id) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description 
            FROM ".TB_PREF."job_material_requisition_details ard 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
            WHERE ard.trans_id = ".db_escape($trans_id);
    
        $result = db_query($sql, "get_job_material_requisition_detail_line_details");
        return db_fetch($result);
}

function get_job_material_requisition_details_lines_count($trans_no, $type) {
    $sql = "SELECT COUNT(trans_id) lines_count 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        $result = db_query($sql, 'get_job_material_requisition_details_lines_count');
        return db_result_value($result, 'lines_count');
}

function update_job_material_requisition_row_state($id, $row_state) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."job_material_requisitions
            SET row_state = ".db_new_row_state($row_state).",
                updated_by = ".db_escape($updated_by).", 
                updated_on = ".db_escape($updated_on)." 
            WHERE id = ".db_escape($id)." 
                AND row_state = ".db_escape($row_state);
            
    db_query($sql, "update_job_material_requisition_row_state");
    return db_num_affected_rows();
}

function get_sql_for_job_material_requisitions_inquiry($trans_no, $jo_id, $reference, $project_id, $from_date, $to_date, $loc_code, $approval_status=-1, 
                                                       $only_outstanding=false, $type=ST_JOB_MATERIAL_REQ, $source_trans_no=0, $source_type=-1, $department_id=0) {
    $sql = "SELECT sa.id,
                sa.jo_id,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                sa.project_id,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                sa.approval_status,  
                sa.source_type,    
                sa.source_trans_no,
                l2.location_name to_location_name, 
                dep.value_name department_name 
            FROM ".TB_PREF."job_material_requisitions sa  
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
                LEFT JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code        
                LEFT JOIN ".TB_PREF."list_values dep  
                    ON sa.department_id = dep.id         
                        AND dep.list_code = 'DEPARTMENTS'        
            WHERE sa.type = ".db_escape($type);
    
        if ($only_outstanding === true) {        
            $sql .= " AND sa.approval_status = ".db_escape(AS_APPROVED); // if all lines processed then AS_COMPLETED
        }
    
        if ($trans_no > 0){
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        } elseif ($jo_id > 0){
            $sql .= " AND sa.jo_id = ".db_escape($jo_id);
        } else {
        
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }

            if ($project_id > 0) {
                $sql .= " AND sa.project_id = ".db_escape($project_id);
            }

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if ($loc_code != '') {        
                $sql .= " AND sa.loc_code = ".db_escape($loc_code);
            }
            
            if ($approval_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($approval_status);
            }                      
            
            if ($source_trans_no > 0) {        
                $sql .= " AND sa.source_trans_no = ".db_escape($source_trans_no);
            }                      
            
            if ($source_type > -1) {        
                $sql .= " AND sa.source_type = ".db_escape($source_type);
            }                      
            
            if ($department_id > 0) {        
                $sql .= " AND sa.department_id = ".db_escape($department_id);
            }                      
        }
    
        $sql .= " ORDER BY sa.id DESC";
    
    return $sql;
}

function update_job_material_requisition_header_approval_status($id, $pre_status, $new_status) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."job_material_requisitions
                SET approval_status = ".db_escape($new_status).",
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".db_escape($id)."   
                    AND approval_status = ".db_escape($pre_status);    

            db_query($sql, "update_job_material_requisition_header_approval_status");
            return db_num_affected_rows();
}

function update_job_material_requisition_header_totals($type, $trans_no, $row_state=0) {
    $lines_total = get_job_material_requisition_lines_total($type, $trans_no);
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql =  "UPDATE ".TB_PREF."job_material_requisitions  
                    SET total = ".db_escape($lines_total).", 
                        row_state = ".db_new_row_state($row_state).", 
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on)." 
                    WHERE type = ".db_escape($type)." 
                        AND trans_no = ".db_escape($trans_no)." 
                        AND row_state = ".db_escape($row_state);

        db_query($sql, "update_job_material_requisition_header_totals");   
        return db_num_affected_rows();
}

function get_job_material_requisition_lines_total($type, $trans_no) {
    $sql = "SELECT SUM(standard_cost * qty) lines_amount_total
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
    $result = db_query($sql, "get_job_material_requisition_lines_total");
    return db_result_value($result, 'lines_amount_total');
}

function get_allocated_job_material_requisition_lines($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND ( issued_alloc > 0 OR returned_alloc > 0 )"; 
    
        return db_query($sql, "get_allocated_job_material_requisition_lines");
}

function check_all_job_material_requisition_lines_fully_processed($type, $trans_no) {
    $sql = "SELECT qty, SUM(issued_quantity + issued_alloc - returned_quantity) total_qty 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
            GROUP BY trans_id 
            HAVING total_qty < qty ";
            
        $result = db_query($sql, "check_all_job_material_requisition_lines_fully_processed");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_job_material_requisition_lines_quantity_by_source($source_type=-1, $source_trans_no=0, $source_line_id=0) {
    $sql = "SELECT SUM(qty) total_base_qty 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE 1 = 1";
    
    if ($source_type > -1) {
        $sql .= " AND source_type = ".db_escape($source_type);
    }
    if ($source_trans_no > 0) {
        $sql .= " AND source_trans_no = ".db_escape($source_trans_no);
    }
    if ($source_line_id > 0) {
        $sql .= " AND source_line_id = ".db_escape($source_line_id);
    }
    
    $result = db_query($sql, "get_job_material_requisition_lines_quantity_by_source");
    return db_result_value($result, 'total_base_qty');
}

function update_job_material_requisition_dpr_quantity($type, $trans_no, $line_id) {
    global $path_to_root;

    include_once($path_to_root . "/inventory/includes/db/job_material_issues_db.inc");
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $ap_array = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR, AS_APPROVED, AS_COMPLETED);
        $total_dpr_qty_ = get_direct_purchase_requisition_lines_quantity_by_source($type, $trans_no, $line_id, $ap_array, true);
        $total_dpr_qty = abs($total_dpr_qty_);
    
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET dpr_quantity = ".db_escape($total_dpr_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on)." 
                    WHERE trans_id = ".db_escape($line_id)." 
                        AND qty >= ".db_escape($total_dpr_qty);
    
        db_query($sql, "update_job_material_requisition_dpr_quantity");   
        return db_num_affected_rows();
}

function check_all_job_material_requisition_lines_fully_requisted($type, $trans_no) {
    $sql = "SELECT qty, dpr_quantity 
            FROM ".TB_PREF."job_material_requisition_details  
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND dpr_quantity < qty ";
            
        $result = db_query($sql, "check_all_job_material_requisition_lines_fully_requisted");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_job_material_requisition_headers_by_sources($source_type, $source_trans_no) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."job_material_requisitions sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE sa.source_type = ".db_escape($source_type)." 
                AND sa.source_trans_no = ".db_escape($source_trans_no);
    
        return db_query($sql, "get_job_material_requisition_headers_by_sources");
}

function get_job_material_requisition_line_remain_qty_array($type, $source_type, $source_trans_no, $source_base_qty, $source_line_id=0, $tran_qty=0, $conversion_factor=1, 
                                                            $line_id=0, $dec=4) {
    
    $source_tran_qty = $source_base_qty / $conversion_factor;
        
        if ($type == ST_DIRECT_PURCHASE_REQ) {
            $ap_array = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR, AS_APPROVED, AS_COMPLETED);
            $total_dpr_qty_ = get_direct_purchase_requisition_lines_quantity_by_source($source_type, $source_trans_no, $source_line_id, $ap_array, true, $line_id);
            
            $processed_qty = abs($total_dpr_qty_);
            
        } elseif ($type == ST_JOB_MATERIAL_ISSUE_REQ) {
            
            $total_issued_req_qty_ = get_job_material_requisition_lines_total_qty_by_source(ST_JOB_MATERIAL_ISSUE_REQ, $source_line_id, array(AS_APPROVED, AS_COMPLETED));
            $total_issued_req_qty = abs($total_issued_req_qty_);

            $ap_array_2 = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR);
            $total_issued_alloc_qty_ = get_job_material_requisition_lines_total_qty_by_source(ST_JOB_MATERIAL_ISSUE_REQ, $source_line_id, $ap_array_2, $line_id);
            $total_issued_alloc_qty = abs($total_issued_alloc_qty_);
            
            $processed_qty = $total_issued_req_qty + $total_issued_alloc_qty;
        }
        
            $old_processed_tran_qty = $processed_qty / $conversion_factor;
            $old_rem_trn_qty = $source_tran_qty - $old_processed_tran_qty;

            $new_rem_trn_qty = $old_rem_trn_qty - $tran_qty;
                
            return array('old_rem_trn_qty' => number_format($old_rem_trn_qty, $dec), 'new_rem_trn_qty' => number_format($new_rem_trn_qty, $dec));
}

function get_job_material_requisition_headers_by_filters($jo_id=0, $id=0, $type=0, $trans_no=0, $loc_code='', $project_id=0, $from_date=null, $to_date=null) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."job_material_requisitions sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE 1 = 1 ";
    
        if ($jo_id > 0) {
            $sql .= " AND sa.jo_id = ".db_escape($jo_id);
        }
        if ($id > 0) {
            $sql .= " AND sa.id = ".db_escape($id);
        }
        if ($type > 0) {
            $sql .= " AND sa.type = ".db_escape($type);
        }
        if ($trans_no > 0) {
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        }
        if (strlen($loc_code) > 0) {
            $sql .= " AND sa.loc_code = ".db_escape($loc_code);
        }
        if ($project_id > 0) {
            $sql .= " AND sa.project_id = ".db_escape($project_id);
        }
        if ($from_date !== null) {
            $sql .= " AND sa.tran_date >= ".db_escape($from_date);
        }
        if ($to_date !== null) {
            $sql .= " AND sa.tran_date <= ".db_escape($to_date);
        }
    
        return db_query($sql, "get_job_material_requisition_headers_by_filters");
}

function get_sql_for_approve_job_material_requisitions($current_user_id, $type=ST_JOB_MATERIAL_REQ) {
    $sql = "SELECT jmr.id,
                jmr.trans_no,
                jmr.type,
                jmr.jo_id,
                jmr.reference,
                jmr.tran_date,                   
                l.location_name,                   
                jmr.project_id,                   
                pm.description project_description,
                jmr.total,
                c.real_name,
                jmr.created_on,
                jmr.approval_status, 
                jmr.source_type,
                jmr.source_trans_no,
                jmr.next_approval_group_id,  
                apgp.approval_group_description 
            FROM ".TB_PREF."job_material_requisitions jmr
                JOIN ".TB_PREF."projects_master pm 
                    ON jmr.project_id = pm.project_id
                JOIN ".TB_PREF."users c 
                    ON jmr.created_by = c.id  
                JOIN ".TB_PREF."approvers apr 
                    ON jmr.next_approval_group_id = apr.approval_group_id  
                JOIN ".TB_PREF."user_data_access_groups udag 
                    ON pm.dag_id = udag.dag_id 
                        AND udag.user_id = apr.user_id  
                JOIN ".TB_PREF."users u 
                    ON apr.user_id = u.id 
                LEFT JOIN ".TB_PREF."approval_group apgp  
                    ON jmr.next_approval_group_id = apgp.approval_group_id  
                LEFT JOIN ".TB_PREF."locations l 
                    ON jmr.loc_code = l.loc_code   
            WHERE jmr.approval_status IN (".implode(',', array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER)).") 
                AND apr.user_id = ".db_escape($current_user_id)." 
                AND jmr.type = ".db_escape($type);
      
        return $sql;
}

function get_job_material_req_with_job_material_issue_req_details($req_trans_no, $trans_no, $type=ST_JOB_MATERIAL_ISSUE_REQ) {
    $sql = "SELECT rqd.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                am.stock_control_method,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description, 
                jmir_d.trans_id issue_req_line_id, 
                jmir_d.tran_unit issue_req_tran_unit, 
                jmir_d.tran_qty issue_req_tran_qty  
            FROM ".TB_PREF."job_material_requisition_details rqd 
                JOIN ".TB_PREF."job_material_requisitions rqh  
                    ON rqd.type = rqh.type   
                        AND rqd.trans_no = rqh.trans_no  
                JOIN ".TB_PREF."locations l 
                    ON rqh.loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON rqd.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON rqd.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON rqd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON rqd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."job_material_requisition_details jmir_d 
                    ON rqd.trans_id = jmir_d.source_line_id  
                        AND jmir_d.type = ".db_escape($type)." 
                        AND jmir_d.trans_no = ".db_escape($trans_no)." 
            WHERE rqd.trans_no = ".db_escape($req_trans_no)." 
                AND rqd.type = ".db_escape(ST_JOB_MATERIAL_REQ)." 
            ORDER BY rqd.trans_id";
    
        return db_query($sql, "get_job_material_req_with_job_material_issue_req_details");
}

function get_job_material_requisition_lines_total_qty_by_source($type, $source_line_id, $ap_array, $not_trans_id=0) {
    $sql = "SELECT SUM(ln.qty) total_qty 
            FROM ".TB_PREF."job_material_requisition_details ln 
                JOIN ".TB_PREF."job_material_requisitions hd  
                    ON ln.type = hd.type 
                        AND ln.trans_no = hd.trans_no  
            WHERE ln.source_line_id = ".db_escape($source_line_id)." 
                AND hd.type = ".db_escape($type)."
                AND hd.approval_status IN (".implode(',', $ap_array).")";
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        $result = db_query($sql, 'get_job_material_requisition_lines_total_qty_by_source');
        return db_result_value($result, 'total_qty');
}

function save_job_material_requisition_lines_issue_req_qty_total($type, $trans_no, $iss_req_type) {
    $result = get_job_material_requisition_lines($trans_no, $type);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            
            $cal_up_msg = calculate_and_update_job_material_requisition_line_issue_req_qty_total($type, $id, $iss_req_type, $row_state);
                if ($cal_up_msg['success'] !== true) {
                    return $cal_up_msg;
                }
        }
        
        return array("success" => true, "message" => '');
}

function calculate_and_update_job_material_requisition_line_issue_req_qty_total($req_type, $id, $type, $req_line_row_state=0) {
    global $systypes_array;
    
    if ($type == ST_JOB_MATERIAL_ISSUE_REQ) {
        $af_row = update_job_material_requisition_line_issue_req_and_issue_req_alloc_qty($id, $req_line_row_state);
            if (! ($af_row == 1)) {
                return array("success" => false, "message" => "Could not update Req line #$id total issue req qty.");
            } 
    } 
    
        return array("success" => true, "message" => $systypes_array[$req_type]." Detail Req Line #$id issue req qty has been updated.");
}

function update_job_material_requisition_line_issue_req_and_issue_req_alloc_qty($trans_id, $req_line_row_state=0) {

    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $ap_array = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR);
        $total_issue_req_alloc_qty_ = get_job_material_requisition_lines_total_qty_by_source(ST_JOB_MATERIAL_ISSUE_REQ, $trans_id, $ap_array);
        $total_issue_req_alloc_qty = abs($total_issue_req_alloc_qty_);

        $total_issue_req_qty_ = get_job_material_requisition_lines_total_qty_by_source(ST_JOB_MATERIAL_ISSUE_REQ, $trans_id, array(AS_APPROVED));
        $total_issue_req_qty = abs($total_issue_req_qty_);

        $total_issue_req_issue_req_alloc_qty = $total_issue_req_alloc_qty + $total_issue_req_qty;
            
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issue_req_alloc_qty = ".db_escape($total_issue_req_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($req_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_issue_req_issue_req_alloc_qty";
            
            db_query($sql, 'update_job_material_requisition_line_issue_req_alloc_qty');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_qch_line_row_state = db_new_row_state($req_line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issue_req_qty = ".db_escape($total_issue_req_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_qch_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $total_issue_req_issue_req_alloc_qty";
            
            db_query($sql_1, 'update_job_material_requisition_line_issue_req_qty');
                $af_row_2 = db_num_affected_rows();

                    if ($af_row_2 != 1) {
                        return 0;
                    } 
                    
        return 1;
}

function get_job_material_issue_req_with_job_material_req_header_record($id) {
    $sql = "SELECT jmi.*, jmr.id jmr_no 
            FROM ".TB_PREF."job_material_requisitions jmi    
                JOIN ".TB_PREF."job_material_requisitions jmr     
                    ON jmi.source_type = jmr.type    
                        AND jmi.source_trans_no = jmr.trans_no     
            WHERE jmi.id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_material_requisition_header_record");
        return db_fetch($result);
}

function check_all_job_material_requisition_lines_fully_issue_requested($type, $trans_no) {
    $sql = "SELECT qty, SUM(issue_req_alloc_qty + issue_req_qty) total_qty 
            FROM ".TB_PREF."job_material_requisition_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
            GROUP BY trans_id 
            HAVING total_qty < qty ";
            
        $result = db_query($sql, "check_all_job_material_requisition_lines_fully_issue_requested");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_job_material_requisition_headers_by_trans_nos($trans_nos_array, $type) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_requisitions    
            WHERE trans_no IN (". implode(',', $trans_nos_array).")  
                AND type = ".db_escape($type);
    
        return db_query($sql, "get_job_material_requisition_headers_by_trans_nos");
}

function update_job_material_requisition_row_state_by_type_trans_no($type, $trans_no, $row_state) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_requisitions 
                SET row_state = ".db_new_row_state($row_state).",
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE type = ".db_escape($type)." 
                    AND trans_no = ".db_escape($trans_no)." 
                    AND row_state = ".db_escape($row_state);

            db_query($sql, "update_job_material_requisition_row_state_by_type_trans_no");
            return db_num_affected_rows();
}

?>