<?php

function write_personal_material_transfer_header($type, $pmr_no, &$id, &$trans_no, $reference, $project_id, $loc_code, $to_loc_code, $comments, $date, $employee_code, 
                                                 $transferred_row_state, $source_id=0) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_personal_material_transfer_header($id, $reference, $loc_code, $to_loc_code, $comments, $date, $employee_code, $transferred_row_state);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $id = insert_personal_material_transfer_header($type, $pmr_no, $trans_no, $reference, $project_id, $loc_code, $to_loc_code, $comments, $date, $employee_code, 
                                                           $source_id);
        }
    
        $Refs->save($type, $trans_no, $reference);

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

        return $id;
}
     
function insert_personal_material_transfer_header($type, $pmr_no, $trans_no, $reference, $project_id, $loc_code, $to_loc_code, $comments, $date, $employee_code, 
                                                  $source_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."job_material_issues
                      ( type, jmr_no, trans_no, reference, 
                        project_id, loc_code, to_loc_code, comments, 
                        tran_date, employee_code, created_by, created_on, source_id )         
                VALUES( ".db_escape($type).", ".db_escape($pmr_no).", ".db_escape($trans_no).", ".db_escape($reference).", 
                        ".db_escape($project_id).", ".db_escape($loc_code).", ".db_escape($to_loc_code).", ".db_escape($comments).", 
                        ".db_escape($date).", ".db_escape($employee_code).", ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_id).")";

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

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

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

function insert_personal_material_transfer_line($trans_no, $type, $loc_code, $to_loc_code, $tran_date, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, 
                                                $qty, $conversion_factor, $standard_cost, $project_site_id, $line_comment, $source_line_id=0, $line_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        if ($line_id > 0) {
            $sql = "INSERT INTO ".TB_PREF."job_material_issue_details
                          ( trans_id, trans_no, type, loc_code, 
                            to_loc_code, tran_date, stock_id, tran_unit, 
                            item_type, item_description, tran_qty, qty, 
                            conversion_factor, standard_cost, project_site_id, 
                            line_comment, created_by, created_on, source_line_id)        
                    VALUE ( ".db_escape($line_id).", ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($loc_code).",
                            ".db_escape($to_loc_code).", ".db_escape($tran_date).", ".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($standard_cost).", ".db_escape($project_site_id).", 
                            ".db_escape($line_comment).", ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";     
        } else {
            $sql = "INSERT INTO ".TB_PREF."job_material_issue_details
                          ( trans_no, type, loc_code, to_loc_code, 
                            tran_date, stock_id, tran_unit, item_type, 
                            item_description, tran_qty, qty, conversion_factor, 
                            standard_cost, project_site_id, line_comment, 
                            created_by, created_on, source_line_id)        
                    VALUE ( ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($loc_code).", ".db_escape($to_loc_code).",
                            ".db_escape($tran_date).", ".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($standard_cost).", ".db_escape($project_site_id).", ".db_escape($line_comment).", 
                            ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";     
        }

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

function update_personal_material_transfer_line($id, $loc_code, $to_loc_code, $item_type, $stock_id, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, 
                                                $project_site_id=0, $standard_cost=0, $line_comment='') {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_issue_details 
                SET loc_code = ".db_escape($loc_code).",
                    to_loc_code = ".db_escape($to_loc_code).",
                    item_type = ".db_escape($item_type).",
                    stock_id = ".db_escape($stock_id).", 
                    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).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE trans_id = ".db_escape($id);   

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

function get_personal_material_requisition_with_personal_material_transfer_details($req_trans_no, $transfer_trans_no) {
    $sql = "SELECT pmrd.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                am.stock_control_method,
                un.name unit_name,
                ps.description site_description, 
                pmtrd.trans_id transfer_line_id, 
                pmtrd.item_type transfer_item_type, 
                pmtrd.stock_id transfer_stock_id, 
                pmtrd.item_description transfer_item_description, 
                pmtrd.tran_unit transfer_tran_unit, 
                pmtrd.tran_qty transfer_tran_qty, 
                pmtrd.project_site_id transfer_project_site_id, 
                pmtrd.line_comment transfer_line_comment 
            FROM ".TB_PREF."job_material_requisition_details pmrd 
                JOIN ".TB_PREF."job_material_requisitions pmr  
                    ON pmrd.type = pmr.type   
                        AND pmrd.trans_no = pmr.trans_no  
                JOIN ".TB_PREF."locations l 
                    ON pmr.loc_code = l.loc_code 
                JOIN ".TB_PREF."locations l2 
                    ON pmr.to_loc_code = l2.loc_code 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON pmrd.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON pmrd.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON pmrd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."job_material_issue_details pmtrd 
                    ON pmrd.trans_id = pmtrd.source_line_id  
                        AND pmtrd.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER)." 
                        AND pmtrd.trans_no = ".db_escape($transfer_trans_no)." 
            WHERE pmrd.trans_no = ".db_escape($req_trans_no)." 
                AND pmrd.type = ".db_escape(ST_PERSONAL_MATERIAL_REQ)." 
            ORDER BY pmrd.trans_id";
    
        return db_query($sql, "get_personal_material_requisition_with_personal_material_transfer_details");
}

function save_personal_material_requisition_lines_transfer_alloc_transfer_qty_total($type, $trans_no) {
    global $systypes_array;
    
    $result = get_job_material_requisition_lines($trans_no, $type);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            $returned_quantity = $row['returned_quantity']; // return program not yet created but can be used this since value is zero
            
            $up_af_rw = update_personal_material_requisition_line_transferred_alloc_qty_transferred_qty($id, $row_state, $returned_quantity);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[$type].' total transferred/ transferred alloc qty.');
                }
        }
        
        return array("success" => true, "message" => '');
}

