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.*;

/**
 * 专用于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;    //新老客户

        /*
            项目总表
         */
        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());
        customerName = "";
        businessUserUnionId = "";
        businessUserName = "";
        kp = "";
        for(int i=0;i<allRecords.size();i++){
            /*
                重置是否为空标识
             */
            isCustomerNameEmpty = false;
            isBusinessUserEmpty = false;
            isKpEmpty = false;

            //获取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)
                ){
                    //不变
                }else if("E".equals(customerLevel)){
                    customerLevel = "保稳E";
                }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);
            }
             */

            /*
                汇总客户数据
             */
            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);
            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 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;
    }


}