<?php

function add_employee_rate_category($employee_code, $from_date, $emp_rate_category, $comment) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."employee_rate_categories (employee_code, from_date, emp_rate_category,  
                                                                comment, created_by, created_on) 
                VALUES  (".db_escape($employee_code).", ".db_escape(date2sql($from_date)).", ".db_escape($emp_rate_category).", 
                         ".db_escape($comment).", ".db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql, "add_employee_rate_category");
        return db_insert_id();
}

function update_employee_rate_category($selected_id, $from_date, $emp_rate_category, $comment) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."employee_rate_categories 
                SET from_date = ".db_escape(date2sql($from_date)).",
                    emp_rate_category = ".db_escape($emp_rate_category).",
                    comment = ".db_escape($comment).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)."
                WHERE id = ".db_escape($selected_id);

            db_query($sql, "update_employee_rate_category");
            return db_num_affected_rows();
}

function delete_employee_rate_category($id) {
    $sql = "DELETE 
            FROM ".TB_PREF."employee_rate_categories 
            WHERE id = ".db_escape($id);

	db_query($sql, "delete_employee_rate_category");
        return db_num_affected_rows();
}

function get_employee_rate_category($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."employee_rate_categories 
            WHERE id = ".db_escape($id);

	$result = db_query($sql, "get_employee_rate_category");
	return db_fetch($result);
}

function get_employee_rate_category_details($employee_code) {
    $sql = "SELECT er.*, lv.value_name rate_category_name 
            FROM ".TB_PREF."employee_rate_categories er 
                JOIN ".TB_PREF."list_values lv  
                    ON er.emp_rate_category = lv.id 
            WHERE er.employee_code = ".db_escape($employee_code)." 
            ORDER BY er.from_date DESC";
        
    return  db_query($sql, "get_employee_rate_category_details");
}

function get_employee_rate_category_on_date($employee_code, $date, $exact_date=false, $not_id=0) {
    $sql = "SELECT er.*, lv.value_name rate_category_name  
            FROM ".TB_PREF."employee_rate_categories er 
                JOIN ".TB_PREF."list_values lv  
                    ON er.emp_rate_category = lv.id 
            WHERE er.employee_code = ".db_escape($employee_code);
    
        if ($exact_date === false) {
            $sql .= " AND er.from_date <= ".db_escape(date2sql($date));
        } else {
            $sql .= " AND er.from_date = ".db_escape(date2sql($date));
        }
        
        if ($not_id > 0) {
            $sql .= " AND er.id <> ".db_escape($not_id);
        }
        
        $sql .= " ORDER BY er.from_date DESC
                    LIMIT 1";

	$result = db_query($sql, "get_employee_rate_category_on_date");
	return db_fetch($result);
}

?>