<?php

function write_budget_to_budget_transfer_header($type, &$id, &$trans_no, $to_jo_id, $reference, $project_id, $to_loc_code, $to_task_id, $comments, $date, 
                                                $from_loc_code, $row_state) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_budget_to_budget_transfer_header($id, $reference, $date, $comments, $row_state);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $id = insert_budget_to_budget_transfer_header($type, $trans_no, $to_jo_id, $reference, $project_id, $to_loc_code, $to_task_id, $comments, 
                                                          $date, $from_loc_code);
        }
    
        $Refs->save($type, $trans_no, $reference);

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

        return $id;
}

function write_budget_to_budget_transfer_header_by_type_trans_no($type, $trans_no, $reference, $project_id, $from_loc_code, $to_loc_code, $comments, 
                                                                 $date, $source_id=0) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_budget_to_budget_transfer_header_by_type_trans_no($type, $trans_no, $reference, $from_loc_code, $to_loc_code, $comments, 
                                                                               $date);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $inser_id = insert_budget_to_budget_transfer_header($type, $trans_no, $reference, $project_id, $from_loc_code, $to_loc_code, $comments, 
                                                                $date, $source_id);
                if (! ($inser_id > 0)) {
                    return 0;
                }
        }
    
        $Refs->save($type, $trans_no, $reference);

        return $trans_no;
}
     
function insert_budget_to_budget_transfer_header($type, $trans_no, $to_jo_id, $reference, $project_id, $to_loc_code, $to_task_id, $comments, $date,
                                                 $from_loc_code) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."budget_to_budget_transfers
                      ( type, trans_no, to_jo_id, reference, 
                        project_id, to_loc_code, to_task_id, comments, 
                        tran_date, from_loc_code, created_by, created_on )         
                VALUES( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($to_jo_id).", ".db_escape($reference).", 
                        ".db_escape($project_id).", ".db_escape($to_loc_code).", ".db_escape($to_task_id).", ".db_escape($comments).", 
                        ".db_escape($date).", ".db_escape($from_loc_code).", ".db_escape($created_by).", ".db_escape($created_on).")";

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

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

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

