package com.ruoyi.project.zjsgfa.controller;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;

import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.data.PictureRenderData;
import com.deepoove.poi.data.PictureType;
import com.deepoove.poi.data.Pictures;
import com.deepoove.poi.plugin.table.LoopColumnTableRenderPolicy;
import com.deepoove.poi.plugin.table.LoopRowTableRenderPolicy;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.bean.BeanUtils;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.config.ServerConfig;
import com.ruoyi.framework.web.domain.BaseEntity;
import com.ruoyi.project.zjsgfa.domain.*;
import com.ruoyi.project.zjsgfa.domain.Vo.DrillingFluidConstant;
import com.ruoyi.project.zjsgfa.mapper.*;

import com.ruoyi.project.zjsgfa.service.ISjZjyFdxnbService;
import com.ruoyi.project.zjsgfa.util.CxszhUtil;
import com.ruoyi.project.zjsgfa.util.ImageProcessUtil;
import com.ruoyi.project.zt.domain.CommonParam;
import com.ruoyi.project.zt.domain.LjDzfc;
import com.ruoyi.project.zt.domain.Ljjw;
import com.ruoyi.project.zt.service.DjdcService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.http.MediaType;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.ruoyi.framework.aspectj.lang.annotation.Log;
import com.ruoyi.framework.aspectj.lang.enums.BusinessType;
import com.ruoyi.project.zjsgfa.service.ISjDjjcService;
import com.ruoyi.framework.web.controller.BaseController;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.framework.web.page.TableDataInfo;
import org.springframework.web.multipart.MultipartFile;
import com.deepoove.poi.config.Configure;
import org.apache.commons.codec.binary.Base64;

/**
 * 设计信息-井基础信息Controller
 * 
/**
 * 设计信息-井基础信息Controller
 * 
 * @author ruoyi
 * @date 2025-06-23
 */
@RestController
@RequestMapping("/designInformation/sjDjjc")
public class SjDjjcController extends BaseController
{
    @Autowired
    private ISjDjjcService sjDjjcService;


    @Autowired
    private SjDzfcMapper sjDzfcMapper;

    @Autowired
    private SjJsjgMapper sjJsjgMapper;

    @Autowired
    private SjJygjInfoMapper sjJygjInfoMapper;

    @Autowired
    private SjJygjGdfdcsMapper sjJygjGdfdcsMapper;

    @Autowired
    private SjJygjGdsjgdcsMapper sjJygjGdsjgdcsMapper;


    @Autowired
    private SjZjyFdsjMapper sjZjyFdsjMapper;


    @Autowired
    private SjZjyCljlMapper sjZjyCljlMapper;

    @Autowired
    private SjZjyFdxnbMapper sjZjyFdxnbMapper;

    @Autowired
    private SjTgTgzMapper sjTgTgzMapper;

    @Autowired
    private SjTgTgxnsjMapper sjTgTgxnsjMapper;

    @Autowired
    private SjTgTgqdjhMapper sjTgTgqdjhMapper;

    @Autowired
    private SjZlyqMapper sjZlyqMapper;
    @Autowired
    private SjZlyqZbyqMapper sjZlyqZbyqMapper;
    @Autowired
    private SjZlyqXxMapper sjZlyqXxMapper;
    @Autowired
    private SjLjsmMapper sjLjsmMapper;
    @Autowired
    private SjZysxMapper sjZysxMapper;
    @Autowired
    private SjH2sMapper sjH2sMapper;
    @Autowired
    private SjGztzMapper sjGztzMapper;

    @Autowired
    private ServerConfig serverConfig;

    @Autowired
    private DjdcService djdcService;

    @Autowired
    private SjDcfxDzfcMapper sjDcfxDzfcMapper;

    @Autowired
    private SjFdsgcsDcyxMapper sjFdsgcsDcyxMapper;

    @Autowired
    private SjFdsgcsMapper sjFdsgcsMapper;
    @Autowired
    private ClFxsbMapper clFxsbMapper;

    @Autowired
    private SjJygjgdsjMapper sjJygjgdsjMapper;

    @Autowired
    private ISjZjyFdxnbService sjZjyFdxnbService;

    @Autowired
    private SjLjjwMapper sjLjjwMapper;

    @Autowired
    private SjZqfxMapper sjZqfxMapper;

    @Autowired
    private SjZtgjsjMapper sjZtgjsjMapper;

    @Autowired
    private SjQkztfxMapper sjQkztfxMapper;

    @Autowired
    private SjSzfxjgMapper sjSzfxjgMapper;

    @Autowired
    private SjDcfxTsyxMapper sjDcfxTsyxMapper;

    @Autowired
    private SjJhzqMapper sjJhzqMapper;

    @Autowired
    private SjSggyZjyCljlMapper sjSggyZjyCljlMapper;


    @Autowired
    private SjSggyZjyFdsjMapper sjSggyZjyFdsjMapper;

    @Autowired
    private SjSggyZjyFdxnbMapper sjSggyZjyFdxnbMapper;

    @Autowired
    private SjFdsgcsZjyFdxnbMapper sjFdsgcsZjyFdxnbMapper;







    /**
     * 查询设计信息-井基础信息列表
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:list')")
    @GetMapping("/list")
    public TableDataInfo list(SjDjjc sjDjjc)
    {
        startPage();
        List<SjDjjc> list = sjDjjcService.selectSjDjjcList(sjDjjc);
        return getDataTable(list);
    }

    @GetMapping("/getList")
    public AjaxResult apiList(SjDjjc sjDjjc)
    {
//        startPage();


        List<SjDjjc> list = sjDjjcService.selectSjDjjcList(sjDjjc);
        Map<String,Object> map=new HashMap<>();



        map.put("jh", "井号");
        map.put("jb", "井别");
        map.put("jx", "井型");
        map.put("qk", "区块");
        map.put("jkhzb", "井口横坐标");
        map.put("jkzzb", "井口纵坐标");
        map.put("gzwz", "构造位置");
        map.put("wzcw", "完钻层位");
        map.put("ztmd", "钻探目的");
        map.put("wzyz", "完钻原则");
        map.put("wjfa", "完井方法");
        map.put("createdBy", "创建人");
        map.put("createdTime", "创建时间");
        map.put("zjgs", "钻井公司");
        map.put("zjd", "钻井队");
        map.put("jdhzb", "井底横坐标");
        map.put("jdzzb", "井底纵坐标");
        return AjaxResult.success(list,map);
    }

    /**
     * 获取井号下拉框
     * @param sjDjjc
     * @return
     */
    @GetMapping("/selectList")
    public AjaxResult selectList(SjDjjc sjDjjc)
    {
        List<SjDjjc> list = sjDjjcService.selectSjDjjcList(sjDjjc);
        return AjaxResult.success(list);
    }


