package cn.com.taiji.mqcrj.task;

import cn.com.taiji.mqcrj.config.RequestDataHelper;
import cn.com.taiji.mqcrj.entity.AjxxBean;
import cn.com.taiji.mqcrj.entity.AjxyrBean;
import cn.com.taiji.mqcrj.entity.MqwgrBean;
import cn.com.taiji.mqcrj.mapper.BaseCreateNewTableMapper;
import cn.com.taiji.mqcrj.service.AjxxService;
import cn.com.taiji.mqcrj.service.AjxyrService;
import cn.com.taiji.mqcrj.service.MqwgrService;
import cn.com.taiji.mqcrj.utils.JDBCUtils;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * @author chen mh
 * @date 2023/7/26 8:36
 */
@Slf4j
@Component
public class OracleScheduleTask {


    @Resource
    private BaseCreateNewTableMapper baseCreateNewTableMapper;

    @Resource
    private AjxyrService ajxyrService;

    @Resource
    private AjxxService ajxxService;

    @Resource
    private MqwgrService mqwgrService;

    @Scheduled(cron = "2 25 2 * * ?")
    public void ajxyrTask() {
        log.info("执行定时任务 AjxyrTask -{}",DateUtil.date());
        Connection connection = JDBCUtils.getConnection();
        String sqlCount = "select count(*) from hndmc.v_ajxyr";
        PreparedStatement ps = null;
        ResultSet rs = null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sqlCount);
            rs = ps.executeQuery();
            int count;
            if(rs.next()){
                count = rs.getInt(1);
                log.info("数据总量是：{}",count);
                if(count>0){
                    //创建新表
                    String format = DateUtil.format(DateUtil.yesterday(), "YYYYMMdd");
                    baseCreateNewTableMapper.createTableAjxyr("v_sg_ajxyr_"+format);
                }else {
                    return;
                }
            }else {
                return;
            }
            for(int x = 0;x<count/10000+1;x++){
                int max = (x+1)*10000;
                int min = x*10000;
                String sql = "select * from (select v.*,rownum rn from hndmc.v_ajxyr v where ROWNUM <= "+max+") where rn>"+min;
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();

                // 获取ResultSet对象的列的数量、类型和属性。
                ResultSetMetaData md = resultSet.getMetaData();
                int columnCount = md.getColumnCount();
                // 将ResultSet对象的列名和值存到map中，再将map转换为json字符串，最后将json字符串转换为实体类对象
                List<AjxyrBean> objectBeanList = new ArrayList<>();
                Map<String, Object> rowData = new HashMap<>(16);
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnLabel(i), resultSet.getObject(i));
                    }
                    String jsonStr = JSONObject.toJSONString(rowData);
                    AjxyrBean objectBean = JSONObject.parseObject(jsonStr, AjxyrBean.class);
                    objectBeanList.add(objectBean);
                }
                RequestDataHelper.setRequestData(new HashMap<String, Object>(16) {{
                    put("v_sg_", "v_sg_ajxyr_"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));
                }});
                ajxyrService.saveBatch(objectBeanList);
                preparedStatement.close();
                resultSet.close();
            }
            log.info("数据导入结束后表名修改");
            //数据导入结束后表名修改
            baseCreateNewTableMapper.modifyTableName("v_sg_ajxyr_his"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"),"v_sg_ajxyr");
            baseCreateNewTableMapper.modifyTableName("v_sg_ajxyr","v_sg_ajxyr_"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));
            //删除七天前的数据表
            baseCreateNewTableMapper.dropTable("v_sg_ajxyr_his"+DateUtil.format(DateUtil.offsetDay(DateUtil.date(),-8), "YYYYMMdd"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeResource(connection,ps,rs);
            JDBCUtils.closeResource(connection,preparedStatement,resultSet);
        }

    }


    @Scheduled(cron = "25 53 1 * * ?")
    public void mqwgrTask() {
        log.info("执行定时任务 MqwgrTask -{}",DateUtil.date());
        Connection connection = JDBCUtils.getConnection();
        String sqlCount = "select count(*) from hndmc.v_sg_mqwgr";
        PreparedStatement ps = null;
        ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sqlCount);
            rs = ps.executeQuery();
            int count;
            if(rs.next()){
                count = rs.getInt(1);
                log.info("数据总量是：{}",count);
                if(count>0){
                    //创建新表
                    String format = DateUtil.format(DateUtil.yesterday(), "YYYYMMdd");
                    baseCreateNewTableMapper.createTableMqwgr("v_sg_mqwgr_"+format);
                }else {
                    return;
                }
            }else {
                return;
            }
            for(int x = 0;x<count/10000+1;x++) {
                int max = (x + 1) * 10000;
                int min = x * 10000;
                String sql = "select * from (select v.*,rownum rn from hndmc.v_sg_mqwgr v where ROWNUM <= "+max+") where rn>"+min;
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();

                // 获取ResultSet对象的列的数量、类型和属性。
                ResultSetMetaData md = resultSet.getMetaData();
                int columnCount = md.getColumnCount();
                // 将ResultSet对象的列名和值存到map中，再将map转换为json字符串，最后将json字符串转换为实体类对象
                List<MqwgrBean> mqwgrBeans = new ArrayList<>();
                Map<String, Object> rowData = new HashMap<>(16);
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnLabel(i), resultSet.getObject(i));
                    }
                    String jsonStr = JSONObject.toJSONString(rowData);
                    MqwgrBean objectBean = JSONObject.parseObject(jsonStr, MqwgrBean.class);
                    mqwgrBeans.add(objectBean);
                }
                RequestDataHelper.setRequestData(new HashMap<String, Object>(16) {{
                    put("v_sg_", "v_sg_mqwgr_" + DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));
                }});
                mqwgrService.saveBatch(mqwgrBeans);
                preparedStatement.close();
                resultSet.close();
            }
            //log.info("数据导入结束后表名修改");
            //数据导入结束后表名修改
            baseCreateNewTableMapper.modifyTableName("v_sg_mqwgr_his"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"),"v_sg_mqwgr");
            baseCreateNewTableMapper.modifyTableName("v_sg_mqwgr","v_sg_mqwgr_"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));

            //删除七天前的数据表
            baseCreateNewTableMapper.dropTable("v_sg_mqwgr_his"+DateUtil.format(DateUtil.offsetDay(DateUtil.date(),-8), "YYYYMMdd"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeResource(connection,ps,rs);
            JDBCUtils.closeResource(connection,preparedStatement,resultSet);
        }
    }


    @Scheduled(cron = "25 23 1 * * ?")
    public void ajxxTask() {
        log.info("执行定时任务 AjxxTask -{}",DateUtil.date());
        Connection connection = JDBCUtils.getConnection();
        String sqlCount = "select count(*) from hndmc.v_sg_ajxx";
        PreparedStatement ps = null;
        ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sqlCount);
            rs = ps.executeQuery();
            int count;
            if(rs.next()){
                count = rs.getInt(1);
                log.info("AjxxTask-数据总量是：{}",count);
                if(count>0){
                    //创建新表
                    String format = DateUtil.format(DateUtil.yesterday(), "YYYYMMdd");
                    baseCreateNewTableMapper.createTableAjxx("v_sg_ajxx_"+format);
                }else {
                    return;
                }
            }else {
                return;
            }
            for(int x = 0;x<count/20000+1;x++) {
                int max = (x + 1) * 20000;
                int min = x * 20000;
                String sql = "select * from (select v.*,rownum rn from hndmc.v_sg_ajxx v where TRANSACT_CASE_CODE is not null and ROWNUM <= "+max+") where rn>"+min;
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();

                // 获取ResultSet对象的列的数量、类型和属性。
                ResultSetMetaData md = resultSet.getMetaData();
                int columnCount = md.getColumnCount();
                // 将ResultSet对象的列名和值存到map中，再将map转换为json字符串，最后将json字符串转换为实体类对象
                List<AjxxBean> ajxxBeans = new CopyOnWriteArrayList<>();
                Map<String, Object> rowData = new HashMap<>(16);
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnLabel(i), resultSet.getObject(i));
                    }
                    String jsonStr = JSONObject.toJSONString(rowData);
                    AjxxBean objectBean = JSONObject.parseObject(jsonStr, AjxxBean.class);
                    ajxxBeans.add(objectBean);
                }
                RequestDataHelper.setRequestData(new HashMap<String, Object>(16) {{
                    put("v_sg_", "v_sg_ajxx_" + DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));
                }});
                ajxxService.saveBatch(ajxxBeans);
                preparedStatement.close();
                resultSet.close();
            }
            log.info("数据导入结束后表名修改");
            //数据导入结束后表名修改
            baseCreateNewTableMapper.modifyTableName("v_sg_ajxx_his"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"),"v_sg_ajxx");
            baseCreateNewTableMapper.modifyTableName("v_sg_ajxx","v_sg_ajxx_"+DateUtil.format(DateUtil.yesterday(), "YYYYMMdd"));

            //删除七天前的数据表
            baseCreateNewTableMapper.dropTable("v_sg_ajxx_his"+DateUtil.format(DateUtil.offsetDay(DateUtil.date(),-8), "YYYYMMdd"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeResource(connection,ps,rs);
            JDBCUtils.closeResource(connection,preparedStatement,resultSet);
        }
    }
}
