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

function add_leave_entitlement($employee_code, $leave_type_id, 
                                    $from_date, $to_date , 
                                    $leave_entitlement, $monthly_limit,
                                    $uninformed_leave_type, $comment)
{
        $leave_used = 0;
        $leave_payroll_deducted = 0;

        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."leave_entitlements (
                    employee_code, leave_type_id, 
                    from_date, to_date,
                    leave_entitlement, monthly_limit, 
                    leave_used, leave_payroll_deducted, 
                    uninformed_leave_type,
                    comment,
                    created_by, created_on) 
                VALUES ("
                    .db_escape($employee_code).", ".db_escape($leave_type_id). ", "
                    .db_escape($from_date).", ".db_escape($to_date). ", "
                    .db_escape($leave_entitlement).", ".db_escape($monthly_limit). ", "
                    .db_escape($leave_used). ", ".db_escape($leave_payroll_deducted).", "
                    .db_escape($uninformed_leave_type). ", "
                    .db_escape($comment). ", "
                    .db_escape($created_by).", ".db_escape($created_on).")";

	
        
        if($uninformed_leave_type == 1){
            begin_transaction();
        }
        
        db_query($sql,"Leave entitlement could not be added");
        
        if($uninformed_leave_type == 1){
            $rec_id = db_insert_id();
            $sql2 = "UPDATE ".TB_PREF."leave_entitlements 
                SET 
                    uninformed_leave_type = 0 
                WHERE 
                    id <> ".db_escape($rec_id)." AND 
                    employee_code =".db_escape($employee_code);
            
            db_query($sql2,"Leave entitlement absent leave types could not be updated");
            
            commit_transaction();
            
        }
}

function update_leave_entitlement($selected_id, $employee_code, 
                                    $from_date, $to_date , 
                                    $leave_entitlement, $monthly_limit, 
                                    $uninformed_leave_type, 
                                    $comment, $closed)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."leave_entitlements 
                SET 
                    from_date=".db_escape($from_date) . ",
                    to_date=".db_escape($to_date) . ",
                    leave_entitlement=".db_escape($leave_entitlement) . ",
                    monthly_limit=".db_escape($monthly_limit) . ",
                    comment=".db_escape($comment) . ",
                    uninformed_leave_type=".db_escape($uninformed_leave_type) . ",
                    closed=".db_escape($closed) . ",
                    updated_by=".db_escape($updated_by) . ",
                    updated_on=".db_escape($updated_on) . " 
		WHERE id=".db_escape($selected_id);
        
        if($uninformed_leave_type == 1){
            begin_transaction();
        }
        
        db_query($sql,"Leave entitlement could not be updated");
        
        if($uninformed_leave_type == 1){
            $sql2 = "UPDATE ".TB_PREF."leave_entitlements 
                SET 
                    uninformed_leave_type = 0 
                WHERE 
                    id <> ".db_escape($selected_id)." AND 
                    employee_code =".db_escape($employee_code);
            
            db_query($sql2,"Leave entitlement absent leave types could not be updated");
            
            commit_transaction();
            
        }
        
	
}

function delete_leave_entitlement($selected_id)
{
    
	$sql = "DELETE FROM ".TB_PREF."leave_entitlements WHERE id=".db_escape($selected_id);
        
	db_query($sql,"could not leave entitlement");
}

function get_leave_entitlements($employee_code)
{
    $sql = "SELECT le.*, lv.value_name leave_type_name 
            FROM 
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lv   
            WHERE 
		le.leave_type_id = lv.id AND 
                le.closed = 0 AND 
                le.employee_code = ".db_escape($employee_code);

    $sql .= " ORDER By lv.value_name, le.from_date" ;
                    
    return db_query($sql, "Leave entitlements could not be retrieved");
}

function get_leave_entitlement($selected_id)
{
    $sql = "SELECT le.*, lv.value_name leave_type_name 
            FROM 
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lv   
            WHERE 
		le.leave_type_id = lv.id AND 
                le.id = ".$selected_id ;

    $result = db_query($sql, "Leave entitlement could not be retrieved");

    return db_fetch($result);
}



function get_existing_leave_entitlement($employee_code,$leave_type_id,$from_date, $to_date,$selected_id)
{
    $sql = "SELECT leave_type_id 
            FROM 
                ".TB_PREF."leave_entitlements  
            WHERE 
		leave_type_id = ".db_escape($leave_type_id)." AND 
                employee_code = ".db_escape($employee_code)." AND 
                ((".db_escape($from_date)." <= from_date AND from_date <= ".db_escape($to_date).") OR 
                (".db_escape($from_date)." <= to_date AND to_date <= ".db_escape($to_date)."))";
    
    if($selected_id > 0)
    {
        $sql .= " AND id <> ".$selected_id;
    }

    $result = db_query($sql, "Existing leave entitlement could not be retrieved");

    return db_fetch($result);
}

function update_leave_scheduled($selected_id,   
                                    $leave_scheduled)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."leave_entitlements 
                SET 
                    leave_scheduled=".db_escape($leave_scheduled) . ",
                    updated_by=".db_escape($updated_by) . ",
                    updated_on=".db_escape($updated_on) . " 
		WHERE id=".db_escape($selected_id);
        
	db_query($sql,"Scheduled leave could not be updated");
}


function check_leave_entitlement_used($selected_id){
        
    $sql = "SELECT leave_entitlement_id 
            FROM 
                ".TB_PREF."leaves  
            WHERE leave_entitlement_id = ".db_escape($selected_id)."  
                LIMIT 1";

    $result = db_query($sql, "Used leave entitlement could not be retrieved");

    return db_fetch($result);
}

function update_leave_entitlement_leave_used($leave_entitlement_id, $leave_used)
{
    $sql = "UPDATE ".TB_PREF."leave_entitlements 
                        SET leave_used = ".  db_escape($leave_used)."   
                        WHERE id=".db_escape($leave_entitlement_id);
    
    db_query($sql, "Leave entitlement could not be update");
    
    return db_num_affected_rows();
    
}
 
?>