<?php

function insert_lot_allocation_line($type, $trans_no, $lot_id, $stock_id , $loc_code, $tran_unit, $tran_qty, $convesion_factor, $base_qty, $tran_date, 
                                    $ref_line_id, $source_type, $source_trans_no, $source_line_id) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."lot_allocations
                      ( type, tran_no, lot_id, 
                        stock_id, loc_code, tran_unit, 
                        tran_qty, conversion_factor, qty, 
                        tran_date, ref_line_id,
                        source_type, source_trans_no, source_line_id, 
                        created_by, created_on)        
                VALUE ( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($lot_id).",
                        ".db_escape($stock_id).", ".db_escape($loc_code).", ".db_escape($tran_unit).",  
                        ".db_escape($tran_qty).", ".db_escape($convesion_factor).", ".db_escape($base_qty).", 
                        ".db_escape($tran_date).", ".db_escape($ref_line_id).",      
                        ".db_escape($source_type).", ".db_escape($source_trans_no).", ".db_escape($source_line_id).",      
                        ".db_escape($created_by).", ".db_escape($created_on)." )";      

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

function get_lot_allocation_lines_count($type, $trans_no, $issue_line_id) {
    $sql = "SELECT COUNT(tran_id) lines_count 
            FROM ".TB_PREF."lot_allocations 
            WHERE tran_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type)." 
                AND ref_line_id = ".db_escape($issue_line_id);
    
        $result = db_query($sql, 'get_lot_allocation_lines_count');
        return db_result_value($result, 'lines_count');
}

function get_lot_allocations_total_qty($type=null, $trans_no=0, $ref_line_id=0, $stock_id=null, $loc_code=null, $lot_id=0, $not_id=0, $source_type=-1, 
                                       $source_trans_no=0, $source_line_id=0) {
    $sql = "SELECT SUM(qty) total_qty 
            FROM ".TB_PREF."lot_allocations 
            WHERE 1 = 1 ";
        
        if ($type !== null) {
            $sql .= " AND type = ".db_escape($type);
        }
    
        if ($trans_no > 0) {
            $sql .= " AND tran_no = ".db_escape($trans_no);
        }
        
        if ($ref_line_id > 0) {
            $sql .= " AND ref_line_id = ".db_escape($ref_line_id);
        }
        
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id); 
        }
        
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
        
        if ($lot_id > 0) {
            $sql .= " AND lot_id = ".db_escape($lot_id);
        }
        
        if ($not_id > 0) {
            $sql .= " AND tran_id <> ".db_escape($not_id);
        }
        
        if ($source_type > -1) {
            $sql .= " AND source_type = ".db_escape($source_type);
        }
        
        if ($source_trans_no > 0) {
            $sql .= " AND source_trans_no = ".db_escape($source_trans_no);
        }
        
        if ($source_line_id > 0) {
            $sql .= " AND source_line_id = ".db_escape($source_line_id);
        }
        
        $result = db_query($sql, 'get_lot_allocations_total_qty');
        return db_result_value($result, 'total_qty');
}

function get_location_lot_remain_total_in_base_qty($lot_id, $loc_code=null, $not_id=0) {
    $sql = "SELECT SUM(qty) total_qty 
            FROM ".TB_PREF."lot_moves  
            WHERE lot_id = ".db_escape($lot_id);
    
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
        
        if ($not_id > 0) {
            $sql .= " AND tran_id <> ".db_escape($not_id);
        }
        
        $result = db_query($sql, 'get_location_lot_remain_total_in_base_qty');
        return db_result_value($result, 'total_qty');
}

function get_location_lot_remain_qty_on_lot_moves_allocations($lot_id=0, $loc_code=null, $not_id=0) {
    $sql_1 = "SELECT SUM(qty) total_qty_, lot_id, loc_code, 0 tran_id
                FROM ".TB_PREF."lot_moves  
                WHERE 1 = 1 ";
    
        if ($lot_id > 0) {
            $sql_1 .= " AND lot_id = ".db_escape($lot_id);
        }
    
        if ($loc_code !== null) {
            $sql_1 .= " AND loc_code = ".db_escape($loc_code);
        }
        
    $sql_2 = "SELECT SUM(qty) total_qty_, lot_id, loc_code, tran_id 
                FROM ".TB_PREF."lot_allocations   
                WHERE 1 = 1 ";
    
        if ($lot_id > 0) {
            $sql_2 .= " AND lot_id = ".db_escape($lot_id);
        }
    
        if ($loc_code !== null) {
            $sql_2 .= " AND loc_code = ".db_escape($loc_code);
        }
        
        if ($not_id > 0) {
            $sql_2 .= " AND tran_id <> ".db_escape($not_id);
        }
        
            $sql_all = " ($sql_1) UNION ($sql_2) ";

            $sql = "SELECT SUM(d.total_qty_) total_qty 
                    FROM ( $sql_all ) d 
                    WHERE 1 = 1 ";
            
                if ($lot_id > 0) {
                    $sql .= " AND d.lot_id = ".db_escape($lot_id);
                }

                if ($loc_code !== null) {
                    $sql .= " AND d.loc_code = ".db_escape($loc_code);
                }

                $result = db_query($sql, 'get_location_lot_remain_qty_on_lot_moves_allocations');
                return db_result_value($result, 'total_qty');
}

