<?php


//function dashboard_get_sql_for_asset_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_asset_utility_expense_assets($from_date, $to_date, $expense_type_id=0, $project_id=-1, $expense_category_id=0, $expense_item_id=-1) {
    
    $sql_ub = dashboard_get_sql_for_utility_bill_expense_types($from_date, $to_date, $expense_type_id, $project_id, $expense_category_id, $expense_item_id);
    $sql_ubcn = dashboard_get_sql_for_utility_bill_credit_note_expense_types($from_date, $to_date, $expense_type_id, $project_id, $expense_category_id, $expense_item_id);
    
    $sql = $sql_ub." UNION ".$sql_ubcn;
    $sql = "SELECT expense_item_id asset_id, SUM(amount) grand_total 
            FROM ($sql) ub 
            GROUP BY expense_item_id 
            ORDER BY grand_total DESC";
                
        return db_query($sql, "dashboard_get_asset_utility_expense_assets"); 
}


function dashboard_get_asset_period_utility_expense_type_total($from_date, $to_date, $expense_type_id=0, $project_id=-1, $expense_category_id=0, $expense_item_id=-1) {
    
    $sql_ub = dashboard_get_sql_for_utility_bill_expense_types($from_date, $to_date, $expense_type_id, $project_id, $expense_category_id, $expense_item_id);
    $sql_ubcn = dashboard_get_sql_for_utility_bill_credit_note_expense_types($from_date, $to_date, $expense_type_id, $project_id, $expense_category_id, $expense_item_id);
    
    $sql = $sql_ub." UNION ".$sql_ubcn;
    
    $sql = "SELECT SUM(amount) total 
            FROM ($sql) ub  ";
                
    $result = db_query($sql, "dashboard_get_asset_period_utility_expense_type_total"); 
    
    return db_result_value($result, "total");
}


