Homestay_app_houtai/mhotel/src/com/happy/dao/impl/UserVisitsDaoImpl.java

package com.happy.dao.impl;

import com.happy.Model.BookingLog;
import com.happy.Model.UserVisits;
import com.happy.Until.DateUtil;
import com.happy.Until.Enum.OrderEnum;
import com.happy.Until.Func;
import com.happy.Until.TimeExchange;
import com.happy.Until.UUIDUtil;
import com.happy.dao.BookingLogDao;
import com.happy.dao.UserVisitsDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository("UserVisitsDao")
public class UserVisitsDaoImpl implements UserVisitsDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(
            NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }


    @Override
    public int insert(UserVisits userVisits) {
        String sql = "insert into `user_visits`(id,userid,create_time,type,remark) " +
                "values(:id,:userid,:create_time,:type,:remark) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if(userVisits.getId()==0){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", userVisits.getId());
        }
        sps.addValue("userid", userVisits.getUserid());
        sps.addValue("create_time", UUIDUtil.getNewDate());
        sps.addValue("type", userVisits.getType());
        sps.addValue("remark", userVisits.getRemark());

        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }


    @Override
    public int add(String userId) {
        UserVisits userVisits = new UserVisits();
        userVisits.setUserid(userId);
        userVisits.setType(OrderEnum.用户访问量.name());
        return this.insert(userVisits);
    }

    @Override
    public UserVisits queryTodayUserVisits() {
        String todaysta = DateUtil.parseDateToStr(new Date(), DateUtil.Time_Formatter_Day) + " 00:00:00 ";
        String todayend = DateUtil.parseDateToStr(new Date(), DateUtil.Time_Formatter_Day) + " 23:59:59 ";
        String yesstar = TimeExchange.getYesturday() + " 00:00:00 ";
        String yesend = TimeExchange.getYesturday() + " 23:59:59 ";


        String sql = " select todayCount,yesterdayCount, (todayCount-yesterdayCount) as compare from ( " +
                "(select COUNT(*) as todayCount from user_visits  where 1=1 AND create_time >= '" + todaysta+ "' and  create_time < '" + todayend+ "'  )  as today, " +
                "(select COUNT(*) as yesterdayCount from user_visits  where 1=1 AND create_time >= '" + yesstar+ "' and  create_time < '" + yesend+ "')  as yesterday )";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        Map map =  namedParameterJdbcTemplate.queryForMap(sql,sps);
        UserVisits info = new UserVisits();
        info.putAll(map);
        return info;
    }

    @Override
    public List queryVisitsBySql(String sql)
    {
        String sqlQuery = "select a.day,IFNULL(b.visitNumber,0) visitNumber from (  " +
                " SELECT DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL seq DAY) AS day  " +
                "  FROM (  " +
                "      SELECT -1 seq UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL  " +
                "      SELECT -5 UNION ALL SELECT -6 UNION ALL SELECT -7 UNION ALL SELECT -8 UNION ALL SELECT -9 UNION ALL SELECT -10 UNION ALL  " +
                "      SELECT -11 UNION ALL SELECT -12 UNION ALL SELECT -13 UNION ALL SELECT -14 UNION ALL SELECT -15 UNION ALL SELECT -16 UNION ALL  " +
                "      SELECT -17 UNION ALL SELECT -18 UNION ALL SELECT -19 UNION ALL SELECT -20 UNION ALL SELECT -21 UNION ALL SELECT -22 UNION ALL  " +
                "      SELECT -23 UNION ALL SELECT -24 UNION ALL SELECT -25 UNION ALL SELECT -26 UNION ALL SELECT -27 UNION ALL SELECT -28 UNION ALL  " +
                "      SELECT -29 UNION ALL SELECT -30 UNION ALL  " +
                "      SELECT 0  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  " +
                "      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL  " +
                "      SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL  " +
                "      SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL  " +
                "      SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL  " +
                "      SELECT 29 UNION ALL SELECT 30  " +
                "  ) t   where " +
                sql       +
                " ) a   "  +
                " left join (select DATE_FORMAT(create_time,'%Y-%m-%d') day,count(1) visitNumber from user_visits group by DATE_FORMAT(create_time,'%Y-%m-%d')) b on a.day=b.day\n " +
                " ORDER BY day ";

        return namedParameterJdbcTemplate.queryForList(sqlQuery,new MapSqlParameterSource());
    }


    @Override
    public List queryVisitsByDiyTime(String startTime, String endTime)
    {
        int dayCount = DateUtil.getDayDiff(startTime,endTime);
        if (dayCount <= 0)
            throw new RuntimeException("请输入正确的时间区间");
        StringBuffer positiveStr = new StringBuffer();  // 负
        StringBuffer negativeStr  = new StringBuffer(); // 正
        for (int i = 0; i < dayCount; i++) {
            if (i == 0) {
                negativeStr.append(" SELECT "+i+" seq ");
                continue;
            }
            negativeStr.append(" UNION ALL SELECT " + i);
            positiveStr.append(" UNION ALL SELECT -" + i);
        }




        String sqlQuery = "select a.day,IFNULL(b.visitNumber,0) visitNumber from (  " +
                " SELECT DATE_ADD(DATE_FORMAT('"+startTime+"','%Y-%m-%d'), INTERVAL seq DAY) AS day  " +
                "  FROM (  " + negativeStr.toString() + positiveStr.toString() +
//                "      SELECT -1 seq UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL  " +
//                "      SELECT -5 UNION ALL SELECT -6 UNION ALL SELECT -7 UNION ALL SELECT -8 UNION ALL SELECT -9 UNION ALL SELECT -10 UNION ALL  " +
//                "      SELECT -11 UNION ALL SELECT -12 UNION ALL SELECT -13 UNION ALL SELECT -14 UNION ALL SELECT -15 UNION ALL SELECT -16 UNION ALL  " +
//                "      SELECT -17 UNION ALL SELECT -18 UNION ALL SELECT -19 UNION ALL SELECT -20 UNION ALL SELECT -21 UNION ALL SELECT -22 UNION ALL  " +
//                "      SELECT -23 UNION ALL SELECT -24 UNION ALL SELECT -25 UNION ALL SELECT -26 UNION ALL SELECT -27 UNION ALL SELECT -28 UNION ALL  " +
//                "      SELECT -29 UNION ALL SELECT -30 UNION ALL  " +
//                "      SELECT 0  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  " +
//                "      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL  " +
//                "      SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL  " +
//                "      SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL  " +
//                "      SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL  " +
//                "      SELECT 29 UNION ALL SELECT 30  " +
                "  ) t   where " +
                "                DATE_ADD(DATE_FORMAT('"+startTime+"','%Y-%m-%d'), INTERVAL seq DAY) >= DATE_FORMAT('"+ startTime +"','%Y-%m-%d')"+
                "               AND DATE_ADD(DATE_FORMAT('"+startTime+"','%Y-%m-%d'), INTERVAL seq DAY) <= DATE_FORMAT('"+ endTime +"','%Y-%m-%d')"+
                " ) a   "  +
                " left join (select DATE_FORMAT(create_time,'%Y-%m-%d') day,count(1) visitNumber from user_visits group by DATE_FORMAT(create_time,'%Y-%m-%d')) b on a.day=b.day  " +
                " ORDER BY day ";

        return namedParameterJdbcTemplate.queryForList(sqlQuery,new MapSqlParameterSource());
    }

}