md_YOS/src/custom/restcontroller/webmanage/sale/agents/agents.java

package restcontroller.webmanage.sale.agents;

import beans.accountbalance.Accountbalance;
import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.enterprise.Enterprise;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.*;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
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.enterprisesaleclass.EnterpriseSaleClass;
import restcontroller.webmanage.sale.enterprisetradefield.EnterpriseTradeField;
import restcontroller.webmanage.sale.salearea.salearea;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

@API(title = "经销商管理")
public class agents extends Controller {
    public agents(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "经销商列表查询", apiversion = R.ID20230427101304.v1.class)
    @CACHEING
    public String query_agentList() 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("t2.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status='").append(whereObject.getString("status")).append("' ");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "经销商列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("siteid", siteid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents", "sys_enterpriseid", "isservice",
                "sa_agentsid","agentnum","priceadjustment","type","signdate","createby","createdate","status");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid","enterprisename","abbreviation"
                ,"contact","address","phonenumber","province","city","county","grade","saleclassauth");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "经销商详情查询")
    @CACHEING
    public String query_agentMain() throws YosException {
        long sa_agentsid = content.getLongValue("sa_agentsid");
        SQLFactory sqlFactory = new SQLFactory(this, "经销商详情查询");
        sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        sqlFactory.addParameter("siteid", siteid);
        Rows agentRows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        Rows tradefieldidRows = dbConnect.runSqlQuery("select t1.sys_enterpriseid,t1.sa_agentsid,t1.sa_saleareaid,t1.tradefield,t3.areaname,t3.sa_saleareaids from sys_enterprise_tradefield t1  LEFT JOIN sa_salearea t3 ON t3.sa_saleareaid = t1.sa_saleareaid and t3.siteid = t1.siteid  where t1.siteid ='" + siteid + "'");
        RowsMap rowsMap = tradefieldidRows.toRowsMap("sys_enterpriseid");
        for (Row agentRow : agentRows) {
            if (rowsMap.containsKey(agentRow.getString("sys_enterpriseid"))) {
                Rows rows = rowsMap.get(agentRow.getString("sys_enterpriseid"));
                if (rows.isNotEmpty()) {
                    agentRow.put("tradefield", rows.toJsonArray("tradefield"));
                    agentRow.put("areaname", rows.toJsonArray("areaname"));
                    agentRow.put("sa_saleareaid", rows.get(0).getString("sa_saleareaid"));
                    agentRow.put("sa_saleareaids", rows.get(0).getJSONArray("sa_saleareaids"));
                }
            } else {
                agentRow.put("tradefield", new JSONArray());
                agentRow.put("areaname", new JSONArray());
                agentRow.put("sa_saleareaid", "");
                agentRow.put("sa_saleareaids", new JSONArray());
            }
            agentRow.put("hrs", getEnterpriseHrs(agentRow.getLong("sys_enterpriseid")));

            SQLFactory salersql = new SQLFactory(this, "经销商详情查询_业务员查询");
            salersql.addParameter("siteid", siteid);
            salersql.addParameter_in("sa_saleareaid", SaleArea.getSaleAreaIds(this, agentRow.getLong("sys_enterpriseid")));
            Rows salerrows = dbConnect.runSqlQuery(salersql.getSQL());
            agentRow.put("salers", salerrows);

            QuerySQL querySQL=SQLFactory.createQuerySQL(this,"sa_bankabcxccard","*");
            querySQL.setTableAlias("t1");
            querySQL.setWhere("t1.siteid",siteid);
            querySQL.setWhere("t1.sa_agentsid",sa_agentsid);
            Rows bankabcxccardrows=querySQL.query();
            agentRow.put("bankabcxccard", bankabcxccardrows);
        }
        return getSucReturnObject().setData(agentRows.isNotEmpty() ? agentRows.get(0) : new Row()).toString();
    }


    @API(title = "经销商新增修改", intervaltime = 200)
    @CACHEING_CLEAN(apiClass = {EnterpriseTradeField.class}, cms = {@cm(clazz = agents.class, method = {"query_agentList", "query_agentMain"}), @cm(clazz = salearea.class, method = {"query_agent"}), @cm(clazz = salearea.class, method = {"query_agent"})})
    public String insertormodify_agent() throws YosException {
        long sa_agentsid = content.getLongValue("sa_agentsid");//新增时传0
        long sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        long parentid = content.getLongValue("parentid");
        String agentnum = content.getString("agentnum");

        String type = content.getString("type");
        String signdate = content.getStringValue("signdate");
        long isallsale = content.getLongValue("isallsale");
        String entity = content.getStringValue("entity");


        if (parentid != 0 && dbConnect.runSqlQuery("select * from sa_agents where siteid='" + siteid + "' and sa_agentsid=" + parentid).isEmpty()) {
            return getErrReturnObject().setErrMsg("指定的上级商户不存在").toString();
        }
        Rows rows = dbConnect.runSqlQuery("select sa_agentsid,sys_enterpriseid from sa_agents where agentnum='" + agentnum + "'");
        ArrayList<String> sqllist = new ArrayList<>();
        SQLFactory sqlFactory = new SQLFactory(this, "经销商新增");
        if (sa_agentsid <= 0 || dbConnect.runSqlQuery("select sa_agentsid from sa_agents where sa_agentsid=" + sa_agentsid).isEmpty()) {
            if (dbConnect.runSqlQuery("select sa_agentsid from sa_agents where sys_enterpriseid=" + sys_enterpriseid).isNotEmpty()) {
                return getErrReturnObject().setErrMsg("当前商户已经创建经销商档案，不可重复创建").toString();
            } else if (rows.isNotEmpty()) {
                return getErrReturnObject().setErrMsg("重复经销商编号，无法创建").toString();
            }
            sa_agentsid = createTableID("sa_agents");
            addAccount(sys_enterpriseid);
            Rows customersRows = dbConnect.runSqlQuery("SELECT sa_customersid from sa_customers WHERE sys_enterpriseid =" + sys_enterpriseid + " and siteid = '" + siteid + "'");
            if (customersRows.isNotEmpty()) {
                Long id = customersRows.get(0).getLong("sa_customersid");
                ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sa_customers", id).toArrayList("userid", new ArrayList<>());
                Executor.sendEml(this, "customers_agent_add", id, siteid, userids);
            }
            sqllist.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "新增", "新增经销商").getSQL());
        } else {
            if (rows.isNotEmpty()) {
                if (rows.get(0).getLong("sa_agentsid") != sa_agentsid || rows.get(0).getLong("sys_enterpriseid") != sys_enterpriseid) {
                    return getErrReturnObject().setErrMsg("重复经销商编号，无法修改").toString();
                }
            }
            sqlFactory = new SQLFactory(this, "经销商修改");
            sqllist.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "编辑", "编辑经销商").getSQL());
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("changeuserid", userid);
        sqlFactory.addParameter("agentnum", agentnum);
        sqlFactory.addParameter("type", type);
        sqlFactory.addParameter("entity", entity);
        sqlFactory.addParameter("parentid", parentid == 0 ? "null" : String.valueOf(parentid));
        sqlFactory.addParameter("signdate", signdate.equals("") ? "null" : signdate);
        sqlFactory.addParameter("isservice", content.getLongValue("isservice", 0L));
        sqlFactory.addParameter("submitautocheck", content.getLongValue("submitautocheck", 1L));
        sqlFactory.addParameter("priceadjustment", content.getBigDecimal("priceadjustment"));

        sqllist.add(sqlFactory.getSQL());

        //扩展字段
        String erpagentnum = content.getStringValue("erpagentnum");
        String sql = "SELECT * from sys_dataextend WHERE ownertable='sys_enterprise' and ownerid=" + sys_enterpriseid + " and siteid = '" + siteid + "'";
        Rows extendRows = dbConnect.runSqlQuery(sql);
        Long sys_dataextendid = 0L;
        if (extendRows.isEmpty()) {
            sys_dataextendid = createTableID("sys_dataextend");
            sqlFactory = new SQLFactory(this, "经销商扩展字段新增");
        } else {
            sys_dataextendid = extendRows.get(0).getLong("sys_dataextendid");
            sqlFactory = new SQLFactory(this, "经销商扩展字段更新");

        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sys_dataextendid", sys_dataextendid);
        sqlFactory.addParameter("ownerid", sys_enterpriseid);
        sqlFactory.addParameter("ownertable", "sys_enterprise");
        sqlFactory.addParameter("erpagentnum", erpagentnum);
        sqllist.add(sqlFactory.getSQL());


        sqlFactory = new SQLFactory(this, "商户档案修改");
        sqlFactory.addParameter("limitreturnday", content.getLongValue("limitreturnday"));
        sqlFactory.addParameter("freefreightamount", content.getBigDecimal("freefreightamount"));
        //开票节点（1:订单审核：2:发货）
        sqlFactory.addParameter("invoicingpoint", content.getLongValue("invoicingpoint") == 0 ? 2 : content.getLongValue("invoicingpoint"));
        //是否需要经营授权,默认1
        sqlFactory.addParameter("saleclassauth", content.getBooleanValue("saleclassauth") == false ? 0 : 1);
        sqlFactory.addParameter("grade", content.getLongValue("grade"));
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("changeuserid", userid);
        sqllist.add(sqlFactory.getSQL());


        //企业信息同步到【我的客户】，并将该客户打上经销商标签，
        Rows customersRows = dbConnect.runSqlQuery("SELECT sa_customersid from sa_customers WHERE sys_enterpriseid =" + sys_enterpriseid + " and siteid = '" + siteid + "'");
        Long sa_customersid = 0L;
        if (customersRows.isNotEmpty()) {
            sa_customersid = customersRows.get(0).getLong("sa_customersid");
            sqlFactory = new SQLFactory(this, "客户更新");

        } else {
            sa_customersid = createTableID("sa_customers");
            sqlFactory = new SQLFactory(this, "客户新增");
        }

        Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_customersid", sa_customersid);
        //上级客户ID
        sqlFactory.addParameter("parentid", "null");
        //合作企业档案ID
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        //客户类型
        sqlFactory.addParameter("type", "经销商（客户）");
        //客户池（公海池）ID
        sqlFactory.addParameter("sa_customerpoolid", 0);
        //客户来源
        sqlFactory.addParameter("source", "经销商同步");
        sqlFactory.addParameter("customergrade", "null");
        sqlFactory.addParameter("ispublic", 0);
        sqlFactory.addParameter("name", enterpriseRow.getString("enterprisename"));
        sqlFactory.addParameter("province", enterpriseRow.getString("province"));
        sqlFactory.addParameter("city", enterpriseRow.getString("city"));
        sqlFactory.addParameter("county", enterpriseRow.getString("county"));
        sqlFactory.addParameter("address", enterpriseRow.getString("address"));
        sqlFactory.addParameter("phonenumber", enterpriseRow.getString("phonenumber"));
        sqllist.add(sqlFactory.getSQL());


        JSONArray tradefields = content.getJSONArray("tradefields");
        Long sa_saleareaid = content.getLong("sa_saleareaid");
        sqllist.add("DELETE FROM sys_enterprise_tradefield WHERE sa_agentsid=" + sa_agentsid + "  AND siteid = '" + siteid + "'");
        if (tradefields != null && !tradefields.isEmpty()) {
            for (Object object : tradefields) {
                String tradefield = String.valueOf(object);
                sqlFactory = new SQLFactory(this, "企业领域授权_新增");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("userid", userid);
                sqlFactory.addParameter("tradefield", tradefield);
                sqlFactory.addParameter("tradefieldmx", "");
                sqlFactory.addParameter("sa_saleareaid", sa_saleareaid);
                sqlFactory.addParameter("hrid", content.getLongValue("hrid"));
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                sqlFactory.addParameter("sa_agentsid", sa_agentsid);
                sqlFactory.addParameter("sys_enterprise_tradefieldid", createTableID("sys_enterprise_tradefield"));
                sqllist.add(sqlFactory.getSQL());
            }
        }


        dbConnect.runSqlUpdate(sqllist);
        content.put("sa_agentsid", sa_agentsid);

        //打经销商标签
        DataTag.createTag(this, "sa_customers", sa_customersid, "经销商");

        return query_agentMain();
    }

    @API(title = "经销商状态变更", apiversion = R.ID20230212101703.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = agents.class, method = {"query_agentList", "query_agentMain"}),
            @cm(clazz = EnterpriseSaleClass.class, method = {"queryList"})}, apiversions = {R.ID20221011144903.class, R.ID20221012164402.class})
    public String changeStatus() throws YosException {
        String status = content.getString("status");
        ArrayList<String> sqllist = new ArrayList<>();
        JSONArray array = content.getJSONArray("sa_agentsids");
        for (Object o : array) {
            long sa_agentsid = Long.parseLong(o.toString());
            sqllist.add("update sa_agents set status='" + status + "' where sa_agentsid=" + sa_agentsid);
//            Rows customersRows = dbConnect.runSqlQuery("SELECT sa_customersid  from sa_customers WHERE sys_enterpriseid in (SELECT sys_enterpriseid from sa_agents WHERE sa_agentsid=" + sa_agentsid + " and siteid='" + siteid + "' ) and siteid='" + siteid + "'");
//            if (customersRows.isNotEmpty()) {
//                Long sa_customersid = customersRows.get(0).getLong("sa_customersid");
            if (status.equals("启用")) {
//                    DataTag.createTag(this, "sa_customers", sa_customersid, "经销商");
                sqllist.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "启用", "启用经销商").getSQL());
            } else {
//                    DataTag.deleteTag(this, "sa_customers", sa_customersid, "经销商");
                sqllist.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "禁用", "禁用经销商").getSQL());
            }
