<?php

function write_job_material_issue_header($type, $jmr_no, &$id, &$trans_no, $reference, $project_id, $location, $comments, $date, $issue_row_state, $jo_id, $source_id=0, 
                                         $employee_code='') {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_job_material_issue_header($id, $reference, $comments, $date, $issue_row_state, $employee_code);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $id = insert_job_material_issue_header($type, $jmr_no, $trans_no, $reference, $project_id, $location, $comments, $date, $jo_id, $source_id, $employee_code);
        }
    
        $Refs->save($type, $trans_no, $reference);

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

        return $id;
}
     
function insert_job_material_issue_header($type, $jmr_no, $trans_no, $reference, $project_id, $location, $comments, $date, $jo_id, $source_id=0, $employee_code='') {
    $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, comments, tran_date, 
                        jo_id, employee_code, created_by, created_on, source_id )         
                VALUES( ".db_escape($type).", ".db_escape($jmr_no).", ".db_escape($trans_no).", ".db_escape($reference).", 
                        ".db_escape($project_id).", ".db_escape($location).", ".db_escape($comments).", ".db_escape($date).", 
                        ".db_escape($jo_id).", ".db_escape($employee_code).", ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_id).")";

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

function update_job_material_issue_header($id, $reference, $comments, $date, $issue_row_state, $employee_code='') {   
    $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).",
                    comments = ".db_escape($comments).", 
                    employee_code = ".db_escape($employee_code).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on).",
                    row_state = ". db_new_row_state($issue_row_state)." 
                WHERE id = ".db_escape($id)."   
                    AND row_state = ".db_escape($issue_row_state);    

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

function insert_job_material_issue_line($trans_no, $type, $location, $tran_date, $stock_id, $item_type, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, 
                                        $standard_cost, $project_site_id, $line_comment, $task_id, $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, 
                            tran_date, stock_id, tran_unit, 
                            item_type, item_description, 
                            tran_qty, qty, conversion_factor, 
                            task_id, 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($location).",
                            ".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($task_id).", ".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, 
                            tran_date, stock_id, tran_unit, 
                            item_type, item_description, 
                            tran_qty, qty, conversion_factor, 
                            task_id, standard_cost, project_site_id, line_comment, 
                            created_by, created_on, source_line_id)        
                    VALUE ( ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($location).",
                            ".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($task_id).", ".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_job_material_issue_line');
            return db_insert_id();  
}

function update_job_material_issue_line($id, $location, $item_type, $stock_id, $item_description, $tran_unit, $tran_qty, $qty, $conversion_factor, $task_id=0, 
                                        $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($location).",
                    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).", 
                    task_id = ".db_escape($task_id).", 
                    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_job_material_issue_line");
            return db_num_affected_rows();
}

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

function get_job_material_issue_header($id) {
    $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_issues 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.id = ".db_escape($id);
    
        $result = db_query($sql, "get_job_material_issue_header");
        return db_fetch($result);
}

function get_job_material_issue_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_issues 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_issue_header_type_trans_no");
        return db_fetch($result);
}

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

function get_job_material_issue_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, 
                am.stock_control_method   
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."locations l 
                    ON ard.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."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_issue_details");
}

function get_job_material_issue_details_with_jmi_requsition_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, 
                am.stock_control_method,
                jmi_rd.stock_id issue_req_stock_id,
                am2.description issue_req_stock_description 
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."locations l 
                    ON ard.loc_code = l.loc_code 
                JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                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 
                LEFT JOIN ".TB_PREF."job_material_requisition_details jmi_rd 
                    ON ard.source_line_id = jmi_rd.trans_id  
                        AND jmi_rd.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                LEFT JOIN ".TB_PREF."stock_master am2  
                    ON jmi_rd.stock_id = am2.stock_id              
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type);
    
        return db_query($sql, "get_job_material_issue_details_with_jmi_requsition_details");
}

