public function beforeInsert() { $last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one(); if ($last === null) { $this->owner->{$this->orderAttribute} = 1; } else { $this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1; } }
/** * Creates data provider instance with search query applied * * @param array $params * * @return ActiveDataProvider */ public function search($params) { $this->buildSearchQuery(); $this->applyContextFilters(); $dataProvider = new ActiveDataProvider(['query' => $this->query, 'pagination' => ['pageSize' => $this->pageSize]]); $this->load($params); if (!$this->validate()) { return $dataProvider; } $this->applySearchFilters(); $this->query->orderBy('date_retrieved DESC'); return $dataProvider; }
public function actionIndex($date = null) { // $apteki=LogReestr::find()->where(['resstr' => 'apteki'])->all(); if (!$date) { $date = date('Y-m'); } $db = new Query(); $db->from(LogReestr::tableName()); $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.action', 'log_reestr.name', 'log_reestr.ur_l_id', 'log_reestr.id_resstr', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']); $db->where(['=', 'resstr', 'apteki']); $db->leftJoin('users', "users.id = log_reestr.user"); $db->orderBy('log_reestr.created_at DESC'); $date_search = $date . '%'; $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]); $apteki = $db->all(); // $apteki_count = $db->count(); $db = new Query(); $db->from(LogReestr::tableName()); $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.name', 'log_reestr.action', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']); $db->where(['=', 'resstr', 'ur_l']); $db->leftJoin('users', "users.id = log_reestr.user"); $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]); $db->orderBy('log_reestr.created_at DESC'); $ur_l = $db->all(); // $ur_l_count = $db->count(); $statm = \Yii::$app->db->createCommand("SELECT users.username , COUNT(*) as count FROM log_reestr INNER JOIN users ON users.id=log_reestr.user\n where log_reestr.created_at like '" . $date . "%'\n GROUP BY USER order by count DESC"); $stat = $statm->queryAll(); $statAllm = \Yii::$app->db->createCommand("SELECT COUNT(*) as count FROM log_reestr\n where log_reestr.created_at like '" . $date . "%' "); $statAll = $statAllm->queryOne(); return $this->render('index', ['apteki' => $apteki, 'ur_l' => $ur_l, 'date' => $date, 'stat' => $stat, 'statAll' => $statAll]); }
public function actionIndex($user = null, $date = null, $trimestr = null, $scroll = null) { if (\Yii::$app->user->identity->status == 3) { $user = \Yii::$app->user->identity->id; } //$model = Preparats::find()->where(['region_id' => $region])->orderBy('name')->all(); if ($user) { //$apteki = Apteki::find()->where(['pi_id' => $user, 'farmopeka' => '1'])->orderBy('address')->all(); $query = new Query(); $query->select(['ur_l.name', 'apteki.address', 'apteki.id']); $query->from('apteki'); $query->LeftJoin('ur_l', 'ur_l.id = apteki.ur_l_id'); $query->andFilterWhere(['=', 'apteki.farmopeka', '1']); $query->andFilterWhere(['=', 'apteki.pi_id', $user]); //$query->orderBy('ur_l.name,apteki.address'); $query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]); $apteki = $query->all(); } if (!$date) { $date = date('Y-m-01'); } // if (!trimestr) $trimestr = Visitors::getTrimestr($date); if ($user) { $table = Visitors::getTable($user, $apteki, $date, $trimestr, $scroll); } // print \Yii::$app->params['infoEmail']; //Количество привязвнных ир.лиц и розничных точек $count['ur'] = Ur::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count(); $count['apteki'] = Apteki::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count(); return $this->render('index', ['user' => $user, 'table' => $table, 'date' => $date, 'trimestr' => $trimestr, 'count' => $count, 'scroll' => $scroll]); }
public function retrieve($className, $primaryKey) { $tableName = call_user_func([$className, "tableName"]); $current = $className::find()->where(['id' => $primaryKey])->asArray()->one(); $query = new Query(); $query->select(['field_name', 'old_value', 'event', 'action_uuid', 'created_at']); $query->from($this->tableName); $query->where(['field_id' => $primaryKey, 'table_name' => $tableName]); $query->orderBy(['created_at' => SORT_ASC]); $changes = []; foreach ($query->all() as $element) { $uuid = $element['action_uuid']; if (!isset($changes[$uuid])) { $changes[$uuid] = $current; } $changes[$uuid][$element['field_name']] = $element['old_value']; $current = $changes[$uuid]; } $models = array_map(function ($element) use($className) { $model = $className::instantiate($element); $className::populateRecord($model, $element); return $model; }, $changes); return new ArrayDataProvider(['allModels' => array_values($models)]); }
public static function getRegion() { $db = new yii\db\Query(); $db->select('id', 'name'); $db->from('region'); $db->orderBy('name'); return $db->all(); }
public function beforeInsert() { if (!is_null($this->where)) { if (!is_array($this->where)) { $this->where = array($this->where); } foreach ($this->where as $where) { $this->query->andWhere([$where => $this->owner->{$where}]); } } $last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one(); if ($last === null) { $this->owner->{$this->orderAttribute} = 1; } else { $this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1; } }
public static function getMostActive() { $query = new Query(); $query->select('profile.*,(SELECT COUNT(*) FROM discussion_post_reply WHERE profile.user_id = discussion_post_reply.user_id) as repliesCount'); $query->from('profile'); $query->orderBy('repliesCount DESC'); $query->limit('4'); return $query->all(); }
public static function getPopularVideos() { $query = new Query(); $query->select('videos.*,(SELECT COUNT(*) FROM videos_comments WHERE videos.id = videos_comments.video_id) as commentsCount'); $query->from('videos'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }
public static function getPopularPosts() { $query = new Query(); $query->select('article.*,(SELECT COUNT(*) FROM article_comments WHERE article.id = article_comments.article_id) as commentsCount'); $query->from('article'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }
/** * @param Query $query * @param array $params * @return Query */ protected function additionalFilter($query, $params) { if (!isset($params['sort'])) { $query->orderBy(['date' => SORT_DESC, 'created_at' => SORT_DESC, 'id' => SORT_DESC]); } else { switch ($params['sort']) { case 'total': $query->orderBy(['type_id' => SORT_DESC, 'total' => SORT_ASC]); break; case '-total': $query->orderBy(['type_id' => SORT_ASC, 'total' => SORT_DESC]); break; } } $period = Yii::$app->request->get('period'); if (isset($period)) { switch ($period) { case 'today': $today = Yii::$app->getFormatter()->asDate('now', "php:Y-m-d"); $query->andFilterWhere(['like', 'date', $today]); break; case 'yesterday': $yesterday = date('Y-m-d', strtotime(date('Y-m-d') . " - 1 day")); $query->andFilterWhere(['like', 'date', $yesterday]); break; case 'current_month': $query = $this->setDefaultPeriod($query); break; case 'all': break; default: $query = $this->setDefaultPeriod($query); break; } } else { $query = $this->setDefaultPeriod($query); } if (isset($params['category'])) { $query->leftJoin(Transaction2Category::tableName(), 'transaction_id = id'); $query->andWhere(['transaction2category.category_id' => $params['category']]); } return $query; }
public function getHistory($chatId, $limit = 10) { $query = new Query(); $query->select(['user_id', 'username', 'message', 'timestamp', 'avatar_16', 'avatar_32'])->from(self::tableName())->where(['chat_id' => $chatId]); $query->orderBy(['timestamp' => SORT_DESC]); if ($limit) { $query->limit($limit); } return $query->all(); }
public function search($params) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(IisAccessLog::tableName()), 'pagination' => ['pageSize' => 50]]); $query->orderBy('Id desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->date_reg) { $this->start_date = $this->date_reg; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } $query->andFilterWhere(['Ip1' => $this->Ip1]); $query->andFilterWhere(['>=', 'date_reg', $this->start_date]); $query->andFilterWhere(['<', 'date_reg', $this->end_date]); $query->orderBy('Id desc'); return $dataProvider; }
public function search($params) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(AccessLogIismost::tableName()), 'pagination' => ['pageSize' => 50]]); $query->orderBy('AccessIPNum desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->Date_time) { $this->start_date = $this->Date_time; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } $query->andFilterWhere(['Website' => $this->Website]); $query->andFilterWhere(['server' => $this->server]); $query->andFilterWhere(['>=', 'Date_time', $this->start_date]); $query->andFilterWhere(['<', 'Date_time', $this->end_date]); $query->orderBy('AccessIPNum desc'); return $dataProvider; }
public static function getSingleCategoryWithPosts($categoryId) { $query = new Query(); $query->select('discussion_posts.*,discussion_categories.*,discussion_posts.id as dpId'); $query->from('discussion_categories'); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 8]]); $query->andFilterWhere(['discussion_categories.id' => $categoryId]); $query->join('left join', 'discussion_posts', 'discussion_posts.discussion_category_id=discussion_categories.id'); $query->orderBy('discussion_posts.id DESC'); return $dataProvider; }
public function actionEmptyregional() { $db = new Query(); $db->from('ur_l'); $db->select(['ur_l.name', 'ur_l.id', 'GROUP_CONCAT(region.name) as rname']); $db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); $db->InnerJoin('region', 'region_ur_l.id_reg = region.id'); //$db->andWhere(['like', 'log_reestr.created_at', $date]); $db->GroupBy('ur_l.id'); $db->orderBy('rname', 'ur_l.name'); $data = $db->all(); return $this->render('empty', ['data' => $data]); }
public function search($params) { $query = new \yii\db\Query(); //判断参数中的时间,从而选择正确的数据库 $queryTable = AccessLogss::tableName(); if (isset($params['AccessLogssSearch']['date_reg'])) { $baseDay = $params['AccessLogssSearch']['date_reg']; //判断当前表是不是在这七天内 $queryDaystring = strtotime($baseDay); $querydaytimestring = date("Y-m-d", $queryDaystring); $querydayint = strtotime($querydaytimestring); //今天的标记daytime $todayint = strtotime(date('Y-m-d', time())); if ($querydayint < $todayint && $querydayint + 8 * 24 * 60 * 60 >= $todayint && $querydayint >= strtotime('2016-8-11')) { //判断是否在对应的天内 //得到要查询的表的数据 $queryTable = $queryTable . "_" . date("Ymd", $querydayint); } } $dataProvider = new ActiveDataProvider(['query' => $query->from($queryTable), 'db' => self::getDb(), 'pagination' => ['pageSize' => 50]]); $query->orderBy('Id desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->date_reg) { $this->start_date = $this->date_reg; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } $query->andFilterWhere(['Ip1' => $this->Ip1]); $query->andFilterWhere(['visitwebsite' => $this->visitwebsite]); $query->andFilterWhere(['>=', 'request_time', $this->request_time]); $query->andFilterWhere(['>=', 'date_reg', $this->start_date]); $query->andFilterWhere(['<', 'date_reg', $this->end_date]); $query->orderBy('date_reg desc'); return $dataProvider; }
/** * Lists all UrQuestions models. * @return mixed */ public function actionIndex() { //топ аптек $db = new Query(); $db->from('ur_questions'); $db->select(['COUNT(*) AS count', 'ur_l_id', 'ur_l.name']); $db->leftJoin('ur_l', "ur_l.id=ur_questions.`ur_l_id`"); $db->groupBy('ur_l_id'); $db->orderBy('count DESC'); $db->limit(5); $top = $db->all(); $query = (new \yii\db\Query())->from('ur_questions'); $query->select('username,ur_questions.id,ur_questions.created_at,question,ur_l.name,date_ansver'); $query->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id'); $query->leftJoin('users', 'ur_questions.user_id = users.id'); if (\Yii::$app->user->identity->status != 1) { // $query->where(['ur_questions.user_id'=>\Yii::$app->user->identity->id]); } $query->orderBy(['ur_questions.id' => SORT_DESC]); $dataProvider = new ActiveDataProvider(['key' => 'id', 'query' => $query->from('ur_questions')]); return $this->render('index', ['dataProvider' => $dataProvider, 'top' => $top]); }
public function ListAptekiAnsver() { $query = new Query(); $query->select(['GROUP_CONCAT(DISTINCT(region.name)) as rname', 'COUNT(preparats_ansver.id) AS count', 'ur_l.name AS uname', 'ur_l.id']); $query->from('ur_l'); $query->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); $query->InnerJoin('region', 'region_ur_l.id_reg = region.id'); $query->leftJoin('preparats_ansver', 'ur_l.id = preparats_ansver.id_apteka'); if (Yii::$app->user->identity->status == 2) { //Регионалы $query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]); } if (Yii::$app->user->identity->status == 3) { //Провизоры $query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]); } $query->andFilterWhere(['=', 'farmopeka', '1']); $query->groupBy('ur_l.id'); $query->orderBy('rname', 'ur_l.name'); // $query->InnerJoin('apteki', 'preparats_ansver.id_o=preparats_ansver.id'); return $query->all(); }
public function search_tip_id($params) { $query = new Query(); $query->from('t_tovar t'); $options = []; $joptions = isset($params['options']) ? $params['options'] : []; // var_dump($joptions);die; if (is_array($joptions)) { foreach ($joptions as $key => $value) { if (!empty($key)) { $options[$key] = $value; } } } else { $options = json_decode($joptions); } $n = 1; // var_dump($joptions);die; foreach ($options as $id => $value) { $name = 't' . $n; $fname = $id; // $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=' . $id) $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=:' . $name, [':' . $name => $id])->addSelect([$fname => $name . '.value_char']); if (!empty($value)) { $p = ':value_' . $n; $param = [$p => $value]; // var_dump($param,$name . '.value_char='.$p);die; $query->andWhere($name . '.value_char=' . $p, $param); // $n=$n+1; } $n = $n + 1; } $query->leftJoin('t_price p', 'p.id_tovar=t.id and p.id_store=:store_id', [':store_id' => $params['store_id']])->addSelect('p.price,p.id_store,p.count,t.id,t.name,t.tip_id,t.category_id'); // $query->limit($params['page_size'])->offset(($params['page'] - 1) * $params['page_size']); $query->andWhere($params['where']); $query->orderBy($params['orderby']); // var_dump($query);die; $dataProvider = new ActiveDataProvider(['query' => $query]); // $dataProvider = new ArrayDataProvider(['allModels' => $query->all()]); $dataProvider->pagination->page = $params['page'] - 1; $dataProvider->pagination->pageSize = $params['page_size']; // var_dump($dataProvider->models);die; //var_dump($dataProvider);die; return $dataProvider; }
public function testOrder() { $query = new Query(); $query->orderBy('team'); $this->assertEquals(['team' => SORT_ASC], $query->orderBy); $query->addOrderBy('company'); $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC], $query->orderBy); $query->addOrderBy('age'); $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_ASC], $query->orderBy); $query->addOrderBy(['age' => SORT_DESC]); $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_DESC], $query->orderBy); $query->addOrderBy('age ASC, company DESC'); $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_DESC, 'age' => SORT_ASC], $query->orderBy); }
public static function find($module, $params = [], $page = 30) { $relation = ['country' => ['translations'], 'city' => ['country', 'country.translations', 'airports', 'airports.translations', 'translations'], 'direction' => ['iata', 'from', 'from.translations', 'from.country', 'from.country.translations', 'to', 'to.translations', 'to.country', 'to.country.translations'], 'airport' => ['translations', 'country', 'country.translations', 'city', 'city.translations'], 'airline' => []]; if (!empty($params['relation'])) { $relation = array_merge($relation, $params['relation']); unset($params['relation']); } $model = 'common\\models\\Received' . ucfirst($module); if (!class_exists($model)) { throw new ErrorException('no module > ' . $module); } $query = new Query(); $query->select(["{$module}.id"])->from([$module => "received_{$module}"]); if ($module == 'direction') { $query->addSelect(["{$module}.popularity"]); $query->orderBy(['direction.popularity' => SORT_DESC]); $query->groupBy(['direction.city_to_code', 'direction.city_from_code']); } foreach ($params as $key => $value) { $postfix = ''; if ($key == 'from' || $key == 'to') { $postfix = "_{$key}"; $key = 'city'; } if (is_array($value) && $value) { $query->leftJoin([$key . $postfix => "received_{$key}"], "{$module}.{$key}{$postfix}_code = {$key}{$postfix}.code"); foreach ($value as $_key => $_value) { if (is_array($_value) && $_value) { $query->leftJoin([$_key . $postfix => "received_{$_key}"], "{$key}{$postfix}.{$_key}_code = {$_key}{$postfix}.code"); foreach ($_value as $__key => $__value) { $query->andFilterWhere(["{$_key}{$postfix}.{$__key}" => $__value]); } } else { $query->andFilterWhere(["{$key}{$postfix}.{$_key}" => $_value]); } } } else { $query->andFilterWhere(["{$module}.{$key}{$postfix}" => $value]); } } $total = $query->count(); $id = $query->column(); if ($page > 1) { if (!empty($_GET['page'])) { $_page = $_GET['page']; } else { $_page = 0; } $id = array_slice($id, $_page > 1 ? ($_page - 1) * $page : 0, $page); // VarDumper::dump($id); } /** @var $model \yii\db\ActiveRecord */ $query = $model::find()->where(['id' => $id])->asArray(); if ($module == 'direction') { $query->orderBy(['received_direction.popularity' => SORT_DESC]); } $query->with($relation[$module]); if ($page == 1) { return $query->all(); } $pagination = new Pagination(['totalCount' => $total, 'pageSize' => $page, 'pageSizeParam' => false]); if ($page == 1) { $page = 0; } $query->offset(0)->limit($page); return [$query->all(), $pagination]; }
public function setLangBegin() { if (!$this->langBegin) { $key = static::className() . 'langBegin'; $this->langBegin = $this->cache->get($key); if ($this->langBegin === false) { $query = new Query(); $query->select(['c' => $this->codeField])->from($this->table); if ($this->condition) { $query->where($this->condition); } if ($this->orderBy) { $query->orderBy($this->orderBy); } $this->langBegin = ArrayHelper::getColumn($query->all($this->getDb()), 'c'); } if ($this->enableCaching) { if ($this->durationCaching !== null) { $this->cache->set($key, $this->langBegin, $this->durationCaching); } else { $query = new Query(); $sql = $query->select('COUNT(*),MAX(' . $this->updatedAtField . ')')->from($this->table)->createCommand($this->getDb())->getRawSql(); $this->cache->set($key, $this->langBegin, $this->durationCaching, new \yii\caching\DbDependency(['sql' => $sql])); } } } }
/** * 获取等待运行的一个条件,锁定,运行 */ public function getPlanGetOrderDownload() { $query = new Query(); $query->select('*'); $query->from('t_run_ebay_order_download'); $query->where('1=1'); $query->andWhere(['status' => 0]); $query->orderBy(['id' => SORT_ASC]); $this->planDownload = $query->one(); if (empty($this->planDownload)) { throw new \Exception('没有符合条件的下载任务'); } }
/** * Пулечение всех юо.лиц в регионе плательщиков взносов * @param null $regional_id * @param $region_id * @return array */ public static function getUr_l_in_region($regional_id, $region_id) { $regional_id = (int) $regional_id; $region_id = (int) $region_id; $db = new Query(); $db->from(Ur::tableName()); $db->select(['ur_l.name', 'ur_l.id']); $db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id"); $db->Where(['=', 'region_ur_l.id_reg', $region_id]); $db->andWhere(['=', 'ur_l.plat', '1']); $db->andwhere(['=', 'ur_l.regional_id', $regional_id]); $db->orderBy('ur_l.name ASC'); $return = $db->all(); return $return; }
public function getPOLineRelatedQuery($params = [], $modelline = [], $groupBy = null) { $query = new Query(); if ($params['partner_id']) { $partner_id = $params['partner_id']; } else { $partner_id = '0'; } if ($modelline) { if ($modelline['product_id']) { $product = $modelline['product_id']; } else { $product = '0'; } } else { $product = $params['product_id']; } if ($params['state']) { $state = $params['state']; } else { $state = '0'; } if ($params['date_order']) { $dattefrom = $params['date_order']; $dateto = $params['duedate']; } else { $dattefrom = '0'; $dateto = '0'; } // if($params['pricelist']){ // if(is_array($params['pricelist'])){ // $pricelist=implode(",", $params['pricelist']); // }else{ // $pricelist=$params['pricelist']; // } // }else{ // $pricelist='0'; // } if ($groupBy) { if (is_array($params['pricelist'])) { $pricelist = implode(",", $params['pricelist']); } else { $pricelist = $params['pricelist']; } } if ($groupBy) { if ($groupBy == 'partner') { $query->select(['CONCAT("pol"."partner_id", \'/\',"pid"."id",\'/\',\'' . $product . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id, rp.name as partner, SUM(pol.price_unit*pol.product_qty) as total, pid.name as pricelist']); } } else { $query->select(' pol.id as id, pol.partner_id as partner_id, po.date_order as date_order, po.name as no_po, pol.name as pol_desc, rp.name as partner, pol.product_id as product_id, pp.name_template as product, pol.price_unit as price_unit, pol.state as state, pol.product_qty as product_qty, pu.name as uom, pid.name as pricelist, (pol.product_qty*pol.price_unit) as total, '); } $query->from('purchase_order_line as pol')->join('LEFT JOIN', 'purchase_order as po', 'po.id=pol.order_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=po.pricelist_id')->join('LEFT JOIN', 'product_product as pp', 'pp.id=pol.product_id')->join('LEFT JOIN', 'product_uom as pu', 'pu.id=pol.product_uom')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=pol.partner_id'); if ($groupBy) { if ($groupBy == 'partner') { $query->groupBy(['pol.partner_id', 'rp.name', 'pid.id']); $query->orderBy('rp.name ASC'); } } if (isset($params['partner_id']) && $params['partner_id']) { if ($params['partner_id'] != '0') { $query->andWhere(['pol.partner_id' => explode(',', $params['partner_id'])]); } } if (isset($modelline['name']) && $modelline['name']) { $query->andWhere(['ilike', 'pol.name', $modelline['name']]); // die(); } if (isset($modelline['product_id']) && $modelline['product_id']) { if ($modelline['product_id'] != '0') { $query->andWhere(['pol.product_id' => explode(',', $modelline['product_id'])]); } } if (isset($params['state']) && $params['state']) { if ($params['state'] == "purchased") { $cekstate = 'confirmed, approved, done'; if ($params['state'] != '0') { $query->andWhere(['pol.state' => explode(',', $cekstate)]); } } else { if ($params['state'] != '0') { $to_state = []; $exps = explode(',', urldecode($params['state'])); foreach ($exps as $exp) { if ($exp == 'purchased') { $to_state[] = 'confirmed'; $to_state[] = 'approved'; $to_state[] = 'done'; } else { $to_state[] = $exp; } } $query->andWhere(['pol.state' => $to_state]); } } } else { $query->andWhere(['in', 'pol.state', ['confirmed', 'approved', 'done']]); } if (isset($params['date_order']) && $params['date_order']) { if ($params['date_order'] != '0') { $query->andWhere(['>=', 'po.date_order', $params['date_order']]); $query->andWhere(['<=', 'po.date_order', $params['duedate']]); } } if (isset($params['pricelist'])) { $query->andWhere(['po.pricelist_id' => $params['pricelist']]); } if (!$groupBy) { $query->addOrderBy(['po.date_order' => SORT_DESC]); } return $query; }
public static function getWarning($orderBy, $limit, $conditions = []) { $query = new Query(); $query->select('w.*, s.name AS station_name, a.name AS area_name, c.name AS center_name')->from('warning w')->leftJoin('station s', 's.id = w.station_id')->innerJoin('area a', 's.area_id = a.id')->innerJoin('center c', 'c.id = s.center_id')->where([]); if (!empty($conditions)) { $oneCondition = 0; foreach ($conditions as $con) { $query->andWhere($con); } } $query->orderBy($orderBy); if ($limit > 0) { $query->limit($limit); } $warnings = $query->all(); if (!empty($warnings)) { foreach ($warnings as $k => $w) { $warnings[$k]['pics'] = self::findPicture($w['id']); } } return $warnings; }
FROM apteki INNER JOIN users ON users.`id` = apteki.pi_id LEFT JOIN visitors ON visitors.apteki_id = apteki.id WHERE apteki.id = 4581 AND visitors.date LIKE '2016%' */ //тут новый запрос $query = new Query(); $query->select(['apteki.address']); $query->from('apteki'); $query->LeftJoin('users', 'users.id = apteki.pi_id'); $query->andFilterWhere(['=', 'apteki.farmopeka', '1']); $query->andFilterWhere(['=', 'users.id', 1]); $query->orderBy('apteki.address'); ?> <br><br> <?php echo Html::SubmitButton("Сохранить", ['class' => 'btn btn-success']); ?> <div style="float:right"> <?php echo Html::a('Удалить', ['/visitors/delete/', 'id' => $model['id']], ['class' => 'btn btn-danger', 'data' => ['confirm' => 'Удалить?']]);
public static function buildContentQuery($tableName, $other = [], $where = null) { $query = new Query(); if (isset($other['fields'])) { $query->select($other['fields']); } if (empty($tableName)) { // todo $tableName = 'model_news'; } $query->from($tableName); if ($where !== null) { $query->andWhere($where); } if (isset($other['where'])) { $query->andWhere($other['where']); } if (isset($other['att1']) && is_integer($other['att1'])) { $query->andWhere(['att1' => $other['att1']]); } if (isset($other['att2']) && is_integer($other['att2'])) { $query->andWhere(['att2' => $other['att2']]); } if (isset($other['att3']) && is_integer($other['att3'])) { $query->andWhere(['att3' => $other['att3']]); } if (isset($other['flag'])) { $flagValue = CommonUtility::getFlagValue($other['flag']); if ($flagValue > 0) { $query->andWhere('flag&' . $flagValue . '>0'); } } if (isset($other['is_pic']) && $other['is_pic']) { $query->andWhere(['is_pic' => 1]); } if (isset($other['order'])) { $query->orderBy($other['order']); } else { $query->orderBy('publish_time desc'); } if (isset($other['offset'])) { $query->offset(intval($other['offset'])); } else { $query->offset(0); } if (isset($other['limit'])) { $query->limit(intval($other['limit'])); } else { $query->limit(10); } return $query; }
public static function getTable($users, $apteki, $date, $trimestr, $scroll = null) { $countMon = ""; //$nowdate = date('Y') . '-' . self::getFirstMonthTrimestr($trimestr) . '-01'; $now = new \DateTime($date); $clone = $now; // $table= $now->format( 'd-m-Y' ). "\n". $clone->format( 'd-m-Y' ); $table = ""; $table .= '<table class="table tableVisitors"> <thead> <tr> <th>Розничная точка</th>'; $clone->modify('-1 month'); for ($i = 0; $i < 3; $i++) { $mon = $clone->format('n'); $table .= "<th align='center'>" . \Yii::$app->params['ruMonth'][$mon] . "</th>"; $dateSearch = $clone->format('Y-m'); //$visitors[$i] = self::find()->andFilterWhere(['LIKE', 'date', $dateSearch])->andFilterWhere(['=', 'user_id', $users])->all(); $query = new Query(); $query->select(['visitors.apteki_id', 'visitors.date_type', 'visitors.date']); $query->from('apteki'); $query->InnerJoin('users', 'users.id = apteki.pi_id'); $query->LeftJoin('visitors', 'visitors.apteki_id = apteki.id'); $query->andFilterWhere(['=', 'apteki.farmopeka', '1']); $query->andFilterWhere(['=', 'users.id', $users]); $query->andFilterWhere(['LIKE', 'visitors.date', $dateSearch]); $query->orderBy('apteki.address'); $visitors[$i] = $query->all(); $mydate[$i] = $clone->format('Y-m'); $myMon[$i] = $clone->format('n'); $clone->modify('+1 month'); } $table .= '</thead><tbody>'; foreach ($apteki as $apt) { $style = ""; if ($scroll == $apt['id']) { $style = "bgcolor='#fdf579'"; } $table .= '<tr class="trr" id="' . $apt['id'] . '" ' . $style . '> <td><a name="apt' . $apt['id'] . '" href="/apteki/update?id=' . $apt['id'] . '"><b>' . $apt['name'] . '</b> ' . $apt['address'] . '</a></td>'; for ($n = 0; $n < count($visitors); $n++) { $m = null; foreach ($visitors[$n] as $vis) { if ($apt['id'] == $vis['apteki_id']) { $monTemp = $myMon[$n]; if (!isset($countMon[$monTemp][1])) { $countMon[$monTemp][1] = 0; } if (!isset($countMon[$monTemp][2])) { $countMon[$monTemp][2] = 0; } if ($vis['date_type'] == 1) { $alert = "warning"; $countMon[$monTemp]['1'] = $countMon[$monTemp]['1'] + 1; } if (date('dmY', strtotime($vis['date'])) == date('dmY')) { //Подсетка текущей даты $table .= '<script> $("#' . $apt['id'] . '").css( "background", "#ddd" ); </script>'; } if ($vis['date_type'] == 2) { $alert = "success"; $countMon[$monTemp]['2'] = $countMon[$monTemp]['2'] + 1; } $table .= '<td align="center" class="' . $alert . '"><a href="/visitors/update/?date=' . $mydate[$n] . '-' . date('d', strtotime($vis['date'])) . '&apt=' . $apt['id'] . '"><span class="badge">' . date('d', strtotime($vis['date'])) . '</span></a></div>'; $m = 1; } } if (!$m) { $table .= '<td align="center" class="addnewdate">'; $table .= '<a href="/visitors/update/?date=' . $mydate[$n] . '&apt=' . $apt[id] . '"><span class="glyphicon glyphicon-flag"></span></td></a></td>'; } } $table .= '</tr>'; } $table .= '</tbody> </table><br>'; $table2 = "<br><table>"; if ($countMon) { foreach ($countMon as $countMon2 => $val) { $table2 .= "<tr><td><span class=\"label label-info\">" . \Yii::$app->params['ruMonth'][$countMon2] . "</span></td>"; $table2 .= "<td><span class=\"label label-warning\">" . $val[1] . "</span></td>"; $table2 .= "<td><span class=\"label label-success\">" . $val[2] . "</span></td>"; $table2 .= "</tr>"; } } $table2 .= "</table>"; return $table2 . $table; }