<?php

function dashboard_get_sql_for_purchasing_data_entry_purchase_orders($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_PURCHORDER)." tran_type, h.order_no tran_no, h.reference, h.ord_date tran_date, h.created_on, h.created_by, 
                DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.ord_date) days,
                u.real_name 
            FROM ".TB_PREF."purch_orders h   
                JOIN ".TB_PREF."users u   
                    ON u.id = h.created_by  
            WHERE h.ord_date >= ".db_escape($from_date)." 
                AND h.ord_date <= ".db_escape($to_date);
    
    if($user_id>0){
        $sql .= " AND h.created_by = ".db_escape($user_id);
    }    
    
    return $sql;
}
 
function dashboard_get_sql_for_purchasing_data_purchase_requisitions($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_PURCH_REQ)." tran_type, h.pr_no tran_no, h.reference, h.pr_date tran_date, h.created_on, h.created_by, 
                DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.pr_date) days,
                u.real_name 
            FROM ".TB_PREF."purchase_requisitions h   
                JOIN ".TB_PREF."users u   
                    ON u.id = h.created_by  
            WHERE h.pr_date >= ".db_escape($from_date)." 
                AND h.pr_date <= ".db_escape($to_date);  
    
    if($user_id>0){
        $sql .= " AND h.created_by = ".db_escape($user_id);
    }       
                
    return $sql;
}

function dashboard_get_sql_for_purchasing_data_entry_grn_batches($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_CUSTDELIVERY)." tran_type, h.id tran_no, h.reference, h.delivery_date tran_date, h.created_on, h.created_by, 
                DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.delivery_date) days,
                u.real_name 
            FROM ".TB_PREF."grn_batch h   
                JOIN ".TB_PREF."users u   
                    ON u.id = h.created_by  
            WHERE h.delivery_date >= ".db_escape($from_date)." 
                AND h.delivery_date <= ".db_escape($to_date);    
    
    if($user_id>0){
        $sql .= " AND h.created_by = ".db_escape($user_id);
    }      
                
    return  $sql;
}

function dashboard_get_purchasing_data_entry_users($from_date, $to_date, $user_id=0) {
    
    $sql_po = dashboard_get_sql_for_purchasing_data_entry_purchase_orders($from_date, $to_date, $user_id);
    $sql_pr = dashboard_get_sql_for_purchasing_data_purchase_requisitions($from_date, $to_date, $user_id);
    $sql_grn_batches = dashboard_get_sql_for_purchasing_data_entry_grn_batches($from_date, $to_date, $user_id);
    
    $sql = $sql_po 
        ." UNION ".$sql_pr 
        ." UNION ".$sql_grn_batches 
            ;   
    
    $sql = "SELECT DISTINCT  created_by, real_name, tran_type  FROM ($sql) tmp_table ORDER BY real_name, tran_type";
    
    return db_query($sql); 
}

function dashboard_get_purchasing_user_data_entry_period_summary($from_date, $to_date, $user_id, $trans_type) {
    
    switch ($trans_type) {
        case ST_PURCHORDER:
            $sql = dashboard_get_sql_for_purchasing_data_entry_purchase_orders($from_date, $to_date, $user_id);
            break;
        case ST_PURCH_REQ:
            $sql = dashboard_get_sql_for_purchasing_data_purchase_requisitions($from_date, $to_date, $user_id);
            break;
        case ST_CUSTDELIVERY:
            $sql = dashboard_get_sql_for_purchasing_data_entry_grn_batches($from_date, $to_date, $user_id);
            break;

        default:
            $sql = "";
            break;
    }

    
    $sql = "SELECT COUNT(tran_no) tran_count, SUM(days) / COUNT(tran_no)  delay  
            FROM ($sql) tmp_table ";
    
    $result = db_query($sql, "dashboard_get_purchasing_user_data_entry_period_summary"); 
    
    return db_fetch($result);
}

