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 LeadersLinked\Hydrator\ObjectPropertyHydrator;
use LeadersLinked\Mapper\Common\MapperCommon;
use LeadersLinked\Model\Connection;

class ConnectionMapper extends MapperCommon
{
    const _TABLE = 'tbl_connections';

    
    /**
     *
     * @var ConnectionMapper
     */
    private static $_instance;
    
    /**
     *
     * @param AdapterInterface $adapter
     */
    private function __construct($adapter)
    {
        parent::__construct($adapter);
    }
    
    /**
     *
     * @param AdapterInterface $adapter
     * @return ConnectionMapper
     */
    public static function getInstance($adapter)
    {
        if(self::$_instance == null) {
            self::$_instance = new ConnectionMapper($adapter);
        }
        return self::$_instance;
    }

  
    /*
    public function fetchAllConnectionByUserIdReturnIds(int $user_id, $return_full_array = false, int $currentpage = 1, int $limit = 10, string $status = Connection::STATUS_ACCEPTED) 
    {
        $connections_array = [];
     
        $select = $this->sql->select();
        $select->columns(['request_from', 'request_to']);
        $select->from(self::_TABLE);
        $select->where->equalTo('status',  $status);
        $select->where->and->nest()
            ->equalTo('request_from', $user_id)
            ->or->equalTo('request_to', $user_id)
            ->unnest();
        
           
        
        $connections = $this->executeFetchAllArray($select);
        if ($connections) {
            for ($i = 0, $max = count($connections); $i < $max; $i++) 
            {
                $request_from = $connections[$i]['request_from'];
                $request_to = $connections[$i]['request_to'];
                if ($request_from == $user_id) {
                    array_push($connections_array, $request_to);
                } else {
                    array_push($connections_array, $request_from);
                }
            }
        }
        if ($return_full_array) {
            $offset = ( $currentpage - 1 ) * $limit;
            $connections_array = array_slice($connections_array, $offset, $limit);
        }
        return $connections_array;
    }

    function fetchAllConnectionByUserIdsReturnIds(array $userIds, int $user_id, $return_full_array = false, int $currentpage = 1, int $limit = 10, string $status = Connection::STATUS_ACCEPTED) 
    {
        $select = $this->sql->select();
        $select->columns(['request_from', 'request_to']);
        $select->from(self::_TABLE);
        $select->where->equalTo('status',  $status);
        $select->where->and->nest()
            ->nest()
            ->equalTo('request_from', $user_id)
            ->and->in('request_to', $userIds)
            ->unnest()
            ->or->nest()
            ->equalTo('request_to', $user_id)
            ->and->in('request_from', $userIds)
            ->unnest()
       ->unnest();     
        
       $connections_array = [];
       $connections = $this->executeFetchAllArray($select);
       if ($connections) {
           for ($i = 0, $max = count($connections); $i < $max; $i++)
           {
               $request_from = $connections[$i]['request_from'];
               $request_to = $connections[$i]['request_to'];
               if ($request_from == $user_id) {
                   array_push($connections_array, $request_to);
               } else {
                   array_push($connections_array, $request_from);
               }
           }
       }
       if ($return_full_array) {
           $offset = ( $currentpage - 1 ) * $limit;
           $connections_array = array_slice($connections_array, $offset, $limit);
       }
       return $connections_array;
    }
    

    public function fetchAllPendingInvitationsByIdUser(int $user_id, bool $return_full_array = false, int $currentpage = 1, int $limit = 10, string $status = Connection::STATUS_ACCEPTED)
    {
       
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['request_from']);
        $select->where->equalTo('request_to', $user_id)->and->equalTo('status',  $status);
        
        $invitations_array = []; 
        $invitations = $this->executeFetchAllArray($select);
        if ($invitations) {
            for ($i = 0 , $max = count($invitations) ; $i < $max ; $i++) 
            {
                $request_from = $invitations[$i]['request_from'];
                array_push($invitations_array, $request_from);
            }
        }
        if ($return_full_array) {
            $offset = ( $currentpage - 1 ) * $limit;
            $invitations_array = array_slice($invitations_array, $offset, $limit);
        }
        return $invitations_array;
    }
    

    function fetchAllSentInvitationsByUserIds(int $user_id, bool $return_full_array = false, int $currentpage = 1, int $limit = 10, string $status = Connection::STATUS_ACCEPTED)
    {
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['request_from']);
        $select->where->equalTo('request_from', $user_id)->and->equalTo('status',  $status);
        
        $invitations_array = [];
        $invitations = $this->executeFetchAllArray($select);
        if ($invitations) {
            for ($i = 0 , $max = count($invitations) ; $i < $max ; $i++)
            {
                $request_from = $invitations[$i]['request_to'];
                array_push($invitations_array, $request_from);
            }
        }
        if ($return_full_array) {
            $offset = ( $currentpage - 1 ) * $limit;
            $invitations_array = array_slice($invitations_array, $offset, $limit);
        }
        return $invitations_array;

    }
    
    /**
     * @param int $user1
     * @param int $user2
     * @return Connection
     */
    public function fetchOneByUserId1AndUserId2($user1, $user2)
    {
        $prototype = new Connection();
        $select = $this->sql->select(self::_TABLE);
        $select->where->and->nest()
        ->nest()
        ->equalTo('request_from', $user1)
        ->and->equalTo('request_to', $user2)
        ->unnest()
        ->or->nest()
        ->equalTo('request_to', $user1)
        ->and->equalTo('request_from', $user2)
        ->unnest()
        ->unnest();   

    
        return $this->executeFetchOneObject($select, $prototype);
    }
    
    
    /**
     *
     * @param int $user_id
     * @return int
     */
    public function fetchTotalConnectionByUser($user_id)
    {
        $select1 = $this->sql->select(self::_TABLE);
        $select1->columns(['total' => new Expression('COUNT(*)')]);
        $select1->where->equalTo('request_from', $user_id);
        $select1->where->equalTo('status', Connection::STATUS_ACCEPTED);
        
        $total = 0;
        $record = $this->executeFetchOneArray($select1);
        $total += $record['total'];
        
        $select2 = $this->sql->select(self::_TABLE);
        $select2->columns(['total' => new Expression('COUNT(*)')]);
        $select2->where->equalTo('request_to', $user_id);
        $select2->where->equalTo('status', Connection::STATUS_ACCEPTED);
        $record = $this->executeFetchOneArray($select2);
        $total += $record['total'];
  
        return  $total;
    }
    
