public function search($params) { $query = new \yii\db\Query(); if (!$this->start_date) { $this->start_date = date("Y-m-d 00:00:00"); $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } $dataProvider = new ActiveDataProvider(['db' => self::getDb(), 'query' => $query->from(SqlTraceTop50::tableName())]); $query->select(['sqltext', 'sum(amount) as amount', 'queryusemaxtime', 'databasetype', 'sqlquerytime', 'ip']); $query->groupBy('querymd5'); $query->orderBy('queryusemaxtime desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } $query->andFilterWhere(['>=', 'queryusemaxtime', $this->queryusemaxtime]); if ($this->databasetype && $this->databasetype != 'all') { $query->andFilterWhere(['databasetype' => $this->databasetype]); } $query->andFilterWhere(['>=', 'queryusemaxtime', $this->queryusemaxtime]); $query->andFilterWhere(['>=', 'sqlquerytime', $this->start_date]); $query->andFilterWhere(['<', 'sqlquerytime', $this->end_date]); $query->groupBy('querymd5'); $query->orderBy('queryusemaxtime desc'); return $dataProvider; }
public function search($params, $personal = false) { /* User identifier */ $userIdentifier = \Yii::$app->user->getId() ? \Yii::$app->user->getId() : 0; /* Base query */ $query = new \yii\db\Query(); /* Selector */ $query->select(['"projects".*', 'COUNT("boxes"."id") as boxCount']); /* Table */ $query->from('projects'); /* Joins */ $query->join('LEFT JOIN', 'boxes', '"projects"."id" = "boxes"."project_id" AND "boxes"."deleted" = FALSE'); $query->join('LEFT JOIN', 'project_counters', '"projects"."id" = "project_counters"."project_id" AND "project_counters"."user_id" = :user_id', ['user_id' => $userIdentifier]); /* Conditions */ $query->where(['"projects"."deleted"' => false]); if (!$personal) { if (!is_object(Yii::$app->user->getIdentity()) || is_object(Yii::$app->user->getIdentity()) && !Yii::$app->user->getIdentity()->hasRole(['validator', 'administrator'])) { $query->andWhere(['or', ['"projects"."owner_id"' => $userIdentifier], ['"projects"."main_observer_id"' => $userIdentifier], ['or', ['is', '"projects"."embargo"', NULL], ['<=', '"projects"."embargo"', 'NOW()']], ['is not', '"project_counters"."user_id"', NULL]]); } } else { $query->andWhere(['or', ['"projects"."owner_id"' => $userIdentifier], ['"projects"."main_observer_id"' => $userIdentifier], ['is not', '"project_counters"."user_id"', NULL]]); } /* Group */ $query->groupBy('"projects"."id"'); $dataProvider = new ActiveDataProvider(['query' => $query]); $this->load($params); return $dataProvider; }
public function search($params) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTraceSqlNumber::tableName())]); $query->orderBy('Amount desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->update_time) { $this->start_date = $this->update_time; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } if (!empty($this->databasetype)) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTraceSqlNumber::tableName())]); } $query->andFilterWhere(['databasetype' => $this->databasetype]); $query->select("sqltext,databasetype,update_time,sum(Amount) as sAmount"); $query->andFilterWhere(['>=', 'update_time', $this->start_date]); $query->andFilterWhere(['<', 'update_time', $this->end_date]); $query->groupBy('sqltext_md5'); $query->orderBy('sAmount desc'); return $dataProvider; }
public static function getInfo($id) { $db = new yii\db\Query(); $db->from('ur_questions'); $db->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id'); $db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); $db->InnerJoin('region', 'region_ur_l.id_reg = region.id'); $db->groupBy('ur_l.id'); $db->select(['ur_l.name as uname', 'GROUP_CONCAT(DISTINCT(region.name)) as rname', 'ur_l.contact_mail', 'ur_l.contact_phone', 'ur_l.contact_face', 'question', 'qfiles', 'ansver', 'ur_questions.created_at', 'ur_l.id', 'ur_questions.id as qid']); $db->where(['=', 'ur_questions.id', $id]); //$db->leftJoin('region', 'ur_l.region_id = region.id'); return $db->One(); }
public function getAllBestSellerProducts($limit) { $time = new \DateTime('now'); $today = $time->format('Y-m-d'); $query = new \yii\db\Query(); $query->select('order_product.product_id , SUM(order_product.quantity) AS total')->from('order_product'); $query->join('LEFT JOIN', 'order', 'order_product.order_id = order.order_id'); $query->join('LEFT JOIN', 'product', 'order_product.product_id = product.product_id'); $query->join('LEFT JOIN', 'product_to_store', 'product.product_id = product_to_store.product_id'); $query->where(['>', 'order.order_status_id', 0]); $query->andWhere(['=', 'product.status', 1]); $query->andWhere(['<=', 'product.date_available', $today]); $query->andWhere(['=', 'product_to_store.store_id', (int) \Yii::$app->params['store_id']]); $query->groupBy('order_product.product_id'); $query->orderBy(['total' => SORT_DESC]); $query->limit($limit); $query->offset(0); return $query->all(); }