<?php

/**
 * Class that operate on table 'user'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2013-04-06 10:57
 */
class UserMySqlExtDAO extends UserMySqlDAO {

    public function queryFirstOne() {
        $sql = 'SELECT * FROM user Limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getUserCount() {
        $sql = 'SELECT count(userid) FROM user';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function validationUser($username, $password) {

        $sql = 'SELECT user.* , usergroup.usergroupname
		FROM user join usergroup
		on user.usergroupid = usergroup.usergroupid
		 WHERE username = "' . $username . '" AND password =  "' . $password . '" and user.conditions=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function validationUserByUserNameOnly($username) {

        $sql = 'SELECT user.* , usergroup.usergroupname
		FROM user join usergroup
		on user.usergroupid = usergroup.usergroupid
		 WHERE username = "' . $username . '" and user.conditions=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllByUserNameAndUserId($username, $userid) {
        $sql = 'SELECT * FROM user where username = "' . $username . '" and userid !=' . $userid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithoutSessionUser($userid) {
        $sql = 'SELECT user.*, usergroup.usergroupname
		 FROM user JOIN usergroup
		 ON user.usergroupid = usergroup.usergroupid
		 where userid !=' . $userid . ' and usergroup.usergroupid > 0 order by userid asc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithoutSessionUserLimited($start, $end, $userid) {
        $sql = 'SELECT user.*, usergroup.usergroupname
		 FROM user JOIN usergroup
		 ON user.usergroupid = usergroup.usergroupid
		  where userid !=' . $userid . ' order by userid desc
		   limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithGroupIdAndUserId($userid, $usergroupid) {
        $sql = 'SELECT user.*, usergroup.usergroupname
		 FROM user JOIN usergroup
		 ON user.usergroupid = usergroup.usergroupid
		 where userid !=' . $userid . ' and user.usergroupid =' . $usergroupid . ' and usergroup.usergroupid > 0 order by userid asc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithGroupId($usergroupid) {
        $sql = 'SELECT user.userid,user.employeename
		 FROM user
		 where user.usergroupid =' . $usergroupid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithUserStoreIdDef($userstoreidDef) {
        $sql = 'SELECT user.userid,user.employeename
		 FROM user
		 where user.userstoreidDef =' . $userstoreidDef . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithGroupName($groupName) {
        $sql = 'SELECT user.userid,user.employeename
		 FROM user
                 join usergroup on usergroup.usergroupid = user.usergroupid
		 where usergroupname = "' . $groupName . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithGroupIdAndUserIdLimited($start, $end, $userid, $usergroupid) {
        $sql = 'SELECT user.*, usergroup.usergroupname
		 FROM user JOIN usergroup
		 ON user.usergroupid = usergroup.usergroupid
		  where userid !=' . $userid . '  and user.usergroupid =' . $usergroupid . '
		   order by userid desc
		   limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateConditions($user) {
        $sql = 'UPDATE user SET currentdate = ?, conditions = ? WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($user->currentdate);
        $sqlQuery->setNumber($user->conditions);

        $sqlQuery->setNumber($user->userid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithLevel($level) {
        $sql = 'SELECT user.*
		FROM user JOIN usergroup
		ON user.usergroupid = usergroup.usergroupid
		where  user.conditions =0 and usergroup.conditions =0
		and usergroup.level =' . $level . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadDocotr($usergroupid) {
        $sql = 'SELECT * FROM user WHERE usergroupid = ? ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($usergroupid);
        return $this->getList($sqlQuery);
    }

    public function loadDocotr2($docid, $usergroupid) {
        $sql = 'SELECT * FROM user WHERE userid = ? AND usergroupid = ? ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($docid);
        $sqlQuery->setNumber($usergroupid);

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

    public function queryAllcountuser() {
        $sql = 'SELECT count(*) FROM user';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllcountuser_f() {
        $sql = 'SELECT count(userid) FROM user';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getFirstUserForbiddenByUserLimits($userno) {
        $sql = 'SELECT userid FROM user ORDER BY userid LIMIT 1 OFFSET ' . $userno . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getFirstUserToActivateByUserLimits($userno) {
        $sql = 'update user set blockedByLimits = 0';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function activateAllUsers() {
        $sql = 'update user set blockedByLimits = 0';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function handleUserLimis($userid) {
        $sql = 'update user set blockedByLimits = 1 where userid >=' . $userid;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateLogginStatus($loginip, $lastactivetime, $deviceType, $userid) {
        $sql = 'UPDATE user SET loginip = "' . $loginip . '", lastactivetime = "' . $lastactivetime . '", deviceType = "' . $deviceType . '" WHERE userid = ' . $userid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function removeUserIp($userid) {
        $sql = 'UPDATE user SET loginip = "" WHERE userid = ' . $userid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function removeUserIpForEndedSessionsUsers() {
        //as session expeire within 3.5 hours approxmately
        $sessionTimeOut = date("Y-m-d H:i:s", strtotime(date('Y-m-d H:i:s') . " - 3 hours -30 minutes"));
        $sql = 'UPDATE user SET loginip = "" WHERE lastactivetime < "' . $sessionTimeOut . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getUsers($search) {
        $sql = 'SELECT userid,employeename FROM user WHERE conditions = 0 and  employeename like "%' . $search . '%" ';
        if ($hallId > 0) {
            $sql .= " and hallid = $hallId ";
        }
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function insertWithIdEx($user) {
        $sql = 'INSERT INTO user (userid, username, password, usergroupid, employeename, currentdate, conditions, saveid,userstoreid ,awardrate,searchinonesave, branchId,question, answer,blockedByLimits,loginip,lastactivetime,directSaveBills,userstoreidDef,storeSupervisor,storeSupervisorStores) VALUES (?, ?, ?, ?, ?, ?, ?, ? ,? ,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->setNumber($user->userid);
        $sqlQuery->set($user->username);
        $sqlQuery->set($user->password);
        $sqlQuery->setNumber($user->usergroupid);
        $sqlQuery->set($user->employeename);
        $sqlQuery->set($user->currentdate);
        $sqlQuery->setNumber($user->conditions);
        $sqlQuery->setNumber($user->saveid);
        $sqlQuery->setNumber($user->userstoreid);
        $sqlQuery->setNumber($user->awardrate);
        $sqlQuery->setNumber($user->searchinonesave);
        $sqlQuery->setNumber($user->branchId);
        $sqlQuery->set($user->question);
        $sqlQuery->set($user->answer);
        $sqlQuery->setNumber($user->blockedByLimits);
        $sqlQuery->set($user->loginip);
        $sqlQuery->set($user->lastactivetime);
        $sqlQuery->setNumber((int) $user->directSaveBills);
        $sqlQuery->setNumber((int) $user->userstoreidDef);
        $sqlQuery->setNumber((int) $user->storeSupervisor);
        $sqlQuery->set($user->storeSupervisorStores);

        $id = $this->executeInsert($sqlQuery);
        $user->userid = $id;
        return $id;
    }

    public function UpdateAllowRemoteAccess($value, $queryString) {
        $sql = 'update user set allowRemoteAccess = ' . $value . ' where 1 ' . $queryString;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function UpdateAllowRemoteAccessMobile($value, $queryString) {
        $sql = 'update user set allowRemoteAccessMobile = ' . $value . ' where 1 ' . $queryString;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>
