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