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) { $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 function search($params) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTracePersqlSearch::tableName()), 'db' => self::getDb(), 'pagination' => ['pageSize' => 50]]); $query->orderBy('amount desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->sqlquerytime) { $this->start_date = $this->sqlquerytime; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->sqlquerytime))); } $query->andFilterWhere(['databasetype' => $this->databasetype]); $query->andFilterWhere(['sqlquerytime' => $this->start_date]); $query->orderBy('amount desc'); return $dataProvider; }
public function getCategories($parent_id = 0) { $query = new \yii\db\Query(); $query->select('*')->from('category'); $query->join('LEFT JOIN', 'category_description', 'category_description.category_id = category.category_id AND category_description.language_id = ' . (int) \Yii::$app->params['config_language_id']); $query->join('LEFT JOIN', 'category_to_store', 'category_to_store.category_id = category.category_id AND category_to_store.store_id = ' . (int) \Yii::$app->params['store_id']); $query->where(['=', 'category.parent_id', $parent_id]); $query->orderBy(['category.sort_order' => SORT_ASC]); return $query->all(); }
public function getAllBanner($banner_id, $limit) { $query = new \yii\db\Query(); $query->select('*')->from('banner_image'); $query->join('LEFT JOIN', 'banner_image_description', 'banner_image_description.banner_image_id = banner_image.banner_image_id'); $query->where(['=', 'banner_image.banner_id', $banner_id]); $query->orderBy(['banner_image.sort_order' => SORT_ASC]); $query->limit($limit); $query->offset(0); return $query->all(); }
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(); }
/** * Method to getMessages. * * @param $whom_id * @param $from_id * @param $type * * @throws EceptionMessages * @return array */ protected function getMessages($whom_id, $from_id = null, $type = null, $last_id = null) { $table_name = Messages::tableName(); $my_id = $this->getIdCurrentUser(); $query = new \yii\db\Query(); $query->select(['FROM_UNIXTIME(msg.created_at, "%d-%m-%Y %H:%i:%S") as created_at', 'msg.id', 'msg.status', 'msg.message', "usr1.id as from_id", "usr1.{$this->attributeNameUser} as from_name", "usr2.id as whom_id", "usr2.{$this->attributeNameUser} as whom_name"])->from("{$table_name} as msg")->leftJoin("{$this->userTableName} as usr1", 'usr1.id = msg.from_id')->leftJoin("{$this->userTableName} as usr2", 'usr2.id = msg.whom_id'); if ($from_id) { $query->where(['msg.whom_id' => $whom_id, 'msg.from_id' => $from_id])->orWhere(['msg.from_id' => $whom_id, 'msg.whom_id' => $from_id]); } else { $query->where(['msg.whom_id' => $whom_id]); } //if not set type //send all message where no delete if ($type) { $query->andWhere(['=', 'msg.status', $type]); } else { /* $query->andWhere('((msg.is_delete_from != 1 AND from_id = :my_id) OR (msg.is_delete_whom != 1 AND whom_id = :my_id) ) ', [ ':my_id' => $my_id, ]); */ } $query->andWhere('((msg.is_delete_from != 1 AND from_id = :my_id) OR (msg.is_delete_whom != 1 AND whom_id = :my_id) ) ', [':my_id' => $my_id]); if ($last_id) { $query->andWhere(['>', 'msg.id', $last_id]); } $return = $query->orderBy('msg.id')->all(); $ids = []; foreach ($return as $m) { if ($m['whom_id'] == $my_id) { $ids[] = $m['id']; } } //change status to is_read if (count($ids) > 0) { Messages::updateAll(['status' => Messages::STATUS_READ], ['in', 'id', $ids]); } $user_id = $this->getIdCurrentUser(); return array_map(function ($r) use($user_id) { $r['i_am_sender'] = $r['from_id'] == $user_id; return $r; }, $return); }
public function findAllUsersInfo($fields, $filter = null, $order_by = null) { $query = new \yii\db\Query(); $query->select($fields)->from('user')->innerJoin('personal_information', 'personal_information.pi_id = user.pi_id')->innerJoin('user_education', 'user_education.id = user.pi_id')->innerJoin('education_information', 'education_information.ei_id = user_education.ei_id')->innerJoin('course', 'course.course_id = education_information.course_id')->innerJoin('institution_course', 'institution_course.course_id = course.course_id')->innerJoin('institution', 'institution.inst_id = institution_course.inst_id'); if ($filter != null) { $fields = array(); foreach ($filter as $key => $value) { $fields[$key] = $value; } $query->where($fields); } if ($order_by != null) { $query->orderBy($order_by); } return $query->all(); }