X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=models%2FItemsSearch.php;h=b3dbe9a52096d3512347a47c09984214b4878cb5;hb=6a4e0c86e4e59436b1e52efd10fdf0cfdf345712;hp=247ee3ca6c7c1e0ef7df1923193827372d50d2f0;hpb=44ab19635dbc7cfa43df07b692c78d40b0473d50;p=invent%2Finvent.git

diff --git a/models/ItemsSearch.php b/models/ItemsSearch.php
index 247ee3c..b3dbe9a 100644
--- a/models/ItemsSearch.php
+++ b/models/ItemsSearch.php
@@ -9,6 +9,7 @@ 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`.
@@ -21,8 +22,8 @@ class ItemsSearch extends Items
     public function rules()
     {
         return [
-            [['id', 'state_id', 'type_id'], 'integer'],
-            [['name', 'model', 'os', 'mac', 'serial', 'product', 'modelnumber', 'invent', 'date', 'comment', 'statusName', 'typeName', 'locationName', 'regionName'], 'safe'],
+            [['id', 'model_id'], 'integer'],
+            [['name', 'modelName', 'os', 'mac', 'serial', 'invent', 'date', 'comment', 'statusName', 'typeName', 'locationName', 'regionName'], 'safe'],
         ];
     }
 
@@ -35,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
      *
@@ -44,19 +144,29 @@ class ItemsSearch extends Items
      */
     public function search($params)
     {
-        $query = Items::find();
-        $query->joinWith(['status']);
-        $query->joinWith(['types']);
-<<<<<<< HEAD
-=======
-        $query->joinWith(['locations']);
-//        $query->joinWith(['regions']);
->>>>>>> develop
+        // Особенность 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->setSort([
@@ -67,7 +177,8 @@ class ItemsSearch extends Items
 
         $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;
@@ -75,45 +186,53 @@ 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
-        ])->andFilterWhere([
-            'like', Locations::tableName().'.name', $this->locationName
-//        ])->andFilterWhere([
-//            'like', Regions::tableName().'.name', $this->regionName
-        ]);
+            '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', '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]);
+        $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],
+            '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],
+            '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],
+            '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],
-//        ];
-        
+        $dataProvider->sort->attributes['regionName'] = [
+            'asc'  => [Regions::tableName() . '.name' => SORT_ASC],
+            'desc' => [Regions::tableName() . '.name' => SORT_DESC],
+        ];
+
         return $dataProvider;
     }
 }