Изменён тип хранения информации о проинвентаризированных, учтённых, и непроверенных...

[invent/invent.git] / models / ItemsSearch.php

diff --git a/models/ItemsSearch.php b/models/ItemsSearch.php
index 0d3216c..b3dbe9a 100644 (file)
--- a/models/ItemsSearch.php
+++ b/models/ItemsSearch.php
@@ -5,6 +5,11 @@ namespace app\models;
 use yii\base\Model;
 use yii\data\ActiveDataProvider;
 use app\models\Items;
+use app\models\Status;
+use app\models\Types;
+use app\models\Locations;
+use app\models\Regions;
+use app\models\Moving;
 
 /**
  * ItemsSearch represents the model behind the search form of `app\models\Items`.
@@ -17,8 +22,8 @@ class ItemsSearch extends Items
     public function rules()
     {
         return [
-            [['id'], 'integer'],
-            [['name', 'model', 'os', 'mac', 'serial', 'product', 'modelnumber', 'invent', 'date', 'comment', 'statusName'], 'safe'],
+            [['id', 'model_id'], 'integer'],
+            [['name', 'modelName', 'os', 'mac', 'serial', 'invent', 'date', 'comment', 'statusName', 'typeName', 'locationName', 'regionName'], 'safe'],
         ];
     }
 
@@ -31,6 +36,105 @@ class ItemsSearch extends Items
         return Model::scenarios();
     }
 
+    public function noinvent($params)
+    {
+
+        $query = Moving::find()
+            ->select('MAX(' . Moving::tableName() . '.id) AS mid');
+        if (isset($params->region) && ($params->region != ''))
+        {
+            $query->joinWith([ 'locations' ])
+                ->where([ 'region_id' => $params->region ]);
+        }
+        if (isset($params->location) && ($params->location != ''))
+        {
+            if (isset($params->region) && ($params->location != ''))
+            {
+                $query->andWhere(['location_id' => $params->location]);
+            }
+            else
+            {
+                $query->where(['location_id' => $params->location]);
+            }
+        }
+        $query->distinct('item_id')->groupBy('item_id');
+
+        $query = Items::find()
+            ->select(Items::tableName() . '.*, ' .
+                Locations::tableName() .  '.name AS locationName, ' .
+                Models::tableName() .     '.name AS modelName, ' .
+                Types::tableName() .      '.name AS typeName, ' .
+                Regions::tableName() .    '.name AS regionName, ' .
+                Status::tableName() .     '.name AS statusName ')
+            ->joinWith([ 'types', 'moving', 'status', 'locations', 'regions', 'models', ])
+            ->where([ 'in', Moving::tableName() . '.id', $query ])
+            ->andWhere([ 'checked' => 0 ]);
+
+        $dataProvider = new ActiveDataProvider([
+            'query' => $query,
+        ]);
+        $dataProvider->setSort([
+            'defaultOrder' => [
+                'id' => SORT_ASC,
+            ],
+        ]);
+
+        $this->load($params);
+        if (!$this->validate())
+        {
+            return $dataProvider;
+        }
+
+        // grid filtering conditions
+        $query->andFilterWhere([
+            'id'   => $this->id,
+        ])->andFilterWhere([
+            'ilike', Status::tableName() .    '.name', $this->statusName
+        ])->andFilterWhere([
+            'ilike', Models::tableName() .    '.name', $this->modelName
+        ])->andFilterWhere([
+            'ilike', Types::tableName() .     '.name', $this->typeName
+        ])->andFilterWhere([ 'OR', [
+            'ilike', Locations::tableName() . '.name', $this->regionName
+        ], [
+            'ilike', Regions::tableName() .   '.name', $this->regionName
+        ]])->andFilterWhere([ 'OR', [
+            'ilike', Locations::tableName() . '.name', $this->locationName
+        ], [
+            'ilike', Regions::tableName() .   '.name', $this->locationName
+        ]]);
+
+        $query->andFilterWhere(['ilike', 'name',        $this->name])
+            ->andFilterWhere(  ['ilike', 'os',          $this->os])
+            ->andFilterWhere(  ['ilike', 'mac',         $this->mac])
+            ->andFilterWhere(  ['ilike', 'serial',      $this->serial])
+            ->andFilterWhere(  ['ilike', 'invent',      $this->invent])
+            ->andFilterWhere(  ['ilike', 'comment',     $this->comment]);
+
+
+        $dataProvider->sort->attributes['modelName'] = [
+            'asc'  => [ Models::tableName() . '.name' => SORT_ASC ],
+            'desc' => [ Models::tableName() . '.name' => SORT_DESC ],
+        ];
+        $dataProvider->sort->attributes['statusName'] = [
+            'asc'  => [ Status::tableName() . '.name' => SORT_ASC ],
+            'desc' => [ Status::tableName() . '.name' => SORT_DESC ],
+        ];
+        $dataProvider->sort->attributes['typeName'] = [
+            'asc'  => [ Types::tableName() . '.name' => SORT_ASC ],
+            'desc' => [ Types::tableName() . '.name' => SORT_DESC ],
+        ];
+        $dataProvider->sort->attributes['locationName'] = [
+            'asc'  => [ Locations::tableName() . '.name' => SORT_ASC ],
+            'desc' => [ Locations::tableName() . '.name' => SORT_DESC ],
+        ];
+        $dataProvider->sort->attributes['regionName'] = [
+            'asc'  => [ Regions::tableName() . '.name' => SORT_ASC ],
+            'desc' => [ Regions::tableName() . '.name' => SORT_DESC ],
+        ];
+        return $dataProvider;
+    }
+
     /**
      * Creates data provider instance with search query applied
      *
@@ -40,33 +144,41 @@ class ItemsSearch extends Items
      */
     public function search($params)
     {
-        $query = Items::find();
-        $query->joinWith(['status']);
-        $query->joinWith(['types']);
+        // Особенность postgresql - нет first и last, потому последнее перемещение всегда имеет наибольший номер
+        $subQuery = Moving::find()
+                ->select('MAX(id) AS id')
+                ->distinct('item_id')
+                ->groupBy([ 'item_id' ]);
+
+        $query = Items::find()
+            ->select(Items::tableName() . '.*, ' .
+                Locations::tableName() .  '.name AS locationName, ' .
+                Models::tableName() .     '.name AS modelName, ' .
+                Types::tableName() .      '.name AS typeName, ' .
+                Regions::tableName() .    '.name AS regionName, ' .
+                Status::tableName() .     '.name AS statusName ')
+            ->joinWith([ 'types', 'moving', 'status', 'locations', 'regions', 'models', ])
+            ->where([ 'in', Moving::tableName() . '.id', $subQuery ]);
 
         // add conditions that should always apply here
 
         $dataProvider = new ActiveDataProvider([
             'query' => $query,
+            'pagination' => [
+                'pageSize' => \Yii::$app->session['pageSize'] ?? 20,
+            ],
         ]);
 
-        $dataProvider->sort->attributes['statusName'] = [
-            'asc' => [Status::className().'.name' => SORT_ASC],
-            'desc' => [Status::className().'.name' => SORT_DESC],
-        ];
-        $dataProvider->sort->attributes['typeName'] = [
-            'asc' => [Types::className().'.name' => SORT_ASC],
-            'desc' => [Types::className().'.name' => SORT_DESC],
-        ];
-        
         $dataProvider->setSort([
             'defaultOrder' => [
                 'id' => SORT_ASC,
             ],
         ]);
+
         $this->load($params);
 
-        if (!$this->validate()) {
+        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;
@@ -74,23 +186,52 @@ class ItemsSearch extends Items
 
         // grid filtering conditions
         $query->andFilterWhere([
-            'id' => $this->id,
-            'date' => $this->date,
-        ])->andFilterWhere([
-            'like', Status::tableName().'.name', $this->statusName
+            'id'   => $this->id,
         ])->andFilterWhere([
-            'like', Types::tableName().'.name', $this->typeName
-        ]);
+            'ilike', Status::tableName() .    '.name', $this->statusName
+        ])->andFilterWhere([ 'OR', [
+            'ilike', Models::tableName() .    '.name', $this->modelName
+        ], [
+            'ilike', Items::tableName() .     '.name', $this->modelName
+        ]])->andFilterWhere([
+            'ilike', Types::tableName() .     '.name', $this->typeName
+        ])->andFilterWhere([ 'OR', [
+            'ilike', Locations::tableName() . '.name', $this->regionName
+        ], [
+            'ilike', Regions::tableName() .   '.name', $this->regionName
+        ]])->andFilterWhere([ 'OR', [
+            'ilike', Locations::tableName() . '.name', $this->locationName
+        ], [
+            'ilike', Regions::tableName() .   '.name', $this->locationName
+        ]]);
+
+        $query->andFilterWhere(['ilike', 'name',        $this->name])
+            ->andFilterWhere(  ['ilike', 'os',          $this->os])
+            ->andFilterWhere(  ['ilike', 'mac',         $this->mac])
+            ->andFilterWhere(  ['ilike', 'serial',      $this->serial])
+            ->andFilterWhere(  ['ilike', 'invent',      $this->invent])
+            ->andFilterWhere(  ['ilike', 'comment',     $this->comment]);
 