function delete_lot_allocation_lines($type, $trans_no, $issue_line_id=0) {
    $sql = "DELETE FROM ".TB_PREF."lot_allocations 
            WHERE type = ".db_escape($type)." 
                AND tran_no = ".db_escape($trans_no);
    
        if ($issue_line_id > 0) {
            $sql .= " AND ref_line_id = ".db_escape($issue_line_id);
        }
    
	db_query($sql, "delete_lot_allocation_lines");
        return db_num_affected_rows();
}

function delete_lot_allocation_line_by_id($id) {
    $sql = "DELETE FROM ".TB_PREF."lot_allocations 
            WHERE tran_id = ".db_escape($id);
    
	db_query($sql, "delete_lot_allocation_line_by_id");
        return db_num_affected_rows();
}

function get_lot_allocation_records($type, $trans_no, $ref_line_id=0, $stock_id=null, $loc_code=null, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."lot_allocations 
            WHERE tran_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        if ($ref_line_id > 0) {
            $sql .= " AND ref_line_id = ".db_escape($ref_line_id);
        }
        
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id); 
        }
        
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
                
        $result = db_query($sql, 'get_lot_allocation_records');
        
        if ($fetch === false) {
            return $result;
        }
        
        return db_fetch($result);
}

function get_lot_allocation_record_by_id($line_id) {
    $sql = "SELECT *
            FROM ".TB_PREF."lot_allocations 
            WHERE tran_id = ".db_escape($line_id);
    
	$result = db_query($sql, "get_lot_allocation_record_by_id");
        return db_fetch($result);
}

function get_lot_allocation_lines_details($type, $trans_no, $ref_line_id, $stock_id, $loc_code) {
    $sql = "SELECT l.lot_reference, lm.lot_id, lm.loc_code, lm.stock_id, SUM(lm.qty) total_lot_remain_base_qty, 
                am.description stock_description, am.stock_control_method, un.name unit_name, lc.location_name,
                la.tran_id jmi_ls_id, la.tran_qty, la.tran_unit, la.qty  
            FROM ".TB_PREF."lot_moves lm
                JOIN ".TB_PREF."lots l 
                    ON lm.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON lm.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON lm.loc_code = lc.loc_code 
                JOIN ".TB_PREF."lot_allocations la 
                    ON lm.lot_id = la.lot_id  
                        AND lm.loc_code = la.loc_code  
                        AND lm.stock_id = la.stock_id  
                        AND la.type = ".db_escape($type)." 
                        AND la.tran_no = ".db_escape($trans_no)." 
                        AND la.ref_line_id = ".db_escape($ref_line_id)." 
                JOIN ".TB_PREF."item_units un  
                    ON la.tran_unit = un.abbr  
            WHERE lm.stock_id = ".db_escape($stock_id)." 
                AND lm.loc_code = ".db_escape($loc_code)." 
            GROUP BY lm.loc_code, lm.lot_id 
            ORDER BY lm.lot_id";

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

function get_lot_moves_total_qty($type, $trans_no, $ref_line_id=0, $stock_id=null, $loc_code=null) {
    $sql = "SELECT SUM(qty) total_qty 
            FROM ".TB_PREF."lot_moves  
            WHERE tran_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        if ($ref_line_id > 0) {
            $sql .= " AND ref_line_id = ".db_escape($ref_line_id);
        }
        
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id); 
        }
        
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
        
        $result = db_query($sql, 'get_lot_moves_total_qty');
        return db_result_value($result, 'total_qty');
}

