<?php

function dashboard_get_purchase_requisitions_count($from_date, $to_date, $approval_status_array=null) {
    $sql = "SELECT COUNT(pr_no) count  
            FROM ".TB_PREF."purchase_requisitions 
            WHERE pr_date >= ".db_escape($from_date)." 
                AND pr_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_purchase_requisitions_count");
        return db_result_value($result, 'count');
}

function dashboard_get_purchase_requisitions_not_ordered_count($from_date, $to_date) {
    $ordered_prs = "SELECT DISTINCT prd.requisition_id 
                    FROM ".TB_PREF."purchase_requisitions pr, 
                        ".TB_PREF."purchase_requisition_details prd, 
                        ".TB_PREF."pr_po_details ppd,   
                        ".TB_PREF."purch_order_details pod   
                    WHERE pr.pr_no = prd.requisition_id 
                        AND prd.id = ppd.pr_line_id  
                        AND ppd.po_line_id = pod.po_detail_item   
                        AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                        AND pr.pr_date >= ".db_escape($from_date)." 
                        AND pr.pr_date <= ".db_escape($to_date)." 
                    GROUP BY prd.id       
                    HAVING SUM(pod.quantity_ordered) > 0";
    
            $sql = "SELECT COUNT(DISTINCT pr.pr_no) count 
                    FROM ".TB_PREF."purchase_requisitions pr, 
                        ".TB_PREF."purchase_requisition_details prd 
                    WHERE pr.pr_no = prd.requisition_id 
                        AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                        AND pr.pr_date >= ".db_escape($from_date)." 
                        AND pr.pr_date <= ".db_escape($to_date)." 
                        AND pr.pr_no NOT IN ($ordered_prs)";

            $result = db_query($sql, "dashboard_get_purchase_requisitions_not_ordered_count");
            return db_result_value($result, 'count');
}

function dashboard_get_purchase_requisitions_partially_ordered_count($from_date, $to_date) {
    $partially_ordered_lines_prs_1 = "SELECT DISTINCT prd.requisition_id, prd.quantity, prd.line_complete   
                                        FROM ".TB_PREF."purchase_requisitions pr, 
                                            ".TB_PREF."purchase_requisition_details prd, 
                                            ".TB_PREF."pr_po_details ppd,   
                                            ".TB_PREF."purch_order_details pod   
                                        WHERE pr.pr_no = prd.requisition_id 
                                            AND prd.id = ppd.pr_line_id  
                                            AND ppd.po_line_id = pod.po_detail_item   
                                            AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                                            AND pr.pr_date >= ".db_escape($from_date)." 
                                            AND pr.pr_date <= ".db_escape($to_date)." 
                                        GROUP BY prd.id       
                                        HAVING SUM(pod.quantity_ordered) < prd.quantity ";
    
    $not_ordered_lines_prs = "SELECT DISTINCT prd.requisition_id  
                                FROM ".TB_PREF."purchase_requisitions pr 
                                    JOIN ".TB_PREF."purchase_requisition_details prd
                                        ON pr.pr_no = prd.requisition_id 
                                    LEFT JOIN ".TB_PREF."pr_po_details ppd 
                                        ON prd.id = ppd.pr_line_id 
                                WHERE pr.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date)." 
                                    AND (ppd.po_line_id IS NULL OR ppd.po_line_id = 0)";
                                
    $partially_ordered_lines_prs_2 = "SELECT DISTINCT prd.requisition_id, prd.quantity, prd.line_complete  
                                        FROM ".TB_PREF."purchase_requisitions pr, 
                                            ".TB_PREF."purchase_requisition_details prd, 
                                            ".TB_PREF."pr_po_details ppd,   
                                            ".TB_PREF."purch_order_details pod   
                                        WHERE pr.pr_no = prd.requisition_id 
                                            AND prd.id = ppd.pr_line_id  
                                            AND ppd.po_line_id = pod.po_detail_item   
                                            AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                                            AND pr.pr_date >= ".db_escape($from_date)." 
                                            AND pr.pr_date <= ".db_escape($to_date)." 
                                            AND prd.requisition_id IN ($not_ordered_lines_prs) 
                                        GROUP BY prd.id       
                                        HAVING (SUM(pod.quantity_ordered) >= prd.quantity OR prd.line_complete = 1) ";
    
    
        $partiall_all_sql = "$partially_ordered_lines_prs_1 UNION $partially_ordered_lines_prs_2 ";
        
        $sql = "SELECT COUNT(DISTINCT requisition_id) counted_no FROM ($partiall_all_sql) aaa ";
    
        $result = db_query($sql, "dashboard_get_purchase_requisitions_partially_ordered_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_purchase_requisitions_fully_ordered_count($from_date, $to_date) {
    $not_ordered_prs = "SELECT DISTINCT prd.requisition_id  
                        FROM ".TB_PREF."purchase_requisitions pr 
                            JOIN ".TB_PREF."purchase_requisition_details prd
                                ON pr.pr_no = prd.requisition_id 
                            LEFT JOIN ".TB_PREF."pr_po_details ppd 
                                ON prd.id = ppd.pr_line_id 
                        WHERE pr.approval_status = ".db_escape(AS_APPROVED)." 
                            AND pr.pr_date >= ".db_escape($from_date)." 
                            AND pr.pr_date <= ".db_escape($to_date)." 
                            AND (ppd.po_line_id IS NULL OR ppd.po_line_id = 0)";
                                
    $fully_ordered_prs = "SELECT DISTINCT prd.requisition_id, prd.quantity, prd.line_complete  
                            FROM ".TB_PREF."purchase_requisitions pr, 
                                ".TB_PREF."purchase_requisition_details prd, 
                                ".TB_PREF."pr_po_details ppd,   
                                ".TB_PREF."purch_order_details pod   
                            WHERE pr.pr_no = prd.requisition_id 
                                AND prd.id = ppd.pr_line_id  
                                AND ppd.po_line_id = pod.po_detail_item   
                                AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                                AND pr.pr_date >= ".db_escape($from_date)." 
                                AND pr.pr_date <= ".db_escape($to_date)." 
                                AND prd.requisition_id NOT IN ($not_ordered_prs) 
                            GROUP BY prd.id       
                            HAVING (SUM(pod.quantity_ordered) >= prd.quantity OR prd.line_complete = 1) ";
            
    $sql = "SELECT COUNT(DISTINCT requisition_id) counted_no FROM ($fully_ordered_prs) aaa ";
    
        $result = db_query($sql, "dashboard_get_purchase_requisitions_fully_ordered_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_purchase_requisitions_not_issued_count($from_date, $to_date) {
    $sql = "SELECT COUNT(DISTINCT pr.pr_no) count 
            FROM ".TB_PREF."purchase_requisitions pr, 
                ".TB_PREF."purchase_requisition_details prd 
            WHERE pr.pr_no = prd.requisition_id 
                AND pr.pr_complete = 0 
                AND prd.issued_qty = 0 
                AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                AND pr.pr_date >= ".db_escape($from_date)." 
                AND pr.pr_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "dashboard_get_purchase_requisitions_not_issued_count");
        return db_result_value($result, 'count');
}

