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


function get_sql_for_subcontractor_purchase_orders_inquiry($selected_id, $reference, $project_id, 
                                                            $supplier_id, $tax_group_id, $from_date, $to_date)
{
    $sql = "SELECT spo.*,
                    pm.description project_description,
                    su.supp_name,
                    tg.name tax_group_name,
                    c.real_name 

            FROM ".TB_PREF."subcontractor_purchase_orders spo
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    spo.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."suppliers su ON
                    spo.supplier_id = su.supplier_id

                LEFT JOIN ".TB_PREF."tax_groups tg ON
                    spo.tax_group_id = tg.id
                    
                LEFT JOIN ".TB_PREF."users c ON
                        spo.created_by = c.id 

            WHERE spo.date  >".db_escape(date2sql($from_date))." AND  spo.date <".db_escape(date2sql($to_date)); 
    
    if($selected_id > 0){
        $sql .= "AND spo.id=".db_escape($selected_id);
    }
    
    if($reference != ''){
        $sql .= "AND spo.reference=".db_escape($reference);
    }
    
    if($project_id > 0){
        $sql .= "AND spo.project_id=".db_escape($project_id);
    }
    
    if($supplier_id > 0){
        $sql .= "AND spo.supplier_id=".db_escape($supplier_id);
    }
    
    if($tax_group_id > 0){
        $sql .= "AND spo.tax_group_id=".db_escape($tax_group_id);
    }
    
    return $sql;
    
}
        

function view_sc_purchase_orders_header($sc_po_id)
{
    $sql = "SELECT spo.*,
                    pm.description project_description,
                    su.supp_name,
                    tg.name tax_group_name


            FROM ".TB_PREF."subcontractor_purchase_orders spo
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    spo.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."suppliers su ON
                    spo.supplier_id = su.supplier_id
                    
                LEFT JOIN ".TB_PREF."tax_groups tg ON
                    spo.tax_group_id = tg.id

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


function view_sc_purchase_order_details($sc_po_id)
{
     $sql = "SELECT spod.*,
                    pt.description budget_description,
                    pt.task_id budget_task_id,
                    pt.task_code budget_code,
                    pbi.description boq_item_description,
                    pbi.code boq_item_code,
                    iu.name unit_name
        
            FROM ".TB_PREF."subcontractor_purchase_order_details spod
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            spod.task_id = pt.task_id
                            
                    LEFT JOIN ".TB_PREF."project_boq_items pbi ON
                            spod.boq_item_id = pbi.id
                            
                    LEFT JOIN ".TB_PREF."item_units iu ON
                            spod.unit = iu.abbr
                                              
            WHERE spod.sc_po_id=".db_escape($sc_po_id)." 
            ORDER BY id";
    
    return db_query($sql);
    
}


function get_sql_for_approve_subcontractor_purchase_orders_inquiry($current_user_id)
{
    $sql = "SELECT scpo.id,
                    scpo.reference,
                    pm.description project_description,
                    sp.supp_name supplier_name,
                    scpo.agreement_reference,
                    scpo.date,
                    c.real_name                
                    

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


?>