<?php

function get_sales_order_details_outstanding_qty($stock_code, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));

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

function get_purchase_requisition_details_outstanding_qty($item_code, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));

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

function get_stock_adjustment_issue_outstanding_qty($stock_id, $loc_code, $date_, $type) {
    $sql = "SELECT SUM(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(date2sql($date_));
    
        $result = db_query($sql, "get_stock_adjustment_issue_outstanding_qty");
        return db_result_value($result, 'total_qty');
}

function get_material_transfer_requisition_details_not_issued_qty($stock_id, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));
    
        $result = db_query($sql, "get_material_transfer_requisition_details_not_issued_qty");
        return db_result_value($result, 'total_qty');
}

function get_material_requisition_details_outstanding_qty($stock_id, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));
    
        $result = db_query($sql, "get_material_requisition_details_outstanding_qty");
        return db_result_value($result, 'total_qty');
}

function get_purchase_order_details_outstanding_qty($stock_id, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));
    
        $result = db_query($sql, "get_purchase_order_details_outstanding_qty");
        return db_result_value($result, 'total_qty');
}

function get_material_transfer_requisition_details_not_received_qty($stock_id, $loc_code, $date_) {
    $sql = "SELECT SUM(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(date2sql($date_));
    
        $result = db_query($sql, "get_material_transfer_requisition_details_not_received_qty");
        return db_result_value($result, 'total_qty');
}

?>