<?php

/**
 * Class that operate on table 'employee'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2014-03-09 11:49
 */
class EmployeeMySqlExtDAO extends EmployeeMySqlDAO
{

    public function queryAllExt()
    {
        $sql = 'SELECT * FROM employee where employeeDele=0 ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //query employees with no user group to show in add.html of userCTRL
    public function queryEmployeesWithNOusergroup()
    {
        $sql = 'SELECT * FROM employee WHERE employeeDele=0 AND  employeeId NOT IN
			(SELECT employeeId FROM user WHERE userDele=0)';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //query employee assigned to user and group specified by employeeId
    //and also other employees who haven't assigned to usergroups yet
    //to show in edit.html of userCTRL
    public function queryEmployees($employeeId)
    {
        $sql = 'SELECT employeeId, employeeName FROM employee WHERE employeeDele=0 AND (employeeId=' . $employeeId . '
				OR employeeId NOT IN
			(SELECT employeeId FROM user WHERE userDele=0))';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllemployee($queryString = '')
    {
        $sql = 'SELECT  employee.* FROM employee
		' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllemployeeworker()
    {
        $sql = 'SELECT  employee.* FROM employee
		 where isworker=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllemployeebycondation($conditions)
    {
        $sql = 'SELECT  employee.* FROM employee

		where conditions =' . $conditions . '
		';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllwithLimit($startpoint, $perpage)
    {
        $sql = 'SELECT  employee.* FROM employee
		  order by employeeDate desc, employeeId desc limit ' . $startpoint . ',' . $perpage . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllwithtname($name)
    {
        $sql = 'SELECT  employee.* FROM employee

		 where  employeeName like "%' . $name . '%" ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllwithLimitname($startpoint, $perpage, $name)
    {
        $sql = 'SELECT branch.branchname , employee.* FROM employee join branch
		on branch.branchid=employee.branchId  where employeeDele=0  and employeeName like "%' . $name . '%"   order by employeeDate desc, employeeId desc limit ' . $startpoint . ',' . $perpage . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateEmployee($employee)
    {
        $sql = 'UPDATE employee SET   employeeDate = ?, conditions = ?,  userId = ? WHERE employeeId = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->set($employee->employeeDate);
        $sqlQuery->setNumber($employee->conditions);

        $sqlQuery->setNumber($employee->userId);

        $sqlQuery->setNumber($employee->employeeId);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllemployeeSalary($from, $end)
    {
        //select employees which were employed before choosen month.
        $sql = 'SELECT `employeeId` ,employeeName
				FROM employee

					where  `employeeid` NOT IN (
                	   SELECT employeeid  FROM  `salaryreport` where salarymonthdate = "' . $from . '" and salaryreport.conditions =0  )
                	AND  conditions=0
					AND employeeDate <= "' . $end . '" order by employeeId desc ';


        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllemployeeSalarylimated($startpoint, $perpage, $start, $end, $branchId)
    {
        //print($startpoint);
        //print($perpage);
        $sql = 'SELECT  `employeeId` FROM employee WHERE branchId=' . $branchId . ' AND `employeeid` NOT
                           IN (
                           SELECT employeeid FROM  `salaryreport` where salarymonthdate  >= "' . $start . '" and  salarymonthdate <= "' . $end . '" )
						   				and  employeeDele=0  order by employeeId desc limit ' . $startpoint . ',' . $perpage . '';

        // print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getall($id)
    {
        $sql = 'SELECT employee.`employeeName` ,
		 employee.employeeBouns,
				 employee.empdept,
				employee.`employeeSalary` ,
				employee.`employeeId` ,

				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  `employeepersonneltype` =1
				and conditions=0 and done=0
				) as Reward,



				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  `employeepersonneltype` =2
				and conditions=0 and done=0
				) as Discount,

				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  `employeepersonneltype` =3
				and conditions=0 and done=0
				) as Withdraw


				,

				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  (`employeepersonneltype` =5 or `employeepersonneltype` =9)
				and conditions=0 and done=0
				) as Absence


				,

				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  `employeepersonneltype` =7
				and conditions=0 and done=0
				) as Repayment


                                ,

				(SELECT SUM(  `employeepersonnelvalue` )
				FROM  `employeepersonnel`
				WHERE  `employeeid` =' . $id . '
				AND  `employeepersonneltype` =11
				and conditions=0 and done=0
				) as permission



				FROM employee
				WHERE employee.`employeeId` =' . $id . '
				and employee.conditions=0
                GROUP BY employee.`employeeId`';
        //print("<br>".$sql."<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getrow($sqlQuery);
    }

    public function queryEmpIdInEmployeePersonnel($id)
    {
        $sql = 'SELECT employeeid FROM employeepersonnel WHERE employeeid=' . $id . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /* 	public function queryEmpIdInSalaryReport($id)
      {
      $sql = 'SELECT employeeid FROM salaryreport WHERE employeeid='.$id.'';
      $sqlQuery = new SqlQuery($sql);
      return $this->getList($sqlQuery);
      }
     */

    public function queryEmpIdInUser($id)
    {
        $sql = 'SELECT employeeId FROM user WHERE userDele=0 AND employeeId=' . $id . '';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getEmpIdWithFingerId($empFingerId)
    {
        $sql = 'SELECT employeeId FROM employee WHERE empFingerId = ' . $empFingerId . ' ';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getEmpIdWithRFID($empRFID)
    {
        $sql = 'SELECT employeeId FROM employee WHERE empRFID = ' . $empRFID . ' ';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function updatedept($employee)
    {
        $sql = 'UPDATE employee SET empdept = ? WHERE employeeId = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->setNumber($employee->empdept);


        $sqlQuery->setNumber($employee->employeeId);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllSimple($queryString)
    {
        $sql = 'SELECT employeeId, employeeName,employeePhone,employeeSalary,minuteLateCost,employeeBOD FROM employee WHERE conditions=0 ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllQueryString($queryString, $day)
    {
        $sql = 'SELECT *
                FROM employee
                join employeeclosedayhistory on (employeeclosedayhistory.employeeid =  employee.employeeId and employeeclosedayhistory.day = "' . $day . '")
                ' . $queryString . ' ';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getEmployeeCount($queryString = '')
    {
        $sql = 'SELECT count(*) FROM employee where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllString($startDate, $endDate,$isAbsent = 0)
    {
        $sql = 'SELECT *,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystem.attendanceTime,employeeattendancesystem.departureTime
                ,employeeclosedayhistory.sysdate
                FROM employee join employeegroup on employeegroup.id = employee.employeegroupid 
                join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid 
                join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
                join (SELECT employeeclosedayhistory.sysdate,employeeclosedayhistory.employeeid,employeeclosedayhistory.isAbsent 
                FROM employeeclosedayhistory where employeeclosedayhistory.isAbsent = '.$isAbsent.' group by employeeclosedayhistory.employeeid ORDER BY id DESC limit 1) employeeclosedayhistory 
                 on employeeclosedayhistory.employeeid = employee.employeeId
                 where employeeDate >= "' . $startDate . '" AND employeeDate <= "' . $endDate . '"
                ';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllByIdAndDate($queryString,$isAbsent)
    {
        $sql = 'SELECT *,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystem.attendanceTime,employeeattendancesystem.departureTime
                ,employeeclosedayhistory.sysdate,employeeclosedayhistory.isAbsent
                FROM employee join employeegroup on employeegroup.id = employee.employeegroupid 
                join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid 
                join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
                join (SELECT employeeclosedayhistory.sysdate,employeeclosedayhistory.employeeid,employeeclosedayhistory.isAbsent 
                FROM employeeclosedayhistory where employeeclosedayhistory.isAbsent = '.$isAbsent.' group by employeeclosedayhistory.employeeid ORDER BY id DESC limit 1) employeeclosedayhistory 
                 on employeeclosedayhistory.employeeid = employee.employeeId
                where -1 ' . $queryString . '
                ';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }


    public function queryAllReportAbsent($queryString)
    {
        $sql = 'SELECT *,employeegroup.name as groupName,employeesubgroup.name as supgroupName,
                employeegroup.employeeattendancesystemid
                FROM employee join employeegroup on employeegroup.id = employee.employeegroupid 
                join employeesubgroup on employeesubgroup.id = employee.employeesubgroupid 
                join employeeattendancesystem on employeeattendancesystem.id = employeegroup.employeeattendancesystemid
                where -1 ' . $queryString . '
                ';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllFromdayhistoty($id)
    {
        $sql = 'SELECT * FROM employeeclosedayhistory where employeeid = '.$id.' AND isAbsent = 1';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>