    /**
     * 导出设计信息-井基础信息列表
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:export')")
    @Log(title = "设计信息-井基础信息", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, SjDjjc sjDjjc)
    {
        List<SjDjjc> list = sjDjjcService.selectSjDjjcList(sjDjjc);
        ExcelUtil<SjDjjc> util = new ExcelUtil<SjDjjc>(SjDjjc.class);
        util.exportExcel(response, list, "设计信息-井基础信息数据");
    }

    /**
     * 获取设计信息-井基础信息详细信息
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:query')")
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id)
    {
        return success(sjDjjcService.selectSjDjjcById(id));
    }

    /**
     * 新增设计信息-井基础信息
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:add')")
    @Log(title = "设计信息-井基础信息", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody SjDjjc sjDjjc)
    {
        int i = sjDjjcService.insertSjDjjc(sjDjjc);
        if(i==3){
            return AjaxResult.error("该井号已存在！");
        }else {
            return toAjax(i);
        }

    }

    /**
     * 修改设计信息-井基础信息
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:edit')")
    @Log(title = "设计信息-井基础信息", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody SjDjjc sjDjjc)
    {
        return toAjax(sjDjjcService.updateSjDjjc(sjDjjc));
    }

    /**
     * 删除设计信息-井基础信息
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:remove')")
    @Log(title = "设计信息-井基础信息", businessType = BusinessType.DELETE)
	@DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids)
    {
        return toAjax(sjDjjcService.deleteSjDjjcByIds(ids));
    }



    /**
     * 下载导入模板
     */
    @RequestMapping(value = "/downdrmb", method = {RequestMethod.GET, RequestMethod.POST})
    public void export(HttpServletResponse response)throws IOException {
        Workbook exl = null;
        OutputStream out = null;
        InputStream in  = null;
        try {
            in =this.getClass().getResourceAsStream("/static/excel/sjdrmb.xlsx");
            exl = WorkbookFactory.create(in);
            out = response.getOutputStream();
            response.reset();
            String filename = URLEncoder.encode("导入模板.xlsx", "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);



            exl.write(out);
            exl.close();
        }catch (Exception e){
            e.getMessage();
            throw new IOException();
        }finally{
            if (exl != null) {
                try {
                    exl.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }



    /**
     * 导入数据
     */
    @PreAuthorize("@ss.hasPermi('designInformation:sjDjjc:importData')")
    @PostMapping("/importData")
    @ResponseBody
    public AjaxResult importData(MultipartFile file, boolean updateSupport,String jh) throws Exception
    {
        XSSFWorkbook workbook = null;
        try {
            //装载流
            workbook = new XSSFWorkbook(file.getInputStream());
            // 获取井基础信息表，下标从0开始
            XSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            // 通过循环，逐行取出表中每行数据
            for(int i=0;i<=lastRowNum;i++){
                //跳过第一行
                if(i==0){
                    continue;
                }
                // 获取行
                XSSFRow row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjDjjc info=new SjDjjc();
//                jh=new DataFormatter().formatCellValue(row.getCell(0));
//                if(StringUtils.isEmpty(jh)){
//                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井号不能为空");
//                }
                info.setJh(jh);
                String qk=new DataFormatter().formatCellValue(row.getCell(1));
                if(StringUtils.isEmpty(qk)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行区块不能为空");
                }
                info.setQk(qk);
                String jb=new DataFormatter().formatCellValue(row.getCell(2));
                if(StringUtils.isEmpty(jb)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井别不能为空");
                }
                info.setJb(jb);

                String jx=new DataFormatter().formatCellValue(row.getCell(3));
                if(StringUtils.isEmpty(jx)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井型不能为空");
                }
                info.setJx(jx);

                String jkhzb=new DataFormatter().formatCellValue(row.getCell(4));
                if(StringUtils.isEmpty(jkhzb)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井口横坐标不能为空");
                }
                info.setJkhzb(Double.parseDouble(jkhzb));

                String jkzzb=new DataFormatter().formatCellValue(row.getCell(5));
                if(StringUtils.isEmpty(jkzzb)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井口纵坐标不能为空");
                }
                info.setJkzzb(Double.parseDouble(jkzzb));

                String jdhzb=new DataFormatter().formatCellValue(row.getCell(6));
                if(StringUtils.isEmpty(jdhzb)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井底横坐标不能为空");
                }
                info.setJdhzb(Double.parseDouble(jdhzb));


                String jdzzb=new DataFormatter().formatCellValue(row.getCell(7));
                if(StringUtils.isEmpty(jdzzb)){
                    return AjaxResult.error("井基础信息中第"+(i+1)+"行井底纵坐标不能为空");
                }
                info.setJdzzb(Double.parseDouble(jdzzb));

                String gzwz=new DataFormatter().formatCellValue(row.getCell(8));
                info.setGzwz(gzwz);
                String wzcw=new DataFormatter().formatCellValue(row.getCell(9));
                info.setWzcw(wzcw);
                String ztmd=new DataFormatter().formatCellValue(row.getCell(10));
                info.setZtmd(ztmd);
                String wzyz=new DataFormatter().formatCellValue(row.getCell(11));
                info.setWzyz(wzyz);
                String wjff=new DataFormatter().formatCellValue(row.getCell(12));
                info.setWjfa(wjff);
                String zjgs=new DataFormatter().formatCellValue(row.getCell(13));
                info.setZjgs(zjgs);
                String zjd=new DataFormatter().formatCellValue(row.getCell(14));
                info.setZjd(zjd);
                sjDjjcService.updateSjDjjc(info);
            }
            //地质分层---------------------------------------------------
            sheet = workbook.getSheetAt(1);
            lastRowNum = sheet.getLastRowNum();
            List<SjDzfc> sjDzfcList =new ArrayList<>();
            XSSFRow row2 =sheet.getRow(1);

//            String sjjh=new DataFormatter().formatCellValue(  row2.getCell(5));
            String yjjh1=new DataFormatter().formatCellValue(  row2.getCell(9));
            String yjjh2=new DataFormatter().formatCellValue(  row2.getCell(13));

            for(int i=4 ; i<=lastRowNum;i++){
                XSSFRow row = sheet.getRow(i);
                if(row==null){
                    continue;
                }

                SjDzfc sjDzfc =new SjDzfc();
                sjDzfc.setSjjh(jh);
                sjDzfc.setYjjh1(yjjh1);
                sjDzfc.setYjjh2(yjjh2);
                String dcJ=new DataFormatter().formatCellValue(  row.getCell(0));
                sjDzfc.setDcJ(dcJ);

                String dcX=new DataFormatter().formatCellValue(  row.getCell(1));
                sjDzfc.setDcX(dcX);


                String dcT=new DataFormatter().formatCellValue(  row.getCell(2));
                sjDzfc.setDcT(dcT);

                String dcZ=new DataFormatter().formatCellValue(  row.getCell(3));
                sjDzfc.setDcZ(dcZ);

                String dcD=new DataFormatter().formatCellValue(  row.getCell(4));
                sjDzfc.setDcD(dcD);

                String sjdcs=new DataFormatter().formatCellValue(  row.getCell(5));
                if(StringUtils.isNotEmpty(sjdcs)){
                    sjDzfc.setSjdcs(Double.parseDouble(sjdcs));
                }


                String sjjcgx=new DataFormatter().formatCellValue(  row.getCell(6));

                sjDzfc.setSjjcgx(sjjcgx);

                String sjhd=new DataFormatter().formatCellValue(  row.getCell(7));
                if(StringUtils.isNotEmpty(sjhd)){
                    sjDzfc.setSjhd(Double.parseDouble(sjhd));
                }


                String sjddsd=new DataFormatter().formatCellValue(  row.getCell(8));
                if(StringUtils.isNotEmpty(sjddsd)){
                    sjDzfc.setSjddsd(Double.parseDouble(sjddsd));
                }


                String yjdcs1=new DataFormatter().formatCellValue(  row.getCell(9));
                if(StringUtils.isNotEmpty(yjdcs1)){
                    sjDzfc.setYjdcs1(Double.parseDouble(yjdcs1));
                }


                String yjjcgx1=new DataFormatter().formatCellValue(  row.getCell(10));
                sjDzfc.setYjjcgx1(yjjcgx1);

                String yjhyjd1=new DataFormatter().formatCellValue(  row.getCell(11));
                sjDzfc.setYjhyjd1(yjhyjd1);

                String yjddsd1=new DataFormatter().formatCellValue(  row.getCell(12));
                if(StringUtils.isNotEmpty(yjddsd1)){
                    sjDzfc.setYjddsd1(Double.parseDouble(yjddsd1));
                }



                String yjdcs2=new DataFormatter().formatCellValue(  row.getCell(13));
                if(StringUtils.isNotEmpty(yjdcs2)){
                    sjDzfc.setYjdcs2(Double.parseDouble(yjdcs2));
                }


                String yjjcgx2=new DataFormatter().formatCellValue(  row.getCell(14));
                sjDzfc.setYjjcgx2(yjjcgx2);

                String yjhyjd2=new DataFormatter().formatCellValue(  row.getCell(15));
                sjDzfc.setYjhyjd2(yjhyjd2);

                String yjddsd2=new DataFormatter().formatCellValue(  row.getCell(16));
                if(StringUtils.isNotEmpty(yjddsd2)){
                    sjDzfc.setYjddsd2(Double.parseDouble(yjddsd2));
                }


                sjDzfcList.add(sjDzfc);
            }
            if(sjDzfcList.size()>0){
                sjDzfcMapper.deleteSjDzfcByJh(jh);
                sjDzfcMapper.insertSjDzfcBatch(sjDzfcList);
            }
            //地质分层 end--------------------------------------------------



            //井身结构---------------------------------------------------
            sheet = workbook.getSheetAt(2);
            lastRowNum = sheet.getLastRowNum();
            List<SjJsjg> sjJsjgList=new ArrayList<>();
            for(int i=0;i<=lastRowNum;i++){
                if(i==0 || i==1){
                    continue;
                }
                SjJsjg sjJsjg =new SjJsjg();
                sjJsjg.setJh(jh);
                XSSFRow row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                String kc=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(kc)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行开次不能为空");
                }
                sjJsjg.setKc(kc);

                String ztzj=new DataFormatter().formatCellValue(row.getCell(1));
                if(StringUtils.isEmpty(ztzj)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行钻头直径不能为空");
                }
                sjJsjg.setZtzj(Double.parseDouble(ztzj));

                String js=new DataFormatter().formatCellValue(row.getCell(2));
                if(StringUtils.isEmpty(js)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行井深不能为空");
                }
                sjJsjg.setJs(Double.parseDouble(js));


                String tgwj=new DataFormatter().formatCellValue(row.getCell(3));
                if(StringUtils.isEmpty(tgwj)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行套管外径不能为空");
                }
                sjJsjg.setTtwj(Double.parseDouble(tgwj));

                String tgds=new DataFormatter().formatCellValue(row.getCell(4));
                if(StringUtils.isEmpty(tgds)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行套管顶深不能为空");
                }
                sjJsjg.setTtds(Double.parseDouble(tgds));

                String tgxs=new DataFormatter().formatCellValue(row.getCell(5));
                if(StringUtils.isEmpty(tgxs)){
                    return AjaxResult.error("井身结构表第"+(i+1)+"行套管下深不能为空");
                }
                sjJsjg.setTtxs(Double.parseDouble(tgxs));

                String snfg=new DataFormatter().formatCellValue(row.getCell(6));
                if(StringUtils.isEmpty(snfg)){
                    sjJsjg.setSnfg(Double.parseDouble("0"));
                }else {
                    sjJsjg.setSnfg(Double.parseDouble(snfg));
                }
                String bz=new DataFormatter().formatCellValue(row.getCell(7));
                sjJsjg.setBz(bz);
                sjJsjgList.add(sjJsjg);
            }


            if(sjJsjgList.size()>0){
                //先删除该井数据
                sjJsjgMapper.deleteSjJsjgByJh(jh);
                //执行插入
                sjJsjgMapper.insertSjJsjgBatch(sjJsjgList);
            }

            //井身结构 end---------------------------------------------------

            //井眼轨迹 ---------------------------------------------------
            sheet = workbook.getSheetAt(3);
            lastRowNum = sheet.getLastRowNum();
            XSSFRow row = sheet.getRow(0);

            SjJygjInfo info =new SjJygjInfo();
            info.setJh(jh);
            String gdlx=new DataFormatter().formatCellValue(row.getCell(1));
            if(StringUtils.isEmpty(gdlx)){
                return AjaxResult.error("井眼轨迹表第"+(1)+"行轨道类型不能为空");
            }
            info.setGdlx(gdlx);

            String jkhzb=new DataFormatter().formatCellValue(row.getCell(3));
            if(StringUtils.isEmpty(jkhzb)){
                return AjaxResult.error("井眼轨迹表第"+(1)+"行井口横坐标不能为空");
            }
            info.setJkhzb(Double.parseDouble(jkhzb));

            String jkzzb=new DataFormatter().formatCellValue(row.getCell(5));
            if(StringUtils.isEmpty(jkzzb)){
                return AjaxResult.error("井眼轨迹表第"+(1)+"行井口纵坐标不能为空");
            }
            info.setJkzzb(Double.parseDouble(jkzzb));

            String bdhzb=new DataFormatter().formatCellValue(row.getCell(7));
            if(StringUtils.isEmpty(bdhzb)){
                return AjaxResult.error("井眼轨迹表第"+(1)+"行靶点横坐标不能为空");
            }
            info.setBdhzb(Double.parseDouble(bdhzb));


            String bdzzb=new DataFormatter().formatCellValue(row.getCell(9));
            if(StringUtils.isEmpty(bdzzb)){
                return AjaxResult.error("井眼轨迹表第"+(1)+"行靶点纵坐标不能为空");
            }
            info.setBdzzb(Double.parseDouble(bdzzb));

            row = sheet.getRow(1);
            String cs=new DataFormatter().formatCellValue(row.getCell(1));
            if(StringUtils.isEmpty(cs)){
                return AjaxResult.error("井眼轨迹表第"+(2)+"行垂深不能为空");
            }
            info.setCs(Double.parseDouble(cs));
            String bhj=new DataFormatter().formatCellValue(row.getCell(3));
            if(StringUtils.isEmpty(bhj)){
                return AjaxResult.error("井眼轨迹表第"+(2)+"行闭合距不能为空");
            }
            info.setBhj(Double.parseDouble(bhj));

            String bhfx=new DataFormatter().formatCellValue(row.getCell(5));
            if(StringUtils.isEmpty(bhfx)){
                return AjaxResult.error("井眼轨迹表第"+(2)+"行闭合方向不能为空");
            }
            info.setBhfx(Double.parseDouble(bhfx));

            String bbj=new DataFormatter().formatCellValue(row.getCell(7));
            if(StringUtils.isEmpty(bbj)){
                return AjaxResult.error("井眼轨迹表第"+(2)+"行靶半径不能为空");
            }
            info.setBbj(Double.parseDouble(bbj));
            sjJygjInfoMapper.insertSjJygjInfo(info);
            //井眼轨迹 基础数据  end---------------------------------------------------

            //井眼轨迹 轨道分段参数---------------------------------------------------
//            sheet = workbook.getSheetAt(3);
//            lastRowNum = sheet.getLastRowNum();
            List<SjJygjGdfdcs> sjJygjGdfdcsList=new ArrayList<>();
            for(int i=5; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjJygjGdfdcs gdfdcs =new SjJygjGdfdcs();
                gdfdcs.setJh(jh);
                String js=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(js)){
                   continue;
                }
                gdfdcs.setJs(Double.parseDouble(js));
                String jx=new DataFormatter().formatCellValue(row.getCell(1));
                gdfdcs.setJx(Double.parseDouble(jx));
                String fw=new DataFormatter().formatCellValue(row.getCell(2));
                gdfdcs.setFw(Double.parseDouble(fw));
                cs=new DataFormatter().formatCellValue(row.getCell(3));
                gdfdcs.setCs(Double.parseDouble(cs));

                String spwy=new DataFormatter().formatCellValue(row.getCell(4));
                gdfdcs.setSpwy(Double.parseDouble(spwy));

                String nbwy=new DataFormatter().formatCellValue(row.getCell(5));
                gdfdcs.setNbwy(Double.parseDouble(nbwy));

                String dxwy=new DataFormatter().formatCellValue(row.getCell(6));
                gdfdcs.setDxwy(Double.parseDouble(dxwy));

                String qjbhl=new DataFormatter().formatCellValue(row.getCell(7));
                gdfdcs.setQjbhl(Double.parseDouble(qjbhl));


                String gjm=new DataFormatter().formatCellValue(row.getCell(8));
                gdfdcs.setGjm(Double.parseDouble(gjm));

                String bd=new DataFormatter().formatCellValue(row.getCell(9));
                gdfdcs.setBd(bd);
                sjJygjGdfdcsList.add(gdfdcs);
            }

            if(sjJygjGdfdcsList.size()>0){
                //先删除数据
                sjJygjGdfdcsMapper.deleteSjJygjGdfdcsByJh(jh);
                //执行插入

                sjJygjGdfdcsMapper.insertSjJygjGdfdcsBatch(sjJygjGdfdcsList);
            }

            //井眼轨迹 轨道分段参数 end---------------------------------------------------

            //井眼轨迹 轨道设计各点参数---------------------------------------------------
            List<SjJygjGdsjgdcs> sjJygjGdsjgdcsList=new ArrayList<>();
            for(int i=3; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjJygjGdsjgdcs gdfdcs =new SjJygjGdsjgdcs();
                gdfdcs.setJh(jh);
                int ks=11;
                String js=new DataFormatter().formatCellValue(row.getCell(ks));
                if(StringUtils.isEmpty(js)){
                    continue;
                }
                gdfdcs.setJs(Double.parseDouble(js));
                String jx=new DataFormatter().formatCellValue(row.getCell(ks+1));
                if(StringUtils.isNotEmpty(jx)){
                    gdfdcs.setJx(Double.parseDouble(jx));
                }

                String fw=new DataFormatter().formatCellValue(row.getCell(ks+2));
                if(StringUtils.isNotEmpty(fw)){
                    gdfdcs.setFw(Double.parseDouble(fw));
                }

                String bhfw=new DataFormatter().formatCellValue(row.getCell(ks+3));
                gdfdcs.setBhfw(Double.parseDouble(bhfw));

               String cs1=new DataFormatter().formatCellValue(row.getCell(ks+4));
                if(StringUtils.isNotEmpty(cs1)){
                    gdfdcs.setCs(Double.parseDouble(cs1));
                }


                bhj=new DataFormatter().formatCellValue(row.getCell(ks+5));
                if(StringUtils.isNotEmpty(bhj)){
                    gdfdcs.setBhj(Double.parseDouble(bhj));
                }


                String nbwy=new DataFormatter().formatCellValue(row.getCell(ks+6));
                if(StringUtils.isNotEmpty(nbwy)){
                    gdfdcs.setNbwy(Double.parseDouble(nbwy));
                }


                String dxwy=new DataFormatter().formatCellValue(row.getCell(ks+7));
                if(StringUtils.isNotEmpty(dxwy)){
                    gdfdcs.setDxwy(Double.parseDouble(dxwy));
                }



                String zxl=new DataFormatter().formatCellValue(row.getCell(ks+8));
                if(StringUtils.isNotEmpty(zxl)){
                    gdfdcs.setZxl(Double.parseDouble(zxl));
                }


                String fwbhl=new DataFormatter().formatCellValue(row.getCell(ks+9));
                if(StringUtils.isNotEmpty(fwbhl)){
                    gdfdcs.setFwbhl(Double.parseDouble(fwbhl));
                }


                String qjbhl=new DataFormatter().formatCellValue(row.getCell(ks+10));
                if(StringUtils.isNotEmpty(qjbhl)){
                    gdfdcs.setQjbhl(Double.parseDouble(qjbhl));
                }

                String gjm=new DataFormatter().formatCellValue(row.getCell(ks+11));
                if(StringUtils.isNotEmpty(gjm)){
                    gdfdcs.setGjm(Double.parseDouble(gjm));
                }

                //计算视平移
                double spy = calculateApparentDisplacement(Double.parseDouble(bhfw), Double.parseDouble(nbwy), Double.parseDouble(dxwy));
                gdfdcs.setSpy(spy);
                sjJygjGdsjgdcsList.add(gdfdcs);
            }

            if(sjJygjGdsjgdcsList.size()>0){
                sjJygjGdsjgdcsMapper.deleteSjJygjGdsjgdcsByJh(jh);
                sjJygjGdsjgdcsMapper.insertSjJygjGdsjgdcsBatch(sjJygjGdsjgdcsList);
            }
            //井眼轨迹 轨道设计各点参数 end---------------------------------------------------
            List<SjJygjgdsj> sjJygjgdsjList=new ArrayList<>();
            for(int i=3; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjJygjgdsj gdfdcs =new SjJygjgdsj();
                gdfdcs.setJh(jh);
                int ks=24;
                String jdcs=new DataFormatter().formatCellValue(row.getCell(ks));
                if(StringUtils.isEmpty(jdcs)){
                    continue;
                }
                gdfdcs.setJdcs(Double.parseDouble(jdcs));
                String jdbhj=new DataFormatter().formatCellValue(row.getCell(ks+1));
                if(StringUtils.isNotEmpty(jdbhj)){
                    gdfdcs.setJdbhj(Double.parseDouble(jdbhj));
                }

                String jdbhfw=new DataFormatter().formatCellValue(row.getCell(ks+2));
                if(StringUtils.isNotEmpty(jdbhfw)){
                    gdfdcs.setJdbhfw(Double.parseDouble(jdbhfw));
                }

                String zxd=new DataFormatter().formatCellValue(row.getCell(ks+3));
                gdfdcs.setZxd(Double.parseDouble(zxd));

                String zdjxj=new DataFormatter().formatCellValue(row.getCell(ks+4));
                if(StringUtils.isNotEmpty(zdjxj)){
                    gdfdcs.setZdjxj(Double.parseDouble(zdjxj));
                }


                String cqj=new DataFormatter().formatCellValue(row.getCell(ks+5));
                if(StringUtils.isNotEmpty(cqj)){
                    gdfdcs.setCqj(Double.parseDouble(cqj));
                }


                String ccqd=new DataFormatter().formatCellValue(row.getCell(ks+6));
                if(StringUtils.isNotEmpty(ccqd)){
                    gdfdcs.setCcqd(Double.parseDouble(ccqd));
                }


                String cpj=new DataFormatter().formatCellValue(row.getCell(ks+7));
                if(StringUtils.isNotEmpty(cpj)){
                    gdfdcs.setCpj(Double.parseDouble(cpj));
                }



                String slj=new DataFormatter().formatCellValue(row.getCell(ks+8));
                if(StringUtils.isNotEmpty(slj)){
                    gdfdcs.setSlj(Double.parseDouble(slj));
                }


                String fwxzj=new DataFormatter().formatCellValue(row.getCell(ks+9));
                if(StringUtils.isNotEmpty(fwxzj)){
                    gdfdcs.setFwxzj(Double.parseDouble(fwxzj));
                }



                sjJygjgdsjList.add(gdfdcs);
            }
            if(sjJygjgdsjList.size()>0){
                sjJygjgdsjMapper.deleteSjJygjgdsjByJh(jh);
                sjJygjgdsjMapper.insertSjJygjgdsjBatch(sjJygjgdsjList);
            }
            //钻井液  分段钻井液设计---------------------------------------------------
            sheet = workbook.getSheetAt(4);
            lastRowNum = sheet.getLastRowNum();

            List<SjZjyFdsj> sjZjyFdsjList =new ArrayList<>();
            List<SjSggyZjyFdsj> sjSggyZjyFdsjList =new ArrayList<>();
            for(int i=2; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjZjyFdsj fdsj =new SjZjyFdsj();
                fdsj.setJh(jh);
                String kc=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(kc)){
                    continue;
                }
                fdsj.setKc(kc);
                String jd=new DataFormatter().formatCellValue(row.getCell(1));
                fdsj.setJd(jd);

                String zjytx=new DataFormatter().formatCellValue(row.getCell(2));
                fdsj.setZjytx(zjytx);
                sjZjyFdsjList.add(fdsj);
                SjSggyZjyFdsj sjSggyZjyFdsj =new SjSggyZjyFdsj();
                BeanUtils.copyProperties(fdsj,sjSggyZjyFdsj);
                sjSggyZjyFdsjList.add(sjSggyZjyFdsj);
            }
            if(sjZjyFdsjList.size()>0){
                sjZjyFdsjMapper.deleteSjZjyFdsjByJh(jh);
                sjZjyFdsjMapper.insertSjZjyFdsjBatch(sjZjyFdsjList);
            }
            if(sjSggyZjyFdsjList.size()>0){
                sjSggyZjyFdsjMapper.deleteSjSggyZjyFdsjByJh(jh);
                sjSggyZjyFdsjMapper.insertSjSggyZjyFdsjBatch(sjSggyZjyFdsjList);
            }
            //钻井液  分段钻井液设计 end ---------------------------------------------------
            //钻井液  加量 ---------------------------------------------------
            List<SjZjyCljl> sjZjyCljlList =new ArrayList<>();
            List<SjSggyZjyCljl> sjSggyZjyCljlList =new ArrayList<>();
            for(int i=2; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjZjyCljl cljl =new SjZjyCljl();
                cljl.setJh(jh);
                int ks =5;
                String clmc=new DataFormatter().formatCellValue(row.getCell(ks));
                if(StringUtils.isEmpty(clmc)){
                    continue;
                }
                cljl.setClmc(clmc);
                String ykjl=new DataFormatter().formatCellValue(row.getCell(ks+1));
                cljl.setYkjl(ykjl);

                String ekjl=new DataFormatter().formatCellValue(row.getCell(ks+2));
                cljl.setEkjl(ekjl);

                String skjl=new DataFormatter().formatCellValue(row.getCell(ks+3));
                cljl.setSkjl(skjl);

                String sikjl=new DataFormatter().formatCellValue(row.getCell(ks+4));
                cljl.setSikjl(sikjl);
                String wkjl=new DataFormatter().formatCellValue(row.getCell(ks+5));
                cljl.setWkjl(wkjl);

                sjZjyCljlList.add(cljl);

                SjSggyZjyCljl sjSggyZjyCljl =new SjSggyZjyCljl();
                BeanUtils.copyProperties(cljl,sjSggyZjyCljl);
                sjSggyZjyCljlList.add(sjSggyZjyCljl);
            }
            if(sjZjyCljlList.size()>0){
                sjZjyCljlMapper.deleteSjZjyCljlByJh(jh);
                sjZjyCljlMapper.insertSjZjyCljlBatch(sjZjyCljlList);
            }
            if(sjSggyZjyCljlList.size()>0){
                sjSggyZjyCljlMapper.deleteSjSggyZjyCljlByJh(jh);
                sjSggyZjyCljlMapper.insertSjSggyZjyCljlBatch(sjSggyZjyCljlList);
            }
            //钻井液  加量 end ---------------------------------------------------

            //钻井液  分段钻井液性能设计表 ---------------------------------------------------
            List<SjZjyFdxnb> sjZjyFdxnbList = new ArrayList<>();
            List<SjSggyZjyFdxnb> sjSggyZjyFdxnbList = new ArrayList<>();
            List<SjFdsgcsZjyFdxnb> sjFdsgcsZjyFdxnbList = new ArrayList<>();
            DataFormatter dataFormatter = new DataFormatter();
            lastRowNum = sheet.getLastRowNum();

            // 第一步：解析表头，确定动态井段（从第 12 列开始，第 0 列是项目列，从第 1 列往后是井段列）
            Row headerRow = sheet.getRow(1); // 假设表头在第 0 行（根据实际 Excel 结构调整）
            int ksl=13;
            List<String> wellSegments = new ArrayList<>();
            if (headerRow != null) {
                int lastCellNum = headerRow.getLastCellNum();
                for (int k = 13; k < lastCellNum; k++) { // 第 0 列是“项目”，从第 1 列开始是井段

                    Cell cell = headerRow.getCell(k);
                    if (cell != null) {
                        String segment = dataFormatter.formatCellValue(cell);
                        if("0-451m或0～451m".equals(segment)){
                            ksl=14;
                            continue;
                        }
                        wellSegments.add(segment);
                    }
                }
            }
            for(int k=0;k<wellSegments.size();k++){
                String segment = wellSegments.get(k);
                SjZjyFdxnb fdxnb = new SjZjyFdxnb();
                fdxnb.setJh(jh);
                fdxnb.setJd(segment); // 设置井段

                for(int i=0;i<=lastRowNum;i++){
                    //获取数据行
                    Row xmRow =  sheet.getRow(i+2);
                    if(xmRow==null){
                        continue;
                    }
                    //项目名称
                    String xm = dataFormatter.formatCellValue(xmRow.getCell(12));
                    if(StringUtils.isEmpty(xm)){
                        continue;
                    }
                    String value = dataFormatter.formatCellValue(xmRow.getCell(k + ksl));

                    String fieldName = DrillingFluidConstant.ITEM_FIELD_MAP.get(xm);
                    if(StringUtils.isEmpty(fieldName)){
                        continue;
                    }
                    switch (fieldName) {
                        case "md":
                            fdxnb.setMd(value);
                            break;
                        case "msldnd":
                            fdxnb.setMsldnd(value);
                            break;
                        case "apilsl":
                            fdxnb.setApilsl(value);
                            break;
                        case "apinb":
                            fdxnb.setApinb(value);
                            break;
                        case "jql":
                            fdxnb.setJql(value);
                            break;
                        case "ph":
                            fdxnb.setPh(value);
                            break;
                        case "hsl":
                            fdxnb.setHsl(value);
                            break;
                        case "zgh":
                            fdxnb.setZgh(value);
                            break;
                        case "mzxs":
                            fdxnb.setMzxs(value);
                            break;
                        case "dql":
                            fdxnb.setDql(value);
                            break;
                        case "sxnd":
                            fdxnb.setSxnd(value);
                            break;
                        // 其他字段的 case 继续补充
                        default:
                            break;
                    }
                }
                sjZjyFdxnbList.add(fdxnb);
                SjSggyZjyFdxnb sjSggyZjyFdxnb =new SjSggyZjyFdxnb();
                BeanUtils.copyProperties(fdxnb,sjSggyZjyFdxnb);
                sjSggyZjyFdxnbList.add(sjSggyZjyFdxnb);
                SjFdsgcsZjyFdxnb sjFdsgcsZjyFdxnb =new SjFdsgcsZjyFdxnb();
                BeanUtils.copyProperties(fdxnb,sjFdsgcsZjyFdxnb);
                sjFdsgcsZjyFdxnbList.add(sjFdsgcsZjyFdxnb);
            }
            // 第三步：执行删除旧数据和插入新数据逻辑
            if (!sjZjyFdxnbList.isEmpty()) {
                sjZjyFdxnbMapper.deleteSjZjyFdxnbByJh(jh);
                sjZjyFdxnbMapper.insertSjZjyFdxnbBatch(sjZjyFdxnbList);
            }
            if(sjSggyZjyFdxnbList.size()>0){
                sjSggyZjyFdxnbMapper.deleteSjSggyZjyFdxnbByJh(jh);
                sjSggyZjyFdxnbMapper.insertSjSggyZjyFdxnbBatch(sjSggyZjyFdxnbList);
            }

            if(sjFdsgcsZjyFdxnbList.size()>0){
                double ksjs=0;
                for(int i=0;i<sjJsjgList.size();i++){
                    if(i==0){
                        ksjs=0;
                    }else {
                        ksjs=sjJsjgList.get(i-1).getJs();
                    }
                    Double js = sjJsjgList.get(i).getJs();
                    for(SjFdsgcsZjyFdxnb fdxnb:sjFdsgcsZjyFdxnbList){
                        if(StringUtils.isNotEmpty(fdxnb.getJd())){
                            String s = extractNumber(fdxnb.getJd());
                            if(StringUtils.isNotEmpty(s)){
                                double jsjs = Double.parseDouble(s);
                                if(jsjs>ksjs&&jsjs<=js){
                                    fdxnb.setKc(sjJsjgList.get(i).getKc());
                                }
                            }
                        }
                    }
                }

                sjFdsgcsZjyFdxnbMapper.deleteSjFdsgcsZjyFdxnbByJh(jh);
                sjFdsgcsZjyFdxnbMapper.insertSjFdsgcsZjyFdxnbBatch(sjFdsgcsZjyFdxnbList);

            }
            //钻井液  分段钻井液性能设计表  end---------------------------------------------------


            sheet = workbook.getSheetAt(5);
            lastRowNum = sheet.getLastRowNum();

            //套管  套管柱数据表---------------------------------------------------
            List<SjTgTgz> sjTgTgzList =new ArrayList<>();
            for(int i=4; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }

                SjTgTgz tgTgz =new SjTgTgz();
                tgTgz.setJh(jh);
                String kc=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(kc)){
                    continue;
                }

                tgTgz.setKc(kc);
                String jd=new DataFormatter().formatCellValue(row.getCell(1));
                tgTgz.setJd(jd);

                String wj=new DataFormatter().formatCellValue(row.getCell(2));
                if(StringUtils.isNotEmpty(wj)){
                    tgTgz.setWj(Double.parseDouble(wj));
                }


                String gj=new DataFormatter().formatCellValue(row.getCell(3));
                tgTgz.setGj(gj);

                String bh=new DataFormatter().formatCellValue(row.getCell(4));
                if(StringUtils.isNotEmpty(bh)){
                    tgTgz.setBh(Double.parseDouble(bh));
                }


                String kx=new DataFormatter().formatCellValue(row.getCell(5));
                tgTgz.setKx(kx);


                String zjsknj=new DataFormatter().formatCellValue(row.getCell(6));
                if(StringUtils.isNotEmpty(zjsknj)){
                    tgTgz.setZjsknj(Double.parseDouble(zjsknj));
                }


                String cd=new DataFormatter().formatCellValue(row.getCell(7));
                if(StringUtils.isNotEmpty(cd)){
                    tgTgz.setCd(Double.parseDouble(cd));
                }


                String zjymd=new DataFormatter().formatCellValue(row.getCell(8));
                if(StringUtils.isNotEmpty(zjymd)){
                    tgTgz.setZjymd(Double.parseDouble(zjymd));
                }


                sjTgTgzList.add(tgTgz);
            }
            if(sjTgTgzList.size()>0){
                sjTgTgzMapper.deleteSjTgTgzByJh(jh);
                sjTgTgzMapper.insertSjTgTgzBatch(sjTgTgzList);
            }

            //套管  套管柱数据表end---------------------------------------------------
            //套管  套管性能数据---------------------------------------------------
            List<SjTgTgxnsj> sjTgTgxnsjList =new ArrayList<>();

            for(int i=4; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjTgTgxnsj tgTgz =new SjTgTgxnsj();
                tgTgz.setJh(jh);
                int ks =11;
                String wj=new DataFormatter().formatCellValue(row.getCell(ks));
                if(StringUtils.isEmpty(wj)){
                    continue;
                }

                tgTgz.setWj(Double.parseDouble(wj));
                String gj=new DataFormatter().formatCellValue(row.getCell(ks+1));
                tgTgz.setGj(gj);

                String bh=new DataFormatter().formatCellValue(row.getCell(ks+2));
                if(StringUtils.isNotEmpty(bh)){
                    tgTgz.setBh(Double.parseDouble(bh));
                }


                String kx=new DataFormatter().formatCellValue(row.getCell(ks+3));
                tgTgz.setKx(kx);

                String mmzl=new DataFormatter().formatCellValue(row.getCell(ks+4));
                if(StringUtils.isNotEmpty(mmzl)){
                    tgTgz.setMmzl(Double.parseDouble(mmzl));
                }


                String jgwj=new DataFormatter().formatCellValue(row.getCell(ks+5));
                if(StringUtils.isNotEmpty(jgwj)){
                    tgTgz.setJgwj(Double.parseDouble(jgwj));
                }


                String klqd=new DataFormatter().formatCellValue(row.getCell(ks+6));
                if(StringUtils.isNotEmpty(klqd)){
                    tgTgz.setKlqd(Double.parseDouble(klqd));
                }


                String kjqd=new DataFormatter().formatCellValue(row.getCell(ks+7));
                if(StringUtils.isNotEmpty(kjqd)){
                    tgTgz.setKjqd(Double.parseDouble(kjqd));
                }



                String knyqd=new DataFormatter().formatCellValue(row.getCell(ks+8));
                if(StringUtils.isNotEmpty(knyqd)){
                    tgTgz.setKnyqd(Double.parseDouble(knyqd));
                }

                sjTgTgxnsjList.add(tgTgz);
            }
            if(sjTgTgxnsjList.size()>0){
                sjTgTgxnsjMapper.deleteSjTgTgxnsjByJh(jh);
                sjTgTgxnsjMapper.insertSjTgTgxnsjBatch(sjTgTgxnsjList);
            }
            //套管  套管性能数据end---------------------------------------------------
            //套管  套管柱强度校核---------------------------------------------------
            List<SjTgTgqdjh> sjTgTgqdjhList =new ArrayList<>();
            for(int i=4; i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjTgTgqdjh tgTgz =new SjTgTgqdjh();
                tgTgz.setJh(jh);
                int ks =22;
                String kc=new DataFormatter().formatCellValue(row.getCell(ks));
                if(StringUtils.isEmpty(kc)){
                    continue;
                }
                tgTgz.setKc(kc);
                String jd=new DataFormatter().formatCellValue(row.getCell(ks+1));
                tgTgz.setJd(jd);

                String mmzl=new DataFormatter().formatCellValue(row.getCell(ks+2));
                if(StringUtils.isNotEmpty(mmzl)){
                    tgTgz.setMmzl(Double.parseDouble(mmzl));
                }


                String djz=new DataFormatter().formatCellValue(row.getCell(ks+3));
                if(StringUtils.isNotEmpty(djz)){
                    tgTgz.setDjz(Double.parseDouble(djz));
                }


                String ljz=new DataFormatter().formatCellValue(row.getCell(ks+4));
                if(StringUtils.isNotEmpty(ljz)){
                    tgTgz.setLjz(Double.parseDouble(ljz));
                }


                String zdzhkwj=new DataFormatter().formatCellValue(row.getCell(ks+5));
                if(StringUtils.isNotEmpty(zdzhkwj)){
                    tgTgz.setZdzhkwj(Double.parseDouble(zdzhkwj));
                }


                String aqxskwj=new DataFormatter().formatCellValue(row.getCell(ks+6));
                if(StringUtils.isNotEmpty(aqxskwj)){
                    tgTgz.setAqxskwj(Double.parseDouble(aqxskwj));
                }


                String zdzhkny=new DataFormatter().formatCellValue(row.getCell(ks+7));
                if(StringUtils.isNotEmpty(zdzhkny)){
                    tgTgz.setZdzhkny(Double.parseDouble(zdzhkny));
                }



                String aqxskny=new DataFormatter().formatCellValue(row.getCell(ks+8));
                if(StringUtils.isNotEmpty(aqxskny)){
                    tgTgz.setAqxskny(Double.parseDouble(aqxskny));
                }


                String zdzhkl=new DataFormatter().formatCellValue(row.getCell(ks+9));
                if(StringUtils.isNotEmpty(zdzhkl)){
                    tgTgz.setZdzhkl(Double.parseDouble(zdzhkl));
                }


                String aqxskl=new DataFormatter().formatCellValue(row.getCell(ks+10));
                if(StringUtils.isNotEmpty(aqxskl)){
                    tgTgz.setAqxskl(Double.parseDouble(aqxskl));
                }

                sjTgTgqdjhList.add(tgTgz);
            }
            if(sjTgTgqdjhList.size()>0){
                sjTgTgqdjhMapper.deleteSjTgTgqdjhByJh(jh);
                sjTgTgqdjhMapper.insertSjTgTgqdjhBatch(sjTgTgqdjhList);
            }
            //套管  套管柱强度校核end---------------------------------------------------
            //质量要求  井身质量要求---------------------------------------------------
            sheet = workbook.getSheetAt(6);
            lastRowNum = sheet.getLastRowNum();
            List<SjZlyq> sjZlyqList =new ArrayList<>();
            for(int i=2;i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjZlyq sjZlyq =new SjZlyq();
                sjZlyq.setJh(jh);
                String jd=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(jd)){
                    continue;
                }
                sjZlyq.setJd(jd);

                String jxj=new DataFormatter().formatCellValue(row.getCell(1));
                if(StringUtils.isNotEmpty(jxj)){
                    sjZlyq.setJxj(Double.parseDouble(jxj));
                }


                String qjbhl=new DataFormatter().formatCellValue(row.getCell(2));
                if(StringUtils.isNotEmpty(qjbhl)){
                    sjZlyq.setQjbhl(Double.parseDouble(qjbhl));

                }

                sjZlyqList.add(sjZlyq);
            }

