<?php
/**********************************************************************
***********************************************************************/
function get_unapproved_i_owe_you_entries()
{  
	$sql= "SELECT ou.*,em.employee_code,
                em.initials,em.last_name,em.first_name,
                st.value_name type_name,
                pt.description project_task,
                pm.description project, 
                sc.value_name status 
            FROM " .TB_PREF."employee_master em, "
                .TB_PREF."i_owe_you ou, "
                .TB_PREF."list_values st, "
                 .TB_PREF."list_values sc, "
                .TB_PREF."project_tasks pt,"
                .TB_PREF."projects_master pm     
            WHERE ou.employee_code=em.employee_code AND ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' AND 
                ou.project_id = pm.project_id AND pt.task_id=ou.task_id AND 
                ou.status_code=sc.value_code AND sc.list_code = 'ATTENDANCE_STATUS' AND ou.status_code IN ('N', 'U', 'O')
            ORDER BY tran_date";
                        

	
	$result = db_query($sql,"Could not retrieve i owe you entries");	
        
        return $result;
}

function get_sql_for_i_owe_you_payments($iou_id, $batch_no, $from_date, $to_date, $employee_code, $type_id, $project_id, $float_id)
{  
    $user_id = $_SESSION['wa_current_user']->user;
    
	$sql= "SELECT 
                pctn.value_name type_name,
                ou.id,
                ou.batch_no,
                ou.company_code,
                pm.description project_description,                
                pf.name float_name,
                
                ou.amount,
                ou.tran_date,
                ou.comments,    
                ou.approval_status iou_approval_status,
                ou.batch_complete,
                ou.employee_code,
                ou.closed_for_payments,
                b.approval_status batch_approval_status,
                pctn.value_code type_code 
            FROM ".TB_PREF."vw_i_owe_you_petty_cash ou 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            ou.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."user_data_access_groups as udag ON 
                            pm.dag_id = udag.dag_id 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON    
                            ou.float_id = pf.id 
                    INNER JOIN ".TB_PREF."petty_cash_float_users pfu ON 
                            pfu.user_id = udag.user_id AND 
                            pf.id = pfu.float_id 
                    INNER JOIN ".TB_PREF."list_values pctn ON 
                            ou.type = pctn.id AND pctn.list_code = 'PETTY_CASH_TYPES' 
                    LEFT JOIN ".TB_PREF."i_owe_you_batches b ON 
                            ou.batch_no = b.batch_no   
                    LEFT JOIN group_companies gc ON 
                            ou.company_code = gc.company_code 
                    
            WHERE (ou.approval_status = ".AS_APPROVED.") AND 
                    ou.batch_complete = 0 AND 
                    udag.user_id = ".db_escape($user_id);
                        
    

    if($iou_id > 0 || $batch_no > 0){
        if($iou_id > 0){
            $sql .= " AND ou.id = ".db_escape($iou_id);
        }

        if($batch_no > 0){
            $sql .= " AND ou.batch_no = ".db_escape($batch_no);
        }
    }
    else{
            
        if(strlen($employee_code) > 0 && $employee_code != 0){
            $sql .= " AND ou.employee_code = ".db_escape($employee_code);
        }

        if($type_id > 0){
            $sql .= " AND ou.type = ".db_escape($type_id);
        }

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

        if($float_id > 0){
            $sql .= " AND ou.float_id = ".db_escape($float_id);
        }
    
            $sql .= " AND ou.tran_date >= ".db_escape(date2sql($from_date))." 
                        AND ou.tran_date <= ".db_escape(date2sql($to_date));
    }

	$sql .=" ORDER BY ou.id, tran_date";
        
	return $sql;
}

       
function get_sql_for_i_owe_you_inquiry($from_date, $to_date, $type_id, $project_id, $float_id, $company_code, $employee_code, $iou_id, $batch_no)
{  
	$sql= "SELECT 
                ou.id,
                ou.batch_no,
                ou.company_code,
                pm.description project_description,                
                pf.name float_name,
                pctn.value_name type_name,
                ou.amount,
                ou.tran_date,
                ou.comments,    
                ou.approval_status,
                ou.batch_complete,
                ou.employee_code,
                ou.closed_for_payments,
                b.approval_status batch_approval_status,
                b.complete 
            FROM ".TB_PREF."i_owe_you ou 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            ou.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON    
                            ou.float_id = pf.id 
                    INNER JOIN ".TB_PREF."list_values pctn ON 
                            ou.type = pctn.id AND pctn.list_code = 'PETTY_CASH_TYPES' 
                    LEFT JOIN ".TB_PREF."i_owe_you_batches b ON 
                            ou.batch_no = b.batch_no 
                    LEFT JOIN group_companies gc ON 
                            ou.company_code = gc.company_code 
            WHERE 1=1 ";
        if ($iou_id > 0 || $batch_no > 0){
            if ($iou_id > 0)
            {
                $sql .= " AND ou.id = ".  db_escape($iou_id);
            }
            if ($batch_no > 0)
            {
                $sql .= " AND b.batch_no = ".  db_escape($batch_no);
            }
        }
        else{
        
            if ($employee_code != "")
            {
                $sql .= " AND ou.employee_code = ".db_escape($employee_code);
            }

            if ($type_id > 0)
            {
                    $sql .= " AND pctn.id=".db_escape($type_id);
            }

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

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

            if ($company_code != '')
            {
                    $sql .= " AND ou.company_code = ".  db_escape($company_code);
            }

            $sql .= " AND ou.tran_date >=".db_escape(date2sql($from_date))."AND ou.tran_date <=".db_escape(date2sql($to_date));
        }
	$sql .=" ORDER BY ou.id, tran_date";
        
	return $sql;
}

