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

package restcontroller.webmanage.sale.salearea;

import beans.data.BatchDeleteErr;
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.annotation.cm;
import common.data.*;
import restcontroller.R;
import restcontroller.sale.promotion.promotion;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

@API(title = "营销区域管理")
public class salearea extends Controller {
    public salearea(JSONObject content) throws YosException {
        super(content);
    }


    @API(title = "营销区域列表查询")
    @CACHEING
    public String query_area() throws YosException {
        HashMap<Long, JSONArray> map = new HashMap<>();
        /*
          查询所有分类
         */
        Rows allrows = dbConnect.runSqlQuery("select sa_saleareaid,areaname,isused,remarks,sequence,ifnull(parentid,0) as parentid from sa_salearea where siteid='" + siteid + "' order by sequence,sa_saleareaid");
        /*
          获取所有一级分类
         */
        Rows toprows = allrows.toRowsMap("parentid").get("0");
        /*
          递归查询
         */
        for (Row row : toprows) {
            row.put("subarea", getSubArea(row, allrows));
        }
        return getSucReturnObject().setData(toprows).toString();
    }

    @API(title = "营销区域列表查询(区域经理)")
    @CACHEING
    public String query_area_qyjl() throws YosException {
        HashMap<Long, JSONArray> map = new HashMap<>();

        /*
          查询所有分类
         */
        SQLFactory areasqlFactory = new SQLFactory(new promotion(content), "区域经理查询下属营销区域");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter("hrid", hrid);
        Rows arearows = dbConnect.runSqlQuery(areasqlFactory);
        String sql ="select sa_saleareaid,areaname,isused,remarks,sequence,ifnull(parentid,0) as parentid from sa_salearea where siteid='" + siteid + "' and sa_saleareaid in "+arearows.toArrayList("sa_saleareaid")+" order by sequence,sa_saleareaid";
        sql = sql.replace("[", "(").replace("]", ")");
        Rows allrows = dbConnect.runSqlQuery(sql);
        /*
          递归查询
         */
        for (Row row : allrows) {
            row.put("subarea", getSubArea(row, allrows));
        }
        return getSucReturnObject().setData(allrows).toString();
    }


