fix in num,add sql index,change every 1s to every 10s

liuxiaolong

2019-05-23 86b189ee9072db4bf2648c16307eff1da077e8e4

fix in num,add sql index,change every 1s to every 10s

 src/main/java/com/cloud/count/dao/CountDao.java

@@ -23,8 +23,8 @@
     * @return
     */
//    @Select("select COALESCE(sum(enters),0) from people where  timestamp BETWEEN #{begin} AND #{end}")
    @Select("SELECT COALESCE(p2.enters-p1.enters,0) FROM (SELECT enters from people where  timestamp BETWEEN #{begin} AND #{end} ORDER BY id ASC LIMIT 1)p1,(SELECT enters from people where  timestamp BETWEEN #{begin} AND #{end} ORDER BY id DESC LIMIT 1)p2")
    Integer countInPeople(@Param("begin") long begin,@Param("end") long end);
    @Select("SELECT COALESCE(p2.enters-p1.enters,0) FROM (SELECT enters from people where dateStr=#{todayStr} and timestamp BETWEEN #{begin} AND #{end} ORDER BY id ASC LIMIT 1)p1,(SELECT enters from people where dateStr=#{todayStr} and timestamp BETWEEN #{begin} AND #{end} ORDER BY id DESC LIMIT 1)p2")
    Integer countInPeople(@Param("begin") long begin,@Param("end") long end,@Param("todayStr") String todayStr);

    /**
     * 统计出去人数
@@ -32,8 +32,8 @@
     * @return
     */
   // @Select("select COALESCE(sum(exits),0) from people where timestamp BETWEEN #{begin} AND #{end}")
    @Select("SELECT COALESCE(p2.exits-p1.exits,0) FROM (SELECT exits from people where  timestamp BETWEEN #{begin} AND #{end} ORDER BY id ASC LIMIT 1)p1,(SELECT exits from people where  timestamp BETWEEN #{begin} AND #{end} ORDER BY id DESC LIMIT 1)p2")
    Integer countOutPeople( @Param("begin") long begin,@Param("end") long end);
    @Select("SELECT COALESCE(p2.exits-p1.exits,0) FROM (SELECT exits from people where dateStr=#{todayStr} and timestamp BETWEEN #{begin} AND #{end} ORDER BY id ASC LIMIT 1)p1,(SELECT exits from people where dateStr=#{todayStr} and timestamp BETWEEN #{begin} AND #{end} ORDER BY id DESC LIMIT 1)p2")
    Integer countOutPeople( @Param("begin") long begin,@Param("end") long end,@Param("todayStr") String todayStr);

    /**
     * 插入一条People数据

 src/main/java/com/cloud/count/service/serviceImpl/CountServiceImpl.java

@@ -104,12 +104,12 @@

        // 从开始计算时间到现在的进校数
        //进校人数 = 当前报文totalEnters - 基准进校人数 + 校正值
        int inPeople = totalEnters - baseEnters + correctionPeople;
        int inPeople = totalEnters - baseEnters + correctionPeople + initialPeople;

        int outPeople = totalExits - baseExits;

        // 校内总人数 = 进校人数 - 出校人数 + 校内初始化人数 + 误差数量
        int allPeople = inPeople - outPeople + initialPeople;
        int allPeople = inPeople - outPeople;

        // 现在离开始统计的时间的小时数 7个多小时 按7个小时算
        //int hour = (int)(Math.abs(now-set)+3599)/3600;
@@ -160,20 +160,21 @@
     * @return
     */
   private int getDataValue( Map<String, Object> map,int i,long start,long end,int initialPeople,int correctionPeople){
       String todayStr = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        switch (i){
            case 1:
                map.put("countType","进校人数");
                Integer countInPeople = dao.countInPeople(start,end);
                Integer countInPeople = dao.countInPeople(start, end, todayStr);
                countInPeople=countInPeople==null||countInPeople<0?0:countInPeople;
                return countInPeople+correctionPeople;
            case -1:
                map.put("countType","出校人数");
                Integer countOutPeople = dao.countOutPeople(start,end);
                Integer countOutPeople = dao.countOutPeople(start, end, todayStr);
                return countOutPeople==null||countOutPeople<0?0:countOutPeople;
            case 0:
                map.put("countType","校内人数");
                Integer in = dao.countInPeople(start,end);
                Integer out = dao.countOutPeople(start,end);
                Integer in = dao.countInPeople(start, end, todayStr);
                Integer out = dao.countOutPeople(start, end, todayStr);
                in = in==null||in<0?0:in;
                out = out==null||out<0?0:out;
                return in-out+initialPeople+correctionPeople;

 src/main/resources/application.yml

@@ -3,9 +3,9 @@
    name: count-service
  datasource:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/count_device?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
      url: jdbc:mysql://192.168.1.173:3306/count-service?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
      username: root
      password: root
      password: c++java123
      hikari:
        minimum-idle: 5
        maximum-pool-size: 20

 src/main/resources/index.html

@@ -20,17 +20,21 @@
<div >
    <div style="background:#FFFFFF;background-size:100%;height:60px; width:100%;border:0px">
        <div style="background:#e6e6fa;height:60px;float:left; width:90%">
            <div style="margin:10px 650px">
            <div style="width:400px;margin:10px auto;">
                <span style="font-size:32px;font-family:SimHei;font-weight:bold;text-align-all:center;">首都师范大学第二附属中学</span>
            </div>
        </div>

        <div style="background:#e6e6fa;height:60px;float:left; width:10%"><img onclick="update()" src="static/cl3.jpg" style="margin:0px  0px"></div>
        <div style="background:#e6e6fa;height:60px;float:left; width:10%"><img onclick="update()" src="static/cl3.jpg" style="margin-top:4px;"></div>
    </div>

    <div style="display:flex;">

    </div>

    <div style="background:#F0F4F7;height:990px; width:100%;border:0">
        <div style="background:#F0F4F7;height:100%;float:left; width:141px"></div>
        <div style="background:#F0F4F7;height:100%;float:left; width:391px">
        <!--<div style="background:#F0F4F7;height:100%;float:left; width:141px"></div>-->
        <div style="background:#F0F4F7;height:100%;float:left; width:391px;margin-left:20px;margin-right:20px;">
            <div style="background:#F0F4F7;height:70px;float:left; width:391px"></div>
            <div onclick="changeCountType(1)" style="background:url('/static/01.jpg');height:160px;float:left; width:391px">
                <div style="margin:60px -10px;text-align:center;float:left;height:160px; width:190px">
@@ -112,9 +116,7 @@
            </div>
            <span style="font-size:20px;font-family:SimHei;text-align-all:center;" id="start_time"></span>
        </div>

        <div style="background:#F0F4F7;height:100%;float:left; width:86px">
        </div>
        <!--<div style="background:#F0F4F7;height:100%;float:left; width:86px"></div>-->
        <div style="background:#F0F4F7;height:100%;float:left; width:1201px">
            <div style="background:#F0F4F7;height:70px;float:right; width:1200px;text-align: right">
                <span style="font-size:28px;font-family:SimHei;text-align-all:center;" id="date_time">2018年11月1日&nbsp;12:11&nbsp;星期四</span>
@@ -225,8 +227,8 @@

        $(function () {
            startRequest();
            setInterval("startRequest()", 5000 );
            setInterval("nowPeople()", 5000);
            setInterval("startRequest()", 10000 );
            setInterval("nowPeople()", 10000);
        });
        function update() {
            $.messager.confirm('消息','确定进行基础设置吗？',function(r){