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

function add_leave_record($leave_entitlement_id, $employee_code, 
                                    $from_date, $leave_from_time_code, 
                                    $to_date ,$leave_to_time_code, 
                                    $leave_days, $comment, $status_code,
                                    $is_revoke,$leave_details)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."leaves (
                    leave_entitlement_id,employee_code,  
                    from_date, from_time_code,
                    to_date, to_time_code,
                    leave_days, comment,status_code,
                    is_revoke,
                    created_by, created_on) 
                VALUES ("
                    .$leave_entitlement_id.",".db_escape($employee_code).", "
                    .db_escape($from_date).", ".db_escape($leave_from_time_code). ", "
                    .db_escape($to_date).", ".db_escape($leave_to_time_code). ", "
                    .$leave_days.", ".db_escape($comment).", ".db_escape($status_code).", "
                    .$is_revoke.", "
                    .db_escape($created_by).", ".db_escape($created_on).")";

        begin_transaction();
        
        db_query($sql,"Leave record could not be added");
        
        $rec_id = db_insert_id();
        
        add_leave_details($rec_id, $leave_details, $created_by);
        
        commit_transaction();
        
        return $rec_id;

}


function add_leave_details($rec_id, $leave_details, $created_by)
{
        foreach($leave_details as $day_leave) 
        {
            $created_on = date('Y-m-d H:i:s');
            
            $sql = "INSERT INTO ".TB_PREF."leave_details (
                            leave_id, date, 
                            leave_days, leave_time_code, 
                            created_by, created_on) 
                    VALUES ("
                            .$rec_id.",".db_escape($day_leave[0]).", "
                            .db_escape($day_leave[1]).", ".db_escape($day_leave[2]).", "
                            .$created_by.",".db_escape($created_on).") ";
        
            db_query($sql,"Leave detail record could not be added");
            
        }
        
    
}

function get_leave_records($employee_code,$revoke = null, $exclude_rejected = false, $order_by_type = false)
{
    $sql = "SELECT l.*, 
                lt.value_name leave_type_name, 
                ls.value_name leave_status_name,
                ftc.value_name from_time_name,
                ttc.value_name to_time_name
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lt,
                ".TB_PREF."list_values ls,
                ".TB_PREF."list_values ftc,
                ".TB_PREF."list_values ttc
            WHERE 
		l.leave_entitlement_id = le.id AND 
                le.leave_type_id = lt.id AND 
                l.status_code = ls.value_code AND ls.list_code = 'ATTENDANCE_STATUS' AND 
                l.from_time_code = ftc.value_code AND ftc.list_code = 'WORKING_TIME' AND 
                l.to_time_code = ttc.value_code AND ttc.list_code = 'WORKING_TIME' AND 
                l.employee_code = ".db_escape($employee_code) ;
   
    if($revoke != null)
    {
        $sql .= " AND l.is_revoke = ".$revoke ;
    }
    if($exclude_rejected)
    {
        $sql .= " AND l.status_code <> 'J' ";
    }
    
    if($order_by_type)
    {
        $sql .= " ORDER BY  leave_type_name, from_date";
    }

    return db_query($sql, "Leave transactions could not be retrieved");
}

function get_entitlement_leave_records($leave_entitlement_id, $employee_code)
{
    $sql = "SELECT l.* 
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le 
            WHERE 
                l.leave_entitlement_id = le.id AND 
                le.id = ".db_escape($leave_entitlement_id)." AND 
                le.employee_code = ".db_escape($employee_code) ;

    return db_query($sql, "Leave entitlement leave records could not be retrieved");
}

function get_leave_details_by_leave_id($leave_id)
{
    $sql = "SELECT ld.* 
            FROM 
                ".TB_PREF."leave_details ld                 
            WHERE 
                ld.leave_id = ".db_escape($leave_id) ;

    return db_query($sql, "Leave details could not be retrieved");
}

function get_leave_details($employee_code,$date)
{
    $sql = "SELECT l.id leave_id, 
                l.employee_code,
                l.leave_entitlement_id,
                l.comment,
                l.status_code leave_status_code,
                l.created_by,
                l.created_on,
                ld.id,
                ld.date,
                ld.leave_days,
                ld.leave_time_code,
                ltime.value_name leave_time_name,
                ltype.value_name leave_type_name,
                ls.value_name leave_status_name,
                u.real_name 
            FROM 
                ".TB_PREF."leave_details ld,
                ".TB_PREF."leaves l,
                ".TB_PREF."list_values ltime,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values ltype,
                ".TB_PREF."list_values ls,
                ".TB_PREF."users u
            WHERE 
                ld.leave_id = l.id AND 
                ld.leave_time_code = ltime.value_code AND ltime.list_code = 'HOLIDAY_TIME' AND 
                l.leave_entitlement_id = le.id AND 
                le.leave_type_id = ltype.id AND 
                l.status_code = ls.value_code AND ls.list_code = 'ATTENDANCE_STATUS' AND 
                l.status_code NOT IN ('J') AND 
                l.created_by = u.id AND 
                l.employee_code = ".db_escape($employee_code)." AND 
                ld.date = ".db_escape($date);
    
        return db_query($sql, "Leave transactions could not be retrieved");
}