    @API(title = "营销区域列表查询(范围)")
    public String query_area_auth() throws YosException {

        Long obiectId = content.getLong("obiectId");
        String obiectName = content.getString("obiectName");

        HashMap<Long, JSONArray> map = new HashMap<>();
        /*
          查询所有分类
         */
        Rows allrows = dbConnect.runSqlQuery("select sa_saleareaid,areaname,isused,remarks,sequence,ifnull(parentid,0) as parentid from sa_salearea where siteid='" + siteid + "' order by sequence,sa_saleareaid");
        /*
          获取所有一级分类
         */
        Rows toprows = allrows.toRowsMap("parentid").get("0");
        /*
          递归查询
         */
        for (Row row : toprows) {
            row.put("subarea", getSubArea(row, allrows));
        }

        String tablename = "";
        String tableid = "";
        /*
         * 商学院
         */
        if (obiectName.equals("sat_courseware")) {
            tablename = "sat_courseware_auth";
            tableid = "sat_coursewareid";
        }
        //通告
        if (obiectName.equals("sat_notice")) {
            tablename = "sat_notice_auth";
            tableid = "sat_noticeid";
        }
        //营销物料
        if (obiectName.equals("sat_salematerial")) {
            tablename = "sat_salematerial_auth";
            tableid = "attachmentid";
        }
        //素材
        if (obiectName.equals("sat_sharematerial")) {
            tablename = "sat_sharematerial_auth";
            tableid = "sat_sharematerialid";
        }
        //提报
        if (obiectName.equals("sat_submiteditmodel")) {
            tablename = "sat_submiteditmodel_auth";
            tableid = "sat_submiteditmodelid";
        }
        //公海池
        if (obiectName.equals("sa_customerpool")) {
            tablename = "sa_customerpool_auth";
            tableid = "sa_customerpoolid";
        }
        Rows authrows = new Rows();
        if (!tablename.equals("") && !tableid.equals("")) {
            authrows = dbConnect.runSqlQuery("SELECT t1.sa_saleareaid,t1.sa_agentsid,t2.sa_saleareaid sa_saleareaid_temp  FROM " + tablename + " t1 LEFT JOIN sys_enterprise_tradefield t2 ON t1.sa_agentsid=t2.sa_agentsid WHERE " + tableid + "=" + obiectId + " AND t1.siteid='" + siteid + "'");
        }


        setRows(toprows, authrows);

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

    /**
     * 设置状态   status:0：未选，1：部分选，2：全选
     *
     * @param rows
     * @param authrows
     */
    public void setRows(Rows rows, Rows authrows) {
        for (Row subRow : rows) {
            setRows((Rows) subRow.get("subarea"), authrows);
            Long sa_saleareaid = subRow.getLong("sa_saleareaid");
            int status = 0;
            for (Row authRow : authrows) {
                Long sa_saleareaid_temp = authRow.getLong("sa_saleareaid");
                if (sa_saleareaid_temp != 0) {
                    if (sa_saleareaid_temp.equals(sa_saleareaid)) {
                        status = 2;
                    }
                }
            }
            if (status != 2) {
                for (Row authRow : authrows) {
                    Long sa_saleareaid_temp = authRow.getLong("sa_saleareaid_temp");
                    Long sa_agentsid = authRow.getLong("sa_agentsid");
                    if (sa_saleareaid.equals(sa_saleareaid_temp)) {
                        if (sa_agentsid > 0) {
                            status = 1;
                        }
                    }
                }
            }
            subRow.put("status", status);

        }
    }

    /**
     * 递归查询下级部门
     *
     * @param root
     * @param allDepartments
     * @return
     */
    private Rows getSubArea(Row root, Rows allDepartments) {
        Rows childrenRows = allDepartments.toRowsMap("parentid").get(root.getString("sa_saleareaid"));
        for (Row row : childrenRows) {
            row.put("subarea", getSubArea(row, allDepartments));
        }
        return childrenRows;
    }

    @API(title = "营销区域新增修改")
    @CACHEING_CLEAN(cms = {@cm(clazz = salearea.class, method = {"query_area"})})
    public String insertormodify_area() throws YosException {
        long sa_saleareaid = content.getLongValue("sa_saleareaid");//新增时传0
        String areaname = content.getString("areaname");
        boolean isused = content.getBoolean("isused");
        long parentid = content.getLongValue("parentid");
        String remarks = content.getString("remarks");
        int sequence = content.getInteger("sequence");

        if (parentid != 0 && dbConnect.runSqlQuery("select * from sa_salearea where siteid='" + siteid + "' and sa_saleareaid=" + parentid).isEmpty()) {
            return getErrReturnObject().setErrMsg("指定的上级区域不存在").toString();
        }
        boolean isrefresh;
        SQLFactory sqlFactory;
        if (sa_saleareaid <= 0 || dbConnect.runSqlQuery("select sa_saleareaid from sa_salearea where sa_saleareaid=" + sa_saleareaid).isEmpty()) {
            sa_saleareaid = createTableID("sa_salearea");
            sqlFactory = new SQLFactory(this, "营销区域新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_saleareaid", sa_saleareaid);
            sqlFactory.addParameter("areaname", areaname);
            sqlFactory.addParameter("isused", isused);
            sqlFactory.addParameter("parentid", parentid == 0 ? "null" : String.valueOf(parentid));
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("sequence", sequence);
            sqlFactory.addParameter("sa_saleareaids", getSaleareaids(parentid, sa_saleareaid));
            isrefresh = true;
        } else {
            sqlFactory = new SQLFactory(this, "营销区域修改");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_saleareaid", sa_saleareaid);
            sqlFactory.addParameter("areaname", areaname);
            sqlFactory.addParameter("isused", isused);
            sqlFactory.addParameter("parentid", parentid == 0 ? "null" : String.valueOf(parentid));
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("sequence", sequence);
            isrefresh = dbConnect.runSqlQuery("select *from sa_salearea where siteid='" + siteid + "' and sa_saleareaid='" + sa_saleareaid + "' and areaname='" + areaname + "'").isEmpty();
        }
        dbConnect.runSqlUpdate(sqlFactory);
        if (isrefresh) {
            SaleArea.RefreshSaleAreaMsg(this);
        }
        return query_area();
    }

    public JSONArray getSaleareaids(Long parentid, Long sa_saleareaid) throws YosException {
        JSONArray jsonArray = new JSONArray();
        if (parentid != 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT sa_saleareaids from sa_salearea WHERE sa_saleareaid=" + parentid + " and siteid ='" + siteid + "'");
            if (rows.isNotEmpty()) {
                jsonArray = rows.get(0).getJSONArray("sa_saleareaids");
            }
        }
        jsonArray.add(sa_saleareaid);
        return jsonArray;
    }


    @API(title = "营销区域删除")
    @CACHEING_CLEAN(cms = {@cm(clazz = salearea.class, method = {"query_area"})})
    public String delete_area() throws YosException {
        long sa_saleareaid = content.getLongValue("sa_saleareaid");
        if (dbConnect.runSqlQuery("select * from sa_salearea where siteid='" + siteid + "' and isused=1 and sa_saleareaid=" + sa_saleareaid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("启用状态不可删除").toString();
        }
        if (dbConnect.runSqlQuery("select * from sa_salearea where siteid='" + siteid + "' and parentid=" + sa_saleareaid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在下级区域，不可删除").toString();
        }
        if (dbConnect.runSqlQuery("select * from sa_salearea_hr where siteid='" + siteid + "' and sa_saleareaid=" + sa_saleareaid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("区域存在业务员信息，不可删除").toString();
        }
        dbConnect.runSqlUpdate("delete from sa_salearea where siteid='" + siteid + "' and sa_saleareaid=" + sa_saleareaid);
        return query_area();
    }

    @API(title = "营销区域查询业务员", apiversion = R.ID20221011144603.v1.class)
    @CACHEING
    public String query_saler() throws YosException {
        long sa_saleareaid = content.getLongValue("sa_saleareaid");
        /*
          过滤条件设置
         */
        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(")");
            }
        }

        ArrayList<Long> ids = SaleArea.getSubSaleAreaIds(this, sa_saleareaid);
        ids.add(sa_saleareaid);

        SQLFactory sqlFactory = new SQLFactory(this, "业务员列表查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_saleareaid", ids);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

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

    @API(title = "营销区域查询业务员详情查询")
    public String query_salermain() throws YosException {
        long sa_salearea_hrid = content.getLongValue("sa_salearea_hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "业务员详情查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_salearea_hrid", sa_salearea_hrid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Row()).toString();
    }


    @API(title = "营销区域添加业务员", apiversion = R.ID20221011144703.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = salearea.class, method = {"query_saler"})})
    public String add_saler() throws YosException {
        long sa_salearea_hrid = content.getLongValue("sa_salearea_hrid");
        long sa_saleareaid = content.getLongValue("sa_saleareaid");
        long hrid = content.getLongValue("hrid");
        double discountrate = 1;
        SQLFactory sqlFactory = new SQLFactory(this, "营销区域添加业务员");
        if (sa_salearea_hrid <= 0) {
//            if (dbConnect.runSqlQuery("select * from sa_salearea_hr where siteid='" + siteid + "' and sa_saleareaid=" + sa_saleareaid).isNotEmpty()) {
//                return getErrReturnObject().setErrMsg("区域存在业务员信息，不可新增").toString();
//            }
            sa_salearea_hrid = createTableID("sa_salearea_hr");
        } else {
            sqlFactory = new SQLFactory(this, "营销区域更新业务员");
        }


        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_salearea_hrid", sa_salearea_hrid);
        sqlFactory.addParameter("sa_saleareaid", sa_saleareaid);
        sqlFactory.addParameter("hrid", hrid);
        sqlFactory.addParameter("discountrate", content.getBigDecimal("discountrate").compareTo(BigDecimal.ZERO) == 0 ? 1 : content.getBigDecimal("discountrate"));
        sqlFactory.addParameter("iseditprice", content.getBooleanValue("iseditprice"));
        dbConnect.runSqlUpdate(sqlFactory.getSQL());
        return getSucReturnObject().toString();
    }

    @API(title = "营销区域删除业务员", apiversion = R.ID20221011144803.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = salearea.class, method = {"query_saler"})})
    public String delete_saler() throws YosException {
        JSONArray sa_saleareaids = content.getJSONArray("sa_saleareaids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_saleareaids.size());
        for (Object o : sa_saleareaids) {
            long sa_saleareaid = ((JSONObject) o).getLong("sa_saleareaid");
            long hrid = ((JSONObject) o).getLong("hrid");
            dbConnect.runSqlUpdate("delete from sa_salearea_hr where siteid='" + siteid + "' and sa_saleareaid='" + sa_saleareaid + "' and hrid='" + hrid + "'");
        }
        return batchDeleteErr.getReturnObject().toString();
    }

    @API(title = "营销区域查询经销商", apiversion = R.ID20221011144903.v1.class)
    @CACHEING
    public String query_agent() throws YosException, IOException {
        /*
          过滤条件设置
         */
        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("or t4.erpagentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and ");
                where.append("t1.status = '").append(whereObject.getString("status").equals("ACTIVE") ? "启用" : "禁用").append("' ");
            }
        }
        boolean containssub = content.getBoolean("containssub");
        JSONArray sa_saleareaidsArray = content.getJSONArray("sa_saleareaids");
        ArrayList<Long> sa_saleareaidsList = new ArrayList<>();
        for (Object o : sa_saleareaidsArray) {
            Long sa_saleareaid = Long.valueOf(o.toString());
            sa_saleareaidsList.add(sa_saleareaid);
            if (containssub) {
                sa_saleareaidsList.addAll(SaleArea.getSubSaleAreaIds(this, sa_saleareaid));
            }
        }
        boolean isExport = content.getBooleanValue("isExport");
        Boolean isAll = content.getBooleanValue("isAll");
        if (sa_saleareaidsList.isEmpty()) {
            isAll = true;
        }
        if (!isAll) {
            String sqlStr = "and t1.sys_enterpriseid in (SELECT sys_enterpriseid from sys_enterprise_tradefield WHERE sa_saleareaid in " + sa_saleareaidsList + ")";
            sqlStr = sqlStr.replace("[", "(").replace("]", ")");
            where.append(sqlStr);
        }


//        SQLFactory sqlFactory = new SQLFactory(this, "营销区域经销商查询", pageSize, pageNumber, pageSorting);
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "营销区域经销商查询");
//        }
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = getAgentList(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("sys_enterpriseid", new ArrayList<>());
        ids.add(0l);

        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商主账号信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", ids);
        RowsMap rowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid");
        String sql ="select t1.sys_enterpriseid,t2.areaname from sys_enterprise_tradefield t1 inner join sa_salearea t2 on t1.sa_saleareaid=t2.sa_saleareaid and t1.siteid=t2.siteid  where t1.siteid='"+siteid+"' and t1.sys_enterpriseid in"+ids;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows enterpriseSaleareaRows =dbConnect.runSqlQuery(sql);
        RowsMap enterpriseSaleareaRowsMap =enterpriseSaleareaRows.toRowsMap("sys_enterpriseid");

        for (Row row : rows) {
            Rows tempRows = rowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows());
            if (tempRows.isNotEmpty()) {
                row.put("userid", tempRows.get(0).getLong("userid"));
                row.put("accountno", tempRows.get(0).getString("accountno"));
            } else {
                row.put("userid", 0);
                row.put("accountno", "");
            }
            if(enterpriseSaleareaRowsMap.containsKey(row.getString("sys_enterpriseid"))){
                row.put("areaname", enterpriseSaleareaRowsMap.get(row.getString("sys_enterpriseid")).get(0).getString("areaname"));
            }else{
                row.put("areaname", "");
            }
        }


