<?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, $queryString = '') {
        $sql = 'SELECT  employee.* FROM employee

		where conditions =' . $conditions . ' ' . $queryString;
        $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   sysdate = ?, conditions = ?,  userId = ? WHERE employeeId = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->set($employee->sysdate);
        $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,$qs='') {
        $sql = 'SELECT employee.`employeeName` ,
		 employee.employeeBouns,
				 employee.empdept,
				employee.`employeeSalary` ,
				employee.`employeeId` ,

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



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

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


				,

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


				,

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


                                ,

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


				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, $queryString = '', $isAbsent = 0) {
        $sql = 'SELECT employee.*,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystemweek.attendanceTime,employeeattendancesystemweek.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 employeeattendancesystemweek on (employeeattendancesystem.id = employeeattendancesystemweek.employeeattendancesystem_id AND employeeattendancesystemweek.employee_id = employee.employeeId)
                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 . '" ' . $queryString;
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllByIdAndDate($queryString, $isAbsent) {
        $sql = 'SELECT employee.*,employeegroup.name as groupName,employeesubgroup.name as supgroupName,employeegroup.employeeattendancesystemid,employeeattendancesystemweek.attendanceTime,employeeattendancesystemweek.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 employeeattendancesystemweek on (employeeattendancesystem.id = employeeattendancesystemweek.employeeattendancesystem_id AND employeeattendancesystemweek.employee_id = employee.employeeId)
                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);
    }

    public function queryAllwithtnameAndQuerystring($name, $queryString) {
        $sql = 'SELECT  employee.* FROM employee

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

    public function queryAllemployeeSalaryWithQueryString($from, $end, $queryString = '') {
        //select employees which were employed before choosen month.
        $sql = 'SELECT `employeeId`,employeeSalary,employeeName,employeeName,treeId,treeId2,socialInsurance,workTax
				FROM employee

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


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

}
