<?php

function view_dashboard_get_material_requisitions_details($from_date, $to_date, $approval_status_array=null) {
    $sql = "SELECT mr.*, pm.description project_name, SUM(mrd.price) total 
            FROM ".TB_PREF."material_requisitions mr 
                JOIN ".TB_PREF."projects_master pm 
                    ON mr.project_id = pm.project_id 
                JOIN ".TB_PREF."material_requisition_details mrd  
                    ON mr.mr_no = mrd.mr_no 
            WHERE mr.mr_date >= ".db_escape($from_date)." 
                AND mr.mr_date <= ".db_escape($to_date);
    
        if (is_array($approval_status_array)) {
            $sql .= " AND mr.approval_status IN (".implode(',', $approval_status_array).")";
        }
        
        $sql .= " GROUP BY mr.mr_no";
                
        return db_query($sql, "view_dashboard_get_material_requisitions_details");
}

function view_dashboard_get_material_requisitions_not_issued_details($from_date, $to_date) {
    $sql = "SELECT DISTINCT mr.*, pm.description project_name, SUM(mrd.price) total  
            FROM ".TB_PREF."material_requisitions mr, 
                ".TB_PREF."material_requisition_details mrd,
                ".TB_PREF."projects_master pm  
            WHERE mr.mr_no = mrd.mr_no 
                AND mr.project_id = pm.project_id 
                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)." 
            GROUP BY mr.mr_no";

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

function view_dashboard_get_material_requisitions_partially_issued_details($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 ";
    
        $partiall_all_sql = "$partially_issued_mrs_1 UNION $partially_issued_mrs_2 ";
        
        $mrs_sql = "SELECT DISTINCT mr_no FROM ($partiall_all_sql) aaa ";
        
        $sql = "SELECT DISTINCT mr.*, pm.description project_name, SUM(mrd.price) total  
                FROM ".TB_PREF."material_requisitions mr  
                    JOIN ".TB_PREF."projects_master pm 
                        ON mr.project_id = pm.project_id 
                    JOIN ".TB_PREF."material_requisition_details mrd  
                        ON mr.mr_no = mrd.mr_no 
                WHERE mr.mr_no IN ($mrs_sql)  
                    AND mr.mr_date >= ".db_escape($from_date)." 
                    AND mr.mr_date <= ".db_escape($to_date)." 
                GROUP BY mr.mr_no";
    
        return db_query($sql, "view_dashboard_get_material_requisitions_partially_issued_details");
}

function view_dashboard_get_material_requisitions_fully_issued_details($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 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 NOT IN ($not_issued_mrs) 
                            AND mrd.issued_qty >= mrd.requested_qty ";
    
        $sql = "SELECT DISTINCT mr.*, pm.description project_name, SUM(mrd.price) total   
                FROM ".TB_PREF."material_requisitions mr  
                    JOIN ".TB_PREF."projects_master pm 
                        ON mr.project_id = pm.project_id 
                    JOIN ".TB_PREF."material_requisition_details mrd  
                        ON mr.mr_no = mrd.mr_no 
                WHERE mr.mr_no IN ($fully_issued_mrs)  
                    AND mr.mr_date >= ".db_escape($from_date)." 
                    AND mr.mr_date <= ".db_escape($to_date)." 
                GROUP BY mr.mr_no";
    
        return db_query($sql, "view_dashboard_get_material_requisitions_fully_issued_details");
}

function view_dashboard_get_material_requisitions_partially_issued_and_closed_details($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 ";
    
        $partiall_all_sql = "$partially_issued_mrs_1 UNION $partially_issued_mrs_2 ";
        
        $mrs_sql = "SELECT DISTINCT mr_no FROM ($partiall_all_sql) aaa ";
        
        $sql = "SELECT DISTINCT mr.*, pm.description project_name, SUM(mrd.price) total   
                FROM ".TB_PREF."material_requisitions mr  
                    JOIN ".TB_PREF."projects_master pm 
                        ON mr.project_id = pm.project_id 
                    JOIN ".TB_PREF."material_requisition_details mrd  
                        ON mr.mr_no = mrd.mr_no 
                WHERE mr.mr_no IN ($mrs_sql)  
                    AND mr.approval_status = ".db_escape(AS_CLOSED)." 
                    AND mr.mr_date >= ".db_escape($from_date)." 
                    AND mr.mr_date <= ".db_escape($to_date)." 
                GROUP BY mr.mr_no";
            
        return db_query($sql, "view_dashboard_get_material_requisitions_partially_issued_and_closed_details");
}

function view_dashboard_get_material_requisitions_closed_without_issuing_details($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 ";
    
    $mrs_sql = "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_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);
        
        $sql = "SELECT DISTINCT mr.*, pm.description project_name, SUM(mrd.price) total  
                FROM ".TB_PREF."material_requisitions mr  
                    JOIN ".TB_PREF."projects_master pm 
                        ON mr.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."material_requisition_details mrd  
                        ON mr.mr_no = mrd.mr_no 
                WHERE mr.mr_no IN ($mrs_sql)  
                    AND mr.mr_date >= ".db_escape($from_date)." 
                    AND mr.mr_date <= ".db_escape($to_date)." 
                GROUP BY mr.mr_no";
        
        return db_query($sql, "view_dashboard_get_material_requisitions_closed_without_issuing_details");
}

?>