            if(sjZlyqList.size()>0){
                sjZlyqMapper.deleteSjZlyqByJh(jh);
                sjZlyqMapper.insertSjZlyqBatch(sjZlyqList);
            }

            //质量要求  中靶要求-定向井---------------------------------------------------
            List<SjZlyqZbyq> sjZlyqZbyqList =new ArrayList<>();
            for(int i=3;i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjZlyqZbyq sjZlyq =new SjZlyqZbyq();
                sjZlyq.setJh(jh);

                String bd=new DataFormatter().formatCellValue(row.getCell(4));
                if(StringUtils.isEmpty(bd)){
                    continue;
                }
                sjZlyq.setBd(bd);

               String bbj1=new DataFormatter().formatCellValue(row.getCell(5));
                if(StringUtils.isNotEmpty(bbj1)){
                    sjZlyq.setBbj(Double.parseDouble(bbj1));

                }


                String bz=new DataFormatter().formatCellValue(row.getCell(6));
                sjZlyq.setBz(bz);
                sjZlyqZbyqList.add(sjZlyq);
            }

            if(sjZlyqZbyqList.size()>0){
                sjZlyqZbyqMapper.deleteSjZlyqZbyqByJh(jh);
                sjZlyqZbyqMapper.insertSjZlyqZbyqBatch(sjZlyqZbyqList);
            }
            //质量要求  中靶要求-定向井end---------------------------------------------------
            //质量要求 质量要求---------------------------------------------------
            List<SjZlyqXx> sjZlyqXxList =new ArrayList<>();
            for(int i=3;i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjZlyqXx sjZlyq =new SjZlyqXx();
                sjZlyq.setJh(jh);

                String dxj=new DataFormatter().formatCellValue(row.getCell(8));
                if(StringUtils.isEmpty(dxj)){
                    continue;
                }
                sjZlyq.setDxjzlyq(dxj);

                String gjzlyq=new DataFormatter().formatCellValue(row.getCell(9));
                sjZlyq.setGjzlyq(gjzlyq);

                String wcjjkzlyq=new DataFormatter().formatCellValue(row.getCell(10));
                sjZlyq.setWcjjkzlyq(wcjjkzlyq);
                String yqcwrpdyq=new DataFormatter().formatCellValue(row.getCell(11));
                sjZlyq.setYqcwrpdyq(yqcwrpdyq);
                sjZlyqXxList.add(sjZlyq);
            }