function insert_budget_to_budget_transfer_line($to_jo_id, $type, $trans_no, $to_loc_code, $to_task_id, $item_type, $stock_id, $item_description, 
                                               $from_jo_id, $from_task_id, $tran_unit, $tran_qty, $qty, $conversion_factor, $standard_cost, 
                                               $project_site_id, $line_comment, $from_loc_code, $source_line_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."budget_to_budget_transfer_details
                      ( to_jo_id, trans_no, type, stock_id, 
                        tran_unit, item_type, item_description, 
                        tran_qty, qty, conversion_factor, 
                        standard_cost, project_site_id, line_comment, 
                        to_loc_code, to_task_id, from_jo_id, from_task_id, 
                        from_loc_code, created_by, created_on, source_line_id)        
                VALUE ( ".db_escape($to_jo_id).", ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($stock_id).", 
                        ".db_escape($tran_unit).", ".db_escape($item_type).", ".db_escape($item_description).",  
                        ".db_escape($tran_qty).", ".db_escape($qty).", ".db_escape($conversion_factor).", 
                        ".db_escape($standard_cost).", ".db_escape($project_site_id).", ".db_escape($line_comment).", 
                        ".db_escape($to_loc_code).", ".db_escape($to_task_id).", ".db_escape($from_jo_id).", ".db_escape($from_task_id).", 
                        ".db_escape($from_loc_code).", ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";     

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

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

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

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

function get_budget_to_budget_transfer_header($id) {
    $sql = "SELECT sa.*,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                pm.description project_description,
                tpt.description to_task_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."budget_to_budget_transfers sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."project_tasks tpt 
                    ON sa.to_task_id = tpt.task_id 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
                LEFT JOIN ".TB_PREF."locations l 
                    ON sa.from_loc_code = l.loc_code 
                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_budget_to_budget_transfer_header");
        return db_fetch($result);
}

function get_budget_to_budget_transfer_header_type_trans_no($type, $trans_no) {
    $sql = "SELECT sa.*,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                tpt.description to_task_description,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."budget_to_budget_transfers sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.from_loc_code = l.loc_code 
                JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
                JOIN ".TB_PREF."project_tasks tpt 
                    ON sa.to_task_id = tpt.task_id 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE sa.type = ".db_escape($type)." 
                AND sa.trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_budget_to_budget_transfer_header_type_trans_no");
        return db_fetch($result);
}

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

function get_budget_to_budget_transfer_details($trans_no, $type) {
    $sql = "SELECT bbt_d.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                am.stock_control_method,
                un.name unit_name,
                am.stock_control_method,
                fpt.description from_task_description,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                ps.description site_description 
            FROM ".TB_PREF."budget_to_budget_transfer_details bbt_d  
                JOIN ".TB_PREF."project_tasks fpt 
                    ON bbt_d.from_task_id = fpt.task_id 
                JOIN ".TB_PREF."project_tasks tpt 
                    ON bbt_d.to_task_id = tpt.task_id 
                JOIN ".TB_PREF."locations l2 
                    ON bbt_d.to_loc_code = l2.loc_code 
                LEFT JOIN ".TB_PREF."locations l 
                    ON bbt_d.from_loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON bbt_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON bbt_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON bbt_d.project_site_id = ps.id 
            WHERE bbt_d.trans_no = ".db_escape($trans_no)." 
                AND bbt_d.type = ".db_escape($type);
    
        return db_query($sql, "get_budget_to_budget_transfer_details");
}

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

function get_sql_for_budget_to_budget_transfers_inquiry($trans_no, $to_jo_id, $reference, $project_id, $from_date, $to_date, $to_loc_code, $ap_status) {
    $sql = "SELECT  sa.id,
                sa.to_jo_id,
                sa.trans_no,
                sa.reference,
                sa.type,
                l.location_name to_location_name,
                sa.tran_date,
                pm.description project_description,
                sa.comments,
                sa.approval_status 
            FROM ".TB_PREF."budget_to_budget_transfers sa  
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.to_loc_code = l.loc_code        
            WHERE sa.type = ".db_escape(ST_BUDGET_TO_BUDGET_TRANSFER);
    
        if ($trans_no > 0){
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        } elseif ($to_jo_id > 0){
            $sql .= " AND sa.to_jo_id = ".db_escape($to_jo_id);
        } else {
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            if ($project_id > 0) {
                $sql .= " AND sa.project_id = ".db_escape($project_id);
            }
            
            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if ($to_loc_code != '') {        
                $sql .= " AND sa.to_loc_code = ".db_escape($to_loc_code);
            }
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }                      
        }
    
        $sql .= " ORDER BY sa.tran_date, sa.id";
    
    return $sql;
}

function update_budget_to_budget_transfer_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."budget_to_budget_transfers
                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_budget_to_budget_transfer_header_approval_status");
            return db_num_affected_rows();
}

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

function get_sql_for_budget_to_budget_transfers_returns_inquiry($req_trans_no, $iss_ret_trans_no, $reference, $type, $project_id, $from_date, $to_date, 
                                                                $from_loc_code, $to_loc_code, $ap_status) {
    $sql = "SELECT  req.trans_no req_trans_no,
                sa.trans_no iss_ret_trans_no,
                sa.reference,
                sa.type,
                l2.location_name to_location_name,
                sa.tran_date,
                pm.description project_description,
                sa.comments,
                sa.approval_status, 
                sa.id  
            FROM ".TB_PREF."budget_to_budget_transfers sa  
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code        
            WHERE sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 
    
        if ($req_trans_no > 0 || $iss_ret_trans_no > 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);
            }
        } else {
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            if ($type > -1) {
                $sql .= " AND sa.type = ".db_escape($type);
            }
            if($project_id > 0){
                $sql .= " AND sa.project_id = ".db_escape($project_id);
            }
            if ($from_loc_code != '') {        
                $sql .= " AND sa.from_loc_code = ".db_escape($from_loc_code);
            }
            if ($to_loc_code != '') {        
                $sql .= " AND sa.to_loc_code = ".db_escape($to_loc_code);
            }
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " ORDER BY sa.tran_date, sa.type, sa.trans_no";
    
        return $sql;
}

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

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

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

