<?php
/**********************************************************************
***********************************************************************/


function get_sql_for_utility_bill_inquiry($ub_no, $ub_reference, $project_id, $supplier_id, $from_date, $to_date, $entry_type_id, $approval_status) {
    $sql = "SELECT ub.id,
                ub.reference,
                pm.description project_description,
                sp.supp_name,
                ub.date tran_date,                   
                ub.approval_status, 
                et.value_name entry_type_name,
                ub.taxes, 
                ub.total,
                ub.updated_on, 
                ub.created_on  
            FROM ".TB_PREF."utility_bills ub
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        ub.project_id = pm.project_id
                    LEFT JOIN ".TB_PREF."suppliers sp ON
                        ub.supplier_id = sp.supplier_id
                    LEFT JOIN ".TB_PREF."list_values et 
                        ON ub.entry_type_id = et.id
            WHERE 1 = 1 "; 
      
        if($ub_no > 0){
            $sql .= " AND ub.id=".db_escape($ub_no);
        } else {
            $sql .= " AND ub.date >=".db_escape(date2sql($from_date))." AND  ub.date <=".db_escape(date2sql($to_date));

            if(strlen($ub_reference) > 0){
                $sql .= " AND ub.reference=".db_escape($ub_reference);
            }

            if($project_id > 0){
                $sql .= " AND ub.project_id=".db_escape($project_id);
            }

            if($supplier_id > 0){
                $sql .= " AND ub.supplier_id=".db_escape($supplier_id);
            }

            if($entry_type_id > 0){
                $sql .= " AND ub.entry_type_id=".db_escape($entry_type_id);
            }

            if ($approval_status > -1) {
                $sql .= " AND ub.approval_status = ".db_escape($approval_status);
            }
        }

        $sql .= " ORDER BY ub.date DESC ";

    return $sql;
}