            if(sjZlyqXxList.size()>0){
                sjZlyqXxMapper.deleteSjZlyqXxByJh(jh);
                sjZlyqXxMapper.insertSjZlyqXxBatch(sjZlyqXxList);
            }

            //质量要求 质量要求end---------------------------------------------------
            //构造特征 ---------------------------------------------------
            sheet = workbook.getSheetAt(7);
            List<SjGztz> sjGztzList=new ArrayList<>();
            List<XSSFPictureData> allPictures = sheet.getWorkbook().getAllPictures();







            List<XSSFPicture> pictures = getPictures(sheet);

            // 遍历图片并处理
            for (XSSFPicture picture : pictures) {
                // 获取锚点信息（位置）
                XSSFClientAnchor anchor = picture.getClientAnchor();
                XSSFPictureData pictureData = picture.getPictureData();
                String ext = pictureData.suggestFileExtension();
                String filePath = RuoYiConfig.getUploadPath()+"/gztz/";
                //判断是否为井位图
                if(isPictureInRow(anchor, 0, 0)){
                    SjGztz sjGztz=new SjGztz();
                    sjGztz.setJh(jh);
                    sjGztz.setGzlx("井位图");
                    String fileName = UUID.randomUUID().toString();
                    String lj = filePath + fileName + "." + ext;

                    File desc = new File(lj);

                    if (!desc.exists())
                    {
                        if (!desc.getParentFile().exists())
                        {
                            desc.getParentFile().mkdirs();
                        }
                    }
                    sjGztz.setLj( Constants.RESOURCE_PREFIX +"/upload/gztz/"+fileName + "." + ext);
                    FileOutputStream fos = new FileOutputStream(new File(filePath+fileName+"." + ext));
                    fos.write(pictureData.getData());
                    fos.close();
                    sjGztzList.add(sjGztz);
                }else if(isPictureInRow(anchor, 1, 1)){
                    SjGztz sjGztz=new SjGztz();
                    sjGztz.setJh(jh);
                    sjGztz.setGzlx("地震刨面图");
                    String fileName = UUID.randomUUID().toString();
                    String lj = filePath + fileName + "." + ext;
                    File desc = new File(lj);
                    if (!desc.exists())
                    {
                        if (!desc.getParentFile().exists())
                        {
                            desc.getParentFile().mkdirs();
                        }
                    }
                    sjGztz.setLj( Constants.RESOURCE_PREFIX +"/upload/gztz/"+fileName + "." + ext);
                    FileOutputStream fos = new FileOutputStream(new File(filePath+fileName+"." + ext));
                    fos.write(pictureData.getData());
                    fos.close();
                    sjGztzList.add(sjGztz);
                }else if(isPictureInRow(anchor, 2, 2)){
                    SjGztz sjGztz=new SjGztz();
                    sjGztz.setJh(jh);
                    sjGztz.setGzlx("三压力刨面图");
                    String fileName = UUID.randomUUID().toString();
                    String lj = filePath + fileName + "." + ext;
                    File desc = new File(lj);
                    if (!desc.exists())
                    {
                        if (!desc.getParentFile().exists())
                        {
                            desc.getParentFile().mkdirs();
                        }
                    }
                    sjGztz.setLj( Constants.RESOURCE_PREFIX +"/upload/gztz/"+fileName + "." + ext);
                    FileOutputStream fos = new FileOutputStream(new File(filePath+fileName+"." + ext));
                    fos.write(pictureData.getData());
                    fos.close();
                    sjGztzList.add(sjGztz);
                }
            }
            if(sjGztzList.size()>0){
                sjGztzMapper.deleteSjGztzByJh(jh);
                sjGztzMapper.insertSjGztzBatch(sjGztzList);
            }


