package com.songshu.mongo.service.impl;

import com.songshu.mongo.model.dto.Room;
import com.songshu.mongo.service.UpdateTingcheService;
import com.songshu.mongo.tools.JDBCUtil;
import com.songshu.mongo.tools.JDBCUtilMysql;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.ObjectUtils;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class UpdateTingcheServiceImpl implements UpdateTingcheService {


    /**
     * tj_tcsjtjn
     */
    @Override
    public void updateTjTcsjtjn() {
        log.info("tj_tcsjtjn");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_tcsjtjn");
            String sql = "insert into tj_tcsjtjn(threelow,threeToFive,fiveToEight,eightup)  VALUES (?, ?, ?, ?)";
            String selectSql = "select " +
                    "ifnull(sum(case when TIMESTAMPDIFF(hour,rcsj,ccsj) <= 3 then 1 else 0 end),0) threelow, " +
                    "ifnull(sum(case when TIMESTAMPDIFF(hour,rcsj,ccsj) > 3 and TIMESTAMPDIFF(hour,rcsj,ccsj) <= 5 then 1 else 0 end),0) threeToFive, " +
                    "ifnull(sum(case when TIMESTAMPDIFF(hour,rcsj,ccsj) > 5 and TIMESTAMPDIFF(hour,rcsj,ccsj) <= 8 then 1 else 0 end),0) fiveToEight, " +
                    "ifnull(sum(case when TIMESTAMPDIFF(hour,rcsj,ccsj) > 8 then 1 else 0 end),0) eightup " +
                    "from vehicle_records where year(ccsj) =  year(SYSDATE())";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("threelow"));
                ps.setObject(2, data.get("threeToFive"));
                ps.setObject(3, data.get("fiveToEight"));
                ps.setObject(4, data.get("eightup"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_tcsjtjn表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_tcsjtjn表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }

    /**
     * tj_cljrsctjn
     */
    @Override
    public void updateTjCljrsctjn() {
        log.info("tj_cljrsctjn");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_cljrsctjn");
            String sql = "insert into tj_cljrsctjn(y,rcsl,ccsl)  VALUES (?, ?, ?)";
            String selectSql = " SELECT a.y,ifnull(b.sl,0) AS rcsl,ifnull(c.sl,0) AS ccsl FROM ( " +
                    "SELECT help_keyword_id y FROM mysql.help_keyword HAVING y BETWEEN 1 AND 12 ORDER BY 1) a LEFT JOIN ( " +
                    "SELECT temp.y,sum(temp.sl) AS sl FROM ( " +
                    "SELECT MONTH (rcsj) AS y,count(MONTH (rcsj)) sl FROM vehicle_records WHERE YEAR (rcsj)=YEAR (sysdate()) GROUP BY MONTH (rcsj) UNION ALL " +
                    "SELECT MONTH (rcsj) AS y,count(MONTH (rcsj)) sl FROM parking_vehicles WHERE YEAR (sysdate())=YEAR (rcsj) GROUP BY MONTH (rcsj)) temp GROUP BY temp.y) b ON a.y=b.y LEFT JOIN ( " +
                    "SELECT MONTH (ccsj) AS y,count(*) AS sl FROM vehicle_records WHERE ccsj IS NOT NULL AND YEAR (ccsj)=YEAR (sysdate()) GROUP BY MONTH (ccsj)) c ON a.y=c.y  ";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("y"));
                ps.setObject(2, data.get("rcsl"));
                ps.setObject(3, data.get("ccsl"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_cljrsctjn表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_cljrsctjn表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }

    /**
     * tj_cljrsctjy
     */
    @Override
    public void updateTjCljrsctjy() {
        log.info("tj_cljrsctjy");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_cljrsctjy");
            String sql = "insert into tj_cljrsctjy(r,rcsl,ccsl)  VALUES (?, ?, ?)";
            String selectSql = " SELECT a.r,ifnull(b.sl,0) AS rcsl,ifnull(c.sl,0) AS ccsl FROM (SELECT help_keyword_id r FROM mysql.help_keyword HAVING r BETWEEN 1 AND DAY (last_day(sysdate())) ORDER BY 1) a LEFT JOIN (SELECT temp.r,sum(temp.sl) sl FROM (SELECT DAY (rcsj) AS r,count(DAY (rcsj)) sl FROM vehicle_records WHERE date_format(rcsj,'%y%m')=date_format(sysdate(),'%y%m') GROUP BY DAY (rcsj) UNION ALL SELECT DAY (rcsj) AS r,count(DAY (rcsj)) sl FROM parking_vehicles WHERE date_format(rcsj,'%y%m')=date_format(sysdate(),'%y%m') GROUP BY DAY (rcsj)) temp GROUP BY temp.r) b ON a.r=b.r LEFT JOIN (SELECT date_format(ccsj,'%y%m') AS ny,DAY (ccsj) AS r,count(*) AS sl FROM vehicle_records WHERE rcsj IS NOT NULL AND date_format(ccsj,'%y%m')=date_format(sysdate(),'%y%m') GROUP BY date_format(ccsj,'%y%m'),DAY (ccsj)) c ON a.r=c.r";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("r"));
                ps.setObject(2, data.get("rcsl"));
                ps.setObject(3, data.get("ccsl"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_cljrsctjy表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_cljrsctjy表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }

    /**
     * tj_cljrsctjr
     */
    @Override
    public void updateTjCljrsctjr() {
        log.info("tj_cljrsctjr");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_cljrsctjr");
            String sql = "insert into tj_cljrsctjr(x,rcsl,ccsl)  VALUES (?, ?, ?)";
            String selectSql = "  SELECT a.x,ifnull(sum(b.sl),0) AS rcsl,IFNULL(sum(c.sl),0) AS ccsl FROM ( \n" +
                    "                    SELECT help_keyword_id x FROM mysql.help_keyword HAVING x BETWEEN 0 AND 23 ORDER BY 1) a \n" +
                    " LEFT JOIN " +
                    " ( " +
                    " SELECT temp.x,count(*) AS sl FROM " +
                    " (" +
                    " SELECT date_format(rcsj,'%H') AS x FROM vehicle_records WHERE rcsj IS NOT NULL AND date_format(rcsj,'%y%m%d')=date_format(SYSDATE(),'%y%m%d') " +
                    " UNION ALL " +
                    " SELECT date_format(rcsj,'%H') AS x FROM parking_vehicles WHERE rcsj IS NOT NULL AND date_format(rcsj,'%y%m%d')=date_format(SYSDATE(),'%y%m%d')" +
                    " ) temp GROUP BY temp.x" +
                    " ) b ON a.x=b.x " +
                    " LEFT JOIN " +
                    " ( " +
                    " SELECT date_format(ccsj,'%H') AS x,count(*) AS sl FROM vehicle_records WHERE date_format(ccsj,'%y%m%d')=date_format(SYSDATE(),'%y%m%d') GROUP BY date_format(ccsj,'%H')) c " +
                    " ON a.x=c.x GROUP BY a.x ORDER BY a.x ";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("x"));
                ps.setObject(2, data.get("rcsl"));
                ps.setObject(3, data.get("ccsl"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_cljrsctjr表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_cljrsctjr表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }

    /**
     * tj_jrcrktcsj
     */
    @Override
    public void updateTjJrcrktcsj() {
        log.info("tj_jrcrktcsj");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_jrcrktcsj");
            String sql = "insert into tj_jrcrktcsj(cphm,zhlx,rcsj,ccsj,tcsj,ssje,rctd,cctd)  VALUES (?, ?, ?,?, ?, ?,?, ?)";
            String selectSql = "select \n" +
                    "vehicle_records.cphm,\n" +
                    "vehicle_records.zhlx,\n" +
                    "vehicle_records.rcsj,\n" +
                    "vehicle_records.ccsj,\n" +
                    "round(timestampdiff(minute,vehicle_records.rcsj,vehicle_records.ccsj)/60,2) as tcsj,\n" +
                    "ifnull(parking_payment.ssje,0) ssje,\n" +
                    "vehicle_records.rctd as rctd,\n" +
                    "vehicle_records.cctd as cctd\n" +
                    "from\n" +
                    "vehicle_records\n" +
                    "left join parking_payment on parking_payment.cphm = vehicle_records.cphm and parking_payment.rcsj = vehicle_records.rcsj\n" +
                    "where  date_format(vehicle_records.rcsj,'%y%m%d') = date_format(NOW(),'%y%m%d')";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("cphm"));
                ps.setObject(2, data.get("zhlx"));
                ps.setObject(3, data.get("rcsj"));
                ps.setObject(4, data.get("ccsj"));
                ps.setObject(5, data.get("tcsj"));
                ps.setObject(6, data.get("ssje"));
                ps.setObject(7, data.get("rctd"));
                ps.setObject(8, data.get("cctd"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_jrcrktcsj表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_jrcrktcsj表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }


    /**
     * tj_parking_payment
     */
    @Override
    public void updateTjParkingPayment() {
        log.info("tj_parking_payment");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_parking_payment");
            String sql = "insert into tj_parking_payment(xh,cphm,zhlx,rcsj,sfsj,sfy,gtmc,ysje,ssje,cxye,dzje,mfje,sflx,zflx,ddh)  VALUES " +
                    "(?, ?, ?,?, ?, " +
                    "?,?, ?, ?, ?," +
                    "?,?, ?, ?, ?)";
            String selectSql = "SELECT xh,cphm,zhlx,rcsj,sfsj,sfy,gtmc,ysje,ssje,cxye,dzje,mfje,sflx,zflx,ddh FROM parking_payment where  date_format(rcsj,'%y%m%d') = date_format(NOW(),'%y%m%d') or date_format(sfsj,'%y%m%d') = date_format(NOW(),'%y%m%d')";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("xh"));
                ps.setObject(2, data.get("cphm"));
                ps.setObject(3, data.get("zhlx"));
                ps.setObject(4, data.get("rcsj"));
                ps.setObject(5, data.get("sfsj"));

                ps.setObject(6, data.get("sfy"));
                ps.setObject(7, data.get("gtmc"));
                ps.setObject(8, data.get("ysje"));
                ps.setObject(9, data.get("ssje"));
                ps.setObject(10, data.get("cxye"));

                ps.setObject(11, data.get("dzje"));
                ps.setObject(12, data.get("mfje"));
                ps.setObject(13, data.get("sflx"));
                ps.setObject(14, data.get("zflx"));
                ps.setObject(15, data.get("ddh"));

                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_jrcrktcsj表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_jrcrktcsj表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }


    /**
     * tj_vehicle_records
     */
    @Override
    public void updateTjVehicleRecords() {
        log.info("tj_vehicle_records");
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtilMysql.getConnection();
            Statement stat = JDBCUtilMysql.getStatement();
            JDBCUtilMysql.executeUpdate("DELETE FROM tj_vehicle_records");
            String sql = "insert into tj_vehicle_records(xh,cphm,zhlx,czxm,rcsj,rctd,ccsj,cctd,ccfs)  VALUES " +
                    "(?, ?, ?,?, ?, " +
                    "?,?, ?, ?)";
            String selectSql = "SELECT * FROM vehicle_records where  date_format(vehicle_records.rcsj,'%y%m%d') = date_format(NOW(),'%y%m%d')\n" +
                    "or date_format(vehicle_records.ccsj,'%y%m%d') = date_format(NOW(),'%y%m%d')";
            ArrayList<Map> all = getQueryList(conn, selectSql);
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            if (all == null || all.size() == 0) {
                return;
            }
            int size = all.size();
            for (int i = 1; i < size + 1; i++) {
                Map data = all.get(i - 1);
                ps.setObject(1, data.get("xh"));
                ps.setObject(2, data.get("cphm"));
                ps.setObject(3, data.get("zhlx"));
                ps.setObject(4, data.get("czxm"));
                ps.setObject(5, data.get("rcsj"));

                ps.setObject(6, data.get("rctd"));
                ps.setObject(7, data.get("ccsj"));
                ps.setObject(8, data.get("cctd"));
                ps.setObject(9, data.get("ccfs"));
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();                      // 将容器中的sql语句提交
                    ps.clearBatch();                        // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            log.info("tj_vehicle_records表同步失败: {}", System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            log.info("tj_vehicle_records表同步结束: {}", System.currentTimeMillis() - start);
            JDBCUtilMysql.getClose();
        }
    }


    private ArrayList<Map> getQueryList(Connection conn, String selectSql) throws SQLException {
        ArrayList<Map> all = new ArrayList<Map>();
        ArrayList<String> titleNameList = new ArrayList<>();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(selectSql);
        int columnCount = rs.getMetaData().getColumnCount();
        for (int i = 0; i < columnCount; i++) {
            titleNameList.add(rs.getMetaData().getColumnLabel((i + 1)));
        }
        while (rs.next()) {
            HashMap<String, Object> map = new HashMap<>();
            for (String name : titleNameList) {
                map.put(name, rs.getString(name));
            }
            all.add(map);
        }
        return all;
    }


}