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; }