<?php

namespace App\Committee;

use Qs_Array;
use Qs_Db;
use App\User\Entity as UserEntity;

class Obj extends AbstractObj
{
    public function getListSelect()
    {
        if (null === $this->_select) {
            $this->_select = parent::getListSelect();
            $this->_select->where('`enabled` = "y"');
        }
        return $this->_select;
    }

    protected function _addDependenciesFromDb(array &$data)
    {
        $data['nextMeeting'] = $this->_getNextMeetingData($data['id']);
        $data['documents'] = $this->_getDocuments($data['id']);
        $data['members'] = $this->_getMembers($data['id']);
        return parent::_addDependenciesFromDb($data);
    }

    protected function _getMembers($committeeId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User', 'u'), ['cp.id', 'u.*'])
            ->join(
                $this->_getPair('UserCommittee', 'uc'),
                '`uc`.`userId` = `u`.`id`',
                []
            )
            ->join(
                $this->_getPair('CommitteePosition', 'cp'),
                '`cp`.`id` = `uc`.`positionId`',
                ['positionTitle' => 'IF(cp.id = ' . ((int) Entity::POSITION_MEMBER_ID) . ', "", cp.title)']
            )
            ->joinLeft(
                $this->_getPair('Company', 'c'),
                '`c`.`id` = `u`.`companyId`',
                [
                    'companyName'     => 'name',
                    'companyAddress'  => 'address',
                    'companyAddress2' => 'address2',
                    'companyCity'     => 'city',
                    'companyState'    => 'state',
                    'companyZip'      => 'zip',
                ]

            )
            ->where('`uc`.`committeeId` = ?', $committeeId, Qs_Db::INT_TYPE)
            ->where('`u`.`status` = ?', UserEntity::STATUS_ACTIVE)
            ->order('cp.sorter')
            ->order('u.lastName')
            ->order('u.firstName');
        return $this->_db->fetchAll($select, null, Qs_Db::FETCH_GROUP);
    }

    protected function _getNextMeetingData($committeeId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Event', 'e'))
            ->where('`e`.`committeeId` = ?', $committeeId, Qs_Db::INT_TYPE)
            ->where('`e`.`startDate` >= ?', date('Y-m-d H:i:s'))
            ->where('`e`.`enabled` = ?', 'y')
            ->order('e.startDate ASC')
            ->limit(1);
        return $this->_db->fetchRow($select);
    }

    protected function _getDocuments($committeeId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('CommitteeDocument', 'd'))
            ->where('`d`.`committeeId` = ?', $committeeId, Qs_Db::INT_TYPE)
            ->order('d.added');
        return $this->_db->fetchAll($select);
    }
}