<?php

namespace app\modules\crm\models;

use yii\data\ActiveDataProvider;
use yii\data\Sort;
use yii\db\ActiveQuery;
use yii\db\Expression;
use yii\helpers\ArrayHelper;

class ClientSearch extends Client
{
    public $registrationDateFrom;
    public $registrationDateTo;
    public $endDateFrom;
    public $endDateTo;
    public $mainProgram;

    public $phone;
    public $fio;
    public $companyName;
    public $companyNameFio;
    public $email;
    public $contractNumber;
    public $service;
    public $cameFrom;
    public $requestNumber;
    public $timeDiff;
    public $remindersCount;
    public $actionsCount;
    public $searchInArchive;
    public $searchInTrash;
    public $executorId;
    public $plan;
    public $result;
    public $billNumber;


    /** @var ActiveQuery */
    private $query;

    public function init()
    {
        parent::init();
        $this->query = static::find();
        $this->query->groupBy('clients.id');
        $this->query->indexBy('id');

        $this->work_status = null;
    }

    public function rules()
    {
        return [
            [['phone', 'fio', 'companyName', 'companyNameFio', 'email', 'contractNumber', 'plan', 'result', 'billNumber'], 'trim'],
            [['inn', 'manager_id', 'remindersCount', 'actionsCount', 'executorId'], 'integer'],
            [['work_status', 'phone', 'fio', 'companyName', 'companyNameFio', 'email', 'contractNumber',
                'service', 'cameFrom', 'requestNumber', 'timeDiff', 'plan', 'result', 'billNumber', 'mainProgram'], 'string'],
            [['registrationDateFrom', 'registrationDateTo'], 'date', 'format' => 'php:' . \Yii::$app->params['dateFormatPhp']],

            [['searchInArchive', 'searchInTrash'], 'boolean'],[['endDateFrom', 'endDateTo'], 'date', 'format' => 'php:' . \Yii::$app->params['dateFormatPhp']],
        ];
    }

    public function attributeLabels()
    {
        return ArrayHelper::merge(parent::attributeLabels(), [
            'phone' => 'Телефон',
            'fio' => 'ФИО',
            'companyName' => 'Название клиента',
            'companyNameFio' => 'Название клиента или ФИО',
            'email' => 'E-mail',
            'contractNumber' => 'Номер договора',
            'service' => 'Услуга',
            'cameFrom' => 'Откуда пришел клиент',
            'requestNumber' => 'Номер заявки',
            'timeDiff' => 'Время клиента',
            'remindersCount' => 'Кол. напоминаний',
            'actionsCount' => 'Кол. действий',
            'searchInArchive' => 'Искать в архиве',
            'searchInTrash' => 'Искать в корзине',
            'executorId' => 'Исполнитель',
            'plan' => 'Планируется',
            'result' => 'Результат',
            'billNumber' => 'Номер счета',
            'id' => 'ID клиента',
            'mainProgram' => 'Название программы',
        ]);
    }

    public function search(array $params)
    {
        $this->query->joinWith(['lastCompany']);



        $dataProvider = new ActiveDataProvider([
            'query' => $this->query,
            'sort' => $this->getSort(),
            'pagination' => [
                'pageSize' => 50,
            ],
        ]);

        $this->load($params);

        $remindersQuery = Action::find()
            ->from(['actions' => Action::tableName()])
            ->andWhere(['remind' => 1])
            ->andWhere('[[actions.client_id]] = [[clients.id]]')
            ->select(new Expression('COUNT(*)'));

        $actionsQuery = Action::find()
            ->from(['actions' => Action::tableName()])
            ->andWhere('[[actions.client_id]] = [[clients.id]]')
            ->select(new Expression('COUNT(*)'));

        $this->query
            ->select('clients.*')
            ->addSelect(['remindersCount' => $remindersQuery])
            ->addSelect(['actionsCount' => $actionsQuery]);

        if (!$this->validate()) {
            return $dataProvider;
        }

        $this->setFilter();
        //$this->query->andFilterWhere(['like','programs.name' , $this->mainProgram]);
     //$this->query->andFilterWhere(['program.type' => $this->service]);
        return $dataProvider;
    }

