<?php

namespace App\MunicipalUtilities\DetailPage;

use Qs_Db;
use Qs_Db_Obj;
use App\Company\Model as CompanyModel;
use App\User\Model as UserModel;
use App\User\Entity as UserEntity;

class Obj extends Qs_Db_Obj
{
    protected $_tableAlias = 'Company';
    protected $_tableShortAlias = 'c';

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

        $columns['counties'] = CompanyModel::getCountiesColumn('c');
        $columns['houseDistrict'] = CompanyModel::getMnHouseDistrictColumn('c');
        $columns['congressDistrict'] = CompanyModel::getUsCongressDistrictColumn('c');

        return $columns;
    }

    protected function _addDependenciesFromDb(array &$data)
    {
        $data['positions'] = $this->getPositions($data['id']);
        $data['categories'] = $this->getCategories($data['id']);

        return parent::_addDependenciesFromDb($data);
    }

    private function getPositions($companyId)
    {
        $select = $this->_db->select();

        $select->from($this->_getPair('User', 'u'), ['name' => UserModel::getFullNameWithNicknameExpr('u')]);

        $select->join($this->_getPair('CompanyUtilityStaff', 'cus'), '`cus`.`userId` = `u`.`id`', []);
        $select->where('`cus`.`companyId` = ?', $companyId, Qs_Db::INT_TYPE);
        $select->where('`u`.`status` = ?', UserEntity::STATUS_ACTIVE);
        $select->order('cus.sorter');

        $select->join(
            $this->_getPair('PersonnelPosition', 'pp'),
            '`pp`.`id` = `cus`.`positionId`',
            ['position' => 'title']
        );

        return $this->_db->fetchAll($select);
    }

    private function getCategories($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UtilityCategory', 'uc'), ['title', 'symbol']);
        $select->join($this->_getPair('CompanyUtilityCategory', 'cuc'), '`cuc`.`utilityCategoryId` = `uc`.`id`', []);
        $select->where('`cuc`.`companyId` = ?', $companyId, Qs_Db::INT_TYPE);
        $select->order('uc.sorter');

        return $this->_db->fetchAll($select);
    }
}