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();
}
