<?php
/**********************************************************************
***********************************************************************/
function update_employee_project($selected, $project_id, $rate_category_id, $start_date_, $end_date_, $days_per_month)
{
    $user = $_SESSION['wa_current_user']->user;
    $date = date('Y-m-d H:i:s');
    
    if($selected != '')
    {
        $sql = "UPDATE ".TB_PREF."employee_projects 
                SET
                    project_id = ".db_escape($project_id).",
                    rate_category_id = ".db_escape($rate_category_id).",
                    start_date=".db_escape(date2sql($start_date_)).",
                    end_date=".db_escape(date2sql($end_date_)).",
                    days_per_month = ".db_escape($days_per_month).",
                    updated_by = ".db_escape($user).",
                    updated_on = ".db_escape($date)."
                WHERE 
                    id = ".db_escape($selected);
        
        db_query($sql, "update_employee_project");
    }
}

function add_employee_project($company_code, $employee_code, $project_id, $rate_category_id, $start_date_, $end_date_, $days_per_month)
{
    $user = $_SESSION['wa_current_user']->user;
    $date = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."employee_projects
                        (company_code, employee_code, 
                        project_id, rate_category_id, 
                        start_date, end_date, 
                        days_per_month,  
                        created_by, created_on) 
                VALUES
                        (".db_escape($company_code).",".db_escape($employee_code).","
                        .db_escape($project_id).",".db_escape($rate_category_id).","
                        .db_escape(date2sql($start_date_)).",".db_escape(date2sql($end_date_)).","  
                        .db_escape($days_per_month).","
                        .db_escape($user).",".db_escape($date).")";
        
        db_query($sql, "add_employee_project");    
}
////
function delete_employee_project($id)
{
	$sql="DELETE FROM ".TB_PREF."employee_projects 
                    WHERE 
                        id=".db_escape($id);

	db_query($sql,"Selected employee project could not be deleted");
}
////
function get_employee_project($id)
{
	$sql="SELECT * FROM ".TB_PREF."employee_projects 
                WHERE 
                    id=".db_escape($id);

	$result = db_query($sql,"Selected employee project could not be retrieved");
	return db_fetch($result);
}

function get_employee_all_projects($company_code, $employee_code) {
    $sql = "SELECT ep.*,
                    pm.description project_description,
                    er.description rate_category_name,
                    er.rate 
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."projects_master pm 
                    ON ep.project_id = pm.project_id 
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
            ORDER BY ep.start_date";
        
    return  db_query($sql, "get_employee_all_projects");
}

function get_employee_curent_projects($employee,$to_date)
{
        $sql = "SELECT  ed.*, 
                    IFNULL(d.value_name,'No Department Entered') project_name                    
            FROM ".TB_PREF."employee_projects ed 
                    INNER JOIN ".TB_PREF."employee_master em ON 
                            ed.employee_code = em.employee_code 
                    INNER JOIN ".TB_PREF."list_values d ON 
                            ed.project_id = d.id                     
                    WHERE ed.start_date <= ".db_escape($to_date)." AND 
                          ed.end_date >= ".db_escape($to_date)." AND
                          ed.employee_code =". db_escape($employee)." 
                    ORDER BY ed.start_date DESC LIMIT 1";
        
        $result=db_query($sql,"an employee could not be retreived");
        return db_fetch($result);
    
    
}

function get_emplyee_default_projects($company_code, $employee_code, $from_date)
{
    $sql = "SELECT ep.*,
                    er.rate 
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
                AND ep.start_date >= ".  db_escape($from_date)." 
            ORDER BY ep.start_date";
        
    return  db_query($sql, "get_emplyee_default_projects");
}

function get_emplyee_default_project_details($company_code, $employee_code, $from_date)
{
    $sql = "SELECT ep.*,
                    pm.description project_description,
                    er.description rate_category_name,
                    er.rate  
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = ep.project_id  
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
                AND ep.start_date >= ".  db_escape($from_date)." 
            ORDER BY ep.start_date";
    
    return  db_query($sql, "get_emplyee_default_projects");
}

function get_emplyee_default_previous_project_details($company_code, $employee_code, $from_date)
{
    $sql = "SELECT ep.*,
                    pm.description project_description,
                    er.description rate_category_name,
                    er.rate 
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = ep.project_id  
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
                AND ep.start_date < ".  db_escape($from_date)." 
            ORDER BY ep.start_date DESC LIMIT 1";
    
    $result = db_query($sql, "get_emplyee_default_previous_project_details");
    return db_fetch($result);
}