function get_lot_move_lines_details($type, $trans_no, $ref_line_id, $stock_id, $loc_code) {
    $sql = "SELECT l.lot_reference, lm.lot_id, lm.loc_code, lm.stock_id, 
                am.description stock_description, am.stock_control_method, un.name unit_name, lc.location_name,
                lm.tran_id, lm.tran_qty, lm.tran_unit, lm.qty  
            FROM ".TB_PREF."lot_moves lm
                JOIN ".TB_PREF."lots l 
                    ON lm.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON lm.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON lm.loc_code = lc.loc_code 
                JOIN ".TB_PREF."item_units un  
                    ON lm.tran_unit = un.abbr  
            WHERE lm.stock_id = ".db_escape($stock_id)." 
                AND lm.loc_code = ".db_escape($loc_code)." 
                AND lm.type = ".db_escape($type)." 
                AND lm.tran_no = ".db_escape($trans_no)." 
                AND lm.ref_line_id = ".db_escape($ref_line_id)." 
            GROUP BY lm.loc_code, lm.lot_id 
            ORDER BY lm.lot_id";

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

function get_lot_balance_records_by_filters($id=0, $lot_id=0, $loc_code=null, $stock_id=null, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."lot_balances   
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND tran_id = ".db_escape($id);
        }
        
        if ($lot_id > 0) {
            $sql .= " AND lot_id = ".db_escape($lot_id);
        }
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
        
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id); 
        }
        
        $result = db_query($sql, 'get_lot_balance_records_by_filters');
        
        if ($fetch === false) {
            return $result;
        } else {
            return db_fetch($result);
        }
}

function get_lot_balances_details_by_filters($id=0, $lot_id=0, $loc_code=null, $stock_id=null, $fetch=false) {
    $sql = "SELECT lb.*, l.lot_reference, 
                am.description stock_description, am.stock_control_method, lc.location_name 
            FROM ".TB_PREF."lot_balances lb 
                JOIN ".TB_PREF."lots l 
                    ON lb.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON lb.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON lb.loc_code = lc.loc_code 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND lb.tran_id = ".db_escape($id);
        }
        
        if ($lot_id > 0) {
            $sql .= " AND lb.lot_id = ".db_escape($lot_id);
        }
        if ($loc_code !== null) {
            $sql .= " AND lb.loc_code = ".db_escape($loc_code);
        }
        
        if ($stock_id !== null) {
            $sql .= " AND lb.stock_id = ".db_escape($stock_id); 
        }
        
        $result = db_query($sql, 'get_lot_balance_details_by_filters');
        
        if ($fetch === false) {
            return $result;
        } else {
            return db_fetch($result);
        }
}

function get_unique_lots_with_locations_from_lot_moves() {
    $sql = "SELECT DISTINCT lot_id, loc_code 
            FROM ".TB_PREF."lot_moves";
    
        return db_query($sql, 'get_unique_lots_with_locations_from_lot_moves');
}

function get_unique_lots_with_locations_from_lot_allocations($type=null, $trans_no=0, $lot_id=0, $stock_id=null, $loc_code=null) {
    $sql = "SELECT DISTINCT lot_id, loc_code 
            FROM ".TB_PREF."lot_allocations 
            WHERE 1 = 1 ";
    
        if ($type !== null) {
            $sql .= " AND type = ".db_escape($type);
        }
    
        if ($trans_no > 0) {
            $sql .= " AND tran_no = ".db_escape($trans_no);
        }
    
        if ($lot_id > 0) {
            $sql .= " AND lot_id = ".db_escape($lot_id);
        }
    
        if ($stock_id !== null) {
            $sql .= " AND stock_id = ".db_escape($stock_id);
        }
    
        if ($loc_code !== null) {
            $sql .= " AND loc_code = ".db_escape($loc_code);
        }
    
        $sql .= " ORDER BY lot_id, loc_code";
        
        return db_query($sql, 'get_unique_lots_with_locations_from_lot_allocations');
}

