package org.jeecg.modules.dataVue.controller;

import com.alibaba.druid.proxy.jdbc.NClobProxyImpl;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import jtznyj.engineer.api.IEngineerApi;
import jtznyj.engineer.dto.WarnItemDto;
import net.sf.json.JSONObject;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.modules.cas.service.IWellboreAuthQuery;
import org.jeecg.modules.dataVue.entity.Jsra;
import org.jeecg.modules.dataVue.model.FocusWarnDto;
import org.jeecg.modules.dataVue.service.*;
import org.jeecg.modules.warning.common.FileUtil;
import org.jeecg.modules.warning.entity.Ltd;
import org.jeecg.modules.warning.model.DrillInfoModel;
import org.jeecg.modules.warning.service.IFzqkService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

@RestController
@RequestMapping("/dataVue/common")
public class CommonUseController {
    @Autowired
    IZjfzService iZjfzService;
    @Autowired
    ILjrbService iLjrbService;
    @Autowired
    ILtdSourceService iLtdSourceService;
    @Autowired
    IZjyService iZjyService;
    @Autowired
    IEpbpService iEpbpService;
    @Autowired
    IFzqkService iFzqkService;
    @Autowired
    IMasterService iLogicService;
    @Autowired
    IJsraService iJsraService;
    @Autowired
    ILtdAndJhService ltdAndJhService;
    @Autowired
    ILtdSourceService ltdService;
    @Autowired
    IAjhService ajhService;
    @Autowired
    IAjhJwssService ajhJwssService;
    @Autowired
    IWellboreAuthQuery iWellboreAuthQuery;
    @Autowired
    IEngineerApi iEngineerApi;

    @RequestMapping(value = "/getCaseList", method = RequestMethod.GET)
    public Result<?> getComplexCaseList(String jh, String sl, String ljkbs,
                                        @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                        @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize, HttpServletRequest req) {


        Result<HashMap<String, Object>> result = new Result<>();
        String sql = "select * from ( select  t.jh || t.sgxh id," +
                "t.ljkbs," +
                "t.jh," +
                "t.sgxh, t.sgdjbh, t.sglbid, t.nyhbkzz, t.fsjgjyy,t.sgclgk," +
                "t.sgmc," +
                "to_char(t.fsrq, 'yyyy-MM-dd') fsrq," +
                "t.fssj, " +
                "to_char(t.jcrq, 'yyyy-MM-dd') jcrq," +
                "t.jcsj," +
                "t.sssj," +
                "t.dsjs," +
                "t1.name mc," +
                "t2.name djmc," +
                "(select  count(*) from azy01 where jh = t.jh and albh = t.sgxh) as sl " +
                "from jsra t " +
                "left join Text_cl t1 " +
                "on t.sglbid = t1.id " +
                "left join Text_cl t2 " +
                "on t.sgdjbh = t2.id )";
        try {
            int start = pageNo * pageSize - pageSize;
            int end = pageNo * pageSize;
            String condition = " where 1 = 1";
            if (StringUtils.isNotEmpty(jh)) {
                condition += String.format(" and jh like '%%%s%%' ", jh);
            }
            if (StringUtils.isNotEmpty(ljkbs)) {
                condition += " and ljkbs = 0 ";
            }
            if (StringUtils.isNotEmpty(sl)) {
                condition += " and sl>0 ";
            }

            String pageSql = "select * from (select rownum a,t.* from (" + sql + " " + condition + ") t where rownum<=" + end + ") where a>" + start;
            String countSql = " select count(*) from (" + sql.toString() + " ）" + condition;
            int count = iZjfzService.querySqlCount(countSql, null);
            List<Map<String, Object>> list = iZjfzService.querySql(pageSql, null);
            HashMap<String, Object> temp = new HashMap<>();
            temp.put("totalCount", count);
            temp.put("pageSize", pageSize);
            temp.put("pageNo", pageNo);
            temp.put("totalPage", Math.ceil(count / Double.parseDouble(pageSize.toString())));
            temp.put("data", list);
            result.setResult(temp);
            return result;
        } catch (Exception ex) {
            result.error500(ex.getMessage());
        }

        return Result.error("");
    }


    @RequestMapping(value = "/getDrillingWell", method = RequestMethod.GET)
    public Result<?> getDrillingWellNameList(String jh, String ejdw, String jwss,
                                             @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                             @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize, HttpServletRequest req) {
        Result<HashMap<String, Object>> result = new Result<>();
        Map<String, Object> pageData = ltdAndJhService.getDrillingPageWell(ejdw, jh, pageNo, pageSize, jwss);

        HashMap<String, Object> temp = new HashMap<>();
        int count = (int) pageData.get("count");
        temp.put("totalCount", count);
        temp.put("pageSize", pageSize);
        temp.put("pageNo", pageNo);
        temp.put("totalPage", Math.ceil(count / Double.parseDouble(pageSize.toString())));
        temp.put("data", pageData.get("list"));
        result.setResult(temp);
        return result;
    }