function get_job_material_issue_requisition_with_job_material_issue_details($issue_req_trans_no, $issue_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, 
                pt.description task_description, 
                jmid.trans_id issue_line_id, 
                jmid.item_type issue_item_type, 
                jmid.stock_id issue_stock_id, 
                jmid.item_description issue_item_description, 
                jmid.tran_unit issue_tran_unit, 
                jmid.tran_qty issue_tran_qty  
            FROM ".TB_PREF."job_material_requisition_details ard 
                JOIN ".TB_PREF."job_material_requisitions mth  
                    ON ard.type = mth.type   
                        AND ard.trans_no = mth.trans_no  
                JOIN ".TB_PREF."locations l 
                    ON mth.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."project_tasks pt 
                    ON ard.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."job_material_issue_details jmid 
                    ON ard.trans_id = jmid.source_line_id  
                        AND jmid.type = ".db_escape(ST_JOB_MATERIAL_ISSUE)." 
                        AND jmid.trans_no = ".db_escape($issue_trans_no)." 
            WHERE ard.trans_no = ".db_escape($issue_req_trans_no)." 
                AND ard.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_job_material_issue_requisition_with_job_material_issue_details");
}

function get_job_material_issue_details_with_no_source($issue_trans_no, $type) {
    $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, 
                pt.description task_description, 
                ard.trans_id issue_line_id, 
                ard.item_type issue_item_type, 
                ard.stock_id issue_stock_id, 
                ard.item_description issue_item_description, 
                ard.tran_unit issue_tran_unit, 
                ard.tran_qty issue_tran_qty  
            FROM ".TB_PREF."job_material_issue_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($issue_trans_no)." 
                AND ard.type = ".db_escape($type)." 
                AND ard.source_line_id = 0  
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_job_material_issue_details_with_no_source");
}

function get_job_material_issue_detail_records_with_no_source($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_issue_details   
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND source_line_id = 0";
    
        return db_query($sql, "get_job_material_issue_detail_records_with_no_source");
}

function get_job_material_issue_details_lines_count($trans_no, $type, $has_source_line=null, $not_id=0) {
    $sql = "SELECT COUNT(trans_id) lines_count 
            FROM ".TB_PREF."job_material_issue_details 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        if ($has_source_line === false) {
            $sql .= " AND source_line_id = 0";
        } elseif ($has_source_line === true) {
            $sql .= " AND source_line_id > 0";
        }
        
        if ($not_id > 0) {
            $sql .= " AND trans_id <> ".db_escape($not_id);
        }
    
        $result = db_query($sql, 'get_job_material_issue_details_lines_count');
        return db_result_value($result, 'lines_count');
}

function get_sql_for_job_material_issues_inquiry($trans_no, $parent_trans_no, $types_array, $jo_id, $reference, $project_id, $from_date, $to_date, $loc_code, 
                                                 $approval_status) {
    $sql = "SELECT sa.id,
                sa.trans_no,
                sa2.trans_no parent_trans_no,   
                sa2.type parent_type,   
                sa.jo_id,
                sa.reference,
                sa.type,
                l.location_name,
                sa.tran_date,
                sa.project_id,
                sa.comments,
                sa.approval_status,  
                sa.total
            FROM ".TB_PREF."job_material_issues sa  
                LEFT JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code        
                LEFT JOIN ".TB_PREF."job_material_issues sa2 
                    ON sa.jmr_no = sa2.id         
            WHERE sa.type IN (".implode(',', $types_array).") ";
    
        if ($trans_no > 0 || $jo_id > 0 || $parent_trans_no > 0 ) {
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
            if ($jo_id > 0) {
                $sql .= " AND sa.jo_id = ".db_escape($jo_id);
            }
            if ($parent_trans_no > 0) {
                $sql .= " AND sa2.trans_no = ".db_escape($parent_trans_no);
            }
        } 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);
            }                      
        }
    
        $sql .= " ORDER BY sa.tran_date, sa.id";
    
    return $sql;
}