function insert_lot_balance($lot_id, $loc_code, $stock_id, $used_qty, $allocated_qty) {
    $sql = "INSERT INTO ".TB_PREF."lot_balances 
                  ( lot_id, loc_code, stock_id, 
                    used, allocated)         
            VALUES( ".db_escape($lot_id).", ".db_escape($loc_code).", ".db_escape($stock_id).", 
                    ".db_escape($used_qty).", ".db_escape($allocated_qty).")";

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

function update_lot_balance($tran_id, $used_qty, $allocated_qty, $row_state) {  
    $sql = "UPDATE ".TB_PREF."lot_balances 
            SET used = ".db_escape($used_qty).",
                allocated = ".db_escape($allocated_qty).", 
                row_state = ".db_new_row_state($row_state)."     
            WHERE tran_id = ".db_escape($tran_id);    

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

function lot_get_budget_to_budget_transfer_lots($from_jo_id, $to_jo_id, $type, $trans_no, $ref_line_id, $stock_id) {
    $in_array = array(ST_DIRECT_PURCHASE_RECEIPT, ST_DIRECT_PURCHASE_RETURN);
    
        $sql = "SELECT SUM(lm.qty) dp_rcp_rtn_qty, lm.lot_id, lm.stock_id, l.lot_reference, am.description stock_description, am.stock_control_method, 
                    lc.location_name, un.name unit_name, la.tran_id, la.tran_qty, la.tran_unit, la.qty, la.conversion_factor 
                FROM ".TB_PREF."lot_moves lm
                    JOIN ".TB_PREF."quality_check_details dpr_d 
                        ON lm.type = dpr_d.type
                            AND lm.tran_no = dpr_d.trans_no
                            AND lm.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."lots l 
                        ON lm.lot_id = l.id 
                    JOIN ".TB_PREF."stock_master am 
                        ON lm.stock_id = am.stock_id 
                    JOIN ".TB_PREF."locations lc 
                        ON lm.loc_code = lc.loc_code 
                    LEFT JOIN ".TB_PREF."lot_allocations la 
                        ON lm.lot_id = la.lot_id 
                            AND la.type = ".db_escape($type)."
                            AND la.tran_no = ".db_escape($trans_no)."
                            AND la.ref_line_id = ".db_escape($ref_line_id)."
                    LEFT JOIN ".TB_PREF."item_units un  
                        ON la.tran_unit = un.abbr  
                WHERE lm.stock_id = ".db_escape($stock_id)." 
                    AND dpr.jo_id = ".db_escape($from_jo_id)."             
                    AND dpr.jo_id <> ".db_escape($to_jo_id)."             
                    AND lm.type IN (".implode(',', $in_array).") 
                GROUP BY lm.loc_code, lm.lot_id 
                HAVING la.tran_id > 0 OR dp_rcp_rtn_qty > 0 
                ORDER BY lm.lot_id";
        
        return db_query($sql, "lot_get_budget_to_budget_transfer_lots");
}

function lot_get_dp_receipt_return_lot_qty($stock_id, $jo_id, $not_jo_id=0, $lot_id=0) {
    $in_array = array(ST_DIRECT_PURCHASE_RECEIPT, ST_DIRECT_PURCHASE_RETURN);
    
        $sql = "SELECT SUM(lm.qty) dp_rep_rtn_qty  
                FROM ".TB_PREF."lot_moves lm
                    JOIN ".TB_PREF."quality_check_details dpr_d 
                        ON lm.type = dpr_d.type
                            AND lm.tran_no = dpr_d.trans_no
                            AND lm.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 lm.stock_id = ".db_escape($stock_id)." 
                    AND dpr.jo_id = ".db_escape($jo_id)."             
                    AND lm.type IN (".implode(',', $in_array).")";
        
            if ($not_jo_id > 0) {
                $sql .= " AND dpr.jo_id <> ".db_escape($not_jo_id);
            }
            
            if ($lot_id > 0) {
                $sql .= " AND lm.lot_id = ".db_escape($lot_id);
            }
            
            $sql .=" GROUP BY lm.loc_code, lm.lot_id 
                    ORDER BY lm.lot_id";
        
            $result = db_query($sql, "lot_get_dp_receipt_return_lot_qty");
            return db_result_value($result, 'dp_rep_rtn_qty');
}

function lot_get_jm_issue_return_lot_qty($stock_id, $jo_id, $not_jo_id=0, $lot_id=0) {
    $in_array = array(ST_JOB_MATERIAL_ISSUE, ST_JOB_MATERIAL_RETURN);
    
        $sql = "SELECT SUM(lm.qty) jm_iss_rtn_qty  
                FROM ".TB_PREF."lot_moves lm
                    JOIN ".TB_PREF."job_material_issue_details jmi_d 
                        ON lm.type = jmi_d.type
                            AND lm.tran_no = jmi_d.trans_no
                            AND lm.stock_id = jmi_d.stock_id
                    JOIN ".TB_PREF."job_material_issues jmi  
                        ON jmi_d.type = jmi.type
                            AND jmi_d.trans_no = jmi.trans_no 
                WHERE lm.stock_id = ".db_escape($stock_id)." 
                    AND jmi.jo_id = ".db_escape($jo_id)."             
                    AND lm.type IN (".implode(',', $in_array).")";
        
            if ($not_jo_id > 0) {
                $sql .= " AND jmi.jo_id <> ".db_escape($not_jo_id);
            }
            
            if ($lot_id > 0) {
                $sql .= " AND lm.lot_id = ".db_escape($lot_id);
            }
            
            $sql .= " GROUP BY lm.loc_code, lm.lot_id 
                    ORDER BY lm.lot_id";
        
            $result = db_query($sql, "lot_get_jm_issue_return_lot_qty");
            return db_result_value($result, 'jm_iss_rtn_qty');
}

function lot_get_jm_issue_lot_alloc_qty($stock_id, $jo_id, $lot_id=0) {
    $in_array = array(ST_JOB_MATERIAL_ISSUE);
    
        $sql = "SELECT SUM(la.qty) jm_iss_rtn_qty  
                FROM ".TB_PREF."lot_allocations la
                    JOIN ".TB_PREF."job_material_issue_details jmi_d 
                        ON la.type = jmi_d.type
                            AND la.tran_no = jmi_d.trans_no
                            AND la.stock_id = jmi_d.stock_id
                    JOIN ".TB_PREF."job_material_issues jmi  
                        ON jmi_d.type = jmi.type
                            AND jmi_d.trans_no = jmi.trans_no 
                WHERE la.stock_id = ".db_escape($stock_id)." 
                    AND jmi.jo_id = ".db_escape($jo_id)."             
                    AND la.type IN (".implode(',', $in_array).")";
        
            if ($lot_id > 0) {
                $sql .= " AND la.lot_id = ".db_escape($lot_id);
            }
        
            $sql .= " GROUP BY la.loc_code, la.lot_id 
                    ORDER BY la.lot_id";
        
        $result = db_query($sql, "lot_get_jm_issue_lot_alloc_qty");
        return db_result_value($result, 'jm_iss_rtn_qty');
}

function lot_get_total_from_job_b_to_b_transfer_lot_qty($stock_id, $from_jo_id, $lot_id=0) {
    $in_array = array(ST_BUDGET_TO_BUDGET_TRANSFER);
    
        $sql = "SELECT SUM(lm.qty) total_btbt_qty  
                FROM ".TB_PREF."lot_moves lm 
                    JOIN ".TB_PREF."budget_to_budget_transfer_details btbt_d 
                        ON lm.type = btbt_d.type
                            AND lm.tran_no = btbt_d.trans_no
                            AND lm.stock_id = btbt_d.stock_id
                    JOIN ".TB_PREF."budget_to_budget_transfers btbt  
                        ON btbt_d.type = btbt.type
                            AND btbt_d.trans_no = btbt.trans_no 
                WHERE lm.stock_id = ".db_escape($stock_id)." 
                    AND lm.type IN (".implode(',', $in_array).") 
                    AND btbt_d.from_jo_id = ".db_escape($from_jo_id);
        
            if ($lot_id > 0) {
                $sql .= " AND lm.lot_id = ".db_escape($lot_id);
            }
        
            $sql .= " GROUP BY lm.loc_code, lm.lot_id 
                    ORDER BY lm.lot_id";
        
        $result = db_query($sql, "lot_get_total_from_job_b_to_b_transfer_lot_qty");
        return db_result_value($result, 'total_btbt_qty');
}

function lot_get_total_from_job_b_to_b_transfer_lot_alloc_qty($stock_id, $from_jo_id, $lot_id=0, $line_id=0, $not_line_id=0) {
    $in_array = array(ST_BUDGET_TO_BUDGET_TRANSFER);
    
        $sql = "SELECT SUM(la.qty) total_btbt_qty  
                FROM ".TB_PREF."lot_allocations la 
                    JOIN ".TB_PREF."budget_to_budget_transfer_details btbt_d 
                        ON la.type = btbt_d.type
                            AND la.tran_no = btbt_d.trans_no
                            AND la.stock_id = btbt_d.stock_id
                            AND la.ref_line_id = btbt_d.trans_id 
                    JOIN ".TB_PREF."budget_to_budget_transfers btbt  
                        ON btbt_d.type = btbt.type
                            AND btbt_d.trans_no = btbt.trans_no 
                WHERE la.stock_id = ".db_escape($stock_id)." 
                    AND la.type IN (".implode(',', $in_array).") 
                    AND btbt_d.from_jo_id = ".db_escape($from_jo_id);
        
            if ($lot_id > 0) {
                $sql .= " AND la.lot_id = ".db_escape($lot_id);
            }
            if ($line_id > 0) {
                $sql .= " AND btbt_d.trans_id = ".db_escape($line_id);
            }
            if ($not_line_id > 0) {
                $sql .= " AND btbt_d.trans_id <> ".db_escape($not_line_id);
            }
        
            $sql .= " GROUP BY la.loc_code, la.lot_id 
                    ORDER BY la.lot_id";
        
        $result = db_query($sql, "lot_get_total_from_job_b_to_b_transfer_lot_alloc_qty");
        return db_result_value($result, 'total_btbt_qty');
}

function lot_get_total_from_job_jm_issue_b_to_b_transfer_lot_qty($stock_id, $from_jo_id, $lot_id=0, $not_line_id=0) {
    $total_jm_iss_lot_alloc_qty = lot_get_jm_issue_lot_alloc_qty($stock_id, $from_jo_id, $lot_id);
    
    $total_b2b_lot_alloc_qty = lot_get_total_from_job_b_to_b_transfer_lot_alloc_qty($stock_id, $from_jo_id, $lot_id, 0, $not_line_id);
    
        $total = $total_jm_iss_lot_alloc_qty + $total_b2b_lot_alloc_qty;
    
        return $total;
}

function get_lots_selection_edit_details($type, $trans_no, $ref_line_id, $stock_id, $loc_code) {
    $sql = "SELECT bl.*, l.lot_reference, am.description stock_description, am.stock_control_method, lc.location_name,
                un.name unit_name, la.tran_id, la.tran_qty, la.tran_unit, la.qty   
            FROM ".TB_PREF."lot_balances bl 
                JOIN ".TB_PREF."lots l 
                    ON bl.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON bl.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON bl.loc_code = lc.loc_code 
                LEFT JOIN ".TB_PREF."lot_allocations la 
                    ON bl.lot_id = la.lot_id 
                        AND bl.loc_code = la.loc_code 
                        AND la.type = ".db_escape($type)."
                        AND la.tran_no = ".db_escape($trans_no)."
                        AND la.ref_line_id = ".db_escape($ref_line_id)."
                LEFT JOIN ".TB_PREF."item_units un  
                    ON la.tran_unit = un.abbr  
            WHERE bl.stock_id = ".db_escape($stock_id)." 
                AND bl.loc_code = ".db_escape($loc_code)." 
            GROUP BY bl.loc_code, bl.lot_id 
            HAVING (la.tran_id > 0 OR (bl.used + bl.allocated) > 0) 
            ORDER BY bl.lot_id";

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

function lot_get_job_material_issue_detail_record_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT isd.*, rqd.type source_type, rqd.trans_no source_trans_no 
            FROM ".TB_PREF."job_material_issue_details isd 
                JOIN ".TB_PREF."job_material_requisition_details rqd 
                    ON isd.source_line_id = rqd.trans_id 
            WHERE isd.type = ".db_escape($type)." 
                AND isd.trans_no = ".db_escape($trans_no)." 
                AND isd.trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "lot_get_job_material_issue_detail_record_type_trans_no_line_no");
        return db_fetch($result);
}

function lot_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, "lot_get_job_material_issue_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

function lot_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, "lot_get_job_material_issue_header_record_type_trans_no");
        return db_fetch($result);
}

