ltc-eastar/src/main/java/com/malk/eastar/service/impl/AitableServiceImpl.java

package com.malk.eastar.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.malk.eastar.model.AITableParam;
import com.malk.eastar.model.AITableResult;
import com.malk.eastar.service.MDTableClient;
import com.malk.eastar.service.AitableService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 专用于AI表格复杂逻辑处理的接口服务
 * add by Jason 20260409
 */
@Slf4j
@Service
public class AitableServiceImpl implements AitableService {

    @Autowired
    private MDTableClient mdTableClient;

    @Override
    public List<Map> querySys1CustomerData() throws Exception {
        /*
            参数定义
         */
        List<Map> customerData = new ArrayList<>(); // 客户数据
        List<Map> customerDataDeduplicate = new ArrayList<>(); // 客户数据（去重）
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        AITableParam aiTableParam = new AITableParam(); //HTTP请求体
        boolean hasMore; //是否还有下一页
        int pageNo; //当前页码
        int pageSize;   //每页获取的数据量（上限100）
        AITableResult aiTableResult;    //请求返回内容
        JSONArray records;  //每页记录数据
        JSONArray allRecords = new JSONArray();  //所有记录数据
        String aiTableId;   //AI表格ID
        JSONObject fields;  //字段数据
        String customerName;    //客户名称
        JSONArray businessUserJSONArray;  //业务【人事】
        String businessUserUnionId;    //业务员unionId
        String businessUserName;    //业务员姓名
        String kp;  //KP
        boolean isCustomerNameEmpty;    //客户名称是否为空
        boolean isBusinessUserEmpty;    //业务员是否为空
        boolean isKpEmpty;  //KP是否为空
        int customerEmptyCount = 0;  //客户名称为空的记录数
        Map<String,String> customerRecord;  //客户记录
        Map<String, Long> customerNameCountMap = new HashMap<>();   //客户名称与出现的次数映射
        String customerLevel;   //客户分级
        String newOrOld;    //新老客户
        String testUseFlag; //测试使用标识

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "E6RcJi3";

        /*
            项目总表副本-20260409
         */
//        baseId = "GZLxjv9VGqBAMDOoHYzrPZzE86EDybno";
//        sheetIdOrName = "E6RcJi3";

        /*
            初始化参数值
         */
        hasMore = true; //第一页默认有数据
        pageNo = 1;
        pageSize = 100;
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        aiTableParam.setMaxResults(pageSize);

        /*
            翻页获取数据
         */
        while(hasMore){
            aiTableResult = mdTableClient.queryMultiRecords(baseId,sheetIdOrName,param,aiTableParam);
            hasMore = aiTableResult.getHasMore();
            log.info("当前第"+pageNo+"页");
            log.info("记录数="+aiTableResult.getRecords().size());
            records = aiTableResult.getRecords();
            for(int i=0;i<records.size();i++){
                allRecords.add(records.getJSONObject(i));
            }
            log.info("是否有更多数据："+hasMore);
            pageNo++;
            aiTableParam.setNextToken(aiTableResult.getNextToken());
        }

        /*
            请求记录数据处理
         */
        log.info("所有记录数量="+allRecords.size());
        for(int i=0;i<allRecords.size();i++){
            /*
                重置是否为空标识
             */
            isCustomerNameEmpty = false;
            isBusinessUserEmpty = false;
            isKpEmpty = false;

            customerName = "";
            businessUserUnionId = "";
            businessUserName = "";
            kp = "";

            testUseFlag = "";

            //获取AI表格ID
            aiTableId = allRecords.getJSONObject(i).getString("id");

            //解析字段数据
            fields = allRecords.getJSONObject(i).getJSONObject("fields");

            /*
                获取客户名称数据
             */
            if(fields.get("客户公司名【基础】") == null){
                isCustomerNameEmpty = true;
            }else{
                customerName = fields.getString("客户公司名【基础】");
                customerName = customerName.replace("\n",""); //去除换行符
                customerName = customerName.trim(); //去除空格
                if(StringUtils.isEmpty(customerName)){
                    isCustomerNameEmpty = true;
                }
            }
            if(isCustomerNameEmpty){
                customerEmptyCount++;
//                throw new RuntimeException("客户公司名【基础】字段为空");
                continue;
            }else{
//                System.out.println("客户名称="+customerName);
            }

            /*
                获取业务员数据
             */
            if(fields.get("业务【人事】") == null){
                isBusinessUserEmpty = true;
            }else{
                businessUserJSONArray = fields.getJSONArray("业务【人事】");
                if(businessUserJSONArray.isEmpty()){
                    isBusinessUserEmpty = true;
                }else{
                    if(businessUserJSONArray.size()>1){ //一个客户有多个业务员负责对接
                        log.info("客户名称="+customerName+"，业务员数量="+businessUserJSONArray.size());
                    }
                    businessUserUnionId = businessUserJSONArray.getJSONObject(0).getString("unionId");
                    businessUserName = businessUserJSONArray.getJSONObject(0).getString("name");
                }
            }
//            System.out.println("业务员是否为空="+isBusinessUserEmpty);
            if(isBusinessUserEmpty){
//                throw new RuntimeException("业务【人事】字段为空");
            }else{
//                System.out.println("业务员="+businessUser);
            }

            /*
                获取客户分级
             */
            customerLevel = "";
            if(fields.get("客户分级【客户】") != null){
                customerLevel = fields.getJSONObject("客户分级【客户】").getString("name");
                if(
                        "A".equals(customerLevel)
                                ||"潜A".equals(customerLevel)
                                ||"B".equals(customerLevel)
                                ||"潜B".equals(customerLevel)
                                ||"C".equals(customerLevel)
                                ||"潜C".equals(customerLevel)
                                ||"D".equals(customerLevel)
                                ||"潜D".equals(customerLevel)
                                ||"E".equals(customerLevel)
                                ||"保稳E".equals(customerLevel)
                ){
                    //不变
                }else{
                    customerLevel = "";
                }
            }

            /*
                获取新老客户
             */
            newOrOld = "";
            if(fields.get("新老客户？【客户】") != null){
                newOrOld = fields.getJSONObject("新老客户？【客户】").getString("name");
                if(newOrOld.contains("新")){
                    newOrOld = "新";
                }
                if(newOrOld.contains("老")){
                    newOrOld = "老";
                }
                if(newOrOld.contains("不合作")){
                    newOrOld = "不合作";
                }
            }

            /*
                获取KP数据
             */
            /*
            if(fields.get("一把手KP") == null){
                isKpEmpty = true;
            }else{
                kp = fields.getString("一把手KP");
            }
//            System.out.println("KP是否为空="+isKpEmpty);
            if(isKpEmpty){
//                throw new RuntimeException("一把手KP字段为空");
            }else{
//                System.out.println("KP="+kp);
            }
             */


            /*
                获取新老客户
             */
            if(fields.get("测试使用") != null){
                testUseFlag = fields.getJSONObject("测试使用").getString("name");
            }

            /*
                汇总客户数据
             */
            customerRecord = new HashMap<>();
            customerRecord.put("aiTableId",aiTableId);
            customerRecord.put("customerName",customerName);
            customerRecord.put("businessUserName",businessUserName);
            customerRecord.put("businessUserUnionId",businessUserUnionId);
//            customerRecord.put("kp",kp);
//            System.out.println(JSONObject.toJSONString(customerRecord));
            customerRecord.put("customerLevel",customerLevel);
            customerRecord.put("newOrOld",newOrOld);
//            System.out.println(aiTableId+","+customerLevel+","+newOrOld);
            customerRecord.put("testUseFlag",testUseFlag);
            customerData.add(customerRecord);
        }
        log.info("客户名称为空的记录数="+customerEmptyCount);

        /*
            排除有重复的客户
         */
        for (Map<String, String> record : customerData) {
            customerName = record.get("customerName");
            customerNameCountMap.merge(customerName, 1L, Long::sum);
        }
        for (Map.Entry<String, Long> entry : customerNameCountMap.entrySet()) {
            if (entry.getValue() > 1) {
                System.out.println("客户名称="+entry.getKey()+"，重复次数="+ entry.getValue());
            }
        }
        for (Map<String, String> record : customerData) {
            customerName = record.get("customerName");
            if(customerNameCountMap.get(customerName)==1){
                customerDataDeduplicate.add(record);
            }
        }


        /*
            返回客户数据（去重）
         */
        return customerDataDeduplicate;
    }

