<?php
/**********************************************************************
***********************************************************************/
function update_employee_project($selected, $start_date_, $end_date_, $days_per_month, $calendar_working_days, $factor)
{
    $user = $_SESSION['wa_current_user']->user;
    $date = date('Y-m-d H:i:s');
    
    if($selected != '')
    {
        $sql = "UPDATE ".TB_PREF."employee_projects 
                SET
                    start_date=".db_escape(date2sql($start_date_)).",
                    end_date=".db_escape(date2sql($end_date_)).",
                    days_per_month = ".db_escape($days_per_month).",
                    calendar_working_days = ".db_escape($calendar_working_days).",
                    factor = ".db_escape($factor).",
                    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, $boq_item_id, $start_date_, $end_date_, $days_per_month, $calendar_working_days, $factor)
{
    $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, boq_item_id, 
                        start_date, end_date, 
                        days_per_month,  
                        calendar_working_days, factor,
                        created_by, created_on) 
                VALUES
                        (".db_escape($company_code).",".db_escape($employee_code).","
                        .db_escape($project_id).",".db_escape($boq_item_id).","
                        .db_escape(date2sql($start_date_)).",".db_escape(date2sql($end_date_)).","  
                        .db_escape($days_per_month).","
                        .db_escape($calendar_working_days).",".db_escape($factor).","
                        .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, "delete_employee_project");
        return db_num_affected_rows();
}

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,
                    boq.code boq_item_code,
                    boq.description boq_item_name  
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."projects_master pm 
                    ON ep.project_id = pm.project_id 
                LEFT JOIN ".TB_PREF."project_boq_items boq   
                    ON ep.boq_item_id = boq.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 
                LEFT 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  
                LEFT 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  
                LEFT 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);
}

function get_employee_project_view_detail($employee_code, $project_id, $from_date, $to_date) {
    $sql = "SELECT ep.*,
                pm.start_date project_start_date,
                pm.end_date project_end_date,
                dp.value_name department_name,  
                bq.code boq_item_code,
                bq.description boq_item_name 
            FROM ".TB_PREF."employee_projects ep  
                JOIN ".TB_PREF."projects_master pm  
                    ON pm.project_id = ep.project_id  
                JOIN ".TB_PREF."list_values dp  
                    ON pm.department_id = dp.id 
                LEFT JOIN ".TB_PREF."project_boq_items bq 
                    ON ep.boq_item_id = bq.id   
            WHERE dp.list_code = ".db_escape('DEPARTMENTS')." 
                AND ((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 ($project_id > 1) {
            $sql .= " AND ep.project_id = ".db_escape($project_id);
        }
        
        $sql .= " ORDER BY ep.start_date";
    
        return db_query($sql, "get_employee_project_view_detail");
}

function get_employee_projects_by_from_and_date_dates($from_date, $to_date, $employee_code='', $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_projects 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 ($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_projects_by_from_and_date_dates");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function create_employee_projects_array($employee_code, $from_date, $to_date) {
    $employee_projects_array = array();
    
        $result = get_employee_projects_by_from_and_date_dates($from_date, $to_date, $employee_code);
            while ($row = db_fetch($result)) {
                $id = $row['id'];
                $project_name = get_project_formatted_name($row['project_id']);
                $boq_i_name = $row['boq_item_code'].' - '.$row['boq_item_name'];
                $days_assigned = $row['days_per_month'];
                $start_date_ = sql2date($row['start_date']);
                $end_date_ = sql2date($row['end_date']);
                
                $employee_projects_array[$id] = "$id. $project_name - $boq_i_name - [Start Date:$start_date_ - End Date:$end_date_] - Days Assigned: $days_assigned";
            }

            return $employee_projects_array;
}

function update_employee_project_days_worked($id) {
    $user = $_SESSION['wa_current_user']->user;
    $date = date('Y-m-d H:i:s');
    
    $total_days_worked = get_employee_project_work_total_worked_days_by_employee_project($id);
    
        $sql = "UPDATE ".TB_PREF."employee_projects 
                SET days_worked = ".db_escape($total_days_worked).",
                    updated_by = ".db_escape($user).",
                    row_state = row_state + 1,
                    updated_on = ".db_escape($date)."
                WHERE id = ".db_escape($id);
        
        db_query($sql, "update_employee_project_days_worked");
        return db_num_affected_rows();
}


function get_employee_projects_by_filters($id=0, $from_date=null, $to_date=null, $employee_code='', $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_projects ep 
                LEFT JOIN ".TB_PREF."project_boq_items bq 
                    ON ep.boq_item_id = bq.id 
            WHERE 1 = 1";
        
        if ($id > 0) {
            $sql .= " AND ep.id = ".db_escape($id);
        }
        
        if ($from_date !== null && $from_date !== null) {
            $sql .= " AND (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 ($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_projects_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_employee_project_boq_groups_and_item_details($employee_code, $project_id) {
    $sql = "SELECT g.id boq_group_id, g.code boq_group_code, g.description boq_group_description, g.depth, g.position,
                tg.id boq_item_id, tg.code boq_item_code, tg.description boq_item_description, tg.start_date, tg.end_date,
                bi.id boq_item_id, bi.code, bi.description, ep.calendar_working_days, ep.factor, ep.days_worked 
                
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."project_boq_items bi 
                    ON ep.boq_item_id = bi.id 
                JOIN ".TB_PREF."project_task_groups tg  
                    ON tg.id = bi.id  
                JOIN ".TB_PREF."project_boq_item_groups g  
                    ON tg.boq_group_id = g.id  
            WHERE ep.employee_code = ". db_escape($employee_code)." 
                AND g.project_id = ". db_escape($project_id);
        
	return db_query($sql, "get_employee_project_boq_groups_and_item_details");
}

function get_employee_project_boq_items_total_calendar_working_days_and_total_worked_days($employee_code, $project_id) {
    $total_qty_and_total_complated_qty = array();
    
        $sql = "SELECT SUM(pbi.max_quantity) total_qty  
                FROM ".TB_PREF."project_boq_items                      
                WHERE project_id = ".db_escape($project_id);

            $result = db_query($sql, "get_project_boq_item_total_qty_and_total_completed_qty");	
            $row = db_fetch($result);

            if (isset($row['total_qty'])) {
                $total_qty_and_total_complated_qty['total_qty'] = $row['total_qty'];
            } else {
                $total_qty_and_total_complated_qty['total_qty'] = 0;
            }
            
            
        $sql = "SELECT SUM(pbi.completed_qty) total_completed_qty 
                FROM ".TB_PREF."project_boq_items pbi                     
                        JOIN ".TB_PREF."project_task_groups ptg 
                            ON pbi.id = ptg.id 
                    WHERE pbi.project_id = ".db_escape($project_id);

            $result_2 = db_query($sql, "get_project_boq_item_total_qty_and_total_completed_qty");	
            $row_2 = db_fetch($result_2);

            if (isset($row_2['total_completed_qty'])) {
                $total_qty_and_total_complated_qty['total_completed_qty'] = $row_2['total_completed_qty'];
            } else {
                $total_qty_and_total_complated_qty['total_completed_qty'] = 0;
            }

                    return $total_qty_and_total_complated_qty;
}