function lot_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, "lot_update_job_material_issue_row_state");
            return db_num_affected_rows();
}

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

function get_return_lot_selections($type, $trans_no, $line_id, $source_line_id, $source_type, $source_trans_no, $stock_id) {
    $sql = "SELECT SUM(lm.qty) remain_qty, lm.*, l.lot_reference, am.description stock_description, am.stock_control_method, 
                lc.location_name, un.name unit_name, la.tran_id id, la.tran_qty, la.tran_unit, la.qty  
            FROM ".TB_PREF."lot_moves lm 
                JOIN ".TB_PREF."lots l 
                    ON lm.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON lm.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON lm.loc_code = lc.loc_code 
                LEFT JOIN ".TB_PREF."lot_allocations la 
                    ON lm.lot_id = la.lot_id 
                        AND lm.loc_code = la.loc_code 
                        AND lm.source_type = la.source_type 
                        AND lm.source_line_id = la.source_line_id
                        AND la.type = ".db_escape($type)."
                        AND la.tran_no = ".db_escape($trans_no)."
                        AND la.ref_line_id = ".db_escape($line_id)."
                LEFT JOIN ".TB_PREF."item_units un  
                    ON la.tran_unit = un.abbr  
            WHERE lm.stock_id = ".db_escape($stock_id)." 
                AND lm.source_type = ".db_escape($source_type)." 
                AND lm.source_trans_no = ".db_escape($source_trans_no)." 
                AND lm.source_line_id = ".db_escape($source_line_id)." 
            GROUP BY lm.lot_id, lm.loc_code, lm.source_line_id 
            HAVING (remain_qty < 0) OR (la.tran_id > 0)
            ORDER BY lm.lot_id";
    
        return db_query($sql, "get_return_lot_selections");
}

