<?php

namespace modules\custom\models;

use Yii;
use yii\base\Model;
use yii\data\{ArrayDataProvider, ActiveDataProvider};
use modules\custom\models\Assetsbe;
use yii\db\Expression;
/**
 * AssetsbeSearch represents the model behind the search form about `backend\models\Assetsbe`.
 */
class Assetsbesearch extends Assetsbe{
	
	public $product_name;
	public $existingids;
	public $created_at_range, $updated_at_range;
	
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id','clientid', 'created_by', 'updated_by', 'deleted_by', 'status', 'relatedid', 'notes', 'productid', 'model', 'packed', 'serialnumber', 'seagoserialnumber', 'agent', 'ownerid','assettype'], 'safe'],
            [['created_at', 'updated_at', 'deleted_at', 'capacity', 'purchasedate', 'invoicenumber', 'registered'], 'integer'],
            [['discountservice'],'boolean'],
            [['product_name','updatedby_name','createdby_name', 'existingids'],'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = Assetsbe::find();

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            
			'sort' => [
				'defaultOrder' => [
				'created_at' => SORT_DESC,
				]
			]
        ]);

        $this->load($params);

        if(!empty($this->product_name)){
            $query->joinwith(['product']);
            $query->andFilterWhere(['like', 'products.product_name', $this->product_name]);
        }
        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
		
		//$dataProvider->sort->attributes['product_name'] = ['asc' => ['products.product_name' => SORT_ASC], 'desc' => ['products.product_name' => SORT_DESC],];
		// grid filtering conditions
        $query->andFilterWhere([
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
            'deleted_at' => $this->deleted_at,
            'capacity' => $this->capacity,
            'purchasedate' => $this->purchasedate,
            'invoicenumber' => $this->invoicenumber,
            'registered' => $this->registered,
            'clientid' => $this->clientid,
        ]);

        $query->andFilterWhere(['like', 'id', $this->id])
            ->andFilterWhere(['like', 'created_by', $this->created_by])
            ->andFilterWhere(['like', 'createdby_name', $this->createdby_name])
            ->andFilterWhere(['like', 'updated_by', $this->updated_by])
            ->andFilterWhere(['like', 'updatedby_name', $this->updatedby_name])
            ->andFilterWhere(['like', 'deleted_by', $this->deleted_by])
            ->andFilterWhere(['like', 'status', $this->status])
            ->andFilterWhere(['like', 'relatedid', $this->relatedid])
            ->andFilterWhere(['like', 'assettype', $this->assettype])
            ->andFilterWhere(['like', 'notes', $this->notes])
            ->andFilterWhere(['like', 'productid', $this->productid])
            ->andFilterWhere(['like', 'model', $this->model])
            ->andFilterWhere(['like', 'packed', $this->packed])
            ->andFilterWhere(['like', 'serialnumber', $this->serialnumber])
            ->andFilterWhere(['like', 'seagoserialnumber', $this->seagoserialnumber])
            ->andFilterWhere(['=', 'discountservice', $this->discountservice])
            ->andFilterWhere(['like', 'agent', $this->agent])
            ->andFilterWhere(['like', 'ownerid', $this->ownerid]);

        return $dataProvider;
    }
    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function ServiceSearchEmail($params, $days, $limit = 5000)
    {
        $activeSerials = array_values(array_column(\modules\activities\models\Activitiesitemsbe::find()
        ->select('activities_items.serial')
        ->where(['not', ['activities_items.status' => ['Complete', 'Deleted', '', '-', 'Quote Needed']]])
        ->andwhere(['not', ['activities_items.serial' => [' ', null]]])
        ->distinct()
        ->asArray()
        ->all(), 'serial'));
        //$SD = new yii\db\Expression("json_value(`assets`.`data`, '$.next_service')");
        
		$SD = new \yii\db\Expression("STR_TO_DATE(json_value(`assets`.`data`, '$.next_service'), '%Y-%m-%d')");
		$MS = new \yii\db\Expression("STR_TO_DATE(json_value(`assets`.`data`, '$.mail_sent'), '%Y-%m-%d')");
        //$LD = new yii\db\Expression("json_value(`assets`.`data`, '$.last_service')");
        $date = date('Y-m-d' , strtotime('+' . $days . 'days'));
        $msdate = date('Y-m-d' , strtotime('today'));
		//$todate = new \yii\db\Expression("STR_TO_DATE('$date', '%Y-%m-%d')");
        $query = Assetsbe::find()
            ->where(['not', ['is', 'clientid', null]])
            ->andwhere(['not', ['assets.status' => ['Complete', 'Deleted', '', '-', 'Finished']]])
            ->andFilterWhere(['<=', $SD, $date])
            ->andWhere(['in', 'assettype', ['liferaft', 'lifejacket']])
            ->andWhere(['in', 'noreminder', [false, '', null]])
            ->andFilterWhere(['>=', $SD, '2022-01-01'])
			->andWhere(['not in', 'serialnumber', $activeSerials])
			->andWhere(['not like', 'sys_user.email', ['chalvingtongroup.com', 'seagogroup.com', 'seagoyachting.co.uk']])
            ->joinwith('owner')
            ->limit($limit);
        $this->load($params);
        if (yii::$app->request->isConsoleRequest == true){
            $query->andWhere(['is', $MS, null]);
        }

        if(!empty($this->product_name)){
            $query->joinwith(['product']);
            $query->andFilterWhere(['like', 'products.product_name', $this->product_name]);
        }
        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
			'sort' => [
				'defaultOrder' => [
				    'created_at' => SORT_DESC,
				]
			]
        ]);


        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
		
		//$dataProvider->sort->attributes['product_name'] = ['asc' => ['products.product_name' => SORT_ASC], 'desc' => ['products.product_name' => SORT_DESC],];
		// grid filtering conditions
        $query->andFilterWhere([
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
            'deleted_at' => $this->deleted_at,
            'capacity' => $this->capacity,
            'purchasedate' => $this->purchasedate,
            'invoicenumber' => $this->invoicenumber,
            'registered' => $this->registered,
            'clientid' => $this->clientid,
        ]);

        $query->andFilterWhere(['like', 'id', $this->id])
            ->andFilterWhere(['like', 'created_by', $this->created_by])
            ->andFilterWhere(['like', 'createdby_name', $this->createdby_name])
            ->andFilterWhere(['like', 'updated_by', $this->updated_by])
            ->andFilterWhere(['like', 'updatedby_name', $this->updatedby_name])
            ->andFilterWhere(['like', 'deleted_by', $this->deleted_by])
            ->andFilterWhere(['like', 'status', $this->status])
            ->andFilterWhere(['like', 'relatedid', $this->relatedid])
            ->andFilterWhere(['like', 'assettype', $this->assettype])
            ->andFilterWhere(['like', 'notes', $this->notes])
            ->andFilterWhere(['like', 'productid', $this->productid])
            ->andFilterWhere(['like', 'model', $this->model])
            ->andFilterWhere(['like', 'packed', $this->packed])
            ->andFilterWhere(['like', 'serialnumber', $this->serialnumber])
            ->andFilterWhere(['like', 'seagoserialnumber', $this->seagoserialnumber])
            ->andFilterWhere(['=', 'discountservice', $this->discountservice])
            ->andFilterWhere(['like', 'agent', $this->agent])
            ->andFilterWhere(['like', 'ownerid', $this->ownerid]);

        return $dataProvider;
    }
    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function ServiceSearch($params, $days)
    {


        $SD = new yii\db\Expression("json_value(`assets`.`data`, '$.next_service')");
        $date = strtotime('+' . $days . 'days');
        $query = Assetsbe::find()
            ->andFilterWhere(['<=', $SD, date('Y-m-d' , $date)]);

        $this->load($params);

        if(!empty($this->product_name)){
            $query->joinwith(['product']);
            $query->andFilterWhere(['like', 'products.product_name', $this->product_name]);
        }
        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
			'sort' => [
				'defaultOrder' => [
				    'created_at' => SORT_DESC,
				]
			]
        ]);


        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
		
		//$dataProvider->sort->attributes['product_name'] = ['asc' => ['products.product_name' => SORT_ASC], 'desc' => ['products.product_name' => SORT_DESC],];
		// grid filtering conditions
        $query->andFilterWhere([
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
            'deleted_at' => $this->deleted_at,
            'capacity' => $this->capacity,
            'purchasedate' => $this->purchasedate,
            'invoicenumber' => $this->invoicenumber,
            'registered' => $this->registered,
            'clientid' => $this->clientid,
        ]);

        $query->andFilterWhere(['like', 'id', $this->id])
            ->andFilterWhere(['like', 'created_by', $this->created_by])
            ->andFilterWhere(['like', 'createdby_name', $this->createdby_name])
            ->andFilterWhere(['like', 'updated_by', $this->updated_by])
            ->andFilterWhere(['like', 'updatedby_name', $this->updatedby_name])
            ->andFilterWhere(['like', 'deleted_by', $this->deleted_by])
            ->andFilterWhere(['like', 'status', $this->status])
            ->andFilterWhere(['like', 'relatedid', $this->relatedid])
            ->andFilterWhere(['like', 'assettype', $this->assettype])
            ->andFilterWhere(['like', 'notes', $this->notes])
            ->andFilterWhere(['like', 'productid', $this->productid])
            ->andFilterWhere(['like', 'model', $this->model])
            ->andFilterWhere(['like', 'packed', $this->packed])
            ->andFilterWhere(['like', 'serialnumber', $this->serialnumber])
            ->andFilterWhere(['like', 'seagoserialnumber', $this->seagoserialnumber])
            ->andFilterWhere(['=', 'discountservice', $this->discountservice])
            ->andFilterWhere(['like', 'agent', $this->agent])
            ->andFilterWhere(['like', 'ownerid', $this->ownerid]);

        return $dataProvider;
    }
    public function Duplicates($params)
    {
        $query1 = Assetsbe::find()
        ->select(['serialnumber', 'assettype', 'COUNT(*) as count'])
        ->groupBy(['serialnumber', 'assettype'])
        ->having('count > 1')
        ->asArray()
        ->all();
        
        $query = Assetsbe::find()
        ->where(['in', 'serialnumber', array_column($query1, 'serialnumber')])
        ->andWhere(['not', ['serialnumber' =>  [null, '--',' ', '?']]])
        ->joinwith(['product']);

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
			'sort' => [
				'defaultOrder' => [
				    'serialnumber' => SORT_ASC,
				]
			]
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
		
		//$dataProvider->sort->attributes['product_name'] = ['asc' => ['products.product_name' => SORT_ASC], 'desc' => ['products.product_name' => SORT_DESC],];
		// grid filtering conditions
        $query->andFilterWhere([
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
            'deleted_at' => $this->deleted_at,
            'capacity' => $this->capacity,
            'purchasedate' => $this->purchasedate,
            'invoicenumber' => $this->invoicenumber,
            'registered' => $this->registered,
            'clientid' => $this->clientid,
        ]);

        $query->andFilterWhere(['like', 'id', $this->id])
            ->andFilterWhere(['like', 'created_by', $this->created_by])
            ->andFilterWhere(['like', 'createdby_name', $this->createdby_name])
            ->andFilterWhere(['like', 'updated_by', $this->updated_by])
            ->andFilterWhere(['like', 'updatedby_name', $this->updatedby_name])
            ->andFilterWhere(['like', 'deleted_by', $this->deleted_by])
            ->andFilterWhere(['like', 'products.product_name', $this->product_name])
            ->andFilterWhere(['like', 'status', $this->status])
            ->andFilterWhere(['like', 'relatedid', $this->relatedid])
            ->andFilterWhere(['like', 'assettype', $this->assettype])
            ->andFilterWhere(['like', 'notes', $this->notes])
            ->andFilterWhere(['like', 'productid', $this->productid])
            ->andFilterWhere(['like', 'model', $this->model])
            ->andFilterWhere(['like', 'packed', $this->packed])
            ->andFilterWhere(['like', 'serialnumber', $this->serialnumber])
            ->andFilterWhere(['like', 'seagoserialnumber', $this->seagoserialnumber])
            ->andFilterWhere(['=', 'discountservice', $this->discountservice])
            ->andFilterWhere(['like', 'agent', $this->agent])
            ->andFilterWhere(['like', 'ownerid', $this->ownerid]);

        return $dataProvider;
    }
    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function clientsearch($params)
    {
        $query = Assetsbe::find()
        ->where(['not', ['clientid' => null]]);
        
        
        $this->load($params);
        if(empty($this->existingids) == false){
            $query->andwhere( ['not in','id', $this->existingids]);
        }
        if(empty($this->clientid) == false){
            $query->andwhere( ['in','clientid', $this->clientid]);
        } else {
            $query->andwhere( ['clientid' => $this->clientid]);

        }
        // add conditions that should always apply here

            $dataProvider = new ActiveDataProvider([
                'query' => $query,
                'pagination' => false,
            ]);


        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
		
		$dataProvider->sort->attributes['product_name'] = ['asc' => ['products.product_name' => SORT_ASC], 'desc' => ['products.product_name' => SORT_DESC],];
		// grid filtering conditions
        $query->andFilterWhere([
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
            'deleted_at' => $this->deleted_at,
            'capacity' => $this->capacity,
            'purchasedate' => $this->purchasedate,
            'invoicenumber' => $this->invoicenumber,
            'registered' => $this->registered,
            'clientid' => $this->clientid,
        ]);

        $query->andFilterWhere(['like', 'id', $this->id])
            ->andFilterWhere(['like', 'created_by', $this->created_by])
            ->andFilterWhere(['like', 'createdby_name', $this->createdby_name])
            ->andFilterWhere(['like', 'updated_by', $this->updated_by])
            ->andFilterWhere(['like', 'updatedby_name', $this->updatedby_name])
            ->andFilterWhere(['like', 'deleted_by', $this->deleted_by])
            ->andFilterWhere(['like', 'products.product_name', $this->product_name])
            ->andFilterWhere(['like', 'status', $this->status])
            ->andFilterWhere(['like', 'relatedid', $this->relatedid])
            ->andFilterWhere(['like', 'assettype', $this->assettype])
            ->andFilterWhere(['like', 'notes', $this->notes])
            ->andFilterWhere(['like', 'productid', $this->productid])
            ->andFilterWhere(['like', 'model', $this->model])
            ->andFilterWhere(['like', 'packed', $this->packed])
            ->andFilterWhere(['like', 'serialnumber', $this->serialnumber])
            ->andFilterWhere(['like', 'seagoserialnumber', $this->seagoserialnumber])
            ->andFilterWhere(['=', 'discountservice', $this->discountservice])
            ->andFilterWhere(['like', 'agent', $this->agent])
            ->andFilterWhere(['like', 'ownerid', $this->ownerid]);

        return $dataProvider;
    }

    public function servicedates($params){
        $query = (new \yii\db\Query())
            ->select([
                'forms.id',
                'forms.data',
                'forms.status',
                'formnsdate' => new Expression("JSON_VALUE(forms.data, '$.date_of_service')"),
                'Form Next Service Date' => new Expression("JSON_VALUE(forms.data, '$.date_of_next_service')"),
                'Form Serial' => new Expression("JSON_VALUE(forms.data, '$.serial_number')"),
                'form_assetid' => new Expression("JSON_VALUE(forms.data, '$.assetid')"),
                'form_productid' => new Expression("JSON_VALUE(forms.data, '$.productid')"),
                'due' => new Expression("from_unixtime(A.duetime)"),
                'arrived' => new Expression("from_unixtime(A.arrived)"),
                'jobid' => new Expression("A.id"),
                'AI.type',
                'AI.serial',
                'AI_assetid' => 'AI.assetid',
                'AI_product' => 'AI.product',
                'Asset Service Date' => new Expression("JSON_VALUE(assets.data, '$.last_service')"),
                'Asset Next Service Date' => new Expression("JSON_VALUE(assets.data, '$.next_service')"),
                'assets.serialnumber',
                'assetid' => 'assets.id',
            ])
            ->from('forms')
            ->leftJoin('activities_items AI', 'AI.id = forms.relatedid')
            ->leftJoin('activities A', 'A.id = AI.relatedid')
            ->leftJoin('assets', 'assets.id = AI.assetid')
            ->where(['like', 'forms.form', 'servicelife%', false])
            ->andwhere(['not', ['AI.serial' => ['', null]]])
            ->andWhere(['not', ['AI.type' => null]])
            ->andWhere(['>=', new Expression("from_unixtime(A.duetime)"), '2020-01-01']);
            

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);


        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
        return $dataProvider;
    }
}