    @Override
    public List<Map> querySys1AllSaleData() throws Exception {
        /*
            参数定义
         */
        List<Map> saleData = new ArrayList<>(); //售前台账数据
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        AITableParam aiTableParam = new AITableParam(); //HTTP请求体
        boolean hasMore; //是否还有下一页
        int pageNo; //当前页码
        int pageSize;   //每页获取的数据量（上限100）
        AITableResult aiTableResult;    //请求返回内容
        JSONArray records;  //每页记录数据
        JSONArray allRecords = new JSONArray();  //所有记录数据
        String aiTableId;   //AI表格ID
        JSONObject fields;  //字段数据
        Map<String,Object> saleRecord;  //售前台账记录
        String piCode;  //原PI号
        String projectName;  //项目名称
        String projectEnName;  //项目英文名
        String pName;  //项目名称缩写
        Double projectAmount;   //预计销售项目金额
        JSONArray linkedRecordIds;  //客户公司名 - S1【基础】
        String customerId;  //客户ID
        Long expectedCloseDate; //预计成单日期
        Long actualCloseDate;   //实际成单日期
        JSONArray unionIdJSONArray;  //业务 - S1【人事】
        String unionId;  //业务员ID
        String clearLevelFlag;  //是否计算为通关项目
        String dealStatus;  //成单状态
        String orderStatus;  //订单状态
        String opportunityStatus;  //商机阶段

        /*
            项目总表 - 【表1】售前台账
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            初始化参数值
         */
        hasMore = true; //第一页默认有数据
        pageNo = 1;
        pageSize = 100;
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        aiTableParam.setMaxResults(pageSize);
        JSONObject filter = new JSONObject();
        filter.put("combination","and");
        JSONArray conditions = new JSONArray();
        JSONObject condition1 = new JSONObject();
        condition1.put("field","Parent Record");
        condition1.put("operator","empty");
        conditions.add(condition1);
        filter.put("conditions",conditions);
        aiTableParam.setFilter(filter);

        /*
            翻页获取数据
         */
        while(hasMore){
            aiTableResult = mdTableClient.queryMultiRecords(baseId,sheetIdOrName,param,aiTableParam);
            hasMore = aiTableResult.getHasMore();
            log.info("当前第"+pageNo+"页");
            log.info("记录数="+aiTableResult.getRecords().size());
            records = aiTableResult.getRecords();
            for(int i=0;i<records.size();i++){
                allRecords.add(records.getJSONObject(i));
            }
            log.info("是否有更多数据："+hasMore);
            pageNo++;
            aiTableParam.setNextToken(aiTableResult.getNextToken());
        }

        /*
            请求记录数据处理
         */
        log.info("所有记录数量="+allRecords.size());
        for(int i=0;i<allRecords.size();i++){
            //重置变量
            piCode = "";
            projectName = "";
            projectEnName = "";
            pName = "";
            customerId = "";
            unionId = "";
            projectAmount = null;
            expectedCloseDate = null;
            actualCloseDate = null;
            clearLevelFlag = null;
            dealStatus = null;
            orderStatus = null;
            opportunityStatus = null;

            //获取AI表格ID
            aiTableId = allRecords.getJSONObject(i).getString("id");

            //解析字段数据
            fields = allRecords.getJSONObject(i).getJSONObject("fields");

            if(fields.get("项目编号 - S1【基础】") != null){
                piCode = fields.getString("项目编号 - S1【基础】");
                piCode = piCode.replace("\n",""); //去除换行符
                piCode = piCode.trim(); //去除空格
                Matcher matcher = Pattern.compile("Eastar-?([A-Za-z]+)").matcher(piCode);
                if (matcher.find()) {
                    pName = matcher.group(1);
                    if(pName != null && pName.length()>0){
                        pName = pName.toUpperCase();    //转大写
                        if(pName.length()>5){
                            pName = pName.substring(0,5);   //最大5个字符
                        }
                    }
                }
            }
            if(fields.get("项目名称 - S1【基础】") != null){
                projectName = fields.getString("项目名称 - S1【基础】");
                projectName = projectName.replace("\n",""); //去除换行符
                projectName = projectName.trim(); //去除空格
            }
            if(fields.get("项目英文名 - S1【基础") != null){
                projectEnName = fields.getString("项目英文名 - S1【基础");
                projectEnName = projectEnName.replace("\n",""); //去除换行符
                projectEnName = projectEnName.trim(); //去除空格
            }
            if(fields.get("订单金额 - S2~3【销售数据】") != null){
                projectAmount = fields.getDouble("订单金额 - S2~3【销售数据】");
            }
            if(fields.get("客户公司名 - S1【基础】") != null){
                linkedRecordIds = fields.getJSONObject("客户公司名 - S1【基础】").getJSONArray("linkedRecordIds");
                if(linkedRecordIds!=null && !linkedRecordIds.isEmpty()){
                    customerId = linkedRecordIds.getString(0);
                }
            }
            if(fields.get("预计成单日期 - S1~2【商机进度】") != null){
                expectedCloseDate = fields.getLong("预计成单日期 - S1~2【商机进度】");
            }
            if(fields.get("实际成单日期 - S3【销售数据】") != null){
                actualCloseDate = fields.getLong("实际成单日期 - S3【销售数据】");
            }
            if(fields.get("业务 - S1【人事】") != null){
                unionIdJSONArray = fields.getJSONArray("业务 - S1【人事】");
                if(unionIdJSONArray!=null && !unionIdJSONArray.isEmpty()){
                    unionId = unionIdJSONArray.getJSONObject(0).getString("unionId");
                }
            }
            if(fields.get("是否计算为通关项目") != null){
                clearLevelFlag = fields.getJSONObject("是否计算为通关项目").getString("name");
            }
            if(fields.get("成单情况【商机进度】") != null){
                dealStatus = fields.getJSONObject("成单情况【商机进度】").getString("name");
            }
            if(fields.get("订单状态 - S1~3【商机进度】") != null){
                orderStatus = fields.getJSONObject("订单状态 - S1~3【商机进度】").getString("name");
            }
            if(fields.get("商机阶段 - S1~3【商机进度】") != null){
                opportunityStatus = fields.getJSONObject("商机阶段 - S1~3【商机进度】").getString("name");
            }

            saleRecord = new HashMap<>();
            saleRecord.put("aiTableId",aiTableId);
            saleRecord.put("piCode",piCode);
            saleRecord.put("projectName",projectName);
            saleRecord.put("projectEnName",projectEnName);
            saleRecord.put("pName",pName);
            saleRecord.put("projectAmount",projectAmount);
            saleRecord.put("customerId",customerId);
            saleRecord.put("expectedCloseDate",expectedCloseDate);
            saleRecord.put("actualCloseDate",actualCloseDate);
            saleRecord.put("unionId",unionId);
            saleRecord.put("clearLevelFlag",clearLevelFlag);
            saleRecord.put("dealStatus",dealStatus);
            saleRecord.put("orderStatus",orderStatus);
            saleRecord.put("opportunityStatus",opportunityStatus);
            saleData.add(saleRecord);
        }

        return saleData;
    }

