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


function get_sql_for_Monthly_pc_inquiry($project_id, $from_date, $to_date, $selected_id, $reference)
{
    $sql = "SELECT DISTINCT mpc.*,
                    pm.description project_description

            FROM ".TB_PREF."Monthly_project_costs mpc  
                JOIN ".TB_PREF."projects_master pm ON
                    mpc.project_id = pm.project_id ";
                    
                if($project_id > 0){
                    $sql .= " JOIN ".TB_PREF."Monthly_project_costs_details mpcd ON
                            mpc.id = mpcd.mpc_id ";
                }
             
            $sql .= " WHERE  mpc.date >".db_escape(date2sql($from_date))." AND  mpc.date <".db_escape(date2sql($to_date));  
            
   
    if($project_id > 0){
        $sql .= "AND mpcd.project_id=".db_escape($project_id);
    }
    
    if($selected_id > 0){
        $sql .= "AND mpc.id=".db_escape($selected_id);
    }
    
    if($reference != ''){
        $sql .= "AND mpc.reference=".db_escape($reference);
    }
    
    return $sql;
    
}
        

function view_Monthly_pc_header($trans_no)
{
    $sql = "SELECT mpc.*,
                    pm.description project_description

            FROM ".TB_PREF."Monthly_project_costs mpc
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    mpc.project_id = pm.project_id
            
            WHERE mpc.id=".db_escape($trans_no);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


function view_Monthly_pc_details($trans_no)
{
     $sql = "SELECT mpcd.*,
                    pbig.description boq_item_group_description,
                    iu.name unit_name,
                    pt.description budget_name,
                    lv.value_name Monthly_type,
                    da.account_name debit_account_name,
                    ca.account_name credit_account_name 
         
            FROM ".TB_PREF."Monthly_project_cost_details mpcd                
                LEFT JOIN ".TB_PREF."project_boq_item_groups pbig ON
                    mpcd.task_boq_group_id = pbig.id

                LEFT JOIN ".TB_PREF."item_units iu ON
                    mpcd.unit = iu.abbr
                    
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    mpcd.task_id = pt.task_id
                    
                LEFT JOIN ".TB_PREF."list_values lv ON
                    mpcd.mpc_type_id = lv.id 
                LEFT JOIN ".TB_PREF."chart_master da ON 
                    mpcd.debit_account = da.account_code  
                LEFT JOIN ".TB_PREF."chart_master ca ON 
                    mpcd.credit_account = ca.account_code  

            WHERE mpcd.mpc_id=".db_escape($trans_no)." 
                
            ORDER BY id";
    
    return db_query($sql);
    
}



function get_sql_for_approve_Monthly_project_cost_inquiry($current_user_id)
{        
    $sql = "SELECT mpc.*,
                    pm.description project_description                
            
            FROM ".TB_PREF."Monthly_project_costs mpc 
                JOIN ".TB_PREF."projects_master pm ON
                    mpc.project_id = pm.project_id          
                                 
                JOIN ".TB_PREF."approvers apr ON
                    mpc.approved_by = apr.user_id                 
                  
                JOIN ".TB_PREF."users c ON
                    mpc.created_by = c.id
                    
                JOIN ".TB_PREF."user_data_access_groups udag ON
                    pm.dag_id = udag.dag_id
                    
                JOIN ".TB_PREF."users u ON
                    apr.user_id = u.id                    
                                             
            WHERE (mpc.approval_status = ".AS_AWAITING_APPROVAL." OR mpc.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    mpc.next_approval_group_id = apr.approval_group_id AND 
                    mpc.created_by = c.id AND                   
                    udag.user_id = apr.user_id AND                   
                    apr.user_id =".db_escape($current_user_id);                    
                        
    return $sql; 
    
}



function view_monthly_project_costs_header($mpc_no)
{
    $sql = "SELECT mpcd.*,
                   mpc.reference,
                   mpc.date,
                   mpc.approval_status,
                   mpc.base_total_type,
                   mpc.distribution_type,
                   mpc.scheduled_total,
                   m.description month_name,
                   pm.description project_description                                 
        
            FROM ".TB_PREF."monthly_project_costs_details mpcd
                JOIN ".TB_PREF."projects_master pm 
                    ON mpcd.project_id = pm.project_id       
                JOIN ".TB_PREF."monthly_project_costs mpc 
                    ON mpcd.mpc_id = mpc.id       
                JOIN ".TB_PREF."months m 
                    ON m.id = mpc.month_id       
                            
            WHERE mpcd.mpc_id = ".db_escape($mpc_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function view_monthly_project_costs_departments($order_no)
{
    $sql = "SELECT dep.id lv_department_id,
                    dep.value_name department_name,
                    mdep.* 
                    
                         
            FROM ".TB_PREF."list_values dep   
                LEFT JOIN ".TB_PREF."monthly_project_costs_departments mdep 
                    ON mdep.department_id = dep.id  
                    AND mdep.mpc_id=".db_escape($order_no)." 
            WHERE dep.list_code = 'DEPARTMENTS' 
                AND mdep.total > 0 
                    
            ORDER BY dep.value_name";
    
    return db_query($sql, "get_monthly_project_costs_detail_departments.");
    
}


function view_monthly_project_costs_details($order_no, $department_id)
{
    $sql = "SELECT pm.description project_description,
                    pt.description task_description,
                    md.* 
  
            FROM ".TB_PREF."monthly_project_costs_details md 
                JOIN ".TB_PREF."projects_master pm 
                    ON md.project_id = pm.project_id 
                JOIN ".TB_PREF."project_tasks pt 
                    ON md.task_id = pt.task_id 

            WHERE md.mpc_id=".db_escape($order_no)." 
                AND pm.department_id=".db_escape($department_id)." 
                    
            ORDER BY pm.description";
    
    return db_query($sql, "view_monthly_project_costs_details.");
    
}


function view_monthly_project_costs_detail_header($mpc_no)
{
    $sql = "SELECT mpc.*,
                    pm.description project_description ,
                    mpcd.calculated_value 
        
            FROM ".TB_PREF."monthly_project_costs mpc
                JOIN ".TB_PREF."projects_master pm 
                    ON mpc.project_id = pm.project_id       
                JOIN ".TB_PREF."monthly_project_costs_departments mpcd  
                    ON mpc.id = mpcd.mpc_id        
                            
            WHERE mpc.id=".db_escape($mpc_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_sql_for_approve_monthly_pc_inquiry($current_user_id)
{
    $sql = "SELECT mpc.id,
                mpc.reference,
                pm.description project_description,
                mpc.date,                   
                mpc.total,
                c.real_name,
                mpc.created_on,
                mpc.approval_status,
                mpc.comment 

        FROM ".TB_PREF."Monthly_project_costs mpc 
                JOIN ".TB_PREF."projects_master pm 
                    ON mpc.project_id = pm.project_id

                JOIN ".TB_PREF."users c 
                    ON mpc.created_by = c.id  
                JOIN ".TB_PREF."approvers as apr 
                    ON mpc.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 (mpc.approval_status = ".AS_AWAITING_APPROVAL." 
                OR mpc.approval_status = ".AS_WITH_THE_APPROVER." 
                OR mpc.approval_status = ".AS_BACK_TO_ORIGINATOR.") 
            AND mpc.created_by = c.id 
            AND udag.user_id = apr.user_id       
            AND apr.user_id = ".db_escape($current_user_id);    
      
    return $sql;
    
}


        
?>








































