public static function multipleDelete($params)
{
//build conditions
$conditions = [];
if (isset($params['from_date']) && trim($params['from_date']) !== '') {
$fromTime = Convert::date2Time($params['from_date'], 'd/m/Y');
$conditions[] = "w.warning_time >= " . $fromTime;
}
if (isset($params['to_date']) && trim($params['to_date']) !== '') {
$toTime = Convert::date2Time($params['to_date'], 'd/m/Y');
$conditions[] = "w.warning_time <= " . $toTime;
}
if (isset($params['station']) && !empty($params['station'])) {
$conditions[] = "s.id IN(" . implode(',', $params['station']) . ")";
}
if (isset($params['area_id']) && $params['area_id'] > 0) {
$conditions[] = "s.area_id = " . $params['area_id'];
}
if (isset($params['center_id']) && $params['center_id'] > 0) {
$conditions[] = "s.center_id = " . $params['center_id'];
}
//delete real pictures
$sql = "SELECT p.picture\n FROM warning_picture p\n INNER JOIN warning w ON(w.id = p.warning_id)\n INNER JOIN station s ON(s.id = w.station_id)\n WHERE 1";
if (!empty($conditions)) {
$sql .= " AND " . implode(' AND ', $conditions);
}
$pictures = Yii::$app->db->createCommand($sql)->queryAll();
if (!empty($pictures)) {
foreach ($pictures as $pic) {
$path = $_SERVER['DOCUMENT_ROOT'] . '/frontend/web/uploads/' . $pic['picture'];
if (file_exists($path)) {
unlink($path);
}
}
}
//delete warning picture in db
$sql = "DELETE p\n FROM warning_picture p\n INNER JOIN warning w ON(w.id = p.warning_id)\n INNER JOIN station s ON(s.id = w.station_id)\n WHERE 1";
if (!empty($conditions)) {
$sql .= ' AND ' . implode(' AND ', $conditions);
}
Yii::$app->db->createCommand($sql)->execute();
//delete warning in db
$sql = "DELETE w\n FROM warning w\n INNER JOIN station s ON(s.id = w.station_id)\n WHERE 1";
if (!empty($conditions)) {
$sql .= ' AND ' . implode(' AND ', $conditions);
}
Yii::$app->db->createCommand($sql)->execute();
}
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];
}