            //构造特征end---------------------------------------------------
            //邻井扫描 table---------------------------------------------------
            sheet = workbook.getSheetAt(8);
            lastRowNum = sheet.getLastRowNum();
            List<SjLjsm> sjLjsmList=new ArrayList<>();
            for(int i=3;i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjLjsm sjLjsm =new SjLjsm();
                sjLjsm.setJh(jh);
                String js=new DataFormatter().formatCellValue(row.getCell(1));
                if(StringUtils.isEmpty(js)){
                    continue;
                }
                sjLjsm.setJs(Double.parseDouble(js));
                String cssj=new DataFormatter().formatCellValue(row.getCell(2));
                if(StringUtils.isEmpty(cssj)){
                    sjLjsm.setCs(0.0);
                }else {
                    sjLjsm.setCs(Double.parseDouble(cssj));
                }
                String bjjh=new DataFormatter().formatCellValue(row.getCell(3));
                sjLjsm.setBjjh(bjjh);

                String bjsjly=new DataFormatter().formatCellValue(row.getCell(4));
                sjLjsm.setBjsjly(bjsjly);

                String bjjs=new DataFormatter().formatCellValue(row.getCell(5));
                if(StringUtils.isNotEmpty(bjjs)){
                    sjLjsm.setBjjs(Double.parseDouble(bjjs));
                }
                String bjcs=new DataFormatter().formatCellValue(row.getCell(6));
                if(StringUtils.isNotEmpty(bjcs)){
                    sjLjsm.setBjcs(Double.parseDouble(bjcs));
                }
                String bjzjjl=new DataFormatter().formatCellValue(row.getCell(7));
                if(StringUtils.isNotEmpty(bjzjjl)){
                    sjLjsm.setBjzjjl(Double.parseDouble(bjzjjl));
                }
                String bjsmj=new DataFormatter().formatCellValue(row.getCell(8));
                if(StringUtils.isNotEmpty(bjsmj)){
                    sjLjsm.setBjsmj(Double.parseDouble(bjsmj));
                }

                String bjyj=new DataFormatter().formatCellValue(row.getCell(9));
                if(StringUtils.isNotEmpty(bjyj)){
                    sjLjsm.setBjyj(Double.parseDouble(bjyj));
                }
                sjLjsmList.add(sjLjsm);

            }
            if(sjLjsmList.size()>0){
                sjLjsmMapper.deleteSjLjsmByJh(jh);
                sjLjsmMapper.insertSjLjsmBatch(sjLjsmList);
            }
            //邻井扫描 end---------------------------------------------------
            //邻井扫描 注意事项---------------------------------------------------
            List<ClFxsb> clFxsbList = clFxsbMapper.selectClFxsbList(new ClFxsb());
            sjZysxMapper.deleteSjZysxByJh(jh);
            String zysx=new DataFormatter().formatCellValue(sheet.getRow(1).getCell(11));
            if(StringUtils.isNotEmpty(zysx)){
                SjZysx sjZysx=new SjZysx();
                sjZysx.setJh(jh);
                sjZysx.setLb("邻井扫描");
                sjZysx.setZysx(zysx);
                for(SjLjsm sjLjsm:sjLjsmList){
                    Double zjjl=sjLjsm.getBjzjjl();
                    Double js=sjLjsm.getJs();
                    if(zjjl<5){
                        if(StringUtils.isNotEmpty(sjZysx.getZysx())){
                            sjZysx.setZysx(sjZysx.getZysx()+"；本井在井深"+sjLjsm.getJs()+"m处与同台"+sjLjsm.getBjjh()+"井最近距离 "+zjjl+"m，现场施工时注意防碰");
                        }else {
                            sjZysx.setZysx("本井在井深"+sjLjsm.getJs()+"m处与同台"+sjLjsm.getBjjh()+"井最近距离 "+zjjl+"m，现场施工时注意防碰");
                        }
                        Double ksjs=0.0;
                        for(int i=0;i<sjJsjgList.size();i++){
                            SjJsjg sjJsjg = sjJsjgList.get(i);
                            String kc = sjJsjg.getKc();
                            if(i==0){
                                ksjs=0.0;
                            }else {
                                ksjs =sjJsjgList.get(i-1).getJs();
                            }
                            ClFxsb clFxsb = clFxsbList.stream().filter(it -> it.getFxys().equals("防碰距离")).findFirst().orElse(null);
                            if(js>ksjs&&js<sjJsjg.getJs()){
                                SjFdsgcs sjFdsgcs=new SjFdsgcs();
                                sjFdsgcs.setJh(jh);
                                sjFdsgcs.setKc(sjJsjg.getKc());
                                SjFdsgcs sjFdsgcs1 = sjFdsgcsMapper.selectSjFdsgcsByJhAndKc(sjFdsgcs);
                                if(sjFdsgcs1!=null){
                                    if(clFxsb!=null){
                                        String zjycs = clFxsb.getZjycs();
                                        String gcjscs = clFxsb.getGcjscs();
                                        String zyfx="防碰风险：本井在井深"+sjLjsm.getJs()+"m处与同台"+sjLjsm.getBjjh()+"井最近距离 "+zjjl+"m，现场施工时注意防碰；风险描述："+clFxsb.getFxms();
                                        if(StringUtils.isNotEmpty(sjFdsgcs1.getZyfx())){
                                            if(!sjFdsgcs1.getZyfx().contains("防碰风险")){
                                                sjFdsgcs1.setZyfx(sjFdsgcs1.getZyfx()+"；"+zyfx);
                                            }
                                        }else {
                                            sjFdsgcs1.setZyfx(zyfx);
                                        }

                                        if(StringUtils.isNotEmpty(zjycs)){
                                            String zjycsms="防碰风险：钻井液措施："+zjycs;
                                            if(StringUtils.isNotEmpty(sjFdsgcs1.getZjycs())){
                                                if(!sjFdsgcs1.getZjycs().contains("防碰风险")){
                                                    sjFdsgcs1.setZjycs(sjFdsgcs1.getZjycs()+"；"+zjycsms);
                                                }
                                            }else {
                                                sjFdsgcs1.setZjycs(zjycsms);
                                            }

                                        }
                                        if(StringUtils.isNotEmpty(gcjscs)){
                                            if(StringUtils.isNotEmpty(sjFdsgcs1.getZjgccs())){
                                                if(!sjFdsgcs1.getZjgccs().contains("防碰风险")){
                                                    sjFdsgcs1.setZjgccs(sjFdsgcs1.getZjgccs()+"；防碰风险：工程技术措施："+gcjscs);
                                                }
                                            }else {
                                                sjFdsgcs1.setZjgccs("防碰风险：工程技术措施："+gcjscs);
                                            }

                                        }
                                    }
                                    sjFdsgcsMapper.updateSjFdsgcs(sjFdsgcs1);
                                }else {
                                    sjFdsgcs1=new SjFdsgcs();
                                    sjFdsgcs1.setJh(jh);
                                    sjFdsgcs1.setKc(kc);

                                    if(clFxsb!=null){
                                        String zjycs = clFxsb.getZjycs();
                                        String gcjscs = clFxsb.getGcjscs();
                                        sjFdsgcs1.setZyfx("本井在井深"+sjLjsm.getJs()+"m处与同台"+sjLjsm.getBjjh()+"井最近距离 "+zjjl+"m，现场施工时注意防碰；风险描述："+clFxsb.getFxms());
                                        if(StringUtils.isNotEmpty(zjycs)){
                                            sjFdsgcs1.setZjycs("防碰风险：钻井液措施："+zjycs);
                                        }
                                        if(StringUtils.isNotEmpty(gcjscs)){
                                            sjFdsgcs1.setZjgccs("防碰风险：工程技术措施："+gcjscs);
                                        }
                                    }
                                    sjFdsgcsMapper.insertSjFdsgcs(sjFdsgcs1);
                                }
                            }
                        }
                    }

                }
                sjZysxMapper.insertSjZysx(sjZysx);
            }

