<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.Situation4GMapper">
<!--查询 事件单和反馈单的结合-->
<select id="getEventList" resultType="java.util.Map">
select
a.SJDBH ,
XZQH ,
JJDWBH ,
SFDZ ,
DWBZW ,
XZB ,
YZB ,
BJR ,
LXDH ,
LXDZ ,
SSFJ ,
XQDW ,
CLLX ,
JQJB ,
AY ,
GLSJDBH ,
SJXQ ,
CJDW ,
CLJG ,
SJCLQK ,
SJZT ,
BJRXB ,
SFDZFL ,
jjygh ,
jjyxm ,
yywj ,
sjc ,
jjybh ,
sfsj ,
ljfknr ,
sffkzj ,
ywwxwz ,
ywbzxl ,
bkryqksm ,
ssryqksm ,
swryqksm ,
zjhm ,
jqlbdm ,
jqlxdm ,
jqxldm ,
jqjbdm ,
zdbz ,
ssxqdm ,
ssxqmc ,
szjqdm ,
szjqmc ,
jzwmc ,
sfdzflmc ,
jjth ,
jjtip ,
xqdwmc ,
ssfjmc ,
aymc ,
jqlbmc ,
jqlxmc ,
jqxlmc ,
scjjdwmc ,
scpjdwmc ,
scfkdwmc ,
jqbqid ,
jqbqmc ,
isHuangpu ,
lat02 ,
lng02 ,
number ,
office_name,
area_name ,
ZLJSSJ ,
fksjc ,
CJQK ,
FKRGH
from
(select
SJDBH ,
XZQH ,
JJDWBH ,
SFDZ ,
DWBZW ,
XZB ,
YZB ,
BJR ,
LXDH ,
LXDZ ,
SSFJ ,
XQDW ,
CLLX ,
JQJB ,
AY ,
GLSJDBH ,
SJXQ ,
CJDW ,
CLJG ,
SJCLQK ,
SJZT ,
BJRXB ,
SFDZFL ,
jjygh ,
jjyxm ,
yywj ,
sjc ,
jjybh ,
sfsj ,
ljfknr ,
sffkzj ,
ywwxwz ,
ywbzxl ,
bkryqksm ,
ssryqksm ,
swryqksm ,
zjhm ,
jqlbdm ,
jqlxdm ,
jqxldm ,
jqjbdm ,
zdbz ,
ssxqdm ,
ssxqmc ,
szjqdm ,
szjqmc ,
jzwmc ,
sfdzflmc ,
jjth ,
jjtip ,
xqdwmc ,
ssfjmc ,
aymc ,
jqlbmc ,
jqlxmc ,
jqxlmc ,
scjjdwmc ,
scpjdwmc ,
scfkdwmc ,
jqbqid ,
jqbqmc ,
isHuangpu ,
lat02 ,
lng02 ,
number ,
office_name,
area_name
from t_sjd
where sjc>= DATE_FORMAT(date_sub(NOW(),INTERVAL 1 DAY),'%Y-%m-%d')
and jqlbmc='诈骗' and SJXQ like '%反诈中心%') a
inner join
(select
SJDBH,
ZLJSSJ,
CJQK,
FKRGH,
max(sjc) fksjc
from t_fkd
where sjc>= DATE_FORMAT(date_sub(NOW(),INTERVAL 1 DAY),'%Y-%m-%d')
and FKRGH in (select user_code from ism_police_user where plate_code not in (4,5) and is_4G is not null)
GROUP BY SJDBH) b on a.SJDBH=b.SJDBH
</select>
<!--查询 4G执法记录仪-->
<select id="get4GRecordList" parameterType="map" resultType="java.util.Map">
select
case_occur_time,
case_end_time,
case_duration,
case_long_effect,
policeman_no,
policeman_name,
case_detail,
video_url,
audio_url
from recog_case_info
where /*case_occur_time>= DATE_FORMAT(date_sub(NOW(),INTERVAL 1 DAY),'%Y-%m-%d') and*/
policeman_no=#{policeNo}
and case_occur_time>=#{startTime}
and hour(case_occur_time)=hour(#{startTime})
</select>
<!--添加留言-->
<insert id="addSituation4G" parameterType="map">
insert into t_4gsituation(
SJDBH ,
XZQH ,
JJDWBH ,
SFDZ,
DWBZW,
XZB,
YZB,
BJR,
LXDH,
LXDZ,
SSFJ,
XQDW,
CLLX,
JQJB,
AY,
GLSJDBH,
SJXQ,
CJDW,
CLJG,
SJCLQK,
SJZT,
BJRXB,
SFDZFL,
jjygh,
jjyxm,
yywj,
sjc,
jjybh,
sfsj,
ljfknr,
sffkzj,
ywwxwz,
ywbzxl,
bkryqksm,
ssryqksm,
swryqksm,
zjhm,
jqlbdm,
jqlxdm,
jqxldm,
jqjbdm,
zdbz,
ssxqdm,
ssxqmc,
szjqdm,
szjqmc,
jzwmc,
sfdzflmc,
jjth,
jjtip,
xqdwmc,
ssfjmc,
aymc,
jqlbmc,
jqlxmc,
jqxlmc,
scjjdwmc,
scpjdwmc,
scfkdwmc,
jqbqid,
jqbqmc,
isHuangpu,
lat02,
lng02,
number,
office_name,
area_name,
fksjc,
ZLJSSJ,
CJQK,
case_detail,
case_occur_time,
case_end_time,
case_duration,
case_long_effect,
video_url,
audio_url,
policeman_no,
policeman_name)
values ( #{SJDBH},
#{XZQH},
#{JJDWBH},
#{SFDZ},
#{DWBZW},
#{XZB},
#{YZB},
#{BJR},
#{LXDH},
#{LXDZ},
#{SSFJ},
#{XQDW},
#{CLLX},
#{JQJB},
#{AY},
#{GLSJDBH},
#{SJXQ},
#{CJDW},
#{CLJG},
#{SJCLQK},
#{SJZT},
#{BJRXB},
#{SFDZFL},
#{jjygh},
#{jjyxm},
#{yywj},
#{sjc},
#{jjybh},
#{sfsj},
#{ljfknr},
#{sffkzj},
#{ywwxwz},
#{ywbzxl},
#{bkryqksm},
#{ssryqksm},
#{swryqksm},
#{zjhm},
#{jqlbdm},
#{jqlxdm},
#{jqxldm},
#{jqjbdm},
#{zdbz},
#{ssxqdm},
#{ssxqmc},
#{szjqdm},
#{szjqmc},
#{jzwmc},
#{sfdzflmc},
#{jjth},
#{jjtip},
#{xqdwmc},
#{ssfjmc},
#{aymc},
#{jqlbmc},
#{jqlxmc},
#{jqxlmc},
#{scjjdwmc},
#{scpjdwmc},
#{scfkdwmc},
#{jqbqid},
#{jqbqmc},
#{isHuangpu},
#{lat02},
#{lng02},
#{number},
#{office_name},
#{area_name},
#{fksjc},
#{ZLJSSJ},
#{CJQK},
#{caseDetail},
#{caseOccurTime},
#{caseEndTime},
#{caseDuration},
#{caseLongEffect},
#{videoUrl},
#{audioUrl},
#{policemanNo},
#{policemanName})
ON DUPLICATE KEY UPDATE
XZQH=#{XZQH},
JJDWBH=#{JJDWBH} ,
SFDZ=#{SFDZ},
DWBZW=#{DWBZW},
XZB=#{XZB},
YZB=#{YZB},
BJR=#{BJR},
LXDH=#{LXDH},
LXDZ=#{LXDZ},
SSFJ=#{SSFJ},
XQDW=#{XQDW},
CLLX=#{CLLX},
JQJB=#{JQJB},
AY=#{AY},
GLSJDBH=#{GLSJDBH},
SJXQ=#{SJXQ},
CJDW=#{CJDW},
CLJG=#{CLJG},
SJCLQK=#{SJCLQK},
SJZT=#{SJZT},
BJRXB=#{BJRXB},
SFDZFL=#{SFDZFL},
jjygh=#{jjygh},
jjyxm=#{jjyxm},
yywj=#{yywj},
sjc=#{sjc},
jjybh=#{jjybh},
sfsj=#{sfsj},
ljfknr=#{ljfknr},
sffkzj=#{sffkzj},
ywwxwz=#{ywwxwz},
ywbzxl=#{ywbzxl},
bkryqksm=#{bkryqksm},
ssryqksm=#{ssryqksm},
swryqksm=#{swryqksm},
zjhm=#{zjhm},
jqlbdm=#{jqlbdm},
jqlxdm=#{jqlxdm},
jqxldm=#{jqxldm},
jqjbdm= #{jqjbdm},
zdbz=#{zdbz},
ssxqdm=#{ssxqdm},
ssxqmc=#{ssxqmc},
szjqdm=#{szjqdm},
szjqmc=#{szjqmc},
jzwmc=#{jzwmc},
sfdzflmc=#{sfdzflmc},
jjth=#{jjth},
jjtip=#{jjtip},
xqdwmc=#{xqdwmc},
ssfjmc=#{ssfjmc},
aymc=#{aymc},
jqlbmc=#{jqlbmc},
jqlxmc=#{jqlxmc},
jqxlmc=#{jqxlmc},
scjjdwmc=#{scjjdwmc},
scpjdwmc=#{scpjdwmc},
scfkdwmc=#{scfkdwmc},
jqbqid=#{jqbqid},
jqbqmc=#{jqbqmc},
isHuangpu=#{isHuangpu},
lat02=#{lat02},
lng02=#{lng02},
number=#{number},
office_name=#{office_name},
area_name=#{area_name},
fksjc=#{fksjc},
ZLJSSJ=#{ZLJSSJ},
CJQK=#{CJQK},
case_detail=#{caseDetail},
case_occur_time=#{caseOccurTime},
case_end_time=#{caseEndTime},
case_duration=#{caseDuration},
case_long_effect=#{caseLongEffect},
video_url=#{videoUrl},
audio_url=#{audioUrl},
policeman_no=#{policemanNo},
policeman_name=#{policemanName};
</insert>
</mapper>
Mapper
package com.ruoyi.system.mapper;
import java.util.List;
import java.util.Map;
public interface Situation4GMapper {
/*
* 获得事件单和反馈单的结合*/
public List<Map<String, Object>> getEventList();
/*
* 获得事件单和反馈单的结合*/
public List<Map<String, Object>> get4GRecordList(Map<String, Object> map);
/*
* 将数据写入表中*/
public void addSituation4G(Map<String, Object> map);
}
Service
package com.ruoyi.system.service;
import java.text.ParseException;
public interface Situation4GService {
/**
* 事件单和反馈单的结合 再结合记录仪合并的数据
*/
public void get4GSituation() throws ParseException;
}
ServiceImpl(主要逻辑)
package com.ruoyi.system.service.impl;
import com.ruoyi.system.mapper.Situation4GMapper;
import com.ruoyi.system.service.Situation4GService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@EnableScheduling
@Service
public class Situation4GServiceImpl implements Situation4GService {
@Autowired
private Situation4GMapper situation4GMapper;
@Override
@Scheduled(cron = "5 * * * * ?")
public void get4GSituation() throws ParseException {
//取出第一个接口的数据,当作参数放入map调用第二个接口
Map<String, Object> paramMap = new HashMap<>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<Map<String, Object>> eventList = situation4GMapper.getEventList();
//先对事件单进行循环
for (Map<String, Object> map:eventList){
//根据员工号 和时间去调用第二个接口,会返回多条数据
paramMap.put("policeNo",map.get("FKRGH"));
paramMap.put("startTime",map.get("fksjc"));
List<Map<String, Object>> gRecordList = situation4GMapper.get4GRecordList(paramMap);
//合并记录仪的相关数据为一条数据
Map<String, Object> resultMap=new HashMap<String, Object>();
//字符串的就追加
StringBuffer strBuffer = new StringBuffer();
StringBuffer videoBuffeer = new StringBuffer();
StringBuffer radioBuffeer = new StringBuffer();
//时间的比较 取最新的或者最早的
Date startDate = sdf.parse("9999-01-01 00:00:00");
Date endDate = sdf.parse("0000-11-27 00:00:00");
//持续时间的累加
int caseDuration=0;
int caseLongEffect=0;
//相同的字符串选取
String policemanName=null;
String policemanNo=null;
int times=0;
//将记录仪中的数据根据事件单合并成一条
for (Map<String, Object> map1:gRecordList){
strBuffer.append(map1.get("case_detail")).append(",");
videoBuffeer.append(map1.get("video_url")).append(",");
radioBuffeer.append(map1.get("audio_url")).append(",");
//开始时间比较 发生时间取最新的
Date case_occur_time = sdf.parse(map1.get("case_occur_time").toString());
if (case_occur_time.before(startDate)){
startDate=case_occur_time;
}
//结束时间比较 结束时间取最后的
Date case_end_time = sdf.parse( map1.get("case_end_time").toString());
if (case_end_time.after(endDate)){
endDate=case_end_time;
}
//持续时间累加
caseDuration += (Integer) map1.get("case_duration");
caseLongEffect += (Integer) map1.get("case_long_effect");
if (times==gRecordList.size()-1){
policemanNo=map1.get("policeman_no").toString();
policemanName=map1.get("policeman_name").toString();
}
times++;
}
//删除掉最后一个逗号(不为空的情况下,否则数组越界) case_detail合并
String caseDetail =null;
String videoUrl = null;
String audioUrl = null;
if(strBuffer.length()>0) {
caseDetail = strBuffer.deleteCharAt(strBuffer.length() - 1).toString();
}
if (videoBuffeer.length()>0) {
videoUrl = videoBuffeer.deleteCharAt(videoBuffeer.length() - 1).toString();
}
if(radioBuffeer.length()>0) {
audioUrl = radioBuffeer.deleteCharAt(radioBuffeer.length() - 1).toString();
}
resultMap.put("caseDetail",caseDetail);
//格林威志时间标准化 startDate endDate
resultMap.put("caseOccurTime",sdf.format(startDate));
resultMap.put("caseEndTime",sdf.format(endDate));
resultMap.put("caseDuration",caseDuration);
resultMap.put("caseLongEffect",caseLongEffect);
resultMap.put("videoUrl",videoUrl);
resultMap.put("audioUrl",audioUrl);
resultMap.put("policemanNo",policemanNo);
resultMap.put("policemanName",policemanName);
//合并两个map
Map<String, Object> combineResultMap = new HashMap<String, Object>();
combineResultMap.putAll(map);
combineResultMap.putAll(resultMap);
System.out.println(combineResultMap);
//每条事件单写入Situation4G表
situation4GMapper.addSituation4G(combineResultMap);
}
}
}