//        if (isExport) {
//            //去除不需要导出项
//            String[] removeFieldList = {"sa_agentsid", "sys_enterpriseid", "createdate", "limitreturnday", "freefreightamount",
//                    "invoicingpoint", "userid"};
//            for (String key : removeFieldList) {
//                rows.getFieldList().remove(key);
//            }
//
//            Rows uploadRows = uploadExcelToObs("经销商档案", "经销商档案", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }

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


    public QuerySQL getAgentList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename","abbreviation", "phonenumber", "contact", "province", "city", "county", "address", "limitreturnday", "freefreightamount", "invoicingpoint","saleclassauth");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t4", "t1.sys_enterpriseid = t4.ownerid and ownertable = 'sys_enterprise' and t1.siteid = t4.siteid",
                "erpagentnum");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        return querySQL;
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        //
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("agentnum", "经销商编号");
        titleMap.put("erpagentnum", "ERP经销商编号");
        titleMap.put("isservice", "是否服务经销商");
        titleMap.put("accountno", "登录账号");
        titleMap.put("enterprisename", "经销商名称");
        titleMap.put("province", "省");
        titleMap.put("city", "市");
        titleMap.put("county", "县");
        titleMap.put("address", "地址");
        titleMap.put("contact", "经销商联系人");
        titleMap.put("phonenumber", "联系人手机号码");
        titleMap.put("status", "状态");


        return titleMap;
    }


    @API(title = "查询所有业务员", apiversion = R.ID20221122153902.v1.class)
    public String getSalers() 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.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "查询所有业务员");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        ArrayList<Long> hrids = rows.toArrayList("hrid", new ArrayList<>());

        sqlFactory = new SQLFactory(this, "查询业务员营销区域");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("hrid", hrids);
        RowsMap saleareaRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("hrid");

        for (Row row : rows) {
            row.put("salearea", saleareaRowsMap.getOrDefault(row.getString("hrid"), new Rows()));
        }


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

}