    /**
     * 
     * @param int $user_id
     * @return Connection[]
     */
    public function fetchAllConnectionsByUser($user_id)
    {
        $prototype = new Connection();
        
        $select1 = $this->sql->select(self::_TABLE);
        $select1->where->equalTo('request_from', $user_id);
        $select1->where->equalTo('status', Connection::STATUS_ACCEPTED);
        
        $connections1 = $this->executeFetchAllObject($select1, $prototype);

        
        
        $select2 = $this->sql->select(self::_TABLE);
        $select2->columns(['user_id' => 'request_from']);
        $select2->where->equalTo('request_to', $user_id);
        $select2->where->equalTo('status', Connection::STATUS_ACCEPTED);
        
        $connections2 = $this->executeFetchAllObject($select2, $prototype);

        return array_merge($connections1, $connections2);
    }
    
    /**
     *
     * @param int $user_id
     * @return int[]
     */
    public function fetchAllConnectionsByUserReturnIds($user_id)
    {
        $user_ids = [];
        
        $select1 = $this->sql->select(self::_TABLE);
        $select1->columns(['request_to']);
        $select1->where->equalTo('request_from', $user_id);
        $select1->where->equalTo('status', Connection::STATUS_ACCEPTED);
        
        $connections = $this->executeFetchAllArray($select1);
        foreach($connections as $connection) 
        {
            array_push($user_ids, $connection['request_to']);
        }
        
        
        
        $select2 = $this->sql->select(self::_TABLE);
        $select2->columns(['user_id' => 'request_from']);
        $select2->where->equalTo('request_to', $user_id);
        $select2->where->equalTo('status', Connection::STATUS_ACCEPTED);
        
        $connections = $this->executeFetchAllArray($select2);
        foreach($connections as $connection)
        {
            array_push($user_ids, $connection['user_id']);
        }
        
        
        

        return $user_ids;
    }
    
