YOS_PRSX/src/custom/restcontroller/webmanage/sale/contract/Contract.java

package restcontroller.webmanage.sale.contract;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.brand.Brand;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.itemclass.ItemClass;
import beans.itemprice.ItemPrice;
import beans.order.Order;
import beans.quotedprice.Quotedprice;
import beans.parameter.Parameter;
import beans.recycle.Recycle;
import beans.reward.LockEntry;
import beans.reward.Reward;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.datadashboards.DataDashboards;
import restcontroller.webmanage.sale.dataoverview.customersdata.customersdata;
import restcontroller.webmanage.sale.hrreward.HrCashBill;
import restcontroller.webmanage.sale.rebate.Rebate;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Set;
import java.util.concurrent.locks.ReentrantLock;

import static beans.order.Order.updateRebateFee;
import static restcontroller.webmanage.sale.contract.JJContract.idLocks;

/**
 * 合同管理
 */
public class Contract extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Contract(JSONObject content) throws YosException {
        super(content);
    }

    public String getBillNo(Long sa_contractid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT billno from sa_contract WHERE sa_contractid= " + sa_contractid + " and siteid = '" + siteid + "'");
        String billno = "";
        if (rows.isNotEmpty()) {
            billno = rows.get(0).getString("billno");
        }
        return billno;
    }

    public Row getItemRow(Long sa_contract_itemsid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT * from plm_item WHERE isshow=1 and itemid in ( SELECT itemid from sa_contract_items WHERE sa_contract_itemsid = " + sa_contract_itemsid + ") and siteid ='" + siteid + "'");
        return rows.isNotEmpty() ? rows.get(0) : new Row();
    }

    @API(title = "合同中的业务员选择", apiversion = R.ID20230305201202.v1.class)
    public String getSale() throws YosException {

        Long type = content.getLongValue("type");
        Long sys_enterpriseid = 0L;
        Rows rows = new Rows();
        SQLFactory sqlFactory = null;
        switch (type.toString()) {
            //经销商合作协议
            case "1":
                sys_enterpriseid = content.getLong("sys_enterpriseid");
                sqlFactory = new SQLFactory(this, "经销商授权的业务员");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                break;
            //经销项目协议,直销项目协议,居间协议
            case "2":
                Long sa_projectid = content.getLong("sa_projectid");
                sqlFactory = new SQLFactory(this, "项目中的业务员");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_projectid", sa_projectid);

                break;
            case "3":
                //工具借用协议，选择的企业（客户负责人、经销商授权业务员）的负责人
                sys_enterpriseid = content.getLong("sys_enterpriseid");
                sqlFactory = new SQLFactory(this, "企业中的业务员");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                break;
            case "4":
                //居间协议，选择客户合同
                sys_enterpriseid = content.getLong("sys_enterpriseid");
                sqlFactory = new SQLFactory(this, "客户负责人");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                break;
        }
        if (sqlFactory != null) {
            String sql = sqlFactory.getSQL();
            rows = dbConnect.runSqlQuery(sql);
        }

        return getSucReturnObject().setData(rows).toString();
    }



    @API(title = "作废", apiversion = R.ID20221121202502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20221223102102.v1.class})
    public String delete() throws YosException {
        JSONArray sa_contractids = content.getJSONArray("sa_contractids");
        Rows rows = dbConnect.runSqlQuery("select 1 from sa_contract where siteid='" + siteid + "' and (status = '已提交' or (status='审核' and CURRENT_DATE >= begdate and CURRENT_DATE <= enddate)) and sa_contractid in "
                + sa_contractids.toString().replace("[", "(").replace("]", ")"));
        if (rows.isNotEmpty()) {
            return getReturnObject().setCode("0199", new String[0]).toString();//提交状态或审核状态的合同无法作废
        }

        SQLFactory sqlFactory = new SQLFactory(this, "合同-作废");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_contractid", sa_contractids.toArray());
        dbConnect.runSqlUpdate(sqlFactory);
        //新增删除或作废原因
        DataExtend.createDeleteReasonSql(this, "sa_contract", sa_contractids.toJavaList(Long.class), content.getStringValue("deletereason"));
        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : sa_contractids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", id, "作废", "作废合同至回收站").getSQL());


            ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_contract", id).toArrayList("userid", new ArrayList<>());
            JSONObject extradata = new JSONObject();
            extradata.put("extraUserList", userids);
            Executor.sendEml_controller(this, "contract_zf", id, extradata);
        }
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "提交", apiversion = R.ID20221121202802.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20221223102102.v1.class}, apiClass = Contract.class)
    public String submit() throws YosException {
        JSONArray sa_contractids = content.getJSONArray("sa_contractids");

        for (Object object : sa_contractids) {
            Rows rows = dbConnect.runSqlQuery("SELECT * from sa_contract_items WHERE sa_contractid=" + object + " and itemid=0 and siteid='" + siteid + "'");

            for (Row row : rows) {
                long sa_lectotypecfgid = row.getLong("sa_lectotypecfgid");
                Rows lectotypecfrows = dbConnect.runSqlQuery("SELECT `status` from sa_lectotypecfg WHERE  sa_lectotypecfgid='" + sa_lectotypecfgid + "' and siteid='" + siteid + "'");
                if (lectotypecfrows.isNotEmpty()) {
                    String status = lectotypecfrows.get(0).getString("status");
                    if (status.contains("评审拒绝") || status.contains("待评审")) {
                        return getErrReturnObject().setErrMsg("报价中存在未通过的成品配置.").toString();
                    }
                }
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "合同-提交");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_contractid", sa_contractids.toArray());
        dbConnect.runSqlUpdate(sqlFactory);
        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : sa_contractids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", id, "提交", "提交合同" + getBillNo(id)).getSQL());

            ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_contract", id).toArrayList("userid", new ArrayList<>());
            JSONObject extradata = new JSONObject();
            extradata.put("extraUserList", userids);
            Executor.sendEml_controller(this, "contract_tj", id, extradata);
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "审核", apiversion = R.ID20221121203102.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class,
            R.ID20221223102102.v1.class, R.ID20240829103004.v1.class, R.ID20240828165904.v1.class, R.ID20230104194002.v1.class,
            R.ID20230105194902.v1.class, R.ID20230105092302.v1.class, R.ID20240926150804.v1.class, R.ID20230713132804.class},
            apiClass = {Contract.class, Rebate.class, HrCashBill.class, DataDashboards.class, customersdata.class})
    public String audit() throws YosException {
        Long type = content.getLongValue("type");
        String status = type == 1 ? "审核" : "新建";
        Long sa_contractid = content.getLong("sa_contractid");

        String sql = "SELECT * from sa_contract WHERE siteid='" + siteid + "' and  sa_contractid = " + sa_contractid;
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isEmpty()) {
            return getReturnObject().setCode("0200", new String[0]).toString();//合同不存在，无法审核

        }

        if (rows.get(0).getString("type").equals("居间")) {
            Long temp_sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
            Long ascription_contractid = rows.get(0).getLong("ascription_contractid");
            String begdate = rows.get(0).getString("begdate");
            String enddate = rows.get(0).getString("enddate");
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_contract WHERE deleted=0 and status='审核' and type='居间' AND ascription_contractid=" + ascription_contractid + " and sys_enterpriseid=" + temp_sys_enterpriseid + " and (current_date<=enddate and current_date>=begdate) and (('" + begdate + "'>=begdate and '" + begdate + "'<=enddate) or ('" + enddate + "'>=begdate and '" + enddate + "'<=enddate) ) AND siteid='" + siteid + "'").isNotEmpty()) {
                return getReturnObject().setCode("0683", new String[0]).toString();//当前客户/项目合同已关联该居间商的居间协议
            }
        }


        String contractType = rows.get(0).getString("type");
        Long temp_sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
        Long sa_projectid = rows.get(0).getLong("sa_projectid");
        String submitby = rows.get(0).getString("submitby");
        if (contractType.equals("框架") || contractType.equals("直销")) {
            SQLFactory sqlFactory = new SQLFactory(this, "查询框架合同审核");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_contractid", sa_contractid);

            if (dbConnect.runSqlQuery(sqlFactory).isNotEmpty()) {
                return getReturnObject().setCode("0201", new String[0]).toString();//审核失败，有效期内只能存在一份审核通过的经销商合作协议或直销合作协议合同

            }
        }


        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "合同-审核");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("status", status);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlList.add(sqlFactory.getSQL());


        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, status, "合同" + status + rows.get(0).getString("billno")).getSQL());
        if (siteid.equals("HY") && contractType.equals("居间")) {
            LockEntry lockentry = idLocks.computeIfAbsent(sa_contractid, k -> new LockEntry());
            lockentry.addUsersingcount();
            ReentrantLock lock = lockentry.getLock();
            lock.lock();
            try {
                JJContract.checkcontract(this, sa_contractid, sqlList);
            } catch (Exception e) {
                throw new YosException(e);
            } finally {
                lock.unlock();
                lockentry.subUsersingcount();
                JJContract.clearMap();
            }
        } else {
            dbConnect.runSqlUpdate(sqlList);
        }
        if (status.equals("审核")) {
            ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_contract", sa_contractid).toArrayList("userid", new ArrayList<>());
            ArrayList<Long> submitbyRows = dbConnect.runSqlQuery("select t1.userid from sys_users t1 left join sys_usersite t2 on t1.userid = t2.userid where siteid='"
                    + siteid + "' and name='" + submitby + "'").toArrayList("userid", new ArrayList<>());
            userids.addAll(submitbyRows);
            JSONObject extradata = new JSONObject();
            extradata.put("extraUserList", userids);
            Executor.sendEml_controller(this, "contract_sh", sa_contractid, extradata);
        }
        updateCustomerStatus(sa_contractid, temp_sys_enterpriseid);