            //邻井扫描 注意事项end---------------------------------------------------
            //H2S table---------------------------------------------------
            sheet = workbook.getSheetAt(9);
            lastRowNum = sheet.getLastRowNum();
            List<SjH2s> sjH2sList=new ArrayList<>();
            for(int i=3;i<=lastRowNum;i++){
                row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                SjH2s sjH2s=new SjH2s();
                String jh1=new DataFormatter().formatCellValue(row.getCell(0));
                if(StringUtils.isEmpty(jh1)){
                    continue;
                }
                sjH2s.setJh(jh);
                sjH2s.setLjjh(jh1);
                String js=new DataFormatter().formatCellValue(row.getCell(1));
                if(StringUtils.isNotEmpty(js)){
                    sjH2s.setJs(Double.parseDouble(js));
                }
                String gczt=new DataFormatter().formatCellValue(row.getCell(2));
                sjH2s.setGczt(gczt);
                String cw=new DataFormatter().formatCellValue(row.getCell(3));
                sjH2s.setCw(cw);
                String xsbd=new DataFormatter().formatCellValue(row.getCell(4));
                if(StringUtils.isNotEmpty(xsbd)){
                    sjH2s.setXsbd(Double.parseDouble(xsbd));
                }

                String xsgc=new DataFormatter().formatCellValue(row.getCell(5));
                sjH2s.setXsgc(xsgc);
                String jcsj=new DataFormatter().formatCellValue(row.getCell(6));
                sjH2s.setJcsj(jcsj);

                String jcfs=new DataFormatter().formatCellValue(row.getCell(7));
                sjH2s.setJcfs(jcfs);
                String wyjkfw=new DataFormatter().formatCellValue(row.getCell(8));
                if(StringUtils.isNotEmpty(wyjkfw)){
                    sjH2s.setWyjkfw(Double.parseDouble(wyjkfw));
                }

                String wyjkjl=new DataFormatter().formatCellValue(row.getCell(9));
                if(StringUtils.isNotEmpty(wyjkjl)){
                    sjH2s.setWyjkjl(Double.parseDouble(wyjkjl));
                }
                String wybdfw=new DataFormatter().formatCellValue(row.getCell(10));
                if(StringUtils.isNotEmpty(wybdfw)){
                    sjH2s.setWybdfw(Double.parseDouble(wybdfw));
                }

                String wybdjl=new DataFormatter().formatCellValue(row.getCell(11));
                if(StringUtils.isNotEmpty(wybdjl)){
                    sjH2s.setWybdjl(Double.parseDouble(wybdjl));
                }
                sjH2sList.add(sjH2s);
            }
            if(sjH2sList.size()>0){
                sjH2sMapper.deleteSjH2sByJh(jh);
                sjH2sMapper.insertSjH2sBatch(sjH2sList);
            }
//
            //H2S end---------------------------------------------------
            //H2S 注意事项---------------------------------------------------
            String zysx1=new DataFormatter().formatCellValue(sheet.getRow(1).getCell(13));
            if(StringUtils.isNotEmpty(zysx1)){
                SjZysx sjZysx=new SjZysx();
                sjZysx.setJh(jh);
                sjZysx.setLb("H2S");
                sjZysx.setZysx(zysx1);
//                sjZysxMapper.deleteSjZysxByJh(jh);
                sjZysxMapper.insertSjZysx(sjZysx);
            }
            //H2S 注意事项end---------------------------------------------------
            //喷漏卡塌 ---------------------------------------------------
            sheet = workbook.getSheetAt(10);
            String zysx2=new DataFormatter().formatCellValue(sheet.getRow(1).getCell(0));
            if(StringUtils.isNotEmpty(zysx2)){
                SjZysx sjZysx=new SjZysx();
                sjZysx.setJh(jh);
                sjZysx.setLb("喷漏卡塌");
                sjZysx.setZysx(zysx2);
//                sjZysxMapper.deleteSjZysxByJh(jh);
                sjZysxMapper.insertSjZysx(sjZysx);
            }
            //喷漏卡塌 end---------------------------------------------------
            //浅层气---------------------------------------------------
            sheet = workbook.getSheetAt(11);
            String zysx3=new DataFormatter().formatCellValue(sheet.getRow(1).getCell(0));
            if(StringUtils.isNotEmpty(zysx3)){
                SjZysx sjZysx=new SjZysx();
                sjZysx.setJh(jh);
                sjZysx.setLb("浅层气");
                sjZysx.setZysx(zysx3);
//                sjZysxMapper.deleteSjZysxByJh(jh);
                sjZysxMapper.insertSjZysx(sjZysx);
            }
            //浅层气end---------------------------------------------------
            //计算地质分层
            CommonParam param =new CommonParam();
            param.setJh(jh);
            List<SjDcfxDzfc> dzfcList = djdcService.getDzfcList2(param);
            if(dzfcList.size()>0){
                sjDcfxDzfcMapper.deleteSjDcfxDzfcByJh(jh);
                sjDcfxDzfcMapper.insertSjDcfxDzfcBatch(dzfcList);
                List<SjFdsgcsDcyx>  sjFdsgcsDcyxList=new ArrayList<>();
                double previousDepth= 0;
                double csaa= 0;
                for (int i = 0; i < sjJsjgList.size(); i++) {
                    SjJsjg sjJsjg = sjJsjgList.get(i);
                    if (i == 0) {
                        previousDepth = 0;
                    } else {
                        //上一次的垂深
                        previousDepth = csaa;
                    }
                    //计算垂深
                    double targetCs = CxszhUtil.getCs(sjJygjGdsjgdcsList, sjJsjg.getJs());
                    csaa = targetCs;
                    sjFdsgcsDcyxList.addAll(processAndDisplay(dzfcList, previousDepth, targetCs, jh, sjJsjg.getKc()));
                }
                if (sjFdsgcsDcyxList.size() > 0) {
                    sjFdsgcsDcyxMapper.deleteSjFdsgcsDcyxByJh(jh);
                    sjFdsgcsDcyxMapper.insertSjFdsgcsDcyxBatch(sjFdsgcsDcyxList);
                }
            }
        } catch (IOException e) {
            e.getMessage();
            e.printStackTrace();
            throw new Exception("导入Excel失败，请联系网站管理员！");

        } finally {
            workbook.close();
        }

        return AjaxResult.success("导入成功");
    }


    private static List<SjFdsgcsDcyx> processAndDisplay(List<SjDcfxDzfc> stratumList,double kscs, double targetCs,String jh,String kc) {

        List<SjFdsgcsDcyx> list =new ArrayList<>();


        System.out.println("名称\t井段垂深 / m");

        double previousDepth = kscs;  // 从kscs开始计算
        boolean foundStartingPoint = false;

        System.out.println("名称\t井段垂深 / m");

        for (SjDcfxDzfc stratum : stratumList) {
            double currentDepth = stratum.getCs();

            // 如果还没找到起始点，且当前地层深度小于kscs，跳过
            if (!foundStartingPoint && currentDepth < kscs) {
                continue;
            }

            // 标记已找到起始点
            foundStartingPoint = true;

            // 确定当前地层的显示结束深度
            double endDepth = Math.min(currentDepth, targetCs);

            // 显示当前地层的范围
            System.out.printf("%s\t%.0f-%.0f%n",
                    stratum.getDzfc(), previousDepth, endDepth);
            SjFdsgcsDcyx sjFdsgcsDcyx = new SjFdsgcsDcyx();
            sjFdsgcsDcyx.setJh(jh);
            sjFdsgcsDcyx.setKc(kc);
            sjFdsgcsDcyx.setDcmc(stratum.getDzfc());
            sjFdsgcsDcyx.setJdcs(previousDepth + "-" + endDepth);
            sjFdsgcsDcyx.setYx(stratum.getYxts());
            list.add(sjFdsgcsDcyx);

            // 更新上一个深度
            previousDepth = currentDepth;

            // 如果当前地层已经超过cs参数，结束处理
            if (currentDepth >= targetCs) {
                break;
            }
        }

        return list;
    }



    public static double getCs(List<SjJygjGdsjgdcs> yjMnJygjJgList, double js){

        DecimalFormat df = new DecimalFormat("#.00");

        double cs=0;
        for (int i=0;i<yjMnJygjJgList.size();i++){
            SjJygjGdsjgdcs jg=yjMnJygjJgList.get(i);
            double sd=jg.getJs();
            if (sd>=js){
                if (i == 0){
                    cs=yjMnJygjJgList.get(i).getJs();
                    break;
                }else {


                    // 找到md所在的区间
//                    SurveyPoint prev = surveyPoints.get(i-1);
//                    SurveyPoint curr = surveyPoints.get(i);
//                    double md;    // 斜深(Measured Depth)，单位：米
//                    double inc;   // 井斜角(Inclination)，单位：度
//                    double azim;  // 方位角(Azimuth)，单位：度
//                    double tvd;   // 垂深(True Vertical Depth)，单位：米
//                    double northing; // 北向坐标
//                    double easting;  // 东向坐标
                    // 线性插值计算垂深
                    double ratio = (js - yjMnJygjJgList.get(i-1).getJs()) / (yjMnJygjJgList.get(i).getJs() - yjMnJygjJgList.get(i-1).getJs());
                    cs= yjMnJygjJgList.get(i-1).getCs() + ratio * (yjMnJygjJgList.get(i).getCs() -yjMnJygjJgList.get(i-1).getCs());
                    break;
                }
            }


        }

        return Double.parseDouble(df.format(cs));
    }

    public static String extractNumber(String str) {
        // 正则表达式：匹配-或～，然后捕获后面的数字
        Pattern pattern = Pattern.compile("[\\-～](\\d+)");
        Matcher matcher = pattern.matcher(str);

        if (matcher.find()) {
            // 返回第一个捕获组的内容（即数字部分）
            return matcher.group(1);
        }
        return null; // 如果没有匹配到，返回null
    }

    /**
     * 保存邻井资料
     */
    @PostMapping("/saveLjzl")
    public AjaxResult saveLjzl(@RequestBody CommonParam param) throws Exception{
        return sjDjjcService.saveLjzl(param);
    }

    /**
     * 计算风险
     */
    @PostMapping("/saveFx")
    public AjaxResult saveFx(@RequestBody CommonParam param) throws Exception{

        return sjDjjcService.saveLjzl(param);
    }

    /**
     * 计算钻头
     */
    @PostMapping("/saveZtxh")
    public AjaxResult saveZtxh(@RequestBody CommonParam param) throws Exception{

        return sjDjjcService.saveZtxh(param);
    }

    @PostMapping("/exportWord")
    public void exportWord(long id,HttpServletResponse response) throws Exception {
        SjDjjc sjDjjc = sjDjjcService.selectSjDjjcById(id);

        InputStream in = null;
        XWPFTemplate template = null;
        OutputStream os = null;
        try {
            in = this.getClass().getResourceAsStream("/static/excel/sgfamb.docx");
            os = response.getOutputStream();
            String fileName = sjDjjc.getJh() + "井施工方案";


            // 设置响应头
            response.setContentType("application/force-download");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".docx");
            //加载数据
            Map<String, Object> data = buildDataModel(sjDjjc);
            // 配置
            LoopRowTableRenderPolicy policy = new LoopRowTableRenderPolicy();
            LoopColumnTableRenderPolicy policyCol = new LoopColumnTableRenderPolicy();
            Configure config = Configure.builder().useSpringEL()
                    .bind("sjDzfcList", policy)//行循环策略
                    .bind("sjJsjgList", policy)//行循环策略
                    .bind("sjJygjGdfdcsList", policy)//行循环策略
                    .bind("sjJygjGdsjgdcsList", policy)//行循环策略
                    .bind("sjJygjGdsjList", policy)//行循环策略
                    .bind("zjyFdsjList", policy)//行循环策略
                    .bind("zjyCljlList", policy)//行循环策略
                    .bind("sjZjyFdxnbList", policyCol)//列循环策略
                    .bind("sjTgTgzList", policy)//行循环策略
                    .bind("sjTgTgxnsjList", policy)//行循环策略
                    .bind("sjTgTgqdjhList", policy)//行循环策略
                    .bind("sjZlyqList", policy)//行循环策略
                    .bind("sjZlyqZbyqList", policy)//行循环策略
                    .bind("sjLjjwList", policy)//行循环策略
                    .bind("sjZqfxList", policy)//行循环策略
                    .bind("sjZtgjsjList", policy)//行循环策略
                    .bind("sjQkztfxList", policy)//行循环策略
                    .bind("sjSzfxjgList", policy)//行循环策略
                    .bind("sjDcfxDzfcList", policy)//行循环策略
                    .bind("sjDcfxTsyxList", policy)//行循环策略
                    .bind("sjJhzqList", policy)//行循环策略
                    .bind("sjH2sList", policy)//行循环策略
                    .bind("sjLjsmList", policy)//行循环策略
                    .build();
            XWPFTemplate compile = XWPFTemplate.compile(in, config);
            // 加载模板并填充数据
            template = compile.render(data);
            template.write(os);
            os.flush();
        } catch (Exception e) {
            throw e;
        } finally {
            if (in != null) {
                in.close();
            }
            if (template != null) {
                template.close();
            }

            if (os != null) {
                os.close();
            }

        }
    }

    public  Map<String, Object> buildDataModel(SjDjjc sjDjjc) {
        Map<String, Object> dataModel = new HashMap<>();
        String jh = sjDjjc.getJh();
        // 添加基础信息
        dataModel.put("jh", jh);
        dataModel.put("jb", sjDjjc.getJb());
        dataModel.put("jx", sjDjjc.getJx());
        dataModel.put("jkhzb", sjDjjc.getJkhzb());
        dataModel.put("jkzzb", sjDjjc.getJkzzb());
        dataModel.put("gzwz", sjDjjc.getGzwz());
        dataModel.put("wzcw", sjDjjc.getWzcw());
        dataModel.put("ztmd", sjDjjc.getZtmd());
        dataModel.put("wzyz", sjDjjc.getWzyz());
        dataModel.put("wjfa", sjDjjc.getWjfa());
        SjDzfc sjDzfc =new SjDzfc();
        sjDzfc.setSjjh(jh);
        List<SjDzfc> sjDzfcList = sjDzfcMapper.selectSjDzfcList(sjDzfc);
        dataModel.put("sjDzfcList", sjDzfcList);
        SjJsjg sjJsjg =new SjJsjg();
        sjJsjg.setJh(jh);
        List<SjJsjg> sjJsjgList = sjJsjgMapper.selectSjJsjgList(sjJsjg);
        dataModel.put("sjJsjgList", sjJsjgList);
        //查询井身结构图
        SjJsjgt sjJsjgt=sjJsjgMapper.selectSjJsjgtById(sjDjjc.getId()+"");
        if(sjJsjgt!=null){
            String svgContent = ImageProcessUtil.extractSvgFromBase64(sjJsjgt.getJsjgt());

            InputStream pngStream = ImageProcessUtil.convertSvgToPng(svgContent);
            PictureRenderData image = Pictures.ofStream(pngStream, PictureType.PNG)
                    .size(500, 600)
                    .create();
            dataModel.put("jsjgt", image);
        }else {
            dataModel.put("jsjgt", null);
        }
        SjJygjInfo sjJygjInfo =new SjJygjInfo();
        sjJygjInfo.setJh(jh);
        List<SjJygjInfo> sjJygjInfos = sjJygjInfoMapper.selectSjJygjInfoList(sjJygjInfo);
        String gdlx="";
        if(sjJygjInfos.size()>0){
            gdlx=sjJygjInfos.get(0).getGdlx();
        }
        dataModel.put("gdlx", gdlx);
        SjJygjgdsj sjJygjgdsj =new SjJygjgdsj();
        sjJygjgdsj.setJh(jh);
        List<SjJygjgdsj> sjJygjgdsjList = sjJygjgdsjMapper.selectSjJygjgdsjList(sjJygjgdsj);
        dataModel.put("sjJygjGdsjList", sjJygjgdsjList);
        //井眼轨迹分段参数
        SjJygjGdfdcs sjJygjGdfdcs=new SjJygjGdfdcs();
        sjJygjGdfdcs.setJh(jh);
        List<SjJygjGdfdcs> sjJygjGdfdcsList = sjJygjGdfdcsMapper.selectSjJygjGdfdcsList(sjJygjGdfdcs);
        dataModel.put("sjJygjGdfdcsList", sjJygjGdfdcsList);
        // 添加井眼轨迹设计各点参数
        SjJygjGdsjgdcs sjJygjGdsjgdcs =new SjJygjGdsjgdcs();
        sjJygjGdsjgdcs.setJh(jh);
        List<SjJygjGdsjgdcs> sjJygjGdsjgdcsList = sjJygjGdsjgdcsMapper.selectSjJygjGdsjgdcsList(sjJygjGdsjgdcs);
        dataModel.put("sjJygjGdsjgdcsList", sjJygjGdsjgdcsList);
        //钻井液分段设计
        SjZjyFdsj sjZjyFdsj =new SjZjyFdsj();
        sjZjyFdsj.setJh(jh);
        List<SjZjyFdsj> zjyFdsjList = sjZjyFdsjMapper.selectSjZjyFdsjList(sjZjyFdsj);
        dataModel.put("zjyFdsjList", zjyFdsjList);
        SjZjyCljl sjZjyCljl =new SjZjyCljl();
        sjZjyCljl.setJh(jh);
        List<SjZjyCljl> zjyCljlList = sjZjyCljlMapper.selectSjZjyCljlList(sjZjyCljl);
        for(int i=0;i<zjyCljlList.size();i++){
            zjyCljlList.get(i).setNum(i+1);
        }
        dataModel.put("zjyCljlList", zjyCljlList);

        SjZjyFdxnb sjZjyFdxnb= new SjZjyFdxnb();
        sjZjyFdxnb.setJh(jh);
        List<SjZjyFdxnb> sjZjyFdxnbList = sjZjyFdxnbMapper.selectSjZjyFdxnbList(sjZjyFdxnb);
        dataModel.put("sjZjyFdxnbList", sjZjyFdxnbList);
        //套管柱
        SjTgTgz sjTgTgz =new SjTgTgz();
        sjTgTgz.setJh(jh);
        List<SjTgTgz> sjTgTgzList = sjTgTgzMapper.selectSjTgTgzList(sjTgTgz);
        dataModel.put("sjTgTgzList", sjTgTgzList);
        SjTgTgxnsj sjTgTgxnsj =new SjTgTgxnsj();
        sjTgTgxnsj.setJh(jh);
        List<SjTgTgxnsj> tgxnsjList = sjTgTgxnsjMapper.selectSjTgTgxnsjList(sjTgTgxnsj);
        dataModel.put("sjTgTgxnsjList", tgxnsjList);
        SjTgTgqdjh sjTgTgqdjh =new SjTgTgqdjh();
        sjTgTgqdjh.setJh(jh);
        List<SjTgTgqdjh> sjTgTgqdjhList = sjTgTgqdjhMapper.selectSjTgTgqdjhList(sjTgTgqdjh);
        dataModel.put("sjTgTgqdjhList", sjTgTgqdjhList);

        SjZlyq sjZlyq =new SjZlyq();
        sjZlyq.setJh(jh);
        List<SjZlyq> sjZlyqList = sjZlyqMapper.selectSjZlyqList(sjZlyq);
        dataModel.put("sjZlyqList", sjZlyqList);
        SjZlyqZbyq sjZlyqZbyq =new SjZlyqZbyq();
        sjZlyqZbyq.setJh(jh);
        List<SjZlyqZbyq> sjZlyqZbyqList = sjZlyqZbyqMapper.selectSjZlyqZbyqList(sjZlyqZbyq);
        dataModel.put("sjZlyqZbyqList", sjZlyqZbyqList);
        SjZlyqXx sjZlyqXx =new SjZlyqXx();
        sjZlyqXx.setJh(jh);
        List<SjZlyqXx> sjZlyqXxList = sjZlyqXxMapper.selectSjZlyqXxList(sjZlyqXx);
        if(sjZlyqXxList.size()>0){
            SjZlyqXx item = sjZlyqXxList.get(0);
            dataModel.put("gjzlyq",item.getGjzlyq());
            dataModel.put("wcjjkzlyq",item.getWcjjkzlyq());
            dataModel.put("yqcwrpdyq",item.getYqcwrpdyq());
        }else {
            dataModel.put("gjzlyq","");
            dataModel.put("wcjjkzlyq","");
            dataModel.put("yqcwrpdyq","");
        }

        //邻井
        SjLjjw sjLjjw =new SjLjjw();
        sjLjjw.setJh(jh);
        List<SjLjjw> sjLjjwList = sjLjjwMapper.selectSjLjjwList(sjLjjw);
        dataModel.put("sjLjjwList", sjLjjwList);
        SjZqfx sjZqfx =new SjZqfx();
        sjZqfx.setJh(jh);
        List<SjZqfx> sjZqfxList = sjZqfxMapper.selectSjZqfxList(sjZqfx);
        dataModel.put("sjZqfxList", sjZqfxList);
        SjZtgjsj sjZtgjsj =new SjZtgjsj();
        sjZtgjsj.setJh(jh);
        List<SjZtgjsj> sjZtgjsjList = sjZtgjsjMapper.selectSjZtgjsjList(sjZtgjsj);
        dataModel.put("sjZtgjsjList", sjZtgjsjList);
        SjQkztfx sjQkztfx =new SjQkztfx();
        sjQkztfx.setJh(jh);
        List<SjQkztfx> sjQkztfxList = sjQkztfxMapper.selectSjQkztfxList(sjQkztfx);
        dataModel.put("sjQkztfxList", sjQkztfxList);

        SjSzfxjg sjSzfxjg =new SjSzfxjg();
        sjSzfxjg.setJh(jh);
        List<SjSzfxjg> sjSzfxjgList = sjSzfxjgMapper.selectSjSzfxjgList(sjSzfxjg);
        dataModel.put("sjSzfxjgList", sjSzfxjgList);

        SjDcfxDzfc sjDcfxDzfc =new SjDcfxDzfc();
        sjDcfxDzfc.setJh(jh);
        List<SjDcfxDzfc> sjDcfxDzfcList = sjDcfxDzfcMapper.selectSjDcfxDzfcList(sjDcfxDzfc);
        dataModel.put("sjDcfxDzfcList", sjDcfxDzfcList);
        SjDcfxTsyx sjDcfxTsyx =new SjDcfxTsyx();
        sjDcfxTsyx.setJh(jh);
        List<SjDcfxTsyx> sjDcfxTsyxList = sjDcfxTsyxMapper.selectSjDcfxTsyxList(sjDcfxTsyx);
        dataModel.put("sjDcfxTsyxList", sjDcfxTsyxList);
        //
        SjGztz sjGztz =new SjGztz();
        sjGztz.setJh(jh);
        List<SjGztz> gztzList = sjGztzMapper.selectSjGztzList(sjGztz);
        List<String> jwtList = gztzList.stream().filter(it -> it.getGzlx().equals("井位图")).map(SjGztz::getLj).collect(Collectors.toList());
        if(jwtList.size()>0){
            List<Map<String, PictureRenderData>> pictureList = createPictureList(jwtList, 500, 600);
            dataModel.put("jwtList", pictureList);
        }else {
            dataModel.put("jwtList", new ArrayList<>());
        }

        List<String> dzpmtList = gztzList.stream().filter(it -> it.getGzlx().equals("地震刨面图")).map(SjGztz::getLj).collect(Collectors.toList());
        if(dzpmtList.size()>0){
            List<Map<String, PictureRenderData>> pictureList = createPictureList(dzpmtList, 500, 600);
            dataModel.put("dzpmtList", pictureList);
        }else {
            dataModel.put("dzpmtList", new ArrayList<>());
        }

        List<String> sylpmtList = gztzList.stream().filter(it -> it.getGzlx().equals("三压力刨面图")).map(SjGztz::getLj).collect(Collectors.toList());
        if(sylpmtList.size()>0){
            List<Map<String, PictureRenderData>> pictureList = createPictureList(sylpmtList, 500, 600);
            dataModel.put("sylpmtList", pictureList);
        }else {
            dataModel.put("sylpmtList", new ArrayList<>());
        }

        //计划周期
        SjJhzq sjJhzq =new SjJhzq();
        sjJhzq.setJh(jh);
        List<SjJhzq> sjJhzqList = sjJhzqMapper.selectSjJhzqList(sjJhzq);
        dataModel.put("sjJhzqList",sjJhzqList);
        //H2s
        SjH2s sjH2s =new SjH2s();
        sjH2s.setJh(jh);
        List<SjH2s> sjH2sList = sjH2sMapper.selectSjH2sList(sjH2s);
        dataModel.put("sjH2sList",sjH2sList);

        SjLjsm sjLjsm =new SjLjsm();
        sjLjsm.setJh(jh);
        List<SjLjsm> sjLjsmList = sjLjsmMapper.selectSjLjsmList(sjLjsm);
        dataModel.put("sjLjsmList",sjLjsmList);



        return dataModel;
    }

    private Map<String, PictureRenderData> createPictureMap(String pictureName, int width, int height) {
        Map<String, PictureRenderData> map = new HashMap<>();
        String filePath = RuoYiConfig.getUploadPath();
        String lj = pictureName.replaceAll("/profile/upload", "");
        String path=filePath + lj;
        File file =new File(path);
        if(!file.exists()){
            return map;
        }
        //创建PictureRenderData对象并设置其大小
        //Pictures还有其他方法，如Pictures.ofStream()流处理，可根据自己的需求及文档替换
        map.put("jwt", Pictures.ofLocal(path).size(width, height).create());
        return map;
    }

    private List<Map<String, PictureRenderData>> createPictureList(List<String> imgList,int width, int height) {
        List<Map<String, PictureRenderData>> list = new ArrayList<>();
        for(String lj:imgList  ){

            list.add(createPictureMap(lj, width, height));
        }


        return list;
    }








    /**
     * 计算指定方向上的视平移
     * @param closureAzimuth 闭合方位角（度）
     * @param nsDisplacement 南北位移（米）
     * @param ewDisplacement 东西位移（米）
     * @return 视平移（米）
     */
    public  double calculateApparentDisplacement(double closureAzimuth,
                                                       double nsDisplacement,
                                                       double ewDisplacement) {
        // 将角度转换为弧度（Java 的三角函数使用弧度）
        double azimuthRadians = Math.toRadians(closureAzimuth);

        // 计算视平移
        return nsDisplacement * Math.cos(azimuthRadians) +
                ewDisplacement * Math.sin(azimuthRadians);
    }



    /**
     * 判断图片是否在指定行范围内
     * @param anchor 图片锚点
     * @return 如果图片在目标行范围内则返回true
     */
    private static boolean isPictureInRow(XSSFClientAnchor anchor, int startRowNum,int endRowNum) {
        // 获取图片覆盖的行范围
        int row1 = anchor.getRow1();  // 起始行
        int row2 = anchor.getRow2();  // 结束行

        // 检查目标行是否在图片覆盖的行范围内
        return startRowNum >= row1 && endRowNum <= row2;
    }


    /**
     * 从XSSFSheet中获取所有图片
     * 兼容所有POI版本的标准写法
     */
    public static List<XSSFPicture> getPictures(XSSFSheet sheet) {
        List<XSSFPicture> pictures = new ArrayList<>();

        // 获取工作表中的绘图对象
        XSSFDrawing drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            return pictures; // 没有任何绘图对象
        }

        // 遍历所有形状，筛选出图片
        List<XSSFShape> shapes = drawing.getShapes();
        for (XSSFShape shape : shapes) {
            if (shape instanceof XSSFPicture) {
                pictures.add((XSSFPicture) shape);
            }
        }
        return pictures;
    }





}