function get_personal_material_transfer_lines_total_qty($type, $source_line_id, $ap_array, $not_trans_id=0) {
    $sql = "SELECT SUM(ln.qty) total_qty 
            FROM ".TB_PREF."job_material_issue_details ln 
                JOIN ".TB_PREF."job_material_issues 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_personal_material_transfer_lines_total_qty');
        return db_result_value($result, 'total_qty');
}

function get_personal_material_transfer_lines_with_edited_item_codes($req_trans_no, $transfer_trans_no) {
    $sql = "SELECT ard.*,
                pmtrd.trans_id transferred_line_id, 
                pmtrd.item_type transferred_item_type, 
                pmtrd.stock_id transferred_stock_id, 
                pmtrd.item_description transferred_item_description, 
                pmtrd.tran_unit transferred_tran_unit, 
                pmtrd.tran_qty transferred_tran_qty  
            FROM ".TB_PREF."job_material_requisition_details ard 
                JOIN ".TB_PREF."job_material_issue_details pmtrd 
                    ON ard.trans_id = pmtrd.source_line_id  
            WHERE ard.trans_no = ".db_escape($req_trans_no)." 
                AND ard.type = ".db_escape(ST_PERSONAL_MATERIAL_REQ)." 
                AND pmtrd.trans_no = ".db_escape($transfer_trans_no)." 
                AND pmtrd.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER)." 
                AND pmtrd.stock_id <> ard.stock_id 
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_personal_material_transfer_lines_with_edited_item_codes");
}

