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