function get_sql_for_approve_utility_bill_inquiry($current_user_id)
{
    $sql = "SELECT ub.id,
                    ub.reference,
                    pm.description project_description,
                    sp.supp_name,
                    ub.date tran_date,                   
                    ub.total + ub.taxes total,
                    c.real_name,
                    ub.created_on,
                    ub.approval_status 

            FROM ".TB_PREF."utility_bills ub
                    JOIN ".TB_PREF."projects_master pm ON
                        ub.project_id = pm.project_id
                    JOIN ".TB_PREF."suppliers sp ON
                        ub.supplier_id = sp.supplier_id 
                        
                    JOIN ".TB_PREF."users c ON 
                        ub.created_by = c.id  
                    JOIN ".TB_PREF."approvers as apr ON 
                        ub.next_approval_group_id = apr.approval_group_id  
                    JOIN ".TB_PREF."user_data_access_groups as udag ON 
                        pm.dag_id = udag.dag_id AND 
                        udag.user_id = apr.user_id  
                    JOIN ".TB_PREF."users u ON 
                        apr.user_id = u.id 
            
            WHERE (ub.approval_status = ".AS_AWAITING_APPROVAL." OR ub.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    apr.user_id = ".$current_user_id;    
      
    return $sql;
    
}

function view_utiltiy_bill($utility_bill_id)
{
    $sql = "SELECT ub.*,
                    pm.description project_name,
                    su.supp_name,
                    tg.name tax_group_name, 
                    et.value_name entry_type_name 
                    

            FROM ".TB_PREF."utility_bills ub
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    ub.project_id = pm.project_id
                
                LEFT JOIN ".TB_PREF."suppliers su ON
                    ub.supplier_id = su.supplier_id
                    
                LEFT JOIN ".TB_PREF."tax_groups tg ON
                    ub.tax_group_id = tg.id
                LEFT JOIN ".TB_PREF."list_values et 
                    ON ub.entry_type_id = et.id

            WHERE ub.id=".db_escape($utility_bill_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


function view_utility_bill_details($utility_bill_id)
{
    $sql = "SELECT ubd.*,
                   pt.description task_description,
                   uet.description expense_description,
                    ps.description project_site_description,
                    un.name unit_name           

            FROM ".TB_PREF."utility_bill_details ubd
                
            LEFT JOIN ".TB_PREF."project_tasks pt ON
                ubd.task_id = pt.task_id

            LEFT JOIN ".TB_PREF."utility_expense_types uet ON
                ubd.expense_type_id = uet.id 
            LEFT JOIN ".TB_PREF."project_sites ps ON
                    ubd.project_site_id = ps.id
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ubd.tran_unit = un.abbr  
                        
            WHERE ubd.ub_id=".db_escape($utility_bill_id);
    
    return db_query($sql);
}


function view_utility_bill_taxes($utility_bill_id)
{
    $sql = "SELECT ubt.*, tt.name tax_type_name 
        
            FROM ".TB_PREF."utility_bill_taxes ubt 
                JOIN ".TB_PREF."tax_types tt ON 
                    ubt.tax_type_id = tt.id 

            WHERE ubt.ub_id=".db_escape($utility_bill_id);
    
    return db_query($sql);
}
        
function get_utility_bill_expense_details_inquiry($id, $reference, $supplier_id, $description, $project_id, $from_date, $to_date, $expense_category_id, 
                                                  $expense_item_id, $expense_type_id) {
    
    $sql = "SELECT ub.id ub_id, ub.reference, ub.supplier_id, ub.date, 
                ubd.*, 
                sup.supp_name, 
                pt.description task_description, 
                ept.description expense_type_name,
                pm.description project_name  
             FROM ".TB_PREF."utility_bills ub 
                JOIN ".TB_PREF."utility_bill_details ubd    
                    ON ub.id = ubd.ub_id    
                JOIN ".TB_PREF."suppliers sup      
                    ON ub.supplier_id = sup.supplier_id    
                JOIN ".TB_PREF."projects_master pm     
                    ON ub.project_id = pm.project_id    
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ubd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."utility_expense_types ept  
                    ON ubd.expense_type_id = ept.id 
             WHERE ub.date >= ".db_escape($from_date)." 
                AND ub.date <= ".db_escape($to_date);     
    
                if ($id > 0) {
                    $sql .= " AND ub.id = ".db_escape($id);   
                }
                if ($reference > 0) {
                    $sql .= " AND ub.reference = ".db_escape($reference); 
                }
                if ($project_id > 0) {
                    $sql .= " AND ub.project_id = ".db_escape($project_id);   
                }
                if (strlen($description) > 0) {
                    $sql .= " AND ubd.description LIKE '%$description%'";   
                }
                if ($supplier_id > 0) {
                    $sql .= " AND ub.supplier_id = ".db_escape($supplier_id);     
                }
                if ($expense_category_id > 0) {
                    $sql .= " AND ubd.expense_category_id = ".db_escape($expense_category_id);   
                }
                if ($expense_item_id > 0) {
                    $sql .= " AND ubd.expense_item_id = ".db_escape($expense_item_id);   
                }
                if ($expense_type_id > 0) {
                    $sql .= " AND ubd.expense_type_id = ".db_escape($expense_type_id);   
                }

            $sql .= " ORDER BY ub.id, ubd.id";
	        
	return db_query($sql, "get_utility_bill_expense_details_inquiry");	
}
 
function get_sql_for_utility_bill_stock_receipts_inquiry($trans_no, $type, $reference, $from_date, $to_date, $project_id, $supplier_id, $source_trans_no, $source_type) {
    $sql = "SELECT * 
            FROM ".TB_PREF."utility_bill_stock_receipts  
            WHERE date >= ".db_escape(date2sql($from_date))." 
                AND date <= ".db_escape(date2sql($to_date));
   
        if ($trans_no > 0) {
            $sql .= " AND id = ".db_escape($trans_no);
        }
        
        if ($type > 0 && is_numeric($type)) {
            $sql .= " AND type = ".db_escape($type);
        }

        if (strlen($reference) > 0) {
            $sql .= " AND reference = ".db_escape($reference);
        }

        if ($project_id > 0) {
            $sql .= " AND project_id = ".db_escape($project_id);
        }

        if ($supplier_id > 0) {
            $sql .= " AND supplier_id = ".db_escape($supplier_id);
        }

        if ($source_trans_no > 0) {
            $sql .= " AND source_trans_no = ".db_escape($source_trans_no);
        }
        
        if ($source_type > 0 && is_numeric($source_type)) {
            $sql .= " AND source_type = ".db_escape($source_type);
        }

        return $sql;
}

function get_utility_expenses_summary($from_, $to_, $entry_type_id=0) {
    $from = date2sql($from_);
    $to = date2sql($to_);

    $sql = "SELECT h.*,
                tg.name tax_group_name, 
                et.value_name entry_type_name  
            FROM ".TB_PREF."utility_bills h   
                LEFT JOIN ".TB_PREF."tax_groups tg 
                    ON h.tax_group_id = tg.id
                LEFT JOIN ".TB_PREF."list_values et  
                    ON h.entry_type_id = et.id 
            WHERE h.date >= ".db_escape($from)." 
                AND h.date <= ".db_escape($to);
    
                if ($entry_type_id > 0) {
                    $sql .= " AND h.entry_type_id = ".db_escape($entry_type_id);
                }
                
            $sql .= " ORDER BY h.date";

        return db_query($sql, "get_utility_expenses_summary");
}