<?php

function view_dashboard_get_purchase_orders_details($from_date, $to_date, $approval_status_array=null) {
    $sql = "SELECT po.*, sup.supp_name 
            FROM ".TB_PREF."purch_orders po 
                JOIN ".TB_PREF."suppliers sup 
                    ON po.supplier_id = sup.supplier_id 
            WHERE po.ord_date >= ".db_escape($from_date)." 
                AND po.ord_date <= ".db_escape($to_date);
    
        if (is_array($approval_status_array)) {
            $sql .= " AND po.approval_status IN (".implode(',', $approval_status_array).")";
        }
                
        return db_query($sql, "view_dashboard_get_purchase_orders_details");
}

function view_dashboard_get_purchase_orders_not_grned_details($from_date, $to_date) {
    $ordered_prs = "SELECT DISTINCT pod.order_no 
                    FROM ".TB_PREF."purch_orders po, 
                        ".TB_PREF."purch_order_details pod  
                    WHERE po.order_no = pod.order_no 
                        AND po.approval_status = ".db_escape(AS_APPROVED)." 
                        AND po.ord_date >= ".db_escape($from_date)." 
                        AND po.ord_date <= ".db_escape($to_date)." 
                        AND pod.quantity_received > 0";
    
        $sql = "SELECT DISTINCT po.*, sup.supp_name 
                FROM ".TB_PREF."purch_orders po, 
                    ".TB_PREF."purch_order_details pod,
                    ".TB_PREF."suppliers sup   
                WHERE po.order_no = pod.order_no 
                    AND po.supplier_id = sup.supplier_id 
                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date)." 
                    AND po.order_no NOT IN ($ordered_prs)";
    
        return db_query($sql, "view_dashboard_get_purchase_orders_not_grned_details");
}

function view_dashboard_get_purchase_orders_partially_grned_details($from_date, $to_date) {
    $partially_grned_pos_1 = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                                FROM ".TB_PREF."purch_orders po, 
                                    ".TB_PREF."purch_order_details pod  
                                WHERE po.order_no = pod.order_no 
                                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND po.ord_date >= ".db_escape($from_date)." 
                                    AND po.ord_date <= ".db_escape($to_date)." 
                                    AND pod.quantity_received > 0 
                                    AND pod.quantity_received < pod.quantity_ordered ";
    
    $not_grned_pos = "SELECT DISTINCT pod.order_no  
                        FROM ".TB_PREF."purch_orders po 
                            JOIN ".TB_PREF."purch_order_details pod
                                ON po.order_no = pod.order_no 
                        WHERE po.approval_status = ".db_escape(AS_APPROVED)." 
                            AND po.ord_date >= ".db_escape($from_date)." 
                            AND po.ord_date <= ".db_escape($to_date)." 
                            AND pod.quantity_received = 0";
                                
    $partially_grned_pos_2 = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                                FROM ".TB_PREF."purch_orders po, 
                                    ".TB_PREF."purch_order_details pod  
                                WHERE po.order_no = pod.order_no 
                                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND po.ord_date >= ".db_escape($from_date)." 
                                    AND po.ord_date <= ".db_escape($to_date)." 
                                    AND pod.order_no IN ($not_grned_pos) 
                                    AND pod.quantity_received >= pod.quantity_ordered ";
    
        $partiall_all_sql = "$partially_grned_pos_1 UNION $partially_grned_pos_2 ";
        
        $pos_sql = "SELECT DISTINCT order_no FROM ($partiall_all_sql) aaa ";
        
        $sql = "SELECT DISTINCT po.*, sup.supp_name 
                FROM ".TB_PREF."purch_orders po  
                    JOIN ".TB_PREF."suppliers sup 
                        ON po.supplier_id = sup.supplier_id
                WHERE po.order_no IN ($pos_sql)  
                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date);
    
        return db_query($sql, "view_dashboard_get_purchase_orders_partially_grned_details");
}

function view_dashboard_get_purchase_orders_fully_grned_details($from_date, $to_date) {
    $not_grned_pos = "SELECT DISTINCT pod.order_no  
                        FROM ".TB_PREF."purch_orders po 
                            JOIN ".TB_PREF."purch_order_details pod
                                ON po.order_no = pod.order_no 
                        WHERE po.approval_status = ".db_escape(AS_APPROVED)." 
                            AND po.ord_date >= ".db_escape($from_date)." 
                            AND po.ord_date <= ".db_escape($to_date)." 
                            AND pod.quantity_received < pod.quantity_ordered";
                                
    $fully_grned_pos = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                        FROM ".TB_PREF."purch_orders po, 
                            ".TB_PREF."purch_order_details pod  
                        WHERE po.order_no = pod.order_no 
                            AND po.approval_status = ".db_escape(AS_APPROVED)." 
                            AND po.ord_date >= ".db_escape($from_date)." 
                            AND po.ord_date <= ".db_escape($to_date)." 
                            AND pod.order_no NOT IN ($not_grned_pos) 
                            AND pod.quantity_received >= pod.quantity_ordered ";
    
        $pos_sql = "SELECT DISTINCT order_no FROM ($fully_grned_pos) aaa ";
        
        $sql = "SELECT DISTINCT po.*, sup.supp_name 
                FROM ".TB_PREF."purch_orders po  
                    JOIN ".TB_PREF."suppliers sup 
                        ON po.supplier_id = sup.supplier_id
                WHERE po.order_no IN ($pos_sql)  
                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date);
    
        return db_query($sql, "view_dashboard_get_purchase_orders_fully_grned_details");
}