    public function setFilter()
    {
        $this->query->joinWith([
            'phones',
            'contacts',
            'companies',
            'emails',
            'documents' => function (ActiveQuery $query) {
                $query->joinWith(['documentBills']);
            },
            'actions',
            'listeners' => function (ActiveQuery $query) {
                $query->joinWith([
                    'programs' => function (ActiveQuery $query) {
                        $query->joinWith(['program']);
                    }
                ]);
            },
        ]);
    //  print_r($this); //statuis



    $this->query->andFilterWhere(['like','program.name' , $this->mainProgram]);
        $this->query
            ->andFilterWhere(['like', 'group_bills.bill_number', $this->billNumber])
            ->andFilterWhere([
                'or',
                ['like', 'phones.phone', $this->phone],
                ['like', 'contacts.phone', $this->phone],
                ['like', 'listeners.phone', $this->phone],
            ])
            ->andFilterWhere([
                'or',
                ['like', 'companies.name', $this->companyNameFio],
                ['like', 'contacts.fio', $this->companyNameFio],
                ['like', 'listeners.fio', $this->companyNameFio]
            ])
            ->andFilterWhere([
                'or',
                ['like', 'emails.email', $this->email],
                ['like', 'contacts.email', $this->email],
                ['like', 'listeners.email', $this->email],
            ])
            ->andFilterWhere(['like', 'documents.contract_number', $this->contractNumber])
            ->andFilterWhere(['like', 'companies.request_number', $this->requestNumber])
            ->andFilterWhere(['program.type' => $this->service])
            ->andFilterWhere(['manager_id' => $this->manager_id])
            ->andFilterWhere(['work_status' => $this->work_status])
            ->andFilterWhere(['companies.came_from' => $this->cameFrom])
            ->andFilterWhere(['inn' => $this->inn])//id

            ->andFilterWhere(['actions.executor_id' => $this->executorId])
            ->andFilterWhere(['like', 'actions.plan', $this->plan])
            ->andFilterWhere(['like', 'actions.result', $this->result])
            ->andFilterHaving(['remindersCount' => $this->remindersCount])
            ->andFilterHaving(['actionsCount' => $this->actionsCount])
            ->andFilterWhere([
                '>=',
                'registration_date',
                $this->registrationDateFrom ? strtotime($this->registrationDateFrom . ' 00:00:00') : null
            ])
            ->andFilterWhere([
                '<=',
                'registration_date',
                $this->registrationDateTo ? strtotime($this->registrationDateTo . ' 23:59:59') : null
            ])->andFilterWhere([
                '>=',
                'DATE(programs.end_date)',
                $this->endDateFrom ? date("Y-m-d",strtotime($this->endDateFrom)) : null
            ])
            ->andFilterWhere([
                '<=',
                'DATE(programs.end_date)',
                $this->endDateTo ? date("Y-m-d",strtotime($this->endDateTo)) : null
            ]
            );
                //
                //die(date("Y-m-d",strtotime($this->endDateTo)));
        if ($this->executorId) {
            $this->query->andWhere([
                'or',
                ['actions.result' => ''],
                ['actions.result' => null],
            ]);
        }
        //print_r($this->_sql);
        //print_r($this);
    }

    public function getSort(): Sort
    {
        return new Sort([
            'defaultOrder' => ['clients.id' => SORT_DESC],
            'attributes' => [
                'clients.id',
                'registration_date',
                'work_status',
                'companyName' => [
                    'asc' => ['lastCompany.name' => SORT_ASC],
                    'desc' => ['lastCompany.name' => SORT_DESC],
                ],
                'timeDiff' => [
                    'asc' => ['lastCompany.time_diff' => SORT_ASC],
                    'desc' => ['lastCompany.time_diff' => SORT_DESC],
                ],
                'remindersCount',
                'actionsCount',
            ],
        ]);
    }

    public function getQuery(): ActiveQuery
    {
        return $this->query;
    }
}