    @Override
    public List<Map> querySys1SaleData() throws Exception {
        /*
            参数定义
         */
        List<Map> saleData = new ArrayList<>(); //售前台账数据
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        AITableParam aiTableParam = new AITableParam(); //HTTP请求体
        boolean hasMore; //是否还有下一页
        int pageNo; //当前页码
        int pageSize;   //每页获取的数据量（上限100）
        AITableResult aiTableResult;    //请求返回内容
        JSONArray records;  //每页记录数据
        JSONArray allRecords = new JSONArray();  //所有记录数据
        String aiTableId;   //AI表格ID
        JSONObject fields;  //字段数据
        Map<String,Object> saleRecord;  //售前台账记录
        String piCode;  //原PI号
        String projectName;  //项目名称
        String projectEnName;  //项目英文名
        String pName;  //项目名称缩写
        Double projectAmount;   //预计销售项目金额
        JSONArray linkedRecordIds;  //客户公司名 - S1【基础】
        String customerId;  //客户ID
        Long expectedCloseDate; //预计成单日期
        Long actualCloseDate;   //实际成单日期
        JSONArray unionIdJSONArray;  //业务 - S1【人事】
        String unionId;  //业务员ID



        /*
            项目总表 - 【表1】售前台账
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            初始化参数值
         */
        hasMore = true; //第一页默认有数据
        pageNo = 1;
        pageSize = 100;
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        aiTableParam.setMaxResults(pageSize);
        JSONObject filter = new JSONObject();
        filter.put("combination","and");
        JSONArray conditions = new JSONArray();
        JSONObject condition1 = new JSONObject();
        condition1.put("field","商机阶段 - S1~3【商机进度】");
        condition1.put("operator","equal");
        condition1.put("value",new String[]{"已成单"});
        conditions.add(condition1);
        JSONObject condition2 = new JSONObject();
        condition2.put("field","项目编号 - S1【基础】");
        condition2.put("operator","notEmpty");
        conditions.add(condition2);
        filter.put("conditions",conditions);
        JSONObject condition3 = new JSONObject();
        condition3.put("field","Parent Record");
        condition3.put("operator","empty");
        conditions.add(condition3);
        filter.put("conditions",conditions);
        aiTableParam.setFilter(filter);

        /*
            翻页获取数据
         */
        while(hasMore){
            aiTableResult = mdTableClient.queryMultiRecords(baseId,sheetIdOrName,param,aiTableParam);
            hasMore = aiTableResult.getHasMore();
            log.info("当前第"+pageNo+"页");
            log.info("记录数="+aiTableResult.getRecords().size());
            records = aiTableResult.getRecords();
            for(int i=0;i<records.size();i++){
                allRecords.add(records.getJSONObject(i));
            }
            log.info("是否有更多数据："+hasMore);
            pageNo++;
            aiTableParam.setNextToken(aiTableResult.getNextToken());
        }

        /*
            请求记录数据处理
         */
        log.info("所有记录数量="+allRecords.size());
        for(int i=0;i<allRecords.size();i++){
            //重置变量
            piCode = "";
            projectName = "";
            projectEnName = "";
            pName = "";
            projectAmount = null;
            customerId = "";
            expectedCloseDate = null;
            actualCloseDate = null;
            unionId = "";

            //获取AI表格ID
            aiTableId = allRecords.getJSONObject(i).getString("id");

            //解析字段数据
            fields = allRecords.getJSONObject(i).getJSONObject("fields");

            if(fields.get("项目编号 - S1【基础】") != null){
                piCode = fields.getString("项目编号 - S1【基础】");
                piCode = piCode.replace("\n",""); //去除换行符
                piCode = piCode.trim(); //去除空格
                Matcher matcher = Pattern.compile("Eastar-?([A-Za-z]+)").matcher(piCode);
                if (matcher.find()) {
                    pName = matcher.group(1);
                    if(pName != null && pName.length()>0){
                        pName = pName.toUpperCase();    //转大写
                        if(pName.length()>5){
                            pName = pName.substring(0,5);   //最大5个字符
                        }
                    }
                }
            }
            if(fields.get("项目名称 - S1【基础】") != null){
                projectName = fields.getString("项目名称 - S1【基础】");
                projectName = projectName.replace("\n",""); //去除换行符
                projectName = projectName.trim(); //去除空格
            }
            if(fields.get("项目英文名 - S1【基础") != null){
                projectEnName = fields.getString("项目英文名 - S1【基础");
                projectEnName = projectEnName.replace("\n",""); //去除换行符
                projectEnName = projectEnName.trim(); //去除空格
            }
            if(fields.get("订单金额 - S2~3【销售数据】") != null){
                projectAmount = fields.getDouble("订单金额 - S2~3【销售数据】");
            }
            if(fields.get("客户公司名 - S1【基础】") != null){
                linkedRecordIds = fields.getJSONObject("客户公司名 - S1【基础】").getJSONArray("linkedRecordIds");
                if(linkedRecordIds!=null && !linkedRecordIds.isEmpty()){
                    customerId = linkedRecordIds.getString(0);
                }
            }
            if(fields.get("预计成单日期 - S1~2【商机进度】") != null){
                expectedCloseDate = fields.getLong("预计成单日期 - S1~2【商机进度】");
            }
            if(fields.get("实际成单日期 - S3【销售数据】") != null){
                actualCloseDate = fields.getLong("实际成单日期 - S3【销售数据】");
            }
            if(fields.get("业务 - S1【人事】") != null){
                unionIdJSONArray = fields.getJSONArray("业务 - S1【人事】");
                if(unionIdJSONArray!=null && !unionIdJSONArray.isEmpty()){
                    unionId = unionIdJSONArray.getJSONObject(0).getString("unionId");
                }
            }

            saleRecord = new HashMap<>();
            saleRecord.put("aiTableId",aiTableId);
            saleRecord.put("piCode",piCode);
            saleRecord.put("projectName",projectName);
            saleRecord.put("projectEnName",projectEnName);
            saleRecord.put("pName",pName);
            saleRecord.put("projectAmount",projectAmount);
            saleRecord.put("customerId",customerId);
            saleRecord.put("expectedCloseDate",expectedCloseDate);
            saleRecord.put("actualCloseDate",actualCloseDate);
            saleRecord.put("unionId",unionId);
//            System.out.println(JSONObject.toJSONString(saleRecord));
            saleData.add(saleRecord);
        }

        return saleData;
    }

