<?php

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

    /**
     * Get Domain object by primry key
     *
     * @param String $id primary key
     * @return UserMySql
     */
    public function load($id) {
        $sql = 'SELECT * FROM user WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    /**
     * Get all records from table
     */
    public function queryAll() {
        $sql = 'SELECT * FROM user where usergroupid > 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Get all records from table ordered by field
     *
     * @param $orderColumn column name
     */
    public function queryAllOrderBy($orderColumn) {
        $sql = 'SELECT * FROM user where usergroupid > 0 ORDER BY ' . $orderColumn;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Delete record from table
     * @param user primary key
     */
    public function delete($userid) {
        $sql = 'DELETE FROM user WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($userid);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Insert record to table
     *
     * @param UserMySql user
     */
    public function insert($user) {
        $sql = 'INSERT INTO user (username, password, usergroupid, employeename, currentdate, conditions, saveid,userstoreid ,awardrate,searchinonesave, branchId,question, answer,blockedByLimits,directSaveBills,userstoreidDef,empid,obygyDoctorId,lang,viewclients,viewbills,storeSupervisor,storeSupervisorStores,projectids,bankids,saveids,searchinonestorebalance,storeids,webApiId,closeSavid,charityids,laterNakdiUserIds) VALUES (?, ?, ?, ?, ?, ?, ?, ? ,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);


        $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->setNumber((int) $user->directSaveBills);
        $sqlQuery->setNumber((int) $user->userstoreidDef);
        $sqlQuery->setNumber((int) $user->empid);
        $sqlQuery->setNumber((int) $user->obygyDoctorId);
        $sqlQuery->set($user->lang);
        $sqlQuery->setNumber((int) $user->viewclients);
        $sqlQuery->setNumber((int) $user->viewbills);
        $sqlQuery->setNumber((int) $user->storeSupervisor);
        $sqlQuery->set($user->storeSupervisorStores);
        $sqlQuery->set($user->projectids);
        $sqlQuery->set($user->bankids);
        $sqlQuery->set($user->saveids);
        $sqlQuery->setNumber((int) $user->searchinonestorebalance);
        $sqlQuery->set($user->storeids);
        $sqlQuery->setNumber((int) $user->webApiId);
        $sqlQuery->setNumber((int) $user->closeSavid);
        $sqlQuery->set($user->charityids);
        $sqlQuery->set($user->laterNakdiUserIds);


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

    /**
     * Update record in table
     *
     * @param UserMySql user
     */
    public function update($user) {
        $sql = 'UPDATE user SET username = ?, password = ?, usergroupid = ?, employeename = ?, currentdate = ?, conditions = ?, saveid = ?  , userstoreid =? ,awardrate = ? ,searchinonesave = ?, branchId = ?, question = ?, answer = ?,blockedByLimits = ?,directSaveBills = ?,userstoreidDef=?,empid=?,obygyDoctorId = ?,lang = ?,viewclients = ?,viewbills = ?,storeSupervisor=?,storeSupervisorStores=?,projectids=?,bankids=?,saveids=?,searchinonestorebalance=?,storeids=?,webApiId=?,closeSavid=?,charityids=?,laterNakdiUserIds=? WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);

        $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->setNumber((int) $user->directSaveBills);
        $sqlQuery->setNumber((int) $user->userstoreidDef);
        $sqlQuery->setNumber((int) $user->empid);
        $sqlQuery->setNumber((int) $user->obygyDoctorId);
        $sqlQuery->set($user->lang);
        $sqlQuery->setNumber((int) $user->viewclients);
        $sqlQuery->setNumber((int) $user->viewbills);
        $sqlQuery->setNumber((int) $user->storeSupervisor);
        $sqlQuery->set($user->storeSupervisorStores);
        $sqlQuery->set($user->projectids);
        $sqlQuery->set($user->bankids);
        $sqlQuery->set($user->saveids);
        $sqlQuery->setNumber((int) $user->searchinonestorebalance);
        $sqlQuery->set($user->storeids);
        $sqlQuery->setNumber((int) $user->webApiId);
        $sqlQuery->setNumber((int) $user->closeSavid);
        $sqlQuery->set($user->charityids);
        $sqlQuery->set($user->laterNakdiUserIds);

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

    /**
     * Delete all rows
     */
    public function clean() {
        $sql = 'DELETE FROM user';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByUsername($value) {
        $sql = 'SELECT * FROM user WHERE username = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByPassword($value) {
        $sql = 'SELECT * FROM user WHERE password = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

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

    public function queryByEmployeename($value) {
        $sql = 'SELECT * FROM user WHERE employeename = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCurrentdate($value) {
        $sql = 'SELECT * FROM user WHERE currentdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

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

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

    public function deleteByUsername($value) {
        $sql = 'DELETE FROM user WHERE username = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByPassword($value) {
        $sql = 'DELETE FROM user WHERE password = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByUsergroupid($value) {
        $sql = 'DELETE FROM user WHERE usergroupid = ? and usergroupid > 0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByEmployeename($value) {
        $sql = 'DELETE FROM user WHERE employeename = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByCurrentdate($value) {
        $sql = 'DELETE FROM user WHERE currentdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByConditions($value) {
        $sql = 'DELETE FROM user WHERE conditions = ? and usergroupid > 0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteBySaveid($value) {
        $sql = 'DELETE FROM user WHERE saveid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Read row
     *
     * @return UserMySql
     */
    protected function readRow($row) {
        $user = new User();

        $user->userid = isset($row['userid']) ? $row['userid'] : '';
        $user->username = isset($row['username']) ? $row['username'] : '';
        $user->password = isset($row['password']) ? $row['password'] : '';
        $user->usergroupid = isset($row['usergroupid']) ? $row['usergroupid'] : '';
        $user->employeename = isset($row['employeename']) ? $row['employeename'] : '';
        $user->currentdate = isset($row['currentdate']) ? $row['currentdate'] : '';
        $user->conditions = isset($row['conditions']) ? $row['conditions'] : '';
        $user->saveid = isset($row['saveid']) ? $row['saveid'] : '';
        $user->userstoreid = isset($row['userstoreid']) ? $row['userstoreid'] : '';
        $user->awardrate = isset($row['awardrate']) ? $row['awardrate'] : '';
        $user->searchinonesave = isset($row['searchinonesave']) ? $row['searchinonesave'] : '';
        $user->branchId = isset($row['branchId']) ? $row['branchId'] : '';
        $user->question = isset($row['question']) ? $row['question'] : '';
        $user->answer = isset($row['answer']) ? $row['answer'] : '';
        $user->blockedByLimits = isset($row['blockedByLimits']) ? $row['blockedByLimits'] : '';
        $user->loginip = isset($row['loginip']) ? $row['loginip'] : '';
        $user->lastactivetime = isset($row['lastactivetime']) ? $row['lastactivetime'] : '';
        $user->deviceType = isset($row['deviceType']) ? $row['deviceType'] : '';
        $user->webApiId = isset($row['webApiId']) ? $row['webApiId'] : '';

        //required
        $user->usergroupname = isset($row['usergroupname']) ? $row['usergroupname'] : '';
        $user->directSaveBills = isset($row['directSaveBills']) ? $row['directSaveBills'] : '';
        $user->userstoreidDef = isset($row['userstoreidDef']) ? $row['userstoreidDef'] : '';
        $user->empid = isset($row['empid']) ? $row['empid'] : '';
        $user->obygyDoctorId = isset($row['obygyDoctorId']) ? $row['obygyDoctorId'] : '';
        $user->sellerid = isset($row['sellerid']) ? $row['sellerid'] : '';
        $user->lang = isset($row['lang']) ? $row['lang'] : '';
        $user->allowRemoteAccess = isset($row['allowRemoteAccess']) ? $row['allowRemoteAccess'] : '';
        $user->allowRemoteAccessMobile = isset($row['allowRemoteAccessMobile']) ? $row['allowRemoteAccessMobile'] : '';
        $user->viewclients = isset($row['viewclients']) ? $row['viewclients'] : '';
        $user->viewbills = isset($row['viewbills']) ? $row['viewbills'] : '';
        $user->storeSupervisor = isset($row['storeSupervisor']) ? $row['storeSupervisor'] : '';
        $user->storeSupervisorStores = isset($row['storeSupervisorStores']) ? $row['storeSupervisorStores'] : '';
        $user->projectids = isset($row['projectids']) ? $row['projectids'] : '';
        $user->bankids = isset($row['bankids']) ? $row['bankids'] : '';
        $user->saveids = isset($row['saveids']) ? $row['saveids'] : '';
        $user->searchinonestorebalance = isset($row['searchinonestorebalance']) ? $row['searchinonestorebalance'] : '';
        $user->storeids = isset($row['storeids']) ? $row['storeids'] : '';
        $user->closeSavid = isset($row['closeSavid']) ? $row['closeSavid'] : '';
        $user->charityids = isset($row['charityids']) ? $row['charityids'] : '';
        $user->lastendvisit = isset($row['lastendvisit']) ? $row['lastendvisit'] : '';
        $user->laterNakdiUserIds = isset($row['laterNakdiUserIds']) ? $row['laterNakdiUserIds'] : '';

        return $user;
    }

    protected function getList($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        $ret = array();
        for ($i = 0; $i < count($tab); $i++) {
            $ret[$i] = $this->readRow($tab[$i]);
        }
        return $ret;
    }

    /**
     * Get row
     *
     * @return UserMySql
     */
    protected function getRow($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        if (count($tab) == 0) {
            return null;
        }
        return $this->readRow($tab[0]);
    }

    /**
     * Execute sql query
     */
    protected function execute($sqlQuery) {
        return QueryExecutor::execute($sqlQuery);
    }

    /**
     * Execute sql query
     */
    protected function executeUpdate($sqlQuery) {
        return QueryExecutor::executeUpdate($sqlQuery);
    }

    /**
     * Query for one row and one column
     */
    protected function querySingleResult($sqlQuery) {
        return QueryExecutor::queryForString($sqlQuery);
    }

    /**
     * Insert row to table
     */
    protected function executeInsert($sqlQuery) {
        return QueryExecutor::executeInsert($sqlQuery);
    }

}

?>