<?php

namespace App\User\MemberDirectory;

use App\User\AbstractObj;
use App\User\Entity;
use App\User\Model;
use Qs_Db;
use Zend_Db_Select;

class Obj extends AbstractObj
{
    protected $_filterFields = null;

    protected function _getFromColumns()
    {
        return [
            'User.id',
            'fullestName' => Model::getFullestNameDbExpr(),
            'lastName',
            'alias',
            'companyName',
            'email',
            'workPhone',
        ];
    }

    protected function _getFromDbColumns()
    {
        return ['*', 'name' => Model::getFullestNameDbExpr()];
    }

    public function getFilterFields()
    {
        if (null === $this->_filterFields) {
            $this->_filterFields = [
                'User' => [Model::getFullestNameDbExpr(), 'companyName', 'workPhone', 'cellPhone', 'homePhone', 'fax'],
                'ua'   => ['address', 'address2', 'address3', 'city', 'state'],
                'cs'   => ['name'],
                'c'    => ['name'],
            ];
        }
        return $this->_filterFields;
    }

    public function getListSelect()
    {
        if (null === $this->_select) {
            parent::getListSelect();
            $this->_filterStatus($this->_select);
            $this->_select->joinLeft(
                $this->_getPair('UserAddress', 'ua'),
                $this->_db->quoteInto('`ua`.`type` = ?', Entity::ADDRESS_HOME) . ' AND `ua`.`userId` = `User`.`id`',
                []
            );
            $this->_select->joinLeft(
                $this->_getPair('Country', 'c'),
                '`c`.`id` = `ua`.`countryId`',
                []
            );
            $this->_select->joinLeft(
                $this->_getPair('CountryState', 'cs'),
                '`cs`.`abbreviation` = `ua`.`state`',
                []
            );
        }
        return $this->_select;
    }

    protected function _filter(Zend_Db_Select $select)
    {
        $this->_filterBought($select);
        if (($lastNameStart = $this->getFilter('lastNameStart'))) {
            $select->where('`User`.`lastName` LIKE ' . Qs_Db::quoteLike($lastNameStart, '{term}%'));
        }
        return parent::_filter($select);
    }

    protected function _filterStatus(Zend_Db_Select $select)
    {
        $select->where('`' . $this->_tableAlias . '`.`status` = ?', Entity::STATUS_ACTIVE);
        return $this;
    }

    protected function _filterBought(Zend_Db_Select $select)
    {
        $select->where('`User`.`bought` = "y"');
        $select->where('"n" = ' . Model::getMembershipExpiredExpr('User'));
        return $this;
    }

    public function getLastNameAlphabet()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair(), ['letter' => 'LOWER(LEFT(lastName, 1))']);
        $this->_filterBought($select);
        $this->_filterStatus($select);
        $select->order('lastName');
        $list = $this->_db->fetchCol($select);
        return array_combine($list, $list);
    }
}