    @Override
    public List<Map> querySys1SaleData(Map<String, String> params) throws Exception {
        /*
            参数定义
         */
        List<Map> saleData = new ArrayList<>(); //售前台账数据
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        AITableParam aiTableParam = new AITableParam(); //HTTP请求体
        boolean hasMore; //是否还有下一页
        int pageNo; //当前页码
        int pageSize;   //每页获取的数据量（上限100）
        AITableResult aiTableResult;    //请求返回内容
        JSONArray records;  //每页记录数据
        JSONArray allRecords = new JSONArray();  //所有记录数据
        String aiTableId;   //AI表格ID
        JSONObject fields;  //字段数据
        Map<String,Object> saleRecord;  //售前台账记录
        String projectStage;  //商机阶段 - S1~3【商机进度】
        String projectCode;  //项目编号 - S1【基础】
        String getRfqDate;  //实际收到RFQ日期


        /*
            项目总表 - 【表1】售前台账
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            初始化参数值
         */
        hasMore = true; //第一页默认有数据
        pageNo = 1;
        pageSize = 100;
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        aiTableParam.setMaxResults(pageSize);
        JSONObject filter = new JSONObject();
        filter.put("combination","and");
        JSONArray conditions = new JSONArray();
        JSONObject condition1 = new JSONObject();
        condition1.put("field","项目编号 - S1【基础】");
        condition1.put("operator","notEmpty");
        conditions.add(condition1);
        filter.put("conditions",conditions);
        aiTableParam.setFilter(filter);

        /*
            翻页获取数据
         */
        while(hasMore){
            aiTableResult = mdTableClient.queryMultiRecords(baseId,sheetIdOrName,param,aiTableParam);
            hasMore = aiTableResult.getHasMore();
            log.info("当前第"+pageNo+"页");
            log.info("记录数="+aiTableResult.getRecords().size());
            records = aiTableResult.getRecords();
            for(int i=0;i<records.size();i++){
                allRecords.add(records.getJSONObject(i));
            }
            log.info("是否有更多数据："+hasMore);
            pageNo++;
            aiTableParam.setNextToken(aiTableResult.getNextToken());
        }

        /*
            请求记录数据处理
         */
        log.info("所有记录数量="+allRecords.size());
        for(int i=0;i<allRecords.size();i++){
            //重置变量
            projectStage = "";
            projectCode = "";
            getRfqDate = "";

            //获取AI表格ID
            aiTableId = allRecords.getJSONObject(i).getString("id");

            //解析字段数据
            fields = allRecords.getJSONObject(i).getJSONObject("fields");

            if(fields.get("商机阶段 - S1~3【商机进度】") != null){
                projectStage = fields.getJSONObject("商机阶段 - S1~3【商机进度】").getString("name");
            }

            if(fields.get("项目编号 - S1【基础】") != null){
                projectCode = fields.getString("项目编号 - S1【基础】");
            }

            if(fields.get("实际收到RFQ日期") != null){
                getRfqDate = fields.getString("实际收到RFQ日期");
            }

            saleRecord = new HashMap<>();
            saleRecord.put("aiTableId",aiTableId);
            saleRecord.put("projectStage",projectStage);
            saleRecord.put("projectCode",projectCode);
            saleRecord.put("getRfqDate",getRfqDate);
//            System.out.println(JSONObject.toJSONString(saleRecord));
            saleData.add(saleRecord);
        }

        return saleData;
    }

