public function search($params) { $query = Message::find()->innerJoinWith(['region' => function ($q) { /* @var \yii\db\ActiveQuery $q */ $q->onCondition([Region::tableName() . '.id' => \Yii::$app->user->identity->currentRegion]); }], false)->innerJoinWith(['payments', 'client', 'category'])->orderBy([Message::tableName() . '.create_dt' => SORT_DESC])->distinct(); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 50, 'pageSizeParam' => 'inpage']]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } switch ($this->timeMark) { case self::TIME_ALL: $query->active(); break; case self::TIME_CURRENT: $query->active()->andWhere([Payment::tableName() . '.num' => Payment::currentNumber()]); break; case self::TIME_FUTURE: $query->active()->andWhere(['>', Payment::tableName() . '.num', Payment::currentNumber()]); break; case self::TIME_PREVIOUS: $query->active()->andWhere(['<', Payment::tableName() . '.num', Payment::currentNumber()]); break; case self::TIME_REMOVED: $query->deleted(); break; } if (!empty($this->subject)) { switch ($this->type) { case self::TYPE_PHONE: $query->andWhere(['LIKE', Phone::tableName() . '.number', $this->subject])->orWhere(['LIKE', Message::tableName() . '.phone', $this->subject]); break; case self::TYPE_CLIENT: $query->andWhere(['LIKE', Client::tableName() . '.name', $this->subject]); break; case self::TYPE_NUM: $query->andWhere([Payment::tableName() . '.num' => $this->subject]); break; } } return $dataProvider; }
public function actionEmptyads() { $msgs = Message::findBySql(' select tm.* from ' . Message::tableName() . ' tm where ( select count(id) as cnt from ' . Payment::tableName() . ' tp where tp.message_id = tm.id ) < 1 ')->with('category')->all(); return $this->render('emptyads', ['messages' => $msgs]); }
public function getTargetMessages($validate = true) { if ($validate && !$this->validate()) { return false; } $editBy = $this->submitter == self::SUBMITTER_ALL ? false : ArrayHelper::getColumn(Yii::$app->db->createCommand(' select user_id from auth_assignment aa where aa.item_name like "' . $this->submitter . '"')->queryAll(), 'user_id'); $q = Message::find()->innerJoinWith(['payments' => function ($query) { /* @var $query \yii\db\ActiveQuery */ $query->onCondition([Payment::tableName() . '.num' => $this->number]); }])->innerJoinWith(['city' => function ($query) { /* @var $query \yii\db\ActiveQuery */ $query->onCondition([City::tableName() . '.region_id' => $this->region]); }], false)->where(['<>', Message::tableName() . '.status', Message::STATUS_DELETE]); if ($editBy) { $q->andWhere(['edit_by' => $editBy]); } return $q->all(); }