function update_personal_material_requisition_line_transferred_alloc_qty_transferred_qty($trans_id, $req_line_row_state=0, $returned_quantity=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_transferred_alloc_qty_ = get_job_material_issue_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER, $trans_id, $ap_array);
        $total_transferred_alloc_qty = abs($total_transferred_alloc_qty_);

        $total_transferred_qty_ = get_job_material_issue_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER, $trans_id, array(AS_APPROVED, AS_COMPLETED));
        $total_transferred_qty = abs($total_transferred_qty_);

        $total_transferred_qty_transferred_alloc_qty = $total_transferred_qty + $total_transferred_alloc_qty - $returned_quantity;
            
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issue_req_alloc_qty = ".db_escape($total_transferred_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_transferred_qty_transferred_alloc_qty";
            
            db_query($sql, 'update_personal_material_requisition_line_transferred_alloc_qty_transferred_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_transferred_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_transferred_qty_transferred_alloc_qty";
            
            db_query($sql_1, 'update_personal_material_requisition_line_transferred_alloc_qty_transferred_qty');
                $af_row_2 = db_num_affected_rows();

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

function update_personal_material_transfer_line_returned_alloc_returned($trans_id, $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_returned_alloc_qty_ = get_personal_material_transfer_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER_RETURN, $trans_id, $ap_array);
        $total_returned_alloc_qty = abs($total_returned_alloc_qty_);

        $total_returned_qty_ = get_personal_material_transfer_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER_RETURN, $trans_id, array(AS_APPROVED));
        $total_returned_qty = abs($total_returned_qty_);
        
        $all_returned_qty = $total_returned_alloc_qty + $total_returned_qty;

            $sql = "UPDATE ".TB_PREF."job_material_issue_details   
                    SET returned_alloc = ".db_escape($total_returned_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $all_returned_qty";
            
            db_query($sql, 'update_personal_material_transfer_line_returned_alloc_returned');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_line_row_state = db_new_row_state($line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."job_material_issue_details   
                    SET returned_quantity = ".db_escape($total_returned_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $all_returned_qty";
            
            db_query($sql_1, 'update_personal_material_transfer_line_returned_alloc_returned');
                $af_row_2 = db_num_affected_rows();

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

        return 1;
}

function save_personal_material_transfer_lines_return_alloc_return_qty_total($type, $trans_no) {
    global $systypes_array;
    
    $result = get_job_material_issue_lines($trans_no, $type);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            
            $up_af_rw = update_personal_material_transfer_line_returned_alloc_returned($id, $row_state);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[$type].' total return/ return alloc qty.');
                }
        }
        
        return array("success" => true, "message" => '');
}

function get_sql_for_personal_material_transfers_inquiry($trans_no, $req_trans_no, $reference, $project_id, $from_date, $to_date, $loc_code, $to_loc_code, $employee_code, 
                                                         $ap_status, $type=ST_PERSONAL_MATERIAL_TRANSFER, $only_outstanding=false) {
    $sql = "SELECT req.trans_no req_trans_no,
                sa.trans_no,
                sa.reference,
                sa.type,
                sa.loc_code,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                sa.tran_date,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                sa.approval_status, 
                sa.id,
                req.type req_type,  
                sa.employee_code 
            FROM ".TB_PREF."job_material_issues sa  
                JOIN ".TB_PREF."job_material_requisitions req 
                    ON sa.jmr_no = req.id 
                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."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
            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 ($req_trans_no > 0 || $trans_no > 0) {
            if ($req_trans_no > 0) {
                $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
            }
            
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
        } else {
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            
            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

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

            if ($to_loc_code != '') {        
                $sql .= " AND sa.to_loc_code = ".db_escape($to_loc_code);
            }

            if (strlen($employee_code) > 2) {        
                $sql .= " AND sa.employee_code = ".db_escape($employee_code);
            }
            
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " ORDER BY sa.id DESC";
    
        return $sql;
}

function get_personal_material_transfer_details_with_pm_requsition_details($trans_no, $type) {
    $sql = "SELECT pmtr_d.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                am.stock_control_method,
                pmrq_d.stock_id request_stock_id,
                am2.description request_stock_description 
            FROM ".TB_PREF."job_material_issue_details pmtr_d 
                JOIN ".TB_PREF."locations l 
                    ON pmtr_d.loc_code = l.loc_code 
                JOIN ".TB_PREF."locations l2 
                    ON pmtr_d.to_loc_code = l2.loc_code 
                JOIN ".TB_PREF."stock_master am 
                    ON pmtr_d.stock_id = am.stock_id 
                JOIN ".TB_PREF."item_units un  
                    ON pmtr_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON pmtr_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."job_material_requisition_details pmrq_d 
                    ON pmtr_d.source_line_id = pmrq_d.trans_id  
                        AND pmrq_d.type = ".db_escape(ST_PERSONAL_MATERIAL_REQ)." 
                LEFT JOIN ".TB_PREF."stock_master am2  
                    ON pmrq_d.stock_id = am2.stock_id              
            WHERE pmtr_d.trans_no = ".db_escape($trans_no)." 
                AND pmtr_d.type = ".db_escape($type);
    
        return db_query($sql, "get_personal_material_transfer_details_with_pm_requsition_details");
}

function get_personal_material_transfer_with_personal_material_transfer_return_details($transfer_trans_no, $return_trans_no) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                am.stock_control_method,
                un.name unit_name,
                ps.description site_description, 
                pmtrrt.trans_id return_line_id, 
                pmtrrt.item_type return_item_type, 
                pmtrrt.stock_id return_stock_id, 
                pmtrrt.item_description return_item_description, 
                pmtrrt.tran_unit return_tran_unit, 
                pmtrrt.tran_qty return_tran_qty, 
                pmtrrt.project_site_id return_project_site_id, 
                pmtrrt.line_comment return_line_comment 
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."job_material_issues mth  
                    ON ard.type = mth.type   
                        AND ard.trans_no = mth.trans_no  
                JOIN ".TB_PREF."locations l 
                    ON mth.to_loc_code = l.loc_code 
                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."job_material_issue_details pmtrrt 
                    ON ard.trans_id = pmtrrt.source_line_id  
                        AND pmtrrt.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER_RETURN)." 
                        AND pmtrrt.trans_no = ".db_escape($return_trans_no)." 
            WHERE ard.trans_no = ".db_escape($transfer_trans_no)." 
                AND ard.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER)." 
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_personal_material_transfer_with_personal_material_transfer_return_details");
}

