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


function get_sql_for_utility_bill_cn_inquiry($ubcn_no, $ubcn_reference, $project_id, $supplier_id, $from_date, $to_date)
{
    $sql = "SELECT ub.id,
                    ub.reference,
                    pm.description project_description,
                    sp.supp_name,
                    ub.date tran_date,                   
                    ub.total + ub.taxes total,
                    ub.approval_status 

            FROM ".TB_PREF."utility_bill_credit_notes 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
            
            WHERE ub.date >=".db_escape(date2sql($from_date))." AND  ub.date <=".db_escape(date2sql($to_date));
      
   
    if($ubcn_no > 0){
        $sql .= "AND ub.id=".db_escape($ubcn_no);
    }
    
    if(strlen($ubcn_reference) > 0){
        $sql .= "AND ub.reference=".db_escape($ubcn_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);
    }
      
    return $sql;
    
}


function get_sql_for_approve_utility_bill_cn_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_bill_credit_notes 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_cn($utility_bill_id)
{
    $sql = "SELECT ub.*,
                    pm.description project_name,
                    su.supp_name,
                    tg.name tax_group_name
                    

            FROM ".TB_PREF."utility_bill_credit_notes 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

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


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

            FROM ".TB_PREF."utility_bill_credit_note_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

            WHERE ubd.ubcn_id=".db_escape($utility_bill_id);
    
    return db_query($sql);
}


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

            WHERE ubt.ubcn_id=".db_escape($utility_bill_id);
    
    return db_query($sql);
}
        
?>