public function actionEmptyregional()
{
$db = new Query();
$db->from('ur_l');
$db->select(['ur_l.name', 'ur_l.id', 'GROUP_CONCAT(region.name) as rname']);
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
//$db->andWhere(['like', 'log_reestr.created_at', $date]);
$db->GroupBy('ur_l.id');
$db->orderBy('rname', 'ur_l.name');
$data = $db->all();
return $this->render('empty', ['data' => $data]);
}
public static function All_excel($select, $where, $groupby, $count)
{
$db = new Query();
$db->from('apteki');
$select[] = 'ur_l.inn as inn2';
$select[] = 'ur_l.id as id2';
$select[] = 'region.id as rid';
$db->select($select);
$db->LeftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
//$db->Where('=', 'region.id',$where[region]);
if ($where['region']) {
$db->andWhere(['=', 'region.id', $where['region']]);
}
if ($where['regional']) {
$db->andWhere(['=', 'users.id', $where['regional']]);
}
if ($groupby['ur_l.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->InnerJoin('ur_l as u2', 'ur_l.id=region_ur_l.id_ur');
$db->GroupBy('ur_l.id');
}
if (!$groupby['ur_l.id']) {
$db->LeftJoin('region', 'apteki.region_id = region.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('users', 'apteki.regional_id = users.id');
$db->LeftJoin('view_pi', 'apteki.pi_id = view_pi.id');
$db->LeftJoin('sb_site_users', 'sb_site_users.ur_l_id = ur_l.id');
// $db->GroupBy('apteki.id');
// $db->orderBy('region.name','ur_l.name','apteki.name');
$db->orderBy(['region.name' => SORT_ASC, 'ur_l.name' => SORT_ASC]);
$data = $db->all();
$countAll = $db->count();
for ($i = 0; $i < count($data); $i++) {
/*
$count = (new Query())
->from('apteki')
->where(['ur_l_id' => $data[$i]['id2']])
->count();
*/
if ($data[$i]['ur_l__farmopeka']) {
$data[$i]['ur_l__farmopeka'] = "Да";
} else {
$data[$i]['ur_l__farmopeka'] = "Нет";
}
if ($data[$i]['apteki__farmopeka']) {
$data[$i]['apteki__farmopeka'] = "Да";
} else {
$data[$i]['apteki__farmopeka'] = "Нет";
}
if ($data[$i]['ur_l__plat']) {
$data[$i]['ur_l__plat'] = "Да";
} else {
$data[$i]['ur_l__plat'] = "Нет";
}
if ($count['apteki']) {
$count2 = new Query();
$count2->from('apteki');
$count2->where(['ur_l_id' => $data[$i]['id2']]);
$count2->andWhere(['apteki.region_id' => $data[$i]['rid']]);
if (!isset($temp)) {
$temp = "";
}
if ($temp != $data[$i]['rid'] . $data[$i]['id2']) {
$counts = $count2->count();
$data[$i]['apteki'] = $counts;
$no = 1;
} else {
$data[$i]['apteki'] = "";
$data[$i]['ur_l'] = "";
$no = 2;
}
$temp = $data[$i]['rid'] . $data[$i]['id2'];
}
if ($no == 1) {
if ($count['ur_l']) {
$count2 = new Query();
$count2->from('ur_l');
$count2->where(['inn' => $data[$i]['inn2']]);
$counts = $count2->count();
$data[$i]['ur_l'] = $counts;
}
$data[$i]['count'] = $countAll;
}
}
return $data;
}