//        //将符合的项目变更为已成交
//        Rows projectRows = dbConnect.runSqlQuery("SELECT distinct t1.sa_projectid FROM sa_contract t1 WHERE t1.sa_contractid =" + sa_contractid +
//                " and not exists(select 1 from sa_project where t1.siteid=siteid and t1.sa_projectid=sa_projectid and status='已成交')");
//        for (Row row : projectRows) {
//            ArrayList<Long> useridList = new ArrayList<>();
//            long sa_projectid1 = row.getLong("sa_projectid");
//            useridList.addAll(DataTeam.queryTeamRowWithoutHeadpic(this, "sa_project", sa_projectid1).toArrayList("userid", new ArrayList<>()));
//            useridList.addAll(DataTeam.getProxyUserids(this, "sa_project", sa_projectid1));
//            HashMap<String, Object> extradata = new HashMap<>();
//            extradata.put("extraUserList", useridList);
//            Executor.sendEml(this, "project_cj", sa_projectid1, siteid, extradata);
//        }
//        dbConnect.runSqlUpdate("UPDATE sa_project SET `status`='已成交' WHERE sa_projectid = " + rows.get(0).getLong("sa_projectid"));
//        ArrayList<String> stageList = dbConnect.runSqlQuery("select stagename from sa_project_stage where siteid='" + siteid + "' and sa_projectid = " + rows.get(0).getLong("sa_projectid")).toArrayList("stagename");
//        if (stageList.contains("供货及服务")) {
//            dbConnect.runSqlUpdate("UPDATE sa_project_stage SET `active`=0 WHERE sa_projectid = " + rows.get(0).getLong("sa_projectid"));
//            dbConnect.runSqlUpdate("UPDATE sa_project_stage SET `active`=1 WHERE sa_projectid = " + rows.get(0).getLong("sa_projectid") + " and stagename='供货及服务'");
//        }

        if (contractType.equals("居间")) {
            if (sa_projectid > 0) {
                rows = dbConnect.runSqlQuery("SELECT sa_project_partiesid from sa_project_parties WHERE sa_projectid = " + sa_projectid + " and sys_enterpriseid = " + temp_sys_enterpriseid + " and siteid = '" + siteid + "'");
                if (rows.isNotEmpty()) {
                    Long id = rows.get(0).getLong("sa_project_partiesid");
                    dbConnect.runSqlUpdate("UPDATE sa_project_parties SET type='居间服务商' WHERE sa_project_partiesid = " + id);
                } else {
                    sqlFactory = new SQLFactory(this, "新增缔约方");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("sa_project_partiesid", createTableID("sa_project_parties"));
                    sqlFactory.addParameter("sa_projectid", sa_projectid);
                    sqlFactory.addParameter("sys_enterpriseid", temp_sys_enterpriseid);
                    dbConnect.runSqlUpdate(sqlFactory);
                }
                DataTag.createTag(this, "sa_project", sa_projectid, "居间");
            }
            Long ascription_contractid = rows.get(0).getLong("ascription_contractid");
            //刷新订单的居间费
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order", "sa_orderid", "sonum")
                    .setTableAlias("t1");
            querySQL.addJoinTable(JOINTYPE.inner, "sa_contract", "t2", "t2.ascription_contractid=t1.sa_contractid and t2.siteid=t1.siteid");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("t1.STATUS in ('审核','关闭') and t1.checkdate>=t2.begdate and t1.checkdate<=t2.enddate");
            querySQL.setWhere("t2.sa_contractid", sa_contractid);
            Rows orderrows = querySQL.query();
            RowsMap rowsMap = orderrows.toRowsMap("sa_orderid");
            ArrayList<String> sa_orderids = orderrows.toArrayList("sa_orderid");
            for (String sa_orderid : sa_orderids) {
                String sonum = rowsMap.getOrDefault(sa_orderid, new Rows()).get(0).getString("sonum");
                updateRebateFee(this, Long.valueOf(sa_orderid), "订单审核", "由订单【" + sonum + "】审核生成", sa_contractid);
            }

        }
        //刷新未关闭订单的收款条件
        if (type == 1 && !contractType.equals("居间")) {
            sqlList.clear();
            Rows orderRows = dbConnect.runSqlQuery("SELECT sa_orderid from sa_order WHERE status!='关闭' and deleted=0 and sa_contractid=" + sa_contractid + "  and siteid='" + siteid + "'");
            String paytype = rows.get(0).getString("paytype");
            Rows receivableruleRows = dbConnect.runSqlQuery("select sa_receivableruleid,accountingnodes from sa_receivablerule where siteid='" + siteid + "' and '" + paytype + "' like concat(ruleno,'-','%')");
            if (receivableruleRows.isNotEmpty() && orderRows.isNotEmpty()) {
                for (Row orderRow : orderRows) {
                    DeleteSQL deleteSQL=SQLFactory.createDeleteSQL(this,"sa_order_receivablerule");
                    deleteSQL.setSiteid(siteid);
                    deleteSQL.setWhere("sa_orderid",orderRow.getLong("sa_orderid"));
                    sqlList.add(deleteSQL.getSQL());
                    JSONObject accountingnodes = receivableruleRows.get(0).getJSONObject("accountingnodes");
                    Long sa_receivableruleid = receivableruleRows.get(0).getLong("sa_receivableruleid");
                    for (String key : accountingnodes.keySet()) {
                        JSONArray jsonArray = accountingnodes.getJSONArray(key);
                        long[] sa_order_receivableruleids = createTableID("sa_order_receivablerule", jsonArray.size());
                        int i = 0;
                        for (Object o : jsonArray) {
                            JSONObject jsonObject = (JSONObject) o;
                            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_order_receivablerule");
                            insertSQL.setSiteid(siteid);
                            insertSQL.setValue("isused", jsonObject.getString("isused"));
                            insertSQL.setValue("days", jsonObject.getString("days"));
                            insertSQL.setValue("amountrate", jsonObject.getString("amountrate"));
                            insertSQL.setValue("iswarranty", jsonObject.getString("iswarranty"));
                            insertSQL.setValue("point", jsonObject.getString("point"));
                            insertSQL.setValue("sa_orderid", orderRow.getLong("sa_orderid"));
                            insertSQL.setValue("sa_receivableruleid", sa_receivableruleid);
                            insertSQL.setValue("sa_order_receivableruleid", sa_order_receivableruleids[i]);
                            sqlList.add(insertSQL.getSQL());
                            i++;
                        }
                    }
                }
            }
            if(sqlList.size()>0){
                dbConnect.runSqlUpdate(sqlList);
            }
        }

        return getSucReturnObject().toString();
    }

    public void updateCustomerStatus(Long sa_contractid, Long sys_enterpriseid) throws YosException {
        //将符合的客户变更为已成交
        String sql = "SELECT COUNT(0) count from sa_contract WHERE sys_enterpriseid = " + sys_enterpriseid + " and `status`='审核'  and deleted=0";
        Row row = dbConnect.runSqlQuery(sql).get(0);
        Rows customersRows = dbConnect.runSqlQuery("select sa_customersid from sa_customers where siteid='" + siteid + "' and sys_enterpriseid=" + sys_enterpriseid);
        Long count = row.getLong("count");
        if (count >= 1) {
            dbConnect.runSqlUpdate("UPDATE sa_customers SET status='合作中' WHERE sys_enterpriseid = (SELECT sys_enterpriseid FROM sa_contract WHERE sa_contractid =" + sa_contractid + " )");
            if (customersRows.isNotEmpty()) {
                Long sa_customersid = customersRows.get(0).getLong("sa_customersid");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "潜在");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "已终止");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "暂缓");
                DataTag.createTag(this, "sa_customers", sa_customersid, "合作中");
            }
        } else {
            dbConnect.runSqlUpdate("UPDATE sa_customers SET status='潜在' WHERE sys_enterpriseid = (SELECT sys_enterpriseid FROM sa_contract WHERE sa_contractid =" + sa_contractid + " )");
            if (customersRows.isNotEmpty()) {
                Long sa_customersid = customersRows.get(0).getLong("sa_customersid");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "合作中");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "已终止");
                DataTag.deleteTag(this, "sa_customers", sa_customersid, "暂缓");
                DataTag.createTag(this, "sa_customers", sa_customersid, "潜在");
            }
        }
    }

    @API(title = "反审核", apiversion = R.ID20221212135402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class,
            R.ID20221223102102.v1.class, R.ID20240829103004.v1.class, R.ID20240828165904.v1.class, R.ID20230104194002.v1.class,
            R.ID20230105194902.v1.class, R.ID20230105092302.v1.class, R.ID20240926150804.v1.class, R.ID20230713132804.class},
            apiClass = {Contract.class, Rebate.class, HrCashBill.class, DataDashboards.class, customersdata.class})
    public String cancelAudit() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Rows rows = dbConnect.runSqlQuery("select * from sa_contract where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "合同-反审核");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_contractid", sa_contractid);
        sqlList.add(sqlFactory.getSQL());
        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "反审核", "反审核合同" + getBillNo(sa_contractid)).getSQL());


        Long sys_enterpriseid = 0L;
        String contractType = "";
        ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_contract", sa_contractid).toArrayList("userid", new ArrayList<>());
        if (rows.isNotEmpty()) {
            ArrayList<Long> submitbyRows = dbConnect.runSqlQuery("select t1.userid from sys_users t1 left join sys_usersite t2 on t1.userid = t2.userid where siteid='"
                    + siteid + "' and name='" + rows.get(0).getString("submitby") + "'").toArrayList("userid", new ArrayList<>());
            userids.addAll(submitbyRows);
            sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
            contractType = rows.get(0).getString("type");

        }

        if (siteid.equals("HY") && contractType.equals("居间")) {

            LockEntry lockentry = idLocks.computeIfAbsent(sa_contractid, k -> new LockEntry());
            lockentry.addUsersingcount();
            ReentrantLock lock = lockentry.getLock();
            lock.lock();
            try {
                JJContract.checkcontract(this, sa_contractid, sqlList);
            } catch (Exception e) {
                throw new YosException(e);
            } finally {
                lock.unlock();
                lockentry.subUsersingcount();
                JJContract.clearMap();
            }
        } else {
            dbConnect.runSqlUpdate(sqlList);
        }

        JSONObject extradata = new JSONObject();
        extradata.put("extraUserList", userids);
        Executor.sendEml_controller(this, "contract_fsh", sa_contractid, extradata);
        updateCustomerStatus(sa_contractid, sys_enterpriseid);
        return getSucReturnObject().toString();
    }

    @API(title = "撤回", apiversion = R.ID20230130091502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20221223102102.v1.class}, apiClass = Contract.class)
    public String revoke() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "合同-撤回");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_contractid", sa_contractid);
        sqlList.add(sqlFactory.getSQL());
        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "撤回", "撤回合同" + getBillNo(sa_contractid)).getSQL());

        dbConnect.runSqlUpdate(sqlList);

        ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_contract", sa_contractid).toArrayList("userid", new ArrayList<>());
        JSONObject extradata = new JSONObject();
        extradata.put("extraUserList", userids);
        Executor.sendEml_controller(this, "contract_ch", sa_contractid, extradata);

        return getSucReturnObject().toString();
    }

    @API(title = "查询产品列表", apiversion = R.ID20221122165302.v1.class)
    @CACHEING
    public String selectItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Boolean isExport = content.getBooleanValue("isExport");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.material like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }


        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同-产品列表", pageSize, pageNumber, pageSorting);
        if (isExport) {
            sqlFactory = new SQLFactory(this, "合同-产品列表");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        ArrayList<Long> itemids = rows.toArrayList("itemid", new ArrayList<>());

        RowsMap itemclassRowsMap = ItemClass.getItemClassNameRowsMap(this, itemids);


        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, itemids);
        //附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", itemids);
        for (Row row : rows) {
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            Long itemid = row.getLong("itemid");
            Long sa_lectotypecfgid = row.getLong("sa_lectotypecfgid");
            if (itemid == 0) {
                Rows extendrows = dbConnect.runSqlQuery("SELECT caliber,nominalpressure FROM plm_itemextend t1 INNER JOIN sa_lectotypecfg_item t2 ON t2.part_itemid=t1.itemid WHERE sa_lectotypecfgid=" + sa_lectotypecfgid + " AND parttype='主阀' and t1.siteid='" + siteid + "'");
                if (extendrows.isNotEmpty()) {
                    row.put("caliber", extendrows.get(0).getString("caliber"));
                    row.put("nominalpressure", extendrows.get(0).getJSONArray("nominalpressure"));
                }
            }
        }

        if (isExport) {
            Rows contractRows = dbConnect.runSqlQuery("select type from sa_contract where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);
            if (contractRows.isEmpty()) {
                return getReturnObject().setCode("0202", new String[0]).toString();//合同不存在
            }
            String type = contractRows.get(0).getString("type");
            ExcelFactory excelFactory = new ExcelFactory("报价单明细导出");

            XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("报价单明细导出");
            XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
            ExportExcel.setBatchDetailSheetColumn3(sheet);// 设置工作薄列宽
            XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
            XSSFCellStyle titleCellStyle2 = ExportExcel.createBodyCellStyle1(xssfFWorkbook);
            ExportExcel.download(sheet, titleCellStyle1, titleCellStyle2, rows, type);// 写入标题

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

    @API(title = "编辑产品", apiversion = R.ID20221122200302.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class}, apiClass = Contract.class)
    public String editItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        JSONArray items = content.getJSONArray("items");
        ArrayList<String> sqlList = new ArrayList<>();
        ArrayList<Long> itemids = new ArrayList<>();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            Long id = item.getLongValue("sa_contract_itemsid");
            BigDecimal price = item.getBigDecimal("price");
            Rows rows = dbConnect.runSqlQuery("SELECT * from sa_contract_items WHERE sa_contract_itemsid=" + id + " and siteid='" + siteid + "'");
            if (rows.isNotEmpty()) {
                if (rows.get(0).getLong("itemid") == 0) {
                    BigDecimal tempprice = rows.get(0).getBigDecimal("price");
                    if (tempprice.compareTo(price) > 0) {
                        //当前配置不存在成品，价格只能往上调整。
                        return getReturnObject().setCode("0692", new String[0]).toString();
                    }
                }
            }
            SQLFactory sqlFactory = new SQLFactory(this, "合同-更新产品");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_contract_itemsid", id);
            if (item.getBigDecimalValue("qty").compareTo(BigDecimal.ZERO) == 0) {
                return getReturnObject().setCode("0203", new String[0]).toString();//添加失败
            }
            sqlFactory.addParameter("qty", item.getBigDecimal("qty"));
            sqlFactory.addParameter("price", item.getBigDecimal("price"));
            sqlFactory.addParameter("discountrate", item.getBigDecimal("discountrate"));
            sqlFactory.addParameter("remarks", item.getStringValue("remarks"));
            sqlFactory.addParameter("type", item.getStringValue("type"));
            sqlFactory.addParameter("marketprice", item.getBigDecimal("marketprice"));
            sqlFactory.addParameter("model", item.getStringValue("model"));
            String sql = sqlFactory.getSQL();
            sqlList.add(sql);
            Row row = getItemRow(id);
            itemids.add(row.getLong("itemid"));
            String itemno = row.isEmpty() ? "" : row.getString("itemno");
            boolean istool = row.isEmpty() ? false : row.getBoolean("istool");
            if (istool) {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "编辑工具", "编辑工具" + itemno).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "编辑产品", "编辑产品" + itemno).getSQL());

            }
        }


        dbConnect.runSqlUpdate(sqlList);

        SQLFactory sqlFactory = new SQLFactory(this, "合同-产品列表2");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_in("itemid", itemids);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

