public function actionRegion($id = null) { if ($id) { $model = Geo::getOne($id); //получчаем количество розничных точек в регионе $col_apteki = Apteki::find()->where(['region_id' => $id])->count(); //получаем количество Юр.лиц $col_ur_l = RegionUrL::find()->where(['id_reg' => $id])->count(); $db = new Query(); $db->from('region_ur_l'); $db->InnerJoin('ur_l', 'id_ur = ur_l.id'); $db->andWhere(['=', 'id_reg', $id]); $db->andWhere(['=', 'ur_l.regional_id', $model['regional_id']]); $col_ur_l_regpred = $db->count(); // $col_ur_l_regpred = RegionUrL::find()->where(['id_reg' => $id])->andWhere(['$model'=>])->count(); } else { $model = Geo::getAll(); } $region = new Geo(); if ($_POST['Geo']) { $model->attributes = $_POST['Geo']; if ($model->validate() && $model->save()) { return $this->render('/edit/region', ['model' => $model, 'region' => $region, 'ok' => 1, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } } if ($id) { return $this->render('/edit/region', ['model' => $model, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } else { return $this->render('region', ['model' => $model]); } }
protected function applyContextFilters() { if (isset($this->topicId)) { $this->query->joinWith('source.topics'); $this->query->andWhere(['scoopit_source_topic.topic_id' => $this->topicId]); } }
/** * Lists all Job models. * @return mixed */ public function actionIndex() { $model = new Job(); // This is used to search/filter organizations $dataProvider = null; if (isset($_GET['user_id'])) { $user_id = $_GET['user_id']; if ($user_id !== null) { $query = new Query(); $query->from(Job::tableName()); $query->where(['user_id' => $user_id]); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } } else { $dataProvider = new ActiveDataProvider(['query' => Job::find(), 'pagination' => ['pageSize' => 5]]); } if ($model->load(Yii::$app->request->post())) { $query = new Query(); $query->from(Job::tableName()); if (!is_null($model->employment_type) && is_array($model->employment_type)) { $query->where(['employment_type' => array_map('intval', $model->employment_type)]); } if (!is_null($model->job_type) && is_array($model->job_type)) { $query->where(['job_type' => array_map('intval', $model->job_type)]); } if (!is_null($model->work_domain) && is_array($model->work_domain)) { $query->andWhere(['work_domain' => array_map('intval', $model->work_domain)]); } if (isset($_GET['user_id'])) { $query->andWhere(['user_id' => $_GET['user_id']]); } $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]); }
/** * setup search function for filtering and sorting * based on fullName field */ public function search($params, Query $query) { $dataProvider = new ActiveDataProvider(['query' => $query]); /** * Setup your sorting attributes * Note: This is setup before the $this->load($params) * statement below */ $dataProvider->setSort(['attributes' => ['action' => ['label' => T::t('Action'), 'asc' => ['action' => SORT_ASC], 'desc' => ['action' => SORT_DESC], 'default' => SORT_ASC], 'created' => ['label' => T::t('Date'), 'asc' => ['created' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['created' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC], 'task_price' => ['label' => T::t('Reward'), 'asc' => ['task_price' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['task_price' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC]]]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } if (isset($params[$this->formName()])) { foreach ($params[$this->formName()] as $prop => $propValue) { if ("" != $propValue && property_exists($this, $prop)) { /* map objet_id to type */ if (in_array($prop, ['object_id', 'task_price'])) { $query->andWhere(sprintf('%s=%d', $prop, $this->{$prop})); } else { $query->andWhere(sprintf('%s LIKE "%%%s%%"', $prop, $this->{$prop})); } } } } return $dataProvider; }
public static function getSalesData($duration) { $dates = []; $transactions = []; $transactionskv = []; $amount = []; $statusPaid = [Order::STATUS_PAID, Order::STATUS_DELIVERED]; $statusPaid = join(",", $statusPaid); $orderTable = CartTables::TABLE_ORDER; $txnTable = CartTables::TABLE_ORDER_TRANSACTION; $query = new Query(); $query->select(["date(`{$txnTable}`.`createdAt`) as date", 'sum( amount ) as amount']); $query->from($txnTable); $query->join('LEFT JOIN', $orderTable, "orderId = `{$orderTable}`.`id`"); $query->where(" {$orderTable}.status in ( {$statusPaid} )"); switch ($duration) { case 0: // Current Week - Starting with Sun $dates = DateUtil::getCurrentWeekDates(); $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE ) ")->groupBy(['date'])->all(); break; case 1: // Last Week - Starting with Sun $dates = DateUtil::getLastWeekDates(); $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) ")->groupBy(['date'])->all(); break; case 2: // This Month $dates = DateUtil::getCurrentMonthDates(); $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all(); break; case 3: // Last Month $dates = DateUtil::getLastMonthDates(); $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) - 1 ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all(); break; } foreach ($transactions as $transaction) { $transactionskv[$transaction['date']] = $transaction['amount']; } foreach ($dates as $date) { if (isset($transactionskv[$date])) { $amount[] = $transactionskv[$date]; } else { $amount[] = 0; } } return $amount; }
public function actionIndex() { $request = Yii::$app->request; $menuId = 31; $theadArray = QueryField::find()->where(['menuId' => $menuId])->asArray()->with('queryTable')->all(); $tables = QueryTable::find()->where(['menuId' => $menuId])->asArray()->all(); $masterTable = $this->getMasterTable($tables); $query = new Query(); $query->from($masterTable['dbName'] . '.' . $masterTable['tabName']); $query->select($masterTable['tabName'] . '.' . 'id'); foreach ($tables as $table) { if ($table['isMain'] != '1') { $query->leftJoin($table['dbName'] . '.' . $table['tabName'], $table['condition']); } } //排序字段 $attributes = []; foreach ($theadArray as $thead) { if ($thead['queryTable']['reName']) { $addSelect = $thead['queryTable']['reName']; } else { $addSelect = $thead['queryTable']['tabName']; } $addSelect = $addSelect . '.' . $thead['fieldName']; if ($thead['makeTbName'] != 1) { $addSelect = $thead['fieldName']; } if ($thead['reName']) { //组装排序字段 array_push($attributes, $thead['reName']); //查询字段 $addSelect = $addSelect . ' ' . 'as' . ' ' . $thead['reName']; } else { array_push($attributes, $thead['fieldName']); } $query->addSelect($addSelect); //组装查询条件 if ($thead['isQuery'] == '1' && $thead['reName'] && $request->get($thead['reName'])) { $query->andWhere(['like', $thead['reName'], $request->get($thead['reName'])]); } elseif ($thead['isQuery'] == '1' && $request->get($thead['fieldName'])) { $query->andWhere(['like', $thead['fieldName'], $request->get($thead['fieldName'])]); } } $sort = new WetSort(['attributes' => $attributes]); $pages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'defaultPageSize' => 20]); $provider = new ActiveDataProvider(['query' => $query, 'pagination' => $pages, 'sort' => $sort]); $models = $provider->getModels(); return $this->render('index', ['models' => $models, 'pages' => $pages]); }
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]); }
/** * Lists all Test1 models. * @return mixed */ public function actionIndex() { $params = $_REQUEST; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('test1')->orderBy($sort); $query->andFilterWhere(['id' => $filter['id'], 'createdAt' => $filter['createdAt'], 'updatedAt' => $filter['updatedAt']]); $query->andFilterWhere(['like', 'name', $filter['name']]); if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); } $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
public function Edit($id_apteka, $id_preparat) { $query = new Query(); $query->from('preparats_ansver'); $query->where(['=', 'id_apteka', $id_apteka]); $query->andWhere(['=', 'id_o', $id_preparat]); return $query->one(); }
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; } }
/** * Lists all Organization models. * @return mixed */ public function actionIndex() { $model = new Organization(); // This is used to search/filter organizations $dataProvider = null; if (isset($_GET['user_id'])) { $user_id = $_GET['user_id']; if ($user_id !== null) { $query = new Query(); $query->from(Organization::tableName()); $query->where(['user_id' => $user_id]); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } } else { if (isset($_GET['search_keyword']) && $_GET['search_keyword'] !== null && strlen($_GET['search_keyword']) > 0 || isset($_GET['search_location']) && $_GET['search_location'] !== null && strlen($_GET['search_location']) > 0) { $search_keyword = $_GET['search_keyword']; if ($search_keyword !== null) { $query = new Query(); $query->from(Organization::tableName()); $query->where(['name' => $search_keyword]); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } } else { $dataProvider = new ActiveDataProvider(['query' => Organization::find(), 'pagination' => ['pageSize' => 5]]); } } if ($model->load(Yii::$app->request->post())) { $query = new Query(); $query->from(Organization::tableName()); if (!is_null($model->org_type) && is_array($model->org_type)) { $query->where(['org_type' => array_map('intval', $model->org_type)]); } if (!is_null($model->work_domain) && is_array($model->work_domain)) { $query->andWhere(['work_domain' => array_map('intval', $model->work_domain)]); } if (isset($_GET['user_id'])) { $query->andWhere(['user_id' => $_GET['user_id']]); } if (isset($_GET['search_keyword']) && $_GET['search_keyword'] !== null && strlen($_GET['search_keyword']) > 0 || isset($_GET['search_location']) && $_GET['search_location'] !== null && strlen($_GET['search_location']) > 0) { $query->andWhere(['name' => $_GET['search_keyword']]); } $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]); }
public function getPermissionsByRole($role) { $query = new Query(); $query->select(['p.id', 'p.category', 'p.name', 'p.description', 'p.form', 'p.default_value', 'p.rule', 'p.sort_num', 'r.role', 'r.value']); $query->from(['p' => $this->permissionTable, 'r' => $this->relationTable]); $query->where('r.permission=p.id'); $query->andWhere(['r.role' => $role]); $rows = $query->all(); return $this->convertPermissionValue($rows); }
public function getData() { $query = new Query(); $query->select(Property::tableName() . '.id, ' . Property::tableName() . '.name')->from(Property::tableName()); $query->leftJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id'); $query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->objectId]); $command = $query->createCommand(); $this->data = ArrayHelper::map($command->queryAll(), 'id', 'name'); return parent::getData(); }
protected function getProperties() { $result = []; $query = new Query(); $query->select(Property::tableName() . '.key, ' . Property::tableName() . '.name')->from(Property::tableName()); $query->innerJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id'); $query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->object->id]); $command = $query->createCommand(); return ArrayHelper::map($command->queryAll(), 'key', 'name'); }
public static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false) { $query = new Query(); $query->select('*')->from($tableName); if (!empty($conditions)) { foreach ($conditions as $name => $value) { if (is_array($value)) { $query->andWhere($value); } else { $query->andWhere([$name => $value]); } } } $total = 0; if ($isGetTotal) { $total = $query->count('*'); } $query->offset($offset)->limit($size)->orderBy($orderby); $list = $query->all(); return ['list' => $list, 'total' => $total]; }
public function testWhere() { $query = new Query(); $query->where('id = :id', [':id' => 1]); $this->assertEquals('id = :id', $query->where); $this->assertEquals([':id' => 1], $query->params); $query->andWhere('name = :name', [':name' => 'something']); $this->assertEquals(['and', 'id = :id', 'name = :name'], $query->where); $this->assertEquals([':id' => 1, ':name' => 'something'], $query->params); $query->orWhere('age = :age', [':age' => '30']); $this->assertEquals(['or', ['and', 'id = :id', 'name = :name'], 'age = :age'], $query->where); $this->assertEquals([':id' => 1, ':name' => 'something', ':age' => '30'], $query->params); }
/** * Joins conditions obtained from getCompositeRelatedConditions into one condition using a subquery. * @param ActiveRecord $model must have the AuthorizerBehavior attached * @param array $relations list of model relations to check, supports dot notation for indirect relations * @param IdentityInterface $user if null, Yii::$app->user->identity will be used * @param array $baseConditions base conditions passed down to getCompositeRelatedCriteria() * @param array $baseParams base params passed down to getCompositeRelatedCriteria() * @param mixed $primaryKey a scalar value used in the condition, if null, an SQL expression is used instead; * composite keys (array) are supported, but order must be retained * @return Query */ public function getRelatedUserQuery($model, $relations, $user = null, $baseConditions = [], $baseParams = [], $primaryKey = null) { if ($user === null) { $user = \Yii::$app->user->identity; } $query = new Query(); if ($user === null) { return $query->andWhere('1=0'); } if ($primaryKey === null) { $pkExpression = $this->quoteColumn('t', $model::primaryKey(), $model->getDb()->getSchema()); if (count($model::primaryKey()) > 1) { $pkExpression = "ROW({$pkExpression})"; } } elseif (!is_array($primaryKey)) { $pkExpression = ':relationAuthorizer_pk'; $query->params[':relationAuthorizer_pk'] = $primaryKey; } else { if (($key = key($primaryKey)) !== null && !is_numeric($key)) { // sort primaryKey by $model::primaryKey() $keys = array_flip($model::primaryKey()); uksort($primaryKey, function ($a, $b) use($keys) { return $keys[$a] - $keys[$b]; }); } $pkExpression = []; foreach (array_values($primaryKey) as $index => $pk) { $pkExpression[] = ':relationAuthorizer_pk' . $index; $query->params[':relationAuthorizer_pk' . $index] = $pk; } $pkExpression = 'ROW(' . implode(',', $pkExpression) . ')'; } /** @var ActiveQuery[] $relationQueries */ $relationQueries = $this->getCompositeRelatedUserQuery($model, $relations, $user, $baseConditions, $baseParams); $conditions = ['OR']; foreach ($relationQueries as $relationQuery) { if (empty($relationQuery->where) || !empty($baseConditions) && $relationQuery->where === $baseConditions) { continue; } $relationQuery->select($this->quoteColumn('t', $model::primaryKey(), $model->getDb()->getSchema())); $command = $relationQuery->createCommand($model->getDb()); $conditions[] = $pkExpression . ' IN (' . $command->getSql() . ')'; $query->params = array_merge($query->params, $relationQuery->params); } if ($conditions !== ['OR']) { $query->where = $conditions; } return $query; }
public function findAllBooks($selectedAuthors = '', $date_create_book = '', $date_create = '', $nameBook = '') { $query = new Query(); $query->select(['b.id', 'b.name', 'b.date_create', 'b.date_create_book', 'b.preview', "CONCAT(a.firstname,' ',a.lastname) as authors"])->from(['b' => 'books'])->innerJoin('authors AS a', 'b.author_id = a.id'); /*$query =Yii::$app->db->createCommand("SELECT b.id, b.name, b.date_create, b.date_create_book, b.preview, CONCAT(a.firstname,' ',a.lastname) as authors FROM books b INNER JOIN authors a ON b.author_id = a.id WHERE b.name = '".$nameBook."' AND b.date_create = '".$beforeDateBook."' AND b.date_create_book = '".$fromDateBook."' AND a.id = '".$selectedAuthors."' ");*/ if (!empty($selectedAuthors) || !empty($fromDateBook) || !empty($nameBook) || !empty($beforeDateBook)) { $query->where(['search' => 1]); } if (!empty($selectedAuthors)) { $query->andWhere(['a.id' => $selectedAuthors]); } if (!empty($nameBook)) { $query->andWhere(['like', 'b.name', $nameBook]); } if (!empty($date_create)) { $query->andWhere('b.date_create>=' . $date_create); } if (!empty($date_create_book)) { $query->andWhere(['b.date_create_book' => $date_create_book]); } $rows = $query->all(); return $rows; }
/** * @param null $query * @return null|Query */ public function build($query = null) { if (is_null($query)) { $query = new Query(); } elseif (!$query instanceof Query) { throw new InvalidParamException("Query must be a Query object."); } if (is_array($this->params)) { foreach ($this->params as $param => $operators) { foreach ($operators as $operator => $value) { $this->_checkOperator($operator); if (is_string($value) && strpos($value, ',')) { $value = explode(',', $value); } $this->_checkValue($value, $operator); $query->andWhere([$this->_operators[$operator], $param, $value]); } } } return $query; }
public static function generateTablePdf($id) { $mains = ValidationMain::find()->orderBy('npp')->all(); $html1 = ""; $html1 .= "\n<style>\nbody{\n\n \n font-size: 16px;\n font-family: 'open', sans-serif;\n\n\n \n\n}\n\nspan{\nfont-size: 17px;\nfont-family: 'roboto', sans-serif;\n}\n\ntd{\nbackground-color:#fff;\nfont-size: 14px;\nfont-family: 'open', sans-serif;\n\n}\n.main td{\nbackground-color:#64b5f6;\nfont-size: 15px;\nfont-family: 'roboto', sans-serif;\n}\n.main2 td{\nbackground-color:#87cefa;\n}\n.header{\nline-height:30px;\n}\nh1,h2,h3,h4{\nfont-family: 'roboto', sans-serif;\n color: #c62828;\n\n\n}\n\n</style>\n\n<body>\n<div id=\"background\">\n<div align='center'><img src=\"http://sojuzpharma.ru/images/logo_04.jpg\"></div><br>"; $table = ""; $priceAll = 0; // $table.= "<pagebreak />"; foreach ($mains as $main) { $params = ValidationParams::find()->where(['validation_main_id' => $main['id']])->all(); $table2 = ''; foreach ($params as $param) { $err = null; //$violations = Validationviolation::find()->where(['validation_params_id' => $param['id']])->all(); $query = new Query(); $query->select('validation_violation.name,validation_violation.price'); $query->InnerJoin('validation_report_violation', 'validation_report_violation.`violation_id`=validation_violation.id'); $query->where(['report_id' => $id]); $query->andWhere(['validation_params_id' => $param['id']]); $query->from('validation_violation'); $val = $query->all(); if (count($val) > 0) { $table2 .= ""; $table2 .= "<tr>"; $table2 .= "<td rowspan='" . count($val) . "' width=10.5%>" . $param['name'] . "</td>"; $table2 .= "<td width='15%'>" . $val[0]['name'] . "</td>"; if ($val[0]['price'] == "") { $table2 .= "<td rowspan='" . count($val) . "' align='center'><b>" . $param['price'] . "</b></td>"; $priceAll = $priceAll + $param['price']; } else { $table2 .= "<td align='center'><b>" . $val[0]['price'] . "<b></td>"; $priceAll = $priceAll + $val[0]['price']; } $table2 .= "<td rowspan='" . count($val) . "' valign=top>" . $param['nd'] . "</td>"; $table2 .= "<td rowspan='" . count($val) . "' valign=top width=45%>" . $param['sankcii'] . "</td>"; $table2 .= "</tr>\n"; $n = 0; foreach ($val as $violation) { if ($n != 0) { $table2 .= "<tr nobr=\"false\">"; $table2 .= "<td>" . $violation['name'] . "</td>"; if ($violation['price'] != "") { $table2 .= "<td align='center'><b>" . $violation['price'] . "</b></td>"; $priceAll = $priceAll + $violation['price']; } $table2 .= "</tr>\n"; } $n++; } $err = 1; } } if ($table2) { $table .= ""; $table .= "<h4 align='center'>" . $main['name'] . "</h4>"; $table .= "<table border='1' cellpadding='3' cellspacing='0' width='100%'>"; $table .= "<thead><tr align='center' class='main'>"; $table .= "<td align='center'>Проверяемый параметр</td>"; $table .= "<td align='center'>Выявленные нарушения</td>"; $table .= "<td align='center'>Риск применения санкций для юр.лица (max)</td>"; $table .= "<td align='center'>п. НД, регламентирующий указанное в колонке 2 требование</td>"; $table .= "<td align='center'>Риск применения санкций возможный контролирующий орган, ст. КоАП и др…</td>"; $table .= "</tr></thead>\n\n"; $table .= "<tr align='center' class='main2'>"; //$table .= "<td colspan='5'>" . $main['name'] . "</td>"; $table .= "</tr>"; $table .= $table2; $table .= "</table>"; } } $table .= "</div></body>"; $query = new Query(); $query->select('validation_report.`created_at`,ur_l.`name`,apteki.`address`,users.`username`'); $query->InnerJoin('apteki', 'apteki.id = validation_report.`apteka_id` '); $query->InnerJoin('ur_l', 'apteki.ur_l_id = ur_l.id'); $query->LeftJoin('users', 'users.id = validation_report.user_id'); $query->where(['validation_report.id' => $id]); $query->from('validation_report'); $val = $query->one(); $dat = new \DateTime($val['created_at']); $dat2 = $dat->format('d.m.Y'); $html2 = ""; $html2 .= "<h1 align='CENTER'>АКТ ОБСЛЕДОВАНИЯ АПТЕЧНОГО УЧРЕЖДЕНИЯ </h1>"; $html2 .= "<h4 align='CENTER'>В РАМКАХ ПРОЕКТА ФАРМОПЕКА (конфиденциально, только для внутреннего пользования) </h4>"; $html2 .= "<br><br><div class='header'>"; $html2 .= "<span>Дата проверки: </span>" . $dat2; $html2 .= "<br><br>"; $html2 .= "<span>Юридическое лицо: </span>" . $val['name']; $html2 .= "<br>"; $html2 .= "<span>Адрес объекта:</span> " . $val['address']; $html2 .= "<br>"; $html2 .= "<span>ФИО инспектирующего лица: </span>" . $val['username']; $html2 .= "<br>"; $html2 .= "<br><br><br><br>"; $html2 .= "<span>Выявлены несоответствия (графа 2) в соблюдение лицензионных условий и фармацевтического порядка на сумму <b>" . \number_format($priceAll, 0) . "</b> руб.</span>"; $html2 .= "</div>"; $html = $html1 . $html2 . $table; return $html; }
/** * Get All specs with choice. * @return Specs */ public function getSpecsAllWithChoice($base = null) { $query = new Query(); // compose the query $query->select(' t1.id AS id, t1.code AS code, t1.name AS name, t1.form_fulltime AS form_fulltime, t1.form_extramural AS form_extramural, t1.base_9 AS base_9, t1.base_11 AS base_11, t2.form_fulltime AS ch_ff, t2.form_extramural AS ch_fe ')->from('spec t1')->join('LEFT JOIN', 'profile_spec t2', 't2.id_spec=t1.id')->orderBy('CAST(t2.priority as unsigned) DESC, t2.priority')->limit(20); if ($base == 9) { $query->andWhere('t1.base_9=1'); } if ($base == 11) { $query->andWhere('t1.base_11=1'); } // build and execute the query return $query->createCommand()->queryAll(); }
/** * start de dialog om een contract aan te bieden **/ public function actionDialogContr() { $id = ArrayHelper::getValue($_POST, 'id'); $calenderevent = CalendarEvents::findOne(['id' => $id]); $start = substr($calenderevent->start, 0, 10); //Zie of er op die dag all contracten die zijn geaccepteerd. // $query = new Query(); $query->from('calendar_events'); $query->where(['start' => substr($calenderevent->start, 0, 10), 'user_id' => $calenderevent->user_id]); $query->andWhere(['like', 'code_title', 'Accep']); if ($query->count() > 0) { return $this->renderAjax('_dialogContrbezet', ['calenderevent' => $calenderevent]); } $usercv = UserCv::findOne(['user_id' => $calenderevent->user_id]); $user = User::findOne(['id' => $calenderevent->user_id]); $contractdeal = new ContractDeals(); $company = Company::findOne(['id' => $user->company_id]); $usercontactperson = Gebruikers::findOne(['id' => $calenderevent->user_id]); return $this->renderAjax('_dialogContr', ['calenderevent' => $calenderevent, 'company' => $company, 'usercv' => $usercv, 'contractdeal' => $contractdeal, 'user' => $user]); }
public static function getBody($class, $condition = [], $columns = []) { $bodyModel = new $class(); $table = $bodyModel::tableName(); if (empty($columns)) { $columns = $bodyModel->getTableSchema()->columns; } $selects = ['a.*']; foreach ((array) $columns as $column) { $columnName = ''; if (is_string($column)) { $columnName = $column; } else { $columnName = $column->name; } $selects[] = "body.{$columnName} as body_{$columnName}"; } $query = new Query(); $query->select($selects)->from(['a' => Content::tableName()])->innerJoin(['body' => $table], '{{a}}.[[id]]={{body}}.[[content_id]]'); if (!empty($condition)) { $query->andWhere($condition); } return $query; }
/** * @param ActiveRecord|NestedSetBehavior $target . * @param int $key . * @param int $levelUp . * @throws Exception * @throws \Exception * @return boolean. */ private function moveNode($target, $key, $levelUp) { $owner = $this->owner; if ($owner->getIsNewRecord()) { throw new Exception(yii::t('app', 'The node should not be new record.')); } if ($this->getIsDeletedRecord()) { throw new Exception(yii::t('app', 'The node should not be deleted.')); } if ($target->getIsDeletedRecord()) { throw new Exception(yii::t('app', 'The target node should not be deleted.')); } if ($owner->equals($target)) { throw new Exception(yii::t('app', 'The target node should not be self.')); } if ($target->isDescendantOf($owner)) { throw new Exception(yii::t('app', 'The target node should not be descendant.')); } if (!$levelUp && $target->isRoot()) { throw new Exception(yii::t('app', 'The target node should not be root.')); } $db = $owner->getDb(); $extTransFlag = $db->getTransaction(); if ($extTransFlag === null) { $transaction = $db->beginTransaction(); } try { $left = $owner->{$this->leftAttribute}; $right = $owner->{$this->rightAttribute}; $levelDelta = $target->{$this->levelAttribute} - $owner->{$this->levelAttribute} + $levelUp; if ($this->hasManyRoots && $owner->{$this->rootAttribute} !== $target->{$this->rootAttribute}) { foreach ([$this->leftAttribute, $this->rightAttribute] as $attribute) { $owner->updateAll([$attribute => new Expression($db->quoteColumnName($attribute) . sprintf('%+d', $right - $left + 1))], ['and', "[[{$attribute}]] >= :key", $this->rootAttribute . '= :root'], [':key' => $key, ':root' => $target->{$this->rootAttribute}]); } $delta = $key - $left; $owner->updateAll([$this->leftAttribute => new Expression($db->quoteColumnName($this->leftAttribute) . sprintf('%+d', $delta)), $this->rightAttribute => new Expression($db->quoteColumnName($this->rightAttribute) . sprintf('%+d', $delta)), $this->levelAttribute => new Expression($db->quoteColumnName($this->levelAttribute) . sprintf('%+d', $levelDelta)), $this->rootAttribute => $target->{$this->rootAttribute}], ["and", "[[{$this->leftAttribute}]] >= :left", "[[{$this->rightAttribute}]] <= :right", "[[{$this->rootAttribute}]] = :root"], [':left' => $left, ':right' => $right, ':root' => $owner->{$this->rootAttribute}]); $this->shiftLeftRight($right + 1, $left - $right - 1); if ($extTransFlag === null) { $transaction->commit(); } $this->correctCachedOnMoveBetweenTrees($key, $levelDelta, $target->{$this->rootAttribute}); } else { $delta = $right - $left + 1; $this->shiftLeftRight($key, $delta); if ($left >= $key) { $left += $delta; $right += $delta; } $query = new Query(); $query->andWhere("[[{$this->leftAttribute}]] >= :left", [':left' => $left])->andWhere("[[{$this->rightAttribute}]] <= :right", [':right' => $right]); if ($this->hasManyRoots) { $query->andWhere("{$this->rootAttribute} = :root", [':root' => $owner->{$this->rootAttribute}]); } $owner->updateAll([$this->levelAttribute => new Expression($db->quoteColumnName($this->levelAttribute) . sprintf('%+d', $levelDelta))], $query->where, $query->params); foreach ([$this->leftAttribute, $this->rightAttribute] as $attribute) { $query = new Query(); $query->andWhere("[[{$attribute}]] >= :left", [':left' => $left])->andWhere("[[{$attribute}]] <= :right", [':right' => $right]); if ($this->hasManyRoots) { $query->andWhere("{$this->rootAttribute} = :root", [':root' => $owner->{$this->rootAttribute}]); } $owner->updateAll([$attribute => new Expression($db->quoteColumnName($attribute) . sprintf('%+d', $key - $left))], $query->where, $query->params); } $this->shiftLeftRight($right + 1, -$delta); if ($extTransFlag === null) { $transaction->commit(); } $this->correctCachedOnMoveNode($key, $levelDelta); } } catch (Exception $e) { if ($extTransFlag === null) { $transaction->rollBack(); } throw $e; } return true; }
public function canExecute($user, $permission, $validate = null) { $query = new Query(); $query->select(['r.permission', 'r.value', 'p.form']); $query->from([$this->relationTable . ' as r', $this->permissionTable . ' as p', $this->assignmentTable . ' as a']); $query->where('r.permission=p.key and r.role=a.role'); $query->andWhere(['a.user' => $user, 'r.permission' => $permission]); $rows = $query->all(); if ($rows === null) { return false; } if ($validate === null) { $validate = function ($p, $v) { if (is_bool($v)) { return $v; } }; } $ret = true; foreach ($rows as $row) { $form = intval($row['form']); $v = $row['value']; if ($form === Permission::Form_Boolean) { if ($v === '1' || $v === 'true') { $v = true; } else { $v = false; } } else { if ($form === Permission::Form_CheckboxList) { $v = explode(',', $v); } } $ret = $ret && call_user_func($validate, $row['permission'], $v); //$ret[$row['permission']]=$v; } return $ret; }
/** * Prepares where condition * * @param Query $query * @param \yii\base\Model $modelForm * @return string */ public function prepareCondition(&$query, $modelForm) { foreach ($modelForm->attributes() as $attribute) { if ($attribute === 'table' || is_null($modelForm[$attribute])) { continue; } $filterConfig = Yii::$app->controller->module->filters[$attribute]; $query->andWhere("{$filterConfig['attribute']} {$filterConfig['criteria']['operator']} :{$attribute}", [$attribute => $modelForm[$attribute]]); } }
/** * 得到查询条件的字符串值 * @param array $diseaseQuery 查询条件 * @param Query $queryObj Query 查询对象 * @return string 查询条件的字符串值 */ private function getQueryCondition($diseaseQuery, $queryObj) { //判断是否有部位方面的查询条件 if (isset($diseaseQuery['part']) && !empty($diseaseQuery['part'])) { $part = $diseaseQuery['part']; //print_r($part);exit; $queryObj->innerJoin("9939_part_disease_rel ddr", "de.id = ddr.diseaseid"); $queryObj->andWhere($part); } elseif (isset($diseaseQuery['disease']) && !empty($diseaseQuery['disease'])) { $disease = $diseaseQuery['disease']; $queryObj->andWhere($disease); } elseif (isset($diseaseQuery['name'])) { $name = $diseaseQuery['name']; $queryObj->andWhere(["like", "de.name", $name]); } elseif (isset($diseaseQuery['typical_symptom'])) { $symptom = $diseaseQuery['typical_symptom']; $queryObj->andWhere(["like", "de.typical_symptom", $symptom]); } return $queryObj; }
/** * Пулечение всех юо.лиц в регионе плательщиков взносов * @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; }
/** * Add criteria condition. * * @param \yii\db\Query $query Query instance. * @param string $attribute Searched attribute name * @param boolean $partialMatch Matching type */ protected function addCondition($query, $attribute, $partialMatch = false) { $value = $this->{$attribute}; if (trim($value) === '') { return; } if ($partialMatch) { $query->andWhere(['like', $attribute, $value]); } else { $query->andWhere([$attribute => $value]); } }
private function getWhereSearch($filters) { $query = new Query(); $query->select(["SDP_TD03_DENPYO.*, TO_CHAR(D03_UPD_DATE, 'YYYY/mm/DD') as CHAR_D03_UPD_DATE,\n SDP_TD01_CUSTOMER.D01_CUST_NAMEN, SDP_TD01_CUSTOMER.D01_CUST_NAMEK, SDP_TD01_CUSTOMER.D01_NOTE"])->from(static::tableName())->leftJoin('SDP_TD01_CUSTOMER', 'SDP_TD03_DENPYO.D03_CUST_NO = SDP_TD01_CUSTOMER.D01_CUST_NO')->orderBy('D03_DEN_NO'); if (isset($filters['status']) && $filters['status'] != '') { if ($filters['status'] == 1) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]); $query->andWhere(['>', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]); } if ($filters['status'] == 2) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 1]); } if ($filters['status'] == 0) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]); $query->andWhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]); } } if (isset($filters['car']) && $filters['car']) { $query->andwhere(['like', 'SDP_TD03_DENPYO.D03_CAR_NO', $filters['car']]); } if (isset($filters['job']) && $filters['job'] != '') { $denpyo_sagyo = new Sdptd04denpyosagyo(); $data = $denpyo_sagyo->getData(['D04_SAGYO_NO' => $filters['job']]); $arr_den_no = [-1]; foreach ($data as $k => $v) { $arr_den_no[] = $v['D04_DEN_NO']; } $query->andwhere(['in', 'SDP_TD03_DENPYO.D03_DEN_NO', $arr_den_no]); } if (isset($filters['start_time']) && $filters['start_time']) { $query->andwhere(['>=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['start_time']]); } if (isset($filters['end_time']) && $filters['end_time']) { $query->andwhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['end_time']]); } if (isset($filters['detail_no'])) { $query->andwhere('SDP_TD03_DENPYO.D03_DEN_NO=:den_no', [':den_no' => $filters['detail_no']]); } if (isset($filters['m50_ss_cd'])) { $query->andwhere('SDP_TD03_DENPYO.D03_SS_CD =:m50_ss_cd', [':m50_ss_cd' => $filters['m50_ss_cd']]); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }