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


function get_employee_allowances_assigned_employee_list($salary_component, $from_date, $to_date)
{  
	$sql = " SELECT em.initials, em.first_name,em.middle_name,em.last_name,
                        sc.amount,sc.calculation_type,sc.table_id,sc.employee_code,sc.comments 
            FROM ".TB_PREF."employee_master em,
                    ".TB_PREF."salary_components sc, 
                        ".TB_PREF."list_values lv 
            WHERE 
                  lv.id = sc.salary_component_id AND 
                  sc.employee_code = em.employee_code AND
                  sc.type = ".  db_escape(SCT_EMPLOYEE_ALLOWANCES_OVER_USAGE)." AND
                  lv.value_code = ".db_escape($salary_component)." AND 
                  sc.effective_date <= ".db_escape($to_date)." AND 
                  sc.end_date >= ".  db_escape($from_date)." 
            ORDER BY office_mobile_no
                    ";
	$result = db_query($sql,"Employee allowances assigned employee list could not be retrieved");	
        
        return $result;
}

function get_saved_employee_allowances_usage($payroll_run_id, $employee_code, $salary_component)
{  
	$sql = "SELECT payroll_run_id, spent_amount, over_spent_amount, comments, status_code, eau.id,
                    lv.value_name status_name 
            FROM ".TB_PREF."employee_allowances_usage eau,
                    ".TB_PREF."list_values lv  
            WHERE eau.status_code = lv.value_code AND lv.list_code = 'ATTENDANCE_STATUS' AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    employee_code = ".db_escape($employee_code)." AND 
                    component = ".db_escape($salary_component)." LIMIT 1 ";
    
	$result = db_query($sql,"Saved employee allowances details could not be retrieved");	
        
    return db_fetch($result);
}

function add_employee_allowances_usage($employee_code, $salary_component, $spent_amount, $over_spent_amount,
                        $payroll_run_id, $comments)
{
    
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."employee_allowances_usage
                            (employee_code, component,  
                            spent_amount, over_spent_amount, 
                            payroll_run_id, comments,  
                            status_code, 
                            created_by, created_on) 
			VALUES( ".db_escape($employee_code).",".db_escape($salary_component).","
                            .db_escape($spent_amount).",".db_escape($over_spent_amount).","
                            .db_escape($payroll_run_id).",".db_escape($comments).","
                            .db_escape(ATD_NEW).","
                            .db_escape($id_user).",".db_escape($datetime).")";

	db_query($sql,"Employee allowance usage could not be added");
    return db_insert_id();
}

function update_employee_allowances_usage($employee_code, $salary_component, 
                                    $spent_amount, $over_spent_amount,
                                    $payroll_run_id, $comments, $status_code)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."employee_allowances_usage 
            SET
                spent_amount = ".db_escape($spent_amount).",
                over_spent_amount = ".db_escape($over_spent_amount).",
                comments = ".db_escape($comments).",
                status_code = ".db_escape($status_code).", 
                updated_by = ".db_escape($id_user).",
                updated_on = ".db_escape($datetime)."

        	WHERE employee_code = ".db_escape($employee_code)."AND 
                    payroll_run_id = ".db_escape($payroll_run_id)."AND
                    component=".db_escape($salary_component) ;

	db_query($sql,"Employee allowance usage could not be updated");
        return db_num_affected_rows();
        
}

function approve_employee_allowances_usage($employee_code, $salary_component, $payroll_run_id)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."employee_allowances_usage 
            SET status_code = ".db_escape(ATD_APPROVED).", 
                approved_by = ".db_escape($id_user).",
                approved_on = ".db_escape($datetime)."
        	WHERE payroll_run_id = ".db_escape($payroll_run_id)."AND
                employee_code = ".db_escape($employee_code)."AND 
                component=".db_escape($salary_component) ;

	db_query($sql,"Employee allowance usage could not be updated");        
}


?>