<?php

class App_BenchmarkPlans_Admin_Obj extends App_BenchmarkPlans_Abstract_Obj
{
    protected $_filterFields = array('year');

    public function getCounties4Select($benchmarkId = null, $year = null)
    {
        $usedCountiesSelect = $this->_db
            ->select()
            ->from($this->_getPair('DCounties'))
            ->joinLeft($this->_getPair($this->_dependencyTable,'dep'), 'dep.countyId = DCounties.id', array())
            ->joinLeft($this->_getPair($this->_tableAlias,'plan'), 'dep.benchmarkId = plan.id', array());
            if(!is_null($year) ){
                $usedCountiesSelect->where('year = ?', $year);
            }
            if(!is_null($benchmarkId)){
                $usedCountiesSelect->where('dep.benchmarkId NOT LIKE ?', $benchmarkId);
            }
        $usedCounties = $this->_db->fetchPairs($usedCountiesSelect);
        $countiesSelect = $this->_db
            ->select()
            ->from($this->_getPair('DCounties'));
        $counties = $this->_db->fetchPairs($countiesSelect);
        return array_diff($counties, $usedCounties);
    }

    public function getListSelect()
    {
        /** @var Zend_Db_Select */
        $select = parent::getListSelect();
        $select->columns(array('county' => '(' . new Zend_Db_Expr($this->_getCounties()) . ')'));
        return $select;
    }

    protected function _getCounties()
    {
        $mainTable   = $this->_getTableName($this->_tableAlias);
        $c2bTable    = $this->_getTableName($this->_dependencyTable);
        $countyTable = $this->_getTableName('DCounties');

        $select = $this->_db->select()
            ->from($c2bTable, array('GROUP_CONCAT(counties.county SEPARATOR ", ")'))
            ->join(array('counties' => $countyTable), $c2bTable . '.countyId = counties.id', array())
            ->where('benchmarkId = ' . $mainTable . ".id")
            ->group('benchmarkId');
        return $select;
    }

    public function getYears4Select()
    {
        $select = $this->_db->select()
            ->from($this->_getPair(), array('year'))
            ->group('year')
            ->order('year');
        $years  = $this->_db->fetchCol($select);
        return array_combine($years, $years);
    }
}