function get_budget_to_budget_transfer_detail_record_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."budget_to_budget_transfer_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_budget_to_budget_transfer_detail_record_type_trans_no_line_no");
        return db_fetch($result);
}

function get_budget_to_budget_transfer_details_line_by_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT bbt_d.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name tran_unit_name,
                un2.name base_unit_name,
                ps.description site_description 
            FROM ".TB_PREF."budget_to_budget_transfer_details bbt_d 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON bbt_d.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON bbt_d.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."item_units un2   
                    ON am.units = un2.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON bbt_d.project_site_id = ps.id 
            WHERE bbt_d.trans_no = ".db_escape($trans_no)." 
                AND bbt_d.type = ".db_escape($type)." 
                AND bbt_d.trans_id = ".db_escape($line_id);
    
        $result = db_query($sql, "get_budget_to_budget_transfer_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

function update_budget_to_budget_transfer_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."budget_to_budget_transfers 
                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_budget_to_budget_transfer_row_state");
            return db_num_affected_rows();
}

function get_budget_to_budget_transfer_headers_by_filters($to_jo_id=0, $id=0, $type=0, $trans_no=0, $from_loc_code='', $to_loc_code='', $project_id=0, 
                                                          $from_date=null, $to_date=null) {
    $sql = "SELECT sa.*,
                l.location_name from_location_name,
                l2.location_name to_location_name,
                pm.description project_description,
                u.real_name requestor_name 
            FROM ".TB_PREF."budget_to_budget_transfers sa 
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.from_loc_code = l.loc_code 
                JOIN ".TB_PREF."locations l2 
                    ON sa.to_loc_code = l2.loc_code 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id 
            WHERE 1 = 1 ";
    
        if ($to_jo_id > 0) {
            $sql .= " AND sa.to_jo_id = ".db_escape($to_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($from_loc_code) > 0) {
            $sql .= " AND sa.from_loc_code = ".db_escape($from_loc_code);
        }
        if (strlen($to_loc_code) > 0) {
            $sql .= " AND sa.to_loc_code = ".db_escape($to_loc_code);
        }
        if ($project_id > 0) {
            $sql .= " AND sa.project_id = ".db_escape($project_id);
        }
        if ($from_date !== null) {
            $sql .= " AND sa.tran_date >= ".db_escape($from_date);
        }
        if ($to_date !== null) {
            $sql .= " AND sa.tran_date <= ".db_escape($to_date);
        }
    
        return db_query($sql, "get_quality_check_headers_by_filters");
}

function create_jobs_array_from_stock_moves_by_stock_id($item_type, $stock_id, $from_loc_code, $to_jo_id, &$jobs_remain_array=array(), $not_id=0) {
    $jobs_array = array();
    
    $result = get_jobs_from_stock_moves_by_stock_id($item_type, $stock_id, $from_loc_code, $to_jo_id);
        while ($row = db_fetch($result)) {
            $jo_id = $row['jo_id'];
            $sm_remain_qty = $row['sm_remain_qty'];
            
            $allocated_qty = get_job_material_req_allocated_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id);
            
            $total_b2b_qty = get_budget_to_budget_transfer_total_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id, 0, $not_id);
            
            $remain_qty = $sm_remain_qty - $allocated_qty - $total_b2b_qty;
            
            if ($remain_qty > 0) {
                $jobs_array[$jo_id] = $jo_id;
                $jobs_remain_array[$jo_id] = $remain_qty;
            }
        }
    
        return $jobs_array;
}

