public function actionIndex()
{
// get station ids by role
$permCondition = [];
$role = new Role();
if (!$role->isAdministrator) {
$stationIds = Station::getByRole($role->getPosition(), Yii::$app->user->id);
$permCondition['s.id'] = $stationIds;
}
// default time points
$timePoints = Convert::currentTimePoints();
// get post form data
$get = Yii::$app->request->get('get_by');
if ($get == 'today') {
$timePoints = Convert::currentTimePoints();
} else {
if ($get == 'week') {
$timePoints = Convert::currentWeekTimePoints();
} else {
if ($get == 'month') {
$timePoints = Convert::currentMonthTimePoints();
}
}
}
$andWhere = ['>=', 'w.warning_time', $timePoints['start']];
$andWhere1 = ['<=', 'w.warning_time', $timePoints['end']];
// get data
$query = new Query();
$warnings = $query->select('w.warning_time, s.area_id')->from('warning w')->innerJoin('station s', 'w.station_id = s.id')->where($permCondition)->andWhere($andWhere)->andWhere($andWhere1)->all();
// data
$parseData = [];
$data = [];
// get areas
$areaQuery = new Query();
$areas = $areaQuery->select('a.*')->from('area a')->innerJoin('station s', 's.area_id = a.id')->where($permCondition)->groupBy('a.id')->all();
$parseData['areas'] = $areas;
if (!empty($areas)) {
foreach ($areas as $area) {
$data[$area['id']]['number'] = 0;
$data[$area['id']]['start'] = 0;
$data[$area['id']]['end'] = 0;
if (!empty($warnings)) {
$no = 1;
foreach ($warnings as $w) {
if ($w['area_id'] == $area['id']) {
$data[$area['id']]['number']++;
if ($no == 1) {
$data[$area['id']]['start'] = $w['warning_time'];
$data[$area['id']]['end'] = $w['warning_time'];
} else {
if ($w['warning_time'] < $data[$area['id']]['start']) {
$data[$area['id']]['start'] = $w['warning_time'];
}
if ($w['warning_time'] > $data[$area['id']]['end']) {
$data[$area['id']]['end'] = $w['warning_time'];
}
}
$no++;
}
}
}
}
}
$parseData['data'] = $data;
return $this->render('warning', $parseData);
}
public function actionIndex()
{
$query = Station::find()->select('station.*');
// session
$role = new Role();
if (!$role->isAdministrator) {
$position = $role->getPosition();
$stationIds = Station::getByRole($position, Yii::$app->user->id);
$condition = ['in', 'station.id', $stationIds];
$query->where($condition);
}
// conditions
$areaId = Yii::$app->request->get('area_id');
if ($areaId > 0) {
$query->andWhere(['area_id' => $areaId]);
}
// has_warning
$hasWarning = Yii::$app->request->get('has_warning');
if ($hasWarning) {
$getBy = Yii::$app->request->get('get_by');
if ($getBy == 'today') {
$timePoints = Convert::currentTimePoints();
} else {
if ($getBy == 'week') {
$timePoints = Convert::currentWeekTimePoints();
} else {
if ($getBy == 'month') {
$timePoints = Convert::currentMonthTimePoints();
}
}
}
if (isset($timePoints)) {
$subSql = new Query();
$subSql->select('s.id')->from('station s')->innerJoin('warning w', 'w.station_id = s.id')->andWhere(['>=', 'w.warning_time', $timePoints['start']])->andWhere(['<=', 'w.warning_time', $timePoints['end']]);
if ($hasWarning == 'yes') {
$query->andWhere(['in', 'id', $subSql]);
} else {
if ($hasWarning == 'no') {
$query->andWhere(['not in', 'id', $subSql]);
}
}
}
}
// connect
$connect = Yii::$app->request->get('connect');
if ($connect) {
if ($connect == 'yes') {
$query->andWhere(['status' => Station::STATUS_CONNECTED]);
} else {
if ($connect == 'no') {
$query->andWhere(['status' => Station::STATUS_LOST]);
}
}
}
// security
$security = Yii::$app->request->get('security');
if ($security) {
$query->leftJoin('sensor_status', 'sensor_status.station_id = station.id');
$query->andWhere(['sensor_id' => Sensor::ID_SECURITY]);
if ($security == 'on') {
$query->andWhere(['sensor_status.value' => Sensor::SECURITY_ON]);
} else {
if ($security == 'off') {
$query->andWhere(['sensor_status.value' => Sensor::SECURITY_OFF]);
}
}
}
$dataProvider = new ActiveDataProvider(['query' => $query->groupBy('station.id')]);
return $this->render('index', ['dataProvider' => $dataProvider]);
}
private function buildQuery($export = false)
{
$parseData = [];
if ($export) {
$query = new Query();
$query->select(['warning.message AS message', 'station.name AS station_name', 'DATE_FORMAT(FROM_UNIXTIME(warning.warning_time), "%d/%m/%Y %H:%i:%s") AS warning_date'])->from('warning')->leftJoin('station', 'station.id = warning.station_id')->where([]);
} else {
$query = Warning::find()->leftJoin('station', 'station.id = warning.station_id')->where([]);
}
// permission
$stationIds = [];
$role = new Role();
if (!$role->isAdministrator) {
$position = $role->getPosition();
$stationIds = Station::getByRole($position, Yii::$app->user->id);
}
//filter by stations
$stations = Yii::$app->request->get('station');
if (!empty($stations)) {
$stationIds = array_merge($stationIds, $stations);
}
if (!empty($stationIds)) {
foreach ($stationIds as $k => $id) {
if (intval($id) <= 0) {
unset($stationIds[$k]);
}
}
}
if (!empty($stationIds)) {
$condition = ['in', 'station_id', $stationIds];
$query->where($condition);
}
// filter by area
$areaId = Yii::$app->request->get('area');
if ($areaId > 0) {
$query->andWhere(['station.area_id' => $areaId]);
}
// filter by center
$center = Yii::$app->request->get('center');
if ($center > 0) {
$query->andWhere(['station.center_id' => $center]);
}
// filter by time points
$getBy = Yii::$app->request->get('get_by');
if ($getBy) {
if ($getBy == 'today') {
$timePoints = Convert::currentTimePoints();
} else {
if ($getBy == 'week') {
$timePoints = Convert::currentWeekTimePoints();
} else {
if ($getBy == 'month') {
$timePoints = Convert::currentMonthTimePoints();
}
}
}
$query->andWhere(['>=', 'warning.warning_time', $timePoints['start']]);
$query->andWhere(['<=', 'warning.warning_time', $timePoints['end']]);
}
// filter by time duration
$fromDate = Yii::$app->request->get('from_date');
if ($fromDate) {
$fromTime = Convert::date2Time($fromDate, 'd/m/Y');
$query->andWhere(['>=', 'warning_time', $fromTime]);
}
$toDate = Yii::$app->request->get('to_date');
if ($toDate) {
$toTime = Convert::date2Time($toDate, 'd/m/Y', 'end');
$query->andWhere(['<=', 'warning_time', $toTime]);
}
$query->orderBy('warning_time DESC');
return ['query' => $query, 'parseData' => $parseData];
}
public function actionIndex()
{
// default time duration
$timePoints = Convert::currentTimePoints();
$getBy = Yii::$app->request->get('get_by');
if ($getBy == 'week') {
$timePoints = Convert::currentWeekTimePoints();
} else {
if ($getBy == 'month') {
$timePoints = Convert::currentMonthTimePoints();
}
}
$whereClause = ['sst.sensor_id' => Sensor::ID_SECURITY];
// get station ids by role
$role = new Role();
if (!$role->isAdministrator) {
$position = $role->getPosition();
$stationIds = Station::getByRole($position, Yii::$app->user->id);
$whereClause['s.id'] = $stationIds;
}
// get data
$query = new Query();
$stations = $query->select('s.area_id, s.status AS station_status, sst.value AS sensor_value, count(w.id) AS total_warning, max(w.warning_time) AS last_warning_time')->from('station s')->leftJoin('sensor_status sst', 'sst.station_id = s.id')->leftJoin('warning w', 'w.station_id = s.id')->where($whereClause)->groupBy('s.id')->all();
// data
$parseData = [];
$data = [];
// get areas
$areas = Area::find()->all();
$parseData['areas'] = $areas;
if (!empty($areas)) {
$notHave = [];
foreach ($areas as $area) {
$have = 0;
$data[$area['id']]['has_warning'] = 0;
$data[$area['id']]['no_warning'] = 0;
$data[$area['id']]['security_on'] = 0;
$data[$area['id']]['security_off'] = 0;
$data[$area['id']]['connected'] = 0;
$data[$area['id']]['lost_connect'] = 0;
$data[$area['id']]['last_warning_time'] = 0;
// security and connect stations
if (!empty($stations)) {
foreach ($stations as $station) {
if ($station['area_id'] == $area['id']) {
$have = 1;
// warnings
if ($station['total_warning'] > 0 && $station['last_warning_time'] >= $timePoints['start'] && $station['last_warning_time'] <= $timePoints['end']) {
$data[$area['id']]['has_warning']++;
$data[$area['id']]['last_warning_time'] = $station['last_warning_time'] > $data[$area['id']]['last_warning_time'] ? $station['last_warning_time'] : $data[$area['id']]['last_warning_time'];
} else {
$data[$area['id']]['no_warning']++;
}
// status
if ($station['station_status'] == Station::STATUS_CONNECTED) {
$data[$area['id']]['connected']++;
} else {
if ($station['station_status'] == Station::STATUS_LOST) {
$data[$area['id']]['lost_connect']++;
}
}
// security mode
if ($station['sensor_value'] == Sensor::SECURITY_ON) {
$data[$area['id']]['security_on']++;
} else {
if ($station['sensor_value'] == Sensor::SECURITY_OFF) {
$data[$area['id']]['security_off']++;
}
}
}
}
}
if ($have == 0) {
$notHave[] = $area['id'];
}
}
if (!empty($notHave)) {
foreach ($notHave as $not) {
unset($data[$not]);
}
}
}
$parseData['data'] = $data;
return $this->render('station', $parseData);
}