<?php

function dashboard_get_material_requisitions_count($from_date, $to_date, $approval_status_array=null) {
    $sql = "SELECT COUNT(mr_no) count  
            FROM ".TB_PREF."material_requisitions 
            WHERE mr_date >= ".db_escape($from_date)." 
                AND mr_date <= ".db_escape($to_date);
    
        if (is_array($approval_status_array)) {
            $sql .= " AND approval_status IN (".implode(',', $approval_status_array).")";
        }
                
        $result = db_query($sql, "dashboard_get_material_requisitions_count");
        return db_result_value($result, 'count');
}

function dashboard_get_material_requisitions_not_issued_count($from_date, $to_date) {
    $sql = "SELECT COUNT(DISTINCT mr.mr_no) count 
            FROM ".TB_PREF."material_requisitions mr, 
                ".TB_PREF."material_requisition_details mrd 
            WHERE mr.mr_no = mrd.mr_no 
                AND mrd.issued_qty = 0 
                AND mr.approval_status = ".db_escape(AS_APPROVED)." 
                AND mr.mr_date >= ".db_escape($from_date)." 
                AND mr.mr_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "dashboard_get_material_requisitions_not_issued_count");
        return db_result_value($result, 'count');
}

function dashboard_get_material_requisitions_partially_issued_count($from_date, $to_date) {
    $partially_issued_mrs_1 = "SELECT DISTINCT mrd.mr_no 
                                FROM ".TB_PREF."material_requisitions mr, 
                                    ".TB_PREF."material_requisition_details mrd  
                                WHERE mr.mr_no = mrd.mr_no 
                                    AND mr.mr_date >= ".db_escape($from_date)." 
                                    AND mr.mr_date <= ".db_escape($to_date)." 
                                    AND mrd.issued_qty > 0 
                                    AND mrd.issued_qty < mrd.requested_qty ";
    
    $not_issued_mrs = "SELECT DISTINCT mrd.mr_no  
                        FROM ".TB_PREF."material_requisitions mr 
                            JOIN ".TB_PREF."material_requisition_details mrd
                                ON mr.mr_no = mrd.mr_no 
                            AND mr.mr_date >= ".db_escape($from_date)." 
                            AND mr.mr_date <= ".db_escape($to_date)." 
                            AND mrd.issued_qty = 0";
                                
    $partially_issued_mrs_2 = "SELECT DISTINCT mrd.mr_no 
                                FROM ".TB_PREF."material_requisitions mr, 
                                    ".TB_PREF."material_requisition_details mrd  
                                WHERE mr.mr_no = mrd.mr_no 
                                    AND mr.mr_date >= ".db_escape($from_date)." 
                                    AND mr.mr_date <= ".db_escape($to_date)." 
                                    AND mrd.mr_no IN ($not_issued_mrs) 
                                    AND (mrd.issued_qty >= mrd.requested_qty OR mrd.line_complete = 1) ";
    
        $partiall_all_sql = "$partially_issued_mrs_1 UNION $partially_issued_mrs_2 ";
        
        $sql = "SELECT COUNT(DISTINCT mr_no) counted_no FROM ($partiall_all_sql) aaa ";
    
        $result = db_query($sql, "dashboard_get_material_requisitions_partially_issued_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_material_requisitions_fully_issued_count($from_date, $to_date) {
    $not_issued_mrs = "SELECT DISTINCT mrd.mr_no  
                        FROM ".TB_PREF."material_requisitions mr 
                            JOIN ".TB_PREF."material_requisition_details mrd
                                ON mr.mr_no = mrd.mr_no 
                            AND mr.mr_date >= ".db_escape($from_date)." 
                            AND mr.mr_date <= ".db_escape($to_date)." 
                            AND mrd.issued_qty < mrd.requested_qty";
                                
    $fully_issued_mrs = "SELECT COUNT(DISTINCT mrd.mr_no) count 
                        FROM ".TB_PREF."material_requisitions mr, 
                            ".TB_PREF."material_requisition_details mrd  
                        WHERE mr.mr_no = mrd.mr_no 
                            AND mr.mr_date >= ".db_escape($from_date)." 
                            AND mr.mr_date <= ".db_escape($to_date)." 
                            AND mrd.mr_no NOT IN ($not_issued_mrs) 
                            AND (mrd.issued_qty >= mrd.requested_qty  OR mrd.line_complete = 1) ";
    
        $result = db_query($fully_issued_mrs, "dashboard_get_material_requisitions_partially_issued_count");
        return db_result_value($result, 'count');
}

function dashboard_get_material_requisitions_partially_issued_and_closed_count($from_date, $to_date) {
    $partially_issued_mrs_1 = "SELECT DISTINCT mrd.mr_no 
                                FROM ".TB_PREF."material_requisitions mr, 
                                    ".TB_PREF."material_requisition_details mrd  
                                WHERE mr.mr_no = mrd.mr_no 
                                    AND mr.approval_status = ".db_escape(AS_CLOSED)." 
                                    AND mr.mr_date >= ".db_escape($from_date)." 
                                    AND mr.mr_date <= ".db_escape($to_date)." 
                                    AND mrd.issued_qty > 0 
                                    AND mrd.issued_qty < mrd.requested_qty ";
    
    $not_issued_mrs = "SELECT DISTINCT mrd.mr_no  
                        FROM ".TB_PREF."material_requisitions mr 
                            JOIN ".TB_PREF."material_requisition_details mrd
                                ON mr.mr_no = mrd.mr_no 
                        WHERE mr.approval_status = ".db_escape(AS_CLOSED)." 
                            AND mr.mr_date >= ".db_escape($from_date)." 
                            AND mr.mr_date <= ".db_escape($to_date)." 
                            AND mrd.issued_qty = 0";
                                
    $partially_issued_mrs_2 = "SELECT DISTINCT mrd.mr_no 
                                FROM ".TB_PREF."material_requisitions mr, 
                                    ".TB_PREF."material_requisition_details mrd  
                                WHERE mr.mr_no = mrd.mr_no 
                                    AND mr.approval_status = ".db_escape(AS_CLOSED)." 
                                    AND mr.mr_date >= ".db_escape($from_date)." 
                                    AND mr.mr_date <= ".db_escape($to_date)." 
                                    AND mrd.mr_no IN ($not_issued_mrs) 
                                    AND (mrd.issued_qty >= mrd.requested_qty OR mrd.line_complete = 1) ";
    
        $partiall_all_sql = "$partially_issued_mrs_1 UNION $partially_issued_mrs_2 ";
        
        $sql = "SELECT COUNT(DISTINCT mr_no) counted_no FROM ($partiall_all_sql) aaa ";
    
        $result = db_query($sql, "dashboard_get_material_requisitions_partially_issued_and_closed_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_material_requisitions_closed_without_issuing_count($from_date, $to_date) {
    $issued_mrs = "SELECT DISTINCT mrd.mr_no 
                    FROM ".TB_PREF."material_requisitions mr, 
                        ".TB_PREF."material_requisition_details mrd  
                    WHERE mr.mr_no = mrd.mr_no 
                        AND mr.approval_status = ".db_escape(AS_CLOSED)." 
                        AND mr.mr_date >= ".db_escape($from_date)." 
                        AND mr.mr_date <= ".db_escape($to_date)." 
                        AND mrd.issued_qty > 0 ";
    
    $sql = "SELECT COUNT(DISTINCT mrd.mr_no) count  
            FROM ".TB_PREF."material_requisitions mr, 
                ".TB_PREF."material_requisition_details mrd  
            WHERE mr.mr_no = mrd.mr_no 
                AND mr.mr_no NOT IN ($issued_mrs)  
                AND mr.approval_status = ".db_escape(AS_CLOSED)." 
                AND mr.mr_date >= ".db_escape($from_date)." 
                AND mr.mr_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "dashboard_get_material_requisitions_closed_without_issuing_count");
        return db_result_value($result, 'count');
}

?>