<?php

function dashboard_get_sql_for_approvals($from_date, $to_date, $user_id=0, $type=null) {
    $sql = "SELECT l.id, l.tran_type, l.tran_no, l.approved_by, l.approved_on, l.approval_status, 
                TIMESTAMPDIFF(SECOND, ( SELECT pl.approved_on   
                                        FROM ".TB_PREF."approval_log pl 
                                        WHERE pl.tran_type = l.tran_type 
                                            AND pl.tran_no = l.tran_no 
                                            AND pl.id < l.id 
                                        ORDER BY pl.id 
                                        DESC LIMIT 1), l.approved_on ) diff, ( SELECT pl.approved_on   
                                                                                FROM ".TB_PREF."approval_log pl 
                                                                                WHERE pl.tran_type = l.tran_type 
                                                                                    AND pl.tran_no = l.tran_no 
                                                                                    AND pl.id < l.id 
                                                                                ORDER BY pl.id 
                                                                                DESC LIMIT 1) pre_approved_on

            FROM ".TB_PREF."approval_log l 
            WHERE l.approval_status IN (".AS_AWAITING_APPROVAL.",".AS_APPROVED.") 
                AND l.approved_on >= ".db_escape($from_date)."
                AND l.approved_on <= ".db_escape($to_date);

                if ($user_id > 0) {
                    $sql .= " AND l.approved_by = ".db_escape($user_id);
                }
                if ($type !== null) {
                    $sql .= " AND l.tran_type = ".db_escape($type);
                }

    $sql .= " HAVING pre_approved_on IS NOT NULL ";
    
    return $sql;
}

function dashboard_get_approvals_users($sql) {
    $sql = "SELECT DISTINCT tmp_table.approved_by, tmp_table.tran_type
            FROM ($sql) tmp_table 
                JOIN ".TB_PREF."users u 
                    ON u.id = tmp_table.approved_by 
            WHERE 1 = 1 
            ORDER BY u.real_name, tmp_table.tran_type";
    
    return db_query($sql); 
}

function dashboard_get_user_approvals_period_summary($from_date, $to_date, $user_id, $trans_type) {
    
    $sql_2 = dashboard_get_sql_for_approvals($from_date, $to_date, $user_id, $trans_type);
    
    $sql = "SELECT (SUM(d.diff) / COUNT(d.id)) avg_delay, d.approved_by, d.approved_on, d.tran_type, d.tran_no, d.approval_status, d.pre_approved_on 
            FROM ($sql_2) d GROUP BY d.approved_by ";
    
    $result = db_query($sql, "dashboard_get_user_approvals_period_summary"); 
    
    return db_result_value($result, 'avg_delay');
                
}

?>