function get_jobs_from_stock_moves_by_stock_id($item_type, $stock_id, $from_loc_code, $not_jo_id=0, $jo_id=0) {
    $in_array = array(ST_DIRECT_PURCHASE_RECEIPT, ST_DIRECT_PURCHASE_RETURN);
    
        $sql = "SELECT SUM(sm.qty) sm_remain_qty, dpr.jo_id ";
        
            if ($item_type == NSIT_STOCK_ITEM) {
                $sql .= " FROM ".TB_PREF."stock_moves sm ";
            } elseif ($item_type == NSIT_NON_STOCK_ITEM) {
                $sql .= " FROM ".TB_PREF."non_stock_moves sm ";
            }
                
            $sql .= " JOIN ".TB_PREF."quality_check_details dpr_d 
                        ON sm.type = dpr_d.type
                            AND sm.trans_no = dpr_d.trans_no
                            AND sm.stock_id = dpr_d.stock_id
                    JOIN ".TB_PREF."quality_checks dpr  
                        ON dpr_d.type = dpr.type
                            AND dpr_d.trans_no = dpr.trans_no
                WHERE sm.stock_id = ".db_escape($stock_id)." 
                    AND sm.loc_code = ".db_escape($from_loc_code)." 
                    AND sm.type IN (".implode(',', $in_array).")";
                        
            if ($not_jo_id > 0) {
                $sql .= " AND dpr.jo_id <> ".db_escape($not_jo_id);
            }
            
            if ($jo_id > 0) {
                $sql .= " AND dpr.jo_id = ".db_escape($jo_id);
            }

            $sql .= " GROUP BY dpr.jo_id 
                        HAVING sm_remain_qty > 0 
                        ORDER BY dpr.jo_id ";
        
        return db_query($sql, "get_jobs_from_stock_moves_by_stock_id");
}

function get_job_material_req_allocated_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id) {
    $ap_array = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR, AS_APPROVED, AS_COMPLETED);
    
        $sql = "SELECT SUM(jmrq_d.qty - jmrq_d.issued_quantity + jmrq_d.returned_quantity) allocated_qty 
                FROM ".TB_PREF."job_material_requisition_details jmrq_d 
                    JOIN ".TB_PREF."job_material_requisitions jmrq 
                        ON jmrq_d.type = jmrq.type  
                            AND jmrq_d.trans_no = jmrq.trans_no  
                WHERE jmrq_d.stock_id = ".db_escape($stock_id)." 
                    AND jmrq.loc_code = ".db_escape($from_loc_code)." 
                    AND jmrq_d.type = ".db_escape(ST_JOB_MATERIAL_REQ)." 
                    AND jmrq.jo_id = ".db_escape($jo_id)." 
                    AND jmrq.approval_status IN (". implode(',', $ap_array).")";
        
        $result = db_query($sql, "get_job_material_req_allocated_qty_by_stock_id_job_id");
        return db_result_value($result, 'allocated_qty');
}

function create_budget_to_budget_transfer_job_tasks_array($item_type, $jo_id, $stock_id, $from_loc_code) {
    $from_tasks_array = array();
    
        $result = get_job_tasks_from_stock_moves_by_stock_id_job_id($item_type, $stock_id, $jo_id, $from_loc_code);
            while ($row = db_fetch($result)) {
                $from_tasks_array[$row['task_id']] = $row['task_code'].' - '.$row['task_name'];
            }

            return $from_tasks_array;
}

function get_job_tasks_from_stock_moves_by_stock_id_job_id($item_type, $stock_id, $jo_id, $loc_code) {
    $in_array = array(ST_DIRECT_PURCHASE_RECEIPT, ST_DIRECT_PURCHASE_RETURN);
    
        $sql = "SELECT SUM(sm.qty) sm_remain_qty, sm.task_id, pt.task_code, pt.description task_name ";
        
            if ($item_type == NSIT_STOCK_ITEM) {
                $sql .= " FROM ".TB_PREF."stock_moves sm ";
            } elseif ($item_type == NSIT_NON_STOCK_ITEM) {
                $sql .= " FROM ".TB_PREF."non_stock_moves sm ";
            }
                
            $sql .= " JOIN ".TB_PREF."quality_check_details dpr_d 
                        ON sm.type = dpr_d.type
                            AND sm.trans_no = dpr_d.trans_no
                            AND sm.stock_id = dpr_d.stock_id
                    JOIN ".TB_PREF."quality_checks dpr  
                        ON dpr_d.type = dpr.type
                            AND dpr_d.trans_no = dpr.trans_no
                    JOIN ".TB_PREF."project_tasks pt 
                        ON sm.task_id = pt.task_id 
                WHERE sm.stock_id = ".db_escape($stock_id)." 
                    AND sm.loc_code = ".db_escape($loc_code)." 
                    AND sm.type IN (".implode(',', $in_array).") 
                    AND dpr.jo_id = ".db_escape($jo_id)." 
                GROUP BY sm.task_id  
                HAVING sm_remain_qty > 0 
                ORDER BY sm.task_id ";
        
        return db_query($sql, "get_job_tasks_from_stock_moves_by_stock_id_job_id");
}

