public function testFilterWhere()
{
// should work with hash format
$query = new Query();
$query->filterWhere(['id' => 0, 'title' => ' ', 'author_ids' => []]);
$this->assertEquals(['id' => 0], $query->where);
$query->andFilterWhere(['status' => null]);
$this->assertEquals(['id' => 0], $query->where);
$query->orFilterWhere(['name' => '']);
$this->assertEquals(['id' => 0], $query->where);
// should work with operator format
$query = new Query();
$condition = ['like', 'name', 'Alex'];
$query->filterWhere($condition);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['between', 'id', null, null]);
$this->assertEquals($condition, $query->where);
$query->orFilterWhere(['not between', 'id', null, null]);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['in', 'id', []]);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['not in', 'id', []]);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['not in', 'id', []]);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['like', 'id', '']);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['or like', 'id', '']);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['not like', 'id', ' ']);
$this->assertEquals($condition, $query->where);
$query->andFilterWhere(['or not like', 'id', null]);
$this->assertEquals($condition, $query->where);
}
protected function applyKeywordFilters()
{
//Do nothing when empty
if (empty($this->keywords)) {
return;
}
//Else process keywords through single character prefix
foreach ($this->keywords as $keyword) {
//Setup keywordFilters array
$this->processKeyword($keyword);
}
$filter = null;
if (count($this->_keywordFilters) > 1) {
$filter = ['OR'];
}
foreach ($this->_keywordFilters as $prefix => $keywords) {
$tableData = $this->_keywordTables[$prefix];
$condition = ['IN', $tableData[0] . '.' . $tableData[1], $keywords];
if (is_array($filter)) {
$filter[] = $condition;
}
if (!is_array($filter)) {
$filter = $condition;
}
}
$this->query->andFilterWhere($filter);
}
/**
* 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 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 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;
}
public function up()
{
$people_to_city = ['Маша' => [1, 2], 'Ваня' => [1, 2, 3, 4, 5], 'Оля' => [4, 5], 'Женя' => [2, 3], 'Вася' => [], 'Коля' => [6], 'Олег' => [3]];
foreach ($people_to_city as $name => $cities) {
$query = new Query();
// compose the query
$query->select('id')->from('people');
$query->andFilterWhere(['like', 'name', $name]);
$row = $query->one();
var_dump($row);
if (!empty($cities)) {
foreach ($cities as $cityId) {
$this->insert('people_city', ['people_id' => $row['id'], 'city_id' => $cityId]);
}
}
}
}
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;
}
users.username,
visitors.date
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">
public function actionUpdate($date = null, $apt)
{
// && $model->validate()
if (is_numeric($apt)) {
$model = Visitors::find()->andFilterWhere(['LIKE', 'date', $date])->andFilterWhere(['=', 'apteki_id', $apt])->one();
if (!$model) {
$model = new Visitors();
}
$model->apteki_id = $apt;
} else {
$model = new Visitors();
}
if (!$model->date) {
$model->date = $date;
}
if ($model->load(\Yii::$app->request->post())) {
$model->date = (int) \Yii::$app->request->post('date_y') . '-' . (int) \Yii::$app->request->post('date_m') . '-' . (int) \Yii::$app->request->post('date_d');
if (\Yii::$app->request->post('date_type')) {
$model->date_type = 2;
} else {
$model->date_type = 1;
}
if ($model->save()) {
$user_id = (int) \Yii::$app->request->post('user_id');
return $this->redirect(["/visitors/?user={$user_id}&scroll={$model->apteki_id}"]);
}
}
// $apteka = Apteki::find()->where(['id' => $apt, 'farmopeka' => '1'])->one();
$query = new Query();
$query->select(['apteki.*', 'ur_l.name']);
$query->from('apteki');
$query->InnerJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'apteki.id', $apt]);
$apteka = $query->one();
return $this->render('update', ['apteka' => $apteka, 'model' => $model]);
}
public function ur_l_excel()
{
$db = new Query();
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$db->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$db->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
}
$db->from('ur_l');
$db->select(['ur_l.name', 'ur_l.plat', 'sb_site_users.password_simple', 'users.username', 'sb_site_users.su_login', 'ur_l.id', 'ur_l.inn', 'ur_l.address', 'ur_l.wabc', 'GROUP_CONCAT(region.name) as rname', 'contact_face', 'contact_phone', 'contact_mail', 'contact_dr', 'farmopeka']);
$db->InnerJoin('users', 'users.id = ur_l.regional_id');
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$db->LeftJoin('sb_site_users', 'sb_site_users.ur_l_id = ur_l.id');
$db->GroupBy('ur_l.id');
$db->orderBy(['rname' => SORT_ASC, 'ur_l.inn' => SORT_DESC]);
$data = $db->all();
$count = $db->count();
for ($i = 0; $i < count($data); $i++) {
$count = (new Query())->from('apteki')->where(['ur_l_id' => $data[$i]['id']])->count();
if ($data[$i]['farmopeka']) {
$data[$i]['farmopeka'] = "Да";
} else {
$data[$i]['farmopeka'] = "Нет";
}
if ($data[$i]['plat']) {
$data[$i]['plat'] = "Да";
} else {
$data[$i]['plat'] = "Нет";
}
$data[$i]['count'] = $count;
}
return $data;
}
public function actionModalapteki($regional_id = null, $region_id)
{
$regional_id = (int) $regional_id;
$region_id = (int) $region_id;
$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');
if ($regional_id) {
$query->andFilterWhere(['=', 'apteki.regional_id', $regional_id]);
}
$query->andFilterWhere(['=', 'apteki.region_id', $region_id]);
//$query->orderBy('ur_l.name,apteki.address');
$query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]);
$return = $query->all();
$this->layout = 'modal_400';
return $this->render('modalapteki', compact(['return']));
// return $this->render('index');
}
protected function getMarkers($lat_max, $lat_min, $lng_min, $lng_max)
{
$query = new Query();
$query->from('{{%adver}}')->select(['[[id]]', '[[title]]', "latitude AS [[lat]]", "longitude AS [[lng]]"]);
$query->andWhere(['between', '[[latitude]]', $lat_min, $lat_max]);
$query->andWhere(['between', '[[longitude]]', $lng_min, $lng_max]);
$query->where(['[[status]]' => self::STATUS_ACTIVE, 'lang' => ['*', Yii::$app->language]]);
// adjust the query by adding the filters
$query->andFilterWhere(['[[category_id]]' => $this->category_id]);
$query->andFilterWhere(['[[country_id]]' => $this->country_id]);
$query->andFilterWhere(['[[province_id]]' => $this->province_id]);
$query->andFilterWhere(['[[city_id]]' => $this->city_id]);
$query->andFilterWhere(['like', '[[title]]', $this->title]);
$query->andFilterWhere(['like', '[[address]]', $this->address]);
$advers = $query->all();
$uniques = [];
$duplicate = [];
$uq_advers = [];
foreach ($advers as $adver) {
$adver['lat'] = Yii::$app->helper->formatLatLng($adver['lat']);
$adver['lng'] = Yii::$app->helper->formatLatLng($adver['lng']);
$latlng = $adver['lat'] . "+" . $adver['lng'];
if (in_array($latlng, $uniques)) {
$duplicate[$latlng][] = $adver['id'];
continue;
}
$uniques[] = $latlng;
$uq_advers[] = $adver;
}
$markers = array();
foreach ($uq_advers as $key => $adver) {
$latlng = $adver['lat'] . "+" . $adver['lng'];
$count = isset($duplicate[$latlng]) ? count($duplicate[$latlng]) : 0;
$ids = trim($adver['id'] . '-' . ($count > 0 ? implode('-', $duplicate[$latlng]) : ''), '-');
//$markers[$key]['id'] = $adver['id'];
$markers[$key]['lat'] = $adver['lat'];
$markers[$key]['lng'] = $adver['lng'];
$markers[$key]['duplicate'] = $count;
$markers[$key]['ids'] = $ids;
$markers[$key]['title'] = $count == 0 ? Html::encode($adver['title']) : '';
}
return $markers;
}
/**
* Dashboard
*
* @return mixed
*/
public function actionIndex()
{
// Unique users today
$usersQuery = new Query();
$usersQuery->select(['domain_userid'])->from('{{%event}}')->where('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->distinct();
// Unique users total
$totalUsersQuery = new Query();
$totalUsersQuery->select(['users'])->from('{{%stats_performance}}')->where('DATE(day) != CURRENT_DATE');
// Submissions today
$submissionsQuery = new Query();
$submissionsQuery->select(['id'])->from('{{%form_submission}}')->andWhere('DATE(FROM_UNIXTIME(created_at)) = CURRENT_DATE');
// Submissions total
$totalSubmissionsQuery = new Query();
$totalSubmissionsQuery->select(['id'])->from('{{%form_submission}}');
// If not admin
if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
// Add user filter to queries
$formIds = Yii::$app->user->getAssignedFormIds();
$formIds = count($formIds) > 0 ? $formIds : 0;
// Important restriction
$usersQuery->andFilterWhere(['app_id' => $formIds]);
$totalUsersQuery->andFilterWhere(['app_id' => $formIds]);
$submissionsQuery->andFilterWhere(['form_id' => $formIds]);
$totalSubmissionsQuery->andFilterWhere(['form_id' => $formIds]);
}
// Execute queries
$users = $usersQuery->count();
$totalUsers = $totalUsersQuery->sum('users');
$submissions = $submissionsQuery->count();
$totalSubmissions = $totalSubmissionsQuery->count();
// Add today data to total
$totalUsers = $totalUsers + $users;
// Users / submissions = Conversion rate
$submissionRate = 0;
if ($users > 0 && $submissions > 0) {
$submissionRate = round($submissions / $users * 100);
}
$totalSubmissionRate = 0;
if ($totalUsers > 0 && $totalSubmissions > 0) {
$totalSubmissionRate = round($totalSubmissions / $totalUsers * 100);
}
// Most viewed forms list by unique users
$formsByUsersQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(DISTINCT(e.domain_userid)) AS users'])->from('{{%event}} AS e')->innerJoin('{{%form}} AS f', 'e.app_id = f.id')->where(['event' => 'pv'])->andWhere('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->groupBy(['id'])->orderBy('users DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// Forms list by submissions
$formsBySubmissionsQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(fs.id) AS submissions'])->from('{{%form_submission}} AS fs')->innerJoin('{{%form}} as f', 'fs.form_id = f.id')->where('DATE(FROM_UNIXTIME(fs.created_at)) = CURRENT_DATE')->groupBy(['f.id'])->orderBy('submissions DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// Last updated forms list
$lastUpdatedFormsQuery = (new Query())->select(['id', 'name', 'updated_at'])->from('{{%form}} AS f')->where('DATE(FROM_UNIXTIME(updated_at)) = CURRENT_DATE')->orderBy('updated_at DESC')->limit(Yii::$app->params['ListGroup.listSize']);
// If not admin
if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
// Add user filter to queries
$formIds = Yii::$app->user->getAssignedFormIds();
$formIds = count($formIds) > 0 ? $formIds : 0;
// Important restriction
$formsByUsersQuery->andFilterWhere(['f.id' => $formIds]);
$formsBySubmissionsQuery->andFilterWhere(['fs.form_id' => $formIds]);
$lastUpdatedFormsQuery->andFilterWhere(['f.id' => $formIds]);
}
$formsByUsers = $formsByUsersQuery->all();
$formsBySubmissions = $formsBySubmissionsQuery->all();
$lastUpdatedForms = $lastUpdatedFormsQuery->all();
return $this->render('index', ['users' => $users, 'submissions' => $submissions, 'submissionRate' => $submissionRate, 'totalUsers' => $totalUsers, 'totalSubmissions' => $totalSubmissions, 'totalSubmissionRate' => $totalSubmissionRate, 'formsByUsers' => $formsByUsers, 'formsBySubmissions' => $formsBySubmissions, 'lastUpdatedForms' => $lastUpdatedForms]);
}
/**
*Отчеты по препаратам в XML
*
*/
public function ListAptekiAnsverXml($region_id, $date = null)
{
$query = new Query();
$query->select(['preparats_ansver.nal', 'preparats_ansver.zakupka', 'preparats_ansver.prodaja', 'preparats.name', 'preparats.manuf', 'ur_l.name as uname', 'users.username', 'region.name as rname']);
$query->from('preparats_ansver');
$query->innerJoin('preparats', 'preparats.id = preparats_ansver.id_o');
$query->innerJoin('ur_l', 'ur_l.id = preparats_ansver.id_apteka');
$query->LeftJoin('users', 'users.id = ur_l.pi_id');
// $query->LeftJoin('users as pi', 'users.id = ur_l.pi_id');
$query->LeftJoin('region', 'region.id = preparats.region_id');
// $query->innerJoin('region', 'region.id = ur_l.region_id');
$query->where(['=', 'preparats.region_id', $region_id]);
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]);
}
if ($date) {
$query->andFilterWhere(['like', 'preparats_ansver.date', $date]);
}
// $query->andFilterWhere(['like', 'preparats_ansver.date', '2016-%']);
return $query->all();
/**
* $date_like=date("Y-m");
* $date_like2=" and pl_pi_o_ansver.date like '".$date_like."%' ";
*
* SELECT
* pl_region.name AS rname,
* pl_apteka.name AS aname,
* pl_apteka.address,
* pl_apteka.site_name,
* pl_pi_o.name,
* pl_pi_o.manuf,
* pl_pi_o_ansver.nal,
* pl_pi_o_ansver.zakupka,
* pl_pi_o_ansver.prodaja,
* pl_pi_o_ansver.date,
* pl_pi.`fio`
* FROM
* pl_pi_o_ansver
* INNER JOIN pl_pi_o ON
* pl_pi_o_ansver.id_o = pl_pi_o.id
* INNER JOIN pl_apteka ON
* pl_pi_o_ansver.id_apteka = pl_apteka.wabc
* INNER JOIN pl_region ON
* pl_region.id = pl_apteka.region
* LEFT JOIN pl_pi_apteki ON
* pl_pi_apteki.apt_id = pl_apteka.id
* LEFT JOIN pl_pi ON
* pl_pi_apteki.pi_id = pl_pi.id
* WHERE
* pl_pi_o.is_active = 1
* $and_pr
* $date_like2
* $order
*
*
*/
}
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];
}
/**
* @param Query $query
*/
protected function prepare($query)
{
$query->andFilterWhere(['createTime' => $this->createTime, 'updateTime' => $this->updateTime]);
$query->andFilterWhere(['like', 'type', $this->type])->andFilterWhere(['like', 'name', $this->name])->andFilterWhere(['like', 'title', $this->title]);
}
/**
* @param Query $query
* @return Query
*/
protected function setDefaultPeriod($query)
{
$currentMonth = Yii::$app->getFormatter()->asDate('now', "php:Y-m");
$query->andFilterWhere(['like', 'date', $currentMonth]);
return $query;
}