function get_i_owe_yous($from_date,$to_date, $type_id = -1, $project_id = -1, $task_id = -1, $employee_code = "", $approval_statuses_in = null)
{  
	$sql= "SELECT 
                ou.id,
                CONCAT(em.employee_code,' - ', em.initials,' ',em.last_name,'(',em.first_name,')') employee,
                st.value_name type_name,
                ou.amount,
                ou.tran_date,
                ou.comments,    
                ou.approval_status 
            FROM " .TB_PREF."employee_master em, "
                .TB_PREF."i_owe_you ou, "
                .TB_PREF."list_values st, "
                .TB_PREF."project_tasks pt,"
                .TB_PREF."projects_master pm     
            WHERE ou.employee_code=em.employee_code AND ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' AND 
                ou.project_id = pm.project_id AND pt.task_id=ou.task_id AND 
                ou.tran_date >=".db_escape(date2sql($from_date))."AND ou.tran_date <=".db_escape(date2sql($to_date));
           
	if ($employee_code != "")
    {
        $sql .= " AND ou.employee_code = ".db_escape($employee_code);
    }
    
	if ($type_id > 0)
	{
		$sql .= " AND st.id=".db_escape($type_id);
	}
        
    if ($project_id > 0)
	{
		$sql .= " AND ou.project_id = ".  db_escape($project_id);
	}
    
    if ($task_id > 0)
	{
		$sql .= " AND ou.task_id = ".  db_escape($task_id);
	}
    
    if ($approval_statuses_in != null)
	{
		$sql .= " AND ou.approval_status IN (".$approval_statuses_in.")";
	}

	$sql .=" ORDER BY ou.id, tran_date";
        
	return db_query($sql,"Could not retrieve i owe you entries");	
}


function approve_i_owe_you($row_id, $status_code)
{
	$approved_by = $_SESSION['wa_current_user']->user;
        $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."i_owe_you 
                SET status_code = ".db_escape($status_code).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".$row_id;
	
	db_query($sql,"Could not approve i owe you");	
        
        return db_num_affected_rows();		
}


function confirm_i_owe_you_details($row_id, $approved_amount)
{
	$approved_by = $_SESSION['wa_current_user']->user;
        $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET approved_amount = ".db_escape($approved_amount).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".db_escape($row_id);
	
	db_query($sql,"Could not approve i owe you");
        //return db_num_affected_rows();	
        		
}

function get_i_owe_you_row($iou_id)
{
    $sql= "SELECT 
                ou.company_code,
                ou.employee_code,
                pm.description project,
                st.value_name type_name,
                ou.* 
            FROM ".TB_PREF."i_owe_you ou, 
                ".TB_PREF."list_values st, 
                ".TB_PREF."projects_master pm   
            WHERE ou.type = st.id AND 
                st.list_code = 'PETTY_CASH_TYPES' AND 
                ou.project_id = pm.project_id AND  
                ou.id=".db_escape($iou_id);


	$sql .=" ORDER BY tran_date";
        
	$result=db_query($sql,"Could not retrieve i owe you");	
        return db_fetch($result);
    
}




//-----------------------------------------------------------------------------------



function get_previous_detail_claim_expenses($employee_code, $validating_date)
{
    $sql= "SELECT exn.value_name expense_type_name, 
                    ex.* 
            FROM ".TB_PREF."i_owe_you_expenses ex, 
                    ".TB_PREF."list_values exn ,
                    ".TB_PREF."i_owe_you ou  
            WHERE ex.id = exn.id AND 
                    ou.id = ex.iou_id AND 
                    ou.employee_code = ".db_escape($employee_code)." AND 
                    ex.id = ".db_escape($id)." AND 
                    ex.expence_date >= ".db_escape($validating_date);

	return db_query($sql,"Could not get_previous_claim_expenses");	

}

function get_latest_previous_detail_claim_expense($employee_code, $id)
{
    $sql= "SELECT exn.value_name expense_type_name, 
                    ex.* 
            FROM ".TB_PREF."i_owe_you_expenses ex, 
                    ".TB_PREF."list_values exn,
                    ".TB_PREF."i_owe_you ou  
            WHERE ex.id = exn.id AND 
                    ou.id = ex.iou_id AND 
                    ou.employee_code = ".db_escape($employee_code)." AND 
                    ex.id = ".db_escape($id)." 
            ORDER BY ex.expence_date DESC LIMIT 1"; 

	$result = db_query($sql,"Could not get_previous_claim_expenses");	
    return db_fetch($result);

}

function get_previous_main_claim_expenses($employee_code, $main_type_id, $validating_date)
{
    $sql= "SELECT mtn.value_name main_type_name, 
                    ex.* 
            FROM ".TB_PREF."i_owe_you_expenses ex, 
                    ".TB_PREF."claim_type_configurations ctc,
                    ".TB_PREF."list_values mtn,
                    ".TB_PREF."i_owe_you ou   
            WHERE ex.id = ctc.detail_type_id AND 
                    ctc.main_type_id = mtn.id AND 
                    ou.id = ex.iou_id AND 
                    ou.employee_code = ".db_escape($employee_code)." AND 
                    ctc.main_type_id = ".db_escape($main_type_id)." AND 
                    ex.expence_date >= ".db_escape($validating_date);

	return db_query($sql,"Could not get_previous_main_claim_expenses");	

}

function get_latest_previous_main_claim_expense($employee_code, $main_type_id)
{
    $sql= "SELECT mtn.value_name main_type_name, 
                    ex.* 
            FROM ".TB_PREF."i_owe_you_expenses ex, 
                    ".TB_PREF."claim_type_configurations ctc,
                    ".TB_PREF."list_values mtn ,
                    ".TB_PREF."i_owe_you ou   
            WHERE ex.id = ctc.detail_type_id AND 
                    ctc.main_type_id = mtn.id AND 
                    ou.id = ex.iou_id AND 
                    ou.employee_code = ".db_escape($employee_code)." AND 
                    ctc.main_type_id = ".db_escape($main_type_id)." 
            ORDER BY ex.expence_date DESC LIMIT 1"; 

	$result = db_query($sql,"Could not get_latest_previous_main_claim_expense");	
    return db_fetch($result);

}

function get_claim_detail_totals($iou_id)
{
    $sql = "SELECT SUM(amount) 'amount'
            FROM ".TB_PREF."i_owe_you_expenses  
            WHERE iou_id = ".db_escape($iou_id); 

    $result = db_query($sql, "Could not get_claim_dtail_totals");    
    return db_fetch($result);
}



?>