function update_job_material_issue_header_approval_status($type, $trans_no, $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_issues
                SET approval_status = ".db_escape($new_status).",
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE type = ".db_escape($type)."   
                    AND trans_no = ".db_escape($trans_no)."   
                    AND approval_status = ".db_escape($pre_status);    

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

function get_job_material_issue_lines_total_by_source_line($type, $source_line_id, $not_trans_id=0, $approval_status_not_array=array()) {
    $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 ln.type = ".db_escape($type);
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        if (count($approval_status_not_array) > 0) {
            $sql .= " AND hd.approval_status NOT IN (".implode(',', $approval_status_not_array).")";
        }
    
        $result = db_query($sql, 'get_job_material_issue_lines_total_by_source_line');
        return db_result_value($result, 'total_qty');
}

function save_job_material_requisition_lines_issued_alloc_issued_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'];
            
            $up_af_rw = update_job_material_requisition_line_issued_alloc_qty_issued_qty($id, $row_state, $returned_quantity);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[$type].' total issued/ issued alloc qty.');
                }
            
            $jm_req_type = $row['source_type']; $jm_req_trans_no = $row['source_trans_no']; $jm_req_line_id = $row['source_line_id'];
                
                $jmr_af_rw = update_job_material_requisition_lines_issued_qty_total($jm_req_type, $jm_req_trans_no, $jm_req_line_id);
                    if ($jmr_af_rw !== 1) {
                        return array("success" => false, "message" => 'Could not update '.$systypes_array[ST_JOB_MATERIAL_REQ].' total issued/ issued alloc qty.');
                    }
        }
        
        return array("success" => true, "message" => '');
}

function get_job_material_issue_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_job_material_issue_lines_total_qty');
        return db_result_value($result, 'total_qty');
}

