php hell

gistfile1.php

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

namespace app\models\search;

use common\models\base\HuActiveRecord;
use app\models\search\Subject;
use common\models\core\System;
use yii\data\SqlDataProvider;

/**
 * This is the view model class for table "nano-object"
 * It is only relevant to the web-application part of this project.
 *
 */
class SearchObject extends HuActiveRecord
{

    public $mode;
    public $detail = FALSE;
    public $activeFilter = 1;
    public $subjectNameFilter;
    public $organisationSizeFilter = [];
    public $organisationStatusFilter = [];
    public $organisationTraitFilter = [];
    public $organisationTypeFilter = [];
    public $regionFilter = [];
    public $countryFilter = [];
    public $sectorFilter = [];
    public $subsectorFilter = [];
    public $nanoFormFilter = [];
    public $nanoStructureFilter = [];
    public $nanoMaterialFilter = [];
    public $byCountry = null;
    public $bySector = null;
    public $byActor = null;
    protected $_multiMode = false;
    protected $_select;
    protected $_query;
    protected $_dataProvider;
    protected $_text = 'abstract';

    public function init()
    {
        $this->_select = [];
        $this->_query = new \yii\db\Query;
        parent::init();
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [[
            //Filters
            'activeFilter', 'subjectNameFilter',
            'organisationSizeFilter', 'organisationStatusFilter',
            'organisationTraitFilter', 'organisationTypeFilter',
            'countryFilter', 'regionFilter', 'sectorFilter', 'subsectorFilter'
                ], 'safe'],
        ];
    }

    /**
     *
     * @param type $params
     * @return type
     */
    public function search($params)
    {

        if (is_array($this->mode)) {
            $this->_multiMode = true;
        }
        $this->generateDefaultColumns();


        //Load filters from params and assert if filter is set
        $unfiltered = !($this->load($params) && $this->validate());

        // separate step to check if all filters are empty, should be unfiltered true
        $scope = $this->formName();
        if (isset($params[$scope])) {
            $non_empty_filters = array_filter($params[$scope]);
            if (!$non_empty_filters) {
                $unfiltered = true;
            }
        }

        $this->_setupProvider();
        if (!$unfiltered) {
            $this->_filterProvider();
        }
        //For counter (much faster)
        $countQuery = $this->_query;
        $countQuery->select = ['id' => 'COUNT(distinct nano_object.id)'];
        $countQuery->groupBy = NULL; //no group by, just count distinct objects
        $this->_dataProvider->totalCount = $countQuery->createCommand()->queryScalar();

        return $this->_dataProvider;
    }

    protected function buildDetailSelect()
    {
        $this->_select['id'] = 'nano_object.id';
        $this->_select['role'] = 'subject_object_role.name';
        $this->_select['name'] = 'nano_object_data.name';
        $this->_select['sectors'] = 'GROUP_CONCAT(distinct CONCAT(sector.code,sector.id) SEPARATOR \';\')';
        $this->_select['subsectors'] = 'GROUP_CONCAT(distinct CONCAT(sector.code,\':\',subsector.code,\':\',subsector.name,\':\',sector.name) SEPARATOR \';\')';
        switch ($this->mode) {
            case 'project': {
                    $this->_select['type'] = "project_type.name";
                    break;
                }
            case 'patent': {
                    $this->_select['type'] = "patent_authority.name";
                    break;
                }
        }
        $this->_query->select = $this->_select;
    }

    /**
     * Must be called after child setup
     */
    protected function buildSelect()
    {

        $this->_select['id'] = 'nano_object.id';
        $this->_select['name'] = 'nano_object_data.name';
        $this->_select['sectors'] = 'GROUP_CONCAT(distinct CONCAT(sector.code,sector.id) SEPARATOR \';\')';
        //    $this->_select['sector_ids'] = 'GROUP_CONCAT(distinct sector.id SEPARATOR \';\')';
        $this->_select['year_entry'] = 'nano_object.year_entry';
        $this->_select['type'] = "system.name";
        //When not multimode mode, object description is queried through (existing) single join
        if (!$this->_multiMode) {
            $this->_select['text'] = "$this->mode.$this->_text";
        }
        if (isset($this->byActor)) {
            $this->_select['role'] = 'subject_object_role.name';
        }
        $this->_query->select = $this->_select;
    }

    private function buildFrom()
    {
        $this->_query->from(['search_object nano_object']);
        $this->_query->innerJoin('nano_object nano_object_data', 'nano_object.id=nano_object_data.id');
        $this->_query->innerJoin('system', 'system.id=nano_object.type' . ($this->_multiMode ? ' AND system.id IN (' . implode(',', array_keys(System::getNodeList($this->mode))) . ')' : ''));

        if (!$this->_multiMode) {
            $this->_query->innerJoin($this->mode, "$this->mode.id=nano_object.id");
        }
    }

    protected function buildJoin()
    {

        if (!isset($this->sectorFilter)) {
            $this->_query->leftJoin('search_subject_object subject_object', 'subject_object.object=nano_object.id');
        }
        //Add branching join
        $this->_query->leftJoin('search_object_sector os', 'nano_object.id=os.object');
        $this->_query->innerJoin('search_sector sector', 'sector.id = os.sector');
        $this->_query->innerJoin('search_sector subsector', 'subsector.id = os.subsector');
        if (isset($this->byActor)) {
            $this->_query->innerJoin('subject_object_role', 'subject_object.role=subject_object_role.id');
        }
    }

    protected function buildSort()
    {
        /* $this->_dataProvider->sort->defaultOrder = [
          'year_entry' => SORT_DESC,
          ]; */

        $this->_dataProvider->sort->attributes['year_entry'] = [
            'asc' => ['year_entry' => SORT_ASC],
            'desc' => ['year_entry' => SORT_DESC],
        ];
        $this->_dataProvider->sort->attributes['name'] = [
            'asc' => ['name' => SORT_ASC],
            'desc' => ['name' => SORT_DESC],
        ];
        $this->_dataProvider->sort->attributes['type'] = [
            'asc' => ['system.name' => SORT_ASC],
            'desc' => ['system.name' => SORT_DESC],
        ];
        $this->_dataProvider->sort->attributes['role'] = [
            'asc' => ['subject_object_role.name' => SORT_ASC],
            'desc' => ['subject_object_role.name' => SORT_DESC],
        ];
    }

    protected function buildFilter()
    {
        $subQuery = new \yii\db\Query;
        $subQuery->select(['id' => 'object'])->from('search_object_sector');
        $expression = new \yii\db\Expression('`nano_object`.`id`');
        $subQuery->where(['object' => $expression]);
        $subQuery->andFilterWhere(['IN', 'sector', $this->sectorFilter]);
        $subQuery->andFilterWhere(['IN', 'subsector', $this->subsectorFilter]);
        $this->_query->andFilterWhere(['EXISTS', $subQuery]);
        //Semi-Join on search_subject if organisation or location filter is set

        $subQuery = new \yii\db\Query;
        if (!isset($this->sectorFilter)) {
            $subQuery->select(['id' => 'subject'])->from('search_subject');
            $expression = new \yii\db\Expression('`subject_object`.`subject`');
            $subQuery->where(['id' => $expression]);
        }
        if (isset($this->byCountry)) {
            $subQuery->andFilterWhere(['country' => new \yii\db\Expression($this->byCountry)]);
        }
        if (!isset($this->byCountry)) {
            $subQuery->andFilterWhere(['IN', 'region', $this->regionFilter]);
            $subQuery->andFilterWhere(['IN', 'country', $this->countryFilter]);
        }

        if (!empty($this->subjectNameFilter)) {
            $subQuery->andFilterWhere(['LIKE', 'name', '%' . $this->subjectNameFilter . '%', false]);
        }
        $subQuery->andFilterWhere(['IN', 'organisation_size', $this->organisationSizeFilter]);
        $subQuery->andFilterWhere(['IN', 'organisation_status', $this->organisationStatusFilter]);
        $subQuery->andFilterWhere(['IN', 'organisation_trait', $this->organisationTraitFilter]);
        $subQuery->andFilterWhere(['IN', 'organisation_type', $this->organisationTypeFilter]);
        $this->_query->andFilterWhere(['EXISTS', $subQuery]);
    }

    private function buildSubSectorFilter()
    {
        $subQuery = $this->_buildSectorFilterQuery();
        $exp = new \yii\db\Expression($this->bySector);
        $subQuery->andWhere(['search_object_sector.subsector' => $exp]);
        $this->_query->andWhere(['IN', 'nano_object.id', $subQuery]);
    }

    private function _buildSectorFilterQuery()
    {
        $subQuery = new \yii\db\Query;
        $subQuery->select(['id' => 'object'])->from('search_object_sector');
        $expression = new \yii\db\Expression('`nano_object`.`id`');
        $subQuery->where(['object' => $expression]);
        return $subQuery;
    }

    private function buildSubjectFilter()
    {

        $subQuery = $this->_buildSubjectFilterQuery();
        Subject::buildSubjectFilter($this, $subQuery);
        $this->_query->andFilterWhere(['EXISTS', $subQuery]);
    }

    private function buildCountryFilter()
    {
        $subQuery = $this->_buildSubjectFilterQuery();
        $exp = new \yii\db\Expression($this->byCountry);
        $subQuery->andWhere(['subject.country' => $exp]);
        $this->_query->andWhere(['IN', 'subject_object.subject', $subQuery]);
    }

    private function buildActorFilter()
    {
        $subQuery = $this->_buildSubjectFilterQuery();
        $exp = new \yii\db\Expression($this->byActor);
        $subQuery->andWhere(['subject.id' => $exp]);
        $this->_query->andWhere(['IN', 'subject_object.subject', $subQuery]);
    }

    private function _buildSubjectFilterQuery()
    {
        //Setup Subquery
        $subQuery = new \yii\db\Query;
        $subQuery->select(['id' => 'subject.id'])->from('search_subject subject');
        $expression = new \yii\db\Expression('`subject_object`.`subject`');
        $subQuery->where(['subject' => $expression]);
        return $subQuery;
    }

    private function _setupProvider()
    {
        if ($this->detail) {
            $this->buildDetailSelect();
        } else {
            $this->buildSelect();
        }
        $this->buildFrom();
        $this->buildJoin();
        if (isset($this->byActor)) {
            $this->buildActorFilter();
        }
        if (isset($this->byCountry)) {
            $this->buildCountryFilter();
        }
        if (isset($this->bySector)) {
            $this->buildSubSectorFilter();
        }
        $this->_query->groupBy(['nano_object.id']);

        $sql = $this->_query->createCommand()->getSql();
  //      var_dump($sql);
        //Super ugly publication hack
       // $sql = str_replace(' (`object`=`subject_object`.`object`) AND ', '', $sql);


        $this->_dataProvider = new SqlDataProvider([
            'sql' => $sql,
            'pagination' => [
                'defaultPageSize' => 10,
            ],
            /* 'pagination' => [
              'pageParam' => 'page',
              'pageSize' => 10,
              ], */
            'sort' => [
                'defaultOrder' => [
                    'name' => SORT_ASC
                ]
            ],
            'key' => function ($model) {
                return $model['id'];
            },
        ]);
        $this->buildSort();
    }

    private function _filterProvider()
    {
        $this->buildFilter();
        $sqlFiltered = $this->_query->createCommand()->getRawSql();
        $this->_dataProvider->sql = $sqlFiltered;
    }

    public function getGridColumns($idx = 'default')
    {
        if (isset($this->gridColumns[$idx])) {
            return $this->gridColumns[$idx];
        }
        return [];
    }

    protected function generateDefaultColumns()
    {

    }

}