md_YOS/src/custom/restcontroller/webmanage/saletool/orderclue/orderclue.java

package restcontroller.webmanage.saletool.orderclue;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;

import java.util.ArrayList;
import java.util.HashMap;

/**
 * 销售线索
 */
public class orderclue extends Controller {
    public orderclue(JSONObject content) throws YosException {
        super(content);
    }

    /**
     * 下载线索导入单模板
     *
     * @return
     */
    @API(title = "销售线索-下载线索导入单模板", apiversion = R.ID2026020914501501.v1.class)
    public String downloadOrderclueuploadbillExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrderclue");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        ExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        Rows aa = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();
    }

    /**
     * 下载线索导入单模板
     *
     * @return
     */
    @API(title = "销售线索-下载线索导入单模板", apiversion = R.ID2026020914503001.v1.class)
    public String downloadOrderclueuploadbillExcelByPlatform() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrderclue");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        ExportExcel.batchDetailByPlatform(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        Rows aa = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();
    }

    /**
     * 更新线索导入单
     *
     * @return
     */
    @API(title = "销售线索-导入线索导入单", apiversion = R.ID2026020914505001.v1.class)
    public String uploadOrderclueuploadbillByExcel() throws YosException {
        long sat_orderclueuploadbillid = createTableID("sat_orderclueuploadbill");

        InsertSQL insertorderclueuploadbill = SQLFactory.createInsertSQL(this, "sat_orderclueuploadbill");
        insertorderclueuploadbill.setValue("status", "待审核");
        insertorderclueuploadbill.setValue("uploadbillnum", createBillCode("default"));
        insertorderclueuploadbill.setValue("cluetype", content.getString("cluetype"));
        insertorderclueuploadbill.setUniqueid(sat_orderclueuploadbillid);
        insertorderclueuploadbill.insert();
        this.createFileLink("sat_orderclueuploadbill", sat_orderclueuploadbillid, "default", content.getLong("attachmentid"));

        ExcelFactory e;
        try {
            e = getExcelAttachment(content.getLong("attachmentid"));
            ArrayList<String> keys = new ArrayList<>();
            SQLDump sqlDump = new SQLDump();
            keys.add("name");
            keys.add("phonenumber");
            keys.add("province");
            keys.add("city");
            keys.add("county");
            keys.add("address");
            keys.add("cluesource");
            keys.add("notes");
            Rows rows = e.getSheetRows(0, keys, 2);
            int i = 0;
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            for (Row row : rows) {
                if (StringUtils.isEmpty(row.getString("phonenumber")) || StringUtils.isEmpty(row.getString("province")) || StringUtils.isEmpty(row.getString("city")) || StringUtils.isEmpty(row.getString("county")) || StringUtils.isEmpty(row.getString("cluesource"))) {
                    iserr = true;
                    row.put("msg", "错误信息:手机号，省市县及来源不能为空");
                    rowserr.add(row);
                } else {
                    rowssuc.add(row);
                }
            }
            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    long[] sat_orderclueuploadbillmxid = createTableID("sat_orderclueuploadbillmx", rowssuc.size());

                    InsertSQL orderclueuploadbillmxInsert = SQLFactory.createInsertSQL(this, "sat_orderclueuploadbillmx");
                    orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillmxid", sat_orderclueuploadbillmxid[i]);
                    orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillid", sat_orderclueuploadbillid);
                    orderclueuploadbillmxInsert.setValue("name", row.getString("name"));
                    orderclueuploadbillmxInsert.setValue("province", row.getString("province"));
                    orderclueuploadbillmxInsert.setValue("city", row.getString("city"));
                    orderclueuploadbillmxInsert.setValue("county", row.getString("county"));
                    orderclueuploadbillmxInsert.setValue("address", row.getString("address"));
                    orderclueuploadbillmxInsert.setValue("phonenumber", row.getString("phonenumber"));
                    orderclueuploadbillmxInsert.setValue("notes", row.getString("notes"));
                    orderclueuploadbillmxInsert.setValue("cluesource", row.getString("cluesource"));
                    orderclueuploadbillmxInsert.setValue("sat_orderclueid", 0);
                    sqlDump.add(orderclueuploadbillmxInsert);
                    i++;
                }
            }

            if (!sqlDump.isEmpty()) {
                sqlDump.commit();
            } else {
                dbConnect.runSqlUpdate("delete from sat_orderclueuploadbill where  sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sat_orderclueuploadbillerr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("name", "客户姓名");
                map.put("phonenumber", "手机号");
                map.put("province", "省");
                map.put("city", "市");
                map.put("county", "县");
                map.put("address", "地址");
                map.put("cluesource", "来源");
                map.put("notes", "备注");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<>();
                colNameList.add("name");
                colNameList.add("phonenumber");
                colNameList.add("province");
                colNameList.add("city");
                colNameList.add("county");
                colNameList.add("address");
                colNameList.add("cluesource");
                colNameList.add("notes");
                colNameList.add("msg");
                rowserr.setFieldList(colNameList);

                keytypemap.put("name", String.class);
                keytypemap.put("phonenumber", String.class);
                keytypemap.put("province", String.class);
                keytypemap.put("city", String.class);
                keytypemap.put("county", String.class);
                keytypemap.put("address", String.class);
                keytypemap.put("cluesource", String.class);
                keytypemap.put("notes", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldTypeMap(keytypemap);

                addSheet(excelFactory, "Sheet1", rowserr, map);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            dbConnect.runSqlUpdate("delete from sat_orderclueuploadbill where  sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();

    }

    /**
     * 更新线索导入单
     *
     * @return
     */
    @API(title = "销售线索-通过平台(抖音，快手)导入线索导入单", apiversion = R.ID2026020914511101.v1.class)
    public String uploadOrderclueuploadbillByExcelAndPlatform() throws YosException {
        long sat_orderclueuploadbillid = createTableID("sat_orderclueuploadbill");
        String billcode = createBillCode("default");

        InsertSQL insertorderclueuploadbill = SQLFactory.createInsertSQL(this, "sat_orderclueuploadbill");
        insertorderclueuploadbill.setValue("status", "待审核");
        insertorderclueuploadbill.setValue("uploadbillnum", billcode);
        insertorderclueuploadbill.setValue("cluetype", content.getString("cluetype"));
        insertorderclueuploadbill.setUniqueid(sat_orderclueuploadbillid);
        insertorderclueuploadbill.insert();

        this.createFileLink("sat_orderclueuploadbill", sat_orderclueuploadbillid, "default", content.getLong("attachmentid"));

        ExcelFactory e;
        try {
            // 华为云
            e = getPostExcel();
            ArrayList<String> keys = new ArrayList<>();
            SQLDump sqlDump = new SQLDump();
            keys.add("name");
            keys.add("phonenumber");
            keys.add("addressinfo");
            keys.add("address");
            keys.add("cluesource");
            keys.add("notes");
            Rows rows = e.getSheetRows(0, keys, 2);
            int i = 0;
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            String regex = "1[3-9][0-9]\\d{8}"; // 手机号码的格式：第一位只能为1，第二位可以是3，4，5，7，8，第三位到第十一位可以为0-9中任意一个数字
            for (Row row : rows) {
                JSONArray addressinfos = new JSONArray();
                String[] addressarray = {};
                if (!StringUtils.isBlank(row.getString("addressinfo"))) {
                    if (row.getString("addressinfo").startsWith("[")) {
                        addressinfos = JSONArray.parseArray(row.getString("addressinfo"));
                    } else if (row.getString("addressinfo").contains("-")) {
                        addressarray = row.getString("addressinfo").split("-");
                    }
                }

                if (StringUtils.isBlank(row.getString("phonenumber")) || StringUtils.isBlank(row.getString("addressinfo")) || StringUtils.isBlank(row.getString("cluesource"))) {
                    iserr = true;
                    row.put("msg", "错误信息:手机号，省市县信息及来源不能为空");
                    rowserr.add(row);
                } else {
                    if (!row.getString("phonenumber").matches(regex)) {
                        iserr = true;
                        row.put("msg", "错误信息:手机号格式不正确");
                        rowserr.add(row);
                    } else if (addressinfos.size() < 2 && addressarray.length < 2) {
                        iserr = true;
                        row.put("msg", "错误信息:省市县信息中缺少省市");
                        rowserr.add(row);
                    } else {
                        rowssuc.add(row);
                    }
                }
            }
            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    long[] sat_orderclueuploadbillmxid = createTableID("sat_orderclueuploadbillmx", rowssuc.size());


                    InsertSQL orderclueuploadbillmxInsert = SQLFactory.createInsertSQL(this, "sat_orderclueuploadbillmx");
                    orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillmxid", sat_orderclueuploadbillmxid[i]);
                    orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillid", sat_orderclueuploadbillid);
                    orderclueuploadbillmxInsert.setValue("name", row.getString("name"));
                    orderclueuploadbillmxInsert.setValue("province", row.getString("province"));
                    orderclueuploadbillmxInsert.setValue("city", row.getString("city"));
                    orderclueuploadbillmxInsert.setValue("county", row.getString("county"));
                    orderclueuploadbillmxInsert.setValue("address", row.getString("address"));
                    orderclueuploadbillmxInsert.setValue("phonenumber", row.getString("phonenumber"));
                    orderclueuploadbillmxInsert.setValue("notes", row.getString("notes"));
                    orderclueuploadbillmxInsert.setValue("cluesource", row.getString("cluesource"));
                    orderclueuploadbillmxInsert.setValue("sat_orderclueid", 0);
                    if (row.getString("addressinfo").startsWith("[")) {
                        JSONArray addressinfos = JSONArray.parseArray(row.getString("addressinfo"));
                        if (addressinfos.size() == 3) {
                            orderclueuploadbillmxInsert.setValue("province", addressinfos.get(0));
                            orderclueuploadbillmxInsert.setValue("city", addressinfos.get(1));
                            orderclueuploadbillmxInsert.setValue("county", addressinfos.get(2));
                        } else if (addressinfos.size() == 2) {
                            orderclueuploadbillmxInsert.setValue("province", addressinfos.get(0));
                            orderclueuploadbillmxInsert.setValue("city", addressinfos.get(1));
                            orderclueuploadbillmxInsert.setValue("county", "");
                        } else {
                            orderclueuploadbillmxInsert.setValue("province", "");
                            orderclueuploadbillmxInsert.setValue("city", "");
                            orderclueuploadbillmxInsert.setValue("county", "");
                        }
                    } else if (row.getString("addressinfo").contains("-")) {
                        String[] addressarray = row.getString("addressinfo").split("-");
                        if (addressarray.length == 3) {
                            orderclueuploadbillmxInsert.setValue("province", addressarray[0]);
                            orderclueuploadbillmxInsert.setValue("city", addressarray[1]);
                            orderclueuploadbillmxInsert.setValue("county", addressarray[2]);
                        } else if (addressarray.length == 2) {
                            orderclueuploadbillmxInsert.setValue("province", addressarray[0]);
                            orderclueuploadbillmxInsert.setValue("city", addressarray[1]);
                            orderclueuploadbillmxInsert.setValue("county", "");
                        } else {
                            orderclueuploadbillmxInsert.setValue("province", "");
                            orderclueuploadbillmxInsert.setValue("city", "");
                            orderclueuploadbillmxInsert.setValue("county", "");
                        }
                    }
                    sqlDump.add(orderclueuploadbillmxInsert);
                    i++;
                }
            }
            if (!sqlDump.isEmpty()) {
                sqlDump.commit();
            } else {
                dbConnect.runSqlUpdate("delete from sat_orderclueuploadbill where  sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sat_orderclueuploadbillerr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("name", "客户姓名");
                map.put("phonenumber", "手机号");
                map.put("province", "省市县信息");
                map.put("address", "地址");
                map.put("cluesource", "来源");
                map.put("notes", "备注");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("name");
                colNameList.add("phonenumber");
                colNameList.add("addressinfo");
                colNameList.add("address");
                colNameList.add("cluesource");
                colNameList.add("notes");
                colNameList.add("msg");
                keytypemap.put("name", String.class);
                keytypemap.put("phonenumber", String.class);
                keytypemap.put("addressinfo", String.class);
                keytypemap.put("address", String.class);
                keytypemap.put("cluesource", String.class);
                keytypemap.put("notes", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet1(excelFactory, "Sheet1", rowserr, map);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }
        } catch (Exception e1) {
            dbConnect.runSqlUpdate("delete from sat_orderclueuploadbill where  sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    /**
     * 更新线索导入单
     *
     * @return
     */
    @API(title = "销售线索-更新线索-导入单", apiversion = R.ID2026020914513301.v1.class)
    public String updateOrderclueuploadbill() throws YosException {
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        String notes = content.getString("notes");
        Rows rows = dbConnect.runSqlQuery("select sat_orderclueuploadbillid FROM sat_orderclueuploadbill WHERE siteid = '" + siteid + "' AND sat_orderclueuploadbillid = '" + sat_orderclueuploadbillid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("线索导入单不存在，无法更新").toString();
        }
        SQLFactory.createUpdateSQL(this, "sat_orderclueuploadbill").setValue("notes", notes).setUniqueid(sat_orderclueuploadbillid).update();
        return queryOrderclueuploadbillMain();
    }

    @API(title = "销售线索-导入列表查询", apiversion = R.ID20221101094702.v1.class)
    public String queryOrderclueuploadbillList() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_orderclueuploadbill", getTableColumnNames("sat_orderclueuploadbill").toArray(new String[0])).setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sat_orderclueuploadbillmx", "t2", "t1.sat_orderclueuploadbillid=t2.sat_orderclueuploadbillid");
        querySQL.addQueryFields("orderclueuploadcount", "count(t2.sat_orderclueuploadbillid)");
        querySQL.addGroupBy();
        querySQL.setWhere("siteid", siteid);
        Rows rows;
        if (rowindex > 0) {
            rows = querySQL.query(rowindex);
            long sat_orderclueuploadbillid = rows.get(0).getLong("sat_orderclueuploadbillid");
            content.put("sat_orderclueuploadbillid", sat_orderclueuploadbillid);
            return queryOrderclueuploadbillMain();
        } else {
            querySQL.setPage(pageSize, pageNumber);
            rows = querySQL.query();
            return getSucReturnObject().setData(rows).toString();
        }
    }

    @API(title = "销售线索-导入详情", apiversion = R.ID2026020914515801.v1.class)
    public String queryOrderclueuploadbillMain() throws YosException {
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_orderclueuploadbill");
        querySQL.setUniqueid(sat_orderclueuploadbillid);
        Rows rows = querySQL.query();

        for (Row row : rows) {
            Rows orderclueuploadbillmxRows = dbConnect.runSqlQuery("select sat_orderclueid from sat_orderclueuploadbillmx where sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
            row.put("xiansuocount", orderclueuploadbillmxRows.size());
            row.put("isdistribution", orderclueuploadbillmxRows.toArrayList("sat_orderclueid", new ArrayList<Long>()).stream().filter(num -> num > 0).toList().isEmpty() ? "否" : "是");
        }
        return getSucReturnObject().setData(!rows.isEmpty() ? rows.get(0) : new Row()).toString();
    }

    @API(title = "销售线索-导入详情明细", apiversion = R.ID20221102100102.v1.class)
    public String queryOrderclueuploadbillMxMain() throws YosException {
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        QuerySQL orderclueuploadbillmxQuery = SQLFactory.createQuerySQL(this, "sat_orderclueuploadbillmx", "*").setTableAlias("t1");
        orderclueuploadbillmxQuery.addJoinTable(JOINTYPE.inner, "sat_orderclueuploadbill", "t2", "t1.sat_orderclueuploadbillid=t2.sat_orderclueuploadbillid", "cluetype");
        orderclueuploadbillmxQuery.setWhere("t1.sat_orderclueuploadbillid", sat_orderclueuploadbillid);
        orderclueuploadbillmxQuery.setPage(pageSize, pageNumber);
        Rows rowsdetaill = orderclueuploadbillmxQuery.query();
        return getSucReturnObject().setData(rowsdetaill).toString();
    }

    @API(title = "销售线索-导入删除", apiversion = R.ID20221101100702.v1.class)
    public String delete() throws YosException {
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        Rows rowscount = dbConnect.runSqlQuery("select status from sat_orderclueuploadbill where sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
        if (rowscount.isEmpty()) {
            return getErrReturnObject().setErrMsg("此销售线索导入单不存在，无法删除销售线索导入单").toString();
        }
        if (!rowscount.get(0).getString("status").equals("待审核")) {
            return getErrReturnObject().setErrMsg("已审核状态的销售线索导入单无法删除销售线索导入单").toString();
        }
        SQLDump sqlDump = new SQLDump();
        sqlDump.add(SQLFactory.createDeleteSQL(this, "sat_orderclueuploadbill").setUniqueid(sat_orderclueuploadbillid));
        sqlDump.add(SQLFactory.createDeleteSQL(this, "sat_orderclueuploadbillmx").setWhere("sat_orderclueuploadbillid", sat_orderclueuploadbillid));
        sqlDump.commit(this);
        return getSucReturnObject().toString();
    }

    /**
     * 线索导入单审核
     *
     * @return
     */
    @API(title = "销售线索-审核反审核", apiversion = R.ID2026020914522001.v1.class)
    public String check() throws YosException {
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        SQLDump sqlDump = new SQLDump();
        boolean ischeck = content.getBoolean("ischeck");
        Rows rowsstatus = dbConnect.runSqlQuery("select status from sat_orderclueuploadbill where sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
        if (rowsstatus.isEmpty()) {
            return getErrReturnObject().setErrMsg("此销售线索导入单不存在，无法审核").toString();
        }
        if (ischeck && !rowsstatus.get(0).getString("status").equals("待审核")) {
            return getErrReturnObject().setErrMsg("已审核的销售线索导入单无法再次审核").toString();
        }
        if (!ischeck && !rowsstatus.get(0).getString("status").equals("已审核")) {
            return getErrReturnObject().setErrMsg("待审核的销售线索导入单无法反审核").toString();
        }
        if (!ischeck) {
            Rows rowscount = dbConnect.runSqlQuery("select count(1) statuscount from sat_orderclueuploadbillmx t1 inner join sat_orderclue t2 on t1.sat_orderclueid=t2.sat_orderclueid and t1.siteid=t2.siteid  where t1.sat_orderclueuploadbillid=" + sat_orderclueuploadbillid + " and  t2.status <>'待分配'");
            if (rowscount.get(0).getLong("statuscount") > 0) {
                return getErrReturnObject().setErrMsg("关联的销售线索池中存在非待分配的线索，无法反审核").toString();
            }
        }
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sat_orderclueuploadbill");
        updateSQL.setValue("status", ischeck ? "已审核" : "待审核");
        updateSQL.setValue("checkby", ischeck ? username : null);
        updateSQL.setValue("checkdate", ischeck ? getDateTime_Str() : null);
        updateSQL.setUniqueid(sat_orderclueuploadbillid);
        sqlDump.add(updateSQL);
        sqlDump.add(geSqlStatement(ischeck, sat_orderclueuploadbillid));
        sqlDump.commit(this);
        return getSucReturnObject().toString();
    }

    @API(title = "销售线索-导入明细新增修改", apiversion = R.ID20230221092603.v1.class)
    public String insertormodify_Orderclueuploadbillmx() throws YosException {
        long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        long sat_orderclueuploadbillmxid = content.getLong("sat_orderclueuploadbillmxid");
        String name = content.getString("name");
        String province = content.getString("province");
        String city = content.getString("city");
        String county = content.getString("county");
        String address = content.getString("address");
        String phonenumber = content.getString("phonenumber");
        String notes = content.getString("notes");
        String cluesource = content.getString("cluesource");

        Rows rowscount = dbConnect.runSqlQuery("select status from sat_orderclueuploadbill where sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
        if (rowscount.isEmpty()) {
            return getErrReturnObject().setErrMsg("此销售线索导入单不存在").toString();
        }
        if (!rowscount.get(0).getString("status").equals("待审核")) {
            return getErrReturnObject().setErrMsg("已审核状态的销售线索导入单无法新增或更新明细内容").toString();
        }

        if (sat_orderclueuploadbillmxid <= 0 || dbConnect.runSqlQuery("select sat_orderclueuploadbillmxid from sat_orderclueuploadbillmx where sat_orderclueuploadbillmxid=" + sat_orderclueuploadbillmxid).isEmpty()) {
            sat_orderclueuploadbillmxid = createTableID("sat_orderclueuploadbillmx");
            InsertSQL orderclueuploadbillmxInsert = SQLFactory.createInsertSQL(this, "sat_orderclueuploadbillmx");
            orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillmxid", sat_orderclueuploadbillmxid);
            orderclueuploadbillmxInsert.setValue("sat_orderclueuploadbillid", sat_orderclueuploadbillid);
            orderclueuploadbillmxInsert.setValue("name", name);
            orderclueuploadbillmxInsert.setValue("province", province);
            orderclueuploadbillmxInsert.setValue("city", city);
            orderclueuploadbillmxInsert.setValue("county", county);
            orderclueuploadbillmxInsert.setValue("address", address);
            orderclueuploadbillmxInsert.setValue("phonenumber", phonenumber);
            orderclueuploadbillmxInsert.setValue("notes", notes);
            orderclueuploadbillmxInsert.setValue("cluesource", cluesource);
            orderclueuploadbillmxInsert.setValue("sat_orderclueid", 0);
            orderclueuploadbillmxInsert.insert();
        } else {
            UpdateSQL orderclueuploadbillmxUpdate = SQLFactory.createUpdateSQL(this, "sat_orderclueuploadbillmx");
            orderclueuploadbillmxUpdate.setValue("name", name);
            orderclueuploadbillmxUpdate.setValue("province", province);
            orderclueuploadbillmxUpdate.setValue("city", city);
            orderclueuploadbillmxUpdate.setValue("county", county);
            orderclueuploadbillmxUpdate.setValue("address", address);
            orderclueuploadbillmxUpdate.setValue("phonenumber", phonenumber);
            orderclueuploadbillmxUpdate.setValue("notes", notes);
            orderclueuploadbillmxUpdate.setValue("cluesource", cluesource);
            orderclueuploadbillmxUpdate.setUniqueid(sat_orderclueuploadbillmxid);
            orderclueuploadbillmxUpdate.update();
        }
        return queryOrderclueuploadbillMxMain();
    }

    @API(title = "销售线索-导入明细删除", apiversion = R.ID20221101100802.v1.class)
    public String deletemx() throws YosException {
        Long sat_orderclueuploadbillmxid = content.getLong("sat_orderclueuploadbillmxid");
        Long sat_orderclueuploadbillid = content.getLong("sat_orderclueuploadbillid");
        Rows rowscount = dbConnect.runSqlQuery("select status from sat_orderclueuploadbill where sat_orderclueuploadbillid=" + sat_orderclueuploadbillid);
        if (rowscount.isEmpty()) {
            return getErrReturnObject().setErrMsg("此销售线索导入单不存在，无法删除导入明细").toString();
        }
        if (!rowscount.get(0).getString("status").equals("待审核")) {
            return getErrReturnObject().setErrMsg("已审核状态的销售线索导入单无法删除明细内容").toString();
        }
        SQLFactory.createDeleteSQL(this, "sat_orderclueuploadbillmx").setUniqueid(sat_orderclueuploadbillmxid).delete();
        return getSucReturnObject().toString();
    }

    // 审核反审核时新增或删除线索池的sql语句汇总
    public SQLDump geSqlStatement(boolean ischeck, Long sat_orderclueuploadbillid) throws YosException {
        SQLDump sqlDump = new SQLDump();
        QuerySQL orderclueuploadbillmxQuery = SQLFactory.createQuerySQL(this, "sat_orderclueuploadbillmx", "name", "province", "city", "county", "address", "notes", "sat_orderclueid", "sat_orderclueuploadbillmxid", "sat_orderclueuploadbillid", "phonenumber", "cluesource").setTableAlias("t1");
        orderclueuploadbillmxQuery.addJoinTable(JOINTYPE.inner, "sat_orderclueuploadbill", "t2", "t1.sat_orderclueuploadbillid=t2.sat_orderclueuploadbillid", "cluetype");
        orderclueuploadbillmxQuery.setWhere("t1.sat_orderclueuploadbillid", sat_orderclueuploadbillid);
        Rows rowsdetaill = orderclueuploadbillmxQuery.query();
        if (ischeck) {
            for (Row row : rowsdetaill) {
                Long sat_orderclueid = createTableID("sat_orderclue");
                InsertSQL orderclueInsert = SQLFactory.createInsertSQL(this, "sat_orderclue");
                orderclueInsert.setValue("sat_orderclueid", sat_orderclueid);
                orderclueInsert.setValue("name", row.getString("name"));
                orderclueInsert.setValue("province", row.getString("province"));
                orderclueInsert.setValue("city", row.getString("city"));
                orderclueInsert.setValue("county", row.getString("county"));
                orderclueInsert.setValue("address", row.getString("address"));
                orderclueInsert.setValue("phonenumber", row.getString("phonenumber"));
                orderclueInsert.setValue("notes", row.getString("notes"));
                orderclueInsert.setValue("cluesource", row.getString("cluesource"));
                orderclueInsert.setValue("cluetype", row.getString("cluetype"));
                orderclueInsert.setValue("isprivate", false);
                orderclueInsert.setValue("status", "待分配");
                sqlDump.add(orderclueInsert);
                sqlDump.add(SQLFactory.createUpdateSQL(this, "sat_orderclueuploadbillmx").setValue("sat_orderclueid", sat_orderclueid).setUniqueid(row.getLong("sat_orderclueuploadbillmxid")));
            }
        } else {
            sqlDump.add(SQLFactory.createDeleteSQL(this, "sat_orderclue").setWhere("sat_orderclueid", rowsdetaill.toArrayList("sat_orderclueid")));
            sqlDump.add(SQLFactory.createUpdateSQL(this, "sat_orderclueuploadbillmx").setValue("sat_orderclueid", 0).setWhere("sat_orderclueuploadbillmxid", rowsdetaill.toArrayList("sat_orderclueuploadbillmxid")));
        }
        return sqlDump;
    }

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows, HashMap<String, String> titlemap) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 2);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger(keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong(keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat(keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble(keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString(keylist.get(i1)));
                }
                if (i1 == 8) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    public XSSFSheet addSheet1(ExcelFactory excelFactory, String sheetname, Rows datarows, HashMap<String, String> titlemap) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        ExportExcel.batchDetailErr1(sheet, titleCellStyle1, titleCellStyle2, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 2);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger(keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong(keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat(keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble(keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString(keylist.get(i1)));
                }
                if (i1 == 6) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
}