function update_job_material_requisition_line_issued_alloc_qty_issued_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_issued_alloc_qty_ = get_job_material_issue_lines_total_qty(ST_JOB_MATERIAL_ISSUE, $trans_id, $ap_array);
        $total_issued_alloc_qty = abs($total_issued_alloc_qty_);

        $total_issued_qty_ = get_job_material_issue_lines_total_qty(ST_JOB_MATERIAL_ISSUE, $trans_id, array(AS_APPROVED, AS_COMPLETED));
        $total_issued_qty = abs($total_issued_qty_);

        $total_qty_issued_qty_issued_alloc_qty = $total_issued_qty + $total_issued_alloc_qty - $returned_quantity;
            
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issued_alloc = ".db_escape($total_issued_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_qty_issued_qty_issued_alloc_qty";
            
            db_query($sql, 'update_job_material_requisition_line_issued_alloc_qty_issued_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 issued_quantity = ".db_escape($total_issued_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_qty_issued_qty_issued_alloc_qty";
            
            db_query($sql_1, 'update_job_material_requisition_line_issued_alloc_qty_issued_qty');
                $af_row_2 = db_num_affected_rows();

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

function save_job_material_requisition_lines_returned_alloc_returned_qty_total($req_trans_no, $return_trans_no) {
    global $systypes_array;
    
    $total_alloc = $total_returned = 0;
    
    $jm_req_lns = $jmi_req_lns = array();
    $result = get_job_material_return_details_with_and_without_issue_req_line($req_trans_no, $return_trans_no); 
        while ($row = db_fetch($result)) {
            $id = $row['issue_line_id'];
            $issued_quantity = $row['qty'];
            $req_ln_id = $row['trans_id'];
            $is_req_ln_id = $row['issue_req_line_id'];
            
            $up_af_rw = update_job_material_issue_line_returned_alloc_returned($id, $row['issue_d_row_state'], $issued_quantity, $total_alloc, $total_returned);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[ST_JOB_MATERIAL_REQ].' total returned/ returned alloc qty.');
                }
            
            // skip not jm issue req line recs
            if ($is_req_ln_id > 0) {
                if (! array_key_exists($is_req_ln_id, $jmi_req_lns)) {
                    $jmi_req_lns[$is_req_ln_id] = 0;
                } 
                if (! isset($is_req_ln_id[$is_req_ln_id]['total_alloc'])) {
                    $jmi_req_lns[$is_req_ln_id] = array('total_alloc' => 0, 'total_returned' => 0);
                } 
                
                $jmi_req_lns[$is_req_ln_id]['total_alloc'] += $total_alloc;
                $jmi_req_lns[$is_req_ln_id]['total_returned'] += $total_returned;
                
                if (! array_key_exists($req_ln_id, $jm_req_lns)) {
                    $jm_req_lns[$req_ln_id] = 0;
                } 
                if (! isset($jm_req_lns[$req_ln_id]['total_alloc'])) {
                    $jm_req_lns[$req_ln_id] = array('total_alloc' => 0, 'total_returned' => 0);
                } 
                $jm_req_lns[$req_ln_id]['total_alloc'] += $total_alloc;
                $jm_req_lns[$req_ln_id]['total_returned'] += $total_returned;
            }
        }
        
        $ir_type = ST_JOB_MATERIAL_ISSUE_REQ;
        foreach ($jmi_req_lns as $trans_id => $value) {
            $jmr_af_rw = update_job_material_requisition_line_returned_alloc_returned($trans_id, $value['total_alloc'], $value['total_returned'], $ir_type);
                if ($jmr_af_rw !== 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[ST_JOB_MATERIAL_ISSUE_REQ].' total returned/ returned alloc qty.');
                }
        }
        
        $rq_type = ST_JOB_MATERIAL_REQ;
        foreach ($jm_req_lns as $trans_id => $value) {
            $jmr_af_rw = update_job_material_requisition_line_returned_alloc_returned($trans_id, $value['total_alloc'], $value['total_returned'], $rq_type);
                if ($jmr_af_rw !== 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[ST_JOB_MATERIAL_ISSUE_REQ].' total returned/ returned alloc qty.');
                }
        }
        
        return array("success" => true, "message" => '');
}

function update_job_material_issue_line_returned_alloc_returned($trans_id, $line_row_state=0, $issued_quantity=0, &$total_returned_alloc_qty=0, &$total_returned_qty=0, 
                                                                $type=ST_JOB_MATERIAL_RETURN) {
    $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_job_material_issue_lines_total_qty($type, $trans_id, $ap_array);
        $total_returned_alloc_qty = abs($total_returned_alloc_qty_);

        $total_returned_qty_ = get_job_material_issue_lines_total_qty($type, $trans_id, array(AS_APPROVED));
        $total_returned_qty = abs($total_returned_qty_);
        
        $all_returned_qty = $total_returned_alloc_qty + $total_returned_qty;

        $total_issued_qty = abs($issued_quantity);

            $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 $total_issued_qty >= $all_returned_qty";
            
            db_query($sql, 'update_job_material_issue_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 $total_issued_qty >= $all_returned_qty";
            
            db_query($sql_1, 'update_job_material_issue_line_returned_alloc_returned');
                $af_row_2 = db_num_affected_rows();

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

        return 1;
}

function update_job_material_requisition_line_returned_alloc_returned($trans_id, $total_alloc, $total_returned, $type) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $total_return_n_alloc = $total_alloc + $total_returned;
    
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details    
                    SET returned_alloc = ".db_escape($total_alloc).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = row_state + 1 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND type = ".db_escape($type)." 
                        AND issued_quantity >= $total_return_n_alloc";
            
            db_query($sql, 'update_job_material_requisition_line_returned_alloc_returned');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $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).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = row_state + 1 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND type = ".db_escape($type)." 
                        AND issued_quantity >= $total_return_n_alloc";
            
            db_query($sql_1, 'update_job_material_requisition_line_returned_alloc_returned');
                $af_row_2 = db_num_affected_rows();

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

        return 1;
}