    @Override
    public Map querySys1SaleRecordByID(String id) throws Exception {
        /*
            参数定义
         */
        Map<String,String> saleRecord = new HashMap<String,String>(); //售前台账记录
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject fields;  //字段数据
        String yidaGetRfqDate;  //实际收到RFQ日期
        String yidaRfqType; //RFQ来源

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId


        /*
            项目总表 - 【表1】售前台账
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            获取数据
         */
        String queryResult = mdTableClient.queryOneRecord(baseId,sheetIdOrName,id,param);

        /*
            请求记录数据处理
         */
        log.info("查询结果：");
        log.info(queryResult);
        JSONObject queryResultJson = JSONObject.parseObject(queryResult);
        fields = queryResultJson.getJSONObject("fields");
        if(fields.get("实际收到RFQ日期") != null){
            yidaGetRfqDate = fields.getString("实际收到RFQ日期");
            saleRecord.put("yidaGetRfqDate",yidaGetRfqDate);
        }
        if(fields.get("RFQ来源") != null){
            yidaRfqType = fields.getString("RFQ来源");
            saleRecord.put("yidaRfqType",yidaRfqType);
        }

        return saleRecord;
    }

    @Override
    public String querySys1CustomerIdByName(String customerName) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        AITableParam aiTableParam = new AITableParam(); //HTTP请求体
        AITableResult aiTableResult;    //请求返回内容
        JSONArray records;  //记录数据
        String customerId = "";  //客户名册记录ID

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "E6RcJi3";


