<?php
/**********************************************************************
***********************************************************************/

function get_sql_unapproved_est_project_inquiry($current_user_id, $project_id, $department_id)
{
       
    $sql = "SELECT pm.*, 
                    lv.value_name department_name 
                    
            FROM ".TB_PREF."est_projects_master pm           
                JOIN ".TB_PREF."users c ON
                    pm.created_by = c.id   
                    
                JOIN ".TB_PREF."user_data_access_groups udag ON
                    pm.dag_id = udag.dag_id  
                
                JOIN ".TB_PREF."approvers apr ON
                    pm.next_approval_group_id = apr.approval_group_id                  
                       
                                
                JOIN ".TB_PREF."users u ON 
                    apr.user_id = u.id 

                LEFT JOIN ".TB_PREF."list_values lv ON
                    pm.department_id = lv.id                     
                 
                                
            WHERE pm.approval_status IN (".db_escape(AS_NEW).",".db_escape(AS_AWAITING_APPROVAL)."," 
                                            .db_escape(AS_WITH_THE_APPROVER).",".db_escape(AS_BACK_TO_ORIGINATOR).",".db_escape(AS_REOPENED).") AND 

                pm.created_by = c.id AND 

                udag.user_id = apr.user_id AND  

                apr.user_id =".db_escape($current_user_id);
                    
        if($project_id > 0){ 
            $sql .= " AND pm.est_project_id=".db_escape($project_id);
        }

        if($department_id > 0){ 
            $sql .= " AND pm.department_id=".db_escape($department_id);
        }
            
        $sql .= " ORDER BY lv.value_name, pm.description";
           
    return $sql; 
}
        
function get_sql_for_approve_project_billing_inquiry($current_user_id)
{

    $sql = "SELECT vpb.type, 
                    vpb.order_no, 
                    vpb.reference,
                    vpb.ord_date,
                    pm.description project_description, 
                    dm.name,
                    pt.terms,
                    vpb.* 
            
            FROM ".TB_PREF."vw_project_billing vpb,
                ".TB_PREF."debtors_master dm, 
                ".TB_PREF."projects_master pm,
                ".TB_PREF."payment_terms pt, 
                ".TB_PREF."users c,
                ".TB_PREF."approvers as apr,
                ".TB_PREF."user_data_access_groups as udag,
                ".TB_PREF."users u  
                 
            WHERE 
                vpb.project_id = pm.project_id AND 
                vpb.debtor_no = dm.debtor_no AND
                vpb.payment_terms = pt.terms_indicator AND 
                (vpb.approval_status = ".AS_AWAITING_APPROVAL." OR vpb.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                vpb.next_approval_group_id = apr.approval_group_id AND 
                vpb.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;
        
    $sql .= " ORDER BY vpb.type, ord_date";
       
                           
    return $sql; 
}

function view_project_invoice($pi_id)
{
    $sql = "SELECT pi.*,
                    pm.project_name,
                    dm.name customer_name,
                    cb.br_name branch_name,
                    pt.terms payment_terms,
                    s.salesman_name,
                    ppa.ord_date ppa_ord_date,
                    ppa.order_no ppa_order_no,
                    ppa.total ppa_total,
                    tg.name tax_group_name 

            FROM  ".TB_PREF."project_invoices pi                
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    pi.project_id = pm.project_id

                LEFT JOIN ".TB_PREF."debtors_master dm ON
                    dm.debtor_no = pi.debtor_no

                LEFT JOIN ".TB_PREF."cust_branch cb ON
                    cb.branch_code = pi.branch_code 

                LEFT JOIN ".TB_PREF."payment_terms pt ON
                    pt.terms_indicator = pi.payment_terms

                LEFT JOIN ".TB_PREF."salesman s ON
                    s.salesman_code = pi.salesman_code
                    
                LEFT JOIN ".TB_PREF."project_payment_applications ppa ON
                    ppa.order_no = pi.order_no 
                    
                LEFT JOIN ".TB_PREF."tax_groups tg ON
                    tg.id = pi.tax_group_id  

            WHERE pi.order_no=".db_escape($pi_id);
            
    $result = db_query($sql);
    return db_fetch($result);
    
}



function get_view_manual_project_invoice_details($pi_id)
{

    $sql = "SELECT pid.*, 
                    u.name unit_name,
                    cn.value_name category_name,
                    da.account_name debit_account_name,
                    ca.account_name credit_account_name 
            FROM ".TB_PREF."project_invoice_details pid 
                    LEFT JOIN ".TB_PREF."item_units u ON 
                            pid.unit = u.abbr 
                    LEFT JOIN ".TB_PREF."list_values cn ON 
                            pid.category_id = cn.id 
                    LEFT JOIN ".TB_PREF."chart_master da ON 
                            pid.debit_account = da.account_code  
                    LEFT JOIN ".TB_PREF."chart_master ca ON 
                            pid.credit_account = ca.account_code  
            WHERE order_no=".db_escape($pi_id)." 
            ORDER BY sequence";
    
    return db_query($sql);
    
    
}



        
        
?>