function get_dp_return_lot_selections($type, $trans_no, $line_id, $source_line_id, $source_type, $source_trans_no, $stock_id) {
    // grn lots with this lot alloc record
    $sql_0 = "(SELECT SUM(lm.qty) remain_qty_, lm.lot_id, lm.loc_code, lm.source_line_id, l.lot_reference, lm.stock_id, am.description stock_description, 
                    am.stock_control_method, lc.location_name, un.name unit_name, la.tran_id id, la.tran_qty, la.tran_unit, la.qty    
                FROM ".TB_PREF."lot_moves lm 
                    JOIN ".TB_PREF."lots l 
                        ON lm.lot_id = l.id
                    JOIN ".TB_PREF."stock_master am 
                        ON lm.stock_id = am.stock_id 
                    JOIN ".TB_PREF."locations lc 
                        ON lm.loc_code = lc.loc_code 
                    LEFT JOIN ".TB_PREF."lot_allocations la 
                        ON lm.lot_id = la.lot_id 
                            AND lm.loc_code = la.loc_code 
                            AND lm.type = la.source_type 
                            AND lm.ref_line_id = la.source_line_id
                            AND la.type = ".db_escape($type)."
                            AND la.tran_no = ".db_escape($trans_no)."
                            AND la.ref_line_id = ".db_escape($line_id)."
                    LEFT JOIN ".TB_PREF."item_units un  
                        ON la.tran_unit = un.abbr  
                WHERE lm.stock_id = ".db_escape($stock_id)." 
                    AND lm.type = ".db_escape($source_type)." 
                    AND lm.tran_no = ".db_escape($source_trans_no)." 
                    AND lm.ref_line_id = ".db_escape($source_line_id)." 
                GROUP BY lm.lot_id, lm.loc_code, lm.ref_line_id 
                ORDER BY lm.lot_id)";
    
    // returned lots
    $sql_1 = "(SELECT SUM(lm.qty) remain_qty_, lm.lot_id, lm.loc_code, lm.source_line_id, l.lot_reference, lm.stock_id, am.description stock_description, 
                    am.stock_control_method, lc.location_name, '' unit_name, 0 id, 0 tran_qty, '' tran_unit, 0 qty 
                FROM ".TB_PREF."lot_moves lm 
                    JOIN ".TB_PREF."lots l 
                        ON lm.lot_id = l.id
                    JOIN ".TB_PREF."stock_master am 
                        ON lm.stock_id = am.stock_id 
                    JOIN ".TB_PREF."locations lc 
                        ON lm.loc_code = lc.loc_code 
                WHERE lm.stock_id = ".db_escape($stock_id)." 
                    AND lm.source_type = ".db_escape($source_type)." 
                    AND lm.source_trans_no = ".db_escape($source_trans_no)." 
                    AND lm.source_line_id = ".db_escape($source_line_id)." 
                GROUP BY lm.lot_id, lm.loc_code, lm.source_line_id 
                ORDER BY lm.lot_id)";
    
    $sql = "SELECT SUM(al.remain_qty_) remain_qty, al.lot_id, al.loc_code, al.source_line_id, al.stock_id, al.lot_reference, al.stock_description, 
                    al.stock_control_method, al.location_name, al.unit_name, al.id, al.tran_qty, al.tran_unit, al.qty 
            FROM ($sql_0 UNION $sql_1) al 
            WHERE 1 = 1 
            GROUP BY al.lot_id, al.loc_code
            HAVING remain_qty > 0 OR id > 0
            ORDER BY al.lot_id, al.id";
    
        return db_query($sql, "get_dp_return_lot_selections");
}