function view_dashboard_get_purchase_orders_not_invoiced_details($from_date, $to_date) {
    $invoiced_pos = "SELECT DISTINCT pod.order_no 
                    FROM ".TB_PREF."purch_orders po, 
                        ".TB_PREF."purch_order_details pod  
                    WHERE po.order_no = pod.order_no 
                        AND po.approval_status = ".db_escape(AS_APPROVED)." 
                        AND po.ord_date >= ".db_escape($from_date)." 
                        AND po.ord_date <= ".db_escape($to_date)." 
                        AND pod.qty_invoiced > 0";
    
            $sql = "SELECT DISTINCT po.*, sup.supp_name 
                    FROM ".TB_PREF."purch_orders po, 
                        ".TB_PREF."purch_order_details pod,
                        ".TB_PREF."suppliers sup  
                    WHERE po.order_no = pod.order_no 
                        AND po.supplier_id = sup.supplier_id
                        AND po.approval_status = ".db_escape(AS_APPROVED)." 
                        AND po.ord_date >= ".db_escape($from_date)." 
                        AND po.ord_date <= ".db_escape($to_date)." 
                        AND po.order_no NOT IN ($invoiced_pos)";
            
        return db_query($sql, "view_dashboard_get_purchase_orders_not_invoiced_details");
}

function view_dashboard_get_purchase_orders_partially_invoiced_details($from_date, $to_date) {
    $partially_invoiced_pos_1 = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                                FROM ".TB_PREF."purch_orders po, 
                                    ".TB_PREF."purch_order_details pod  
                                WHERE po.order_no = pod.order_no 
                                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND po.ord_date >= ".db_escape($from_date)." 
                                    AND po.ord_date <= ".db_escape($to_date)." 
                                    AND pod.qty_invoiced > 0 
                                    AND pod.qty_invoiced < pod.quantity_ordered ";
    
    $partially_invoied_pos = "SELECT DISTINCT pod.order_no  
                                FROM ".TB_PREF."purch_orders po 
                                    JOIN ".TB_PREF."purch_order_details pod
                                        ON po.order_no = pod.order_no 
                                WHERE po.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND po.ord_date >= ".db_escape($from_date)." 
                                    AND po.ord_date <= ".db_escape($to_date)." 
                                    AND pod.qty_invoiced = 0";
                                
    $partially_invoiced_pos_2 = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                                FROM ".TB_PREF."purch_orders po, 
                                    ".TB_PREF."purch_order_details pod  
                                WHERE po.order_no = pod.order_no 
                                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                                    AND po.ord_date >= ".db_escape($from_date)." 
                                    AND po.ord_date <= ".db_escape($to_date)." 
                                    AND pod.order_no IN ($partially_invoied_pos) 
                                    AND pod.qty_invoiced >= pod.quantity_ordered ";
    
        $partiall_all_sql = "$partially_invoiced_pos_1 UNION $partially_invoiced_pos_2 ";
        
        $pos_sql = "SELECT DISTINCT order_no FROM ($partiall_all_sql) aaa ";
        
        $sql = "SELECT DISTINCT po.*, sup.supp_name 
                FROM ".TB_PREF."purch_orders po  
                    JOIN ".TB_PREF."suppliers sup 
                        ON po.supplier_id = sup.supplier_id
                WHERE po.order_no IN ($pos_sql)  
                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date);
    
        return db_query($sql, "view_dashboard_get_purchase_orders_partially_invoiced_details");
}

function view_dashboard_get_purchase_orders_fully_invoiced_details($from_date, $to_date) {
    $not_grned_pos = "SELECT DISTINCT pod.order_no  
                        FROM ".TB_PREF."purch_orders po 
                            JOIN ".TB_PREF."purch_order_details pod
                                ON po.order_no = pod.order_no 
                        WHERE po.approval_status = ".db_escape(AS_APPROVED)." 
                            AND po.ord_date >= ".db_escape($from_date)." 
                            AND po.ord_date <= ".db_escape($to_date)." 
                            AND pod.qty_invoiced < pod.quantity_ordered";
                                
    $fully_grned_pos = "SELECT DISTINCT pod.order_no, pod.quantity_ordered 
                        FROM ".TB_PREF."purch_orders po, 
                            ".TB_PREF."purch_order_details pod  
                        WHERE po.order_no = pod.order_no 
                            AND po.approval_status = ".db_escape(AS_APPROVED)." 
                            AND po.ord_date >= ".db_escape($from_date)." 
                            AND po.ord_date <= ".db_escape($to_date)." 
                            AND pod.order_no NOT IN ($not_grned_pos) 
                            AND pod.qty_invoiced >= pod.quantity_ordered ";
    
        $pos_sql = "SELECT DISTINCT order_no FROM ($fully_grned_pos) aaa ";
        
        $sql = "SELECT DISTINCT po.*, sup.supp_name 
                FROM ".TB_PREF."purch_orders po  
                    JOIN ".TB_PREF."suppliers sup 
                        ON po.supplier_id = sup.supplier_id
                WHERE po.order_no IN ($pos_sql)  
                    AND po.approval_status = ".db_escape(AS_APPROVED)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date);
        
        return db_query($sql, "view_dashboard_get_purchase_orders_fully_invoiced_details");
}

?>