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;


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);
    }
    
    /**
     * 
     * @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);
    }
    
    /**
     * 
     * @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 $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 $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 $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)->equalTo('status', $status);
        
        if($current_user) {
            $select->where->and->notEqualTo('id ', $current_user);
        }
        
        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);
        

        return $this->executeFetchAllObject($select, $prototype);
    }
    
    /**
     * 
     * @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 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);
        $value = $this->removeEmpty($values);
        
        $values['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 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,
            '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 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 $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 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);
    }
    
    /**
     *
     * @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 $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 updateChatOfflineStatus($id)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set([
            'online' => 0,
        ]);
        $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()'),
        ]);
        $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);
    }
        
}