<?php

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

    public function queryAllEX($queryString) {
        $sql = 'SELECT employeepersonnelid, `employeeName` ,  `employeepersonnelvalue` ,  `employeepersonneldate` ,  `employeepersonneltype` ,employeepersonnelnote
		FROM employeepersonnel
		JOIN employee
		on employeepersonnel.employeeid=employee.employeeId
		where employeepersonnel.conditions=0
		' . $queryString . '


		AND employee.`employeeId` = employeepersonnel.employeeId order BY employeepersonnelid DESC';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXWithDeleted($queryString) {
        $sql = 'SELECT employeepersonnelid, `employeeName` , employeepersonnel.employeeid, `employeepersonnelvalue` ,  `employeepersonneldate` ,  `employeepersonneltype` ,employeepersonnelnote, employeepersonnel.conditions
		FROM employeepersonnel
		JOIN employee
		on employeepersonnel.employeeid=employee.employeeId
		where 1
		' . $queryString . '


		AND employee.`employeeId` = employeepersonnel.employeeId order BY employeepersonnelid DESC';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXWithDeleted2($queryString, $queryString2) {
        $sql = '(SELECT employeepersonnelid, `employeeName` , employeepersonnel.employeeid, `employeepersonnelvalue` ,  `employeepersonneldate` ,  `employeepersonneltype` ,employeepersonnelnote, employeepersonnel.conditions,employeepersonnel.done
		FROM employeepersonnel
		JOIN employee
		on employeepersonnel.employeeid=employee.employeeId
		where 1
		' . $queryString . '

		AND employee.`employeeId` = employeepersonnel.employeeId

                )union(

                SELECT 0 as employeepersonnelid,employee.employeeName,salaryreport.employeeid,salaryreport.salaryreportnet,salaryreport.salaryreportdate as employeepersonneldate,10 as employeepersonneltype,"" as employeepersonnelnote,salaryreport.conditions,0 as done
                FROM salaryreport
		join employee
		on employee.employeeId=salaryreport.employeeId

		' . $queryString2 . ')


                order BY employeepersonneldate DESC';
      
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXsum($queryString, $employeepersonneltype) {
        $sql = 'SELECT employeepersonnelid, `employeeName` ,  sum(`employeepersonnelvalue`) as total ,employeepersonnel.employeeid
		FROM employeepersonnel
		JOIN employee
		on employeepersonnel.employeeid=employee.employeeId
		where employeepersonnel.conditions=0 and
		employeepersonnel.employeepersonneltype = ' . $employeepersonneltype . '
		' . $queryString . '


		AND employee.`employeeId` = employeepersonnel.employeeId group BY employeepersonnel.employeeid ';
        //print($sql.'<br><br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function updatedel($employeepersonnel) {
        $sql = 'UPDATE employeepersonnel SET done = ?,  conditions = ?, userid = ?  WHERE employeepersonnelid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($employeepersonnel->done);

        $sqlQuery->setNumber($employeepersonnel->conditions);
        $sqlQuery->setNumber($employeepersonnel->userid);


        $sqlQuery->setNumber($employeepersonnel->employeepersonnelid);
        return $this->executeUpdate($sqlQuery);
    }

    public function loadEX($id) {
        $sql = 'SELECT employeepersonnel.* , `employeeName`  FROM employeepersonnel
		JOIN employee
		on employeepersonnel.employeeid=employee.employeeId

		 WHERE employeepersonnelid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function updatedon($start, $end, $don, $employeeid, $userid, $salarymonth) {
        $sql = 'UPDATE employeepersonnel SET done = ' . $don . ' ,  userid = ' . $userid . '  ,salarymonth ="' . $salarymonth . '" WHERE  employeeid=' . $employeeid . '
		';


        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);


        return $this->executeUpdate($sqlQuery);
    }

    //fatma
    public function updatedonDateFromTo($start, $end, $don, $employeeid, $userid, $salarymonth) {
        $sql = 'UPDATE employeepersonnel SET done = ' . $don . ' ,  userid = ' . $userid . '  ,salarymonth ="' . $salarymonth . '" WHERE  employeeid=' . $employeeid . ' and employeepersonneldate  >= "' . $start . '" and  employeepersonneldate <= "' . $end . '"
		';


        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);


        return $this->executeUpdate($sqlQuery);
    }

    public function updatedonold($start, $end, $don, $employeeid, $userid, $salarymonth) {
        $sql = 'UPDATE employeepersonnel SET done = ' . $don . ' ,  userid = ' . $userid . '  ,salarymonth ="' . $salarymonth . '" WHERE employeepersonneldate  >= "' . $start . '" and  employeepersonneldate <= "' . $end . '"

		and employeeid=' . $employeeid . '
		';


        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);


        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllbydate($startDate, $endDate) {
        $sql = 'SELECT * FROM employeepersonnel

		where conditions=0
		and employeepersonneltype in(3,4,7)
		and done = 1
		  and employeepersonneldate  >= "' . $startDate . '" AND employeepersonneldate <= "' . $endDate . '"
		';



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

    public function queryAllbydateforreport($startDate, $endDate) {
        $sql = 'SELECT * FROM employeepersonnel

		where conditions=0
		and employeepersonneltype in(3,4,6)

		  and employeepersonneldate  >= "' . $startDate . '" AND employeepersonneldate <= "' . $endDate . '"
		';



        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }
    
    public function getMandobRewards($empid,$emDateFrom,$emDateTo) {
        $sql = 'SELECT * 
            FROM employeepersonnel
            where conditions=0
            and employeepersonneltype = 1
            and isMandobReward = 1
            and employeeid = '.$empid.'
            and date(employeepersonneldate) >= "'.$emDateFrom.'"
            and date(employeepersonneldate) <= "'.$emDateTo.'"
            ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }
    
    public function queryByEmpDayTypeValue($empid,$empValue,$emDate,$type) {
        $sql = 'SELECT * 
            FROM employeepersonnel
            where conditions=0
            and employeeid = '.$empid.'
            and employeepersonnelvalue = '.$empValue.'
            and date(employeepersonneldate) = "'.$emDate.'"
            and employeepersonneltype = '.$type.'';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function countEmployeePersonnel($empid,$emDate) {
        $sql = 'SELECT * 
            FROM employeepersonnel
            where conditions = 0
            and employeeid = '.$empid.'
            and date(employeepersonneldate) = "'.$emDate.'"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function countEmployeePersonnelbefore($empid,$emDate) {
        $sql = 'SELECT * 
            FROM employeepersonnel
            where conditions = 0
            and employeeid = '.$empid.'
            and employeepersonneldate = "'.$emDate.'"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

}

?>