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

function add_employee_project_work($company_code, $employee_code, $project_id, $boq_item_id, $month_id, $employee_project_id, $start_date, $end_date, $no_of_days, $comment) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
	$sql = "INSERT INTO ".TB_PREF."employee_project_work (
                    company_code, employee_code, project_id, 
                    month_id, employee_project_id, start_date, 
                    end_date, no_of_days, comment,
                    boq_item_id, created_by, created_on) 
                VALUES ("
                    .db_escape($company_code).", ".db_escape($employee_code). ", ".db_escape($project_id). ", "
                    .db_escape($month_id).", ".db_escape($employee_project_id). ", ".db_escape($start_date).", "
                    .db_escape($end_date). ", ".db_escape($no_of_days). ", ".db_escape($comment). ", "
                    .db_escape($boq_item_id).", ".db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"add_employee_project_work");
        return db_insert_id();
}

function update_employee_project_work($selected_id, $start_date, $end_date, $no_of_days, $comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."employee_project_work 
                SET 
                    start_date=".db_escape($start_date) . ",
                    end_date=".db_escape($end_date) . ",
                    no_of_days=".db_escape($no_of_days) . ",
                    comment=".db_escape($comment) . ",
                    row_state = row_state + 1,
                    updated_by=".db_escape($updated_by) . ",
                    updated_on=".db_escape($updated_on) . " 
		WHERE id=".db_escape($selected_id);
        
        db_query($sql,"update_employee_project_work");
        return db_num_affected_rows();
}

function delete_employee_project_work($selected_id)
{
    
	$sql = "DELETE FROM ".TB_PREF."employee_project_work WHERE id=".db_escape($selected_id);
        
	db_query($sql,"delete_employee_project_work");
        return db_num_affected_rows();
}

function get_employee_month_project_work($company_code, $employee_code, $month_id, $employee_project_id)
{
    $sql = "SELECT epw.*,
                    pm.description project_description,  
                    bq.code boq_item_code, bq.description boq_item_name  
            FROM ".TB_PREF."employee_project_work epw 
                JOIN ".TB_PREF."projects_master pm  
                    ON epw.project_id = pm.project_id 
                LEFT JOIN ".TB_PREF."project_boq_items bq 
                    ON epw.boq_item_id = bq.id 
            WHERE epw.company_code = ".db_escape($company_code)." 
                AND epw.employee_code = ".db_escape($employee_code)." 
                AND epw.month_id = ".db_escape($month_id)." 
                AND epw.employee_project_id = ".db_escape($employee_project_id);

    $sql .= " ORDER By epw.id" ;
                    
    return db_query($sql, "get_employee_month_project_work");
}

function get_employee_project_work($selected_id)
{
    $sql = "SELECT * 
            FROM 
                ".TB_PREF."employee_project_work  
            WHERE id = ".$selected_id ;

    $result = db_query($sql, "get_employee_project_work");

    return db_fetch($result);
}


function get_existing_employee_project_work($company_code, $employee_code, $from_date, $to_date, $selected_id)
{
    $sql = "SELECT *
            FROM 
                ".TB_PREF."employee_project_work  
            WHERE 
		company_code = ".db_escape($company_code)." 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, "get_existing_employee_project_work");

    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."employee_project_work 
                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_employee_project_work_used($selected_id){
        
    $sql = "SELECT employee_project_work_id 
            FROM 
                ".TB_PREF."leaves  
            WHERE employee_project_work_id = ".db_escape($selected_id)."  
                LIMIT 1";

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

    return db_fetch($result);
}

function update_employee_project_work_leave_used($employee_project_work_id, $leave_used)
{
    $sql = "UPDATE ".TB_PREF."employee_project_work 
                        SET leave_used = ".  db_escape($leave_used)."   
                        WHERE id=".db_escape($employee_project_work_id);
    
    db_query($sql, "Leave entitlement could not be update");
    
    return db_num_affected_rows();
    
}
 
function get_employee_project_work_total_worked_days_by_employee_project($employee_project_id) {
    $sql = "SELECT SUM(no_of_days) total_days 
            FROM ".TB_PREF."employee_project_work  
            WHERE employee_project_id = ".db_escape($employee_project_id);

        $result = db_query($sql, "get_employee_project_work_total_worked_days_by_employee_project");
        $row = db_fetch($result);
        
        if (isset($row['total_days']) && $row['total_days'] > 0) {
            return $row['total_days'];
        } else {
            return 0;
        }
}
 
// if want add more filters
function get_employee_project_works_by_filters($id=0, $employee_code='', $project_id=0, $month_id=0, $employee_project_id=0, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."employee_project_work  
            WHERE 1 = 1";
        
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
    
        if (strlen($employee_code) > 0) {
            $sql .= " AND employee_code = ".db_escape($employee_code);
        }
        
        if ($project_id > 0) {
            $sql .= " AND project_id = ".db_escape($project_id);
        }
        
        if ($month_id > 0) {
            $sql .= " AND month_id = ".db_escape($month_id); 
        }
        
        if ($employee_project_id > 0) {
            $sql .= " AND employee_project_id = ".db_escape($employee_project_id);
        }

        $result = db_query($sql, "get_employee_project_works_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_employee_project_works_by_from_and_date_dates($from_date, $to_date, $employee_code='', $employee_project_id=0, $project_id=0, $boq_item_id=0, $not_id=0, 
                                                           $fetch=false) {
    $sql = "SELECT ep.*, bq.code boq_item_code, bq.description boq_item_name  
            FROM ".TB_PREF."employee_project_work ep 
                LEFT JOIN ".TB_PREF."project_boq_items bq 
                    ON ep.boq_item_id = bq.id 
            WHERE ((ep.end_date >= ".db_escape($from_date)." AND ep.start_date <= ".db_escape($to_date).") 
                    OR (ep.end_date >= ".db_escape($to_date)." AND ep.start_date <= ".db_escape($from_date)."))";
    
        if (strlen($employee_code) > 1) {
            $sql .= " AND ep.employee_code = ".db_escape($employee_code);
        }
        
        if ($employee_project_id > 0) {
            $sql .= " AND ep.employee_project_id = ".db_escape($employee_project_id);
        }
    
        if ($project_id > 0) {
            $sql .= " AND ep.project_id = ".db_escape($project_id);
        }
    
        if ($boq_item_id > 0) {
            $sql .= " AND ep.boq_item_id = ".db_escape($boq_item_id);
        }
        
        if ($not_id > 0) {
            $sql .= " AND ep.id <> ".db_escape($not_id);
        }
        
        $sql .= " ORDER BY ep.start_date";
        
        $result = db_query($sql, "get_employee_project_works_by_from_and_date_dates");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

?>