Appends a LEFT OUTER JOIN part to the query.
| public leftJoin ( string | array $table, string | array $on = '', array $params = [] ) | ||
| $table | string | array | the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query. |
| $on | string | array | the join condition that should appear in the ON part. Please refer to [[join()]] on how to specify this parameter. |
| $params | array | the parameters (name => value) to be bound to the query |
public function actionIndex($date = null)
{
// $apteki=LogReestr::find()->where(['resstr' => 'apteki'])->all();
if (!$date) {
$date = date('Y-m');
}
$db = new Query();
$db->from(LogReestr::tableName());
$db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.action', 'log_reestr.name', 'log_reestr.ur_l_id', 'log_reestr.id_resstr', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
$db->where(['=', 'resstr', 'apteki']);
$db->leftJoin('users', "users.id = log_reestr.user");
$db->orderBy('log_reestr.created_at DESC');
$date_search = $date . '%';
$db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
$apteki = $db->all();
// $apteki_count = $db->count();
$db = new Query();
$db->from(LogReestr::tableName());
$db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.name', 'log_reestr.action', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
$db->where(['=', 'resstr', 'ur_l']);
$db->leftJoin('users', "users.id = log_reestr.user");
$db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
$db->orderBy('log_reestr.created_at DESC');
$ur_l = $db->all();
// $ur_l_count = $db->count();
$statm = \Yii::$app->db->createCommand("SELECT users.username , COUNT(*) as count FROM log_reestr INNER JOIN users ON users.id=log_reestr.user\n where log_reestr.created_at like '" . $date . "%'\n GROUP BY USER order by count DESC");
$stat = $statm->queryAll();
$statAllm = \Yii::$app->db->createCommand("SELECT COUNT(*) as count FROM log_reestr\n where log_reestr.created_at like '" . $date . "%' ");
$statAll = $statAllm->queryOne();
return $this->render('index', ['apteki' => $apteki, 'ur_l' => $ur_l, 'date' => $date, 'stat' => $stat, 'statAll' => $statAll]);
}
public static function getFullname()
{
$db = new Query();
$db->from('apteki');
$db->select(['ur_l.name', 'ur_l.id as id2', 'apteki.address', 'apteki.id']);
$db->leftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
$data = $db->all();
return $data;
}
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);
if (!$masterTable) {
$NullPages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'totalCount' => 0, 'defaultPageSize' => 20]);
return $this->render('index', ['models' => [], 'pages' => $NullPages, 'theadArray' => []]);
}
$query = new Query();
$query->from($masterTable['tabName']);
$query->select($masterTable['tabName'] . '.' . 'id');
foreach ($tables as $table) {
if ($table['isMain'] != '1') {
$query->leftJoin($table['tabName'], $table['condition']);
}
}
//排序字段
$attributes = [];
//查询条件
$where = [];
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']) {
$where[$thead['reName']] = $request->get($thead['reName']);
} elseif ($thead['isQuery'] == '1') {
$where[$thead['fieldName']] = $request->get($thead['fieldName']);
}
}
$query->where($where);
$pages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'defaultPageSize' => 20]);
$sort = new WetSort(['attributes' => $attributes]);
$provider = new ActiveDataProvider(['query' => $query, 'pagination' => $pages, 'sort' => $sort]);
$models = $provider->getModels();
return $this->render('index', ['models' => $models, 'pages' => $pages, 'theadArray' => $theadArray]);
}
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();
}
/**
* Lists all UrQuestions models.
* @return mixed
*/
public function actionIndex()
{
//топ аптек
$db = new Query();
$db->from('ur_questions');
$db->select(['COUNT(*) AS count', 'ur_l_id', 'ur_l.name']);
$db->leftJoin('ur_l', "ur_l.id=ur_questions.`ur_l_id`");
$db->groupBy('ur_l_id');
$db->orderBy('count DESC');
$db->limit(5);
$top = $db->all();
$query = (new \yii\db\Query())->from('ur_questions');
$query->select('username,ur_questions.id,ur_questions.created_at,question,ur_l.name,date_ansver');
$query->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id');
$query->leftJoin('users', 'ur_questions.user_id = users.id');
if (\Yii::$app->user->identity->status != 1) {
// $query->where(['ur_questions.user_id'=>\Yii::$app->user->identity->id]);
}
$query->orderBy(['ur_questions.id' => SORT_DESC]);
$dataProvider = new ActiveDataProvider(['key' => 'id', 'query' => $query->from('ur_questions')]);
return $this->render('index', ['dataProvider' => $dataProvider, 'top' => $top]);
}
public function ListAptekiAnsver()
{
$query = new Query();
$query->select(['GROUP_CONCAT(DISTINCT(region.name)) as rname', 'COUNT(preparats_ansver.id) AS count', 'ur_l.name AS uname', 'ur_l.id']);
$query->from('ur_l');
$query->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$query->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$query->leftJoin('preparats_ansver', 'ur_l.id = preparats_ansver.id_apteka');
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
}
$query->andFilterWhere(['=', 'farmopeka', '1']);
$query->groupBy('ur_l.id');
$query->orderBy('rname', 'ur_l.name');
// $query->InnerJoin('apteki', 'preparats_ansver.id_o=preparats_ansver.id');
return $query->all();
}
public function actionUsers()
{
$_currUser = Yii::$app->user->isGuest ? null : User::findOne(['id' => Yii::$app->user->id]);
if (!$_currUser || 0 === $_currUser->is_admin) {
return $this->goHome();
}
if ($_currUser && $_currUser->is_admin && Yii::$app->request->get('user_id') && 'markAdmin' === Yii::$app->request->get('action')) {
$tarUser = User::findOne(['id' => intval(Yii::$app->request->get('user_id'))]);
if ($tarUser) {
$tarUser->is_admin = 1;
$tarUser->save();
}
}
$users = [];
$currPage = Yii::$app->request->get('page', 0);
$filteredAddress = Yii::$app->request->get('address', null);
$queryUserCount = new Query();
$queryUserCount->select('*')->from('users u');
$queryUser = new Query();
$queryUser->select('u.id as id, u.first_name as fname, u.last_name as lname, u.is_admin as is_admin, ' . 'u.photo as photo, u.is_complete as is_complete, avg(r.rate) as rate')->from('users u')->leftJoin('rates r', 'r.rated_id = u.id')->groupBy('id, fname, lname, is_admin, photo, is_complete')->orderBy('rate desc')->offset(intval(Yii::$app->params['driversPerPageLimit'] * $currPage))->limit(Yii::$app->params['driversPerPageLimit']);
if ($filteredAddress) {
$queryUser->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
$queryUser->leftJoin('addresses adr', 'adr.id = rua.address_id');
$queryUser->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
$queryUserCount->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
$queryUserCount->leftJoin('addresses adr', 'adr.id = rua.address_id');
$queryUserCount->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
} else {
$queryUser->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
$queryUserCount->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
}
$totalCount = $queryUserCount->count();
$context = ['is_guest' => Yii::$app->user->isGuest, 'curr_user_id' => Yii::$app->user->isGuest ? null : Yii::$app->user->id, 'is_admin' => $_currUser ? 1 === $_currUser->is_admin : false, 'is_drivers' => 0, 'total_pages' => ceil($totalCount / Yii::$app->params['driversPerPageLimit']), 'total_count' => $totalCount, 'current_page' => $currPage, 'filtered_address' => $filteredAddress, 'address_start' => Yii::$app->request->get('address_start', null), 'address_dest' => Yii::$app->request->get('address_dest', null), 'time_start' => Yii::$app->request->get('time_start', null), 'message' => Yii::$app->request->get('message', null)];
$rows = $queryUser->all();
foreach ($rows as $row) {
$users[] = ['id' => $row['id'], 'first_name' => $row['fname'], 'last_name' => $row['lname'], 'is_admin' => $row['is_admin'], 'rating' => sprintf("%.2f", $row['rate']), 'photo' => $row['photo'], 'is_complete' => $row['is_complete']];
}
return $this->render('drivers', ['drivers' => $users, 'context' => $context]);
}
public function search_tip_id($params)
{
$query = new Query();
$query->from('t_tovar t');
$options = [];
$joptions = isset($params['options']) ? $params['options'] : [];
// var_dump($joptions);die;
if (is_array($joptions)) {
foreach ($joptions as $key => $value) {
if (!empty($key)) {
$options[$key] = $value;
}
}
} else {
$options = json_decode($joptions);
}
$n = 1;
// var_dump($joptions);die;
foreach ($options as $id => $value) {
$name = 't' . $n;
$fname = $id;
// $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=' . $id)
$query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=:' . $name, [':' . $name => $id])->addSelect([$fname => $name . '.value_char']);
if (!empty($value)) {
$p = ':value_' . $n;
$param = [$p => $value];
// var_dump($param,$name . '.value_char='.$p);die;
$query->andWhere($name . '.value_char=' . $p, $param);
// $n=$n+1;
}
$n = $n + 1;
}
$query->leftJoin('t_price p', 'p.id_tovar=t.id and p.id_store=:store_id', [':store_id' => $params['store_id']])->addSelect('p.price,p.id_store,p.count,t.id,t.name,t.tip_id,t.category_id');
// $query->limit($params['page_size'])->offset(($params['page'] - 1) * $params['page_size']);
$query->andWhere($params['where']);
$query->orderBy($params['orderby']);
// var_dump($query);die;
$dataProvider = new ActiveDataProvider(['query' => $query]);
// $dataProvider = new ArrayDataProvider(['allModels' => $query->all()]);
$dataProvider->pagination->page = $params['page'] - 1;
$dataProvider->pagination->pageSize = $params['page_size'];
// var_dump($dataProvider->models);die;
//var_dump($dataProvider);die;
return $dataProvider;
}
/**
* update sensor status with input status
* - update temperature
* - update humidity
* - update security mode
* - update all sensor equipment
*/
public function updateSensor()
{
$inputBin = Convert::powOf2($this->request['input_status']);
// get all sensor status of this station
$query = new Query();
$query->select('s.binary_pos, s.type, st.id, st.sensor_id, st.value');
$query->from('sensor_status st');
$query->leftJoin('sensor s', 'st.sensor_id = s.id');
$query->where('station_id = ' . $this->request['id']);
$sensors = $query->all();
if (!empty($sensors)) {
foreach ($sensors as $sensor) {
$vs = in_array($sensor['binary_pos'], $inputBin) ? 1 : 0;
if ($sensor['type'] == Sensor::TYPE_CONFIGURE) {
Yii::$app->db->createCommand()->update('sensor_status', ['value' => $vs], ['id' => $sensor['id']])->execute();
} else {
if ($sensor['type'] == Sensor::TYPE_VALUE) {
$value = '';
// if this is security mode
if ($sensor['sensor_id'] == Sensor::ID_SECURITY) {
// security mode handler
if ($this->request['message'] == self::MSG_ARMING) {
$value = 1;
}
if ($this->request['message'] == self::MSG_DISARM) {
$value = 0;
}
// compare with handler status
if (isset($this->handler['security']['status']) && $value != $this->handler['security']['status']) {
$value = $this->handler['security']['status'];
$this->sendBack = true;
}
// if security has been turn off, create an alarm
if ($sensor['value'] != $value) {
if ($value == 0) {
// create turn off security mode alarm
$message = 'Tat bao dong';
} elseif ($value == 1) {
// create turn on security mode alarm
$message = 'Bat bao dong';
}
$this->alarm($message);
}
}
// if this is temperature
if ($sensor['sensor_id'] == Sensor::ID_TEMPERATURE) {
$value = $this->request['temp'];
}
// if this is humidity
if ($sensor['sensor_id'] == Sensor::ID_HUMIDITY) {
$value = $this->request['humi'];
}
Yii::$app->db->createCommand()->update('sensor_status', ['value' => $value], ['id' => $sensor['id']])->execute();
}
}
}
}
}
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];
}
/**
* @return array
*/
public function apteki_excel()
{
$db = new Query();
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$db->andFilterWhere(['=', 'apteki.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$db->andFilterWhere(['=', 'apteki.pi_id', Yii::$app->user->identity->id]);
}
if ($_POST[pharmopeka]) {
$db->andWhere(['=', 'apteki.farmopeka', '1']);
}
$db->from('apteki');
$db->select(['ur_l.name', 'sb_site_users.password_simple', 'sb_site_users.su_login', 'ur_l.id as id2', 'apteki.address', 'apteki.id', 'region.name as rname', 'apteki.tip', 'apteki.contact_phone', 'apteki.contact_face', 'apteki.contact_face_dolj', 'apteki.contact_mail', 'apteki.farmopeka', 'apteki.site']);
$db->leftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
$db->leftJoin('region', 'apteki.region_id = region.id');
$db->LeftJoin('sb_site_users', 'sb_site_users.apteki_id = apteki.id');
$data = $db->all();
$count = $db->count();
for ($i = 0; $i < count($data); $i++) {
if ($data[$i]['farmopeka']) {
$data[$i]['farmopeka'] = "Да";
} else {
$data[$i]['farmopeka'] = "Нет";
}
}
$array = array('draw' => intval(Yii::$app->request->post('draw')), 'recordsTotal' => $count, 'recordsFiltered' => $count, 'data' => $data);
return $data;
}
