package com.qianwen.smartman.modules.mdc.mapper;
|
|
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
|
import java.sql.Timestamp;
|
import java.time.DayOfWeek;
|
import java.time.LocalDate;
|
import java.time.temporal.WeekFields;
|
import java.util.List;
|
import java.util.Random;
|
import javax.sql.DataSource;
|
import com.qianwen.smartman.common.constant.CalendarConstant;
|
import com.qianwen.core.tool.utils.DateUtil;
|
import com.qianwen.smartman.modules.smis.entity.Workstation;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Component;
|
|
@Component
|
public class TimeUsedAnalysisMapper {
|
private final JdbcTemplate jdbcTemplate;
|
|
public TimeUsedAnalysisMapper(DataSource dataSource) {
|
this.jdbcTemplate = new JdbcTemplate(((DynamicRoutingDataSource) dataSource).getDataSource("tdengine"));
|
}
|
|
public void save(List<Workstation> workstations) {
|
dropTable(workstations);
|
createTable(workstations);
|
insert(workstations);
|
}
|
|
public void saveOutput(List<Workstation> workstations) {
|
dropTable(workstations);
|
createTable(workstations);
|
insertOutput(workstations);
|
}
|
|
public void saveAlarm(List<Workstation> workstations) {
|
dropTableAlarm(workstations);
|
createTableAlarm(workstations);
|
insertAlarm(workstations);
|
}
|
|
public void dropTable(List<Workstation> workstations) {
|
workstations.forEach(w -> {
|
String sql = "drop table if exists iot_data.aggregate_state_" + w.getId();
|
this.jdbcTemplate.execute(sql);
|
});
|
}
|
|
public void dropTableAlarm(List<Workstation> workstations) {
|
workstations.forEach(w -> {
|
String sql = "drop table if exists alarm_" + w.getId();
|
this.jdbcTemplate.execute(sql);
|
});
|
}
|
|
public void createTable(List<Workstation> workstations) {
|
workstations.forEach(w -> {
|
String createTable = "create table aggregate_state_" + w.getId() + " using super_aggregate_state tags(" + w.getId() + ")";
|
this.jdbcTemplate.execute(createTable);
|
});
|
}
|
|
public void processData(Long workstationId, String name, String value) {
|
createProcess(workstationId, name);
|
insertProcess(workstationId, name, value);
|
}
|
|
public void createProcess(Long workstationId, String name) {
|
String createTable = "create table if not exists iot_data.process_param_" + workstationId + "_" + name + " using super_collect_data tags(" + workstationId + ",'" + name + "')";
|
this.jdbcTemplate.execute(createTable);
|
}
|
|
public void insertProcess(Long workstationId, String name, String value) {
|
String sql = "insert into process_param_" + workstationId + "_" + name + " values" + value;
|
System.out.println(sql);
|
this.jdbcTemplate.update(sql);
|
}
|
|
public void createTableAlarm(List<Workstation> workstations) {
|
workstations.forEach(w -> {
|
String createTable = "create table alarm_" + w.getId() + " using super_alarm tags(" + w.getId() + ")";
|
this.jdbcTemplate.execute(createTable);
|
});
|
}
|
|
public void insert(List<Workstation> workstations) {
|
Random random = new Random();
|
workstations.forEach(workstation -> {
|
String calendarCode = workstation.getCalendarCode();
|
String sql = "insert into aggregate_state_" + workstation.getId() + " values" + insert(random, calendarCode == null ? CalendarConstant.DEFAULT_CALENDAR : calendarCode);
|
System.out.println(sql);
|
this.jdbcTemplate.update(sql);
|
});
|
}
|
|
public void insertOutput(List<Workstation> workstations) {
|
Random random = new Random();
|
workstations.forEach(workstation -> {
|
String calendarCode = workstation.getCalendarCode();
|
String sql = "insert into aggregate_output_" + workstation.getId() + " values" + insertOutput(random, calendarCode == null ? CalendarConstant.DEFAULT_CALENDAR : calendarCode);
|
this.jdbcTemplate.update(sql);
|
});
|
}
|
|
private String insertOutput(Random random, String calendarCode) {
|
long time = System.currentTimeMillis();
|
LocalDate now = LocalDate.now();
|
StringBuilder sb = new StringBuilder(sqlOutput(time - 1, random.nextInt(1000), random.nextInt(6), now, calendarCode));
|
for (int i = 0; i < 50; i++) {
|
sb.append(",").append(sqlOutput(time + (i * 1000), random.nextInt(1000), random.nextInt(6), now, calendarCode));
|
}
|
return sb.toString();
|
}
|
|
private String sqlOutput(long time, int duration, int value, LocalDate now, String calendarCode) {
|
return "('" + new Timestamp(time) + "','" + new Timestamp(time + 5000) + "','" + new Random().nextInt(6) + "','" + new Random().nextInt(6) + "','" + new Random().nextInt(6) + "','" + calendarCode + "','" + now.getYear() + "','" + now.getMonthValue() + "','" + now.get(WeekFields.of(DayOfWeek.MONDAY, 1).weekOfYear()) + "','" + DateUtil.format(now, "yyyyMMdd") + "','1','1','" + value + "','" + new Random().nextInt(6) + "',0)";
|
}
|
|
private String insert(Random random, String calendarCode) {
|
long time = System.currentTimeMillis();
|
LocalDate now = LocalDate.now();
|
StringBuilder sb = new StringBuilder(sql(time - 5000, time, random.nextInt(5) + 1, now, calendarCode));
|
for (int i = 1; i < 50; i++) {
|
long endTime = time + (i * 500000);
|
sb.append(",").append(sql(time, endTime, random.nextInt(5) + 1, now, calendarCode));
|
time = endTime;
|
}
|
return sb.toString();
|
}
|
|
private String sql(long startTime, long endTime, int value, LocalDate now, String calendarCode) {
|
int duration = Math.toIntExact((endTime - startTime) / 1000);
|
int wcs = new Random().nextInt(3) + 1;
|
int rps = new Random().nextInt(5) + 1;
|
return "('" + new Timestamp(startTime) + "','" + new Timestamp(endTime) + "','" + duration + "','" + value + "','" + calendarCode + "','" + now.getYear() + "','" + now.getMonthValue() + "','" + now.get(WeekFields.of(DayOfWeek.MONDAY, 1).weekOfYear()) + "','" + DateUtil.format(now, "yyyyMMdd") + "','" + wcs + "','1','" + value + "','" + rps + "',0," + (rps == 2 ? 0 : 1) + ")";
|
}
|
|
private String sql(long time, int value, LocalDate now, String calendarCode) {
|
return "('" + new Timestamp(time) + "'," + ((Object) null) + "," + ((Object) null) + ",'" + value + "','" + calendarCode + "','" + now.getYear() + "','" + now.getMonthValue() + "','" + now.get(WeekFields.of(DayOfWeek.MONDAY, 1).weekOfYear()) + "','" + DateUtil.formatDate(now) + "','1','1','0','0',0)";
|
}
|
|
public void insertAlarm(List<Workstation> workstations) {
|
workstations.forEach(workstation -> {
|
String calendarCode = workstation.getCalendarCode();
|
String sql = "insert into alarm_" + workstation.getId() + " values" + insertAlarm(calendarCode == null ? CalendarConstant.DEFAULT_CALENDAR : calendarCode);
|
this.jdbcTemplate.update(sql);
|
});
|
}
|
|
private String insertAlarm(String calendarCode) {
|
long time = System.currentTimeMillis();
|
LocalDate now = LocalDate.now();
|
StringBuilder sb = new StringBuilder(sqlAlarm(time - 1, now, calendarCode));
|
for (int i = 0; i < 50; i++) {
|
sb.append(",").append(sqlAlarm(time + i, now, calendarCode));
|
}
|
return sb.toString();
|
}
|
|
private String sqlAlarm(long time, LocalDate now, String calendarCode) {
|
return "('" + new Timestamp(time) + "','" + calendarCode + "','" + now.getYear() + "','" + now.getMonthValue() + "','" + now.get(WeekFields.of(DayOfWeek.MONDAY, 1).weekOfYear()) + "','" + DateUtil.formatDate(now) + "','1','1','1710001','报警啦','1')";
|
}
|
}
|