        /*
            项目总表副本-20260409
         */
//        baseId = "GZLxjv9VGqBAMDOoHYzrPZzE86EDybno";
//        sheetIdOrName = "E6RcJi3";

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        JSONObject filter = new JSONObject();
        JSONArray conditions = new JSONArray();
        JSONObject condition = new JSONObject();
        condition.put("field","20rO7zS");
        condition.put("operator","equal");
        condition.put("value",new String[]{customerName});
        conditions.add( condition);
        filter.put("conditions",conditions);
        aiTableParam.setFilter(filter);

        /*
            获取数据
         */
        log.info(JSONObject.toJSONString(aiTableParam));
        aiTableResult = mdTableClient.queryMultiRecords(baseId,sheetIdOrName,param,aiTableParam);
        log.info(JSONObject.toJSONString(aiTableResult));
        records = aiTableResult.getRecords();
        if(records!=null && !records.isEmpty()){
            customerId = records.getJSONObject(0).getString("id");
        }
        return customerId;
    }

    @Override
    public String addSys1CustomerData(Map<String, Object> fields) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject insertData = new JSONObject();   //HTTP请求体
        JSONArray insertRecords = new JSONArray();  //新增记录集合
        JSONObject insertRecord = new JSONObject();      //新增记录行数据
        String result;  //返回结果
        JSONObject resultJSONObject;
        JSONArray aitableIdJSONArray;
        String aitableId = "";   //AI表格记录ID

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "E6RcJi3";

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        insertRecord.put("fields",fields);
        insertRecords.add(insertRecord);
        insertData.put("records",insertRecords);

        /*
            新增数据
         */
        log.info(JSONObject.toJSONString(insertData));
        result = mdTableClient.createMultiRecords(baseId,sheetIdOrName,param,insertData);
        log.info(result);
        resultJSONObject = JSONObject.parseObject(result);
        aitableIdJSONArray = resultJSONObject.getJSONArray("value");
        if(aitableIdJSONArray!=null || !aitableIdJSONArray.isEmpty()){
            aitableId = aitableIdJSONArray.getJSONObject(0).getString("id");
        }
        return aitableId;
    }

    @Override
    public String updateSys1CustomerData(String id,Map<String, Object> fields) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject updateData = new JSONObject();   //HTTP请求体
        JSONArray updateRecords = new JSONArray();  //更新记录集合
        JSONObject updateRecord = new JSONObject();      //更新记录行数据
        String result;  //返回结果

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "E6RcJi3";

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        updateRecord.put("id",id);
        updateRecord.put("fields",fields);
        updateRecords.add(updateRecord);
        updateData.put("records",updateRecords);

        /*
            更新数据
         */
        log.info(JSONObject.toJSONString(updateData));
        result = mdTableClient.modifyMultiRecords(baseId,sheetIdOrName,param,updateData);
        log.info(result);
        return result;
    }

    @Override
    public String addSys1SaleData(Map<String,Object> fields) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject insertData = new JSONObject();   //HTTP请求体
        JSONArray insertRecords = new JSONArray();  //新增记录集合
        JSONObject insertRecord = new JSONObject();      //新增记录行数据
        String result;  //返回结果
        JSONObject resultJSONObject;
        JSONArray aitableIdJSONArray;
        String aitableId = "";   //AI表格记录ID

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            项目总表副本-20260409
         */