    /**
     * 
     * @param int $user_id
     * @return int[]
     */
    function fetchAllSecondDegreeConnectionsForUserIdReturnIds($user_id) 
    {
        $user_ids = [];
        $connected_user_ids = $this->fetchAllConnectionsByUserReturnIds($user_id);
        

        if($connected_user_ids) {
            $select = $this->sql->select(self::_TABLE);
            $select->where->notEqualTo('request_from', $user_id)
            ->and->notEqualTo('request_to', $user_id)
            ->and->equalTo('status', Connection::STATUS_ACCEPTED)
            ->and->NEST->in('request_from', $connected_user_ids)->or->in('request_to', $connected_user_ids)->UNNEST;
            
            
            $connections = $this->executeFetchAllArray($select);
            foreach($connections as $connection)
            {
                $request_from   = $connection['request_from'];
                $request_to     = $connection['request_to'];
                if (in_array($request_from, $connected_user_ids)) {
                    $intermediate_user_id = $request_to;
                } else {
                    $intermediate_user_id = $request_from;
                }
                
                $checkIfRequestSent = $this->fetchOneByUserId1AndUserId2($intermediate_user_id, $user_id);
                if(!$checkIfRequestSent) {
                    
                    if(!in_array($intermediate_user_id, $user_ids)) {
                        array_push($user_ids, $intermediate_user_id);
                    }
                }
            }
        }
        return $user_ids;
    }
    
    /**
     * 
     * @param int $first_user_id
     * @param int $second_user_id
     * @return int[]
     */
    function fetchAllCommonConnectionsUserIdByUser1ReturnIds($first_user_id, $second_user_id)
    {
        $first_user_connections     = $this->fetchAllConnectionsByUserReturnIds($first_user_id);
        $second_user_connections    = $this->fetchAllConnectionsByUserReturnIds($second_user_id);
        
        return array_values(array_intersect($first_user_connections, $second_user_connections));
    }
    
    /**
     *
     * @param int $user_id
     * @return int[]
     */
    public function fetchAllSentsUserIdForUser($user_id)
    {
        $prototype = new Connection();
        
        $select = $this->sql->select(self::_TABLE);
        $select->where->equalTo('request_to', $user_id);
        $select->where->equalTo('status', Connection::STATUS_SENT);
        
        $connections = $this->executeFetchAllObject($select, $prototype);
        
        
        
        $user_ids = [];
        foreach($connections as $connection)
        {
            array_push($user_ids, $connection->request_from);
        }
        
        return $user_ids;
    }
    
    /**
     *
     * @param int $user_id
     * @return int[]
     */
    public function fetchAllReceiveInvitationsReturnIds($user_id)
    {
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['request_from']);
        $select->where->equalTo('request_to', $user_id)->and->equalTo('status', Connection::STATUS_SENT);
        
        //echo $select->getSqlString($this->adapter->platform); exit;
        
