Proyectos de Subversion LeadersLinked - Antes de SPA

<?php

declare(strict_types=1);

namespace LeadersLinked\Mapper;

use Laminas\Db\Adapter\AdapterInterface;
use Laminas\Db\Sql\Expression;
use Laminas\Log\LoggerInterface;

use LeadersLinked\Model\User;
use LeadersLinked\Mapper\Common\MapperCommon;
use LeadersLinked\Hydrator\ObjectPropertyHydrator;
use Laminas\Hydrator\ArraySerializableHydrator;
use Laminas\Paginator\Paginator;
use Laminas\Paginator\Adapter\DbSelect;
use Laminas\Db\ResultSet\HydratingResultSet;
use LeadersLinked\Model\UserType;
use LeadersLinked\Model\CompanyUser;
use LeadersLinked\Model\CompanyMicrolearningCapsuleUser;
use LeadersLinked\Model\HighPerformanceTeamsGroupsMembers;




class UserMapper extends MapperCommon
{
    const _TABLE = 'tbl_users';

    /**
     *
     * @var UserMapper
     */
    private static $_instance;
    
    /**
     *
     * @param AdapterInterface $adapter
     */
    private function __construct($adapter)
    {
        parent::__construct($adapter);
    }
    
    /**
     *
     * @param AdapterInterface $adapter
     * @return UserMapper
     */
    public static function getInstance($adapter)
    {
        if(self::$_instance == null) {
            self::$_instance = new UserMapper($adapter);
        }
        return self::$_instance;
    }
    
