<?php

//function dashboard_get_utility_expense_types($from_date, $to_date, $vehicle_id=0) {
function dashboard_get_sql_for_utility_bill_expense_types($from_date, $to_date, $expense_type_id=0, $project_id=-1, $expense_category_id=0, $expense_item_id=-1){
    
    $sql_ub = "SELECT ".ST_UTILITY_BILLS." trans_type, ub.id trans_no, ub.date, ub.project_id,  
        ut.id expense_type_id, ut.description expense_type_name, 
        ubd.amount, ubd.bill_no, ubd.bill_reference, ubd.expense_category_id, ubd.description, ubd.expense_item_id   
            FROM ".TB_PREF."utility_bills ub   
                JOIN ".TB_PREF."utility_bill_details ubd    
                    ON ub.id = ubd.ub_id   
                JOIN ".TB_PREF."utility_expense_types ut    
                    ON ubd.expense_type_id = ut.id   
            WHERE ub.date >= ".db_escape($from_date)." 
                AND ub.date <= ".db_escape($to_date);
    
    if($project_id > 0){
        $sql_ub .=" AND ub.project_id = ".db_escape($project_id);
    }
    if($expense_type_id > 0){
        $sql_ub .=" AND ubd.expense_type_id = ".db_escape($expense_type_id);
    }
    if($expense_category_id > 0){
        $sql_ub .=" AND ubd.expense_category_id = ".db_escape($expense_category_id);
    }
    if($expense_item_id > 0){
        $sql_ub .=" AND ubd.expense_item_id = ".db_escape($expense_item_id);
    }
    
//    $sql_ub .=" GROUP BY ubd.expense_type_id ";   
    
    return $sql_ub;
}

function dashboard_get_sql_for_utility_bill_credit_note_expense_types($from_date, $to_date, $expense_type_id=0, $project_id=-1, $expense_category_id=0, $expense_item_id=-1){
    
    $sql_ubcn = "SELECT ".ST_UTILITY_BILL_CREDIT_NOTE." trans_type, ub.id trans_no, ub.date, ub.project_id,  
        ut.id expense_type_id, ut.description expense_type_name, 
        (-ubd.amount) amount, ubd.bill_no, ubd.bill_reference, ubd.expense_category_id, ubd.description, ubd.expense_item_id     
            FROM ".TB_PREF."utility_bill_credit_notes ub   
                JOIN ".TB_PREF."utility_bill_credit_note_details ubd    
                    ON ub.id = ubd.ubcn_id   
                JOIN ".TB_PREF."utility_expense_types ut    
                    ON ubd.expense_type_id = ut.id   
            WHERE ub.date >= ".db_escape($from_date)." 
                AND ub.date <= ".db_escape($to_date);
                    
    if($project_id > 0){
        $sql_ubcn .=" AND ub.project_id = ".db_escape($project_id);
    }
    if($expense_type_id > 0){
        $sql_ubcn .=" AND ubd.expense_type_id = ".db_escape($expense_type_id);
    }
    if($expense_category_id > 0){
        $sql_ubcn .=" AND ubd.expense_category_id = ".db_escape($expense_category_id);
    }
    if($expense_item_id > 0){
        $sql_ubcn .=" AND ubd.expense_item_id = ".db_escape($expense_item_id);
    }
    
//    $sql_ubcn .=" GROUP BY ubd.expense_type_id ";    
    
    
    return $sql_ubcn;
    
}

function dashboard_get_utility_expense_types($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_type_id, expense_type_name, SUM(amount) grand_total 
            FROM ($sql) ub 
            GROUP BY expense_type_id 
            ORDER BY grand_total DESC";
                
        return db_query($sql, "dashboard_get_utility_expense_types"); 
}

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

//function dashboard_get_utility_period_expense_type_total($expense_type_id, $from_date, $to_date) {
//    
//    $sql_ub = "SELECT SUM(ubd.amount) total    
//            FROM ".TB_PREF."utility_bills ub   
//                JOIN ".TB_PREF."utility_bill_details ubd    
//                    ON ub.id = ubd.ub_id   
//            WHERE ub.date >= ".db_escape($from_date)." 
//                AND ub.date <= ".db_escape($to_date)."  
//                AND ubd.expense_type_id = ".db_escape($expense_type_id);
//    
//    $sql_ubcn = "SELECT SUM(ubd.amount) total  
//            FROM ".TB_PREF."utility_bill_credit_notes ub   
//                JOIN ".TB_PREF."utility_bill_credit_note_details ubd    
//                    ON ub.id = ubd.ubcn_id   
//                JOIN ".TB_PREF."utility_expense_types ut    
//                    ON ubd.expense_type_id = ut.id   
//            WHERE ub.date >= ".db_escape($from_date)." 
//                AND ub.date <= ".db_escape($to_date)."  
//                AND ubd.expense_type_id = ".db_escape($expense_type_id);
//    
//    $sql = $sql_ub." UNION ".$sql_ubcn;
//    
//    $sql = "SELECT SUM(total) total 
//            FROM ($sql) ub ";
//    
//        $result = db_query($sql, "dashboard_get_utility_period_expense_type_total"); 
//        return db_result_value($result, 'total');
//}

?>