function get_sql_for_job_material_issues_returns_inquiry($req_trans_no, $iss_ret_trans_no, $jo_id, $reference, $type, $project_id, $from_date, $to_date, $loc_code, 
                                                         $ap_status) {
    $sql = "SELECT sa.jo_id, req.trans_no req_trans_no,
                sa.trans_no iss_ret_trans_no,
                sa.reference,
                sa.type,
                l.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 
            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 
            WHERE sa.type IN (".implode(',', array(ST_JOB_MATERIAL_ISSUE, ST_JOB_MATERIAL_RETURN)).")";
    
        if ($req_trans_no > 0 || $iss_ret_trans_no > 0 || $jo_id > 0) {
            if ($req_trans_no > 0) {
                $sql .= " AND req.trans_no = ".db_escape($req_trans_no);
            }
            
            if ($iss_ret_trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($iss_ret_trans_no);
            }
            
            if ($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 ($type > -1) {
                $sql .= " AND sa.type = ".db_escape($type);
            }
            
            $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 ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " ORDER BY sa.id DESC";
    
        return $sql;
}

function delete_job_material_issue_detail_lines($type, $trans_no, $has_source_line=null) {
    $sql = "DELETE FROM ".TB_PREF."job_material_issue_details 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
        if ($has_source_line === false) {
            $sql .= " AND source_line_id = 0";
        } elseif ($has_source_line === true) {
            $sql .= " AND source_line_id > 0";
        }
    
	db_query($sql, "delete_job_material_issue_detail_lines");
        return db_num_affected_rows();
}

function delete_job_material_issue_detail_line_by_id($id) {
    $sql = "DELETE FROM ".TB_PREF."job_material_issue_details 
            WHERE trans_id = ".db_escape($id);
    
	db_query($sql, "delete_job_material_issue_detail_line_by_id");
        return db_num_affected_rows();
}

function get_job_material_issue_detail_record($line_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."job_material_issue_details 
            WHERE trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "get_job_material_issue_detail_record");
        return db_fetch($result);
}

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

function get_job_material_issue_details_line_by_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name tran_unit_name,
                un2.name base_unit_name,
                ps.description site_description, 
                pt.description task_description, 
                l.location_name  
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."locations l 
                    ON ard.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."item_units un2   
                    ON am.units = un2.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)." 
                AND ard.trans_id = ".db_escape($line_id);
    
        $result = db_query($sql, "get_job_material_issue_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

function update_job_material_issue_row_state($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_issues 
                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_issue_row_state");
            return db_num_affected_rows();
}

function get_job_material_issue_headers_by_jmr_no_type($jmr_no, $type) {
    $sql = "SELECT sa.*,
                l.location_name,
                l2.location_name to_location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."job_material_issues 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.jmr_no = ".db_escape($jmr_no)." 
                AND sa.type = ".db_escape($type);
    
        return db_query($sql, "get_job_material_issue_headers_by_jmr_no_type");
}

function get_job_material_issue_header_by_jmr_no_type($jmr_no, $type) {
    $sql = "SELECT sa.*,
                l.location_name,
                l2.location_name to_location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."job_material_issues 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.jmr_no = ".db_escape($jmr_no)." 
                AND sa.type = ".db_escape($type);
    
        $result = db_query($sql, "get_job_material_issue_header_by_jmr_no_type");
        return db_fetch($result);
}

function get_job_material_issue_headers_by_filters($jo_id=0, $id=0, $type=0, $trans_no=0, $loc_code='', $project_id=0, $from_date=null, $to_date=null, $order='', 
                                                   $types_array=array(), $source_id=0) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."job_material_issues 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);
        }
        if (count($types_array) > 0) {
            $sql .= " AND sa.type IN (".implode(',', $types_array).")";
        }
        if ($source_id > 0) {
            $sql .= " AND sa.jmr_no = ".db_escape($source_id);
        }
    
        if (strlen($order) > 0) {
            $sql .= " ORDER BY $order";
        }
    
        return db_query($sql, "get_job_material_issue_headers_by_filters");
}