-        $query->andFilterWhere(['ilike', 'name', $this->name])
-            ->andFilterWhere(['ilike', 'model', $this->model])
-            ->andFilterWhere(['ilike', 'os', $this->os])
-            ->andFilterWhere(['ilike', 'mac', $this->mac])
-            ->andFilterWhere(['ilike', 'serial', $this->serial])
-            ->andFilterWhere(['ilike', 'product', $this->product])
-            ->andFilterWhere(['ilike', 'modelnumber', $this->modelnumber])
-            ->andFilterWhere(['ilike', 'invent', $this->invent])
-            ->andFilterWhere(['ilike', 'comment', $this->comment]);
+        $dataProvider->sort->attributes['modelName'] = [
+            'asc'  => [Models::tableName() . '.name' => SORT_ASC],
+            'desc' => [Models::tableName() . '.name' => SORT_DESC],
+        ];
+        $dataProvider->sort->attributes['statusName'] = [
+            'asc'  => [Status::tableName() . '.name' => SORT_ASC],
+            'desc' => [Status::tableName() . '.name' => SORT_DESC],
+        ];
+        $dataProvider->sort->attributes['typeName'] = [
+            'asc'  => [Types::tableName() . '.name' => SORT_ASC],
+            'desc' => [Types::tableName() . '.name' => SORT_DESC],
+        ];
+        $dataProvider->sort->attributes['locationName'] = [
+            'asc'  => [Locations::tableName() . '.name' => SORT_ASC],
+            'desc' => [Locations::tableName() . '.name' => SORT_DESC],
+        ];
+        $dataProvider->sort->attributes['regionName'] = [
+            'asc'  => [Regions::tableName() . '.name' => SORT_ASC],
+            'desc' => [Regions::tableName() . '.name' => SORT_DESC],
+        ];
 
         return $dataProvider;
     }