<?php

namespace App\TradeShow\Admin;

use App\TradeShow\AbstractObj;
use App\TradeShow\Entity as Entity;
use Zend_Db_Expr;

class Obj extends AbstractObj
{
    protected $_filterFields = ['title'];
    protected $_nullableFields = ['boothSpaceLimit'];

    protected function _filter(\Zend_Db_Select $select)
    {
        parent::_filter($select);
        if (($value = $this->getFilter('year'))) {
            $select->where('YEAR(`ts`.`date`) = ?', $value, \Qs_Db::INT_TYPE);
        }
        return $this;
    }

    public function initFromForm(array $data)
    {
        foreach (['Start', 'End'] as $name) {
            $data["registration{$name}Time"] = $this->getDateTime($data, "registration{$name}");
        }
        return parent::initFromForm($data);
    }

    protected function getDateTime($data, $field)
    {
        if (empty($data[$field . 'Date']) || empty($data[$field . 'Time'])) {
            return null;
        }
        return $data[$field . 'Date'] . ' ' . $data[$field . 'Time'];
    }
    
    protected function _getFromDbColumns()
    {
        $columns = parent::_getFromDbColumns();
        foreach (['Start', 'End'] as $name) {
            $columns["registration{$name}Date"] = new Zend_Db_Expr("DATE(`ts`.`registration{$name}Time`)");
            $columns["registration{$name}Time"] = new Zend_Db_Expr("TIME(`ts`.`registration{$name}Time`)");
        }
        return $columns;
    }

    protected function _getFromColumns()
    {
        $columns = parent::_getFromColumns();

        $columns['registrationIds'] = $this->_getRegistrationIds();
        $columns['electricityHookUpCount'] = $this->_getElectricityHookUpCount();
        $columns['boothSpaceCount'] = $this->_getBoothSpaceCount();
        $columns['representativeCount'] = $this->_getAttendeeCount(Entity::TYPE_VENDOR);
        $columns['attendeeCount'] = $this->_getAttendeeCount();
        return $columns;
    }

    /**
     * Get Electricity Hook-Up Count for Trade Show
     * If Tarde Show electricityPrice
     * @return \Zend_Db_Expr
     */
    protected function _getElectricityHookUpCount()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('TradeShowVendor', 'tsv'), 'COUNT(*)');
        $select->where('FIND_IN_SET(`tsv`.`registrationId`, `registrationIds`)');
        $select->where('`tsv`.`electricityHookUp` = "y"');
        return new Zend_Db_Expr('IF(`ts`.`electricityPrice` IS NULL, NULL, (' . $select . '))');
    }

    protected function _getBoothSpaceCount()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('TradeShowVendor', 'tsv'), 'COUNT(*)');
        $select->where('FIND_IN_SET(`tsv`.`registrationId`, `registrationIds`)');
        return new Zend_Db_Expr('(' . $select . ')');
    }

    protected function _getAttendeeCount($type = Entity::TYPE_SINGLE_ATTENDEE)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('TradeShowAttendee', 'tsa'), 'COUNT(*)');
        $select->join($this->_getPair('TradeShowRegistration', 'tsr'), '`tsr`.`id` = `tsa`.`registrationId`', []);
        $select->where('FIND_IN_SET(`tsa`.`registrationId`, `registrationIds`)');
        $select->where('`tsr`.`recordType` = ?', $type);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    protected function _getRegistrationIds()
    {
         $select = $this->_db->select();
         $select->from($this->_getPair('TradeShowRegistration', 'tsr'), 'GROUP_CONCAT(`tsr`.`id` SEPARATOR ",")');
         $select->where('`tsr`.`tradeShowId` = `ts`.`id`');
         $select->where('`tsr`.`bought` = "y"');
         return new Zend_Db_Expr('(' . $select . ')');
    }

    /**
     * Conference events that have a date that is not in the past, and does not have trade shows 
     * @param bool $excludeUsedEvents
     * @param int $currentEventId
     * @return array
     */
    public function getEventList($excludeUsedEvents = false, $currentEventId = null)
    {
        $select = clone (new \App\Event\Upcoming\Obj())->getListSelect();
        $select->reset(\Zend_Db_Select::LIMIT_COUNT)->reset(\Zend_Db_Select::LIMIT_OFFSET);
        $select->where('`e`.`type` = "conference" OR `e`.`type` = "workshop"');
        if ($excludeUsedEvents && ($eventsIds = $this->_getTradeShowEventsIds())) {
            if ($currentEventId) {
                unset($eventsIds[array_search($currentEventId, $eventsIds)]);
            }
            if ($eventsIds) {
                $select->where('`e`.`id` NOT IN (?)', $eventsIds);
            }
        }
        return ($list = $this->_db->fetchAll($select)) ? \Qs_Array::fetchPairs($list, ['id', 'title']) : [];
    }
    
    protected function _getTradeShowEventsIds()
    {
        return array_filter(\Qs_array::fetchCol($this->getList(), 'eventId'));
    }

    public function getEventTitleById($eventId)
    {
        return ($eventId) ? $this->_getTable('Event')->searchBy(['id' => $eventId], 'title') : [];
    }
}