签到分为发布与签到两个部分
发布需要由飞讯注册用户发布
发布的实体的属性
private Integer Id;
public String userid;
public String signLeader;
public String signEvent;
public String signWord;
public String createTime;
public String expire;
签到不需要是飞讯用户,而是扫码签到,需要输入自己的名字进行数据录入
签到实体的属性
private Integer id;
private int signId;
private String name;
private String word;
private String signTime;
前端数据展示部分需要展示:总发布数、总签到人数.
public int[] getSignNum(){//0-launch,1-do
Connection c=null;
List<SignDo> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select count(*) count from signlaunch\n" +
"union all\n" +
"select count(*) count from signdo";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
rs.next();
int nums[]={0,0};
nums[0]=rs.getInt("count");
rs.next();
nums[1]=rs.getInt("count");
return nums;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
需要最近七天发布数与签到人数
//发布签到数七个长度的一维数组,0-最近第一天,6-最近第七天
public int[] GetSevenDaysSignLaunchNumber(){
Timestamp nowTime=new Timestamp(System.currentTimeMillis());
long sevenDaysAgo= nowTime.getTime()-7*24*62*62*1000;
Timestamp sevenDaysAgoTime=new Timestamp(sevenDaysAgo);
String time=(sevenDaysAgoTime.getYear()+1900)+"-"+(sevenDaysAgoTime.getMonth()+1)+"-"+(sevenDaysAgoTime.getDate());
Connection c=null;
try {
c=DBConnection.getConnection();
PreparedStatement ps = null;
String sql = "select * from signlaunch\n" +
"where createTime>\'"+time+"\'";
ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
int[] msgNums= {0,0,0,0,0,0,0};
while (rs.next()){
Timestamp curTime=Timestamp.valueOf(rs.getString("createTime"));
long timeMinus=nowTime.getTime()-curTime.getTime();
if(timeMinus>0&&timeMinus<1*24*62*62*1000){
msgNums[0]++;
}else if(timeMinus<2*24*62*62*1000){
msgNums[1]++;
}else if(timeMinus<3*24*62*62*1000){
msgNums[2]++;
}else if(timeMinus<4*24*62*62*1000){
msgNums[3]++;
}else if(timeMinus<5*24*62*62*1000){
msgNums[4]++;
}else if(timeMinus<6*24*62*62*1000){
msgNums[5]++;
}else if(timeMinus<7*24*62*62*1000){
msgNums[6]++;
}
}
return msgNums;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeConnection(c);
}
return null;
}
//签到人数七个长度的一维数组,0-最近第一天,6-最近第七天
public int[] GetSevenDaysSignDoNumber(){
Timestamp nowTime=new Timestamp(System.currentTimeMillis());
long sevenDaysAgo= nowTime.getTime()-7*24*62*62*1000;
Timestamp sevenDaysAgoTime=new Timestamp(sevenDaysAgo);
String time=(sevenDaysAgoTime.getYear()+1900)+"-"+(sevenDaysAgoTime.getMonth()+1)+"-"+(sevenDaysAgoTime.getDate());
Connection c=null;
try {
c=DBConnection.getConnection();
PreparedStatement ps = null;
String sql = "select * from signDo\n" +
"where signTime>\'"+time+"\'";
ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
int[] msgNums= {0,0,0,0,0,0,0};
while (rs.next()){
Timestamp curTime=Timestamp.valueOf(rs.getString("signTime"));
long timeMinus=nowTime.getTime()-curTime.getTime();
if(timeMinus>0&&timeMinus<1*24*62*62*1000){
msgNums[0]++;
}else if(timeMinus<2*24*62*62*1000){
msgNums[1]++;
}else if(timeMinus<3*24*62*62*1000){
msgNums[2]++;
}else if(timeMinus<4*24*62*62*1000){
msgNums[3]++;
}else if(timeMinus<5*24*62*62*1000){
msgNums[4]++;
}else if(timeMinus<6*24*62*62*1000){
msgNums[5]++;
}else if(timeMinus<7*24*62*62*1000){
msgNums[6]++;
}
}
return msgNums;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnection.closeConnection(c);
}
return null;
}
传送到前端
int signNums[]=signDao.getSignNum();
int recentSignNums[]=signDao.GetSevenDaysSignLaunchNumber();
int recentSignDoNums[]=signDao.GetSevenDaysSignDoNumber();
request.setAttribute("signNums",signNums);
request.setAttribute("recentSignNums",recentSignNums);
request.setAttribute("recentSignDoNums",recentSignDoNums);
Timestamp nowTime=new Timestamp(System.currentTimeMillis());
int days[]={0,0,0,0,0,0,0};//日期
for(int i=0;i<7;i++){
days[i]=new Timestamp(nowTime.getTime()-i*24*60*60*1000).getDate();
}
request.setAttribute("days",days);
request.getRequestDispatcher("/main/SignBrief.jsp").forward(request, response);
前端的数据展示
<div class="result-wrap">
<div class="layui-col-md4">
<div class="site-text layui-elem-quote">
共发布了${signNums[0]}个签到
</div>
<div id="LaunchNums" style="height: 400px"></div>
<script type="text/javascript">
var chart = Highcharts.chart('LaunchNums', {
title: {
text: '最近七天签到发布数'
},
credits: {
enabled:false,
},
subtitle: {
text: ''
},
xAxis: {
categories: ['${days[6]}日', '${days[5]}日', '${days[4]}日', '${days[3]}日', '${days[2]}日', '${days[1]}日', '${days[0]}日']
},
yAxis: {
title: {
text: '签到发布数'
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle'
},
plotOptions: {
series: {
label: {
connectorAllowed: false
},
}
},
series: [{
name: '',
data: [${recentSignNums[6]}
,${recentSignNums[5]}
,${recentSignNums[4]}
,${recentSignNums[3]}
,${recentSignNums[2]}
,${recentSignNums[1]}
,${recentSignNums[0]},]
}],
responsive: {
rules: [{
condition: {
maxWidth: 500
},
chartOptions: {
legend: {
layout: 'horizontal',
align: 'center',
verticalAlign: 'bottom'
}
}
}]
}
});
</script>
</div>
<div class="layui-col-md2" style="width: 150px"> </div>
<div class="layui-col-md4">
<div class="site-text layui-elem-quote">
共签到了${signNums[1]}人
</div>
<div id="DoNums" style="height: 400px"></div>
<script type="text/javascript">
var chart = Highcharts.chart('DoNums', {
title: {
text: '最近七天签到人数'
},
credits: {
enabled:false,
},
subtitle: {
text: ''
},
xAxis: {
categories: ['${days[6]}日', '${days[5]}日', '${days[4]}日', '${days[3]}日', '${days[2]}日', '${days[1]}日', '${days[0]}日']
},
yAxis: {
title: {
text: '签到人数'
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle'
},
plotOptions: {
series: {
label: {
connectorAllowed: false
},
}
},
series: [{
name: '',
data: [${recentSignDoNums[6]}
,${recentSignDoNums[5]}
,${recentSignDoNums[4]}
,${recentSignDoNums[3]}
,${recentSignDoNums[2]}
,${recentSignDoNums[1]}
,${recentSignDoNums[0]},]
}],
responsive: {
rules: [{
condition: {
maxWidth: 500
},
chartOptions: {
legend: {
layout: 'horizontal',
align: 'center',
verticalAlign: 'bottom'
}
}
}]
}
});
</script>
</div>
<div class="layui-col-md4">
<div id="container" style="height: 400px">
</div>
</div>
<div style="margin-top: 200px">
</div>
</div>
效果
签到发布的数据列表查询,使用List存储Sign对象
public List<Sign> getSignList(){
Connection c=null;
List<Sign> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signlaunch";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
Sign sign=new Sign();
sign.setId(rs.getInt("signId"));
sign.setUserid(rs.getString("userid"));
sign.setSignLeader(rs.getString("signLeader"));
sign.setSignEvent(rs.getString("signEvent"));
sign.setSignWord(rs.getString("signWord"));
sign.setCreateTime(rs.getTimestamp("createTime").toString());
sign.setExpire(rs.getString("expire"));
list.add(sign);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
传输到前端
List<Sign> signList=signDao.getSignList();
request.setAttribute("signList",signList);
request.getRequestDispatcher("/main/SignList.jsp").forward(request, response);
前端的数据呈现
<div style="margin-left: 200px;">
<div class="result-wrap">
<fieldset class="table-search-fieldset" style="color: #009688">
<legend>群组信息</legend>
<div style="margin: 10px 10px 10px 10px" id="btn">
<form class="layui-form layui-form-pane" action="">
<div class="layui-form-item">
<div class="layui-inline">
<label class="layui-form-label">搜索内容</label>
<div class="layui-input-inline">
<input class="layui-input" name="keyword" id="demoReload" autocomplete="off">
</div>
</div>
<div class="layui-inline">
<button type="button" class="layui-btn layui-btn-primary" lay-submit data-type="reload" lay-filter="data-search-btn" style="color:#428bca" onclick="find()"><i class="layui-icon"></i> 搜 索</button>
<input type="radio" name="type" value="user" title="发起人" checked>
<input type="radio" name="type" value="leader" title="领导人" >
<input type="radio" name="type" value="event" title="事件" >
</div>
</div>
</form>
</div>
</fieldset>
<table class="result-tab" width="100%" lay-filter="demo" id="groupTable">
<thead>
<tr align="left" >
<!-- <th class="tc" width="5%"><input class="allChoose" name="" type="checkbox"></th>
<th>排序</th> -->
<th lay-data="{field:'clientIP', width:150,sort: true}">发起人</th>
<th lay-data="{field:'groupId', width:200,sort: true}">领导人</th>
<th lay-data="{field:'operator_Account', width:150,sort: true}">事件</th>
<th lay-data="{field:'owner_Account', width:150,sort: true}">关键词</th>
<th lay-data="{field:'type', width:200,sort: true}">创建时间</th>
<th lay-data="{field:'expire', width:150,sort: true}">expire</th>
<th lay-data="{field:'name', width:150,sort: true}">操作</th>
</tr>
</thead>
<c:forEach items="${signList}" var="sign">
<tr style="display: block">
<td>${sign.userid}</td>
<td>${sign.signLeader}</td>
<td>${sign.signEvent}</td>
<td>${sign.signWord}</td>
<td>${sign.createTime}</td>
<td>${sign.expire}</td>
<td id="${sign.getId()}"></td>
<script type="text/javascript">
var url="${sign.getId()}";
var res=encodeURIComponent(url);
console.log(res);
document.getElementById("${sign.getId()}").innerHTML="<a href=/SignDetail?signId="+res+">查看</a>"
</script>
</tr>
</c:forEach>
</table>
<script>
var table = layui.table;
//转换静态表格
table.init('demo', {
height: 500 //设置高度
,limit: 10 //注意:请务必确保 limit 参数(默认:10)是与你服务端限定的数据条数一致
,page:true
});
</script>
</div>
</div>
前端对表格内容进行搜索
function find(){
var find=document.getElementsByName("type");
var data=document.getElementById("demoReload").value;
var list=-1;
for (var i=0;i<find.length;i++) {
if (find[i].checked&&data!="") {
list = i;
console.log(find[i].value);
var url = encodeURIComponent(data);
window.location.href = "/SignFind?find=" + find[i].value + "&data=" + url;
}
}
if(list==-1)window.alert("请选择搜索列或输入搜索内容")
}
后端接收到信息,调用对应的查询方法
String find=request.getParameter("find");
String data=request.getParameter("data");
System.out.println(find);
System.out.println(data);
switch (find){
case "user":slist= dao.getSignByUserid(data);break;
case "leader":slist=dao.getSignByLeader(data);break;
case "event":slist=dao.getSignByEvent(data);break;
default:break;
}
public List<Sign> getSignByUserid(String userid){
Connection c=null;
List<Sign> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signlaunch where userid like\'%"+userid+"%\'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
Sign sign=new Sign();
sign.setId(rs.getInt("signId"));
sign.setUserid(rs.getString("userid"));
sign.setSignLeader(rs.getString("signLeader"));
sign.setSignWord(rs.getString("signWord"));
sign.setSignEvent(rs.getString("signEvent"));
sign.setCreateTime(rs.getTimestamp("createTime").toString());
sign.setExpire(rs.getString("expire"));
list.add(sign);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
public List<Sign> getSignByLeader(String leader){
Connection c=null;
List<Sign> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signlaunch where signLeader like\'%"+leader+"%\'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
Sign sign=new Sign();
sign.setId(rs.getInt("signId"));
sign.setUserid(rs.getString("userid"));
sign.setSignLeader(rs.getString("signLeader"));
sign.setSignWord(rs.getString("signWord"));
sign.setSignEvent(rs.getString("signEvent"));
sign.setCreateTime(rs.getTimestamp("createTime").toString());
sign.setExpire(rs.getString("expire"));
list.add(sign);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
public List<Sign> getSignByEvent(String event){
Connection c=null;
List<Sign> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signlaunch where signEvent like\'%"+event+"%\'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
Sign sign=new Sign();
sign.setId(rs.getInt("signId"));
sign.setUserid(rs.getString("userid"));
sign.setSignLeader(rs.getString("signLeader"));
sign.setSignWord(rs.getString("signWord"));
sign.setSignEvent(rs.getString("signEvent"));
sign.setCreateTime(rs.getTimestamp("createTime").toString());
sign.setExpire(rs.getString("expire"));
list.add(sign);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
之后重定向到原界面
request.getRequestDispatcher("/main/SignList.jsp").forward(request, response);
搜索前
搜索后
对某个签到进行详细的查看,点击查看进入,需要展现签到主题,所有的签到人,签到时间,以及最早和最近的签到人
获取所有签到人列表,使用List存储SignDo对象
public List<SignDo> getSignDoList(String signId){
Connection c=null;
List<SignDo> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signdo where signId=\'"+signId+"\'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
SignDo signDo=new SignDo();
signDo.setSignId(rs.getInt("SignDoId"));
signDo.setName(rs.getString("name"));
signDo.setWord(rs.getString("signWord"));
signDo.setSignTime(rs.getTimestamp("signTime").toString());
list.add(signDo);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
获取最早与最近签到信息
public String[] GetRecentSignDoMsg(String signId){
Connection c=null;
List<SignDo> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signdo where signId=\'"+signId+"\' order by signTime ";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
rs.next();
String [] data={"","","",""};
data[0]=rs.getString("name");//第一个签到人
data[1]=rs.getTimestamp("signTime").toString();//第一个签到时间
while(rs.next()){
data[2]=rs.getString("name");//最后一个签到人
data[3]=rs.getTimestamp("signTime").toString();//最后一个签到时间
}
return data;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
前端传查看的签到id到后端,后端查询到对应Sign
public List<Sign> getSignById(int id){
Connection c=null;
List<Sign> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signlaunch where signId=\'"+id+"'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
Sign sign=new Sign();
sign.setId(rs.getInt("signId"));
sign.setUserid(rs.getString("userid"));
sign.setSignLeader(rs.getString("signLeader"));
sign.setSignWord(rs.getString("signWord"));
sign.setSignEvent(rs.getString("signEvent"));
sign.setCreateTime(rs.getTimestamp("createTime").toString());
sign.setExpire(rs.getString("expire"));
list.add(sign);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
传回到前端并重定向
String signId=request.getParameter("signId");
SignDao signDao=new SignDao();
List<SignDo> signDoList= signDao.getSignDoList(signId);
String[] data=signDao.GetRecentSignDoMsg(signId);
List<Sign> signList= signDao.getSignById(Integer.parseInt(signId));
request.setAttribute("signDoList",signDoList);
request.setAttribute("data",data);
request.setAttribute("Sign",signList.get(0));
request.getRequestDispatcher("/main/SignDetail.jsp").forward(request, response);
对签到人的搜索
需要传输签到id与搜索内容
function find(){
var find=document.getElementsByName("type");
var data=document.getElementById("demoReload").value;
var list=-1;
var groupid='${groupId}';
for (var i=0;i<find.length;i++) {
if (find[i].checked&&data!="") {
list = i;
console.log(find[i].value);
var url1 = encodeURIComponent(data);
var url2=encodeURIComponent(groupid);
window.location.href = "/SignUserFind?find=" + find[i].value
+ "&data=" + url1
+"&groupid="+url2
+"&signId=${Sign.getId()}"
;
}
}
if(list==-1)window.alert("请选择搜索列或输入搜索内容")
}
后端进行搜索
public List<SignDo> getSignDoListByUserid(String signId,String userid){
Connection c=null;
List<SignDo> list =new ArrayList<>();
try{
c=DBConnection.getConnection();
String sql= "";
sql="select * from signdo where signId=\'"+signId+"\'and name=\'"+userid+"'";
PreparedStatement pst=c.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next()){
SignDo signDo=new SignDo();
signDo.setSignId(rs.getInt("SignDoId"));
signDo.setName(rs.getString("name"));
signDo.setWord(rs.getString("signWord"));
signDo.setSignTime(rs.getTimestamp("signTime").toString());
list.add(signDo);
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
DBConnection.closeConnection(c);
}
}
传回前端并重定向
List<SignDo> signDoList= signDao.getSignDoListByUserid(signId,udata);
request.setAttribute("signDoList",signDoList);
request.getRequestDispatcher("/main/SignDetail.jsp").forward(request, response);
搜索前
搜索后