//        Rows rows = dbConnect.runSqlQuery("select ifnull(sum(price*qty),0) signamount from sa_contract_items WHERE sa_contractid = " + sa_contractid);
//        String signamount = rows.get(0).getBigDecimal("signamount").toPlainString();

//        JSONObject jsonObject = new JSONObject();
//        jsonObject.put("signamount", signamount);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "获取项目报价单", apiversion = R.ID20221222151302.v1.class)
    public String getProjectQuotedPrice() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }
        String quotedpricerecheck = Parameter.get(siteid, "quotedpricerecheck");
        SQLFactory sqlFactory = new SQLFactory(this, "查询项目报价单列表", pageSize, pageNumber, pageSorting);
//        if (quotedpricerecheck.equals("1")) {
//            sqlFactory = new SQLFactory(this, "查询项目报价单列表_特价审批", pageSize, pageNumber, pageSorting);
//        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        RowsMap quotedpriceRowsMap = Quotedprice.getSpecialoffer(this, rows.toArrayList("sa_quotedpriceid", new ArrayList<>()));
        RowsMap leaderRowsMap = DataTeam.getLeaderWithoutHeadpic(this, "sa_quotedprice", rows.toArrayList("sa_quotedpriceid")).toRowsMap("ownerid");
        for (Row row : rows) {
            String sa_quotedpriceid = row.getString("sa_quotedpriceid");
            Rows specialofferRows = quotedpriceRowsMap.get(sa_quotedpriceid);
            if (quotedpriceRowsMap.containsKey(sa_quotedpriceid)) {
                row.put("specialoffer", specialofferRows.get(0).getBigDecimal("specialoffer").compareTo(BigDecimal.ZERO) > 0 ? 1 : 0);
            } else {
                row.put("specialoffer", 0);
            }
            row.put("leader", leaderRowsMap.get(sa_quotedpriceid));
        }
        return getSucReturnObject().setData(rows).toString();

    }

    @API(title = "查询可添加的商品（报价单里的产品列表）", apiversion = R.ID20221123162902.v1.class)
    public String canAddItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Long sa_quotedpriceid = content.getLong("sa_quotedpriceid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

            if (whereObject.containsKey("standards") && !"".equals(whereObject.getString("standards"))) {
                where.append(" and(");
                where.append("t2.standards like'%").append(whereObject.getString("standards")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {
                where.append(" and t2.itemid in( SELECT itemid from  sa_itemsaleclass WHERE itemclassid =" + whereObject.getLong("itemclassid") + " and siteid='" + siteid + "')");

            }
            if (whereObject.containsKey("sa_brandid") && !"".equals(whereObject.getString("sa_brandid"))) {
                where.append(" and t2.itemid in( SELECT DISTINCT itemid from sa_itemsaleclass WHERE itemclassid in (SELECT itemclassid from plm_itemclass WHERE sa_brandid = " + whereObject.getLong("sa_brandid") + " and siteid = '" + siteid + "'))");
            }
            if (whereObject.containsKey("model") && !"".equals(whereObject.getString("model"))) {
                where.append(" and(").append("t2.model like'%").append(whereObject.getString("model")).append("%')");
            }
            if (whereObject.containsKey("spec") && !"".equals(whereObject.getString("spec"))) {
                where.append(" and(").append("t2.spec like'%").append(whereObject.getString("spec")).append("%') ");
            }
            if (whereObject.containsKey("material") && !"".equals(whereObject.getString("material"))) {
                where.append(" and(").append("t3.material like'%").append(whereObject.getString("material")).append("%') ");
            }
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and t2.itemid in( SELECT itemid from  plm_item_tradefield WHERE tradefield ='" + whereObject.getString("tradefield") + "' and siteid='" + siteid + "')");

            }
        }
        if (Parameter.get(siteid, "isitemclass").equals("1") && userInfo.isSaler()) {
            ArrayList<Long> saleItemclassids = ItemClass.getSaleItemclass(this);
            if (saleItemclassids.size() > 0) {
                where.append(" and t1.itemid in( SELECT itemid from  sa_itemsaleclass WHERE itemclassid in (" + StringUtils.join(saleItemclassids, ",") + ") and siteid='" + siteid + "')");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同-查询可添加的商品(项目)", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter("sa_quotedpriceid", sa_quotedpriceid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        ArrayList<Long> itemids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap itemclassRowsMap = ItemClass.getItemClassNameRowsMap(this, itemids);

        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, itemids);
        //附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", itemids);
        for (Row row : rows) {
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("discountDifferenceAmount", row.getBigDecimal("marketprice").subtract(row.getBigDecimal("price")));
        }


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询可添加的商品（所有）", apiversion = R.ID20221124093602.v1.class)
    public String canAddAllItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

            if (whereObject.containsKey("istool") && !"".equals(whereObject.getString("istool"))) {
                where.append(" and(");
                where.append("t1.istool =").append(whereObject.getBooleanValue("istool")).append(" ");
                where.append(")");
            }

            if (whereObject.containsKey("standards") && !"".equals(whereObject.getString("standards"))) {
                where.append(" and(");
                where.append("t1.standards like'%").append(whereObject.getString("standards")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {
                where.append(" and t1.itemid in( SELECT itemid from  sa_itemsaleclass WHERE itemclassid =" + whereObject.getLong("itemclassid") + " and siteid='" + siteid + "')");

            }
            if (whereObject.containsKey("sa_brandid") && !"".equals(whereObject.getString("sa_brandid"))) {
                where.append(" and t1.itemid in( SELECT DISTINCT itemid from sa_itemsaleclass WHERE itemclassid in (SELECT itemclassid from plm_itemclass WHERE sa_brandid = " + whereObject.getLong("sa_brandid") + " and siteid = '" + siteid + "'))");
            }
            if (whereObject.containsKey("model") && !"".equals(whereObject.getString("model"))) {
                where.append(" and(").append("t1.model like'%").append(whereObject.getString("model")).append("%')");
            }
            if (whereObject.containsKey("spec") && !"".equals(whereObject.getString("spec"))) {
                where.append(" and(").append("t1.spec like'%").append(whereObject.getString("spec")).append("%') ");
            }
            if (whereObject.containsKey("material") && !"".equals(whereObject.getString("material"))) {
                where.append(" and(").append("t3.material like'%").append(whereObject.getString("material")).append("%') ");
            }


        }
        if (Parameter.get(siteid, "isitemclass").equals("1") && userInfo.isSaler()) {
            ArrayList<Long> saleItemclassids = ItemClass.getSaleItemclass(this);
            if (saleItemclassids.size() > 0) {
                where.append(" and t1.itemid in( SELECT itemid from  sa_itemsaleclass WHERE itemclassid in (" + StringUtils.join(saleItemclassids, ",") + ") and siteid='" + siteid + "')");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同-查询可添加的商品", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        ArrayList<Long> itemids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap itemclassRowsMap = ItemClass.getItemClassNameRowsMap(this, itemids);
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, itemids);
        //附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", itemids);
        for (Row row : rows) {
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            try {
                ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
                row.put("oldprice", itemPrice.getPrice());
                row.put("gradeprice", itemPrice.getGraderateprice().toPlainString());
            } catch (Exception e) {
                e.printStackTrace();
                row.put("oldprice", 0);
                row.put("gradeprice", 0);
            }
        }


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "判断商品（报价单里的产品列表）是否重复", apiversion = R.ID20230221151903.v1.class)
    public String hasRepeatItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        JSONArray itemids = content.getJSONArray("itemids");

        String sql = "select * from sa_contract_items WHERE itemid in " + itemids + " and siteid ='" + siteid + "' and sa_contractid=" + sa_contractid;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isEmpty()) {
            return getSucReturnObject().setData(false).toString();
        }
        return getSucReturnObject().setData(true).toString();
    }

    @API(title = "添加商品", apiversion = R.ID20221123164402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20230221151903.v1.class})
    public String addItem() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        JSONArray items = content.getJSONArray("items");
        ArrayList<String> sqlList = new ArrayList<>();
        ArrayList<Long> itemids = new ArrayList<>();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            itemids.add(item.getLong("itemid"));
            sqlList.add(addItemSql(sa_contractid, item.getLong("itemid"), item.getBigDecimal("price"), item.getBigDecimalValue("discountrate", 1), item.getBigDecimal("qty"), item.getBigDecimal("marketprice"), item.getString("type")));
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemno", "istool");
        querySQL.setTableAlias("t1");
        querySQL.setWhere("t1.itemid", itemids.toArray());
        Rows query = querySQL.query();
        for (Row row : query) {
            boolean istool = row.getBoolean("istool");
            if (istool) {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "添加工具", "添加工具" + row.getString("itemno")).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "添加产品", "添加产品" + row.getString("itemno")).getSQL());
            }
        }
        dbConnect.runSqlUpdate(sqlList);

        SQLFactory sqlFactory = new SQLFactory(this, "合同-产品列表2");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_in("itemid", itemids);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "删除商品", apiversion = R.ID20221123164502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20230221151903.v1.class})
    public String deleteItem() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        ArrayList<String> sqlList = new ArrayList<>();
        JSONArray sa_contract_itemsids = content.getJSONArray("sa_contract_itemsids");

        String sql = "DELETE from sa_contract_items WHERE sa_contract_itemsid in " + sa_contract_itemsids + " and siteid ='" + siteid + "'";
        sql = sql.replace("[", "(").replace("]", ")");
        sqlList.add(sql);

        for (Object obj : sa_contract_itemsids) {
            Long sa_contract_itemsid = Long.valueOf(obj.toString());
            Row row = getItemRow(sa_contract_itemsid);
            String itemno = row.isEmpty() ? "" : row.getString("itemno");
            //删除选型配置单
            Rows rows = dbConnect.runSqlQuery("SELECT sa_lectotypecfgid from sa_contract_items WHERE sa_contract_itemsid=" + sa_contract_itemsid + " and siteid='" + siteid + "'");
            if (rows.isNotEmpty() && rows.get(0).getLong("sa_lectotypecfgid") > 0) {
                DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sa_lectotypecfg");
                deleteSQL.setSiteid(siteid);
                deleteSQL.setUniqueid(rows.get(0).getLong("sa_lectotypecfgid"));
                deleteSQL.setWhere("status", "待评审");
                sqlList.add(deleteSQL.getSQL());
            }

            boolean istool = row.isEmpty() ? false : row.getBoolean("istool");
            if (istool) {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "删除工具", "删除工具" + itemno).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "删除产品", "删除产品" + itemno).getSQL());

            }
        }
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "添加条款", apiversion = R.ID20221128144502.v1.class)
    public String addClause() throws YosException {

        Long sa_contractid = content.getLong("sa_contractid");
        JSONArray items = content.getJSONArray("items");
        String editable = "1";
        ArrayList<String> sqlList = new ArrayList<>();
        ArrayList<Long> ids = new ArrayList<>();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            Long id = item.getLong("sa_contract_clauseid");
            String contentStr = item.getStringValue("content");
            if (id <= 0) {
                id = createTableID("sa_contract_clause");
                sqlList.add(addContractClauseSql(sa_contractid, id, contentStr, editable));
            } else {
                sqlList.add(updateContractClauseSql(sa_contractid, id, contentStr));
            }
            ids.add(id);
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "添加", "合同添加条款" + sa_contractid).getSQL());

        dbConnect.runSqlUpdate(sqlList);


        String sql = "SELECT * from sa_contract_clause WHERE sa_contract_clauseid in " + ids;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows rows = dbConnect.runSqlQuery(sql);


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "删除", apiversion = R.ID20221128160302.v1.class)
    public String deleteClause() throws YosException {


        JSONArray sa_contract_clauseids = content.getJSONArray("sa_contract_clauseids");

        String sql = "DELETE from sa_contract_clause WHERE sa_contract_clauseid in " + sa_contract_clauseids + " and siteid ='" + siteid + "'";
        sql = sql.replace("[", "(").replace("]", ")");
        dbConnect.runSqlUpdate(sql);


        return getSucReturnObject().toString();
    }

    @API(title = "重置", apiversion = R.ID20221128161602.v1.class)
    public String reset() throws YosException {


        Long sa_contractid = content.getLong("sa_contractid");

        dbConnect.runSqlUpdate("DELETE from sa_contract_clause WHERE sa_contractid = " + sa_contractid);

        Rows rows = dbConnect.runSqlQuery("SELECT type,typemx from sa_contract WHERE sa_contractid= " + sa_contractid);
        if (rows.isNotEmpty()) {
            addContractClause(sa_contractid, rows.get(0).getString("type"), rows.get(0).getString("typemx"));
        }

        return getSucReturnObject().toString();
    }

    @API(title = "条款列表", apiversion = R.ID20221128162302.v1.class)
    public String clauseList() throws YosException {


        Long sa_contractid = content.getLong("sa_contractid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.content like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同-条款-列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "业绩目标", apiversion = R.ID20221209150102.v1.class)
    @CACHEING
    public String getTarget() throws YosException {
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        Long sa_contractid = content.getLong("sa_contractid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("year") && !"".equals(whereObject.getString("year"))) {
                where.append(" and(");
                where.append("year ='").append(whereObject.getString("year")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("tradefield ='").append(whereObject.getString("tradefield")).append("' ");
                where.append(")");
            }

        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同-业绩目标");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "终止合同", apiversion = R.ID20221212102802.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20221223102102.v1.class, R.ID20230713132804.class},
            apiClass = {DataDashboards.class, customersdata.class})
    public String endContract() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");

        ArrayList<String> sqlList = new ArrayList<>();

        String sql = "SELECT sys_enterpriseid,status from sa_contract WHERE sa_contractid =" + sa_contractid + " and siteid = '" + siteid + "'";
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isEmpty()) {
            return getReturnObject().setCode("0204", new String[0]).toString();//未找到终止合同
        }
        Long sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
        sqlList.add("UPDATE sa_contract SET `status`='已终止' WHERE  sa_contractid =" + sa_contractid + " and siteid = '" + siteid + "'");
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "终止", "终止合同" + getBillNo(sa_contractid)).getSQL());

        dbConnect.runSqlUpdate(sqlList);

        updateCustomerStatus(sa_contractid, sys_enterpriseid);
        return getSucReturnObject().toString();
    }

    @API(title = "查询我负责的项目合同列表", apiversion = R.ID20221217145402.v1.class)
    public String getMyInChargeProjectContract() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        Long sa_projectid = 0L;
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (");
                where.append("t1.title like '%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.billno like '%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.projectname like '%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.projectnum like '%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.name like '%").append(whereObject.getString("condition")).append("%' ");
                where.append(" )");
            }
            //查询指定项目下的我负责的项目合同列表
            if (whereObject.containsKey("sa_projectid") && !"".equals(whereObject.getString("sa_projectid"))) {
                sa_projectid = whereObject.getLongValue("sa_projectid");
                where.append(" and(t1.sa_projectid = '").append(sa_projectid).append("')");
            }


        }
        //项目商机中
        if (sa_projectid > 0) {
            //当前项目负责人的合同
            where.append(" and t1.sa_contractid in ( SELECT ownerid FROM sys_datateam WHERE ownertable='sa_contract' AND isleader=1 AND siteid='");
            where.append(siteid);
            where.append("' AND userid IN (SELECT userid FROM sys_datateam WHERE ownertable='sa_project' AND isleader=1 AND siteid='");
            where.append(siteid);
            where.append("' AND ownerid='");
            where.append(sa_projectid).append("'))");
        } else {
            //合同中使用
            //业务员
//            if (isSaler()) {
//                //选择操作人负责的项目合同（包括经销、直销项目合同）
//                where = " (" + where + ") and (" + DataTeam.getDataWhereStr(this, "sa_contract", "t1", 1) + ")";
//            } else {
//                //选择操作人参与团队的负责人的名下负责的项目合同（包括经销、直销项目合同）
//                where = " (" + where + ") and (" + getDataWhereStr(this, "sa_contract", "t1") + ")";
//            }

//            ArrayList<Long> userids = DataTeam.getProxyUserids(this);
//
//            where = " (" + where + ") and (" + getDataWhereStr(this, userids, "sa_contract", "t1") + " )";
            where.append(" and (");
            where.append("(").append(DataTeam.getDataWhereStr(this, "sa_contract", "t1", 1)).append(")");
            where.append(" or (").append(DataTeam.getDataWhereStr(this, "sa_contract", "t1", 2)).append(")");
            where.append(")");
        }


        SQLFactory sqlFactory = new SQLFactory(this, "项目合同列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        //查询居间商信息
        ArrayList<Long> sa_projectids = rows.toArrayList("sa_projectid", new ArrayList<>());
        sqlFactory = new SQLFactory(this, "查询项目居间商信息");
        sqlFactory.addParameter_in("sa_projectid", sa_projectids);
        sqlFactory.addParameter("siteid", siteid);
        RowsMap rowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("sa_projectid");

        for (Row row : rows) {
            row.put("parties", rowsMap.getOrDefault(row.getString("sa_projectid"), new Rows()));
        }


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询我负责的客户合同列表", apiversion = R.ID20240509112704.v1.class)
    public String getMyInChargeCustomersContract() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (");
                where.append("t1.title like '%").append(whereObject.getString("condition")).append("%'");
                where.append("or t1.billno like '%").append(whereObject.getString("condition")).append("%'");
                where.append("or t2.enterprisename like '%").append(whereObject.getString("condition")).append("%'");
                where.append("or t3.name like '%").append(whereObject.getString("condition")).append("%'");
                where.append(")");
            }
        }

        where.append(" and (");
        where.append("(").append(DataTeam.getDataWhereStr(this, "sa_contract", "t1", 1)).append(")");
        where.append(" or (").append(DataTeam.getDataWhereStr(this, "sa_contract", "t1", 2)).append(")");
        where.append(")");

        SQLFactory sqlFactory = new SQLFactory(this, "客户合同列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        return getSucReturnObject().setData(rows).toString();
    }

    //选择操作人及代理人负责的项目合同（包括经销、直销项目合同）
    public static String getDataWhereStr(Controller controller, ArrayList<Long> userids, String ownertable, String alias) throws YosException {
        String str = " exists(select sys_datateamid from sys_datateam where siteid='" + controller.siteid +
                "' and isleader=1 and userid in " + userids + "  and ownertable='" + ownertable + "' and ownerid=" + alias + "." + controller.getuniquecolumnname(ownertable) + " )";
        str = str.replace("[", "(").replace("]", ")");
        return str;
    }

    @API(title = "查询经销商的合同列表", apiversion = R.ID20221219141702.v1.class)
    @CACHEING
    public String getAgentContract() throws YosException {
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t1.sa_contractid like '%" + whereObject.getString("condition")
                        + "%' or t1.title like '%" + whereObject.getString("condition")
                        + "%' or t1.billno like '%" + whereObject.getString("condition")
                        + "%' or t3.projectname like '%" + whereObject.getString("condition") + "%')";
            }


        }

        Long sys_enterpriseid = content.getLong("sys_enterpriseid");


        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商的合同列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());
        //查询价格
        RowsMap signAmountRowsMap = beans.contract.Contract.getContractSignAmount(this, ids);

        for (Row row : rows) {
            Long id = row.getLong("sa_contractid");
            if (signAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("signamount", signAmountRowsMap.get(id.toString()).get(0).getBigDecimal("signamount").toPlainString());
            } else {
                row.put("signamount", 0);
            }

        }


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "保存合同条款信息", apiversion = R.ID20230117112002.v1.class)
    public String setContractclause() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        JSONObject dataextend = content.getJSONObject("dataextend");
        String deliverymode = content.getStringValue("deliverymode");
        String paytype = content.getStringValue("paytype");
        JSONObject parta = content.getJSONObject("parta");
        JSONObject partb = content.getJSONObject("partb");

        SQLFactory sqlFactory = new SQLFactory(this, "保存甲乙合同条款信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter("dataextend", dataextend);
        sqlFactory.addParameter("deliverymode", deliverymode);
        sqlFactory.addParameter("paytype", paytype);
        sqlFactory.addParameter("parta", parta);
        sqlFactory.addParameter("partb", partb);

        String sql = sqlFactory.getSQL();
        dbConnect.runSqlUpdate(sql);


        return getContractclause();
    }

    @API(title = "获取合同条款信息", apiversion = R.ID20230117112102.v1.class)
    public String getContractclause() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        SQLFactory sqlFactory = new SQLFactory(this, "查询甲乙合同条款信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        for (Row row : rows) {
            //已方
            if (row.getJSONObject("partb").isEmpty()) {
                JSONObject obj = new JSONObject();
                obj.put("freightpayer", "0");
                row.put("partb", obj);


                sqlFactory = new SQLFactory(this, "查询企业落款信息");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                Rows rowsA = dbConnect.runSqlQuery(sqlFactory);
                JSONObject objectA = row.getJSONObject("partb");

                if (rowsA.isNotEmpty()) {
                    objectA.putAll(rowsA.get(0));
                }
                row.put("partb", objectA);
            }


            //甲方
            if (row.getJSONObject("parta").isEmpty()) {
                JSONObject obj = new JSONObject();
                obj.put("freightpayer", "1");
                row.getJSONObject("parta").put("parta", obj);

                sqlFactory = new SQLFactory(this, "查询当前站点企业落款信息");
                sqlFactory.addParameter("siteid", siteid);
                Rows rowsB = dbConnect.runSqlQuery(sqlFactory);
                JSONObject objectB = row.getJSONObject("parta");
                if (rowsB.isNotEmpty()) {
                    objectB.putAll(rowsB.get(0));
                }
                row.put("parta", objectB);
            }

            //扩展信息
            if (row.getJSONObject("dataextend").isEmpty()) {
                JSONObject obj = new JSONObject();
                obj.put("pressure", "");
                row.put("dataextend", obj);
            }
        }
        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Row()).toString();
    }

    @API(title = "查询企业落款信息", apiversion = R.ID20230118150402.v1.class)
    public String selectSignInfo() throws YosException {

        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询企业落款信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);

        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "产品信息同步合作协议", apiversion = R.ID20231204153904.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class}, apiClass = Contract.class)
    public String synchronousContractItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Rows contractRows = dbConnect.runSqlQuery("select * from sa_contract where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);
        if (contractRows.isEmpty()) {
            return getReturnObject().setCode("0205", new String[0]).toString();//无效合同

        }
        String type = contractRows.get(0).getString("type");
        if (!type.equals("项目")) {
            return getReturnObject().setCode("0206", new String[0]).toString();//该类型合同无此功能
        }
        Long sys_enterpriseid = contractRows.get(0).getLong("sys_enterpriseid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract", "sa_contractid");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.status ='审核' and t1.enddate >= current_date  and t1.sys_enterpriseid=" + sys_enterpriseid + " and type in ('框架','直销')");
        Rows query = querySQL.query();
        if (query.isEmpty()) {
            return getReturnObject().setCode("0207", new String[0]).toString();//当前企业不存在经销商合作协议或者直销客户合作协议

        }
        querySQL = SQLFactory.createQuerySQL(this, "sa_contract_items");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", query.get(0).getLong("sa_contractid"));
        Rows contactitemsRows = querySQL.query();
        if (contactitemsRows.isEmpty()) {
            return getSucReturnObject().toString();
        }
        RowsMap contactItemsRowsMap = contactitemsRows.toRowsMap("itemid");
        querySQL = SQLFactory.createQuerySQL(this, "sa_contract_items");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", sa_contractid);
        contactitemsRows = querySQL.query();
        Boolean isadd = content.getBoolean("isadd");

        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : contactitemsRows) {
            String itemid = row.getString("itemid");
            if (contactItemsRowsMap.containsKey(itemid)) {
                Row stayItemsRow = contactItemsRowsMap.get(itemid).get(0);
                UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_contract_items");
                updateSQL.setValue("changeuserid", userid);
                updateSQL.setValue("changeby", username);
                updateSQL.setDateValue("changedate");
                updateSQL.setValue("type", stayItemsRow.getString("type"));
                updateSQL.setValue("discountrate", stayItemsRow.getBigDecimal("discountrate"));
                updateSQL.setValue("price", stayItemsRow.getBigDecimal("price"));
                updateSQL.setValue("qty", stayItemsRow.getBigDecimal("qty"));
                updateSQL.setValue("marketprice", stayItemsRow.getBigDecimal("marketprice"));
                updateSQL.setSiteid(siteid);
                updateSQL.setWhere("itemid", itemid);
                updateSQL.setWhere("sa_contractid", sa_contractid);
                sqlList.add(updateSQL.getSQL());
                contactItemsRowsMap.remove(itemid);
            }
        }
        if (isadd) {
            long[] sa_contract_items = createTableID("sa_contract_items", contactItemsRowsMap.size());
            int i = 0;
            for (String itemid : contactItemsRowsMap.keySet()) {
                Row stayItemsRow = contactItemsRowsMap.get(itemid).get(0);
                InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_contract_items");
                insertSQL.setValue("sa_contract_itemsid", sa_contract_items[i]);
                insertSQL.setValue("siteid", siteid);
                insertSQL.setValue("createby", username);
                insertSQL.setDateValue("createdate");
                insertSQL.setValue("changeuserid", userid);
                insertSQL.setValue("changeby", username);
                insertSQL.setDateValue("changedate");
                insertSQL.setValue("createuserid", userid);
                insertSQL.setValue("sa_contractid", sa_contractid);
                insertSQL.setValue("itemid", itemid);
                insertSQL.setValue("type", stayItemsRow.getString("type"));
                insertSQL.setValue("discountrate", stayItemsRow.getBigDecimal("discountrate"));
                insertSQL.setValue("price", stayItemsRow.getBigDecimal("price"));
                insertSQL.setValue("qty", stayItemsRow.getBigDecimal("qty"));
                insertSQL.setValue("marketprice", stayItemsRow.getBigDecimal("marketprice"));
                sqlList.add(insertSQL.getSQL());
                i++;
            }
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "同步框架协议产品", "同步合作协议的产品").getSQL());
        } else {
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "同步框架协议产品", "同步合作协议的产品折扣价格").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "居间合同产品信息同步关联合同", apiversion = R.ID20240518155604.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class}, apiClass = Contract.class)
    public String JJsynchronousContractItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Rows contractRows = dbConnect.runSqlQuery("select * from sa_contract where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);
        if (contractRows.isEmpty()) {
            return getReturnObject().setCode("0205", new String[0]).toString();//无效合同

        }
        String type = contractRows.get(0).getString("type");
        if (!type.equals("居间")) {
            return getReturnObject().setCode("0206", new String[0]).toString();//该类型合同无此功能

        }
        Long ascription_contractid = contractRows.get(0).getLong("ascription_contractid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract_items");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", ascription_contractid);
        Rows contactitemsRows = querySQL.query();
        if (contactitemsRows.isEmpty()) {
            return getSucReturnObject().toString();
        }
        RowsMap contactItemsRowsMap = contactitemsRows.toRowsMap("itemid");
        querySQL = SQLFactory.createQuerySQL(this, "sa_contract_items");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", sa_contractid);
        contactitemsRows = querySQL.query();

        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : contactitemsRows) {
            String itemid = row.getString("itemid");
            if (contactItemsRowsMap.containsKey(itemid)) {
                Row stayItemsRow = contactItemsRowsMap.get(itemid).get(0);
                UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_contract_items");
                updateSQL.setValue("changeuserid", userid);
                updateSQL.setValue("changeby", username);
                updateSQL.setDateValue("changedate");
                updateSQL.setValue("type", stayItemsRow.getString("type"));
                updateSQL.setValue("discountrate", stayItemsRow.getBigDecimal("discountrate"));
                updateSQL.setValue("price", stayItemsRow.getBigDecimal("price"));
                updateSQL.setValue("qty", stayItemsRow.getBigDecimal("qty"));
                updateSQL.setValue("marketprice", stayItemsRow.getBigDecimal("marketprice"));
                updateSQL.setSiteid(siteid);
                updateSQL.setWhere("itemid", itemid);
                updateSQL.setWhere("sa_contractid", sa_contractid);
                sqlList.add(updateSQL.getSQL());
                contactItemsRowsMap.remove(itemid);
            }
        }
        long[] sa_contract_items = createTableID("sa_contract_items", contactItemsRowsMap.size());
        int i = 0;
        for (String itemid : contactItemsRowsMap.keySet()) {
            Row stayItemsRow = contactItemsRowsMap.get(itemid).get(0);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_contract_items");
            insertSQL.setValue("sa_contract_itemsid", sa_contract_items[i]);
            insertSQL.setValue("siteid", siteid);
            insertSQL.setValue("createby", username);
            insertSQL.setDateValue("createdate");
            insertSQL.setValue("changeuserid", userid);
            insertSQL.setValue("changeby", username);
            insertSQL.setDateValue("changedate");
            insertSQL.setValue("createuserid", userid);
            insertSQL.setValue("sa_contractid", sa_contractid);
            insertSQL.setValue("itemid", itemid);
            insertSQL.setValue("type", stayItemsRow.getString("type"));
            insertSQL.setValue("discountrate", stayItemsRow.getBigDecimal("discountrate"));
            insertSQL.setValue("price", stayItemsRow.getBigDecimal("price"));
            insertSQL.setValue("qty", stayItemsRow.getBigDecimal("qty"));
            insertSQL.setValue("marketprice", stayItemsRow.getBigDecimal("marketprice"));
            sqlList.add(insertSQL.getSQL());
            i++;
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "同步关联合同产品", "同步关联合同的产品").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "居间合同产品类别信息同步关联合同", apiversion = R.ID20240518160504.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221124135602.class, R.ID20221124140102.v1.class, R.ID20230219202203.v1.class}, apiClass = Contract.class)
    public String JJsynchronousContractItemsClass() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Rows contractRows = dbConnect.runSqlQuery("select * from sa_contract where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);
        if (contractRows.isEmpty()) {
            return getReturnObject().setCode("0205", new String[0]).toString();//无效合同

        }
        String type = contractRows.get(0).getString("type");
        if (!type.equals("居间")) {
            return getReturnObject().setCode("0206", new String[0]).toString();//该类型合同无此功能
        }
        Long ascription_contractid = contractRows.get(0).getLong("ascription_contractid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract_itemsaleclass");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", ascription_contractid);
        Rows contactitemssaleclassRows = querySQL.query();
        if (contactitemssaleclassRows.isEmpty()) {
            return getSucReturnObject().toString();
        }
        RowsMap contactItemsSaleClassRowsMap = contactitemssaleclassRows.toRowsMap("itemclassid");
        querySQL = SQLFactory.createQuerySQL(this, "sa_contract_itemsaleclass");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_contractid", sa_contractid);
        contactitemssaleclassRows = querySQL.query();

        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : contactitemssaleclassRows) {
            String itemclassid = row.getString("itemclassid");
            if (contactItemsSaleClassRowsMap.containsKey(itemclassid)) {
                Row stayItemsSaleClassRow = contactItemsSaleClassRowsMap.get(itemclassid).get(0);
                UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_contract_itemsaleclass");
                updateSQL.setValue("changeuserid", userid);
                updateSQL.setValue("changeby", username);
                updateSQL.setDateValue("changedate");
                updateSQL.setValue("discountrate", stayItemsSaleClassRow.getBigDecimal("discountrate"));
                updateSQL.setValue("remarks", stayItemsSaleClassRow.getString("remarks"));
                updateSQL.setSiteid(siteid);
                updateSQL.setWhere("itemclassid", itemclassid);
                updateSQL.setWhere("sa_contractid", sa_contractid);
                sqlList.add(updateSQL.getSQL());
                contactItemsSaleClassRowsMap.remove(itemclassid);
            }
        }
        long[] sa_contract_itemssaleclass = createTableID("sa_contract_itemsaleclass", contactItemsSaleClassRowsMap.size());
        int i = 0;
        for (String itemclassid : contactItemsSaleClassRowsMap.keySet()) {
            Row stayItemsSaleClassRow = contactItemsSaleClassRowsMap.get(itemclassid).get(0);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_contract_itemsaleclass");
            insertSQL.setValue("sa_contract_itemsaleclassid", sa_contract_itemssaleclass[i]);
            insertSQL.setValue("siteid", siteid);
            insertSQL.setValue("createby", username);
            insertSQL.setDateValue("createdate");
            insertSQL.setValue("changeuserid", userid);
            insertSQL.setValue("changeby", username);
            insertSQL.setDateValue("changedate");
            insertSQL.setValue("createuserid", userid);
            insertSQL.setValue("sa_contractid", sa_contractid);
            insertSQL.setValue("itemclassid", itemclassid);
            insertSQL.setValue("discountrate", stayItemsSaleClassRow.getBigDecimal("discountrate"));
            insertSQL.setValue("remarks", stayItemsSaleClassRow.getString("remarks"));
            sqlList.add(insertSQL.getSQL());
            i++;
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "同步关联合同产品类别", "同步关联合同的产品类别").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }




    @API(title = "批量修改类型", apiversion = R.ID20240110154404.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20230221151903.v1.class})
    public String updateType() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        JSONArray sa_contract_itemsids = content.getJSONArray("sa_contract_itemsids");
        String type = content.getString("type");
        if (content.getBooleanValue("isAll")) {
            dbConnect.runSqlUpdate("update sa_contract_items set type='" + type + "' where siteid='" + siteid + "' and sa_contractid=" + sa_contractid);
        } else {
            sa_contract_itemsids.add("-1");
            String replace = sa_contract_itemsids.toString().replace("[", "(").replace("]", ")");
            dbConnect.runSqlUpdate("update sa_contract_items set type='" + type + "' where siteid='" + siteid + "' and sa_contract_itemsid in " + replace);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "下载导入模板", apiversion = R.ID20230314133304.v1.class)
    public String downloadExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("商品明细导入模板");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        CellStyle style = xssfFWorkbook.createCellStyle();
        DataFormat format = xssfFWorkbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        for (int i = 0; i <= 2; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }
        // 设置工作薄列宽
        ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        XSSFCellStyle titleCellStyle4 = ExportExcel.createBodyCellStyle4(xssfFWorkbook);

        ExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4, xssfFWorkbook);

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

    @API(title = "合同商品价格协议导入", apiversion = R.ID20230314133404.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20230221151903.v1.class})
    public String importItems() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");
        Rows contract = dbConnect.runSqlQuery("select status from sa_contract where siteid='" + siteid + "' and deleted=0 and sa_contractid=" + sa_contractid);
        if (contract.isEmpty()) {
            return getReturnObject().setCode("0205", new String[0]).toString();//无效合同
        }
        if (!contract.get(0).getString("status").equals("新建")) {
            return getReturnObject().setCode("0209", new String[0]).toString();//非新建状态不能导入
        }
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

            // 华为云
            e = getPostExcelFactory(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            ArrayList<String> keys = new ArrayList<>();
            keys.add("itemno");
            keys.add("type");
            keys.add("value");

            HashMap<String, CellType> cellmap = new HashMap<>();
            cellmap.put("itemno", CellType.STRING);
            cellmap.put("value", CellType.STRING);

            Rows rows = e.getSheetRows(0, keys, cellmap, 3);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();

            ArrayList<String> itemnoList = rows.toArrayList("itemno");

            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemno", "marketprice", "orderminqty");
            querySQL.setTableAlias("t1");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("itemno", itemnoList.toArray());
            querySQL.setWhere("isshow=1");
            Rows itemsRows = querySQL.query();

            RowsMap itemRowsMap = itemsRows.toRowsMap("itemno");
            ArrayList<Long> itemidList = dbConnect.runSqlQuery("select itemid from sa_contract_items where siteid='" + siteid + "' and sa_contractid=" + sa_contractid).toArrayList("itemid", new ArrayList<>());

            RowsMap imtitemRowsMap = rows.toRowsMap("itemno");
            for (Row row : rows) {
                String itemno = row.getString("itemno");
                String type = row.getString("type");
                String value = row.getString("value");
                try {
                    new BigDecimal(value);
                } catch (Exception exception) {
                    iserr = true;
                    row.put("msg", "错误信息:数值格式错误");
                    rowserr.add(row);
                    continue;
                }
                if (StringUtils.isEmpty(itemno)
                        || StringUtils.isEmpty(type)
                        || StringUtils.isEmpty(value)) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号/类型/值不能为空");
                    rowserr.add(row);
                    continue;
                }
                if (imtitemRowsMap.get(itemno).size() > 1) {
                    iserr = true;
                    row.put("msg", "错误信息:本次导入存在重复商品");
                    rowserr.add(row);
                    continue;
                }
                if (!itemRowsMap.containsKey(itemno)) {
                    iserr = true;
                    row.put("msg", "错误信息:商品不存在");
                    rowserr.add(row);
                    continue;
                } else {
                    Row itemRow = itemRowsMap.get(itemno).get(0);
                    BigDecimal marketprice = itemRow.getBigDecimal("marketprice");
                    if (marketprice.compareTo(BigDecimal.ZERO) == 0) {
                        iserr = true;
                        row.put("msg", "错误信息:商品牌价为0");
                        rowserr.add(row);
                        continue;
                    }

                    row.put("itemid", itemRow.getLong("itemid"));
                    row.put("marketprice", itemRow.getBigDecimal("marketprice"));
                    row.put("orderminqty", itemRow.getBigDecimal("orderminqty"));
                }
                if (itemidList.contains(row.getLong("itemid"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品已存在价格协议中");
                    rowserr.add(row);
                    continue;
                }
                if (!type.equals("折扣系数") && !type.equals("指定单价")) {
                    iserr = true;
                    row.put("msg", "错误信息:类型不存在");
                    rowserr.add(row);
                    continue;
                }
                rowssuc.add(row);
            }

            if (!rowssuc.isEmpty()) {
                HashMap<Long, ItemPrice> itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, rowssuc.toArrayList("itemid", new ArrayList<Long>()));
                for (Row row : rowssuc) {
                    BigDecimal value = BigDecimal.valueOf(row.getDouble("value"));
                    BigDecimal marketprice = itemPrice.get(row.getLong("itemid")).getMarketprice();
                    if (row.getString("type").equals("折扣系数")) {
                        row.put("discountrate", value);
                        row.put("price", marketprice.multiply(value));
                    } else {
                        row.put("price", value);
                        row.put("discountrate", value.divide(marketprice, 4, RoundingMode.HALF_UP));
                    }
                    sqlList.add(addItemSql(sa_contractid, row.getLong("itemid"), row.getBigDecimal("price"), row.getBigDecimal("discountrate"), row.getBigDecimal("orderminqty"), marketprice, row.getString("type")));
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("商品明细导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "商品编号");
                map.put("type", "类型");
                map.put("value", "值");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("type");
                colNameList.add("value");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("type", String.class);
                keytypemap.put("value", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr, map);

                Rows aa = uploadExcelToObs(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    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);

        CellStyle style = xssfFWorkbook.createCellStyle();
        DataFormat format = xssfFWorkbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        for (int i = 0; i <= 3; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }
        ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 3);
            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((String) keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
                }
                if (i1 == 3) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

//    //查询出项目中的商品添加到合同中去
//    public void addItem(Long sa_contractid, Long sa_projectid) throws YosException {
//
//        Long sa_quotedpriceid = getSaQuotedPriceId(sa_projectid);
//        Rows rows = dbConnect.runSqlQuery("SELECT itemid,price,qty from sa_quotedprice_items  WHERE sa_quotedpriceid =" + sa_quotedpriceid + " AND siteid='" + siteid + "'");
//        ArrayList<String> sqlList = new ArrayList<>();
//        for (Row row : rows) {
//            sqlList.add(addItemSql(sa_contractid, row.getLong("itemid"), row.getBigDecimal("price"), row.getBigDecimal("qty")));
//        }
//        dbConnect.runSqlUpdate(sqlList);
//
//    }

    //查询项目对应的报价单ID
    public Long getSaProjectId(Long sa_contractid) throws YosException {
        String sql = "SELECT sa_projectid from sa_contract WHERE sa_contractid = " + sa_contractid + " and siteid = '" + siteid + "'";
        Long sa_projectid = -1L;
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isNotEmpty()) {
            sa_projectid = rows.get(0).getLong("sa_projectid");
        }
        return sa_projectid;
    }

//    //查询项目对应的报价单ID
//    public Long getSaQuotedPriceId(Long sa_projectid) throws YosException {
//        String sql = "SELECT sa_quotedpriceid from sa_quotedprice WHERE deleted=0 and CURRENT_TIME>=begdate and CURRENT_TIME<=enddate and status = '审核' and sa_projectid = " + sa_projectid + " and siteid = '" + siteid + "' ORDER BY changedate DESC LIMIT 1";
//        Long sa_quotedpriceid = -1L;
//        Rows rows = dbConnect.runSqlQuery(sql);
//        if (rows.isNotEmpty()) {
//            sa_quotedpriceid = rows.get(0).getLong("sa_quotedpriceid");
//        }
//        return sa_quotedpriceid;
//    }

    public String addItemSql(Long sa_contractid, Long itemid, BigDecimal price, BigDecimal discountrate, BigDecimal qty, BigDecimal marketprice, String type) throws YosException {
        if (price.compareTo(BigDecimal.ZERO) == 0) {
            //当价格为0时，取商品的牌价
            price = ItemPrice.getItemPrice(this, itemid).getMarketprice();
        }
        if (StringUtils.isEmpty(type)) {
            type = "折扣系数";
        }
        Rows rows = dbConnect.runSqlQuery("select sa_contract_itemsid,discountrate,remarks from sa_contract_items where sa_contractid=" + sa_contractid + " and itemid=" + itemid + " and siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            SQLFactory sqlFactory = new SQLFactory(this, "合同商品价格协议表");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("sa_contract_itemsid", createTableID("sa_contract_items"));
            sqlFactory.addParameter("sa_contractid", sa_contractid);
            sqlFactory.addParameter("itemid", itemid);
            sqlFactory.addParameter("price", price);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("discountrate", discountrate);
            sqlFactory.addParameter("qty", qty);
            sqlFactory.addParameter("marketprice", marketprice);
            return sqlFactory.getSQL();
        } else {
            Long sa_contract_itemsid = rows.get(0).getLong("sa_contract_itemsid");
            SQLFactory sqlFactory = new SQLFactory(this, "合同-更新产品");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_contract_itemsid", sa_contract_itemsid);
            sqlFactory.addParameter("qty", qty);
            sqlFactory.addParameter("price", price);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("discountrate", discountrate);
            sqlFactory.addParameter("model", "");
            sqlFactory.addParameter("remarks", rows.get(0).getString("remarks"));
            sqlFactory.addParameter("marketprice", marketprice);
            return sqlFactory.getSQL();
        }


    }

    //添加条款
    public void addContractClause(Long sa_contractid, String type, String typemx) throws YosException {
        //项目、框架、居间、工具借用
        Long contracttype = 0L;
        switch (type) {
            case "项目":
                if (typemx.equals("直销")) {
                    contracttype = 1L;
                } else if (typemx.equals("经销")) {
                    contracttype = 2L;
                }
                break;
            case "框架":
                contracttype = 3L;
                break;
            case "居间":
                contracttype = 4L;
                break;
            case "工具借用":
                contracttype = 5L;
                break;
            default:
                contracttype = 0L;
                break;
        }

        Long sa_contract_templateid = new ContractTemplateClause(content).getContractTemplateid(contracttype);
        Rows rows = dbConnect.runSqlQuery("SELECT content,editable from sa_contract_template_clause WHERE sa_contract_templateid = " + sa_contract_templateid + " AND siteid='" + siteid + "'");
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : rows) {
            sqlList.add(addContractClauseSql(sa_contractid, createTableID("sa_contract_clause"), row.getString("content"), row.getString("editable")));
        }

        dbConnect.runSqlUpdate(sqlList);
    }


    public String addContractClauseSql(Long sa_contractid, Long sa_contract_clauseid, String content, String editable) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "合同-条款-新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sa_contract_clauseid", sa_contract_clauseid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter("content", content);
        sqlFactory.addParameter("editable", editable);
        return sqlFactory.getSQL();
    }

    public String updateContractClauseSql(Long sa_contractid, Long sa_contract_clauseid, String content) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "合同-条款-更新");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sa_contract_clauseid", sa_contract_clauseid);
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        sqlFactory.addParameter("content", content);
        return sqlFactory.getSQL();
    }

    @API(title = "查询合同关联的订单", apiversion = R.ID2025022115343002.v1.class)
    public String queryOrder() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");

        Rows contractrows = dbConnect.runSqlQuery("SELECT ascription_contractid from sa_contract WHERE ascription_contractid>0 and sa_contractid='" + sa_contractid + "'");

        ArrayList<Long> sa_contractids = contractrows.toArrayList("ascription_contractid", new ArrayList<>());
        sa_contractids.add(sa_contractid);

        QuerySQL a = SQLFactory.createQuerySQL(this, "sa_orderitems", "sa_orderid", "siteid");
        a.addQueryFields("amount", "ifnull(sum(amount), 0)");
        a.addQueryFields("qty", "ifnull(sum(qty), 0)");
        a.addGroupBy("sa_orderid", "siteid");


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                        "sa_orderid", "sonum", "type", "tradefield", "status", "billdate", "checkdate", "remarks")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t2", "t2.ownerid = t1.sa_orderid and t2.siteid = t1.siteid and t2.ownertable = 'sa_order'",
                "erpbillno");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t1.sys_enterpriseid and t3.siteid=t1.siteid",
                "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_project", "t4", "t4.sa_projectid=t1.sa_projectid and t4.siteid=t1.siteid",
                "projectname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t5", "t5.hrid=t1.saler_hrid and t5.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_brand", "t6", "t6.sa_brandid=t1.sa_brandid and t6.siteid=t1.siteid",
                "brandname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t7", "t7.contactsid=t1.rec_contactsid and t7.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, a, "t8", "t8.sa_orderid=t1.sa_orderid and t8.siteid=t1.siteid",
                "amount", "qty");

        querySQL.addQueryFields("salername", "t5.name");
        querySQL.addQueryFields("contactsname", "t7.name");
        querySQL.addQueryFields("contactsphonenumber", "t7.phonenumber");
        querySQL.addQueryFields("address", "CONCAT(t7.province,t7.city,t7.county,t7.address)");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.deleted=0");
        querySQL.setWhere("t1.sa_contractid ", sa_contractids);
        querySQL.setCondition("t1.sonum", "t3.enterprisename", "t4.projectname", "t5.name");
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();


        return getSucReturnObject().setData(rows).toString();
    }


}