        $user_ids  = [];
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($user_ids, (int)  $record['request_from']);
        }
        return $user_ids;
    }
    
    
    /**
     * 
     * @param int $user_id
     * @return int[]
     */
    public function fetchAllSentInvitationsReturnIds($user_id)
    {
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['request_to']);
        $select->where->equalTo('request_from', $user_id)->and->equalTo('status', Connection::STATUS_SENT);
        
        $user_ids  = [];
        $records = $this->executeFetchAllArray($select);
        foreach($records as $record)
        {
            array_push($user_ids, (int)  $record['request_to']);
        }
        return $user_ids;
    }
    
    /**
     * 
     * @param int $user_id
     * @return int
     */
    public function fetchConnectionRequestCount($user_id)
    {
        $select = $this->sql->select(self::_TABLE);
        $select->columns(['total' => new Expression('COUNT(*)')]);
        $select->where->equalTo('request_to', $user_id)->and->equalTo('status', Connection::STATUS_SENT);
        
        $record = $this->executeFetchOneArray($select);
        return $record['total'];
    }
    
    /**
     *
     * @param Connection $connection
     * @return boolean
     */
    public function approve($connection) 
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set(['status' => Connection::STATUS_ACCEPTED]);
        $update->where->equalTo('id', $connection->id);
        
        return $this->executeUpdate($update);
    }
    
        
    /**
     *
     * @param Connection $connection
     * @return boolean
     */
    public function reject($connection)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set(['status' => Connection::STATUS_REJECTED]);
        $update->where->equalTo('id', $connection->id);
        
        return $this->executeUpdate($update);
    }
    
    /**
     * 
     * @param Connection $connection
     * @return boolean
     */
    public function cancel($connection)
    {
        $update = $this->sql->update(self::_TABLE);
        $update->set(['status' => Connection::STATUS_CANCELLED]);
        $update->where->equalTo('id', $connection->id);
        
        return $this->executeUpdate($update);
   
    }
    
    
    /**
     *
     * @param Connection $connection
     * @return boolean
     */
    public function delete($connection)
    {
        $delete = $this->sql->delete(self::_TABLE);
        $delete->where->equalTo('id', $connection->id);
        
        return $this->executeDelete($delete);
        
    }
    
    
    /**
     *
     * @param Connection $connection
     * @return boolean
     */
    public function update($connection)
    {
        $hydrator = new ObjectPropertyHydrator();
        $values = $hydrator->extract($connection);
        $values = $this->removeEmpty($values);
        
        $update = $this->sql->update(self::_TABLE);
        $update->set($values);
        $update->where->equalTo('id', $connection->id);
        
        return $this->executeUpdate($update);
        
    }
    
    /**
     * 
     * @param int $user_id
     * @return int[]
     */
    public function getSecondDegreeConnectionsReturnIds($user_id)
    {
        $connected_uses_ids_array = $this->fetchAllConnectionsByUserReturnIds($user_id);

        $user_ids_array = [];
        if ($connected_uses_ids_array) {
                
            $queryMapper = QueryMapper::getInstance($this->adapter);
            $select = $queryMapper->getSql()->select(ConnectionMapper::_TABLE);
            $select->columns(['request_from', 'request_to']);
            $select->where->equalTo('status', Connection::STATUS_ACCEPTED)
            ->and->notEqualTo('request_from', $user_id)
            ->and->notEqualTo('request_to', $user_id)
            ->and->nest()->in('request_from',$connected_uses_ids_array)->or->in('request_to',$connected_uses_ids_array)->unnest();
                
                
            $connections = $queryMapper->fetchAll($select);
            if ($connections) {
                foreach ($connections as $connection) {
                    $request_from   = $connection['request_from'];
                    $request_to     = $connection['request_to'];
                    if (in_array($request_from, $connected_uses_ids_array)) {
                        $intermediate_user_id = $request_to;
                    } else {
                        $intermediate_user_id = $request_from;
                    }
                    if (!in_array($intermediate_user_id, $user_ids_array)) {
                        $select = $queryMapper->getSql()->select(ConnectionMapper::_TABLE);
                         $select->columns(['request_from', 'request_to']);
                         $select->where->nest()->equalTo('request_from', $user_id)->and->equalTo('request_to', $intermediate_user_id)->unnest()
                         ->or->nest()->equalTo('request_from', $intermediate_user_id)->and->equalTo('request_to', $user_id)->unnest();
                            
                         $checkIfRequestSent = $queryMapper->fetchOne($select);
                         if (!$checkIfRequestSent) {
                             array_push($user_ids_array, $intermediate_user_id);
                        }
                    }
                }
            }
        }
        return $user_ids_array;
    }
    
    /**
     * 
     * @param Connection $connection
     * @return boolean
     */
    public function insert($connection)
    {
        $hydrator = new ObjectPropertyHydrator();
        $values = $hydrator->extract($connection);
        $values = $this->removeEmpty($values);
        
        $insert = $this->sql->insert(self::_TABLE);
        $insert->values($values);
        
        $response = $this->executeInsert($insert);
        if($response) {
            $connection->id = $this->lastInsertId;
        }
        
        return $response;
    }
    

    
}