<?php

function dashboard_get_sql_for_petty_cash_data_entry_petty_cash_expenses($from_date, $to_date, $user_id=0) {
    
//    date_format(date, '%a %D %b %Y') 
//            DATEDIFF("2017-05-25", "2017-06-15");  
    
    $sql = "SELECT ".db_escape(ST_PETTY_CASH)." tran_type, h.id tran_no, h.reference, h.tran_date, h.created_on,  h.created_by, 
                    DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.tran_date) days,
                    u.real_name 
            FROM ".TB_PREF."petty_cash_header h   
                JOIN ".TB_PREF."users u   
                    ON u.id = h.created_by  
            WHERE h.tran_date >= ".db_escape($from_date)." 
                AND h.tran_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_petty_cash_data_entry_ious($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_I_OWE_YOU)." tran_type, h.id tran_no, h.reference, h.tran_date, h.created_on,  h.created_by, 
                    DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.tran_date) days,
                    u.real_name 
            FROM ".TB_PREF."i_owe_you h   
                JOIN ".TB_PREF."users u   
                    ON u.id = h.created_by  
            WHERE h.tran_date >= ".db_escape($from_date)." 
                AND h.tran_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_petty_cash_data_entry_iou_batches($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_I_OWE_YOU_BATCH)." tran_type, h.batch_no tran_no, '' reference, h.tran_date, h.created_on,  h.created_by, 
                        DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.tran_date) days,
                        u.real_name 
                FROM ".TB_PREF."i_owe_you_batches h   
                    JOIN ".TB_PREF."users u   
                        ON u.id = h.created_by  
                WHERE h.tran_date >= ".db_escape($from_date)." 
                    AND h.tran_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_petty_cash_data_entry_iou_payments($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_I_OWE_YOU_PAYMENT)." tran_type, h.id tran_no, '' reference, h.payment_date tran_date, h.created_on,  h.created_by, 
                        DATEDIFF(DATE_FORMAT(h.created_on, '%Y-%m-%d'), h.payment_date) days,
                        u.real_name 
                FROM ".TB_PREF."i_owe_you_payments h   
                    JOIN ".TB_PREF."users u   
                        ON u.id = h.created_by  
                WHERE h.payment_date >= ".db_escape($from_date)." 
                    AND h.payment_date <= ".db_escape($to_date)." 
                    AND h.expense_source = ".db_escape(ST_I_OWE_YOU)." 
                    AND h.paid = 1 ";
    if($user_id>0){
        $sql .= " AND h.created_by = ".db_escape($user_id);
    }     
                
    return  $sql;
}

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

function dashboard_get_sql_for_petty_cash_data_entry_iou_bank_payments($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_IOU_BANKPAYMENT)." tran_type, h.trans_no tran_no, h.ref reference, h.trans_date tran_date, a.stamp created_on, a.user created_by, 
                        DATEDIFF(DATE_FORMAT(a.stamp, '%Y-%m-%d'), h.trans_date) days,
                        u.real_name 
                FROM ".TB_PREF."bank_trans h   
                    JOIN ".TB_PREF."audit_trail a    
                        ON a.type = h.type   
                        AND a.trans_no = h.trans_no    
                    JOIN ".TB_PREF."users u   
                        ON u.id = a.user   
                WHERE h.trans_date >= ".db_escape($from_date)." 
                    AND h.trans_date <= ".db_escape($to_date)." 
                    AND h.type = ".db_escape(ST_IOU_BANKPAYMENT);
    if($user_id>0){
        $sql .= " AND a.user = ".db_escape($user_id);
    }       
                
    return  $sql;
}

function dashboard_get_sql_for_petty_cash_data_entry_iou_bank_returns($from_date, $to_date, $user_id=0) {
    
    $sql = "SELECT ".db_escape(ST_IOU_BANKPAYMENT_RETURN)." tran_type, h.trans_no tran_no, h.ref reference, h.trans_date tran_date, a.stamp created_on, a.user created_by, 
                        DATEDIFF(DATE_FORMAT(a.stamp, '%Y-%m-%d'), h.trans_date) days,
                        u.real_name 
                FROM ".TB_PREF."bank_trans h   
                    JOIN ".TB_PREF."audit_trail a    
                        ON a.type = h.type   
                        AND a.trans_no = h.trans_no     
                    JOIN ".TB_PREF."users u   
                        ON u.id = a.user   
                WHERE h.trans_date >= ".db_escape($from_date)." 
                    AND h.trans_date <= ".db_escape($to_date)." 
                    AND h.type = ".db_escape(ST_IOU_BANKPAYMENT_RETURN);
    if($user_id>0){
        $sql .= " AND a.user = ".db_escape($user_id);
    }       
                
    return  $sql;
}

function dashboard_get_petty_cash_data_entry_users($from_date, $to_date, $user_id=0) {
    
    $sql_petty_cash_expenses = dashboard_get_sql_for_petty_cash_data_entry_petty_cash_expenses($from_date, $to_date, $user_id);
    $sql_ious = dashboard_get_sql_for_petty_cash_data_entry_ious($from_date, $to_date, $user_id);
    $sql_iou_batches = dashboard_get_sql_for_petty_cash_data_entry_iou_batches($from_date, $to_date, $user_id);
    $sql_iou_payments = dashboard_get_sql_for_petty_cash_data_entry_iou_payments($from_date, $to_date, $user_id);
    $sql_petty_cash_payments = dashboard_get_sql_for_petty_cash_data_entry_petty_cash_payments($from_date, $to_date, $user_id);
    $sql_bank_payments = dashboard_get_sql_for_petty_cash_data_entry_iou_bank_payments($from_date, $to_date, $user_id);
    $sql_bank_returns = dashboard_get_sql_for_petty_cash_data_entry_iou_bank_returns($from_date, $to_date, $user_id);
    
    $sql = $sql_petty_cash_expenses 
        ." UNION ".$sql_ious 
        ." UNION ".$sql_iou_batches 
        ." UNION ".$sql_iou_payments 
        ." UNION ".$sql_petty_cash_payments 
        ." UNION ".$sql_bank_payments 
        ." UNION ".$sql_bank_returns
            ;   
    
    $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_petty_cash_user_data_entry_period_summary($from_date, $to_date, $user_id, $trans_type) {
    
    switch ($trans_type) {
        case ST_PETTY_CASH:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_petty_cash_expenses($from_date, $to_date, $user_id);
            break;
        case ST_I_OWE_YOU:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_ious($from_date, $to_date, $user_id);
            break;
        case ST_I_OWE_YOU_BATCH:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_iou_batches($from_date, $to_date, $user_id);
            break;
        case ST_I_OWE_YOU_PAYMENT:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_iou_payments($from_date, $to_date, $user_id);
            break;
        case ST_PETTY_CASH_PAYMENT:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_petty_cash_payments($from_date, $to_date, $user_id);
            break;
        case ST_IOU_BANKPAYMENT:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_iou_bank_payments($from_date, $to_date, $user_id);
            break;
        case ST_IOU_BANKPAYMENT_RETURN:
            $sql = dashboard_get_sql_for_petty_cash_data_entry_iou_bank_returns($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_petty_cash_user_data_entry_period_summary"); 
    
    return db_fetch($result);
                
}