    /**
     *
     * @param string $uuid
     * @return User
     */
    public function fetchOneByUuid($uuid)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('uuid', $uuid);
      
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    /**
     *
     * @return User
     */
    public function fetchOneDefaultForConnection()
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('default_for_connection', User::DEFAULT_FOR_CONNECTION_YES);
        
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    
    /**
     *
     * @param string $uuid
     * @param int $network_id
     * @return User
     */
    public function fetchOneByUuidAndNetworkId($uuid, $network_id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('uuid', $uuid);
        $select->where->equalTo('network_id', $network_id);
        
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    /**
     * 
     * @param int $id
     * @return User
     */
    public function fetchOne($id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('id', $id);
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    public function fetchOneRelation($id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('id', $id);
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    

    /**
     * 
     * @param string $email
     * @return void|User
     */
    public function fetchOneByEmail($email)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('email', $email);
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    /**
     *
     * @param string $email
     * @param int $network_id
     * @return void|User
     */
    public function fetchOneByEmailAndNetworkId($email, $network_id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('email', $email);
        $select->where->equalTo('network_id', $network_id);
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    /**
     *
     * @param string $email
     * @param int $network_id
     * @return void|User
     */
    public function fetchOneActiveByEmailAndNetworkId($email, $network_id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('email', $email);
        $select->where->equalTo('network_id', $network_id);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    
    /**
     * 
     * @param string $password_reset_key
     * @return void|User
     */
    public function fetchOneByPasswordResetKey($password_reset_key)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('password_reset_key', $password_reset_key);
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    /**
     *
     * @param string $password_reset_key
     * @param int $network_id
     * @return void|User
     */
    public function fetchOneByPasswordResetKeyAndNetworkId($password_reset_key, $network_id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('password_reset_key', $password_reset_key);
        $select->where->equalTo('network_id', $network_id);
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    /**
     * 
     * @param string $activation_key
     * @return void|User
     */
    public function fetchOneByActivationKey($activation_key)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('activation_key', $activation_key);
        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    /**
     *
     * @param string $activation_key
     * @param int $network_id
     * @return void|User
     */
    public function fetchOneByActivationKeyAndNetworkId($activation_key, $network_id)
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('activation_key', $activation_key);
        $select->where->equalTo('network_id', $network_id);
        

        
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    /**
     *
     * @param int $network_id
     * @return int
     */
    public function fetchCountByNetworkId($network_id)
    {
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['total' => new Expression('COUNT(*)')]);
        $select->where->equalTo('network_id', $network_id);
       
        $record = $this->executeFetchOneArray($select);
        
        return $record['total'];
    }
    
    /**
     *
     * @param int $network_id
     * @return int
     */
    public function fetchCountActiveByDefaultNetworkId($network_id)
    {
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['total' => new Expression('COUNT(*)')]);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::USER, UserType::ADMIN]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        
        $record = $this->executeFetchOneArray($select);
        
        return $record['total'];
    }
   
    /**
     *
     * @param int $network_id
     * @return int
     */
    public function fetchCountActiveByOtherNetworkId($network_id)
    {
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['total' => new Expression('COUNT(*)')]);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::USER, UserType::ADMIN]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('request_access', User::REQUEST_ACCESS_APPROVED);
        

        $record = $this->executeFetchOneArray($select);
        
        return $record['total'];
    }
    
    

    /**
     * 
     * @param string $keyword
     * @param int $current_user
     * @return User[]
     */
    public function fetchAllByKeyword($keyword, $current_user = 0)
    {
        
        $prototype = new User();

        $select = $this->sql->select(self::_TABLE);
       
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->notEqualTo('id', $current_user);
        $select->where->and->nest()
            ->like('first_name', '%' . $keyword . '%')
            ->or->like('last_name', '%' . $keyword . '%')
            ->or->like(new Expression("CONCAT(first_name,' ',last_name)"), '%'. $keyword . '%')
            ->unnest();
        
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     * 
     * @param int[] $ids
     * @param int $current_user
     * @param string $status
     * @return User[]
     */
    public function fetchAllByIds($ids, $current_user = 0, $status = User::STATUS_ACTIVE)
    {
        
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        
        
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->and->in('id',$ids);
        
        if($current_user) {
            $select->where->and->notEqualTo('id ', $current_user);
        }
        $select->order(['first_name ASC', 'last_name ASC', 'email ASC']);
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int[] $ids
     * @param string $search
     * @param int $current_user
     * @param string $status
     * @return User[]
     */
    public function fetchAllByIdsAndSearch($ids, $search, $current_user = 0, $status = User::STATUS_ACTIVE)
    {
        
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        
        
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->and->in('id',$ids);
        $select->where->nest()
        ->like('first_name', '%'.$search. '%')
        ->or->like('last_name', '%'.$search. '%')
        ->or->like('email', '%'.$search. '%')->unnest();
        
        if($current_user) {
            $select->where->and->notEqualTo('id ', $current_user);
        }
        $select->order(['first_name ASC', 'last_name ASC', 'email ASC']);
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    
    

    
    
    /**
     *
     * @return User[]
     */
    public function fetchAllByActives()
    {
        
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        
        
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified',User::EMAIL_VERIFIED_YES);
        
        $select->order(['first_name DESC, last_name DESC, email_DESC']);
        

        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $network_id
     * @param int $current_user_id;
     * @return int[]
     */
    public function fetchAllIdsByDefaultNetworkId($network_id, $current_user_id = 0)
    {
        

        $select = $this->sql->select(self::_TABLE);
        $select->columns(['id']);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        
        if($current_user_id) {
            $select->where->notEqualTo('id', $current_user_id);
        }
        
        $ids = [];
        
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($ids, $record['id']);
        }
        
        return $ids;
    }
    
    /**
     *
     * @param int $network_id
     * @param string $search
     * @param int $current_user_id;
     * @return int[]
     */
    public function fetchAllIdsByDefaultNetworkIdAndSearch($network_id, $search, $current_user_id = 0)
    {
        
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['id']);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->nest()
        ->like('first_name', '%'.$search. '%')
        ->or->like('last_name', '%'.$search. '%')
        ->or->like('email', '%'.$search. '%')->unnest();
        
        if($current_user_id) {
            $select->where->notEqualTo('id', $current_user_id);
        }
        
        $ids = [];
        
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($ids, $record['id']);
        }
        
        return $ids;
    }
    
    /**
     *
     * @param int $network_id
     * @param int $current_user_id;
     * @return int[]
     */
    public function fetchAllIdsByNonDefaultNetworkId($network_id, $current_user_id = 0)
    {
        
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['id']);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('request_access', User::REQUEST_ACCESS_APPROVED);
        
        if($current_user_id) {
            $select->where->notEqualTo('id', $current_user_id);
        }
        
        $ids = [];
        
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($ids, $record['id']);
        }
        
        return $ids;
    }
    
    
    /**
     *
     * @param int $network_id
     * @param string $search
     * @param int $current_user_id;
     * @return int[]
     */
    public function fetchAllIdsByNonDefaultNetworkIdAndSearch($network_id, $search, $current_user_id = 0)
    {
        
        
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['id']);
        $select->where->equalTo('network_id', $network_id);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('request_access', User::REQUEST_ACCESS_APPROVED);
        $select->where->nest()
        ->like('first_name', '%'.$search. '%')
        ->or->like('last_name', '%'.$search. '%')
        ->or->like('email', '%'.$search. '%')->unnest();
        