function get_receipt_lots_selection_edit_details($type, $trans_no, $ref_line_id, $stock_id, $loc_code) {
    $sql = "SELECT bl.*, l.lot_reference, am.description stock_description, am.stock_control_method, lc.location_name,
                un.name unit_name, la.tran_id line_id, la.tran_qty, la.tran_unit, la.qty   
            FROM ".TB_PREF."lot_balances bl 
                JOIN ".TB_PREF."lots l 
                    ON bl.lot_id = l.id 
                JOIN ".TB_PREF."stock_master am 
                    ON bl.stock_id = am.stock_id 
                JOIN ".TB_PREF."locations lc 
                    ON bl.loc_code = lc.loc_code 
                LEFT JOIN ".TB_PREF."lot_allocations la 
                    ON bl.lot_id = la.lot_id 
                        AND bl.loc_code = la.loc_code 
                        AND la.type = ".db_escape($type)."
                        AND la.tran_no = ".db_escape($trans_no)."
                        AND la.ref_line_id = ".db_escape($ref_line_id)."
                LEFT JOIN ".TB_PREF."item_units un  
                    ON la.tran_unit = un.abbr  
            WHERE bl.stock_id = ".db_escape($stock_id)." 
                AND bl.loc_code = ".db_escape($loc_code)." 
            GROUP BY bl.loc_code, bl.lot_id 
            ORDER BY bl.lot_id";

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

function lot_get_quality_check_detail_record_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT d.*, h.loc_code, h.tran_date, rqd.type source_type, rqd.trans_no source_trans_no  
            FROM ".TB_PREF."quality_check_details d 
                JOIN ".TB_PREF."quality_checks h 
                    ON d.trans_no = h.trans_no 
                        AND d.type = h.type  
                JOIN ".TB_PREF."quality_check_details rqd 
                    ON d.source_line_id = rqd.trans_id 
            WHERE d.type = ".db_escape($type)." 
                AND d.trans_no = ".db_escape($trans_no)." 
                AND d.trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "lot_get_quality_check_detail_record_type_trans_no_line_no");
        return db_fetch($result);
}