//            }
        }


        dbConnect.runSqlUpdate(sqllist);
        return getSucReturnObject().toString();
    }

    @API(title = "商户作废", apiversion = R.ID20221011145003.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = agents.class, method = {"query_agentList", "query_agentMain"}), @cm(clazz = salearea.class, method = {"query_agent"})})
    public String delete_agent() throws YosException {
        JSONArray sa_agentsids = content.getJSONArray("sa_agentsids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_agentsids.size());
        ArrayList<String> list = new ArrayList<>();
        ArrayList<Long> delagentsidList = new ArrayList<>();
        for (Object o : sa_agentsids) {
            long sa_agentsid = Long.parseLong(o.toString());
            Rows userRows = dbConnect.runSqlQuery("SELECT userid,t1.sys_enterpriseid FROM sys_enterprise_hr t1 INNER JOIN sa_agents t2 ON t2.sys_enterpriseid=t1.sys_enterpriseid WHERE t2.siteid='" + siteid + "' and t2.sa_agentsid='" + sa_agentsid + "'");
            ArrayList<String> useridlist = userRows.toArrayList("userid");
            useridlist.add("0");
//            SQLFactory sqlFactory = new SQLFactory("sql:select * from sys_userrequestlog where userid!=0 and userid in $userid$");
//            sqlFactory.addParameter_in("userid", useridlist);
//            Rows logRows = dbConnect.runSqlQuery(sqlFactory.getSQL());
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_userrequestlog", "*");
            querySQL.setWhere("userid!=0");
            querySQL.setWhere("userid",useridlist);
            Rows logRows = querySQL.query();
            if (logRows.isNotEmpty()) {
                batchDeleteErr.addErr(sys_enterpriseid, "该商户下属账号存在访问记录，不可进行作废操作");
                continue;
            }
            list.add("update sa_agents set status='作废' where siteid='" + siteid + "' and sa_agentsid=" + sa_agentsid);
            String replace = Arrays.toString(useridlist.toArray()).replace("[", "(").replace("]", ")");
            list.add("update sys_users set status='INACTIVE' where userid in" + replace);
            list.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "作废", "作废经销商至回收站").getSQL());
            delagentsidList.add(sa_agentsid);
//            {
//                SQLFactory deletesql = new SQLFactory("sql:delete from sa_agents where siteid='" + siteid + "' and sa_agentsid=" + sa_agentsid);
//                list.add(deletesql.getSQL());
//            }
//            {
//                SQLFactory deletesql = new SQLFactory("sql:DELETE from sys_enterprise_hr  WHERE sys_enterpriseid in (SELECT sys_enterpriseid FROM sa_agents WHERE sa_agentsid = " + sa_agentsid + " AND siteid = '" + siteid + "')");
//                list.add(deletesql.getSQL());
//            }
//            if (useridlist.size() > 0) {
//                {
//                    SQLFactory deletesql = new SQLFactory("sql:delete from sys_users where userid in $userid$");
//                    deletesql.addParameter_in("userid", useridlist);
//                    list.add(deletesql.getSQL());
//                }
//                {
//                    SQLFactory deletesql = new SQLFactory("sql:delete from sys_usersite where userid in $userid$");
//                    deletesql.addParameter_in("userid", useridlist);
//                    list.add(deletesql.getSQL());
//                }
//                {
//                    SQLFactory deletesql = new SQLFactory("sql:delete from sys_userrole where userid in $userid$");
//                    deletesql.addParameter_in("userid", useridlist);
//                    list.add(deletesql.getSQL());
//                }
//            }
//            //删除经销商标签
//            Row agentRow = getAgentByAgentid(sa_agentsid);
//            Long sys_enterpriseid = agentRow.isEmpty() ? 0 : agentRow.getLong("sys_enterpriseid");
//            if (sys_enterpriseid > 0) {
//                list.add("DELETE from sys_datatag WHERE tag='经销商' and ownertable='sa_customers' and  siteid = '" + siteid + "' and ownerid in (SELECT sa_customersid FROM sa_customers WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "')");
//            }
        }
        dbConnect.runSqlUpdate(list);
        DataExtend.createDeleteReasonSql(this, "sa_agents", delagentsidList, content.getStringValue("deletereason"));
        return batchDeleteErr.getReturnObject().toString();
    }

    @API(title = "经销商联系人信息及地址信息新增修改", apiversion = R.ID20221009155703.v1.class)
    @CACHEING_CLEAN(apiversions = R.ID20221009155803.v1.class)
    public String insertormodify_agentEnterpriseContacts() throws YosException {
        long contactsid = content.getLongValue("contactsid");//新增时传0
        long sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        String name = content.getString("name");
        String sex = content.getString("sex");
        String depname = content.getString("depname");
        String position = content.getString("position");
        long isleader = content.getLongValue("isleader");
        String birthday = content.getStringValue("birthday");
        String phonenumber = content.getString("phonenumber");
        String email = content.getString("email");
        String province = content.getString("province");
        String city = content.getString("city");
        String county = content.getString("county");
        String remarks = content.getString("remarks");
        String address = content.getString("address");
        long workaddress = content.getLongValue("workaddress");
        boolean isdefault = content.getBooleanValue("isdefault");
        String type = content.getStringValue("type");

        ArrayList<String> sqllist = new ArrayList<>();
        if (contactsid <= 0 || dbConnect.runSqlQuery("select contactsid from sys_enterprise_contacts where contactsid=" + contactsid).isEmpty()) {
            contactsid = createTableID("sys_enterprise_contacts");
            SQLFactory sqlFactory = new SQLFactory(this, "联系人信息新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("contactsid", contactsid);
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            sqlFactory.addParameter("name", name);
            sqlFactory.addParameter("sex", sex);
            sqlFactory.addParameter("depname", depname);
            sqlFactory.addParameter("position", position);
            sqlFactory.addParameter("isleader", isleader);
            sqlFactory.addParameter("birthday", birthday.equals("") ? "null" : birthday);
            sqlFactory.addParameter("phonenumber", phonenumber);
            sqlFactory.addParameter("email", email);
            sqlFactory.addParameter("province", province);
            sqlFactory.addParameter("city", city);
            sqlFactory.addParameter("county", county);
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("address", address);
            sqlFactory.addParameter("workaddress", workaddress);
            sqlFactory.addParameter("type", type);

            sqlFactory.addParameter("userid", userid);
            if (workaddress == 1) {
                sqlFactory.addParameter("isdefault", isdefault);
                if (isdefault) {
                    sqllist.add("update sys_enterprise_contacts set isdefault=0 where contactsid !=" + contactsid + " and sys_enterpriseid=" + sys_enterpriseid);
                }
            } else {
                sqlFactory.addParameter("isdefault", 0);
            }
            sqllist.add(sqlFactory.getSQL());
            content.put("sys_enterpriseid", sys_enterpriseid);

        } else {
            SQLFactory sqlFactory = new SQLFactory(this, "联系人信息修改");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("contactsid", contactsid);
            sqlFactory.addParameter("name", name);
            sqlFactory.addParameter("sex", sex);
            sqlFactory.addParameter("depname", depname);
            sqlFactory.addParameter("position", position);
            sqlFactory.addParameter("isleader", isleader);
            sqlFactory.addParameter("birthday", birthday.equals("") ? "null" : birthday);
            sqlFactory.addParameter("phonenumber", phonenumber);
            sqlFactory.addParameter("email", email);
            sqlFactory.addParameter("province", province);
            sqlFactory.addParameter("city", city);
            sqlFactory.addParameter("county", county);
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("address", address);
            sqlFactory.addParameter("workaddress", workaddress);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("userid", userid);
            if (workaddress == 1) {
                sqlFactory.addParameter("isdefault", isdefault);
                if (isdefault) {
                    sqllist.add("update sys_enterprise_contacts set isdefault=0 where contactsid !=" + contactsid + " and sys_enterpriseid=" + sys_enterpriseid);
                }
            } else {
                sqlFactory.addParameter("isdefault", 0);
            }
            sqllist.add(sqlFactory.getSQL());
        }
        dbConnect.runSqlUpdate(sqllist);
        return getSucReturnObject().toString();
    }


    @API(title = "联系人信息列表查询", apiversion = R.ID20221009155803.v1.class)
    @CACHEING
    public String query_agentEnterpriseContactsList() throws YosException {
        /*
          过滤条件设置
         */
        long sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        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.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("workaddress") && !"".equals(whereObject.getString("workaddress"))) {
                where.append(" and t1.workaddress='" + whereObject.getString("workaddress") + "' ");
            }
            if (whereObject.containsKey("isdefault") && !"".equals(whereObject.getString("isdefault"))) {
                where.append(" and t1.isdefault='" + whereObject.getString("isdefault") + "' ");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "联系人信息列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        sqlFactory.addParameter("siteid", siteid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_enterprise_contacts", "contactsid", "sys_enterpriseid",
                "name","sex","depname","position","isleader","birthday","phonenumber","email","province","city","county","remarks","address","workaddress","isdefault","type");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("ifnull(t1.deleted,0)=0");
        querySQL.setWhere("t1.sys_enterpriseid",sys_enterpriseid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询订单收货人信息", apiversion = R.ID20230220002602.v1.class)
    @CACHEING
    public String queryList() throws YosException {
        /*
          过滤条件设置
         */
        long sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        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.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "收货人列表查询");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "联系人信息删除", apiversion = R.ID20221009155903.v1.class)
    @CACHEING_CLEAN(apiversions = R.ID20221009155803.v1.class)
    public String delete_agentEnterpriseContacts() throws YosException {
        long contactsid = content.getLongValue("contactsid");
        Rows rows = dbConnect.runSqlQuery("select contactsid,ifnull(isdefault,0) isdefault from sys_enterprise_contacts where contactsid=" + contactsid);
        if (!rows.isEmpty()) {
            if (rows.get(0).getBoolean("isdefault")) {
                return getErrReturnObject().setErrMsg("该地址信息为默认地址，无法删除").toString();
            }
        }
        dbConnect.runSqlUpdate("update sys_enterprise_contacts set deleted=1 where contactsid=" + contactsid);
        return getSucReturnObject().toString();
    }

    @API(title = "经销商账户余额查询", apiversion = R.ID20221009160003.v1.class)
    @CACHEING
    public String query_agentaccountbalance() throws YosException {
        long sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        long isorder = content.getLongValue("isorder");
//        SQLFactory sqlFactory = new SQLFactory(this, "经销商账户余额查询");
//        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        sqlFactory.addParameter("siteid", siteid);
//        Rows agentaccountbalanceRows = dbConnect.runSqlQuery(sqlFactory.getSQL());
//        if (!agentaccountbalanceRows.isEmpty()) {
//            for (Row row : agentaccountbalanceRows) {
//                row.put("balance", row.getBigDecimal("balance"));
//                row.put("creditquota", row.getBigDecimal("creditquota"));
//            }
//        }

        BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, sys_enterpriseid);

        String sql = "SELECT sa_accountclassid from sa_accountclass WHERE siteid = '" + siteid + "' and isrebate = 1";
        if (isorder == 1) {
            sql = sql + " and isorder=1";
        }

        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isNotEmpty()) {
            Long sa_accountclassid = rows.get(0).getLong("sa_accountclassid");
            sql = "SELECT * from sa_accountbalance WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "' and sa_accountclassid=" + sa_accountclassid;
            if (isorder == 1) {
                sql = sql + " and isorder=1";
            }
            rows = dbConnect.runSqlQuery(sql);
            if (rows.isNotEmpty()) {
                Long sa_accountbalanceid = rows.get(0).getLong("sa_accountbalanceid");
                dbConnect.runSqlUpdate("UPDATE sa_accountbalance SET balance=" + rebateBalance + " WHERE sa_accountbalanceid= '" + sa_accountbalanceid + "' and siteid='" + siteid + "'");
            }
        }
        sql = "SELECT accountno,accountname,sa_accountclassid,isrebate from sa_accountclass WHERE siteid = '" + siteid + "' and isused = 1";

        if (isorder == 1) {
            sql = sql + " and isorder=1";
        }
        rows = dbConnect.runSqlQuery(sql);
        Rows balanceRows = dbConnect.runSqlQuery("SELECT sa_accountbalanceid,balance,ifnull(discountamount,0) discountamount,creditquota,changeuserid,changeby,changedate,sa_accountclassid from sa_accountbalance WHERE siteid = '" + siteid + "' and sys_enterpriseid = " + sys_enterpriseid);

        for (Row row : rows) {
            row.put("sa_accountbalanceid", "");
            row.put("balance", "0");
            row.put("discountamount", "0");
            row.put("creditquota", "0");
            row.put("changeuserid", "");
            row.put("changeby", "");
            row.put("changedate", "");
            for (Row balanceRow : balanceRows) {
                if (row.getLong("sa_accountclassid") == balanceRow.getLong("sa_accountclassid")) {
                    row.putAll(balanceRow);
                }
            }


        }


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


    /**
     * 添加默认的营销账户余额
     *
     * @return
     * @throws YosException
     */
    public void addAccount(Long sys_enterpriseid) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = dbConnect.runSqlQuery("SELECT sa_accountclassid from sa_accountclass WHERE isused = 1 and siteid ='" + siteid + "'");
        for (Row row : rows) {
            SQLFactory sqlFactory = new SQLFactory(this, "新增营销账户");
            sqlFactory.addParameter("sa_accountbalanceid", createTableID("sa_accountbalance"));
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            sqlFactory.addParameter("sa_accountclassid", row.getLong("sa_accountclassid"));
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlList.add(sqlFactory.getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);
    }

    @API(title = "经销商档案导入模板下载", apiversion = R.ID20230311162904.v1.class)
    public String uploadAgents() 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 <= 16; 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.ID20230311163004.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = agents.class, method = {"query_agentList", "query_agentMain"}), @cm(clazz = salearea.class, method = {"query_agent"}), @cm(clazz = salearea.class, method = {"query_agent"})})
    public String importAgents() {
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

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

            ArrayList<String> keys = new ArrayList<>();
            keys.add("agentnum");
            keys.add("enterprisename");
            keys.add("abbreviation");
            keys.add("province");
            keys.add("city");
            keys.add("county");
            keys.add("signdate");
            keys.add("contact");
            keys.add("phonenumber");
            keys.add("grade");
            keys.add("isservice");
            keys.add("type");
            keys.add("erpagentnum");
            keys.add("freefreightamount");
            keys.add("limitreturnday");
            keys.add("invoicingpoint");
            keys.add("saleclassauth");

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

            ArrayList<String> agentsList = dbConnect.runSqlQuery("select agentnum from sa_agents where siteid='" + siteid + "'").toArrayList("agentnum");
            ArrayList<String> gradeList = dbConnect.runSqlQuery("select value from sys_optiontype t1 inner join sys_optiontypemx t2 on t1.optiontypeid=t2.optiontypeid where typename='agentgrade' and isused=1").toArrayList("value");
            RowsMap agentnumRowsMap = rows.toRowsMap("agentnum");

            for (Row row : rows) {

                if (StringUtils.isEmpty(row.getString("agentnum")) || StringUtils.isEmpty(row.getString("enterprisename")) || StringUtils.isEmpty(row.getString("grade"))) {
                    iserr = true;
                    row.put("msg", "错误信息:经销商编号/企业名称/企业等级不能为空");
                    rowserr.add(row);
                    continue;
                }
                if (agentnumRowsMap.get(row.getString("agentnum")).size() > 1) {
                    iserr = true;
                    row.put("msg", "错误信息:本次导入经销商档案存在重复的经销商编号");
                    rowserr.add(row);
                    continue;
                }
                if (agentsList.contains(row.getString("agentnum"))) {
                    iserr = true;
                    row.put("msg", "错误信息:经销商编号已存在");
                    rowserr.add(row);
                    continue;
                }
                if (!gradeList.contains(row.getString("grade"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商户等级不存在");
                    rowserr.add(row);
                }
                rowssuc.add(row);
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("经销商导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("agentnum", "经销商编码");
                map.put("enterprisename", "企业名称");
                map.put("abbreviation", "经销商简称");
                map.put("province", "省");
                map.put("city", "市");
                map.put("county", "县");
                map.put("signdate", "签约日期");
                map.put("contact", "联系人");
                map.put("phonenumber", "联系电话");
                map.put("grade", "企业等级");
                map.put("isservice", "是否服务商");
                map.put("type", "经销商类型");
                map.put("erpagentnum", "erp编号");
                map.put("freefreightamount", "免运费金额");
                map.put("limitreturnday", "订单可退货天数");
                map.put("invoicingpoint", "是否订单审核可开票");
                map.put("saleclassauth", "是否授权所有营销类别");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("agentnum");
                colNameList.add("enterprisename");
                colNameList.add("abbreviation");
                colNameList.add("province");
                colNameList.add("city");
                colNameList.add("county");
                colNameList.add("signdate");
                colNameList.add("contact");
                colNameList.add("phonenumber");
                colNameList.add("grade");
                colNameList.add("isservice");
                colNameList.add("type");
                colNameList.add("erpagentnum");
                colNameList.add("freefreightamount");
                colNameList.add("limitreturnday");
                colNameList.add("invoicingpoint");
                colNameList.add("saleclassauth");
                colNameList.add("msg");
                keytypemap.put("agentnum", String.class);
                keytypemap.put("enterprisename", String.class);
                keytypemap.put("abbreviation", String.class);
                keytypemap.put("province", String.class);
                keytypemap.put("city", String.class);
                keytypemap.put("county", String.class);
                keytypemap.put("signdate", String.class);
                keytypemap.put("contact", String.class);
                keytypemap.put("phonenumber", String.class);
                keytypemap.put("grade", String.class);
                keytypemap.put("isservice", String.class);
                keytypemap.put("type", String.class);
                keytypemap.put("erpagentnum", String.class);
                keytypemap.put("freefreightamount", String.class);
                keytypemap.put("limitreturnday", String.class);
                keytypemap.put("invoicingpoint", String.class);
                keytypemap.put("saleclassauth", 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();
            }

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    Long sys_enterpriseid = createTableID("sys_enterprise");
                    SQLFactory sqlFactory = new SQLFactory(this, "企业新增");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                    sqlFactory.addParameter("enterprisename", row.getString("enterprisename"));
                    sqlFactory.addParameter("limitreturnday", row.getString("limitreturnday"));
                    sqlFactory.addParameter("freefreightamount", row.getString("freefreightamount"));
                    sqlFactory.addParameter("invoicingpoint", row.getString("invoicingpoint").equals("否") ? 2 : 1);
                    sqlFactory.addParameter("abbreviation", row.getString("abbreviation"));
                    sqlFactory.addParameter("grade", row.getString("grade"));
                    sqlFactory.addParameter("saleclassauth", row.getString("saleclassauth").equals("否") ? 0 : 1);
                    sqlFactory.addParameter("province", row.getString("province"));
                    sqlFactory.addParameter("city", row.getString("city"));
                    sqlFactory.addParameter("county", row.getString("county"));
                    sqlFactory.addParameter("contact", row.getString("contact"));
                    sqlFactory.addParameter("phonenumber", row.getString("phonenumber"));
                    sqlList.add(sqlFactory.getSQL());
                    Long sa_agentsid = createTableID("sa_agents");
                    sqlFactory = new SQLFactory(this, "经销商导入新增");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("sa_agentsid", sa_agentsid);
                    sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("changeuserid", userid);
                    sqlFactory.addParameter("agentnum", row.getString("agentnum"));
                    sqlFactory.addParameter("type", row.getString("type"));
                    sqlFactory.addParameter("parentid", 0);
                    sqlFactory.addParameter("signdate", row.getString("signdate"));
                    sqlFactory.addParameter("isservice", row.getString("isservice").equals("是") ? 1 : 0);
                    sqlList.add(sqlFactory.getSQL());
                    Long sys_dataextendid = createTableID("sys_dataextend");
                    sqlFactory = new SQLFactory(this, "经销商扩展字段新增");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("ownerid", sys_enterpriseid);
                    sqlFactory.addParameter("ownertable", "sys_enterprise");
                    sqlFactory.addParameter("erpagentnum", row.getString("erpagentnum"));
                    sqlFactory.addParameter("sys_dataextendid", sys_dataextendid);
                    sqlList.add(sqlFactory.getSQL());
                    sqlList.add(DataContrlLog.createLog(this, "sys_enterprise", sys_enterpriseid, "新增", "经销商档案导入").getSQL());
                    sqlList.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "新增", "经销商档案导入").getSQL());
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
            }
        } 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 <= 17; 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 == 17) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
}