package com.casic.alarm.manager;
import com.casic.alarm.DTO.*;
import com.casic.alarm.Request.DeviceRequest;
import com.casic.alarm.domain.AcceptPerson;
import com.casic.alarm.domain.Device;
import com.casic.alarm.domain.DeviceType;
import com.casic.alarm.restful.DeviceUtilDTO;
import com.casic.alarm.restful.DocumentHandler;
import com.casic.alarm.utils.Configure;
import com.casic.alarm.utils.DateUtils;
import com.casic.core.hibernate.HibernateEntityDao;
import com.casic.core.hibernate.HibernateUtils;
import com.casic.core.hibernate.MatchType;
import com.casic.core.page.Page;
import com.casic.core.util.StringUtils;
import com.google.gson.GsonBuilder;
import jxl.Workbook;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.activiti.engine.impl.util.json.JSONArray;
import org.activiti.engine.impl.util.json.JSONObject;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;
@Service
public class DeviceManager extends HibernateEntityDao<Device> {
private DeviceTypeManager deviceTypeManager;
private AcceptPersonManager acceptPersonManager;
private DeviceSensorManager deviceSensorManager;
private AlarmRecordManager alarmRecordManager;
private AlarmRuleManager alarmRuleManager;
private SensorTypeManager sensorTypeManager;
private DeviceConfigManager deviceConfigManager;
@Resource
private DocumentHandler documentHandler;
@Resource
private DeviceRequest deviceRequest;
@Resource
public void setAcceptPersonManager(AcceptPersonManager acceptPersonManager) {
this.acceptPersonManager = acceptPersonManager;
}
@Resource
public void setDeviceTypeManager(DeviceTypeManager deviceTypeManager) {
this.deviceTypeManager = deviceTypeManager;
}
@Resource
public void setDeviceSensorManager(DeviceSensorManager deviceSensorManager) {
this.deviceSensorManager = deviceSensorManager;
}
@Resource
public void setSensorTypeManager(SensorTypeManager sensorTypeManager) {
this.sensorTypeManager = sensorTypeManager;
}
@Resource
public void setDeviceConfigManager(DeviceConfigManager deviceConfigManager) {
this.deviceConfigManager = deviceConfigManager;
}
@Resource
public void setAlarmRuleManager(AlarmRuleManager alarmRuleManager) {
this.alarmRuleManager = alarmRuleManager;
}
public Criteria getCriteria() {
return getSession().createCriteria(Device.class);
}
public List<Device> findDeviceListByDeviceCode(String code) {
Criteria criteria = getCriteria();
criteria.add(Restrictions.eq("active", true))
.add(Restrictions.eq("devCode", code));
return criteria.list();
}
public boolean isExist(String code, Long id) {
List<Device> list = findDeviceListByDeviceCode(code);
if (list.size() > 1) {
return true;
} else if (list.size() == 1) {
if (list.get(0).getId().longValue() != id.longValue()) {
return true;
}
}
return false;
}
public Map editDevice(DeviceDTO model) {
Map<String, Object> map = new HashMap<String, Object>();
try {
if (isExist(model.getDevCode(), model.getId())) {
map.put("success", false);
map.put("msg", "设备编号不能重复！");
return map;
}
DeviceType deviceType = deviceTypeManager.findUniqueBy("id", model.getDeviceTypeId());
// AcceptPerson acceptPerson = acceptPersonManager.findUniqueBy("id", Long.parseLong(model.getOwnerId()));
Device device = findUniqueBy("id", model.getId());
if (null != deviceType) {
map.put("srcPath", device.getDeviceType().getLocation());
map.put("destPath", deviceType.getLocation());
map.put("srcDevId", device.getDevCode());
map.put("destDevId", model.getDevCode());
map.put("srcDevType", device.getDeviceType().getTypeName());
map.put("destDevType", deviceType.getTypeName());
device.setDeviceType(deviceType);
device.setDevName(deviceType.getTypeName() + model.getDevCode());
}
// if(null!=acceptPerson)
// device.setAcceptPerson(acceptPerson);
device.setDevCode(model.getDevCode());
device.setGaocheng(model.getGaocheng());
device.setFactory(model.getFactory());
save(device);
map.put("success", true);
map.put("msg", "编辑成功！");
} catch (Exception e) {
e.printStackTrace();
map.put("success", false);
map.put("msg", "编辑失败！");
}
return map;
}
public Map saveDevice(DeviceDTO model) {
Map<String, Object> map = new HashMap<String, Object>();
try {
Device device = null;
Criteria criteria = getSession().createCriteria(Device.class);
criteria.add(Restrictions.and(
HibernateUtils.buildCriterion("active", true, MatchType.EQ),
HibernateUtils.buildCriterion("devCode", model.getDevCode(), MatchType.EQ)));
List<Device> deviceList = criteria.list();
if (deviceList.size() > 0) {
for (int i = 0; i < deviceList.size(); i++) {
device = deviceList.get(i);
if (i == 0) {
device = getSaveDevice(deviceList.get(i), model);
} else {
device.setActive(false);
}
// DeviceUtilDTO.sendMsg(Configure.getProperty("batchCrudURL"),
// new ArrayList<DeviceUtilDTO>(
// Arrays.asList(new DeviceUtilDTO(
// model, "V1.1", "U"))));
save(device);
}
} else {
device = new Device();
device = getSaveDevice(device, model);
// DeviceUtilDTO.sendMsg(Configure.getProperty("batchCrudURL"),
// new ArrayList<DeviceUtilDTO>(
// Arrays.asList(new DeviceUtilDTO(
// model, "V1.0", "C"))));
save(device);
}
} catch (Exception e) {
e.printStackTrace();
map.put("success", false);
map.put("msg", "设备编号" + model.getDevCode() + "保存失败");
return map;
}
//传感器挂载
Map<String, Object> paraMap = new HashMap<String, Object>();
String hql = " from Device where active=1 and devCode = '" + model.getDevCode() + "'";
List<Device> deviceList = (List<Device>) createQuery(hql, paraMap).list();
if (deviceList.size() > 0) {
model.setId(deviceList.get(0).getId());
}
saveDeviceSensor(model);
map.put("success", true);
map.put("msg", "保存成功");
map.put("dev", model);
return map;
}
public Device getSaveDevice(Device device, DeviceDTO model) throws Exception {
DeviceType deviceType = deviceTypeManager.findUniqueBy("id", model.getDeviceTypeId());
AcceptPerson acceptPerson = acceptPersonManager.findUniqueBy("id", Long.parseLong(model.getOwnerId()));
model.setDeviceTypeName(deviceType.getTypeName());
model.setModelLocation(deviceType.getLocation());
device.setDeviceType(deviceType);
device.setAcceptPerson(acceptPerson);
device.setLongtitude(model.getLongtitude());
device.setLatitude(model.getLatitude());
device.setDevCode(model.getDevCode());
device.setGaocheng(model.getGaocheng());
device.setHeight(model.getHeight());
device.setDevName(model.getDevName());
device.setInstallDate(DateUtils.sdf1.parse(model.getSetupDate()));
device.setFactory(model.getRoadName());
device.setTurnX(model.getAttachLayer());
device.setTurnY(model.getAttachFeature());
return device;
}
private void saveDeviceSensor(DeviceDTO model) {
String devTypeName = model.getDeviceTypeName();
DeviceSensorDTO deviceSensorDTO1 = new DeviceSensorDTO();
DeviceSensorDTO deviceSensorDTO2 = new DeviceSensorDTO();
DeviceSensorDTO deviceSensorDTO3 = new DeviceSensorDTO();
DeviceSensorDTO deviceSensorDTO4 = new DeviceSensorDTO();
deviceSensorDTO1.setDeviceid(model.getId());
deviceSensorDTO2.setDeviceid(model.getId());
deviceSensorDTO3.setDeviceid(model.getId());
deviceSensorDTO4.setDeviceid(model.getId());
if (devTypeName.equals("液位监测仪")) {
deviceSensorDTO1.setSensorid("液位" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000034");
} else if (devTypeName.equals("噪声记录仪")) {
deviceSensorDTO1.setSensorid("噪声" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000032");
} else if (devTypeName.equals("多功能漏损监测仪")) {
deviceSensorDTO1.setSensorid("流量" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000031");
deviceSensorDTO2.setSensorid("噪声" + model.getDevCode());
deviceSensorDTO2.setSensorcode("000032");
deviceSensorDTO3.setSensorid("压力" + model.getDevCode());
deviceSensorDTO3.setSensorcode("000033");
} else if (devTypeName.equals("水质监测仪")) {
deviceSensorDTO1.setSensorid("余氯" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000035");
deviceSensorDTO2.setSensorid("pH" + model.getDevCode());
deviceSensorDTO2.setSensorcode("000036");
deviceSensorDTO3.setSensorid("浊度" + model.getDevCode());
deviceSensorDTO3.setSensorcode("000037");
deviceSensorDTO4.setSensorid("电导率" + model.getDevCode());
deviceSensorDTO4.setSensorcode("000038");
} else if (devTypeName.equals("远传水表")) {
deviceSensorDTO1.setSensorid("流量" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000031");
} else if (devTypeName.equals("有害气体监测仪")) {
deviceSensorDTO1.setSensorid("一氧化碳" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000039");
deviceSensorDTO2.setSensorid("硫化氢" + model.getDevCode());
deviceSensorDTO2.setSensorcode("000040");
deviceSensorDTO3.setSensorid("氧气" + model.getDevCode());
deviceSensorDTO3.setSensorcode("000041");
deviceSensorDTO4.setSensorid("甲烷" + model.getDevCode());
deviceSensorDTO4.setSensorcode("000042");
} else if (devTypeName.equals("雨量计")) {
deviceSensorDTO1.setSensorid("雨量" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000043");
} else if (devTypeName.equals("燃气智能监测终端")) {
deviceSensorDTO1.setSensorid("流量" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000031");
deviceSensorDTO2.setSensorid("压力" + model.getDevCode());
deviceSensorDTO2.setSensorcode("000033");
} else if (devTypeName.equals("入户燃气报警器")) {
deviceSensorDTO1.setSensorid("浓度" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000044");
} else if (devTypeName.equals("远传电表")) {
deviceSensorDTO1.setSensorid("用电量" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000047");
} else if (devTypeName.equals("温度压力监测仪")) {
deviceSensorDTO1.setSensorid("温度" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000050");
deviceSensorDTO2.setSensorid("压力" + model.getDevCode());
deviceSensorDTO2.setSensorcode("000033");
} else if (devTypeName.equals("井盖状态监测仪")) {
deviceSensorDTO1.setSensorid("翻转" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000048");
} else if (devTypeName.equals("保温层下腐蚀速率监测仪")) {
deviceSensorDTO1.setSensorid("外腐蚀速率" + model.getDevCode());
deviceSensorDTO1.setSensorcode("000060");
}
if (null != deviceSensorDTO1.getSensorcode()) {
deviceSensorManager.saveModel(deviceSensorDTO1);
}
if (null != deviceSensorDTO2.getSensorcode()) {
deviceSensorManager.saveModel(deviceSensorDTO2);
}
if (null != deviceSensorDTO3.getSensorcode()) {
deviceSensorManager.saveModel(deviceSensorDTO3);
}
if (null != deviceSensorDTO4.getSensorcode()) {
deviceSensorManager.saveModel(deviceSensorDTO4);
}
}
public Map pageQueryDevice(DeviceDTO model, int page, int rows) {
try {
StringBuilder hql = new StringBuilder();
Map<String, Object> map = new HashMap<String, Object>();
hql.append("from Device where active=true");
if (StringUtils.isNotBlank(model.getDevCode())) {
hql.append(" and devCode like :devcode");
map.put("devcode", "%" + model.getDevCode() + "%");
}
if (StringUtils.isNotBlank(model.getDevName())) {
hql.append(" and devName like :devname");
map.put("devname", "%" + model.getDevName() + "%");
}
if (StringUtils.isNotBlank(model.getOwnerId())) {
hql.append(" and acceptPerson.id=:pid");
map.put("pid", Long.parseLong(model.getOwnerId()));
}
if (null != model.getDeviceTypeId()) {
hql.append(" and deviceType.id=:did");
map.put("did", model.getDeviceTypeId());
}
if (StringUtils.isNotBlank(model.getDeviceTypeName())) {
hql.append(" and deviceType.typeName=:nam");
map.put("nam", model.getDeviceTypeName());
}
hql.append(" order by deviceType.typeName asc, devCode asc");
page = page <= 0 ? 1 : page;
rows = rows <= 0 ? 10 : rows;
Page p = pagedQuery(hql.toString(), page, rows, map);
List<Device> list = (List<Device>) p.getResult();
int total = p.getTotalCount();
List<DeviceDTO> dtos = DeviceDTO.ConvertToDTOs(list);
map.clear();
map.put("rows", dtos);
map.put("total", total);
return map;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public List<DeviceDTO> getDeviceListByModel(DeviceDTO model) {
try {
StringBuilder hql = new StringBuilder();
Map<String, Object> map = new HashMap<String, Object>();
hql.append(" from Device " +
" where active=true ");
if (StringUtils.isNotBlank(model.getDeviceTypeName())) {
hql.append(" and deviceType.typeName=:nam");
map.put("nam", model.getDeviceTypeName());
}
hql.append(" order by devCode asc");
List<Device> deviceList = (List<Device>) createQuery(hql.toString(), map).list();
List<DeviceDTO> deviceDTOList = (List<DeviceDTO>) DeviceDTO.ConvertToDTOs(deviceList);
return deviceDTOList;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public List<DeviceDTO> queryDevList(DeviceDTO model) {
try {
StringBuilder hql = new StringBuilder();
Map<String, Object> map = new HashMap<String, Object>();
hql.append("from Device where active=true");
if (StringUtils.isNotBlank(model.getDevCode())) {
hql.append(" and devCode like :devcode");
map.put("devcode", "%" + model.getDevCode() + "%");
}
if (StringUtils.isNotBlank(model.getDevName())) {
hql.append(" and devName like :devname");
map.put("devname", "%" + model.getDevName() + "%");
}
if (StringUtils.isNotBlank(model.getOwnerId())) {
hql.append(" and acceptPerson.id=:pid");
map.put("pid", Long.parseLong(model.getOwnerId()));
}
if (null != model.getDeviceTypeId()) {
hql.append(" and deviceType.id=:did");
map.put("did", model.getDeviceTypeId());
}
if (StringUtils.isNotBlank(model.getDeviceTypeName())) {
hql.append(" and deviceType.typeName=:nam");
map.put("nam", model.getDeviceTypeName());
}
hql.append(" order by devCode asc");
List<Device> list = (List<Device>) createQuery(hql.toString(), map).list();
List<DeviceDTO> dtos = DeviceDTO.ConvertToDTOs(list);
return dtos;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 根据devCode查询设备信息（一台）
*/
public List<DeviceDTO> getDevInfo(String deviceCode) {
Map<String, Object> paraMap = new HashMap<String, Object>();
String hql = " from Device " +
" where devCode = '" + deviceCode + "'";
List<Device> deviceList = (List<Device>) createQuery(hql, paraMap).list();
List<DeviceDTO> deviceDTOList = new ArrayList<DeviceDTO>();
for (Device device : deviceList) {
DeviceDTO deviceDTO = new DeviceDTO();
deviceDTO.setDeviceTypeName(device.getDeviceType().getTypeName());
deviceDTO.setDevName(device.getDevName());
deviceDTOList.add(deviceDTO);
}
return deviceDTOList;
}
/**
* 返回所有设备信息列表
*/
public List<DeviceDTO> getDeviceList() {
Map<String, Object> paraMap = new HashMap<String, Object>();
String hql = " from Device ";
List<Device> deviceList = (List<Device>) createQuery(hql, paraMap).list();
List<DeviceDTO> deviceDTOList = new ArrayList<DeviceDTO>();
for (Device device : deviceList) {
DeviceDTO deviceDTO = new DeviceDTO();
deviceDTO.setDeviceTypeName(device.getDeviceType().getTypeName());
deviceDTO.setDevName(device.getDevName());
deviceDTOList.add(deviceDTO);
}
return deviceDTOList;
}
public Device findDeviceByDevCode(String devCode) {
Criteria criteria = getCriteria();
criteria.add(Restrictions.eq("active", true));
criteria.add(Restrictions.eq("devCode", devCode));
try {
List<Device> deviceList = criteria.list();
if (deviceList != null && deviceList.size() > 0) {
return deviceList.get(0);
}
} catch (Exception e) {
}
return null;
}
public List<DeviceDTO> findDtoListByDevType(DeviceDTO model) {
Criteria criteria = getCriteria();
criteria.add(Restrictions.eq("active", true));
criteria.createAlias("deviceType", "deviceType");
criteria.add(Restrictions.eq("deviceType.id", model.getDeviceTypeId()));
if (StringUtils.isNotBlank(model.getDevCode())) {
criteria.add(Restrictions.like("devCode", model.getDevCode() + "%"));
}
criteria.addOrder(Order.asc("devCode"));
return DeviceDTO.ConvertToDTOs(criteria.list());
}
public List<DeviceDTO> findDtoListByDevName() {
Criteria criteria = getCriteria();
criteria.add(Restrictions.eq("active", true));
criteria.addOrder(Order.desc("id"));
return DeviceDTO.ConvertToDTOs(criteria.list());
}
private static DecimalFormat df = null;
public static DecimalFormat getDecimalFormat() {
if (df == null) {
synchronized (DeviceManager.class) {
if (df == null) {
df = new DecimalFormat("0.000");
}
}
}
return df;
}
public boolean export() {
String targetfile = ServletActionContext.getServletContext().getRealPath(File.separator) +
"设备数据上传状态列表.xls";//输出的excel文件名
String worksheet = "sheet1";//输出的excel文件工作表名
String[] title = {"序号", "设备编号", "设备类型", "管线类型", "最新采集时间", "报警状态", "设备状态", "安装位置"};//excel工作表的标题
WritableWorkbook workbook;
try {
//创建可写入的Excel工作薄,运行生成的文件在tomcat/bin下
//workbook = Workbook.createWorkbook(new File("output.xls"));
OutputStream os = new FileOutputStream(targetfile);
workbook = Workbook.createWorkbook(os);
WritableFont font = new WritableFont(WritableFont.ARIAL);
WritableCellFormat cellFormat = new WritableCellFormat(font);
WritableSheet sheet = workbook.createSheet(worksheet, 0); //添加第一个工作表
//WritableSheet sheet1 = workbook.createSheet("MySheet1", 1); //可添加第二个工作
String result = deviceRequest.getDeviceList();
JSONArray array = new JSONArray(result);
jxl.write.Label label;
for (int i = 0; i < title.length; i++) {
//Label(列号,行号 ,内容 )
label = new jxl.write.Label(i, 0, title[i]); //put the title in row1
sheet.addCell(label);
if (0 != i) {
sheet.setColumnView(i, 25);
} else {
sheet.setColumnView(i, 10);
}
}
for (int i = 0, j = 0; i < array.length(); i++) {
JSONObject jsonObject = new JSONObject(array.get(i).toString());
//序号
label = new jxl.write.Label(0, j + 1, String.valueOf(j + 1));
sheet.addCell(label);
//设备编号
label = new jxl.write.Label(1, j + 1, jsonObject.getString("devCode"));
sheet.addCell(label);
//设备类型
label = new jxl.write.Label(2, j + 1, jsonObject.getString("typeName"));
sheet.addCell(label);
//管线类型
label = new jxl.write.Label(3, j + 1, jsonObject.getString("pipeType"));
sheet.addCell(label);
//最新采集时间
label = new jxl.write.Label(4, j + 1, jsonObject.getString("currentTime"));
sheet.addCell(label);
//报警状态
label = new jxl.write.Label(5, j + 1, jsonObject.getString("alarmStatus"));
sheet.addCell(label);
//设备状态
label = new jxl.write.Label(6, j + 1, jsonObject.getString("deviceStatus"));
sheet.addCell(label);
//安装位置
label = new jxl.write.Label(7, j + 1, jsonObject.getString("roadName"));
sheet.addCell(label);
j++;
}
workbook.write();
workbook.close();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
@Transactional
public Map removeDevice(DeviceDTO model) {
// String url= Configure.getProperty("sendURL")+Configure.getProperty("batchCrudURL");
// DeviceUtilDTO.sendMsg(Configure.getProperty("batchCrudURL"),
// new ArrayList<DeviceUtilDTO>(
// Arrays.asList(new DeviceUtilDTO(model.getDevCode(),"D"))));//同步数据
Map<String, Object> map = new HashMap<String, Object>();
Device device = get(model.getId());
deviceSensorManager.removeDeviceSensorByDevice(model);
deviceConfigManager.deleteDeviceConfigByDeviceCode(device.getDevCode());
device.setActive(false);
this.save(device);
map.put("success", true);
map.put("devCode", device.getDevCode());
map.put("msg", "设备删除成功！");
return map;
}
public String getWellDepth(String wellCode, String attachLayer) {
Map<String, Object> paraMap = new HashMap<String, Object>();
String wellDepth = "0";
try {
String sql = " select 井深 " +
" from " + attachLayer +
" where 编号 = :wellCode ";
paraMap.clear();
paraMap.put("wellCode", wellCode);
List<Object> depthList = (List<Object>) getSession().createSQLQuery(sql).setProperties(paraMap).list();
if (depthList.size() > 0) {
wellDepth = depthList.get(0).toString();
}
} catch (Exception e) {
e.printStackTrace();
String msg = e.getMessage();
}
return wellDepth;
}
public List<WeekDTO> getWeekList(String startTime, String endTime, String sensorName) {
Map<String, Object> paraMap = new HashMap<String, Object>();
List<WeekDTO> weekDTOList = new ArrayList<>();
List<WeekDTO> alarmDTOList = new ArrayList<>();
List<WeekDTO> placesDTOList = new ArrayList<>();
List<ProjectWeekDTO> projectDTOList = new ArrayList<>();
try {
String sql = "select t1.typename as devType,t0.num as total from （select t.devicetype_id,count(1) as num from alarm_device t where t.active=1 group by t.devicetype_id order by t.devicetype_id） t0 left join \n" +
"alarm_device_type t1 on t0.devicetype_id = t1.dbid ";
paraMap.clear();
List<Object[]> devList = (List<Object[]>) getSession().createSQLQuery(sql).setProperties(paraMap).list();
for (int i = 0; i < devList.size(); i++) {
WeekDTO weekDTO = new WeekDTO();
weekDTO.setAlarmNo((i + 1) + "");
weekDTO.setDevType(devList.get(i)[0].toString());
weekDTO.setInNum(devList.get(i)[1].toString());
weekDTO.setTotal(devList.get(i)[1].toString());
weekDTO.setRate("100%");
weekDTO.setPlaces("0");
weekDTO.setAlarms("0");
weekDTO.setAlarmRa("0");
weekDTOList.add(weekDTO);
}
String sql1 = "select count(1) as alarms, device_type_name as devType from alarm_alarm_record r where r.eventid is not null and to_char(r.recorddate,'yyyy-MM-dd')>='" +
startTime + "' and to_char(r.recorddate,'yyyy-MM-dd')<='" + endTime +
"' group by r.device_type_name ";
paraMap.clear();
List<Object[]> alarmList = (List<Object[]>) getSession().createSQLQuery(sql1).setProperties(paraMap).list();
for (int i = 0; i < alarmList.size(); i++) {
WeekDTO weekDTO = new WeekDTO();
weekDTO.setDevType(alarmList.get(i)[1].toString());
weekDTO.setAlarms(alarmList.get(i)[0].toString());
alarmDTOList.add(weekDTO);
}
String sql2 = "select factory,device_type_name as devType,1 as places from (select t1.factory,t0.device_type_name from (select r.device_id,r.device_type_name\n" +
" from alarm_alarm_record r\n" +
" where r.eventid is not null\n" +
" and to_char(r.recorddate, 'yyyy-MM-dd') >= '" + startTime +
"' and to_char(r.recorddate, 'yyyy-MM-dd') <= '" + endTime + "') t0 left join alarm_device t1" +
" on t0.device_id = t1.dbid)tt group by tt.device_type_name,tt.factory ";
paraMap.clear();
List<Object[]> placesList = (List<Object[]>) getSession().createSQLQuery(sql2).setProperties(paraMap).list();
for (int i = 0; i < placesList.size(); i++) {
WeekDTO weekDTO = new WeekDTO();
weekDTO.setDevType(placesList.get(i)[1].toString());
weekDTO.setPlaces(placesList.get(i)[2].toString());
placesDTOList.add(weekDTO);
}
if (alarmDTOList.size() > 0) {
for (WeekDTO weekDTO : weekDTOList) {
for (WeekDTO alarmDTO : alarmDTOList) {
if (weekDTO.getDevType().equals(alarmDTO.getDevType())) {
weekDTO.setAlarms(alarmDTO.getAlarms());
}
}
}
}
if (placesDTOList.size() > 0) {
for (WeekDTO weekDTO : weekDTOList) {
for (WeekDTO placeDTO : placesDTOList) {
if (weekDTO.getDevType().equals(placeDTO.getDevType())) {
weekDTO.setPlaces((Integer.valueOf(weekDTO.getPlaces()) + 1) + "");
}
}
}
}
for (WeekDTO weekDTO : weekDTOList) {
weekDTO.setAlarmRa(String.format("%.2f", Double.valueOf(weekDTO.getAlarms()) * 100 / Double.valueOf(weekDTO.getInNum())) + "%");
}
//设备报警信息
List<WeekAlarmDTO> weekAlarmDTOS = new ArrayList<>();
String sqlalarm = "select t0.device_type_name as typeName, to_char(t0.recorddate, 'yyyy-MM-dd hh24:mi:ss') as time ,a.factory from (select r.device_type_name,r.recorddate,r.device_id from alarm_alarm_record r where r.eventid is not null and r.recordcode=0\n" +
" and to_char(r.recorddate, 'yyyy-MM-dd') >= '" + startTime +
"' and to_char(r.recorddate, 'yyyy-MM-dd') <= '" + endTime + "')t0 left join alarm_device a on t0.device_id= a.dbid order by t0.recorddate desc ";
paraMap.clear();
List<Object[]> alarmObjList = (List<Object[]>) getSession().createSQLQuery(sqlalarm).setProperties(paraMap).list();
for (int i = 0; i < alarmObjList.size(); i++) {
WeekAlarmDTO weekAlarmDTO = new WeekAlarmDTO();
weekAlarmDTO.setAlarmNo((i + 1) + "");
weekAlarmDTO.setDevType(alarmObjList.get(i)[0].toString());
weekAlarmDTO.setAlarmMsg("监测值报警");
weekAlarmDTO.setPlace(alarmObjList.get(i)[2].toString());
weekAlarmDTO.setResult("处置中");
weekAlarmDTO.setFactory(alarmObjList.get(i)[2].toString());
weekAlarmDTO.setTime(alarmObjList.get(i)[1].toString());
weekAlarmDTOS.add(weekAlarmDTO);
}
//工程进度信息
String sqlproject = "select a.typename,t0.addNum from (select t.devicetype_id,count(*) as addNum from alarm_device t where t.active=1 and to_char(t.installdate,'yyyy-MM-dd')>='" + startTime
+ "' and to_char(t.installdate,'yyyy-MM-dd')<='" + endTime + "' " +
"group by t.devicetype_id)t0 left join alarm_device_type a on t0.devicetype_id= a.dbid";
paraMap.clear();
List<Object[]> projectObjList = (List<Object[]>) getSession().createSQLQuery(sqlproject).setProperties(paraMap).list();
for (int i = 0; i < projectObjList.size(); i++) {
ProjectWeekDTO projectWeekDTO = new ProjectWeekDTO();
projectWeekDTO.setDevType(projectObjList.get(i)[0].toString());
projectWeekDTO.setInNum(projectObjList.get(i)[1].toString());
projectDTOList.add(projectWeekDTO);
}
List<ProjectWeekDTO> projectWeekDTOList = new ArrayList<>();
for (WeekDTO weekDTO : weekDTOList) {
ProjectWeekDTO projectWeekDTO = new ProjectWeekDTO();
projectWeekDTO.setAlarmNo(weekDTO.getAlarmNo());
projectWeekDTO.setDevType(weekDTO.getDevType());
projectWeekDTO.setTotal(weekDTO.getTotal());
projectWeekDTO.setInNum("0");
for (ProjectWeekDTO projectWeekDTO1 : projectDTOList) {
if (weekDTO.getDevType().equals(projectWeekDTO1.getDevType())) {
projectWeekDTO.setInNum(projectWeekDTO1.getInNum());
}
}
projectWeekDTOList.add(projectWeekDTO);
}
String gwIndex=weekAlarmDTOS.size()<1?"100":((int) (90 + Math.random() * 11) + "");
HashMap<String, Object> dataMap = new HashMap<>();
dataMap.put("weekList", weekDTOList);
dataMap.put("alarmList", weekAlarmDTOS);
dataMap.put("projectList", projectWeekDTOList);
// dataMap.put("roadLength", "1500");
// dataMap.put("gasLength", "2300");
// dataMap.put("dlLength", "1800");
dataMap.put("begTime", startTime);
dataMap.put("endTime", endTime);
dataMap.put("gwIndex", gwIndex);
String template = "week".equals(sensorName) ? "weekTemp.ftl" : "monthTemp.ftl";
documentHandler.createDoc(dataMap, startTime + "~" + endTime + sensorName, template);
} catch (Exception e) {
e.printStackTrace();
}
return weekDTOList;
}
}
