public function getProduct($product_id)
{
$query = new \yii\db\Query();
$query->select(['*', 'product_description.name AS name', 'product.image', 'manufacturer.name AS manufacturer', '(SELECT price FROM product_discount pd2 WHERE pd2.product_id = product.product_id AND pd2.customer_group_id = "' . (int) \Yii::$app->params['config_customer_group_id'] . '" AND pd2.quantity = "1" AND ((pd2.date_start = "0000-00-00" OR pd2.date_start < NOW()) AND (pd2.date_end = "0000-00-00" OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount', '(SELECT price FROM product_special ps WHERE ps.product_id = product.product_id AND ps.customer_group_id = "' . (int) \Yii::$app->params['config_customer_group_id'] . '" AND ((ps.date_start = "0000-00-00" OR ps.date_start < NOW()) AND (ps.date_end = "0000-00-00" OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special', '(SELECT points FROM product_reward pr WHERE pr.product_id = product.product_id AND pr.customer_group_id ="' . (int) \Yii::$app->params['config_customer_group_id'] . '") AS reward', '(SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = product.stock_status_id AND ss.language_id ="' . (int) \Yii::$app->params['config_language_id'] . '") AS stock_status', '(SELECT wcd.unit FROM weight_class_description wcd WHERE product.weight_class_id = wcd.weight_class_id AND wcd.language_id = "' . (int) \Yii::$app->params['config_language_id'] . '") AS weight_class', '(SELECT lcd.unit FROM length_class_description lcd WHERE product.length_class_id = lcd.length_class_id AND lcd.language_id = "' . (int) \Yii::$app->params['config_language_id'] . '") AS length_class', '(SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = product.product_id AND r1.status = "1" GROUP BY r1.product_id) AS rating', '(SELECT COUNT(*) AS total FROM review r2 WHERE r2.product_id = product.product_id AND r2.status = "1" GROUP BY r2.product_id) AS reviews', 'product.sort_order'])->from('product')->join('LEFT JOIN', 'product_description', 'product.product_id = product_description.product_id')->join('LEFT JOIN', 'product_to_store', 'product.product_id = product_to_store.product_id')->join('LEFT JOIN', 'manufacturer', 'product.manufacturer_id = manufacturer.manufacturer_id');
$query->where('product.product_id = :product_id ' . 'AND product_description.language_id = :language_id ' . 'AND product.status = :status ' . 'AND product.date_available <= NOW() ' . 'AND product_to_store.store_id = :store_id ', [':product_id' => (int) $product_id, ':language_id' => (int) \Yii::$app->params['config_language_id'], ':status' => 1, ':store_id' => (int) \Yii::$app->params['store_id']]);
return $query->one();
}
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()
{
$query = new \yii\db\Query();
$maskExp = new \yii\db\Expression("'****************************************************************************************'");
$query->select(['id' => 'id', 'label' => 'label', 'tokenMask' => $maskExp]);
$query->from = ['authentication_token'];
$query->where(['user_id' => $this->user_id]);
return new ActiveDataProvider(['query' => $query]);
}
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 getModule($module_id)
{
$query = new \yii\db\Query();
$query->select('*')->from('module');
$query->where(['=', 'module.module_id', $module_id]);
$data = $query->one();
if ($data) {
return json_decode($data['setting'], true);
} else {
return array();
}
}
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 sum($type, $date_from, $date_to)
{
//$date_from = "01-01-$yaer 00:00:00";
//$date_to = "31-12-$yaer 23:59:59";
$query = new \yii\db\Query();
$query->select("sum(`leadsum`) AS lead,{{%transactions}}.type,{{%transactions}}.valuedate")->from('{{%transactions}}');
$query->join = [['LEFT JOIN', '{{%accounts}}', '{{%accounts}}.id=account_id']];
$query->groupBy = "{{%accounts}}.type";
$query->where("{{%accounts}}.type = :type");
$query->andWhere("valuedate>=:date_from");
$query->andWhere("valuedate<=:date_to");
$query->params([':date_from' => $date_from, ':date_to' => $date_to, ':type' => $type]);
$command = $query->createCommand();
return $command->queryScalar();
}
public function actionView($menuId)
{
$query = new \yii\db\Query();
$query->select(['id', 'tabName', 'reName'])->from('query_table');
$query->where(['menuId' => $menuId]);
$tabs = $query->all();
$items = [];
foreach ($tabs as $key => $value) {
$reName = '(noReName)';
if ($value['reName']) {
$reName = '(' . $value['reName'] . ')';
}
$items[$value['id']] = $value['tabName'] . $reName;
}
return $this->render('view', ['menuId' => $menuId, 'items' => $items]);
}
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();
}
public function search($params)
{
$query = new \yii\db\Query();
$dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTracePersqlSearch::tableName()), 'db' => self::getDb(), 'pagination' => ['pageSize' => 50]]);
$query->where('is_new=1');
$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)));
}
if ($this->databasetype && $this->databasetype != 'all') {
$query->andFilterWhere(['databasetype' => $this->databasetype]);
}
$query->andFilterWhere(['sqlquerytime' => $this->start_date]);
$query->orderBy('amount desc');
return $dataProvider;
}
/**
* 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();
}
