<?php

function get_view_sales_order_details($stock_code, $loc_code, $date) {
    $sql = "SELECT so.order_no trans_no, so.trans_type, so.reference, so.ord_date tran_date, 
                (sod.quantity - sod.qty_dispatched) total_qty 
            FROM ".TB_PREF."sales_order_details sod 
                JOIN ".TB_PREF."sales_orders so 
                    ON sod.order_no = so.order_no 
                        AND sod.trans_type = so.trans_type  
            WHERE so.approval_status = ".db_escape(AS_APPROVED)." 
                AND sod.stk_code = ".db_escape($stock_code)." 
                AND so.from_stk_loc = ".db_escape($loc_code)." 
                AND so.ord_date <= ".db_escape($date);

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

function get_view_purchase_requisition_details($item_code, $loc_code, $date) {
    $sql = "SELECT pr.pr_no trans_no, ".ST_PURCH_REQ." trans_type, pr.reference, pr.pr_date tran_date, 
                (prd.quantity - (prd.issued_qty - prd.returned_qty)) total_qty 
            FROM ".TB_PREF."purchase_requisition_details prd 
                JOIN ".TB_PREF."purchase_requisitions pr 
                    ON prd.requisition_id = pr.pr_no  
            WHERE (pr.approval_status = ".db_escape(AS_APPROVED)." OR pr.approval_status = ".db_escape(AS_ISSUED).") 
                AND pr.source_order_id = 0 
                AND pr.pr_complete = 0  
                AND prd.item_code = ".db_escape($item_code)." 
                AND pr.location = ".db_escape($loc_code)." 
                AND pr.pr_date <= ".db_escape($date);

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

function get_view_stock_adjustment_issue_details($stock_id, $loc_code, $date, $type) {
    $sql = "SELECT sa.id trans_no, sa.type trans_type, sa.reference, sad.tran_date, 
                (sad.qty) total_qty 
            FROM ".TB_PREF."stock_adjustment_request_details sad 
                JOIN ".TB_PREF."stock_adjustment_requests sa  
                    ON sad.trans_no = sa.id  
            WHERE sa.approval_status = ".db_escape(AS_APPROVED)." 
                AND sa.type = ".db_escape($type)." 
                AND sad.stock_id = ".db_escape($stock_id)." 
                AND sad.loc_code = ".db_escape($loc_code)." 
                AND sad.tran_date <= ".db_escape($date);
    
        return db_query($sql, "get_view_stock_adjustment_issue_details");
}

function get_view_material_transfer_requisition_details_not_issued($stock_id, $loc_code, $date) {
    $sql = "SELECT mt.mtr_no trans_no, ".ST_TRANSFERREQ." trans_type, mt.reference, mt.mtr_date tran_date, 
                (mtd.requested_qty - mtd.issued_qty) total_qty 
            FROM ".TB_PREF."material_transfer_requisition_details mtd 
                JOIN ".TB_PREF."material_transfer_requisitions mt  
                    ON mtd.mtr_no = mt.mtr_no  
            WHERE (mt.approval_status = ".db_escape(AS_APPROVED)." OR mt.approval_status = ".db_escape(AS_TRANSFERRED).") 
                AND mtd.stock_id = ".db_escape($stock_id)." 
                AND mt.issuing_location = ".db_escape($loc_code)." 
                AND mt.mtr_date <= ".db_escape($date);
    
        return db_query($sql, "get_view_material_transfer_requisition_details_not_issued");
}

function get_view_material_requisition_details($stock_id, $loc_code, $date) {
    $sql = "SELECT mr.mr_no trans_no, ".ST_MATERIAL_REQ." trans_type, mr.reference, mr.mr_date tran_date, 
                (mrd.requested_qty - mrd.issued_qty - mrd.returned_qty) total_qty 
            FROM ".TB_PREF."material_requisition_details mrd 
                JOIN ".TB_PREF."material_requisitions mr  
                    ON mrd.mr_no = mr.mr_no   
            WHERE mr.approval_status = ".db_escape(AS_APPROVED)." 
                AND mrd.stock_id = ".db_escape($stock_id)." 
                AND mr.issuing_location = ".db_escape($loc_code)." 
                AND mr.mr_date <= ".db_escape($date);
    
        return db_query($sql, "get_view_material_requisition_details");
}

function get_view_purchase_order_details($stock_id, $loc_code, $date) {
    $sql = "SELECT po.order_no trans_no, ".ST_PURCHORDER."  trans_type, po.reference, po.ord_date tran_date, 
                (pod.quantity_ordered - pod.quantity_received) total_qty 
            FROM ".TB_PREF."purch_order_details pod 
                JOIN ".TB_PREF."purch_orders po  
                    ON pod.order_no = po.order_no   
            WHERE po.approval_status = ".db_escape(AS_APPROVED)." 
                AND pod.item_code = ".db_escape($stock_id)." 
                AND po.into_stock_location = ".db_escape($loc_code)." 
                AND po.ord_date <= ".db_escape($date)." 
                AND pod.quantity_ordered > pod.quantity_received ";
    
        return db_query($sql, "get_view_purchase_order_details");
}

function get_view_material_transfer_requisition_details_not_received($stock_id, $loc_code, $date) {
    $sql = "SELECT mt.mtr_no trans_no, ".ST_TRANSFERREQ." trans_type, mt.reference, mt.mtr_date tran_date, 
                (mtd.requested_qty - mtd.received_qty) total_qty 
            FROM ".TB_PREF."material_transfer_requisition_details mtd 
                JOIN ".TB_PREF."material_transfer_requisitions mt  
                    ON mtd.mtr_no = mt.mtr_no  
            WHERE (mt.approval_status = ".db_escape(AS_APPROVED)." OR mt.approval_status = ".db_escape(AS_TRANSFERRED).") 
                AND mtd.stock_id = ".db_escape($stock_id)." 
                AND mt.requesting_location = ".db_escape($loc_code)." 
                AND mt.mtr_date <= ".db_escape($date);
    
        return db_query($sql, "get_view_material_transfer_requisition_details_not_received");
}

?>