KprAIModelPower/src/main/java/com/shkpr/service/aimodelpower/dbdao/shizilaishuiDataSource/WaterCollecationDao.java

package com.shkpr.service.aimodelpower.dbdao.shizilaishuiDataSource;

import com.shkpr.service.aimodelpower.bizmgr.KprAimWaterCollecationBizFun;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

/**
 * @ClassName WaterCollecationDao
 * @Description: TODO
 * @Author LX
 * @Date 2024/5/27
 * @Version V1.0
 **/
@Slf4j
@Repository
public class WaterCollecationDao {
    @Autowired
    @Qualifier("childDatasource")
    private DataSource childDataSource;

    @Autowired
    @Qualifier("child2Datasource")
    private DataSource child2DataSource;

    //TODO 查询样本及预测水量表;支持是否分页
    public List<Map<String,Object>> getTbMWater(boolean isPage,int limit,int offset,String extend){
            String sql = "";
            if ("water_volume_prediction_jiangjin2".equals(KprAimWaterCollecationBizFun.databaseName)){
                sql = "SELECT " +
                        " a.\"id\",to_char(a.\"date\", 'YYYY-MM-DD') AS \"date\",a.\"max_temperature\",a.\"min_temperature\",a.\"weather\",a.\"month\",a.\"week\",a.\"holiday\",a.\"last_actual_water_withdrawals\",a.\"last_actual_water_supply\",a.\"actual_water_withdrawals\",a.\"actual_water_supply\",a.\"forecast_water_withdrawals\",a.\"forecast_actual_water_supply\",a.\"is_abnormal\",a.\"is_forecast\",a.\"last_modify_time\",a.\"zone_id\" " +
                        " FROM tb_m_water AS a WHERE 1=1 ";
            }else {
                sql = "SELECT " +
                        " a.\"ID\",to_char(a.\"Date\", 'YYYY-MM-DD') AS \"Date\",a.\"Max_temperature\",a.\"Min_temperature\",a.\"Weather\",a.\"Month\",a.\"Week\",a.\"Holiday\",a.\"LastActualWaterWithdrawals\",a.\"LastActualWaterSupply\",a.\"ActualWaterWithdrawals\",a.\"ActualWaterSupply\",a.\"ForecastWaterWithdrawals\",a.\"ForecastActualWaterSupply\",a.\"isAbnormal\",a.\"isForecast\",a.\"LastModifyTime\",a.\"orgId\" " +
                        " FROM tb_m_water AS a WHERE 1=1 ";
            }
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询小时水量预测表;支持是否分页
    public List<Map<String,Object>> getTbMHourwater(boolean isPage,int limit,int offset,String extend){
            String sql = "";
            if ("water_volume_prediction_jiangjin2".equals(KprAimWaterCollecationBizFun.databaseName)){
                sql = "SELECT " +
                        " a.\"id\",to_char(a.\"date\", 'YYYY-MM-DD') AS \"date\",a.\"hour\",a.\"hour_forecast_water_withdrawals\",a.\"hour_forecast_actual_water_supply\",a.\"water_withdrawals_energy\",a.\"water_supply_energy\",a.\"real_water_withdrawals_energy\",a.\"real_water_supply_energy\",a.\"last_modify_time\",a.\"zone_id\"," +
                        " a.\"hour_actual_water_withdrawals\",a.\"hour_actual_water_supply\" " +
                        " FROM tb_m_hourwater AS a WHERE 1=1 ";
            }else {
                sql = "SELECT " +
                        " a.\"ID\",to_char(a.\"Date\", 'YYYY-MM-DD') AS \"Date\",a.\"Hour\",a.\"HourForecastWaterWithdrawals\",a.\"HourForecastActualWaterSupply\",a.\"WaterWithdrawalsEnergy\",a.\"WaterSupplyEnergy\",a.\"RealWaterWithdrawalsEnergy\",a.\"RealWaterSupplyEnergy\",a.\"LastModifyTime\",a.\"orgId\"," +
                        " a.\"HourActualWaterWithdrawals\",a.\"HourActualWaterSupply\" " +
                        " FROM tb_m_hourwater AS a WHERE 1=1 ";
            }
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询取水泵房电耗预测接口,支持是否分页
    public List<Map<String,Object>> getTbMHourwaterWaterwithdrawals(boolean isPage,int limit,int offset,String extend){
            String sql = "";
            if ("water_volume_prediction_jiangjin2".equals(KprAimWaterCollecationBizFun.databaseName)){
                sql = "SELECT " +
                        " to_char(a.\"date\", 'YYYY-MM-DD') AS \"date\",a.\"hour\",a.\"pump_id\",a.\"pump_status\",a.\"hour_forecast_water_withdrawals\",a.\"pump_water\",a.\"real_pump_status\",a.\"pump_energy\",a.\"real_pump_energy\",a.\"last_modify_time\",a.\"id\",a.\"zone_id\" " +
                        " FROM tb_m_hourwater_waterwithdrawals AS a WHERE 1=1 ";
            }else {
                sql = "SELECT " +
                        " to_char(a.\"Date\", 'YYYY-MM-DD') AS \"Date\",a.\"Hour\",a.\"PumpID\",a.\"PumpStatus\",a.\"HourForecastWaterWithdrawals\",a.\"PumpWater\",a.\"RealPumpStatus\",a.\"PumpEnergy\",a.\"RealPumpEnergy\",a.\"LastModifyTime\",a.\"ID\",a.\"orgId\" " +
                        " FROM tb_m_hourwater_waterwithdrawals AS a WHERE 1=1 ";
            }
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询供水泵房电耗预测接口,支持是否分页
    public List<Map<String,Object>> getTbMHourwaterWatersupply(boolean isPage,int limit,int offset,String extend){
            String sql = "";
            if ("water_volume_prediction_jiangjin2".equals(KprAimWaterCollecationBizFun.databaseName)){
                sql = "SELECT " +
                        " a.\"id\",to_char(a.\"date\", 'YYYY-MM-DD') AS \"date\",a.\"hour\",a.\"pump_id\",a.\"pump_status\",a.\"hour_forecast_actual_water_supply\",a.\"pump_water\",a.\"real_pump_status\",a.\"pump_energy\",a.\"real_pump_energy\",a.\"last_modify_time\",a.\"zone_id\" " +
                        " FROM tb_m_hourwater_watersupply AS a WHERE 1=1 ";
            }else {
                sql = "SELECT " +
                        " a.\"ID\",to_char(a.\"Date\", 'YYYY-MM-DD') AS \"Date\",a.\"Hour\",a.\"PumpID\",a.\"PumpStatus\",a.\"HourForecastActualWaterSupply\",a.\"PumpWater\",a.\"RealPumpStatus\",a.\"PumpEnergy\",a.\"RealPumpEnergy\",a.\"LastModifyTime\",a.\"orgId\" " +
                        " FROM tb_m_hourwater_watersupply AS a WHERE 1=1 ";
            }
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询水厂id配置列表
    public List<Map<String,Object>> getOrgConfig(boolean isPage,int limit,int offset,String extend){
        
            String sql = "SELECT * FROM water_org_config WHERE 1=1 ";
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询取水泵房方案数据,支持是否分页
    public List<Map<String,Object>> getTbMWaterwithdrawalspump(boolean isPage,int limit,int offset,String extend){
        
            String sql = "SELECT * FROM tb_m_waterwithdrawalspump WHERE 1=1 ";
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    //TODO 查询供水泵房方案数据,支持是否分页
    public List<Map<String,Object>> getTbMWatersupplypump(boolean isPage,int limit,int offset,String extend){
        
            String sql = "SELECT * FROM tb_m_watersupplypump WHERE 1=1 ";
            if(!StringUtils.isEmpty(extend)){
                sql+=extend;
            }
            if(isPage) {
                sql += " LIMIT " + limit + " OFFSET " + offset;
            }
            JdbcTemplate pgJdbc = new JdbcTemplate(childDataSource);
            List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
            return tableData;
        
    }

    /**
     * TODO 水位预测相关
     */
    //TODO 查询小时水位预测表;支持是否分页
    public List<Map<String,Object>> getTbLevelHourwater(boolean isPage,int limit,int offset,String extend){
        String sql = "";
        sql = "select aTable.id,TO_CHAR(aTable.date, 'YYYY-MM-DD') AS date,aTable.hour,aTable.hour_forecast_actual_level,aTable.hour_actual_water_level,aTable.last_modify_time,aTable.device_code,bTable.site_id,bTable.zone_id " +
                "from tb_m_hourlevel as aTable " +
                "left JOIN water_level_config as bTable  " +
                "ON aTable.device_code = bTable.device_code " +
                "WHERE 1=1 ";
        if(!StringUtils.isEmpty(extend)){
            sql+=extend;
        }
        if(isPage) {
            sql += " LIMIT " + limit + " OFFSET " + offset;
        }
        JdbcTemplate pgJdbc = new JdbcTemplate(child2DataSource);
        List<Map<String, Object>> tableData = pgJdbc.queryForList(sql);
        return tableData;

    }
}