<?php


function dashboard_get_sql_for_transport_utility_expense_vehicles($from_date, $to_date, $expense_type_id=0, $vehicle_id=0) {
    
    $sql_ub = "SELECT ".ST_UTILITY_BILLS." trans_type, ub.id trans_no, ub.date trans_date, ub.project_id, 
                        ubd.expense_item_id vehicle_id, ubd.expense_type_id, ubd.amount, ubd.bill_no, ubd.bill_reference, ubd.description, ubd.expense_category_id    
            FROM ".TB_PREF."utility_bills ub   
                JOIN ".TB_PREF."utility_bill_details ubd    
                    ON ub.id = ubd.ub_id   
            WHERE ubd.expense_category_id = ".db_escape(EC_VEHICLE_MAINTENANCE)." 
                AND ub.date >= ".db_escape($from_date)." 
                AND ub.date <= ".db_escape($to_date);
    
    if($expense_type_id > 0){
        $sql_ub .= " AND ubd.expense_type_id = ".db_escape($expense_type_id);
    }
    if($vehicle_id > 0){
        $sql_ub .= " AND ubd.expense_item_id = ".db_escape($vehicle_id);
    }
    
//    $sql_ub = " GROUP BY ubd.expense_type_id ";    
    
    
    $sql_ubcn = "SELECT ".ST_UTILITY_BILL_CREDIT_NOTE." trans_type, ub.id trans_no, ub.date trans_date, ub.project_id,
                        ubd.expense_item_id vehicle_id, ubd.expense_type_id, -ubd.amount, ubd.bill_no, ubd.bill_reference, ubd.description, ubd.expense_category_id   
            FROM ".TB_PREF."utility_bill_credit_notes ub   
                JOIN ".TB_PREF."utility_bill_credit_note_details ubd    
                    ON ub.id = ubd.ubcn_id    
            WHERE ubd.expense_category_id = ".db_escape(EC_VEHICLE_MAINTENANCE)."  
                AND ub.date >= ".db_escape($from_date)." 
                AND ub.date <= ".db_escape($to_date);
    
    if($expense_type_id > 0){
        $sql_ubcn .= " AND ubd.expense_type_id = ".db_escape($expense_type_id);
    }
    if($vehicle_id > 0){
        $sql_ubcn .= " AND ubd.expense_item_id = ".db_escape($vehicle_id);
    }
    
//        $sql_ubcn = " GROUP BY ubd.expense_type_id ";    
    
    $sql = $sql_ub." UNION ".$sql_ubcn;
    
    return $sql;
    
}

function dashboard_get_transport_utility_expense_vehicles($from_date, $to_date, $expense_type_id=0, $vehicle_id=0) {
    
    $sql = dashboard_get_sql_for_transport_utility_expense_vehicles($from_date, $to_date, $expense_type_id, $vehicle_id);
    
    $sql = "SELECT vehicle_id, SUM(amount) grand_total 
            FROM ($sql) ub 
            GROUP BY vehicle_id 
            ORDER BY grand_total DESC";
                
        return db_query($sql, "dashboard_get_transport_utility_expense_vehicles"); 
}


function dashboard_get_transport_period_utility_expense_type_total($vehicle_id, $from_date, $to_date,$expense_type_id=0) {
    
    $sql = dashboard_get_sql_for_transport_utility_expense_vehicles($from_date, $to_date, $expense_type_id, $vehicle_id);
    
    $sql = "SELECT SUM(amount) total 
            FROM ($sql) ub  ";
                
    $result = db_query($sql, "dashboard_get_transport_period_utility_expense_type_total"); 
    
    return db_result_value($result, "total");
}