function lot_get_quality_check_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,
                hd.loc_code,
                lc.location_name 
            FROM ".TB_PREF."quality_check_details ard 
                JOIN ".TB_PREF."quality_checks hd   
                    ON ard.type = hd.type 
                        AND ard.trans_no = hd.trans_no 
                JOIN ".TB_PREF."locations lc 
                    ON hd.loc_code = lc.loc_code         
                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, "lot_get_quality_check_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

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

function lot_update_quality_check_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."quality_checks 
                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, "lot_update_quality_check_row_state");
            return db_num_affected_rows();
}

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

function lot_get_budget_to_budget_transfer_detail_record_type_trans_no_line_no($type, $trans_no, $line_id) {
    $sql = "SELECT bbt_d.*, bbt.to_jo_id, bbt_d.to_loc_code loc_code, '' source_type, 0 source_trans_no, bbt.tran_date, l.location_name,  
                un.name tran_unit_name 
            FROM ".TB_PREF."budget_to_budget_transfer_details bbt_d 
                JOIN ".TB_PREF."budget_to_budget_transfers bbt 
                    ON bbt.type = bbt_d.type 
                        AND bbt.trans_no = bbt_d.trans_no 
                LEFT JOIN ".TB_PREF."locations l 
                    ON bbt.to_loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON bbt_d.tran_unit = un.abbr  
            WHERE bbt_d.type = ".db_escape($type)." 
                AND bbt_d.trans_no = ".db_escape($trans_no)." 
                AND bbt_d.trans_id = ".db_escape($line_id);
    
	$result = db_query($sql, "lot_get_budget_to_budget_transfer_detail_record_type_trans_no_line_no");
        return db_fetch($result);
}

function lot_get_budget_to_budget_transfer_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 from_task_description, 
                pt2.description task_description, 
                l.location_name,  
                ard.to_loc_code loc_code   
            FROM ".TB_PREF."budget_to_budget_transfer_details ard 
                JOIN ".TB_PREF."locations l 
                    ON ard.to_loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."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.from_task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_tasks pt2 
                    ON ard.to_task_id = pt2.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, "lot_get_budget_to_budget_transfer_details_line_by_type_trans_no_line_no");
        return db_fetch($result);
}

function lot_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, "lot_get_budget_to_budget_transfer_header_record_type_trans_no");
        return db_fetch($result);
}

function lot_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, "lot_update_budget_to_budget_transfer_row_state");
            return db_num_affected_rows();
}

function lot_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, "lot_get_budget_to_budget_transfer_details_record");
        return db_fetch($result);
}

?>