function get_leave_detail_records($leave_entitlement_id, $employee_code)
{
    $sql = "SELECT 
                ld.*, 
                l.id leave_id,
                l.is_revoke,
                l.status_code 
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."leave_details ld     
            WHERE 
                l.leave_entitlement_id = le.id AND 
                l.id = ld.leave_id AND 
                l.employee_code = ".db_escape($employee_code)." AND 
                l.leave_entitlement_id = ".db_escape($leave_entitlement_id)." 
            ORDER BY 
                l.approved_on";

    return db_query($sql, "Leavedetail records could not be retrieved");

}

function get_unapproved_leave_records()
{
    $sql = "SELECT 
                l.*, 
                le.leave_entitlement, le.leave_used,
                le.uninformed_leave_type,
                em.initials, em.first_name, em.last_name,
                lt.value_name leave_type_name, 
                ls.value_name leave_status_name,
                ft.value_name from_time_name,
                tt.value_name to_time_name
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lt,
                ".TB_PREF."list_values ls ,
                ".TB_PREF."list_values ft,
                ".TB_PREF."list_values tt ,
                ".TB_PREF."employee_master em   
            WHERE 
                l.leave_entitlement_id = le.id AND 
                le.leave_type_id = lt.id AND 
                em.employee_code = l.employee_code AND 
                l.from_time_code = ft.value_code AND ft.list_code = 'HOLIDAY_TIME' AND 
                l.to_time_code = tt.value_code AND  tt.list_code = 'HOLIDAY_TIME' AND 
                l.status_code = ls.value_code AND  ls.list_code = 'LEAVE_STATUS' AND 
                l.status_code IN ('N')" ;

    return db_query($sql, "Leave transactions could not be retrieved");
}

function reupdate_already_approved_leave($leave_id, $employee_code, $current_leave_nopay_days)
{
    //return;
    
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
          
        $sql = "UPDATE ".TB_PREF."leaves 
                SET nopay_days = ".db_escape($current_leave_nopay_days).",
                    approved_by = ".db_escape($updated_by).",
                    approved_on = ".db_escape($updated_on)."
                WHERE id=".db_escape($leave_id)." AND 
                    employee_code = ".db_escape($employee_code)." AND 
                    (status_code = 'A' OR status_code = 'L')";

        db_query($sql, "Leave record could not be updated");


}

function reopen_already_approved_leave($leave_id, $employee_code)
{
    //return;
    
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
          
        $sql = "UPDATE ".TB_PREF."leaves 
                SET status_code = ".db_escape('N').",
                    nopay_days = ".db_escape(0).",
                    approved_by = ".db_escape($updated_by).",
                    approved_on = ".db_escape($updated_on)."
                WHERE id=".db_escape($leave_id)." AND 
                    employee_code = ".db_escape($employee_code)." AND 
                    (status_code = 'A' OR status_code = 'L')";

        db_query($sql, "Leave record could not be updated");


}

function update_approved_leave($leave_id, $current_leave_nopay_days, $status, 
                                $leave_entitlement_id, $leave_used)
{
    //return;
    
        $approved_by = $_SESSION['wa_current_user']->user;
        $approved_on = date('Y-m-d H:i:s');
        
        $commit = false;
        begin_transaction();
        
        if($status == "A"){   
            $sql = "UPDATE ".TB_PREF."leaves 
                    SET status_code = ".db_escape($status).",  
                        nopay_days = ".db_escape($current_leave_nopay_days).",
                        approved_by = ".db_escape($approved_by).",
                        approved_on = ".db_escape($approved_on)."
                    WHERE id=".db_escape($leave_id)." AND 
                        status_code = 'N'";

            db_query($sql, "Leave record could not be updated");
        
            if(db_num_affected_rows() == 1){
                $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 record could not be updated");
                
                $commit = true;
            }
        }
        
        if($status == "J"){   
            $sql = "UPDATE ".TB_PREF."leaves 
                    SET status_code = ".db_escape($status).",  
                        approved_by = ".db_escape($approved_by).",
                        approved_on = ".db_escape($approved_on)."
                    WHERE id=".db_escape($leave_id)." AND 
                        status_code = 'N'";

            db_query($sql, "Leave record could not be updated");
            
            if(db_num_affected_rows() == 1){
                $commit = true;
            }
        }
        
        if($commit){
            commit_transaction();
        }
        else{
            cancel_transaction();
        }

}