function get_job_material_issue_details_by_filters($jo_id=0, $id=0, $types_array=array(), $trans_no=0, $loc_code='', $project_id=0, $from_date=null, $to_date=null, 
                                                   $task_id=0, $project_site_id=0, $approval_array=array(), $order='', $fetch=false) {
    
    $sql = "SELECT ard.*, hd.jo_id, l.location_name, am.description stock_description, am.stock_id, am.mb_flag, un.name tran_unit_name,
                un2.name base_unit_name, ps.description site_description, pt.description task_description, hd.approval_status  
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."job_material_issues hd 
                    ON ard.type = hd.type  
                        AND ard.trans_no = hd.trans_no   
                JOIN ".TB_PREF."locations l 
                    ON ard.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."item_units un2   
                    ON am.units = un2.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 1 = 1 ";
    
        if ($jo_id > 0) {
            $sql .= " AND hd.jo_id = ".db_escape($jo_id);
        }
        if ($id > 0) {
            $sql .= " AND ard.id = ".db_escape($id);
        }
        if (count($types_array) > 0) {
            $sql .= " AND hd.type IN (".implode(',', $types_array).")";
        }
        if ($trans_no > 0) {
            $sql .= " AND ard.trans_no = ".db_escape($trans_no);
        }
        if (strlen($loc_code) > 0) {
            $sql .= " AND ard.loc_code = ".db_escape($loc_code);
        }
        if ($project_id > 0) {
            $sql .= " AND ard.project_id = ".db_escape($project_id);
        }
        if ($from_date !== null) {
            $sql .= " AND ard.tran_date >= ".db_escape($from_date);
        }
        if ($to_date !== null) {
            $sql .= " AND ard.tran_date <= ".db_escape($to_date);
        }
        if ($task_id > 0) {
            $sql .= " AND ard.task_id = ".db_escape($task_id);
        }
        if ($project_site_id > 0) {
            $sql .= " AND ard.project_site_id = ".db_escape($project_site_id);
        }
        if (count($approval_array) > 0) {
            $sql .= " AND hd.approval_status IN (".implode(',', $approval_array).")";
        }
    
        if (strlen($order) > 0) {
            $sql .= " ORDER BY $order";
        }
        
            $result = db_query($sql, "get_job_material_issue_details_by_filters");
                if ($fetch === false) {
                    return $result; 
                } else {
                    return db_fetch($result);
                }
}

function get_sql_for_approve_job_material_issues($current_user_id, $type) {
    $sql = "SELECT jmi.id,
                jmi.trans_no,
                jmi.type,
                jmi.jo_id,
                jmi.reference,
                jmi.tran_date,                   
                l.location_name,                   
                jmi.project_id,                   
                pm.description project_description,
                c.real_name,
                jmi.created_on,
                jmi.approval_status 
            FROM ".TB_PREF."job_material_issues jmi 
                JOIN ".TB_PREF."projects_master pm 
                    ON jmi.project_id = pm.project_id
                JOIN ".TB_PREF."users c 
                    ON jmi.created_by = c.id  
                JOIN ".TB_PREF."approvers apr 
                    ON jmi.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."locations l 
                    ON jmi.loc_code = l.loc_code   
            WHERE jmi.approval_status IN (".implode(',', array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER)).") 
                AND apr.user_id = ".db_escape($current_user_id)." 
                AND jmi.type = ".db_escape($type);
      
        return $sql;
}

function get_job_material_issue_requisition_line_remain_qty_array($source_base_qty, $source_line_id=0, $tran_qty=0, $conversion_factor=1, $line_id=0, $dec=4, $returned_qty=0) {
    
    $source_tran_qty = $source_base_qty / $conversion_factor;
            
        $total_issued_req_qty_ = get_job_material_issue_lines_total_qty(ST_JOB_MATERIAL_ISSUE, $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_issue_lines_total_qty(ST_JOB_MATERIAL_ISSUE, $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 - $returned_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 update_job_material_requisition_lines_issued_qty_total($jm_req_type, $jm_req_trans_no, $jm_req_line_id) {
    $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_issued_alloc_qty_ = get_job_material_issue_lines_total_qty_by_jm_req($jm_req_type, $jm_req_trans_no, $jm_req_line_id, $ap_array);
        $total_issued_alloc_qty = abs($total_issued_alloc_qty_);

        $total_issued_qty_ = get_job_material_issue_lines_total_qty_by_jm_req($jm_req_type, $jm_req_trans_no, $jm_req_line_id, array(AS_APPROVED, AS_COMPLETED));
        $total_issued_qty = abs($total_issued_qty_);

        $total_qty_issued_qty_issued_alloc_qty = $total_issued_qty + $total_issued_alloc_qty;
            
            $sql = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issued_alloc = ".db_escape($total_issued_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = row_state + 1
                    WHERE trans_id = ".db_escape($jm_req_line_id)." 
                        AND qty >= $total_qty_issued_qty_issued_alloc_qty - returned_quantity";
            
            db_query($sql, 'update_job_material_requisition_lines_issued_qty_total');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
        $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."job_material_requisition_details  
                    SET issued_quantity = ".db_escape($total_issued_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = row_state + 1
                    WHERE trans_id = ".db_escape($jm_req_line_id)." 
                        AND qty >= $total_qty_issued_qty_issued_alloc_qty - returned_quantity";
            
            db_query($sql_1, 'update_job_material_requisition_lines_issued_qty_total');
                $af_row_2 = db_num_affected_rows();

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

function get_job_material_issue_lines_total_qty_by_jm_req($jm_req_type, $jm_req_trans_no, $jm_req_line_id, $ap_array, $not_trans_id=0) {
    $sql = "SELECT SUM(isd.qty) total_qty 
            FROM ".TB_PREF."job_material_issue_details isd  
                JOIN ".TB_PREF."job_material_issues ish  
                    ON isd.type = ish.type 
                        AND isd.trans_no = ish.trans_no  
                JOIN ".TB_PREF."job_material_requisition_details ird   
                    ON isd.source_line_id = ird.trans_id   
                        AND ird.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                JOIN ".TB_PREF."job_material_requisition_details rd   
                    ON ird.source_type = rd.type 
                        AND ird.source_trans_no = rd.trans_no  
                        AND ird.source_line_id = rd.trans_id   
            WHERE rd.type = ".db_escape($jm_req_type)." 
                AND rd.trans_no = ".db_escape($jm_req_trans_no)."
                AND rd.trans_id = ".db_escape($jm_req_line_id)."
                AND ish.approval_status IN (". implode(',', $ap_array).") 
                AND ish.type = ".db_escape(ST_JOB_MATERIAL_ISSUE);
    
        if ($not_trans_id > 0) {
            $sql .= " AND ln.trans_id <> ".db_escape($not_trans_id);
        }
    
        $result = db_query($sql, 'get_job_material_issue_lines_total_qty_by_jm_req');
        return db_result_value($result, 'total_qty');
}

function get_not_job_material_issue_requisition_lines_from_job_material_issue($issue_req_trans_no, $issue_trans_no) {
    $sql = "SELECT ard.*,
                jmid.trans_id issue_line_id, 
                jmid.item_type issue_item_type, 
                jmid.stock_id issue_stock_id, 
                jmid.item_description issue_item_description, 
                jmid.tran_unit issue_tran_unit, 
                jmid.tran_qty issue_tran_qty  
            FROM ".TB_PREF."job_material_requisition_details ard 
                JOIN ".TB_PREF."job_material_issue_details jmid 
                    ON ard.trans_id = jmid.source_line_id  
            WHERE ard.trans_no = ".db_escape($issue_req_trans_no)." 
                AND ard.type = ".db_escape(ST_JOB_MATERIAL_ISSUE_REQ)." 
                AND jmid.trans_no = ".db_escape($issue_trans_no)." 
                AND jmid.type = ".db_escape(ST_JOB_MATERIAL_ISSUE)." 
                AND jmid.stock_id <> ard.stock_id 
            ORDER BY ard.trans_id";
    
        return db_query($sql, "get_not_job_material_issue_requisition_lines_from_job_material_issue");
}

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

function update_job_material_issue_row_state_by_id($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_issues 
                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_issue_row_state_by_id");
            return db_num_affected_rows();
}

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

function update_job_material_issue_header_totals($type, $trans_no, $row_state=0) {
    $lines_total = get_job_material_issue_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_issues   
                    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_issue_header_totals");   
            return db_num_affected_rows();
}

function get_job_material_issue_and_return_details($type, $trans_no, $return_type, $return_trans_no) {
    $sql = "SELECT iss_d.trans_id issue_line_id, iss_d.conversion_factor, iss_d.item_type, iss_d.item_description, iss_d.tran_qty, iss_d.qty, iss_d.tran_unit, 
                iss_d.returned_quantity, iss_d.returned_alloc, 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, iss_d.project_site_id, iss_d.trans_no issue_trans_no, iss_d.row_state issue_d_row_state, 
                iss_d.standard_cost, iss_d.task_id,   
                
                rtn_d.trans_id return_line_id, rtn_d.tran_unit return_tran_unit, rtn_d.tran_qty return_tran_qty, rtn_d.conversion_factor return_conversion_factor, 
                rtn_d.line_comment, rtn_d.project_site_id return_site_id, un_2.name return_unit_name 
            FROM ".TB_PREF."job_material_issues iss  
                JOIN ".TB_PREF."job_material_issue_details iss_d 
                    ON iss_d.type = iss.type   
                        AND iss_d.trans_no = iss.trans_no 
                LEFT JOIN ".TB_PREF."job_material_issue_details rtn_d 
                    ON iss_d.trans_id = rtn_d.source_line_id  
                        AND rtn_d.type = ".db_escape($return_type)." 
                        AND rtn_d.trans_no = ".db_escape($return_trans_no)." 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON iss_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON iss_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON iss_d.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON iss_d.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."item_units un_2   
                    ON rtn_d.tran_unit = un_2.abbr  
            WHERE iss.type = ".db_escape($type)." 
                AND iss.trans_no = ".db_escape($trans_no)." 
                AND iss.approval_status IN (".implode(',', array(AS_APPROVED, AS_COMPLETED, AS_CLOSED)).") 
            ORDER BY iss_d.trans_id";
    
        return db_query($sql, "get_job_material_issue_and_return_details");  
}

function get_job_material_issue_partially_returned_lines($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."job_material_issue_details   
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND qty > returned_quantity + returned_alloc";
    
        return db_query($sql, "get_job_material_issue_partially_returned_lines");
}

function update_job_material_issue_all_lines_returned_alloc_returned($issue_type, $issue_trans_no, $return_type) {
    global $systypes_array;
    
    $result = get_job_material_issue_details($issue_trans_no, $issue_type);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $issued_quantity = $row['qty'];
            
            $up_af_rw = update_job_material_issue_line_returned_alloc_returned($id, $row['row_state'], $issued_quantity, $total_alloc, $total_returned, $return_type);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[$issue_type].' total returned/ returned alloc qty.');
                }
        }
        
        return array("success" => true, "message" => '');
}

?>