package restcontroller.webmanage.sale.contract;

import beans.CommonHepler;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.recycle.Recycle;
import beans.salearea.SaleArea;
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.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import restcontroller.R;
import restcontroller.sale.customer.ExportExcel;

import javax.mail.search.DateTerm;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

public class Contract2 extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Contract2(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "列表", apiversion = R.ID20221121201502.v1.class)
    public String list() throws YosException {

        //  1：我负责的；2：我参与的；3：我下属负责的；4：我下属参与的
        int type = content.getIntValue("type");
        Long deleted = content.getLongValue("deleted");

        String tablename = "sa_contract";
        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("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t5.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                String status = whereObject.getString("status");
                if (status.equals("未生效")) {
                    where.append(" and current_date < t1.begdate");
                }
                if (status.equals("生效")) {
                    where.append(" and current_date >= t1.begdate and current_date <= t1.enddate ");
                }
                if (status.equals("结束")) {
                    where.append(" and current_date > t1.enddate ");
                }
            }
            //类型
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }

            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.createdate >='").append(whereObject.getString("begindate")).append(" 00:00:00'");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.createdate <='").append(whereObject.getString("enddate")).append(" 23:59:59'");
            }
        }
        where.append(" and(").append("t1.deleted = ").append(deleted).append(")");

        String where2 = " 1=1 ";
        if (deleted == 0) {
            if (type == 0) {
                where2 = DataTeam.getDataWhereStr(this, tablename, "t1", 1)
                        + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 2);
            } else {
                where2 = DataTeam.getDataWhereStr(this, tablename, "t1", type);
            }
        } else {
            where2 = Recycle.getWhereSql(this, tablename, "t1.sa_contractid");
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract",
                "sa_contractid", "billno", "type", "begdate", "enddate", "createdate", "createby", "remarks", "signdate");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid and t3.siteid=t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t4.sa_agentsid=t1.sa_agentsid and t4.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid=t4.sys_enterpriseid and t5.siteid=t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t6", "t6.hrid=t1.saler_hrid and t6.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t7", "t1.siteid = t7.siteid and t1.sa_contractid = t7.ownerid and t7.ownertable = 'sa_contract'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere(where2);
        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salername", "t6.name");
        querySQL.addQueryFields("deletechangedate", "t7.changedate");
        querySQL.addQueryFields("deletechangeby", "t7.changeby");
        querySQL.addQueryFields("status", "if(current_date < t1.begdate,'未生效',if(current_date > t1.enddate,'结束','生效'))");
        querySQL.setPage(pageSize, pageNumber);
        if (pageSorting.equals("''")) {
            pageSorting = "t1.createdate desc";
        }
        querySQL.setOrderBy(pageSorting);

        querySQL.withDeleteData(true);

        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());
        RowsMap leaderRows = DataTeam.getLeader(this, "sa_contract", rows.toArrayList("sa_contractid", new ArrayList<>())).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_contract", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_contract", ids, true);

        for (Row row : rows) {
            Long id = row.getLong("sa_contractid");
            row.put("leader", leaderRows.get(row.getString("sa_contractid")));
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
        }

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

    }

    @API(title = "新增或更新", apiversion = R.ID20221121185302.v1.class, intervaltime = 2000)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.v1.class, R.ID20221121201502.v1.class, R.ID20221122165302.v1.class, R.ID20221223102102.v1.class, R.ID20220930103501.v1.class, R.ID20230214163402.v1.class, R.ID20240829103004.v1.class})
    public String insertOrUpdate() throws YosException {

        Long sa_contractid = content.getLongValue("sa_contractid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");
        String signdate = content.getStringValueForDate("signdate", "yyyy-MM-dd", "null");
        String begdate = content.getStringValueForDate("begdate", "yyyy-MM-dd", "null");
        String enddate = content.getStringValueForDate("enddate", "yyyy-MM-dd", "null");
        String earnestmoney = content.getStringValue("earnestmoney");
        String payment = content.getStringValue("payment");
        String reward = content.getStringValue("reward");
        Long opqty = content.getLongValue("opqty");
        Long saler_hrid = content.getLongValue("saler_hrid");
        String remarks = content.getStringValue("remarks");


        Long saler_userid = 0L;
        if (sa_customersid > 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT t1.userid,t2.hrid FROM sys_datateam t1 " +
                    "INNER JOIN sys_hr t2 ON t2.userid=t1.userid AND t2.siteid=t1.siteid " +
                    "WHERE ownertable='sa_customers' AND ownerid=" + sa_customersid + " AND t1.isleader=1 AND t1.siteid='" + siteid + "'");
            if (rows.isNotEmpty()) {
                saler_hrid = rows.getRow(0).getLong("hrid");
            }
        }
        Rows hrRows = dbConnect.runSqlQuery("SELECT * from sys_hr WHERE hrid=" + saler_hrid + " and siteid='" + siteid + "'");
        if (hrRows.isEmpty()) {
            return getReturnObject().setCode("0198").toString();//业务员不存在，无法新增合同
        }
        saler_userid = hrRows.getRow(0).getLong("userid");


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

        boolean isAdd = false;
        if (sa_contractid <= 0) {
            isAdd = true;
            sa_contractid = createTableID("sa_contract");
            String billno = createBillCode("contractbill");
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_contract");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(sa_contractid);
            insertSQL.setValue("sa_customersid", sa_customersid);
            insertSQL.setValue("sa_agentsid", sa_agentsid);
            insertSQL.setValue("type", sa_customersid > 0 ? "医院协议" : "经销商协议");
            insertSQL.setValue("signdate", signdate);
            insertSQL.setValue("begdate", begdate);
            insertSQL.setValue("enddate", enddate);
            insertSQL.setValue("billno", billno);
            insertSQL.setValue("earnestmoney", earnestmoney);
            insertSQL.setValue("payment", payment);
            insertSQL.setValue("reward", reward);
            insertSQL.setValue("opqty", opqty);
            insertSQL.setValue("saler_hrid", saler_hrid);
            insertSQL.setValue("remarks", remarks);
            sqlList.add(insertSQL.getSQL());
        } else {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_contract");
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(sa_contractid);
            updateSQL.setValue("sa_customersid", sa_customersid);
            updateSQL.setValue("sa_agentsid", sa_agentsid);
            updateSQL.setValue("signdate", signdate);
            updateSQL.setValue("begdate", begdate);
            updateSQL.setValue("enddate", enddate);
            updateSQL.setValue("earnestmoney", earnestmoney);
            updateSQL.setValue("payment", payment);
            updateSQL.setValue("reward", reward);
            updateSQL.setValue("opqty", opqty);
            updateSQL.setValue("saler_hrid", saler_hrid);
            updateSQL.setValue("remarks", remarks);
            sqlList.add(updateSQL.getSQL());
        }


        if (isAdd) {
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "新建", "新建合同成功").getSQL());
            sqlList.addAll(DataTeam.createTeamSQL(this, "sa_contract", sa_contractid, saler_userid));
            DataExtend.markDataFrom(this, "sa_contract", sa_contractid);
        } else {
            sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "编辑", "编辑合同成功").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        if (sa_customersid > 0) {
            if (sa_agentsid > 0) {
                DataTag.createSystemTag(this, "sa_contract", sa_contractid, "经销");
            } else {
                DataTag.createSystemTag(this, "sa_contract", sa_contractid, "直销");
            }
        }


        content.put("sa_contractid", sa_contractid);

        return detail();
    }

    @API(title = "详情", apiversion = R.ID20221121195102.v1.class)
    @CACHEING
    public String detail() throws YosException {
        Long sa_contractid = content.getLong("sa_contractid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid and t3.siteid=t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t4.sa_agentsid=t1.sa_agentsid and t4.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid=t4.sys_enterpriseid and t5.siteid=t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t6", "t6.hrid=t1.saler_hrid and t6.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t7", "t1.siteid = t7.siteid and t1.sa_contractid = t7.ownerid and t7.ownertable = 'sa_contract'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_contractid", sa_contractid);
        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salername", "t6.name");
        querySQL.addQueryFields("deletechangedate", "t7.changedate");
        querySQL.addQueryFields("deletechangeby", "t7.changeby");
        querySQL.addQueryFields("status", "if(current_date < t1.begdate,'未生效',if(current_date > t1.enddate,'结束','生效'))");
        querySQL.withDeleteData(true);

        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());

        RowsMap leaderRows = DataTeam.getLeader(this, "sa_contract", rows.toArrayList("sa_contractid", new ArrayList<>())).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_contract", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_contract", ids, true);

        for (Row row : rows) {
            Long id = row.getLong("sa_contractid");
            row.put("leader", leaderRows.get(row.getString("sa_contractid")));
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
        }

        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();


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

    @API(title = "选择医院", apiversion = R.ID2025102416054102.v1.class)
    public String chooseHospitalList() throws YosException {
        String tablename = "sa_customers";
        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.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t8.depname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        where.append(" and ( t1.datastatus in (0,2) )");


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid", "createby", "createdate",
                "sys_enterpriseid", "status", "type", "tradingstatus", "datastatus", "billno", "province", "city", "county", "address", "stagename", "grade");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid AND t2.siteid = t1.siteid", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t3", "t3.sa_saleareaid = t1.sa_saleareaid AND t3.siteid = t1.siteid", "areaname");

        QuerySQL t5 = SQLFactory.createQuerySQL(this, "sys_datafollowup", "ownerid");
        t5.setWhere("ownertable", "sa_customers");
        t5.setSiteid(siteid);
        t5.addGroupBy("ownerid");
        t5.addQueryFields("followdate", "max(createdate)");
        t5.setRoleDataLimit(false);
        querySQL.addJoinTable(JOINTYPE.left, t5, "t5", "t5.ownerid = t1.sa_customersid", "followdate");
        querySQL.addJoinTable(JOINTYPE.left, "sys_datateam", "t6", "t6.ownerid = t1.sa_customersid and t6.siteid = t1.siteid and t6.ownertable = 'sa_customers' and t6.isleader = 1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t7", "t7.userid = t6.userid and t7.siteid = t6.siteid ");
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t8", "t8.departmentid = t7.departmentid and t8.siteid = t7.siteid ");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t10", "t1.siteid = t10.siteid and t1.sa_customersid = t10.ownerid and t10.ownertable = 'sa_customers'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setPage(pageSize, pageNumber);

        if (pageSorting.equals("''")) {
            pageSorting = "t1.createdate desc";
        }
        querySQL.setOrderBy(pageSorting);

        querySQL.setRoleDataLimit(false);
        querySQL.withDeleteData(true);
        Rows rows = querySQL.query();
        RowsMap leaderRows = DataTeam.getLeaderWithoutHeadpic(this, "sa_customers", rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        ArrayList<Long> ids = rows.toArrayList("sa_customersid", new ArrayList<>());
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, tablename, ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, tablename, ids, true);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        RowsMap AgentRowsMap = CommonHepler.getAgentRowsMap(this, ids);
        RowsMap KeyDoctorsRowsMap = CommonHepler.getKeyDoctorsRowsMap(this, ids);
        for (Row row : rows) {
            Long id = row.getLong("sa_customersid");
            row.put("leader", leaderRows.get(String.valueOf(id)));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("name", leaderRows.get(String.valueOf(id)).get(0).getString("name"));
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>();
            if (row.getString("agentsstatus").equals("禁用") || row.getString("agentsstatus").equals("作废")) {
                tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
                sys_tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
            }
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            row.putIfAbsent("followdate", "");
            //预估手术总量
            row.putIfAbsent("totalop", 0);
            //关键人
            Rows KeyDoctors = KeyDoctorsRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("keyperson", StringUtils.join(KeyDoctors.toArray("doctorname")));
            //签约经销商
            Rows signagent = AgentRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("signagent", StringUtils.join(signagent.toArray("enterprisename")));
            String stagename = row.getString("stagename");
            for (Row stageRow : stageRows) {
                if (stagename.equals(stageRow.getString("stagename"))) {
                    stageRow.put("active", 1);
                }
                stageRow.putIfAbsent("active", 0);
            }
            row.put("stages", stageRows);
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "选择经销商", apiversion = R.ID2025102416055302.v1.class)
    public String chooseAgentsList() 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.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpagentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        Long sa_customersid = content.getLongValue("sa_customersid");
        if (sa_customersid > 0) {
            where.append(" and t1.sa_agentsid in (SELECT sa_agentsid from sa_agents_hospital WHERE sa_customersid=" + sa_customersid + ") ");
        }

        where.append(" and t1.status ='启用' ");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents",
                        "sa_agentsid", "sys_enterpriseid", "gmname", "gmphonenumber", "agentnum", "cooperatetype", "remarks", "status", "createdate").
                setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename", "province", "city", "county", "address");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t3", "t1.sys_enterpriseid = t3.ownerid and ownertable = 'sys_enterprise' and t1.siteid = t3.siteid",
                "erpagentnum");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy("t1.createdate desc");
        Rows rows = querySQL.query();

        RowsMap areaRowsMap = CommonHepler.getAreaRowsMap(this, rows.toArrayList("sa_agentsid", new ArrayList<>()));
        RowsMap salerRowsMap = CommonHepler.getSalerNameRowsMap(this, rows.toArrayList("sa_agentsid", new ArrayList<>()));
        for (Row row : rows) {
            row.put("p_c_c", row.getString("province") + "-" + row.getString("city") + "-" + row.getString("county"));
            Rows areaRows = areaRowsMap.getOrDefault(row.getString("sa_agentsid"), new Rows());
            row.put("areanames", StringUtils.join(areaRows.toArrayList("areaname"), ","));
            Rows salerRows = salerRowsMap.getOrDefault(row.getString("sa_agentsid"), new Rows());
            row.put("salernames", StringUtils.join(salerRows.toArrayList("name"), ","));
            row.putIfAbsent("scale", "");
            row.putIfAbsent("mainproducts", "");
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "校验合同", apiversion = R.ID2025102416540802.v1.class)
    public String checkContract() throws YosException {
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");

        String begdate = content.getStringValueForDate("begdate", "yyyy-MM-dd", "null");
        String enddate = content.getStringValueForDate("enddate", "yyyy-MM-dd", "null");


        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_contract " +
                "WHERE sa_agentsid=" + sa_agentsid + " and sa_customersid=" + sa_customersid + " " +
                "and (('" + begdate + "'>=begdate and '" + begdate + "'<=enddate) or('" + enddate + "'>=begdate and '" + enddate + "'<=enddate ))");
        if (rows.isNotEmpty()) {
            return getErrReturnObject().setErrMsg("该合同已存在").toString();
        } else {
            return getSucReturnObject().toString();
        }

    }

    @API(title = "复制", apiversion = R.ID20240102135104.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221219141702.v1.class, R.ID20221121195102.class, R.ID20221121201502.class, R.ID20221122165302.class, R.ID20221223102102.class, R.ID20220930103501.class, R.ID20221121195102.class})
    public String copy() throws YosException {
        Long copyContractid = content.getLong("sa_contractid");
        Rows contractRows = dbConnect.runSqlQuery("select * from sa_contract where siteid='" + siteid + "' and sa_contractid=" + copyContractid);
        if (contractRows.isEmpty()) {
            return getReturnObject().setCode("0205", new String[0]).toString();//无效合同

        }
        Row contractRow = contractRows.get(0);
        ArrayList<String> sqlList = new ArrayList<>();
        long sa_contractid = createTableID("sa_contract");
        String billno = createBillCode("contractbill");
        sqlList.addAll(DataTeam.createTeamSQL(this, "sa_contract", sa_contractid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_contract", sa_contractid, userid));

        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_contract");
        insertSQL.setSiteid(siteid);
        insertSQL.setUniqueid(sa_contractid);
        insertSQL.setValue("sa_customersid", contractRow.getLong("sa_customersid"));
        insertSQL.setValue("sa_agentsid", contractRow.getLong("sa_agentsid"));
        insertSQL.setValue("type", contractRow.getString("type"));
        insertSQL.setValue("signdate", contractRow.getString("signdate", "null"));
        insertSQL.setValue("begdate", contractRow.getString("begdate", "null"));
        insertSQL.setValue("enddate", contractRow.getString("enddate", "null"));
        insertSQL.setValue("billno", billno);
        insertSQL.setValue("earnestmoney", contractRow.getString("earnestmoney"));
        insertSQL.setValue("payment", contractRow.getString("payment"));
        insertSQL.setValue("reward", contractRow.getString("reward"));
        insertSQL.setValue("opqty", contractRow.getLong("opqty"));
        insertSQL.setValue("saler_hrid", contractRow.getLong("saler_hrid"));
        insertSQL.setValue("remarks", contractRow.getString("remarks"));
        sqlList.add(insertSQL.getSQL());

        sqlList.add(DataContrlLog.createLog(this, "sa_contract", sa_contractid, "复制", "复制合同" + billno).getSQL());

        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_contractid", sa_contractid);
        return new Contract2(content).detail();
    }


}