function get_pm_requisition_lines_from_pm_transfer_with_pm_return($transfer_trans_no, $return_trans_no) {
    $sql = "SELECT rtn.trans_id ret_line_id, tnsf.trans_id tnsf_line_id, req.trans_id, 
                req.row_state, req_h.row_state req_hd_row_state, req_h.trans_no req_trans_no, req_h.approval_status 
            FROM ".TB_PREF."job_material_issue_details rtn  
                JOIN ".TB_PREF."job_material_issue_details tnsf 
                    ON rtn.source_line_id = tnsf.trans_id   
                        AND tnsf.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER)." 
                        AND tnsf.trans_no = ".db_escape($transfer_trans_no)." 
                JOIN ".TB_PREF."job_material_requisition_details req 
                    ON tnsf.source_line_id = req.trans_id   
                        AND req.type = ".db_escape(ST_PERSONAL_MATERIAL_REQ)." 
                JOIN ".TB_PREF."job_material_requisitions req_h 
                    ON req.type = req_h.type 
                        AND req.trans_no = req_h.trans_no 
            WHERE rtn.trans_no = ".db_escape($return_trans_no)." 
                AND rtn.type = ".db_escape(ST_PERSONAL_MATERIAL_TRANSFER_RETURN)." 
            GROUP BY req.trans_id
            ORDER BY req.trans_id";
    
        return db_query($sql, "get_pm_requisition_lines_from_pm_transfer_with_pm_return");
}