function dashboard_get_purchase_requisitions_partially_issued_count($from_date, $to_date) {
    $not_issued_prs = "SELECT DISTINCT prd.requisition_id  
                        FROM ".TB_PREF."purchase_requisitions pr 
                            JOIN ".TB_PREF."purchase_requisition_details prd
                                ON pr.pr_no = prd.requisition_id 
                        WHERE pr.approval_status = ".db_escape(AS_APPROVED)." 
                            AND pr.pr_date >= ".db_escape($from_date)." 
                            AND pr.pr_date <= ".db_escape($to_date)." 
                            AND prd.issued_qty = 0 ";
                                
    $partially_issued_prs_2 = "SELECT DISTINCT prd.requisition_id 
                                FROM ".TB_PREF."purchase_requisitions pr, 
                                    ".TB_PREF."purchase_requisition_details prd 
                                WHERE pr.pr_no = prd.requisition_id                                      
                                    AND pr.pr_complete = 0 
                                    AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date)." 
                                    AND prd.quantity <= prd.issued_qty 
                                    AND prd.requisition_id IN ($not_issued_prs)";
    
    $partially_issued_prs_1 = "SELECT DISTINCT prd.requisition_id 
                                FROM ".TB_PREF."purchase_requisitions pr, 
                                    ".TB_PREF."purchase_requisition_details prd 
                                WHERE pr.pr_no = prd.requisition_id                                       
                                    AND pr.pr_complete = 0 
                                    AND pr.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND prd.issued_qty > 0 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date);
    
        $partiall_all_sql = "$partially_issued_prs_1 UNION $partially_issued_prs_2 ";
        
        $sql = "SELECT COUNT(DISTINCT requisition_id) counted_no FROM ($partiall_all_sql) aaa ";
    
        $result = db_query($sql, "dashboard_get_purchase_requisitions_partially_ordered_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_purchase_requisitions_fully_issued_count($from_date, $to_date) {
    $not_issued_prs = "SELECT DISTINCT prd.requisition_id  
                        FROM ".TB_PREF."purchase_requisitions pr 
                            JOIN ".TB_PREF."purchase_requisition_details prd
                                ON pr.pr_no = prd.requisition_id 
                        WHERE pr.pr_no = prd.requisition_id 
                            AND pr.pr_date >= ".db_escape($from_date)." 
                            AND pr.pr_date <= ".db_escape($to_date)." 
                            AND (prd.issued_qty = 0 OR prd.issued_qty < prd.quantity)";
                                
    $fully_ordered_prs = "SELECT COUNT(DISTINCT prd.requisition_id) count 
                            FROM ".TB_PREF."purchase_requisitions pr, 
                                ".TB_PREF."purchase_requisition_details prd 
                            WHERE pr.pr_no = prd.requisition_id 
                                AND pr.pr_no = prd.requisition_id 
                                AND pr.pr_date >= ".db_escape($from_date)." 
                                AND pr.pr_date <= ".db_escape($to_date)." 
                                AND prd.quantity <= prd.issued_qty 
                                AND prd.requisition_id NOT IN ($not_issued_prs)";
    
            $result = db_query($fully_ordered_prs, "dashboard_get_purchase_requisitions_fully_issued_count");
            return db_result_value($result, 'count');
}