    /**
     * 新的获取井列表
     *
     * @param jh
     * @param ejdw
     * @param jwss
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @RequestMapping(value = "/getDrillingWell2", method = RequestMethod.GET)
    public Result<?> getDrillingWellNameList2(String jh, String ejdw, String jwss,
                                              @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                              @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                              HttpServletRequest req,
                                              @RequestParam(name = "status", defaultValue = "0") Integer status
    ) {

        Result<HashMap<String, Object>> result = new Result<>();
        List<Map<String, Object>> list = new ArrayList<>();
        String table = "A_JH";
        if (StringUtils.isNotEmpty(jwss) && jwss.equals("Y")) {
            table = "A_JH_JWSS";
        }
        String where = "";
        String focusJhSql = String.format(" select JH from A_FOCUS_JH where cas_user = '%s'", iWellboreAuthQuery.getSessionCasName());
        switch (status) {
            case 1://关注
                where = String.format(" and JH in (%s ) ", focusJhSql);
                break;
            case 2://未关注
                where = String.format(" and JH not in (%s ) ", focusJhSql);
                break;
            default://全部
                break;
        }

        String sql = "select ID,DH,ZJY zjy,SGYQ,DSSJ,SJJS,SJDW,KZRQ,ZJYDAY zjyDay,DT,EJDW,JJRQ,LTD ltd, TO_CHAR(LASTDATE,'YYYY-MM-DD') lastDate, ";
        sql += "LTDDAY ltdDay,JH,(select x.jh from B_WELLOWNENGINEER x where x.jh=O.jh) mx from " + table + " O where 1=1 " + where;
        if (StringUtils.isNotEmpty(jh)) {
            sql += String.format(" and JH like '%%%s%%' ", jh);
        }
        if (StringUtils.isNotEmpty(ejdw)) {
            sql += "and EJDW= '" + ejdw + "'";
        }
//        sql += "order by TO_DATE(NVL(LASTDATE,nvl(LTDDAY,'1999.01.01')), 'yyyy.MM.dd') desc,JH asc";
        sql += "order by NVL(LASTDATE,TO_DATE('1999-01-01','yyyy.MM.dd')) desc,JH asc";
        int start = pageNo * pageSize - pageSize;
        int end = pageNo * pageSize;
        String condition = "";
        String pageSql = "select * from (select rownum a,t.* from (" + sql + " " + condition + ") t where rownum<=" + end + ") where a>" + start;
        String countSql = " select count(*) from (" + sql.toString() + " ）" + condition;
        int count = ajhService.querySqlCount(countSql, null);
        list = ajhService.querySql(pageSql, null);

        List<Map<String, Object>> focusList = ajhService.querySql(focusJhSql, null);
        for (Map<String, Object> itemMap : list) {
            itemMap.put("focus", 0);
            for (Map<String, Object> focusMap : focusList) {
                if (itemMap.get("JH").toString().equals(focusMap.get("JH").toString())) {
                    itemMap.put("focus", 1);
                }
            }
            BigDecimal ltd = new BigDecimal(itemMap.get("LTD").toString());
            itemMap.put("ltd", ltd);
            Object ltdDay = itemMap.get("LTDDAY");
            itemMap.put("ltdDay", ltdDay);
            BigDecimal zjy = new BigDecimal(itemMap.get("ZJY").toString());
            itemMap.put("zjy", zjy);
            Object zjyDay = itemMap.get("ZJYDAY");
            itemMap.put("zjyDay", zjyDay);
            Object lastDate = itemMap.get("LASTDATE");
            itemMap.put("lastDate", lastDate);

//            String DSJS = "";
//            String SJJS = "";
//            String kzSql = "select DSJS,SJJS from ( select * from v_yw_zjrb where jh='" + itemMap.get("JH").toString() + "' ORDER BY LRSJ desc ) where ROWNUM =1 ";
//            List<Map<String, Object>> kzList = iLjrbService.querySql(kzSql, null);
//            kzList.removeAll(Collections.singleton(null));
//            if (kzList.size() > 0) {
//                DSJS = kzList.get(0).get("DSJS") == null ? "" : kzList.get(0).get("DSJS").toString();
//                SJJS = kzList.get(0).get("SJJS") == null ? "" : kzList.get(0).get("SJJS").toString();
//            }
            if ("钻井液".equals(itemMap.getOrDefault("SOURCE", ""))) {
                itemMap.put("MX", "");
            } else {
                if (itemMap.getOrDefault("MX", null) == null) {
                    itemMap.putIfAbsent("MX", "默认");
                } else {
                    itemMap.put("MX", "定制");
                }
            }

            itemMap.put("DSJS", itemMap.get("DSSJ"));
//            itemMap.put("SJJS", SJJS
//
//            );
        }
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("totalCount", count);
        temp.put("pageSize", pageSize);
        temp.put("pageNo", pageNo);
        temp.put("totalPage", Math.ceil(count / Double.parseDouble(pageSize.toString())));
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }


    @GetMapping("/getAJh")
    public Result<?> getAjh(String jwss) {
        int pageStart = 1;
        int count = 1000000;
        Map<String, Object> exist = new HashMap<>();
        while (pageStart * 10 < count) {
            Map<String, Object> paged = ltdAndJhService.getDrillingPageWell(null, "", pageStart, 10, jwss);
            count = (int) paged.get("count");
            List<Map<String, Object>> list = (List<Map<String, Object>>) paged.get("list");
            for (Map<String, Object> row : list) {
                if (row.containsKey("ltd") && (int) row.get("ltd") == 1) {
                    exist = row;
                    break;
                }
                if (row.containsKey("zjy") && (int) row.get("zjy") == 1) {
                    exist = row;
                    break;
                }
            }
            if (exist.keySet().size() > 0) {
                break;
            }
            pageStart++;
        }
        Result<Map<String, Object>> result = new Result<>();
        result.setResult(exist);

        return result;
    }

    //定时任务版本
    @GetMapping("/getAJh2")
    public Result<?> getAjh2(String jwss) {

        Result<Map<String, Object>> result = new Result<>();
        Map<String, Object> exist = new HashMap<>();
        String table = "A_JH";
        if (jwss.equals("Y")) {
            table = "A_JH_JWSS";
        }
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select ID,DH,ZJY zjy,SGYQ,SJDW,KZRQ,ZJYDAY zjyDay,DT,EJDW,JJRQ,LTD ltd,LTDDAY ltdDay,JH from " + table + " ";
        sql += "order by TO_DATE(NVL(LTDDAY,nvl(ZJYDAY,'1999.01.01')), 'yyyy.MM.dd') desc,JH asc";
        list = ajhService.querySql(sql, null);
        for (Map<String, Object> itemMap : list) {
            BigDecimal ltd = new BigDecimal(itemMap.get("LTD").toString());
            itemMap.put("ltd", ltd);
            Object ltdDay = itemMap.get("LTDDAY");
            itemMap.put("ltdDay", ltdDay);
            BigDecimal zjy = new BigDecimal(itemMap.get("ZJY").toString());
            itemMap.put("zjy", zjy);
            Object zjyDay = itemMap.get("ZJYDAY");
            itemMap.put("zjyDay", zjyDay);
        }
        exist = list.get(0);
        result.setResult(exist);

        return result;

    }

    //定时任务版本
    @GetMapping("/getInfoByJh")
    public Result<?> getInfoByJh(String jh) {

        Result<Map<String, Object>> result = new Result<>();
        Map<String, Object> exist = new HashMap<>();
        String table = "A_JH";
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select ID,DH,ZJY zjy,SGYQ,SJDW,KZRQ,ZJYDAY zjyDay,DT,EJDW,JJRQ,LTD ltd,LTDDAY ltdDay,JH from " + table + " ";
        sql += " where JH='" + jh + "' ";
        sql += "order by TO_DATE(NVL(LTDDAY,nvl(ZJYDAY,'1999.01.01')), 'yyyy.MM.dd') desc,JH asc";
        list = ajhService.querySql(sql, null);
        for (Map<String, Object> itemMap : list) {
            BigDecimal ltd = new BigDecimal(itemMap.get("LTD").toString());
            itemMap.put("ltd", ltd);
            Object ltdDay = itemMap.get("LTDDAY");
            itemMap.put("ltdDay", ltdDay);
            BigDecimal zjy = new BigDecimal(itemMap.get("ZJY").toString());
            itemMap.put("zjy", zjy);
            Object zjyDay = itemMap.get("ZJYDAY");
            itemMap.put("zjyDay", zjyDay);
        }
        exist = list.get(0);
        result.setResult(exist);

        return result;

    }

    @GetMapping("/getAJhByJh")
    public Result<?> getAJhByJh(String jwss, String jh) {
        int pageStart = 1;
        Map<String, Object> paged = ltdAndJhService.getDrillingPageWell(null, jh, pageStart, 2, jwss);
        List<Map<String, Object>> list = (List<Map<String, Object>>) paged.get("list");

        Result<Map<String, Object>> result = new Result<>();
        if (list.size() > 0) {
            result.setResult(list.get(0));
        }

        return result;
    }

    @GetMapping("/getAJhByJh2")
    public Result<?> getAJhByJh2(String jwss, String jh) {
        int pageStart = 1;
        Map<String, Object> paged = ltdAndJhService.getDrillingPageWell(null, jh, pageStart, 2, jwss);
        List<Map<String, Object>> list = (List<Map<String, Object>>) paged.get("list");

        Result<Map<String, Object>> result = new Result<>();
        if (list.size() > 0) {
            result.setResult(list.get(0));
        }

        return result;
    }

    @RequestMapping(value = "/getEjdwList", method = RequestMethod.GET)
    public Result<?> getEjdwList() {
        Result<HashMap<String, Object>> result = new Result();
//        String sql = "SELECT groupid, g_cname FROM V_SYS_gROUP \n" +
//                "WHERE G_PARENTID='101'\n" +
//                "ORDER BY G_SHOWORDER";
//        List<Map<String, Object>> data = iLjrbService.querySql(sql, null);
        List<Map<String, Object>> data = iLjrbService.getEjdwList();
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", data);
        result.setResult(temp);
        return result;
    }


    @RequestMapping(value = "/getRbEjdwList", method = RequestMethod.GET)
    public Result<?> getRbEjdwList() {
        Result<HashMap<String, Object>> result = new Result();
        String sql = "select ejdw,count(1) count from A_JH GROUP BY ejdw order by count desc";
        List<Map<String, Object>> data = ajhService.querySql(sql, null);
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", data);
        result.setResult(temp);
        return result;
    }

    @RequestMapping(value = "/queryWellCoordinate", method = RequestMethod.GET)
    public Result<?> queryWellCoordinate(String jh) {
        Result<HashMap<String, Object>> result = new Result();
        String sql = " select  xzb x, yzb y  "
                + " from v_scdt_jwxx t " +
                " where xzb is not null and yzb is not null "
                + String.format(" and jh = '%s' ", jh);
        List<Map<String, Object>> data = iLjrbService.querySql(sql, null);
        if (data.size() == 0) {
            sql = " select t2.sjjwzzbx x, t2.SJJWZZBY y from mv_azj01 t2 " +
                    " where sjjwzzbx is not null and sjjwzzby is not null "
                    + String.format(" and jh = '%s' ", jh);
            data = iZjfzService.querySql(sql, null);
        }
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("exist", data.size());
        String[] x = new String[2];
        if (data.size() > 0) {
            x[0] = data.get(0).get("X").toString();
            x[1] = data.get(0).get("Y").toString();
            temp.put("coor", x);
        } else {
            temp.put("coor", x);
        }
        result.setResult(temp);
        return result;
    }

    @RequestMapping(value = "/queryFocusWarnReal", method = RequestMethod.GET)
    public Result<?> queryFocusWarnReal(FocusWarnDto dto, HttpServletRequest req) {
        long startTime = System.currentTimeMillis();
        Date requestStartTime = new Date();
        Result<HashMap<String, Object>> result = new Result();
        HashMap<String, Object> temp = new HashMap<>();
        if (dto.getCheckFzqk()) {
            List<WarnItemDto> fzqklist = new ArrayList<>();
            Map<String, List<DrillInfoModel>> fzqk = iFzqkService.Calculate(5000, dto.getJh(),
                    "", "", 0, dto.getCoorX(), dto.getCoorY());
            final DrillInfoModel maxDanger = new DrillInfoModel();
            maxDanger.setCorr(-1);
            fzqk.forEach((s, list) -> {
                list.forEach(d -> {
                    if (Double.compare(d.getCorr(), maxDanger.getCorr()) > 0) {
                        try {
                            BeanUtils.copyProperties(maxDanger, d);
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                });
            });
            String sql = String.format("select * from jsra where jh ='%s' and sgxh= '%s' ",
                    maxDanger.getAlbh(), maxDanger.getJh());
            List<Map<String, Object>> caseContent = iZjfzService.querySql(sql, null);
            if (caseContent.size() > 0) {
                Map<String, Object> first = caseContent.get(0);
                WarnItemDto record = new WarnItemDto();
                record.source = "复杂情况";
                record.jh = maxDanger.getAlbh() + "(案例井)";
                record.catogary = maxDanger.getFzmc();
                record.requestStart = requestStartTime;
                record.warnTime = new Date();
                record.jcrq = null;
                record.bjnr = NullOrStr(first.get("FSJGJYY"));
                record.clfs = NullOrStr(first.get("SGCLGK"));
                record.dsjs = NullOrStr(first.get("DSJS"));
                record.zjzh = NullOrStr(first.get("ZJZH"));
                record.serverCost = System.currentTimeMillis() - startTime;
                record.key = maxDanger.getJh() + maxDanger.getAlbh();
                fzqklist.add(record);
            }

            temp.put("fzqk", fzqklist);
        }
        if (dto.getCheckGc()) {
            long subStatic = System.currentTimeMillis();
            List<WarnItemDto> gcList = iEngineerApi.getHalfHourWarnResults(dto.getJh(), new Date());
            temp.put("gc", gcList);
        }
        // TODO: 拆函数、 去重
        if (dto.getCheckZjy()) {
            List<WarnItemDto> zjyList = new ArrayList<>();
            String t = String.format("ZJYCS_%s_BJ", dto.getJh().replace('-', '_'));
            long subStatic = System.currentTimeMillis();
            // region
            String sql = " with t1 as (\n" +
                    "                             select  sj, bjlx, to_char(sj, 'yyyyMMDD') || bjlx || '_' ||substr(bjxx, 0, 12)  bjpart, bjxx " +
                    "           from %s\n  " +
                    "               where %s \n" +
                    "                        ),\n" +
                    "\n" +
                    "                        tt1 as (\n" +
                    "                        select row_number() over (partition by  bjpart order by sj ) pn,  t1.* from t1),\n" +
                    "\n" +
                    "                        tt5 as (\n" +
                    "                        select bjpart, max(pn) maxPn from tt1 group by bjpart  ),\n" +
                    "\n" +
                    "                        tt3 as (\n" +
                    "                        select  tt2.sj nSj, tt5.maxpn, tt1.* from tt1 \n" +
                    "                        left join tt1 tt2 on tt2.pn = tt1.pn - 1 and tt1.bjpart = tt2.bjpart\n" +
                    "                        left join tt5 on tt5.bjpart = tt1.bjpart),\n" +
                    "\n" +
                    "                        tt6 as(\n" +
                    "                        select tt3.sj , tt3.nsj , round(to_number(tt3.sj - tt3.nsj)* 24 * 60 ) diffM, tt3.pn, tt3.maxpn, tt3.bjlx,  bjPart, tt3.bjxx  from tt3 \n" +
                    "                        order by bjpart, pn),\n" +
                    "                        tt9 as (\n" +
                    "                        select * from tt6  \n" +
                    "                        where diffM > 8 or maxpn = pn)\n" +
                    "\n" +
                    "                            select*\n" +
                    "                              from tt9\n" +
                    "                             order by sj ";
            // endregion
            String lastZjyTime = dto.getZjyLastTime() != null ?
                    String.format(" sj > to_date('%s', 'yyyy-MM-dd hh24:mi:ss') ", dto.getZjyLastTime())
                    : String.format(" sj > to_date((select to_char(max(sj), 'yyyy-MM-dd') || '00:00:01' from %s), 'yyyy-MM-dd hh24:mi:ss') ", t);

            sql = String.format(sql, t, lastZjyTime);
            List<Map<String, Object>> data = iZjyService.querySql(sql, null);
            data.forEach(k -> {
                WarnItemDto record = new WarnItemDto();
                record.source = "钻井液";
                record.catogary = GetZjyCatogaryStr(k.get("BJLX").toString());
                record.jh = dto.getJh();
                record.requestStart = requestStartTime;
                try {
                    record.warnTime = org.jeecg.common.util.DateUtils.parseDate(k.get("SJ").toString(), "yyyy-MM-dd HH:mm:ss");
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                record.jcrq = null;
                record.bjnr = k.get("BJXX").toString();
                record.clfs = "";
                record.dsjs = null;
                record.serverCost = System.currentTimeMillis() - subStatic;
                record.key = k.get("SJ") + "_" + k.get("BJLX");
                zjyList.add(record);
            });

            temp.put("zjy", zjyList);
        }

        long endTime = System.currentTimeMillis();

        result.setResult(temp);
        return result;
    }


    private String GetZjyCatogaryStr(String bjlx) {
        switch (bjlx) {
            case "0":
                return "门限报警";
            case "1":
                return "溢流报警";
            case "2":
                return "井漏报警";
            case "3":
                return "异常报警";
            default:
                return "未分类";
        }
    }

    private String NullOrStr(Object src) {
        if (src == null) {
            return null;
        } else {
            return src.toString();
        }
    }

    //region valuesMonitor
    @GetMapping(value = "queryValueMonitorLastHour")
    public Result<?> queryValueMonitoryLastHour(String ltd, String jh) {
        if (ltd.equals("1")) {
            return queryLtdLastHourData(jh);
        } else {
            return queryZjyLastHourData(jh);
        }
    }

    @GetMapping(value = "queryValueMonitorNew")
    public Result<?> queryValueMonitoryNewData(String ltd, String jh, String lastTime) {
        if (ltd.equals("1")) {
            return queryLtdNewData(jh, lastTime);
        } else {
            return queryZjyNewData(jh, lastTime);
        }
    }

    @RequestMapping(value = "/queryLtdLastHour", method = RequestMethod.GET)
    public Result<?> queryLtdLastHourData(String jh) {
        HashMap<String, Object> temp = new HashMap<>();
        String table = iLogicService.getTableCode(jh);
        String sql = " select *\n" +
                "  from %s \n" +
                " where time like\n" +
                "       (select substr(max(time), 0, 14) tl from %s) || '%%'";
        sql = String.format(sql, table, table);
        List<Map<String, Object>> data = iLtdSourceService.querySql(sql, null);
        temp.put("ltd", data);
        Result<HashMap<String, Object>> result = new Result();
        result.setResult(temp);
        return result;
    }

    @RequestMapping(value = "/getLtdNewData", method = RequestMethod.GET)
    public Result<?> queryLtdNewData(String jh, String lastTime) {
        HashMap<String, Object> temp = new HashMap<>();
        String table = iLogicService.getTableCode(jh);
        String sql = " select *\n" +
                "  from %s \n" +
                " where time > '%s'";
        sql = String.format(sql, table, lastTime);
        List<Map<String, Object>> data = iLtdSourceService.querySql(sql, null);
        temp.put("ltd", data);
        Result<HashMap<String, Object>> result = new Result();
        result.setResult(temp);
        return result;
    }

    @RequestMapping(value = "/queryZjyLastHour", method = RequestMethod.GET)
    public Result<?> queryZjyLastHourData(String jh) {
        HashMap<String, Object> temp = new HashMap<>();
        String table = String.format(" ZJYCS_%s ", jh.replace('-', '_'));
        String sql = " select t.rkmd,\n" +
                "       t.ckmd,\n" +
                "       t.rkll,\n" +
                "       t.ckll,\n" +
                "       t.yhbc,\n" +
                "       t.ehbc,\n" +
                "       t.ctj1,\n" +
                "       t.ctj2,\n" +
                "       t.ctj3,\n" +
                "       t.ctj4,\n" +
                "       t.sj TIME,\n" +
                "       t.cyl,\n" +
                "       t.lsl,\n" +
                "       t.ljbc,\n" +
                "       t.njyl,\n" +
                "       t.ztj \n" +
                "  from %s t \n" +
                "  where sj >= (select max(sj) - 1/24 from %s)\n" +
                "      order by sj ";
        sql = String.format(sql, table, table);
        List<Map<String, Object>> data = iZjyService.querySql(sql, null);
        temp.put("ltd", data);
        Result<HashMap<String, Object>> result = new Result();
        result.setResult(temp);
        return result;
    }

    @RequestMapping(value = "/getZjyNewData", method = RequestMethod.GET)
    public Result<?> queryZjyNewData(String jh, String lastTime) {
        HashMap<String, Object> temp = new HashMap<>();
        String table = String.format(" ZJYCS_%s ", jh.replace('-', '_'));
        String sql = " select  t.rkmd,\n" +
                "       t.ckmd,\n" +
                "       t.rkll,\n" +
                "       t.ckll,\n" +
                "       t.yhbc,\n" +
                "       t.ehbc,\n" +
                "       t.ctj1,\n" +
                "       t.ctj2,\n" +
                "       t.ctj3,\n" +
                "       t.ctj4,\n" +
                "       t.sj TIME,\n" +
                "       t.cyl,\n" +
                "       t.lsl,\n" +
                "       t.ljbc,\n" +
                "       t.njyl,\n" +
                "       t.ztj" +
                "  from %s t \n" +
                " where sj >  to_date('%s', 'yyyy-mm-dd hh24:mi:ss') \n" +
                " order by sj";
        sql = String.format(sql, table, lastTime);
        List<Map<String, Object>> data = iZjyService.querySql(sql, null);
        temp.put("ltd", data);
        Result<HashMap<String, Object>> result = new Result();
        result.setResult(temp);
        return result;
    }

    // endregion
    private List<Map<String, Object>> getWellEngineer(String jh) {
        List<Map<String, Object>> list = iLogicService.querySql(
                String.format("select  id, create_by, create_time, update_by, update_time, jh, large_text largeText from b_wellownEngineer where jh ='%s'", jh), null);
        if (list.size() > 0) {
        } else {
            list = iLogicService.querySql(
                    String.format("select  id, create_by, create_time, update_by, update_time, jh, large_text largeText from b_wellownEngineer where jh = '默认' ", jh), null);
        }
        return list;
    }

    @PostMapping(value = "/addCase")
    public Result<?> addCase(@RequestBody Jsra form) {
        String sql = "select nvl(max(sgxh),0) + 1 from jsra where jh = '%s'";
        sql = String.format(sql, form.getJh());
        int sgxh = iZjfzService.querySqlCount(sql, null);
        form.setSgxh(sgxh);

        iJsraService.save(form);
        return Result.ok("已保存");
    }

    @PutMapping(value = "/editCase")
    public Result<?> eidtCase(@RequestBody Jsra form) {
        QueryWrapper queryWrapper = new QueryWrapper<Jsra>();
        queryWrapper.eq("jh", form.getJh());
        queryWrapper.eq("sgxh", form.getAlbh());
        iJsraService.update(form, queryWrapper);
        return Result.ok("已更新");
    }

    @DeleteMapping(value = "/deleteCase")
    public Result<?> deleteCase(@RequestParam(name = "jh", required = true) String jh,
                                @RequestParam(name = "albh", required = true) String albh) {
        QueryWrapper queryWrapper = new QueryWrapper<Jsra>();
        queryWrapper.eq("jh", jh);
        queryWrapper.eq("sgxh", albh);
        iJsraService.remove(queryWrapper);
        return Result.ok("已删除");
    }

    @GetMapping(value = "/queryDerrickNow")
    public Result<?> queryDerrickNow(@RequestParam(name = "jh", required = true) String jh, String ltd) {
        Result<List<Map<String, Object>>> result = new Result();
        if (ltd.equals("1")) {
            String aTab = iLogicService.getTableCode(jh);
            String sql = " select * from %s where time = (select max(time) from %s)\n  ";
            sql = String.format(sql, aTab, aTab);

            List<Map<String, Object>> list = iLtdSourceService.querySql(sql, null);
            result.setResult(list);
        } else {
            String table = String.format("ZJYCS_%s", jh.replace('-', '_'));
            String sql = " select 0 HKH,\n" +
                    "       0 BITDEPTH,\n" +
                    "        t.rkmd,\n" +
                    "       t.ckmd,\n" +
                    "       t.rkll,\n" +
                    "       t.ckll FLOWOUT,\n" +
                    "       t.yhbc PUMP1,\n" +
                    "       t.ehbc PUMP2,\n" +
                    "       t.ctj1,\n" +
                    "       t.ctj2,\n" +
                    "       t.ctj3,\n" +
                    "       t.ctj4,\n" +
                    "       t.sj TIME,\n" +
                    "       t.cyl,\n" +
                    "       t.lsl,\n" +
                    "       t.ljbc,\n" +
                    "       t.njyl,\n" +
                    "       t.ztj PITTOTAL,\n" +
                    "       0 ACTPITTOTAL,\n" +
                    "       0 SPP, \n" +
                    "       '%s' JH " +
                    "  from %s t\n" +
                    " where sj = (select max(sj) from %s )";
            sql = String.format(sql, jh, table, table);

            List<Map<String, Object>> list = iZjyService.querySql(sql, null);
            result.setResult(list);
        }
        return result;
    }

    @GetMapping(value = "/queryStratum")
    public Result<?> queryStratumWithCountDatas(String jh, String distance) {
        String sql = " select  xzb x, yzb y  "
                + " from v_scdt_jwxx t " +
                " where xzb is not null and yzb is not null "
                + String.format(" and jh = '%s' ", jh);
        List<Map<String, Object>> data = iLjrbService.querySql(sql, null);
        if (data.size() == 0) {
            sql = " select t2.sjjwzzbx x, t2.SJJWZZBY y from mv_azj01 t2 " +
                    " where sjjwzzbx is not null and sjjwzzby is not null "
                    + String.format(" and jh = '%s' ", jh);
            data = iZjfzService.querySql(sql, null);
        }
        String[] x = new String[2];
        if (data.size() > 0) {
            x[0] = data.get(0).get("X").toString();
            x[1] = data.get(0).get("Y").toString();
        }
        // todo : 测试删除
//        x[0] = "4208317.98";
//        x[1] = "20607823.53";
        jh = "盐222-3";
//        if (data.size() == 0) {
//            return Result.ok(jh + "位置信息不完整，无法进行案例搜索。");
//        }

        // TODO: 补充层位信息
        String zzbX = x[0];// table[0]["SJJWZZBX"].ToString()
        String hzbY = x[1];// table[0]["SJJWHZBY"].ToString();
        sql = "select sjdjsd, sjhd, cw, sum(jh) sl\n" +
                "                      from (select sjdjsd, sjhd, cw, case when tt2.jh is null then 0 else 1 end JH\n" +
                "                              from cwxx tt1\n" +
                "                              left join (select t1.ID, t1.JH, t1.sglbid, t1.sgmc, t1.dsjs\n" +
                "                                          from jsra t1\n" +
                "                                          left join jjbxx t2\n" +
                "                                            on t2.jh = t1.jh\n" +
                "                                         where t1.sglbid is not null\n" +
                "                                           and sqrt(power((t2.sjjwzzbx - %s), 2) +\n" +
                "                                                    power((t2.sjjwhzby - %s), 2)) < %s\n" +
                "                                         order by t1.dsjs) tt2\n" +
                "                                on tt2.dsjs >= tt1. sjdjsd - sjhd\n" +
                "                               and tt2.dsjs < tt1.sjdjsd        \n" +
                "                             where tt1.jh = '%s'\n" +
                "                             order by sjdjsd)\n" +
                "                     group by sjdjsd, sjhd, cw\n" +
                "                     order by sjdjsd ";
        sql = String.format(sql, zzbX, hzbY, distance, jh);
        List<Map<String, Object>> cwData = iZjfzService.querySql(sql, null);
        HashMap<String, Object> temp = new HashMap<>();
        double bottom = 5000;
        temp.put("cw", cwData);
        temp.put("bottom", bottom);
        Result<HashMap<String, Object>> result = new Result<>();
        result.setResult(temp);
        return result;
    }

    @GetMapping(value = "/queryCaseInDistance")
    public Result<?> queryCaseInDistance(String jh, String distance) {
        Result<HashMap<String, Object>> result = new Result<>();
        HashMap<String, Object> temp = new HashMap<>();

        String sql = " select  xzb x, yzb y  "
                + " from v_scdt_jwxx t " +
                " where xzb is not null and yzb is not null "
                + String.format(" and jh = '%s' ", jh);
        List<Map<String, Object>> data = iLjrbService.querySql(sql, null);
        if (data.size() == 0) {
            sql = " select t2.sjjwzzbx x, t2.SJJWZZBY y from mv_azj01 t2 " +
                    " where sjjwzzbx is not null and sjjwzzby is not null "
                    + String.format(" and jh = '%s' ", jh);
            data = iZjfzService.querySql(sql, null);
        }
        String[] x = new String[2];
        if (data.size() > 0) {
            x[0] = data.get(0).get("X").toString();
            x[1] = data.get(0).get("Y").toString();
        }

        if (data.size() == 0) {
            temp.put("error", true);
            temp.put("msg", jh + "位置信息不完整，无法进行案例搜索。");
            result.setResult(temp);
            return result;
        }

        String zzbX = x[0]; // 4xxxxxx
        String hzbY = x[1]; // 2yyyyyyyy
        sql = "select t1.ID,t1.JH, SGXH, SGMC, DSJS, t3.name as caselevel, t2.sjjwzzbx, t2.sjjwhzby\n" +
                "                      from jsra t1\n" +
                "                      left join jjbxx t2\n" +
                "                        on t2.jh = t1.jh\n" +
                "                      left join Text_cl t3\n" +
                "                        on t3.id = t1.sgdjbh\n" +
                "                     where sglbid is not null\n" +
                "                       and sqrt(power((t2.sjjwzzbx - %s), 2) +\n" +
                "                                power((t2.sjjwhzby - %s), 2)) < %s\n" +
                "                     order by t1.dsjs ";
        sql = String.format(sql, zzbX, hzbY, distance);
        List<Map<String, Object>> list = iZjfzService.querySql(sql, null);

        temp.put("data", list);
        result.setResult(temp);
        return result;
    }

    @GetMapping(value = "queryCase")
    public Result<?> queryCase(String id) {
        Result<HashMap<String, Object>> result = new Result<>();
        String sql = String.format("select * from jsra where id ='%s'", id);
        List<Map<String, Object>> list = iZjfzService.querySql(sql, null);
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }

    @GetMapping(value = "queryCaseFromChart")
    public Result<?> queryCaseFromChart(@RequestParam(name = "jh", required = true) String jh,
                                        @RequestParam(name = "albh", required = true) int albh) {
        Result<HashMap<String, Object>> result = new Result<>();
        String sql = String.format("select * from jsra where jh ='%s' and sgxh= %s ", jh, albh);
        List<Map<String, Object>> list = iZjfzService.querySql(sql, null);
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }

    @GetMapping(value = "/queryWarnHistoryDay")
    public Result<?> queryWarnHistory(String jh) {
        String sql = "with tt1 as\n" +
                " (select to_char(warntime, 'yyyy-MM-dd') day, source, count(1) cn\n" +
                "    from B_WARNHISTORY t\n" +
                "   where jh = '%s'\n" +
                "   group by to_char(warntime, 'yyyy-MM-dd'), source),\n" +
                "tt2 as\n" +
                " (select null day, null fzqk, null gcyj, null zjy, null total from dual),\n" +
                "tt3 as\n" +
                " (select * from tt2 join (select distinct (day) day1 from tt1) kk1 on 1 = 1),\n" +
                "tt4 as\n" +
                " (select nvl(kk1.cn, 0) gcyj,\n" +
                "         nvl(kk2.cn, 0) zjy,\n" +
                "         nvl(kk3.cn, 0) fzqk,\n" +
                "         tt3.day1 \"DATE\"\n" +
                "    from tt3\n" +
                "    left join (select * from tt1 where source = '工程预警') kk1\n" +
                "      on kk1.day = tt3.day1\n" +
                "    left join (select * from tt1 where source = '钻井液') kk2\n" +
                "      on kk2.day = tt3.day1\n" +
                "    left join (select * from tt1 where source = '复杂情况') kk3\n" +
                "      on kk3.day = tt3.day1)\n" +
                "\n" +
                "select row_number() over(order by \"DATE\") \"lineNum\",\n" +
                "       gcyj \"gcyj\",\n" +
                "       zjy \"zjy\",\n" +
                "       fzqk \"fzqk\",\n" +
                "       tt4.gcyj + tt4.zjy + tt4.fzqk \"total\",  \n" +
                "       tt4.\"DATE\" \"date\" \n" +
                "  from tt4\n" +
                " ORDER BY \"DATE\" ";
        sql = String.format(sql, jh);
        List<Map<String, Object>> list = iLogicService.querySql(sql, null);
        Result<HashMap<String, Object>> result = new Result<>();
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }


    @GetMapping(value = "/queryWarnHistoryHour")
    public Result<?> queryWarnHistoryHour(String jh) {
        String sql = "with tt1 as\n" +
                " (select to_char(warntime, 'yyyy-MM-dd hh24') day, source, count(1) cn\n" +
                "    from B_WARNHISTORY t\n" +
                "   where jh = '%s'\n" +
                "   group by to_char(warntime, 'yyyy-MM-dd hh24'), source),\n" +
                "tt2 as\n" +
                " (select to_char(level - 1, '09') hh,\n" +
                "         null day,\n" +
                "         null fzqk,\n" +
                "         null gcyj,\n" +
                "         null zjy,\n" +
                "         null total\n" +
                "    from dual\n" +
                "  connect by 0 + level <= 24),\n" +
                "tt3 as\n" +
                " (select *\n" +
                "    from tt2\n" +
                "    join (select distinct (substr(day, 0, 10)) day1 from tt1) kk1\n" +
                "      on 1 = 1),\n" +
                "tt5 as\n" +
                " (select tt3.*, day1 || hh day2 from tt3),\n" +
                "tt4 as\n" +
                " (select nvl(kk1.cn, 0) gcyj,\n" +
                "         nvl(kk2.cn, 0) zjy,\n" +
                "         nvl(kk3.cn, 0) fzqk,\n" +
                "         tt5.day2 \"DATE\",\n" +
                "         to_number(tt5.hh) hh\n" +
                "    from tt5\n" +
                "    left join (select * from tt1 where source = '工程预警') kk1\n" +
                "      on kk1.day = tt5.day2\n" +
                "    left join (select * from tt1 where source = '钻井液') kk2\n" +
                "      on kk2.day = tt5.day2\n" +
                "    left join (select * from tt1 where source = '复杂情况') kk3\n" +
                "      on kk3.day = tt5.day2)\n" +
                "\n" +
                "select row_number() over(order by \"DATE\") * 10000 + hh  \"lineNum\",\n" +
                "       gcyj \"gcyj\",\n" +
                "       zjy \"zjy\",\n" +
                "       fzqk \"fzqk\",\n" +
                "       substr(tt4.\"DATE\",0, 10) \"date\",\n" +
                "       hh \"hour\",\n" +
                "       tt4.gcyj + tt4.zjy + tt4.fzqk \"total\"\n" +
                "  from tt4\n" +
                " ORDER BY \"DATE\" ";
        sql = String.format(sql, jh);
        List<Map<String, Object>> list = iLogicService.querySql(sql, null);
        Result<HashMap<String, Object>> result = new Result<>();
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }

    public static Map transformLowerCase(Map orgMap) {
        Map resultMap = new HashMap<>();
        if (orgMap == null || orgMap.isEmpty()) {
            return resultMap;
        }
        Set keySet = orgMap.keySet();
        for (Object key : keySet) {
            String newKey = key.toString().toLowerCase();
            resultMap.put(newKey, orgMap.get(key));
        }
        return resultMap;
    }

    @GetMapping(value = "queryLastRow")
    public Result<?> queryLastRow(String ltd, String jh, String jwss) {

        HashMap<String, Object> temp = new HashMap<>();
        if (ltd.equals("1")) {
            if (jwss.equals("Y")) {
                List<Ltd> list = ltdService.selectJwssList(jh, "");
                temp.put("data", list);
            } else {
                String table = iLogicService.getTableCode(jh);
                String sql = " select *\n" +
                        "  from %s\n" +
                        " WHERE TIME = (SELECT MAX(TIME) FROM %s)";
                sql = String.format(sql, table, table);
                List<Map<String, Object>> data = iLtdSourceService.querySql(sql, null);
                temp.put("data", data);
            }

        } else {
            try {
                String table = String.format(" ZJYCS_%s ", jh.replace('-', '_'));
                String sql = " select *\n" +
                        "  from %s t\n" +
                        " where sj = (select max(sj) from %s)";
                sql = String.format(sql, table, table);
                List<Map<String, Object>> data = iZjyService.querySql(sql, null);
                temp.put("data", data);
            } catch (Exception ex) {
                return Result.error("sql查询时出错," + ex.getMessage());
            }
        }
        Result<HashMap<String, Object>> result = new Result();
        result.setResult(temp);
        return result;
    }



    /**
     * 新的获取井列表
     *
     * @param jh
     * @param ejdw
     * @param jwss
     * @param pageNo
     * @param pageSize
     * @return
     */
    @RequestMapping(value = "/getJhWarnList", method = RequestMethod.GET)
    public Result<?> getJhWarnList(String jh, String ejdw, String jwss,
                                              @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                              @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize
    ) {

        Result<HashMap<String, Object>> result = new Result<>();
        List<Map<String, Object>> list = new ArrayList<>();
        String where = "";

        String sql = "select ID,DH,ZJY zjy,SGYQ,SJDW,KZRQ,ZJYDAY zjyDay,DT,EJDW,JJRQ,LTD ltd, JB,JX,SGDD,JFDW,WARNTIME,WARNCOUNT, ";
        sql += "LTDDAY ltdDay,JH,(select x.jh from B_WELLOWNENGINEER x where x.jh=O.jh) mx from A_JH O where 1=1 " + where;
        if (StringUtils.isNotEmpty(jh)) {
            sql += String.format(" and JH like '%%%s%%' ", jh);
        }
        if (StringUtils.isNotEmpty(ejdw)) {
            sql += "and EJDW= '" + ejdw + "'";
        }


        sql += "order by EJDW asc,WARNTIME desc NULLS LAST";
        int start = pageNo * pageSize - pageSize;
        int end = pageNo * pageSize;
        String condition = "";
        String pageSql = "select * from (select rownum a,t.* from (" + sql + " " + condition + ") t where rownum<=" + end + ") where a>" + start;
        String countSql = " select count(*) from (" + sql.toString() + " ）" + condition;
        int count = ajhService.querySqlCount(countSql, null);
        list = ajhService.querySql(pageSql, null);


        for (Map<String, Object> itemMap : list) {

            BigDecimal ltd = new BigDecimal(itemMap.get("LTD").toString());
            itemMap.put("ltd", ltd);
            Object ltdDay = itemMap.get("LTDDAY");
            itemMap.put("ltdDay", ltdDay);
            BigDecimal zjy = new BigDecimal(itemMap.get("ZJY").toString());
            itemMap.put("zjy", zjy);
            Object zjyDay = itemMap.get("ZJYDAY");
            itemMap.put("zjyDay", zjyDay);
            Object newWarnTime = itemMap.get("WARNTIME");
            itemMap.put("newWarnTime", newWarnTime);
            Object warnCount = itemMap.get("WARNCOUNT");
            itemMap.put("warnCount", warnCount == null ? 0 : warnCount);

        }
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("totalCount", count);
        temp.put("pageSize", pageSize);
        temp.put("pageNo", pageNo);
        temp.put("totalPage", Math.ceil(count / Double.parseDouble(pageSize.toString())));
        temp.put("data", list);
        result.setResult(temp);
        return result;
    }



}