//        baseId = "GZLxjv9VGqBAMDOoHYzrPZzE86EDybno";
//        sheetIdOrName = "C1mtX34";  //售前台账

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        insertRecord.put("fields",fields);
        insertRecords.add(insertRecord);
        insertData.put("records",insertRecords);

        /*
            新增数据
         */
        log.info(JSONObject.toJSONString(insertData));
        result = mdTableClient.createMultiRecords(baseId,sheetIdOrName,param,insertData);
        log.info(result);
        resultJSONObject = JSONObject.parseObject(result);
        aitableIdJSONArray = resultJSONObject.getJSONArray("value");
        if(aitableIdJSONArray!=null || !aitableIdJSONArray.isEmpty()){
            aitableId = aitableIdJSONArray.getJSONObject(0).getString("id");
        }
        return aitableId;
    }

    @Override
    public String updateSys1SaleData(String id,Map<String, Object> fields) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject updateData = new JSONObject();   //HTTP请求体
        JSONArray updateRecords = new JSONArray();  //更新记录集合
        JSONObject updateRecord = new JSONObject();      //更新记录行数据
        String result;  //返回结果

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "C1mtX34";

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        updateRecord.put("id",id);
        updateRecord.put("fields",fields);
        updateRecords.add(updateRecord);
        updateData.put("records",updateRecords);

        /*
            更新数据
         */
        log.info(JSONObject.toJSONString(updateData));
        result = mdTableClient.modifyMultiRecords(baseId,sheetIdOrName,param,updateData);
        log.info(result);
        return result;
    }

    @Override
    public String addCustomerABDevData(Map<String, Object> fields) throws Exception {
        /*
            参数定义
         */
        String baseId; //AI表格文档ID
        String sheetIdOrName;   //数据表ID或数据表名称
        Map<String,Object> param = new HashMap<>(); //HTTP请求参数
        JSONObject insertData = new JSONObject();   //HTTP请求体
        JSONArray insertRecords = new JSONArray();  //新增记录集合
        JSONObject insertRecord = new JSONObject();      //新增记录行数据
        String result;  //返回结果
        JSONObject resultJSONObject;
        JSONArray aitableIdJSONArray;
        String aitableId = "";   //AI表格记录ID

        /*
            项目总表
         */
        baseId = "G53mjyd80pEr5grBfpjmMX6586zbX04v";
        sheetIdOrName = "S4bMyJU";

        /*
            初始化参数值
         */
        param.put("operatorId","aj1wcWqKLXITiPDwbMIjUbAiEiE");  //操作人（Jason）的unionId
        insertRecord.put("fields",fields);
        insertRecords.add(insertRecord);
        insertData.put("records",insertRecords);

        /*
            新增数据
         */
        log.info(JSONObject.toJSONString(insertData));
        result = mdTableClient.createMultiRecords(baseId,sheetIdOrName,param,insertData);
        log.info(result);
        resultJSONObject = JSONObject.parseObject(result);
        aitableIdJSONArray = resultJSONObject.getJSONArray("value");
        if(aitableIdJSONArray!=null || !aitableIdJSONArray.isEmpty()){
            aitableId = aitableIdJSONArray.getJSONObject(0).getString("id");
        }
        return aitableId;
    }


}