function get_uninformed_leave_type($employee_code)
{
    $sql = "SELECT le.id 
            FROM ".TB_PREF."leave_entitlements le 
            WHERE 
                le.uninformed_leave_type = 1 AND 
                le.employee_code  = ".db_escape($employee_code);
    

    $result = db_query($sql, "Uninformed leave type could not be retrieved");
    
    return db_fetch($result);
}

function get_leave_record_by_id($id)
{
    $sql = "SELECT l.* 
            FROM ".TB_PREF."leaves l  
            WHERE 
                l.id = ".  db_escape($id) ;
    

    $result = db_query($sql, "Leave record could not be retrieved");
    
    return db_fetch($result);
}


function get_leave_by_id($id)
{
    $sql = "SELECT 
                l.*, 
                le.leave_entitlement, le.leave_used,
                em.employee_code, em.first_name, em.middle_name, em.last_name,
                lt.value_name leave_type_name, 
                ls.value_name leave_status_name,
                ft.value_name from_time_name,
                tt.value_name to_time_name
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lt,
                ".TB_PREF."list_values ls ,
                ".TB_PREF."list_values ft,
                ".TB_PREF."list_values tt ,
                ".TB_PREF."employee_master em   
            WHERE 
		l.leave_entitlement_id = le.id AND 
                le.leave_type_id = lt.id AND 
                em.employee_code = l.employee_code AND 
                l.from_time_code = ft.value_code AND ft.list_code = 'HOLIDAY_TIME' AND
                l.to_time_code = tt.value_code AND  tt.list_code = 'HOLIDAY_TIME' AND 
                l.status_code = ls.value_code AND  ls.list_code = 'ATTENDANCE_STATUS' AND 
                l.id = ".  db_escape($id) ;

    $result = db_query($sql, "Leave record could not be retrieved");
    
    return db_fetch($result);
}


function delete_leave($leave_id)
{
    begin_transaction();
    
    $sql = "DELETE FROM ".TB_PREF."leaves 
            WHERE id=".db_escape($leave_id)." AND 
                status_code = 'N'";
        
	db_query($sql,"Leave could not be deleted.");
    
    $sql = "DELETE FROM ".TB_PREF."leave_details 
            WHERE leave_id=".db_escape($leave_id);
        
	db_query($sql,"Leave details could not be deleted.");
    
    commit_transaction();
}


function delete_already_approved_leave($leave_id, $employee_code)
{
    begin_transaction();
    $leave = get_leave_by_id($leave_id);
    $leave_entitlement = get_leave_entitlement($leave['leave_entitlement_id']);
    
    $new_leave_used = $leave_entitlement['leave_used'] - $leave['leave_days'];
    $num_rows = update_leave_entitlement_leave_used($leave['leave_entitlement_id'], $new_leave_used);
    if($num_rows === 1){
    
        $sql = "DELETE FROM ".TB_PREF."leaves 
                WHERE id=".db_escape($leave_id)." AND 
                    employee_code = ".db_escape($employee_code)." AND 
                    (status_code = 'A' OR status_code = 'L')";

        db_query($sql,"Leave could not be deleted.");
        
        $num_rows2 = db_num_affected_rows();
        if($num_rows2 === 1){
            $sql = "DELETE FROM ".TB_PREF."leave_details 
                    WHERE leave_id=".db_escape($leave_id);

            db_query($sql,"Leave details could not be deleted.");

            commit_transaction();
        }
        else{
            cancel_transaction();
        }
    }
    else{
        cancel_transaction();
    }
}


function get_leaves_from_to($employee_code, $from_date, $to_date)
{
    $sql = "SELECT SUM(leave_days) no_of_leave_days 
            FROM ".TB_PREF."leaves 
            WHERE employee_code  = ".db_escape($employee_code)." AND 
                    	to_date >= ".db_escape($from_date)." AND 
                        to_date <= ".db_escape($to_date);   

    $result = db_query($sql, "Could not get_leaves_from_to");
    
    $leaves = db_fetch($result);
    
    if(isset($leaves['no_of_leave_days']) && $leaves['no_of_leave_days'] > 0){
        return $leaves['no_of_leave_days'];
    }
    else{
        return 0;
    }
}

?>