function update_personal_material_requisition_line_retruned_qty($trans_id, $line_row_state=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $ap_array = array(AS_APPROVED, AS_COMPLETED);
        
        $total_returned_alloc_qty_ = get_personal_material_transfer_lines_returned_total_qty(ST_PERSONAL_MATERIAL_TRANSFER, $trans_id, $ap_array, 0, false);
        $total_returned_alloc_qty = abs($total_returned_alloc_qty_);

        $total_returned_qty_ = get_personal_material_transfer_lines_returned_total_qty(ST_PERSONAL_MATERIAL_TRANSFER, $trans_id, $ap_array, 0, true);
        $total_returned_qty = abs($total_returned_qty_);

        $total_all_returned = $total_returned_alloc_qty + $total_returned_qty;
            
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET returned_alloc = ".db_escape($total_returned_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND issue_req_qty >= $total_all_returned";
            
            db_query($sql, 'update_personal_material_requisition_line_retruned_qty');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_line_row_state = db_new_row_state($line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET returned_quantity = ".db_escape($total_returned_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND issue_req_qty >= $total_all_returned";
            
            db_query($sql_1, 'update_personal_material_requisition_line_retruned_qty');
                $af_row_2 = db_num_affected_rows();

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

function get_personal_material_transfer_lines_returned_total_qty($type, $source_line_id, $ap_array, $not_trans_id=0, $approved_qty=true) {
    if ($approved_qty === true) {
        $sql = "SELECT SUM(ln.returned_quantity) total_qty ";
    } else {
        $sql = "SELECT SUM(ln.returned_alloc) total_qty ";
    }
    
    $sql .= "FROM ".TB_PREF."job_material_issue_details ln 
                JOIN ".TB_PREF."job_material_issues 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_personal_material_transfer_lines_returned_total_qty');
        return db_result_value($result, 'total_qty');
}

function check_all_personal_material_transfer_lines_fully_processed($type, $trans_no, $ap_status_array=array(AS_REJECTED), $not_in=true) {
    $sql = "SELECT ln.qty, SUM(ln.returned_quantity + ln.returned_alloc) total_qty 
            FROM ".TB_PREF."job_material_issue_details ln  
                JOIN ".TB_PREF."job_material_issues hd 
                    ON ln.type = hd.type
                        AND ln.trans_no = hd.trans_no 
            WHERE ln.type = ".db_escape($type)." 
                AND ln.trans_no = ".db_escape($trans_no);
    
        if (count($ap_status_array) > 0) {
            if ($not_in === true) {
                $sql .= " AND hd.approval_status NOT IN (".implode(",", $ap_status_array).")";
            } else {
                $sql .= " AND hd.approval_status IN (".implode(",", $ap_status_array).")";
            }
        }
            
        $sql .= " GROUP BY ln.trans_id 
                HAVING total_qty < ln.qty ";
            
        $result = db_query($sql, "check_all_personal_material_transfer_lines_fully_processed");
            if (db_num_rows($result) > 0) {
                return false;
            } 
            
        return true;
}

function get_sql_for_personal_material_transfer_returns_inquiry($trans_no, $req_trans_no, $reference, $project_id, $from_date, $to_date, $loc_code, $to_loc_code, 
                                                                $employee_code, $ap_status, $type= ST_PERSONAL_MATERIAL_TRANSFER_RETURN, $only_outstanding=false) {
    $sql = "SELECT req.trans_no transfer_trans_no,
                sa.trans_no,
                sa.reference,
                sa.type,
                sa.loc_code,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                sa.tran_date,
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                sa.approval_status, 
                sa.id,
                req.type req_type,  
                sa.employee_code 
            FROM ".TB_PREF."job_material_issues sa  
                JOIN ".TB_PREF."job_material_issues req 
                    ON sa.jmr_no = req.id 
                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."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
            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 ($req_trans_no > 0 || $trans_no > 0) {
            if ($req_trans_no > 0) {
                $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
            }
            
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
        } else {
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            
            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

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

            if ($to_loc_code != '') {        
                $sql .= " AND sa.to_loc_code = ".db_escape($to_loc_code);
            }

            if (strlen($employee_code) > 2) {        
                $sql .= " AND sa.employee_code = ".db_escape($employee_code);
            }
            
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " ORDER BY sa.id DESC";
    
        return $sql;
}

function get_personal_material_transfer_line_remain_qty_array($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;
            
        $total_transfer_retuned_qty_ = get_job_material_issue_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER_RETURN, $source_line_id, array(AS_APPROVED, AS_COMPLETED));
        $total_transfer_retuned_qty = abs($total_transfer_retuned_qty_);

        $ap_array_2 = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR);
        $total_transfer_retuned_alloc_qty_ = get_job_material_issue_lines_total_qty(ST_PERSONAL_MATERIAL_TRANSFER_RETURN, $source_line_id, $ap_array_2, $line_id);
        $total_transfer_retuned_alloc_qty = abs($total_transfer_retuned_alloc_qty_);
        
        $processed_qty = $total_transfer_retuned_qty + $total_transfer_retuned_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));
}

?>