From cc3a57f2a466a76a808b8171fdcfa5d10263e4eb Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Tue, 18 Jan 2022 14:42:50 +0800
Subject: [PATCH] 1.考试查询修改 2.新增登录情况导出 3.题库查询修改
---
src/main/java/org/springblade/modules/loginrecord/service/LoginRecordService.java | 10 ++
src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.xml | 66 ++++++++++++++++
src/main/java/org/springblade/modules/loginrecord/controller/LoginRecordController.java | 16 ++++
src/main/java/org/springblade/modules/loginrecord/excel/LoginRecordExcel.java | 60 +++++++++++++++
src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.java | 7 +
src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml | 8 +
src/main/java/org/springblade/modules/exam/mapper/ExamSubjectChoicesMapper.xml | 7 +
src/main/java/org/springblade/modules/loginrecord/service/impl/LoginRecordServiceImpl.java | 12 +++
src/main/java/org/springblade/modules/exam/vo/ExamPaperVO.java | 5 +
9 files changed, 185 insertions(+), 6 deletions(-)
diff --git a/src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml b/src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml
index 790c29e..6478a01 100644
--- a/src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml
+++ b/src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml
@@ -130,10 +130,16 @@
<if test="paper.deptId!=null and paper.deptId!=''">
and bd.id = #{paper.deptId}
</if>
+ <if test="paper.deptName!=null and paper.deptName!=''">
+ and bd.dept_name like concat('%',#{paper.deptName},'%')
+ </if>
+ <if test="paper.examTime!=null and paper.examTime!=''">
+ and date_format(ke.start_time,'%Y-%m-%d') = #{paper.examTime}
+ </if>
<if test="paper.auditStatus!=null and paper.auditStatus!=''">
and audit_status = #{paper.auditStatus}
</if>
- order by start_time desc
+ order by ke.start_time desc,ke.id desc
</select>
<select id="PagerSubject" resultMap="PagerSubjectAnswer">
diff --git a/src/main/java/org/springblade/modules/exam/mapper/ExamSubjectChoicesMapper.xml b/src/main/java/org/springblade/modules/exam/mapper/ExamSubjectChoicesMapper.xml
index b126c16..17f6af0 100644
--- a/src/main/java/org/springblade/modules/exam/mapper/ExamSubjectChoicesMapper.xml
+++ b/src/main/java/org/springblade/modules/exam/mapper/ExamSubjectChoicesMapper.xml
@@ -13,9 +13,10 @@
<if test="examSubjectChoices.subjectName!=null and examSubjectChoices.subjectName!=''">
and esc.subject_name like concat('%', #{examSubjectChoices.subjectName},'%')
</if>
- <if test="examSubjectChoices.choicesType!=null and examSubjectChoices.choicesType!=''">
+ <if test="examSubjectChoices.choicesType!=null">
and choices_type = #{examSubjectChoices.choicesType}
</if>
+ order by id desc
</select>
<!--查询试卷绑定的题目-->
@@ -33,8 +34,8 @@
<if test="examSubjectChoices.subjectName!=null and examSubjectChoices.subjectName!=''">
and esc.subject_name like concat('%', #{examSubjectChoices.subjectName},'%')
</if>
- <if test="examSubjectChoices.choicesType!=null and examSubjectChoices.choicesType!=''">
- and esc.choices_type = #{examSubjectChoices.choices_type}
+ <if test="examSubjectChoices.choicesType!=null">
+ and esc.choices_type = #{examSubjectChoices.choicesType}
</if>
</select>
diff --git a/src/main/java/org/springblade/modules/exam/vo/ExamPaperVO.java b/src/main/java/org/springblade/modules/exam/vo/ExamPaperVO.java
index f3634a4..15209ff 100644
--- a/src/main/java/org/springblade/modules/exam/vo/ExamPaperVO.java
+++ b/src/main/java/org/springblade/modules/exam/vo/ExamPaperVO.java
@@ -89,4 +89,9 @@
* 用户id
*/
private String userId;
+
+ /**
+ * 考试时间
+ */
+ private String examTime;
}
diff --git a/src/main/java/org/springblade/modules/loginrecord/controller/LoginRecordController.java b/src/main/java/org/springblade/modules/loginrecord/controller/LoginRecordController.java
index 3761f43..575d353 100644
--- a/src/main/java/org/springblade/modules/loginrecord/controller/LoginRecordController.java
+++ b/src/main/java/org/springblade/modules/loginrecord/controller/LoginRecordController.java
@@ -4,14 +4,20 @@
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
+import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
+import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.loginrecord.entity.LoginRecord;
+import org.springblade.modules.loginrecord.excel.LoginRecordExcel;
import org.springblade.modules.loginrecord.service.LoginRecordService;
import org.springblade.modules.loginrecord.vo.LoginRecordVo;
import org.springframework.web.bind.annotation.*;
+
+import javax.servlet.http.HttpServletResponse;
+import java.util.List;
/**
* @author zhongrj
@@ -112,4 +118,14 @@
}
+ /**
+ * 企业登录记录导出
+ * @param response
+ * @param loginRecord
+ */
+ @GetMapping("/export-login-record")
+ public void exportLoginRecord(HttpServletResponse response, LoginRecordVo loginRecord){
+ List<LoginRecordExcel> list = loginRecordService.exportLoginRecord(loginRecord);
+ ExcelUtil.export(response, "企业登录记录数据" + DateUtil.time(), "企业登录记录数据表", list, LoginRecordExcel.class);
+ }
}
diff --git a/src/main/java/org/springblade/modules/loginrecord/excel/LoginRecordExcel.java b/src/main/java/org/springblade/modules/loginrecord/excel/LoginRecordExcel.java
new file mode 100644
index 0000000..180faba
--- /dev/null
+++ b/src/main/java/org/springblade/modules/loginrecord/excel/LoginRecordExcel.java
@@ -0,0 +1,60 @@
+/*
+ * Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ *
+ * Redistributions of source code must retain the above copyright notice,
+ * this list of conditions and the following disclaimer.
+ * Redistributions in binary form must reproduce the above copyright
+ * notice, this list of conditions and the following disclaimer in the
+ * documentation and/or other materials provided with the distribution.
+ * Neither the name of the dreamlu.net developer nor the names of its
+ * contributors may be used to endorse or promote products derived from
+ * this software without specific prior written permission.
+ * Author: Chill 庄骞 (smallchill@163.com)
+ */
+package org.springblade.modules.loginrecord.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import lombok.Data;
+
+import java.io.Serializable;
+
+/**
+ * 登录记录导出
+ * @author zhongrj
+ * @since 2022-01-18
+ */
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class LoginRecordExcel implements Serializable {
+ private static final long serialVersionUID = 1L;
+
+ @ColumnWidth(30)
+ @ExcelProperty("企业名称")
+ private String deptName;
+
+ @ColumnWidth(15)
+ @ExcelProperty("企业类型")
+ private String stats;
+
+ @ColumnWidth(15)
+ @ExcelProperty("所属辖区")
+ private String jurisdictionName;
+
+
+ @ColumnWidth(20)
+ @ExcelProperty("最近一次登录时间")
+ private String createTime;
+
+ @ColumnWidth(15)
+ @ExcelProperty("登录次数")
+ private Integer num;
+
+}
diff --git a/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.java b/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.java
index 4974c4a..58b2401 100644
--- a/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.java
+++ b/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.java
@@ -4,6 +4,7 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.loginrecord.entity.LoginRecord;
+import org.springblade.modules.loginrecord.excel.LoginRecordExcel;
import org.springblade.modules.loginrecord.vo.LoginRecordVo;
import java.util.List;
@@ -32,4 +33,10 @@
* @return
*/
List<LoginRecordVo> getInformationLoginPage(@Param("page") IPage<LoginRecordVo> page,@Param("loginRecord") LoginRecordVo loginRecord);
+
+ /**
+ * 企业登录记录导出
+ * @param loginRecord
+ */
+ List<LoginRecordExcel> exportLoginRecord(@Param("loginRecord") LoginRecordVo loginRecord);
}
diff --git a/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.xml b/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.xml
index 3a8bf07..7fdfabb 100644
--- a/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.xml
+++ b/src/main/java/org/springblade/modules/loginrecord/mapper/LoginRecordMapper.xml
@@ -46,7 +46,7 @@
LEFT JOIN
(
SELECT count( * ) c, dept_id FROM `sys_login_record`
- where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
+ where DATE_SUB(CURDATE(), INTERVAL 14 DAY) <= date(create_time)
GROUP BY dept_id
) b ON a.departmentid = b.dept_id
) c
@@ -78,7 +78,7 @@
LEFT JOIN
(
SELECT count( * ) c, dept_id FROM `sys_login_record`
- where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
+ where DATE_SUB(CURDATE(), INTERVAL 14 DAY) <= date(create_time)
GROUP BY dept_id
) b ON a.departmentid = b.dept_id
) c
@@ -139,4 +139,66 @@
and r.num > 0
</if>
</select>
+
+
+ <!--企业登录记录导出-->
+ <select id="exportLoginRecord" resultType="org.springblade.modules.loginrecord.excel.LoginRecordExcel">
+ select * from (
+ SELECT
+ a.enterpriseName deptName,
+ case when a.stats=0 then '自招保安单位'
+ when a.stats=2 then '本市保安公司'
+ when a.stats=4 then '分公司'
+ when a.stats=1 then '保安培训学校'
+ else "其他" end as stats,
+ sj.dept_name jurisdictionName,
+ ifnull( b.c, 0 ) num,
+ c.create_time createTime
+ FROM
+ (
+ SELECT
+ si.*
+ FROM
+ sys_information si
+ LEFT JOIN blade_dept bd ON si.departmentid = bd.id
+ WHERE
+ bd.is_deleted = 0
+ AND bd.dept_category = 1
+ ) a
+ left join
+ (
+ SELECT count( * ) c, dept_id FROM `sys_login_record`
+ where 1=1
+ and type = 1
+ <if test="loginRecord.startTime!=null and loginRecord.startTime!='' and loginRecord.startTime!='undefined'">
+ and date(create_time) >= #{loginRecord.startTime}
+ </if>
+ <if test="loginRecord.endTime!=null and loginRecord.endTime!='' and loginRecord.endTime!='undefined'">
+ and date(create_time) <= #{loginRecord.endTime}
+ </if>
+ GROUP BY dept_id
+ ) b ON a.departmentid = b.dept_id
+ left join
+ (
+ select dept_id,max(create_time) create_time from sys_login_record GROUP BY dept_id
+ ) c on a.departmentid = c.dept_id
+ left join sys_jurisdiction sj on a.jurisdiction = sj.id
+ where 1=1
+ <if test="loginRecord.jurisdiction!=null and loginRecord.jurisdiction!='' and loginRecord.jurisdiction!='1372091709474910209'">
+ and (sj.id = #{loginRecord.jurisdiction} or sj.parent_id = #{loginRecord.jurisdiction})
+ </if>
+ <if test="loginRecord.deptName!=null and loginRecord.deptName!=''">
+ and a.enterpriseName like concat('%',#{loginRecord.deptName},'%')
+ </if>
+ <if test="loginRecord.stats!=null and loginRecord.stats!=''">
+ and a.stats = #{loginRecord.stats}
+ </if>
+ ) r where 1=1
+ <if test="loginRecord.types ==1">
+ and r.num = 0
+ </if>
+ <if test="loginRecord.types ==2">
+ and r.num > 0
+ </if>
+ </select>
</mapper>
diff --git a/src/main/java/org/springblade/modules/loginrecord/service/LoginRecordService.java b/src/main/java/org/springblade/modules/loginrecord/service/LoginRecordService.java
index c13a8b4..8480539 100644
--- a/src/main/java/org/springblade/modules/loginrecord/service/LoginRecordService.java
+++ b/src/main/java/org/springblade/modules/loginrecord/service/LoginRecordService.java
@@ -3,7 +3,10 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.loginrecord.entity.LoginRecord;
+import org.springblade.modules.loginrecord.excel.LoginRecordExcel;
import org.springblade.modules.loginrecord.vo.LoginRecordVo;
+
+import java.util.List;
/**
* 登录记录服务类
@@ -31,4 +34,11 @@
* @return
*/
IPage<LoginRecordVo> getInformationLoginPage(IPage<LoginRecordVo> page, LoginRecordVo loginRecord);
+
+
+ /**
+ * 企业登录记录导出
+ * @param loginRecord
+ */
+ List<LoginRecordExcel> exportLoginRecord(LoginRecordVo loginRecord);
}
diff --git a/src/main/java/org/springblade/modules/loginrecord/service/impl/LoginRecordServiceImpl.java b/src/main/java/org/springblade/modules/loginrecord/service/impl/LoginRecordServiceImpl.java
index fe67b12..fa125e2 100644
--- a/src/main/java/org/springblade/modules/loginrecord/service/impl/LoginRecordServiceImpl.java
+++ b/src/main/java/org/springblade/modules/loginrecord/service/impl/LoginRecordServiceImpl.java
@@ -5,10 +5,13 @@
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.AllArgsConstructor;
import org.springblade.modules.loginrecord.entity.LoginRecord;
+import org.springblade.modules.loginrecord.excel.LoginRecordExcel;
import org.springblade.modules.loginrecord.mapper.LoginRecordMapper;
import org.springblade.modules.loginrecord.service.LoginRecordService;
import org.springblade.modules.loginrecord.vo.LoginRecordVo;
import org.springframework.stereotype.Service;
+
+import java.util.List;
/**
* 保安员证管理服务实现类
@@ -50,4 +53,13 @@
public IPage<LoginRecordVo> getInformationLoginPage(IPage<LoginRecordVo> page, LoginRecordVo loginRecord) {
return page.setRecords(baseMapper.getInformationLoginPage(page,loginRecord));
}
+
+ /**
+ * 企业登录记录导出
+ * @param loginRecord
+ */
+ @Override
+ public List<LoginRecordExcel> exportLoginRecord(LoginRecordVo loginRecord) {
+ return baseMapper.exportLoginRecord(loginRecord);
+ }
}
--
Gitblit v1.9.3