统计一段时间内，人员在线数量
1.表格
（1）人员表
CREATE TABLE IF NOT EXISTS public.fd_operatives
(
pid integer NOT NULL DEFAULT nextval('fd_operatives_pid_seq'::regclass),
name character varying(16) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
sex smallint NOT NULL DEFAULT 0,
phone character varying COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
email character varying COLLATE pg_catalog."default" DEFAULT ''::character varying,
remark character varying COLLATE pg_catalog."default" DEFAULT ''::character varying,
createuser character varying COLLATE pg_catalog."default" DEFAULT ''::character varying,
createtime timestamp without time zone NOT NULL DEFAULT now(),
status character(1) COLLATE pg_catalog."default"
CONSTRAINT fd_operatives_pidkey PRIMARY KEY (pid)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.fd_operatives
OWNER to postgres;
COMMENT ON TABLE public.fd_operatives
IS '作业人员';
COMMENT ON COLUMN public.fd_operatives.name
IS '名称';
COMMENT ON COLUMN public.fd_operatives.sex
IS '性别(radio:1.男;2.女)';
COMMENT ON COLUMN public.fd_operatives.phone
IS '手机号';
COMMENT ON COLUMN public.fd_operatives.email
IS '邮箱';
COMMENT ON COLUMN public.fd_operatives.remark
IS '备注';
COMMENT ON COLUMN public.fd_operatives.createuser
IS '创建人';
COMMENT ON COLUMN public.fd_operatives.createtime
IS '创建时间';
COMMENT ON COLUMN public.fd_operatives.status
IS '打卡状态：1 上船 0 离船';
（2）打卡记录表
CREATE TABLE IF NOT EXISTS public.fd_operatives_sign
(
pid integer NOT NULL DEFAULT nextval('fd_operatives_sign_pid_seq'::regclass),
userid integer NOT NULL,
createtime timestamp without time zone NOT NULL DEFAULT now(),
out_in_type integer,
index_code character varying(32) COLLATE pg_catalog."default",
CONSTRAINT fd_operatives_sign_pidkey PRIMARY KEY (pid)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.fd_operatives_sign
OWNER to postgres;
COMMENT ON TABLE public.fd_operatives_sign
IS '作业人员签到';
COMMENT ON COLUMN public.fd_operatives_sign.userid
IS '作业人员的pid';
COMMENT ON COLUMN public.fd_operatives_sign.createtime
IS '打卡时间';
COMMENT ON COLUMN public.fd_operatives_sign.out_in_type
IS '进出类型(1：进 0：出 -1:未知)';
COMMENT ON COLUMN public.fd_operatives_sign.index_code
IS '门禁唯一编码';
2.需求
（1）查询当前时间 和 过去6天，总计7天，这段时间内，每天人员的累计数量
（2）若有打卡记录，则计数
（3）员工可能连续工作，非每天打卡，只要打了上班卡，未打下班卡，则视为 打卡时间 到 目前为止该员工都在线，跨越的时间都需要计数
3.实现
实现类种的方法
@Override
public List<FdCountConstructionData> countOnlineOperativesWeek(FdCountOperativesSignVo vo, HttpServletRequest request) {
Date dateYMR = TimeUtils.getDateYMR();
Date startTime = TimeUtils.addDate(dateYMR, -6);
Date endTime = TimeUtils.addDate(dateYMR, 1);
List<String> dateList = TimeUtils.getTimeIntervalStrList(TimeUtils.toLocalDateTime(startTime),TimeUtils.toLocalDateTime(endTime),1,"day");
for (int i = 0; i < dateList.size(); i++) {
String s = dateList.get(i);
dateList.set(i,s.split(" ")[0]);
}
//去掉最后一个日期，最后一个日期为当天+1
dateList.remove(dateList.size()-1);
Map<String, Set<Integer>> map = new HashMap<>(dateList.size());
for (String s : dateList) {
map.put(s,new HashSet<>());
}
//打卡记录
vo.setStartTime(startTime);
vo.setEndTime(endTime);
List<OperativesSignByStatisticsDto> dtos = this.getBaseMapper().selectOperativesSignByStatistics(vo);
Map<Integer, List<OperativesSignByStatisticsDto>> map_dtoId = dtos.stream().collect(Collectors.groupingBy(a -> a.getUserid()));
Map<String, List<OperativesSignByStatisticsDto>> map_dtoDate = dtos.stream().collect(Collectors.groupingBy(a -> a.getDate()));
for (int i = 0; i < dateList.size(); i++) {
String s = dateList.get(i);
Set<Integer> set = map.get(s);
//若当天有打卡记录，则当天在线
List<OperativesSignByStatisticsDto> operativesSignByStatisticsDtos = map_dtoDate.get(s);
if (null == operativesSignByStatisticsDtos){
continue;
}
Map<Integer, List<OperativesSignByStatisticsDto>> collect = operativesSignByStatisticsDtos.stream().collect(Collectors.groupingBy(a -> a.getUserid()));
for (Integer id : collect.keySet()) {
set.add(id);
//若当天的最后一次打卡记录为 进入，则到下一次最近的一次 打卡记录，视为这段时间每天在线
//若当天的最后一次打卡记录为 进入，且没有下次 打卡记录为 出，则视为该时间到目前位置，该人员每天在线
OperativesSignByStatisticsDto dto = collect.get(id).stream().max(Comparator.comparing(OperativesSignByStatisticsDto::getCreatetime)).get();
if (null != dto.getOutInType() && dto.getOutInType() == 1){
//是否到今天为止都是在线
Date startTime_zx = dto.getCreatetime();
Date endTime_zx = null;
if (i == dateList.size()-1){
}else {
Boolean wc = false;
for (int j = i+1; j <dateList.size(); j++) {
List<OperativesSignByStatisticsDto> os = map_dtoDate.get(dateList.get(j));
if (null == os){
continue;
}
Map<Integer, List<OperativesSignByStatisticsDto>> collect1 = os.stream().collect(Collectors.groupingBy(a -> a.getUserid()));
List<OperativesSignByStatisticsDto> jls = collect1.get(id);
if (null == jls){
continue;
}else {
List<OperativesSignByStatisticsDto> collect2 = jls.stream().sorted(Comparator.comparing(OperativesSignByStatisticsDto::getCreatetime)).collect(Collectors.toList());
for (OperativesSignByStatisticsDto jl : collect2) {
if (jl.getOutInType() == 0){
endTime_zx = jl.getCreatetime();
wc = true;
break;
}
}
}
if (wc){
break;
}
}
}
if (null == endTime_zx){
endTime_zx = new Date();
}
//连续在线所在区间
List<String> dateList1 = TimeUtils.getTimeIntervalStrList(TimeUtils.toLocalDateTime(startTime_zx),TimeUtils.toLocalDateTime(endTime_zx),1,"day");
for (String s1 : dateList1) {
map.get(s1.split(" ")[0]).add(id);
}
}
}
}
//查询范围以外的记录
List<OperativesSignByStatisticsDto> dtos_w = this.getBaseMapper().selectOperativesSignByStatisticsOut(vo);
for (OperativesSignByStatisticsDto d : dtos_w) {
List<OperativesSignByStatisticsDto> ds = map_dtoId.get(d.getUserid());
if (null == ds){
//这里说明在统计时间以前就已经在线，从未下线
for (String s : map.keySet()) {
map.get(s).add(d.getUserid());
}
}else {
//找到最小的打卡记录
OperativesSignByStatisticsDto min = ds.stream().min(Comparator.comparing(OperativesSignByStatisticsDto::getCreatetime)).get();
//从记录外的时间 到 最小的打卡记录 直接的时间视为在线
if (d.getCreatetime().getTime() < min.getCreatetime().getTime()){
List<String> day = TimeUtils.getTimeIntervalStrList(TimeUtils.toLocalDateTime(d.getCreatetime()), TimeUtils.toLocalDateTime(min.getCreatetime()), 1, "day");
for (String s : day) {
Set<Integer> set = map.get(s.split(" ")[0]);
if (null != set){
set.add(d.getUserid());
}
}
}
}
}
List<FdCountConstructionData> list = new ArrayList<>();
for (String s : dateList) {
FdCountConstructionData a = new FdCountConstructionData();
a.setTime(s);
a.setOperatives(map.get(s).size());
a.setOs(new ArrayList<>(map.get(s)));
list.add(a);
}
return list;
}
mapper中的查询语句
<select id="selectOperativesSignByStatistics"
resultType="com.maphao.windfarms.service.dto.ship.OperativesSignByStatisticsDto">
select TO_CHAR(a.createtime,'yyyy-MM-dd') date,a.userid,a.out_in_type as outInType,a.createtime,b.status
from fd_operatives_sign a
join fd_operatives b on a.userid = b.pid
where a.createtime >= #{startTime} and a.createtime < #{endTime}
</select>
<select id="selectOperativesSignByStatisticsOut"
resultType="com.maphao.windfarms.service.dto.ship.OperativesSignByStatisticsDto">
SELECT a.userid, TO_CHAR(a.createtime,'yyyy-MM-dd') date,a.createtime
FROM fd_operatives_sign a
INNER JOIN (
SELECT userid, MAX(createtime) AS max_createtime
FROM fd_operatives_sign
GROUP BY userid
) b ON a.userid = b.userid AND a.createtime = b.max_createtime
where a.out_in_type = 1 and a.createtime < #{startTime}
</select>
所用的实体类
@Data
public class FdCountOperativesSignVo {
/**
* 风电场fdid
*/
private String fdid;
/**
* 打卡时间
*/
private String punchInTime;
/**
* 统计类型
* 0 默认 所有时间点 统计规则为累计数据
* 1 最近的时间节点为当天，则改时间节点的数据为实时数据
* 例如 当天为 2024-05-16 14：00：00，
* 查询 2024-05-10 至 2024-05-16 数据时，2024-05-10 至 2024-05-15 数据为累计数据，2024-05-16 数据为实时数据
* 查询当天数据时，2024-05-16 00：00：00 至 2024-05-16 13：30：00 数据为 累计数据，2024-05-16 14：00：00 数据为实时数据，2024-05-16 14：30：00至 2024-05-16 23：30：00 数据为空（为发生时间节点数据为空）
*/
private Integer statisticsType = 0;
private Date startTime;
private Date endTime;
}
@Data
public class OperativesSignByStatisticsDto {
/**
* 人员id
*/
private Integer userid;
/**
* 人员实时状态
* 0 在船
* 1 离船
*/
private Integer status;
/**
* 打卡时间所在日期
* 年月日
*/
private String date;
/**
* 打卡时间
*/
private Date createtime;
/**
* 打卡类型
* 进出类型(1：进 0：出 -1:未知)
*/
private Integer outInType;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FdCountConstructionData implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "时间")
private String time;
@ApiModelProperty(value = "施工船数量")
private Integer constructionShip;
@ApiModelProperty(value = "作业人员数量")
private Integer operatives;
/**
* 在线人员 pid
*/
private List<Integer> os;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
