getWeather/src/main/java/com/example/getweather/service/XmoComplements.java

package com.example.getweather.service;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.example.getweather.entity.DistrictCodeDO;
import com.example.getweather.entity.HeFengDay;
import com.example.getweather.entity.HeFengHour;
import com.example.getweather.util.WeixinPush;
import com.taosdata.jdbc.TSDBDriver;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;

@Service
@Validated
@Slf4j
public class XmoComplements {
    @Resource
    private WeixinPush weixinPush;

    // 辅助方法：安全地获取列表中的值并转换为字符串，如果列表为空或索引越界则返回空字符串
    private static String getValueSafelyAsString(List<?> list, int index) {
        if (list == null || index < 0 || index >= list.size()) {
            return "";
        }
        Object value = list.get(index);
        return value != null ? String.valueOf(value) : "";
    }

    // 辅助方法：安全地获取列表中的值，如果列表为空或索引越界则返回null
    private static <T> T getValueSafely(List<T> list, int index) {
        if (list == null || index < 0 || index >= list.size()) {
            return null;
        }
        return list.get(index);
    }

    public static Timestamp convertToTimestamp(String dateTimeStr) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date parsedDate = dateFormat.parse(dateTimeStr);
            return new Timestamp(parsedDate.getTime());
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

    public void download() {
        List<String> areaCodes = new ArrayList<>();
        List<DistrictCodeDO> districtCodeDOList = new ArrayList<>();
        // 获取今天的日期（yyyy-MM-dd）
        String startDate = "2025-02-18";
        String endDate = "2025-03-05";
        boolean isApiQuotaExhausted = false; // 引入标志变量
        try {
            List<Entity> dictList = Db.use().findAll("system_dict_data");
            for (Entity entity : dictList) {
                String name = entity.getStr("label");
                if (name.contains("area_code")) {
                    String code = entity.getStr("value");
                    String[] s = code.split(",");
                    areaCodes = Arrays.asList(s);
                }
            }
            List<Entity> districtCodes = Db.use().findAll("jy_district_code");
            for (Entity e : districtCodes) {
                DistrictCodeDO districtCodeDO = new DistrictCodeDO();
                districtCodeDO.setCode(Integer.parseInt(e.getStr("code")));
                districtCodeDO.setName(e.getStr("name"));
                districtCodeDO.setLevel(Integer.parseInt(e.getStr("level")));
                districtCodeDO.setType(Integer.parseInt(e.getStr("type")));
                districtCodeDO.setAbname(e.getStr("abname"));
                districtCodeDO.setPid(Integer.parseInt(e.getStr("pid")));
                districtCodeDO.setLat(!Objects.equals(e.getStr("lat"), "") && e.getStr("lat") != null ? new BigDecimal(e.getStr("lat")).setScale(6, RoundingMode.HALF_UP).doubleValue() : 0d);
                districtCodeDO.setLng(!Objects.equals(e.getStr("lng"), "") && e.getStr("lng") != null ? new BigDecimal(e.getStr("lng")).setScale(6, RoundingMode.HALF_UP).doubleValue() : 0d);
                districtCodeDOList.add(districtCodeDO);
            }
            String jdbcUrl = "jdbc:TAOS://192.168.12.241:29501/etadm_local?user=root&password=taosdata";
            Properties connProps = new Properties();
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_CHARSET, "UTF-8");
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_LOCALE, "en_US.UTF-8");
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_TIME_ZONE, "UTC-8");
            Connection conn = DriverManager.getConnection(jdbcUrl, connProps);
            //省份
            for (String code : areaCodes) {
                if (isApiQuotaExhausted) break; // 检查标志变量
                List<DistrictCodeDO> shiList = districtCodeDOList.stream().filter(item -> item.getPid().toString().equals(code)).collect(Collectors.toList());
                //市
                for (DistrictCodeDO d : shiList) {
                    if (isApiQuotaExhausted) break; // 检查标志变量
                    List<DistrictCodeDO> quList = districtCodeDOList.stream().filter(item -> item.getPid().toString().equals(d.getCode().toString())).collect(Collectors.toList());
                    String xmoUrl;
                    //曦谋综合数据查询
                    xmoUrl = "https://weather-api.xm-opt.com/v1/forecast15Minutes?latitude=" + d.getLat() + "&longitude=" + d.getLng() + "&minutely_15=weather_code,temperature_2m,surface_pressure,relative_humidity_2m,precipitation,cloud_cover,wind_speed_10m,wind_direction_10m,shortwave_radiation&timezone=Asia%2FShanghai&start_date=" + startDate + "&end_date=" + endDate;
                    isApiQuotaExhausted = analysis(xmoUrl, d.getCode(), d.getName(), conn); // 传递标志变量
                    if (isApiQuotaExhausted) break; // 检查标志变量
                    //区
                    for (DistrictCodeDO q : quList) {
                        if (isApiQuotaExhausted) break; // 检查标志变量
                        xmoUrl = "https://weather-api.xm-opt.com/v1/forecast15Minutes?latitude=" + d.getLat() + "&longitude=" + d.getLng() + "&minutely_15=weather_code,temperature_2m,surface_pressure,relative_humidity_2m,precipitation,cloud_cover,wind_speed_10m,wind_direction_10m,shortwave_radiation&timezone=Asia%2FShanghai&start_date=" + startDate + "&end_date=" + endDate;
                        isApiQuotaExhausted = analysis(xmoUrl, q.getCode(), q.getName(), conn); // 传递标志变量
                        if (isApiQuotaExhausted) break; // 检查标志变量
                        //线程睡5秒 防止请求过快
                        Thread.sleep(5000);
                    }
                }
            }
            conn.close();
            String alarm = "电力交易系统在获取15分钟级天气数据时报错, 曦谋天气API 额度已用尽";
            String description = "接口调用异常";
            if (isApiQuotaExhausted) {
                //给企业微信发送消息
                //weixinPush.sendMessageBot(alarm, description);
            } else {
                alarm = "电力交易系统曦谋天气数据全部下载完毕";
                description = "接口调用成功";
                //weixinPush.sendMessageBot(alarm, description);
                log.info("曦谋天气15分钟级天气数据全部下载完毕");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean analysis(String xmoUrl, int areaCode, String name, Connection conn) {
        log.info("曦谋天气数据,开始下载地区:{} 的天气数据", name);
        try {
            Statement ps = conn.createStatement();
            String body = HttpUtil.createGet(xmoUrl).header("apikey", "c615a8f6-f5dd-4a81-86fe-db2a42862c15").execute().charset("utf-8").body();
            JSONObject jsonObject = JSONUtil.parseObj(body);
            if (jsonObject.toString().contains("API call limit has been reached")) {
                return true;
            }
            JSONObject result = JSONUtil.parseObj(jsonObject.get("minutely_15"));
            List<HeFengHour> xmoList = new ArrayList<>();
            // 获取各个数据数组
            List<String> timeList = result.get("time", List.class);
            List<Integer> weatherCodeList = result.get("weather_code", List.class);
            List<Double> temperature2mList = result.get("temperature_2m", List.class);
            List<Double> surfacePressureList = result.get("surface_pressure", List.class);
            List<Double> relativeHumidity2mList = result.get("relative_humidity_2m", List.class);
            List<Double> precipitationList = result.get("precipitation", List.class);
            List<Double> cloudCoverList = result.get("cloud_cover", List.class);
            List<Double> windSpeed10mList = result.get("wind_speed_10m", List.class);
            List<Double> windDirection10mList = result.get("wind_direction_10m", List.class);
            List<Double> shortwaveRadiationList = result.get("shortwave_radiation", List.class);
            int length = timeList == null ? 0 : timeList.size();
            //获取当前时间 yyyy-mm-dd hh:mm
            //String currentTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
            // 定义输入日期时间格式
            DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm");
            if (length > 0) {
                // 遍历数组，填充XMO对象并添加到列表
                for (int i = 0; i < length; i++) {
                    HeFengHour heFengHour = new HeFengHour();
                    // 解析输入字符串为 LocalDateTime 对象
                    LocalDateTime localDateTime = LocalDateTime.parse(getValueSafely(timeList, i), inputFormatter);
                    // 将 LocalDateTime 转换为 Instant 对象
                    Instant instant = localDateTime.atZone(ZoneId.systemDefault()).toInstant();
                    // 将 Instant 对象转换为 Date 对象
                    //Date 转为 timestamp。getTimestamp
                    Date date = Date.from(instant);
                    heFengHour.setTs(new Timestamp(date.getTime()));
                    heFengHour.setWindSpeed(Objects.equals(getValueSafelyAsString(windSpeed10mList, i), "") || getValueSafelyAsString(windSpeed10mList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(windSpeed10mList, i)));
                    heFengHour.setWindSpeedM(Objects.equals(getValueSafelyAsString(windSpeed10mList, i), "") || getValueSafelyAsString(windSpeed10mList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(windSpeed10mList, i)) * 1000 / 3600);
                    heFengHour.setWindDir(getValueSafelyAsString(windDirection10mList, i));
                    heFengHour.setTemp(Objects.equals(getValueSafelyAsString(temperature2mList, i), "") || getValueSafelyAsString(temperature2mList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(temperature2mList, i)));
                    heFengHour.setPrecip(Float.parseFloat(getValueSafelyAsString(precipitationList, i)));
                    heFengHour.setText(skycon(getValueSafelyAsString(weatherCodeList, i)));
                    heFengHour.setDswrf(Float.parseFloat(getValueSafelyAsString(shortwaveRadiationList, i)));
                    heFengHour.setHumidity(Objects.equals(getValueSafelyAsString(relativeHumidity2mList, i), "") || getValueSafelyAsString(relativeHumidity2mList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(relativeHumidity2mList, i)));
                    heFengHour.setPressure(Objects.equals(getValueSafelyAsString(surfacePressureList, i), "") || getValueSafelyAsString(surfacePressureList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(surfacePressureList, i)));
                    heFengHour.setCloud(Objects.equals(getValueSafelyAsString(cloudCoverList, i), "") || getValueSafelyAsString(cloudCoverList, i) == null ? null : Float.parseFloat(getValueSafelyAsString(cloudCoverList, i)));
                    xmoList.add(heFengHour);
                }
            }
            for (HeFengHour item : xmoList) {
                String insertSql = " INSERT INTO hefeng_hour_unit_" + areaCode + " USING hefeng_hour_unit TAGS('" + areaCode + "') values('" + item.getTs() + "', " + item.getTemp() + ", '" + item.getText() + "', '" + item.getWindDir() + "', " + item.getWind360() + ", " + item.getWindSpeed() + ", " + item.getWindScale() + ", " + item.getHumidity() + ", " + item.getCloud() + ", " + item.getPrecip() + ", " + item.getDew() + ", " + item.getPressure() + ", " + item.getDswrf() + ", " + item.getWindSpeedM() + ")";
                int affectedRows = ps.executeUpdate(insertSql);
            }
            if (ps != null)
                ps.close();
        } catch (Exception e) {
            log.error("曦谋天气,地区:{}  15分钟级天气数据下载失败JSON格式化错误" + e, name);
            return true;
        }
        log.info("曦谋天气,地区:{} 15分钟级天气数据下载完成", name);
        return false;
    }

    /**
     * 根据代码获取对应天气描述
     *
     * @param skycon
     * @return
     */
    private String skycon(String skycon) {
        switch (skycon) {
            case "0":
                return "晴";
            case "1":
                return "多云";
            case "2":
                return "少云";
            case "3":
                return "阴";
            case "45":
                return "雾";
            case "48":
                return "冻雾";
            case "51":
            case "53":
            case "55":
                return "毛毛雨";
            case "56":
            case "57":
                return "冻毛毛雨";
            case "61":
                return "小雨";
            case "63":
                return "中雨";
            case "65":
                return "大雨";
            case "66":
            case "67":
                return "冻雨";
            case "71":
                return "小雪";
            case "73":
                return "中雪";
            case "75":
                return "大雪";
            case "77":
                return "雪粒";
            case "80":
                return "小阵雨";
            case "81":
                return "阵雨";
            case "82":
                return "强阵雨";
            case "85":
                return "小阵雪";
            case "86":
                return "阵雪";
            case "95":
                return "雷雨";
            case "96":
            case "99":
                return "雷雨伴有冰雹";
            default:
                return "晴"; // 默认返回晴
        }
    }

    public void cal() {

        try {
            String jdbcUrl = "jdbc:TAOS://192.168.12.241:29501/etadm_local?user=root&password=taosdata";
            Properties connProps = new Properties();
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_CHARSET, "UTF-8");
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_LOCALE, "en_US.UTF-8");
            connProps.setProperty(TSDBDriver.PROPERTY_KEY_TIME_ZONE, "UTC-8");
            Connection conn = DriverManager.getConnection(jdbcUrl, connProps);
            //--------------------------------------------------
            Statement ps;
            ps = conn.createStatement();
            //所有数据
            ResultSet rs = null;
            //转换过后的数据
            List<Map<String, String>> a;
            String start = "";
            String end = "";
            //---------创建文件----------
            //String[] csvNames = table.split(",");
            String sql = "select * from etadm_local.hefeng_day_unit";
            rs = ps.executeQuery(sql);
            //计数
            int count = 0;
            List<HeFengDay> hefengDayList = new ArrayList<>();
            while (rs.next()) {
                HeFengDay heFengDay = new HeFengDay();
                heFengDay.setAreaCode(rs.getString("area_code"));
                heFengDay.setTs(convertToTimestamp(rs.getString("ts")));
                heFengDay.setTempMax(Float.parseFloat(rs.getString("temp_max")));
                heFengDay.setTempMin(Float.parseFloat(rs.getString("temp_min")));
                heFengDay.setTextDay(rs.getString("text_day"));
                heFengDay.setTextNight(rs.getString("text_night"));
                heFengDay.setWind360Day(Float.parseFloat(rs.getString("wind_360_day")));
                heFengDay.setWindDirDay(rs.getString("wind_dir_day"));
                heFengDay.setWindScaleDay(new BigDecimal(rs.getString("wind_scale_day")).intValue());
                heFengDay.setWindSpeedDay(Float.parseFloat(rs.getString("wind_speed_day")));
                heFengDay.setWind360Night(Float.parseFloat(rs.getString("wind_360_night")));
                heFengDay.setWindDirNight(rs.getString("wind_dir_night"));
                heFengDay.setWindScaleNight(new BigDecimal(rs.getString("wind_scale_night")).intValue());
                heFengDay.setWindSpeedNight(Float.parseFloat(rs.getString("wind_speed_night")));
                heFengDay.setHumidity(Float.parseFloat(rs.getString("humidity")));
                heFengDay.setPrecip(Float.parseFloat(rs.getString("precip")));
                heFengDay.setPressure(Float.parseFloat(rs.getString("pressure")));
                heFengDay.setDswrf(Float.parseFloat(rs.getString("dswrf")));
                heFengDay.setWindSpeedMDay(new BigDecimal(rs.getString("wind_speed_day")).divide(BigDecimal.valueOf(3.6), 4, RoundingMode.HALF_UP).floatValue());
                heFengDay.setWindSpeedMNight(new BigDecimal(rs.getString("wind_speed_night")).divide(BigDecimal.valueOf(3.6), 4, RoundingMode.HALF_UP).floatValue());
                hefengDayList.add(heFengDay);
            }

            for (HeFengDay heFengDay : hefengDayList) {
                String insertSQL = "INSERT INTO etadm_local.hefeng_day_unit_" + heFengDay.getAreaCode() + " USING hefeng_day_unit TAGS('" + heFengDay.getAreaCode() + "')  values('" + heFengDay.getTs() + "', " + heFengDay.getTempMax() + ", " + heFengDay.getTempMin() + ", '" + heFengDay.getTextDay() + "', '" + heFengDay.getTextNight() + "', " + heFengDay.getWind360Day() + ", '"
                        + heFengDay.getWindDirDay() + "', " + heFengDay.getWindScaleDay() + ", " + heFengDay.getWindSpeedDay() + ", " + heFengDay.getWind360Night() + ", '" + heFengDay.getWindDirNight() + "', " + heFengDay.getWindScaleNight() + ", " + heFengDay.getWindSpeedNight() + ", " + heFengDay.getHumidity() + ", " + heFengDay.getPrecip() + ", "
                        + heFengDay.getPressure() + ", " + heFengDay.getDswrf() + ", " + heFengDay.getWindSpeedMDay() + ", " + heFengDay.getWindSpeedMNight() + ")";
                int affectedRows = ps.executeUpdate(insertSQL);
                count = count + affectedRows;
            }
            log.info("数据转换完成,共转换{}条数据", count);
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}