<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Report_model
 *
 * @author Ulaleka
 */
class Report_model extends CI_Model
{

	public function getEmpDetail($id)
	{
		try {
			$sql = "select emp.id,emp.empName,emp.designation,emp.empId,emp.department,duration.text "
				. "'duration' ,sys_users.name 'Supervisor' "
				. "from emp_info emp join duration on emp.duration=duration.id "
				. "join sys_users on sys_users.id=emp.Supervisor "
				. "where emp.empId='$id'";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function getEmpTaskList($id)
	{
		try {
			$sql = "SELECT `id`, `emp_id`, `activity`, `frequent`, `times`,  TIME_FORMAT(`hours`,'%H:%i') 
			as hours, `satisfaction`, `targetText`, `targetSatisfaction`, `targetMonitor`, `monitorFrequent`, `suggestion`, 
			 TIME_FORMAT(`sup_hr`,'%H:%i') as sup_hr, `sup_exp_tar`, `sup_value`, `sup_monitor`, `sup_monitor_frqu`, 
			 `sup_suggestion`,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(sup_hr)-TIME_TO_SEC(hours)),'%H:%i') 'discrepancy' ,TIME_TO_SEC(sup_hr) sup_hr_sec,TIME_TO_SEC(hours) hours_sec
			  FROM activity where emp_id = '$id' order by satisfaction";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function getSupExpec($id)
	{
		try {
			$sql = "SELECT * FROM supervisor_prediction where emp_id='$id'";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function distributionEmpVsHours($keys, $sup, $dep)
	{
		$whereIn = implode('","', $keys);
		try {
			if ($sup == "all" && $dep == "all") {
				$sql = 'SELECT emp_id,TIME_FORMAT(hours,"%H:%i") as hours FROM activity where activity in ("' . $whereIn . '") order by hours;';
			} elseif ($sup != "all" && $dep == "all") {
				$sql = 'SELECT ac.emp_id,TIME_FORMAT(ac.hours,"%H:%i") as hours FROM activity ac join emp_info emp on emp.empId=ac.emp_id where ac.activity  in ("' . $whereIn . '") and emp.Supervisor="' . $sup . '"  order by ac.hours;';
			} else if ($sup == "all" && $dep != "all") {
				$sql = 'SELECT ac.emp_id,TIME_FORMAT(ac.hours,"%H:%i") as hours FROM activity ac join emp_info emp on emp.empId=ac.emp_id where ac.activity  in ("' . $whereIn . '") and emp.department="' . $dep . '"  order by ac.hours;';
			} else {
				$sql = 'SELECT ac.emp_id,TIME_FORMAT(ac.hours,"%H:%i") as hours FROM activity ac join emp_info emp on emp.empId=ac.emp_id where ac.activity  in ("' . $whereIn . '") and emp.department="' . $dep . '" and  emp.Supervisor="' . $sup . '" order by ac.hours;';
			}
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function distributionEmpCountVsHours($keys, $sup, $dep)
	{
		$whereIn = implode('","', $keys);
		try {
			if ($sup == "all" && $dep == "all") {
				$sql = 'SELECT TIME_FORMAT(ac.hours,"%H:%i") as hours ,count(ac.id) as count from activity  ac   where activity in ("' . $whereIn . '")   group by ac.hours;';
			} elseif ($sup != "all" && $dep == "all") {
				$sql = 'SELECT TIME_FORMAT(ac.hours,"%H:%i") as hours ,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.Supervisor="' . $sup . '" group by ac.hours;';
			} else if ($sup == "all" && $dep != "all") {
				$sql = 'SELECT TIME_FORMAT(ac.hours,"%H:%i") as hours ,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.department="' . $dep . '" group by ac.hours;';
			} else {
				$sql = 'SELECT TIME_FORMAT(ac.hours,"%H:%i") as hours ,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.department="' . $dep . '" and  emp.Supervisor="' . $sup . '" group by ac.hours;';
			}
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function activityReporEmpList($keys, $sup, $dep, $hour)
	{
		$whereIn = implode('","', $keys);
		try {
			if ($sup == "all" && $dep == "all") {
				$sql = 'SELECT distinct emp.empName,emp.empId from activity  ac  join  emp_info emp on emp.empId=ac.emp_id  where activity in ("' . $whereIn . '")  and ac.hours="' . $hour . '";';
			} elseif ($sup != "all" && $dep == "all") {
				$sql = 'SELECT distinct emp.empName,emp.empId from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and ac.hours="' . $hour . '"  and emp.Supervisor="' . $sup . '" ;';
			} else if ($sup == "all" && $dep != "all") {
				$sql = 'SELECT distinct emp.empName,emp.empId from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '") and ac.hours="' . $hour . '"  and emp.department="' . $dep . '" ;';
			} else {
				$sql = 'SELECT distinct emp.empName,emp.empId from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '") and ac.hours="' . $hour . '"  and emp.department="' . $dep . '" and  emp.Supervisor="' . $sup . '" ;';
			}
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function activityReportStatisfaction($keys, $sup, $dep)
	{
		$whereIn = implode('","', $keys);
		try {
			if ($sup == "all" && $dep == "all") {
				$sql = 'SELECT ac.satisfaction,count(ac.id) as count from activity  ac   where activity in ("' . $whereIn . '")   group by ac.satisfaction order by ac.satisfaction';
			} elseif ($sup != "all" && $dep == "all") {
				$sql = 'SELECT ac.satisfaction,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.Supervisor="' . $sup . '" group by ac.satisfaction order by ac.satisfaction';
			} else if ($sup == "all" && $dep != "all") {
				$sql = 'SELECT ac.satisfaction,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.department="' . $dep . '" group by ac.satisfaction order by ac.satisfaction';
			} else {
				$sql = 'SELECT ac.satisfaction,count(ac.id) as count from activity  ac  join  emp_info emp on emp.empId=ac.emp_id where activity in ("' . $whereIn . '")  and emp.department="' . $dep . '" and  emp.Supervisor="' . $sup . '" group by ac.satisfaction order by ac.satisfaction;';
			}
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function departmentVsEmp()
	{
		try {
			$sql = "select department,count(empId) Count from emp_info group by department order by department ";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function departmentVsSup()
	{
		try {
			$sql = "SELECT department,count(id) Count FROM sys_users where userType='Supervisor' group by department order by department; ";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function organizationReportEmpList($dep)
	{
		try {
			$sql = "SELECT emp.empId,emp.empName,emp.designation,du.text as duration,sy.name as Supervisor, 
                    ROUND(avg(ac.satisfaction),2) satisfaction,count(ac.activity) activitycout  FROM emp_info emp 
                    join activity ac on emp.empId=ac.emp_id join sys_users sy on emp.Supervisor=sy.id 
                    join duration du on du.id=emp.duration   where emp.department='$dep'
                    group by emp.empId;";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

	public function departmentActList($dep)
	{
		try {
			$sql = "SELECT distinct activity.activity FROM activity join emp_info on emp_info.empId=activity.emp_id where emp_info.department='$dep';";
			$result = $this->db->query($sql)->result();
			$this->db->close();
			return $result;
		} catch (Exception $exc) {
			return [];
		}
	}

}
