<?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 employeepersonneldate <= "' . $end . '"
        and employeeid=' . $employeeid . ' AND done = 0';
        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatedonDel($start, $end, $don, $employeeid, $userid, $salarymonth)
    {
        $sql = 'UPDATE employeepersonnel SET done = ' . $don . ' ,  userid = ' . $userid . ' WHERE  employeeid=' . $employeeid . ' AND salarymonth ="' . $salarymonth . '"';
        //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);
    }
}