thridParty/GetDataByView/src/main/java/com/jyxt/getdatabyview/view/IBPSRepository.java

package com.jyxt.getdatabyview.view;

import com.jyxt.getdatabyview.view.entity.CollectorEntity;
import com.jyxt.getdatabyview.view.entity.MonitoredEntity;
import com.jyxt.getdatabyview.view.entity.WSDEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Repository
public class IBPSRepository {

    private static final Logger log = LoggerFactory.getLogger(IBPSRepository.class);

    @Autowired
    @Qualifier("secondJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Value("${config.testmode}")
    private String testmode;


    public List<String> getExistID(){
        List<String> existIDList = new ArrayList<String>();
        /**
         * 正式启用开启这一段
         * String existSql = "select id_ from t_dsfwsdjlb where Date(collectTime) = CURDATE()"
         */
        String existSql = "select id_ from t_dsfwsdjlb where Date(collectTime) = CURDATE()";
        existIDList = jdbcTemplate.queryForList(existSql, String.class);
        return existIDList;
    }

    public List<CollectorEntity> getCollectorlist(){
        RowMapper<CollectorEntity> collectorRowMapper = new BeanPropertyRowMapper<>(CollectorEntity.class);
        String collectorSql = "select deviceNo,deviceName,collectID,collectName,type from t_cjqsbglpzb where enable='1'";
        List<CollectorEntity> collectorList = jdbcTemplate.query(collectorSql, collectorRowMapper);
        return collectorList;
    }

    public void save(final WSDEntity wsdEntity){
        //sql语句
        final String sql =  "insert into t_dsfwsdjlb (id_,create_time_,collectTime,result,resultHigh,resultLow,deviceNo,deviceName,collectID, "+
                            "collectName,shiYanZu,note,type,area,di_dian_,room,controlFlag) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        if((testmode.equals("1")) || (testmode.equals("2"))){
            log.info("pretend to insert: id:{},Res:{},ResH:{},ResL:{},DevName:{},ColID:{},ColName:{},ShiYanZu:{},Type:{},Flag:{}",
                    wsdEntity.getId(),wsdEntity.getResult(),wsdEntity.getResultHigh(),wsdEntity.getResultLow(),wsdEntity.getDeviceName(),
                    wsdEntity.getCollectID(),wsdEntity.getCollectName(),wsdEntity.getShiYanZu(),wsdEntity.getType(),wsdEntity.getControlFlag()
            );
        }
        if(testmode.equals("0")){
            log.info("ready to insert: id:{},Res:{},ResH:{},ResL:{},DevName:{},ColID:{},ColName:{},ShiYanZu:{},Type:{},Flag:{}",
                    wsdEntity.getId(),wsdEntity.getResult(),wsdEntity.getResultHigh(),wsdEntity.getResultLow(),wsdEntity.getDeviceName(),
                    wsdEntity.getCollectID(),wsdEntity.getCollectName(),wsdEntity.getShiYanZu(),wsdEntity.getType(),wsdEntity.getControlFlag()
            );
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement prepareStatement = connection.prepareStatement(sql);
                    prepareStatement.setString(1,wsdEntity.getId());
                    prepareStatement.setTimestamp(2, new java.sql.Timestamp(wsdEntity.getCreateTime().getTime()));
                    prepareStatement.setTimestamp(3, new java.sql.Timestamp(wsdEntity.getCollectTime().getTime()));
                    prepareStatement.setString(4,wsdEntity.getResult());
                    prepareStatement.setString(5,wsdEntity.getResultHigh());
                    prepareStatement.setString(6,wsdEntity.getResultLow());
                    prepareStatement.setString(7,wsdEntity.getDeviceNo());
                    prepareStatement.setString(8,wsdEntity.getDeviceName());
                    prepareStatement.setString(9,wsdEntity.getCollectID());
                    prepareStatement.setString(10,wsdEntity.getCollectName());
                    prepareStatement.setString(11,wsdEntity.getShiYanZu());
                    prepareStatement.setString(12,wsdEntity.getNote());
                    prepareStatement.setString(13,wsdEntity.getType());
                    prepareStatement.setString(14,wsdEntity.getArea());
                    prepareStatement.setString(15,wsdEntity.getDiDian());
                    prepareStatement.setString(16,wsdEntity.getRoom());
                    prepareStatement.setString(17,wsdEntity.getControlFlag());
                    return prepareStatement;
                }
            });
        }
    }

    public List<MonitoredEntity> getUndoFridgeList(String curdate) {
        List<MonitoredEntity> undoFridgeList = jdbcTemplate.query(
                "select * from t_bxwdjkjlb where date(create_time_)=? and shi_fou_guo_shen_='待处理'",
                new RowMapper<MonitoredEntity>() {
                    @Override
                    public MonitoredEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                        MonitoredEntity entity = new MonitoredEntity();
                        entity.setId(rs.getString("id_"));
                        entity.setDeviceNo(rs.getString("bing_xiang_bian_h"));
                        entity.setJsonStr(rs.getString("lie_biao_shu_ju_"));
                        return entity;
                    }
                },
                curdate  // 使用预编译参数防止SQL注入
        );
        return undoFridgeList;
    }

    public List<WSDEntity> getWSDEntityList(String CurDate, String Type,String DeviceNo) {
        List<WSDEntity> wsdEntityList = jdbcTemplate.query(
                "select id_,collecttime,collectID,collectName,shiYanZu,result,deviceNo,type,note,controlFlag from t_dsfwsdjlb where date(collectTime)=? and deviceNo=? order by collectTime desc",
                new RowMapper<WSDEntity>() {
                    @Override
                    public WSDEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                        WSDEntity entity = new WSDEntity();
                        entity.setId(rs.getString("id_"));
                        entity.setCollectTime(rs.getTimestamp("collectTime"));
                        entity.setCollectID(rs.getString("collectID"));
                        entity.setCollectName(rs.getString("collectName"));
                        entity.setShiYanZu(rs.getString("shiYanZu"));
                        entity.setResult(rs.getString("result"));
                        entity.setDeviceNo(rs.getString("deviceNo"));
                        entity.setType(rs.getString("type"));
                        entity.setNote(rs.getString("note"));
                        entity.setControlFlag(rs.getString("controlFlag"));
                        return entity;
                    }
                },
                CurDate,DeviceNo  // 使用预编译参数防止SQL注入
        );
        return wsdEntityList;
    }

    /**
     * 20250619 huangws
     * 保存被监测设备的温湿度结果
     * 如果还没未完成的不会改变是否过审的状态
     * @param undoMonitorID
     * @param hasFinished
     * @param newJsonStr
     */
    public void saveMonitoredData(String undoMonitorID, String hasFinished, String contrlFlag, String newJsonStr){
        String shangWuJianKon = LocalTime.now().format(DateTimeFormatter.ofPattern("HH:mm"));
        final String sql = hasFinished.equals("1")
                ? "update t_bxwdjkjlb set lie_biao_shu_ju_=?, update_time_=?, shang_wu_jian_kon=?, bei_zhu_='自动导入', shi_kong_zhuang_t =?, shi_fou_guo_shen_='已完成' where id_=?"
                : "update t_bxwdjkjlb set lie_biao_shu_ju_=?, update_time_=?, shang_wu_jian_kon=?, bei_zhu_='自动导入', shi_kong_zhuang_t =? where id_=?";
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement prepareStatement = connection.prepareStatement(sql);
                prepareStatement.setString(1, newJsonStr);
                prepareStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));  // 设置当前时间
                prepareStatement.setString(3, shangWuJianKon);  // 设置上午时间
                prepareStatement.setString(4, contrlFlag);
                prepareStatement.setString(5, undoMonitorID);
                return prepareStatement;
            }
        });
    }


}