function dashboard_get_purchase_requisitions_partially_issued_and_closed_count($from_date, $to_date) {
    $not_issued_prs = "SELECT DISTINCT prd.requisition_id  
                        FROM ".TB_PREF."purchase_requisitions pr 
                            JOIN ".TB_PREF."purchase_requisition_details prd
                                ON pr.pr_no = prd.requisition_id 
                        WHERE pr.approval_status = ".db_escape(AS_CLOSED)." 
                            AND pr.pr_date >= ".db_escape($from_date)." 
                            AND pr.pr_date <= ".db_escape($to_date)." 
                            AND prd.issued_qty = 0 ";
                                
    $partially_issued_prs_2 = "SELECT DISTINCT prd.requisition_id 
                                FROM ".TB_PREF."purchase_requisitions pr, 
                                    ".TB_PREF."purchase_requisition_details prd 
                                WHERE pr.pr_no = prd.requisition_id                                       
                                    AND pr.pr_complete = 0 
                                    AND pr.approval_status = ".db_escape(AS_CLOSED)." 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date)." 
                                    AND prd.quantity <= prd.issued_qty 
                                    AND prd.requisition_id IN ($not_issued_prs)";
    
    $partially_issued_prs_1 = "SELECT DISTINCT prd.requisition_id 
                                FROM ".TB_PREF."purchase_requisitions pr, 
                                    ".TB_PREF."purchase_requisition_details prd 
                                WHERE pr.pr_no = prd.requisition_id                                       
                                    AND pr.pr_complete = 0 
                                    AND pr.approval_status = ".db_escape(AS_CLOSED)." 
                                    AND prd.issued_qty > 0 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date);
    
        $partiall_all_sql = "$partially_issued_prs_1 UNION $partially_issued_prs_2 ";
        
        $sql = "SELECT COUNT(DISTINCT requisition_id) counted_no FROM ($partiall_all_sql) aaa ";
    
        $result = db_query($sql, "dashboard_get_purchase_requisitions_partially_ordered_count");
        return db_result_value($result, 'counted_no');
}

function dashboard_get_purchase_requisitions_closed_without_issuing_count($from_date, $to_date) {
    $issued_prs = "SELECT DISTINCT prd.requisition_id  
                    FROM ".TB_PREF."purchase_requisitions pr 
                        JOIN ".TB_PREF."purchase_requisition_details prd
                            ON pr.pr_no = prd.requisition_id 
                    WHERE pr.approval_status = ".db_escape(AS_CLOSED)." 
                        AND pr.pr_date >= ".db_escape($from_date)." 
                        AND pr.pr_date <= ".db_escape($to_date)." 
                        AND prd.issued_qty > 0 ";
                                
    $closed_without_issue_prs = "SELECT COUNT(DISTINCT prd.requisition_id) count  
                                FROM ".TB_PREF."purchase_requisitions pr, 
                                    ".TB_PREF."purchase_requisition_details prd 
                                WHERE pr.pr_no = prd.requisition_id                                       
                                    AND pr.pr_complete = 0 
                                    AND pr.approval_status = ".db_escape(AS_CLOSED)." 
                                    AND pr.pr_date >= ".db_escape($from_date)." 
                                    AND pr.pr_date <= ".db_escape($to_date)." 
                                    AND prd.quantity = 0 
                                    AND prd.requisition_id NOT IN ($issued_prs)";

        $result = db_query($closed_without_issue_prs, "dashboard_get_purchase_requisitions_partially_ordered_count");
        return db_result_value($result, 'count');
}

?>