etl-camel-huge/src/main/java/com/gct/tools/etlcamelhuge/controller/BaseDataController.java

package com.gct.tools.etlcamelhuge.controller;

import com.alibaba.fastjson.JSONObject;
import com.gct.tools.etlcamelhuge.entity.BaseDataBody;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.omg.CORBA.IRObject;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * class name: BaseDataController
 *
 * @author gxt
 * @version 1.0
 * @since 2021/8/30 下午3:51 周一
 */
@RestController
@RequestMapping("/BaseData")
@Api(value = "BaseDataController", description = "基础数据操作controller")
public class BaseDataController {

    private JdbcTemplate jdbcTemplate;
    @Resource(name = "oracle")
    DataSource oracleDataSource;

    @Resource(name = "centralbase")
    DataSource baseDataSource;

    @Resource(name = "gtsj")
    DataSource gtsjDataSource;

    @PostMapping("/saveDataToStatusDaily")
    @ApiOperation("从 A2 获取 数据插入到 centrlBase-StatusDaily 中,只需要传入查询时间")
    public JSONObject saveDataToStatusDaily(@RequestBody BaseDataBody baseDataBody) {
        JSONObject jsonObject = new JSONObject();
        int insertCount = 0;
        int updateDYMCount = 0;
        int updateCMDCount = 0;
        int updateOLiNozzleCount = 0;
        try {
            jdbcTemplate = new JdbcTemplate(oracleDataSource);
            String date = baseDataBody.getDate();
            String sql = "select  distinct jh,rq,cyfs,yz,hysx , yysx ,tysx,bs,dym from DBA01 where rq  = to_date('" + date + "','yyyy-MM-dd') and qyrq is not null ";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            for (Map<String, Object> map : list) {
                if (map.get("YZ") == null) map.put("YZ", "0.0");
                if (map.get("HYSX") == null) map.put("HYSX", "0.0");
                if (map.get("YYSX") == null) map.put("YYSX", "0.0");
                if (map.get("TYSX") == null) map.put("TYSX", "0.0");
                if (map.get("BS") == null) map.put("BS", "0.0");
                sql = "insert into centralbase.cb_pc_pro_wellbore_status_daily(well_id,prod_date,oil_prod_method,oil_nozzle,back_pres,tubing_pres,casing_pres,pump_depth) " +
                        "values (?,?,?,?,?,?,?,?)";
                insertCount = insertBaseDataSourceOfStatusDaily(sql, map);
                sql = "select  distinct jh , rq , dym from DBA01 where  (jh,rq) in (SELECT  jh,max(rq) rq  FROM DBA01 WHERE dym is not null and rq<=to_date('"+date+"','yyyy-MM-dd') group by jh)";
                List<Map<String, Object>> dymIsNotNUllList = selectA2DataOfNotIsNullDYMOrBJ(sql);
                for (Map<String, Object> stringObjectMap : dymIsNotNUllList) {
                    sql = "update centralbase.cb_pc_pro_wellbore_status_daily set start_pump_liq_level = '" + stringObjectMap.get("DYM") + "' where well_id = '" + stringObjectMap.get("JH") + "' and prod_date::date  = '" + stringObjectMap.get("RQ") + "' ";
                    updateDYMCount = updateBaseDataSourceOfStatusDaily(sql) + updateDYMCount;
                }
                sql = "select well_id,prod_date,start_pump_liq_level,pump_depth from centralbase.cb_pc_pro_wellbore_status_daily where prod_date = '" + map.get("RQ") + "' ";
                List<Map<String, Object>> CMDDataList = selectBaseDataSourceStatusDaily(sql);
                for (Map<String, Object> stringObjectMap : CMDDataList) {
                    stringObjectMap.put("submergence_depth", null);
                    if (stringObjectMap.get("start_pump_liq_level") != null && stringObjectMap.get("pump_depth") != null) {
                        double cmd = Double.valueOf(stringObjectMap.get("pump_depth").toString()) - Double.valueOf(stringObjectMap.get("start_pump_liq_level").toString()) / 10;
                        BigDecimal bd = new BigDecimal(cmd);
                        double cmd1 = bd.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
                        stringObjectMap.put("submergence_depth", cmd1);
                    }
                    //修改为batch 执行,用一个list 接受,在执行那个batchUpdate
                    sql = " update centralbase.cb_pc_pro_wellbore_status_daily set submergence_depth = '" + stringObjectMap.get("submergence_depth") + "' where well_id = '" + stringObjectMap.get("well_id") + "' and prod_date  = '" + stringObjectMap.get("prod_date") + "' ";
                    updateCMDCount = updateBaseDataSourceOfStatusDaily(sql) + updateCMDCount;
                }
                sql = "select distinct jh , rq , bj from DBA01 where  (jh,rq) in (SELECT  jh,max(rq) rq  FROM DBA01 WHERE bj is not null and rq<= to_date('"+date+"','yyyy-MM-dd') group by jh)";
                List<Map<String, Object>> oilNozzleList = selectA2DataOfNotIsNullDYMOrBJ(sql);
                for (Map<String, Object> stringObjectMap : oilNozzleList) {
                    sql = " update centralbase.cb_pc_pro_wellbore_status_daily set oil_nozzle = '" + stringObjectMap.get("BJ") + "' where well_id ='" + stringObjectMap.get("JH") + "' and prod_date='" + stringObjectMap.get("RQ") + "' ";
                    updateOLiNozzleCount = updateBaseDataSourceOfStatusDaily(sql) + updateOLiNozzleCount;
                }
                insertCount++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            jsonObject.put("error", e.getMessage());
        } finally {
            jsonObject.put("插入条数", insertCount);
            jsonObject.put("修改DYM条数", updateDYMCount);
            jsonObject.put("修改CMD条数", updateCMDCount);
            jsonObject.put("修改OliNozzle条数", updateOLiNozzleCount);
        }
        return jsonObject;
    }


    public int insertBaseDataSourceOfStatusDaily(String sql, Map<String, Object> map) {
        jdbcTemplate = new JdbcTemplate(baseDataSource);
        if (map.isEmpty()) return 0;
        return jdbcTemplate.update(sql, map.get("JH"), map.get("RQ"), map.get("CYFS"), map.get("YZ"), map.get("HYSX"), map.get("YYSX"), map.get("BS"), map.get("DYM"));
    }

    public List<Map<String, Object>> selectA2DataOfNotIsNullDYMOrBJ(String sql) {
        jdbcTemplate = new JdbcTemplate(oracleDataSource);
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String, Object>> selectBaseDataSourceStatusDaily(String sql) {
        jdbcTemplate = new JdbcTemplate(baseDataSource);
        return jdbcTemplate.queryForList(sql);
    }

    public int updateBaseDataSourceOfStatusDaily(String sql) {
        jdbcTemplate = new JdbcTemplate(baseDataSource);
        return jdbcTemplate.update(sql);
    }


    @PostMapping("/saveDataToVolDaily")
    @ApiOperation("从 A2 获取 数据插入到 centrlBase-VolDaily 中,只需要传入查询时间")
    public JSONObject saveVolDaily(@RequestBody BaseDataBody baseDataBody) {
        JSONObject jsonObject = new JSONObject();
        int insertCount = 0;
        int updateLiqOliDailyCount = 0;
        try {
            jdbcTemplate = new JdbcTemplate(oracleDataSource);
            String date = baseDataBody.getDate();
            String sql = " select distinct  jh,rq,scsj, rcyl1,rcyl,rcql,hs, bz from DBA01 where rq  = to_date('" + date + "','yyyy-MM-dd') and qyrq is not null ";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            for (Map<String, Object> map : list) {
                if (map.get("SCSJ") == null) map.put("SCSJ", "0.0");
                if (map.get("RCYL1") == null) map.put("RCYL1", "0.0");
                if (map.get("RCYL") == null) map.put("RCYL", "0.0");
                if (map.get("RCQL") == null) map.put("RCQL", "0.0");
                if (map.get("HS") == null) map.put("HS", "0.0");
                if (map.get("BZ") == null) map.put("BZ", "");
                map.put("RCSL", -1);
                map.put("QYB", -1);
                map.put("SQB", -1);
                if (map.get("RCQL") != null && map.get("RCYL") != null && !map.get("RCYL").equals("0.0") && map.get("RCYL") != "0.0") {
                    double qyb = Double.valueOf(map.get("RCQL").toString()) / Double.valueOf(map.get("RCYL").toString());
                    if (!Double.isNaN(qyb) && !Double.isInfinite(qyb)) {
                        BigDecimal bd = new BigDecimal(qyb);
                        double d1 = bd.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
                        map.put("QYB", d1);
                    }
                }
                if (map.get("RCYL1") != null && map.get("HS") != null && !map.get("HS").equals("0.0") && map.get("HS") != "0.0") {
                    double rcsl = (Double.valueOf(map.get("RCYL1").toString()) * Double.valueOf(map.get("HS").toString())) / 100;
                    if (!Double.isNaN(rcsl) && !Double.isInfinite(rcsl)) {
                        BigDecimal bd = new BigDecimal(rcsl);
                        double d1 = bd.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
                        map.put("RCSL", d1);
                    }
                }
                if (map.get("RCQL") != null && map.get("RCSL") != null && !map.get("RCQL").equals("0.0") && map.get("RCQL") != "0.0") {
                    double sqb = Double.valueOf(map.get("RCSL").toString()) / Double.valueOf(map.get("RCQL").toString());
                    if (!Double.isNaN(sqb) && !Double.isInfinite(sqb)) {
                        BigDecimal bd = new BigDecimal(sqb);
                        double d1 = bd.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
                        map.put("SQB", d1);
                    }
                }
                if (!map.containsKey("SMD")) {
                    map.put("SMD", 1);
                }
                if (!map.containsKey("YMD")) {
                    map.put("YMD", 0.85);
                }
                sql = "insert into centralbase.cb_pc_pro_wellbore_vol_daily(well_id,prod_date,prod_time,liq_prod_daily,oil_prod_daily,gas_prod_daily,water_cut,remarks,gas_oil_ratio,water_prod_daily,water_gas_ratio,surface_crude_water_density,surface_crude_oil_density) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?) ";
                insertCount = insertBaseDataVolDaily(sql, map) + insertCount;
                sql = "select distinct  jh,rq,scsj, rcyl1,rcyl,rcql,hs, bz from DBA01 where rq  = to_date('" + date + "','yyyy-MM-dd') and qyrq is not null ";
                updateLiqOliDailyCount = updateBaseDataVolDaily(sql) + updateLiqOliDailyCount;
            }

        } catch (Exception e) {
            jsonObject.put("error", e.getMessage());
        } finally {
            jsonObject.put("插入条数", insertCount);
            jsonObject.put("修改liqOliDaily条数", updateLiqOliDailyCount);
        }
        return jsonObject;
    }

    public int insertBaseDataVolDaily(String sql, Map<String, Object> map) {
        jdbcTemplate = new JdbcTemplate(baseDataSource);
        if (map.isEmpty()) return 0;
        return jdbcTemplate.update(sql, map.get("JH"), map.get("RQ"), map.get("SCSJ"), map.get("RCYL1"), map.get("RCYL"), map.get("RCQL"), map.get("HS"), map.get("BZ"), map.get("QYB"), map.get("RCSL"), map.get("SQB"), map.get("SMD"), map.get("YMD"));
    }

    public int updateBaseDataVolDaily(String sql) {
        jdbcTemplate = new JdbcTemplate(baseDataSource);
        return jdbcTemplate.update(sql);
    }

    @PostMapping("/getNeedRunTimeData")
    @ApiOperation("查询天安的数据库 只需要传过来 需要查询的sql 就行")
    public JSONObject getNeedRunTimeData(@RequestBody BaseDataBody baseDataBody){
        JSONObject jsonObject = new JSONObject();
        jdbcTemplate = new JdbcTemplate(gtsjDataSource);
        try{
            List<Map<String, Object>> list = jdbcTemplate.queryForList(baseDataBody.getSql());
            jsonObject.put("RunTimedata",list);
        }catch (Exception e){
            jsonObject.put("error",e.getMessage());
        }
         return jsonObject;
    }

    @PostMapping("/getNeedA2Data")
    @ApiOperation("查询A2数据库 只需要传过来 需要查询的sql 就行")
    public JSONObject getNeedA2Data(@RequestBody BaseDataBody baseDataBody){
        JSONObject jsonObject = new JSONObject();
        jdbcTemplate = new JdbcTemplate(oracleDataSource);
        try{
            List<Map<String, Object>> list = jdbcTemplate.queryForList(baseDataBody.getSql());
            jsonObject.put("A2data",list);
        }catch (Exception e){
            jsonObject.put("error",e.getMessage());
        }
        return jsonObject;
    }

    @PostMapping("/getA2WellSorceData")
    @ApiOperation("查询A2数据库 并且和 wellSource 做对比 只填写需要查询A2Data 的时间就行")
    public JSONObject getA2WellSorceData(@RequestBody BaseDataBody baseDataBody){
        JSONObject jsonObject = new JSONObject();
        jdbcTemplate = new JdbcTemplate(oracleDataSource);
        try{
            String sql = "select  distinct jh  from DBA01 where rq  = to_date('"+baseDataBody.getDate()+"','yyyy-MM-dd') and qyrq is not null ";
            List<Map<String, Object>>  A2DataList = jdbcTemplate.queryForList(sql);
            List<Map<String, Object>>  wellSourceList = getWellSource();
            Object collect = A2DataList.stream().filter(item -> !wellSourceList.contains(item)).collect(Collectors.toList());
            Object collect1 = wellSourceList.stream().filter(item -> !A2DataList.contains(item)).collect(Collectors.toList());
            jsonObject.put("wellSource 中不存在 A2 的井号为",collect);
            jsonObject.put("A2 中不存在 wellSource 的井号为",collect1);
        }catch (Exception e){
            jsonObject.put("error",e.getMessage());
        }
        return jsonObject;
    }

    public List<Map<String, Object>>  getWellSource(){
        List<Map<String, Object>>  list = new ArrayList<>();
        try{
            jdbcTemplate = new JdbcTemplate(baseDataSource);
            String sql = " select distinct well_id from centralbase.cb_cd_well_source ";
            list = jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}