        if($current_user_id) {
            $select->where->notEqualTo('id', $current_user_id);
        }
        
        
        $ids = [];
        
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($ids, $record['id']);
        }
        
        return $ids;
    }
    
    
    /**
     * 
     * @param int $company_id
     * @return array
     */
    public function fetchAllByCompanyId($company_id)
    {
        
        $prototype = new User();
        $select = $this->sql->select();
        $select->from(['tb1' => self::_TABLE]);
        $select->join(['tb2' => CompanyUserMapper::_TABLE], 'tb1.id = tb2.user_id', []);
        
        
        $select->where->equalTo('tb2.company_id', $company_id);
        $select->where->in('tb2.status', [CompanyUser::STATUS_ACCEPTED, CompanyUser::STATUS_ADMIN_WILL_ADD]);
        
        $select->where->equalTo('tb1.status', User::STATUS_ACTIVE);
        $select->where->equalTo('tb1.email_verified',User::EMAIL_VERIFIED_YES);
        
        $select->order(['first_name DESC, last_name DESC, email_DESC']);
        
   
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     * 
     * @param int $user_id
     * @return boolean
     */
    public function leaveImpersonate($user_id)
    {
        $values = [
            'impersonate_user_id' => new Expression('NULL')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user_id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param int $user_id
     * @param int $impersonate_user_id
     * @return boolean
     */
    public function startImpersonate($user_id, $impersonate_user_id)
    {
        $values = [
            'impersonate_user_id' => $impersonate_user_id
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user_id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     * 
     * @param int $user_id
     * @param string $password_reset_key
     * @return boolean
     */
    public function updatePasswordResetKey($user_id, $password_reset_key)
    {
        $values = [
            'password_reset_key' => $password_reset_key,
            'password_generated_on' => date('Y-m-d H:i:s'),
            'updated_on' => new Expression('NOW()')
        ];

        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user_id);
        
        return $this->executeUpdate($update);
    }
    
    
    /**
     *
     * @param int $user_id
     * @param string $delete_account_key
     * @return boolean
     */
    public function updateDeleteAccountKey($user_id, $delete_account_key)
    {
        $values = [
            'delete_account_key' => $delete_account_key,
            'delete_account_generated_on' => date('Y-m-d H:i:s'),
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user_id);
        
        return $this->executeUpdate($update);
    }
    

    
    /**
     * 
     * @param User $user
     * @param string $password_hash
     * @return boolean
     */
    public function updatePassword($user, $password_hash)
    {
        $values = [
            'password'              => $password_hash,
            'password_reset_key'    => '',
            'password_updated_on'   => date('Y-m-d H:i:s'),
            'login_attempt'         => 0,
            'blocked'               => User::BLOCKED_NO,
            'updated_on'            => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user->id);
        
        return $this->executeUpdate($update);
    }
    
    
    /**
     *
     * @param User $user
     * @return boolean
     */
    public function unblock($user)
    {
        $values = [
            'login_attempt'         => 0,
            'blocked'               => User::BLOCKED_NO,
            'updated_on'            => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user->id);
        
        return $this->executeUpdate($update);
    }
    
    
    
    
    /**
     *
     * @param User $user
     * @param string $one_time_password
     * @return boolean
     */
    public function updateOneTimePassword($user, $one_time_password)
    {
        $values = [
            'one_time_password' => $one_time_password,
            'updated_on'        => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user->id);
        
        return $this->executeUpdate($update);
    }
    
    
    /**
     * 
     * @param User $user
     * @return boolean
     */
    public function update($user)
    {
        $hydrator = new ObjectPropertyHydrator();
        $values = $hydrator->extract($user);
        //$values = $this->removeEmpty($values);


        unset($values['added_on']);
        $values['updated_on'] = new Expression('NOW()') ;
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id',$user->id);
        
        //echo $update->getSqlString($this->adapter->platform); exit;
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param User $user
     * @return boolean
     */
    public function updatePrivacy($user)
    {
        $values = [
            'show_in_search' => $user->show_in_search,
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id',$user->id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param User $user
     * @return boolean
     */
    public function updateBasic($user)
    {
        $values = [
            'first_name' => $user->first_name,
            'last_name' => $user->last_name,
            'phone' => $user->phone,
            'gender' => $user->gender,
            'timezone' => $user->timezone,
            'is_adult' => $user->is_adult,
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id',$user->id);
        
        return $this->executeUpdate($update);
    }
   
    
    /**
     *
     * @param User $user
     * @return boolean
     */
    public function updateImage($user)
    {
        $values = [
            'image' => $user->image,
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id',$user->id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param User $user
     * @return boolean
     */
    public function updateLocation($user)
    {
        $values = [
            'location_id' => $user->location_id,
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id',$user->id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     * 
     * @param int $user_id
     * @return boolean
     */
    public function activateAccount($user_id)
    {
        $values = [
            'email_verified'    => User::EMAIL_VERIFIED_YES,
            'status'            => User::STATUS_ACTIVE,
            'activation_key'    => '',
            'updated_on' => new Expression('NOW()')
        ];
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $user_id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     * 
     * @param User $user
     * @return boolean
     */
    public function insert($user)
    {
        
        $hydrator = new ObjectPropertyHydrator();
        $values = $hydrator->extract($user);
        $values = $this->removeEmpty($values);
        
        $insert = $this->sql->insert(self::_TABLE);
        $insert->values($values);
        
        $response = $this->executeInsert($insert);
        if($response) {
            $user->id = $this->lastInsertId;
        }
        
        return $response;
    }
    
    
    /**
     *
     * @return boolean
     */
    public function truncate()
    {
        $sql = sprintf('TRUNCATE TABLE `%s` ', self::_TABLE);
        return $this->executeSentenceWithParameters($sql);
    }
    
    /**
     *
     * @param int $company_id
     * @param string $status
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllDataTableByCompanyId($company_id, $status = '', $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
    {
        $select = $this->sql->select(); 
        $select->columns(['status', 'backend', 'creator']);
        $select->from(['tb1' => CompanyUserMapper::_TABLE]);
        $select->join(['tb2' => self::_TABLE] , 'tb1.user_id = tb2.id', [
            'id', 'uuid', 'first_name', 'last_name', 'email', 'last_activity_on', 'image',
            'blocked', 'login_attempt', 'email_verified'
            
        ]);
        
        $select->where->equalTo('tb1.company_id', $company_id);
        $select->where->equalTo('tb1.owner', CompanyUser::OWNER_NO);
        
        if($status) {
            $select->where->equalTo('tb1.status', $status); 
           
        } 
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        
        $select->order($order_field . ' ' . $order_direction);
        
        $hydrator   = new ArraySerializableHydrator();
        $resultset  = new HydratingResultSet($hydrator);
        
        $adapter = new DbSelect($select, $this->sql, $resultset);
        $paginator = new Paginator($adapter);
        $paginator->setItemCountPerPage($records_per_page);
        $paginator->setCurrentPageNumber($page);
        
        
        return $paginator;
    }
    
    
    /**
     *
     * @param int $company_id
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllDataTableStudensByCompanyId($company_id,  $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
    {
        $date = date('Y-m-d');
        
        $selectCapsuleUser = $this->getSql()->select();
        $selectCapsuleUser->columns(['user_id' => new Expression('DISTINCT(user_id)')  ]);
        $selectCapsuleUser->from(CompanyMicrolearningCapsuleUserMapper::_TABLE); 
        $selectCapsuleUser->where->equalTo('company_id', $company_id);
        $selectCapsuleUser->where->nest->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_UNLIMITED)->or->nest()
        ->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_PAY_PERIOD)
        ->and->lessThanOrEqualTo(new Expression('DATE(paid_from)'), $date)
        ->and->greaterThanOrEqualTo(new Expression('DATE(paid_to)'), $date )->unnest()->unnest();
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email','blocked']);
        $select->from(UserMapper::_TABLE);
        $select->where->in('id', $selectCapsuleUser);
        

        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        
        $select->order($order_field . ' ' . $order_direction);
        
        $hydrator   = new ArraySerializableHydrator();
        $resultset  = new HydratingResultSet($hydrator);
        
        $adapter = new DbSelect($select, $this->sql, $resultset);
        $paginator = new Paginator($adapter);
        $paginator->setItemCountPerPage($records_per_page);
        $paginator->setCurrentPageNumber($page);
        
        
        return $paginator;
    }
        

    /**
     *
     * @param int $company_id
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllStudensByCompanyId($company_id)
    {
                
        $date = date('Y-m-d');
        
        $selectCapsuleUser = $this->getSql()->select();
        $selectCapsuleUser->columns(['user_id' => new Expression('DISTINCT(user_id)')  ]);
        $selectCapsuleUser->from(CompanyMicrolearningCapsuleUserMapper::_TABLE); 
        $selectCapsuleUser->where->equalTo('company_id', $company_id);
        $selectCapsuleUser->where->nest->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_UNLIMITED)->or->nest()
        ->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_PAY_PERIOD)
        ->and->lessThanOrEqualTo(new Expression('DATE(paid_from)'), $date)
        ->and->greaterThanOrEqualTo(new Expression('DATE(paid_to)'), $date )->unnest()->unnest();
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        $prototype = new User();
        $select = $this->sql->select();
        $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email','blocked']);
        $select->from(UserMapper::_TABLE);
        $select->where->in('id', $selectCapsuleUser);
        return $this->executeFetchAllObject($select, $prototype);

    }

    /**
     *
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggest($search)
    {

        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $network_id
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggestByNetworkIdAndSearch($network_id, $search)
    {
        
        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->equalTo('network_id', $network_id);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $company_id
     * @param int $network_id
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggestForInvitationByCompanyIdAndNetworkIdAndSearch($company_id, $network_id, $search)
    {
        $selectCompanyUsers = $this->sql->select(CompanyUserMapper::_TABLE);
        $selectCompanyUsers->columns(['user_id']);
        $selectCompanyUsers->where->equalTo('company_id', $company_id);
        $selectCompanyUsers->where->in('status', [
            CompanyUser::STATUS_ACCEPTED,
            CompanyUser::STATUS_PENDING,
            CompanyUser::STATUS_SENT,
            
        ]);
        
        //echo $selectCompanyUsers->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->notIn('id', $selectCompanyUsers);
        $select->where->equalTo('network_id', $network_id);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $company_id
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggestForInvitationByCompanyId($company_id, $search)
    {
        $selectCompanyUsers = $this->sql->select(CompanyUserMapper::_TABLE);
        $selectCompanyUsers->columns(['user_id']);
        $selectCompanyUsers->where->equalTo('company_id', $company_id);
        $selectCompanyUsers->where->in('status', [ 
            CompanyUser::STATUS_ACCEPTED,
            CompanyUser::STATUS_PENDING,
            CompanyUser::STATUS_SENT,

        ]);
        
        //echo $selectCompanyUsers->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->notIn('id', $selectCompanyUsers);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
       // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User(); 
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $group_id
     * @param int $network_id
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggestForInvitationByGroupIdAndNetworkIdAndSearch($group_id, $network_id, $search)
    {
        $selectGroupMembers = $this->sql->select(GroupMemberMapper::_TABLE);
        $selectGroupMembers->columns(['user_id']);
        $selectGroupMembers->where->equalTo('group_id', $group_id);
        $selectGroupMembers->where->in('status', [
            CompanyUser::STATUS_ACCEPTED,
        ]);
        
        //echo $selectGroupMembers->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->equalTo('network_id', $network_id);
        $select->where->notIn('id', $selectGroupMembers);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param int $group_id
     * @param string $search
     * @return User[]
     */
    public function fetchAllSuggestForInvitationByGroupId($group_id, $search)
    {
        $selectGroupMembers = $this->sql->select(GroupMemberMapper::_TABLE);
        $selectGroupMembers->columns(['user_id']);
        $selectGroupMembers->where->equalTo('group_id', $group_id);
        $selectGroupMembers->where->in('status', [
         CompanyUser::STATUS_ACCEPTED,
        ]);
        
        //echo $selectGroupMembers->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->from(self::_TABLE);
        $select->where->notIn('id', $selectGroupMembers);
        $select->where->equalTo('status', User::STATUS_ACTIVE);
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['first_name', 'last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }

    
    
    public function fetchAllSuggestForInvitationByHptgId($group_id,$company_id, $search)
    {
        $selectGroupMembers = $this->sql->select(HighPerformanceTeamsGroupsMembersMapper::_TABLE);
        $selectGroupMembers->columns(['user_id']);
        $selectGroupMembers->where->equalTo('group_id', $group_id);
        $selectGroupMembers->where->in('status', [
            HighPerformanceTeamsGroupsMembers::STATUS_ACCEPTED,
            HighPerformanceTeamsGroupsMembers::STATUS_ADDED_BY_ADMIN,
            HighPerformanceTeamsGroupsMembers::STATUS_INVITED,
        ]);
        
        //echo $selectGroupMembers->getSqlString($this->adapter->platform); exit;
        
        $select = $this->sql->select();
        $select->from(['u' => self::_TABLE]);
        $select->join(['cu' => CompanyUserMapper::_TABLE], 'cu.user_id = u.id', []);
        $select->where->notIn('u.id', $selectGroupMembers);
        $select->where->equalTo('u.status', User::STATUS_ACTIVE);
        $select->where->equalTo('u.email_verified', User::EMAIL_VERIFIED_YES);
        $select->where->equalTo('cu.company_id', $company_id);

        
        if($search) {
            $select->where->nest()
            ->like('u.first_name', '%' . $search . '%')
            ->or->like('u.last_name', '%' . $search . '%')
            ->or->like('u.email', '%' . $search . '%')
            ->unnest();
            
        }
        
        $select->order(['u.first_name', 'u.last_name']);
        
        // echo $select->getSqlString($this->adapter->platform); exit;
        
        $prototype = new User();
        
        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     *
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllDataTable($search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        
 
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }

        
        $select->order($order_field . ' ' . $order_direction);
        
        $hydrator   = new ObjectPropertyHydrator();
        $resultset  = new HydratingResultSet($hydrator, $prototype);
        
        $adapter = new DbSelect($select, $this->sql, $resultset);
        $paginator = new Paginator($adapter);
        $paginator->setItemCountPerPage($records_per_page);
        $paginator->setCurrentPageNumber($page);
        
        
        return $paginator;
    }
    
    /**
     *
     * @param int $network_id
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllDataTableByNetworkId($network_id, $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('network_id', $network_id);
        $select->where->equalTo('request_access', User::REQUEST_ACCESS_APPROVED);
        
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        
        $select->order($order_field . ' ' . $order_direction);
        
        $hydrator   = new ObjectPropertyHydrator();
        $resultset  = new HydratingResultSet($hydrator, $prototype);
        
        $adapter = new DbSelect($select, $this->sql, $resultset);
        $paginator = new Paginator($adapter);
        $paginator->setItemCountPerPage($records_per_page);
        $paginator->setCurrentPageNumber($page);
        
        
        return $paginator;
    }
    
    
   
    
    /**
     *
     * @param int $network_id
     * @param string $search
     * @param int $page
     * @param int $records_per_page
     * @param string $order_field
     * @param string $order_direction
     * @return Paginator
     */
    public function fetchAllDataTableRequestAccessPendingByNetworkId($network_id, $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
    {
        $prototype = new User();
        $select = $this->sql->select(self::_TABLE);
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
        $select->where->equalTo('network_id', $network_id);
        $select->where->equalTo('request_access', User::REQUEST_ACCESS_PENDING);
        
        if($search) {
            $select->where->nest()
            ->like('first_name', '%' . $search . '%')
            ->or->like('last_name', '%' . $search . '%')
            ->or->like('email', '%' . $search . '%')
            ->unnest();
            
        }
        
        
        $select->order($order_field . ' ' . $order_direction);
        
        $hydrator   = new ObjectPropertyHydrator();
        $resultset  = new HydratingResultSet($hydrator, $prototype);
        
        $adapter = new DbSelect($select, $this->sql, $resultset);
        $paginator = new Paginator($adapter);
        $paginator->setItemCountPerPage($records_per_page);
        $paginator->setCurrentPageNumber($page);
        
        
        return $paginator;
    }

    
    /**
     *
     * @param int $id
     * @return boolean
     */
    public function markIsAdult($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'id_adult' => User::IS_ADULT_YES,
        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param int $id
     * @return boolean
     */
    public function markIsNotAdult($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'id_adult' => User::IS_ADULT_NO,
        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     * 
     * @param int $id
     * @return boolean
     */
    public function updateChatOnlineStatus($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'online' => 1, 
        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
   

    
    /**
     * 
     * @param int $id
     * @return boolean
     */
    public function updateLastActivity($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'last_activity_on' => new Expression('NOW()'),
            'online' => 1,
        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
    
    
    /**
     *
     * @param int $id
     * @return boolean
     */
    public function updateLastHeartBeat($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'last_heart_beat_at' => new Expression('NOW()'),
            'online' => 1,

        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     *
     * @param int $id
     * @return boolean
     */
    public function emailVerifyAndActive($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'email_verified' => User::EMAIL_VERIFIED_YES,
            'status' => User::STATUS_ACTIVE, 
        ]);
        $update->where->equalTo('id', $id);
        
        return $this->executeUpdate($update);
    }
        
    
    /**
     *
     * @param int $company_id
     * @return User[]
     */
    public function fetchAllVerifyAndActiveByCompanyId($company_id)
    {
        $select = $this->sql->select();
        $select->columns(['status', 'company_id']);
        $select->from(['tb1' => CompanyUserMapper::_TABLE]);
        $select->join(['tb2' => self::_TABLE] , 'tb1.user_id = tb2.id');
        

        
        $select->where->equalTo('tb1.company_id', $company_id);
        $select->where->equalTo('tb1.owner', CompanyUser::OWNER_NO);
        $select->where->in('tb1.status', [CompanyUser::STATUS_ACCEPTED, CompanyUser::STATUS_ADMIN_WILL_ADD]);
        $select->where->equalTo('tb2.email_verified', User::EMAIL_VERIFIED_YES);
        $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);
        
        $select->order('first_name', 'last_name', 'email');

        $prototype = User();
      
        return $this->executeFetchAllObject($select, $prototype);
    }
}