The new condition and the existing one will be joined using the 'AND' operator.
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;
}