function get_job_remain_qty_from_stock_moves_and_b_to_b_transfer($item_type, $stock_id, $from_loc_code, $jo_id, $selected_id) {
    $remain_qty = 0;
    
    $result = get_jobs_from_stock_moves_by_stock_id($item_type, $stock_id, $from_loc_code, 0, $jo_id);
        $row = db_fetch($result);
        
            if (is_array($row)) {
                $sm_remain_qty = $row['sm_remain_qty'];
                $allocated_qty = get_job_material_req_allocated_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id);

                $total_b2b_qty = get_budget_to_budget_transfer_total_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id, 0, $selected_id);
            
                $remain_qty = $sm_remain_qty - $allocated_qty - $total_b2b_qty;
            }
            
                return $remain_qty;
}

function get_budget_to_budget_transfer_total_qty_by_stock_id_job_id($stock_id, $from_loc_code, $jo_id, $id=0, $not_id=0) {
    $sql = "SELECT SUM(qty) total_qty 
            FROM ".TB_PREF."budget_to_budget_transfer_details  
            WHERE stock_id = ".db_escape($stock_id)." 
                AND from_loc_code = ".db_escape($from_loc_code)." 
                AND from_jo_id = ".db_escape($jo_id);
    
        if ($id > 0) {
            $sql .= " AND trans_id = ".db_escape($id);
        }
        
        if ($not_id > 0) {
            $sql .= " AND trans_id <> ".db_escape($not_id);
        }
        
        $result = db_query($sql, "get_budget_to_budget_transfer_total_qty_by_stock_id_job_id");
        return db_result_value($result, 'total_qty');
}

function get_budget_to_budget_transfer_details_by_filters($select='', $stock_id='', $from_jo_id=0, $to_jo_id=0, $type=-1, $trans_no=0, $item_type=0, 
                                                          $from_task=0, $to_task=0, $from_loc_code='', $to_loc_code='', $id=0, $not_id=0, $group_by='', 
                                                          $order_by='', $fetch=true) {
    if (strlen($select) > 0) {
        $sql = "SELECT $select ";
    } else {
        $sql = "SELECT * ";
    }
    
        $sql .= " FROM ".TB_PREF."budget_to_budget_transfer_details  
                  WHERE 1 = 1";

            if (strlen($stock_id) > 0) {
                $sql .= " AND stock_id = ".db_escape($stock_id);
            }
            if ($from_jo_id > 0) {
                $sql .= " AND from_jo_id = ".db_escape($from_jo_id);
            }
            if ($to_jo_id > 0) {
                $sql .= " AND to_jo_id = ".db_escape($to_jo_id);
            }
            if ($type > -1) {
                $sql .= " AND type = ".db_escape($type);
            }
            if ($trans_no > 0) {
                $sql .= " AND trans_no = ".db_escape($trans_no);
            }
            if ($item_type > 0) {
                $sql .= " AND item_type = ".db_escape($item_type);
            }
            if ($from_task > 0) {
                $sql .= " AND from_task_id = ".db_escape($from_task);
            }
            if ($to_task > 0) {
                $sql .= " AND to_task_id = ".db_escape($to_task);
            }
            if (strlen($from_loc_code) > 0) {
                $sql .= " AND from_loc_code = ".db_escape($from_loc_code);
            }
            if (strlen($to_loc_code) > 0) {
                $sql .= " AND to_loc_code = ".db_escape($to_loc_code);
            }
            if ($id > 0) {
                $sql .= " AND trans_id = ".db_escape($id);
            }
            if ($not_id > 0) {
                $sql .= " AND trans_id <> ".db_escape($not_id);
            }

            if (strlen($group_by) > 0) {
                $sql .= " GROUP BY $group_by";
            }
            if (strlen($order_by) > 0) {
                $sql .= " ORDER BY $order_by";
            }

            $result = db_query($sql, "get_budget_to_budget_transfer_details_by_filters");
                if ($fetch === true) {
                    return db_fetch($result);
                } else {
                    return $result;
                }
}

?>