CUCU2/src/custom/restcontroller/webmanage/sale/salestarget/personnel.java

package restcontroller.webmanage.sale.salestarget;

import beans.datacontrllog.DataContrlLog;
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 org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.saletool.orderclue.ExportExcel;

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

@API(title = "管理端-销售目标-人员目标")
public class personnel extends Controller {
    /**
     * 构造函数
     * @param content
     */
    public personnel(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新建年度或添加人员", apiversion = R.ID20220831165302.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = personnel.class, method = {"queryList", "queryDetailList"}), @cm(clazz = personnelstatistics.class, method = {"queryList"})})
    public String insert() throws YosException {
        Long year = content.getLong("year");
        String assessmentindicators=content.getString("assessmentindicators");
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        String statisticaldimension=content.getStringValue("statisticaldimension");
        String sa_accountclassids=content.getStringValue("sa_accountclassids");

        JSONArray salesArray = content.getJSONArray("sales");

        ArrayList<String> sqlList = new ArrayList<>();
        if (sa_salestargetbillid <= 0) {
            //查询创建年度是否存在
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_salestargetbill WHERE year = " + year + " AND targettype ='人员目标' AND siteid = '" + siteid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg(year + "年度目标已存在，无法创建").toString();
            }
            sa_salestargetbillid = createTableID("sa_salestargetbill");
            SQLFactory sqlFactory = new SQLFactory(this, "创建新年度");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("sys_enterpriseid", "null");
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("year", year);
            sqlFactory.addParameter("assessmentindicators", assessmentindicators);
            sqlFactory.addParameter("statisticaldimension", statisticaldimension);
            sqlFactory.addParameter("sa_accountclassids", sa_accountclassids);

            sqlFactory.addParameter("targettype", "人员目标");
            sqlFactory.addParameter("tradefield", "");
            sqlList.add(sqlFactory.getSQL());
            sqlList.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "人员目标新增", "人员目标新增"+year ).getSQL());


            //获取所有启用业务员
            SQLFactory saleareahrsqlFactory = new SQLFactory(this, "通用-人员范围查询");
            saleareahrsqlFactory.addParameter("siteid", siteid);
            saleareahrsqlFactory.addParameter_SQL("where", " 1=1 ");
            saleareahrsqlFactory.addParameter_SQL("isIn", " not in ");
            saleareahrsqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            String sql = saleareahrsqlFactory.getSQL();
            Rows saleareahrrows = dbConnect.runSqlQuery(sql);
            salesArray=saleareahrrows.toJsonArray();
        }else{
            SQLFactory sqlFactory = new SQLFactory(this, "更新新年度");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("assessmentindicators", assessmentindicators);
            sqlFactory.addParameter("statisticaldimension", statisticaldimension);
            sqlFactory.addParameter("sa_accountclassids", sa_accountclassids);
            sqlList.add(sqlFactory.getSQL());
        }


        String[] types = {"年", "季", "月"};

        long[] ids = createTableID("sa_salestarget", salesArray.size() * 17);
        int index = 1;

        for (Object obj : salesArray) {
            JSONObject saleObj = (JSONObject) obj;
             System.out.println(saleObj.toString());
            SQLFactory saleFactory = new SQLFactory(this, "人员范围新增");
            saleFactory.addParameter("siteid", siteid);
            saleFactory.addParameter("sa_salestargethrid", createTableID("sa_salestargethr"));
            saleFactory.addParameter("hrid", saleObj.getLongValue("hrid"));
            saleFactory.addParameter("position", saleObj.getStringValue("position"));

            saleFactory.addParameter("sa_saleareaid", saleObj.getStringValue("sa_saleareaid"));
            saleFactory.addParameter("areaname", saleObj.getStringValue("areaname"));
            saleFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            saleFactory.addParameter("depname", saleObj.getStringValue("depname"));
            saleFactory.addParameter("departmentid", saleObj.getLongValue("departmentid"));
            saleFactory.addParameter("name", saleObj.getStringValue("name"));
            sqlList.add(saleFactory.getSQL());

            for (String type : types) {
                int point;
                if (type.equals("年")) {
                    point = 1;
                } else if (type.equals("季")) {
                    point = 4;
                } else if (type.equals("月")) {
                    point = 12;
                } else {
                    point = 0;
                }

                for (int i = 0; i < point; i++) {
                    SQLFactory targetFactory = new SQLFactory(this, "人员-目标明细新增");
                    targetFactory.addParameter("siteid", siteid);
                    targetFactory.addParameter("sa_salestargetid", ids[index - 1]);
                    targetFactory.addParameter("type", type);
                    targetFactory.addParameter("point", i + 1);
                    targetFactory.addParameter("targettype", "人员目标");
                    targetFactory.addParameter("sa_saleareaid", saleObj.getString("sa_saleareaid"));
                    targetFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
                    targetFactory.addParameter("year", year);
                    targetFactory.addParameter("sa_projectid", "null");
                    targetFactory.addParameter("createby", username);
                    targetFactory.addParameter("target_l", 0);
                    targetFactory.addParameter("target_h", 0);
                    sqlList.add(targetFactory.getSQL());
                    index++;
                }
            }

            sqlList.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "区域编辑", "添加区域" + saleObj.getStringValue("areaname")).getSQL());

        }


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


    @API(title = "删除人员", apiversion = R.ID20220901111202.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = personnel.class, method = {"queryList", "queryDetailList"}), @cm(clazz = personnelstatistics.class, method = {"queryList"})})
    public String deletePeople() throws YosException {
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        JSONArray salesArray = content.getJSONArray("sa_saleareaid");
        if (salesArray.size() <= 0) {
            return getErrReturnObject().setErrMsg("请选择人员").toString();
        }

        String sql = "SELECT 1 FROM sa_salestarget WHERE sa_saleareaid in " + salesArray + " and sa_salestargetbillid = " + sa_salestargetbillid + " and targettype = '人员目标' and (target_l  >0 or target_h >0)";
        sql = sql.replace("[", "(").replace("]", ")");
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在已填写的数据，无法删除").toString();
        }

        ArrayList<String> sqlList = new ArrayList<>();
        //删除人员范围
        SQLFactory sqlFactory = new SQLFactory(this, "人员-删除人员范围");
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_saleareaid", salesArray.toArray());
        sqlList.add(sqlFactory.getSQL());
        //删除人员目标
        SQLFactory sqlFactory2 = new SQLFactory(this, "人员-删除目标明细");
        sqlFactory2.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory2.addParameter("siteid", siteid);
        sqlFactory2.addParameter_in("sa_saleareaid", salesArray.toArray());
        sqlList.add(sqlFactory2.getSQL());

        //操作记录
        for (Object obj : salesArray) {
            Long sa_saleareaid = Long.valueOf(obj.toString());
            String name = "";
            Rows rows = dbConnect.runSqlQuery("SELECT areaname from sa_salearea WHERE sa_saleareaid = " + sa_saleareaid);
            if (rows.isNotEmpty()) {
                name = rows.get(0).getString("areaname");
            }
            sqlList.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "人员编辑", "移除人员" + name).getSQL());
        }


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

    @API(title = "年度目标列表", apiversion = R.ID20220901132502.v1.class)
    @CACHEING
    public String queryList() throws YosException {
         /*
          过滤条件设置
         */
         String targettype =content.getString("targettype");
        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.year like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status like'%").append(whereObject.getString("status")).append("%' ");
                where.append(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "年度目标列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("targettype", targettype);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Rows accountclassrows = dbConnect.runSqlQuery("select sa_accountclassid,accountname from sa_accountclass where siteid='"+siteid+"'");
        RowsMap accountclassrowsMap = accountclassrows.toRowsMap("sa_accountclassid");

        for (Row row:rows) {
            JSONArray jsonArray= new JSONArray();
            if(isJSONArray(row.getString("statisticaldimension"))){
                row.put("statisticaldimension",JSONArray.parseArray(row.getString("statisticaldimension")));
            }
            if(isJSONObject(row.getString("statisticaldimension"))){
                row.put("statisticaldimension",JSONObject.parseObject(row.getString("statisticaldimension")));
            }
            if(isJSONArray(row.getString("sa_accountclassids"))){
                JSONArray sa_accountclassids =JSONArray.parseArray(row.getString("sa_accountclassids"));
                for (Object object:sa_accountclassids) {
                    Long sa_accountclassid = Long.valueOf(object.toString());
                    if(accountclassrowsMap.containsKey(String.valueOf(sa_accountclassid))){
                        if(accountclassrowsMap.get(String.valueOf(sa_accountclassid)).isNotEmpty()){
                            jsonArray.add(accountclassrowsMap.get(String.valueOf(sa_accountclassid)).get(0));
                        }
                    }
                }
                row.put("sa_accountclassids",jsonArray);
            }
        }


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

    @API(title = "目标详情", apiversion = R.ID20220901140402.v1.class)
    public String queryDetail() throws YosException {
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        SQLFactory sqlFactory = new SQLFactory(this, "人员-目标详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        Rows accountclassrows = dbConnect.runSqlQuery("select sa_accountclassid,accountname from sa_accountclass where siteid='"+siteid+"'");
        RowsMap accountclassrowsMap = accountclassrows.toRowsMap("sa_accountclassid");

        for (Row row:rows) {
            JSONArray jsonArray= new JSONArray();
            if(isJSONArray(row.getString("statisticaldimension"))){
                row.put("statisticaldimension",JSONArray.parseArray(row.getString("statisticaldimension")));
            }
            if(isJSONObject(row.getString("statisticaldimension"))){
                row.put("statisticaldimension",JSONObject.parseObject(row.getString("statisticaldimension")));
            }
            if(isJSONArray(row.getString("sa_accountclassids"))){
                JSONArray sa_accountclassids =JSONArray.parseArray(row.getString("sa_accountclassids"));
                for (Object object:sa_accountclassids) {
                    Long sa_accountclassid = Long.valueOf(object.toString());
                    if(accountclassrowsMap.containsKey(String.valueOf(sa_accountclassid))){
                        if(accountclassrowsMap.get(String.valueOf(sa_accountclassid)).isNotEmpty()){
                            jsonArray.add(accountclassrowsMap.get(String.valueOf(sa_accountclassid)).get(0));
                        }
                    }
                }
                row.put("sa_accountclassids",jsonArray);
            }
        }
        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Rows()).toString();
    }

    @API(title = "目标编辑", apiversion = R.ID20220902162902.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = personnel.class, method = {"queryList", "queryDetailList"}), @cm(clazz = personnelstatistics.class, method = {"queryList"})})
    public String edit() throws YosException {
        Long sa_salestargetbillid = content.getLongValue("sa_salestargetbillid");
        Long sa_saleareaid = content.getLong("sa_saleareaid");
        if (sa_salestargetbillid == 0) {
            Long year = content.getLongValue("year");
            Rows rows = dbConnect.runSqlQuery("SELECT DISTINCT sa_salestargetbillid from sa_salestarget WHERE year = " + year + " and sa_saleareaid=" + sa_saleareaid + " and targettype ='人员目标' and siteid='" + siteid + "'");
            if (rows.isNotEmpty()) {
                sa_salestargetbillid = rows.get(0).getLong("sa_salestargetbillid");
            }
        }
        if (sa_salestargetbillid == 0) {
            return getErrReturnObject().setErrMsg("年度目标不存在或该人员不在年度目标中，请先创建年度目标或添加相关人员到年度目标中").toString();
        }

        ArrayList<String> sqlList = new ArrayList<>();
        String key_l = "y1l";
        String key_h = "y1h";
        if (content.containsKey(key_l) && content.containsKey(key_h)) {
            sqlList.add(getSql(content.getString(key_l), content.getString(key_h), sa_salestargetbillid, sa_saleareaid, "年", 1));
        }
        for (int i = 1; i < 5; i++) {
            key_l = "s" + i + "l";
            key_h = "s" + i + "h";
            if (content.containsKey(key_l) && content.containsKey(key_h)) {
                sqlList.add(getSql(content.getString(key_l), content.getString(key_h), sa_salestargetbillid, sa_saleareaid, "季", i));
            }
        }

        for (int i = 1; i < 13; i++) {
            key_l = "m" + i + "l";
            key_h = "m" + i + "h";
            if (content.containsKey(key_l) && content.containsKey(key_h)) {
                sqlList.add(getSql(content.getString(key_l), content.getString(key_h), sa_salestargetbillid, sa_saleareaid, "月", i));
            }
        }

        sqlList.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "编辑人员目标", "编辑人员目标").getSQL());

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


    public String getSql(String target_l, String target_h, Long id, Long sa_saleareaid, String type, int point) {

        return " UPDATE sa_salestarget SET target_l=" + target_l + ",target_h = " + target_h + "   WHERE sa_salestargetbillid =" + id + " and sa_saleareaid =" + sa_saleareaid + " and type ='" + type + "' AND point = " + point;
    }

    @API(title = "目标详情列表", apiversion = R.ID20220901141802.v1.class)
    @CACHEING
    public String queryDetailList() throws YosException {
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        /*
          过滤条件设置
         */
        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("t.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.depfullname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.depname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.position like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "人员-目标详情列表");
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        Rows saleareas = dbConnect.runSqlQuery("select * from sa_salearea where parentid=0 and siteid='"+siteid+"'");
        Long sa_saleareaid=0l;
        if(!saleareas.isEmpty() && saleareas.size()==1){
            sa_saleareaid=saleareas.get(0).getLong("sa_saleareaid");
            for (Row row :rows) {
                if(row.getLong("parentid")==0 ){
                    row.put("y1l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("y1l"));
                    row.put("y1h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("y1h"));
                    row.put("s1l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s1l"));
                    row.put("s1h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s1h"));
                    row.put("s2l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s2l"));
                    row.put("s2h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s2h"));
                    row.put("s3l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s3l"));
                    row.put("s3h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s3h"));
                    row.put("s4l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s4l"));
                    row.put("s4h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("s4h"));
                    row.put("m1l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m1l"));
                    row.put("m1h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m1h"));
                    row.put("m2l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m2l"));
                    row.put("m2h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m2h"));
                    row.put("m3l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m3l"));
                    row.put("m3h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m3h"));
                    row.put("m4l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m4l"));
                    row.put("m4h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m4h"));
                    row.put("m5l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m5l"));
                    row.put("m5h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m5h"));
                    row.put("m6l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m6l"));
                    row.put("m6h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m6h"));
                    row.put("m7l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m7l"));
                    row.put("m7h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m7h"));
                    row.put("m8l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m8l"));
                    row.put("m8h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m8h"));
                    row.put("m9l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m9l"));
                    row.put("m9h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m9h"));
                    row.put("m10l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m10l"));
                    row.put("m10h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m10h"));
                    row.put("m11l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m11l"));
                    row.put("m11h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m11h"));
                    row.put("m12l",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m12l"));
                    row.put("m12h",rows.toRowsMap("parentid").get(sa_saleareaid.toString()).sum("m12h"));
                }
            }
        }



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

    @API(title = "人员目标导入模板", apiversion = R.ID20220913093102.v1.class)
    public String downloadExcel() throws YosException {

        ExcelFactory excelFactory = new ExcelFactory("人员目标导入模板");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        personnelexcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        personnelexcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, 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.ID20220913092702.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220901141802.class})
    public String personnelTargetImport() throws YosException {

        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        Long year = content.getLong("year");
        ExcelFactory e;

        try {
            if (content.getLong("attachmentid") > 0) {
                e = getPostExcelFactory(content.getLong("attachmentid"));
                // 本地
                //e = getPostExcelFactory();
            } else {
                e = getPostExcelFactory();
                // 本地
                //e = getPostExcelFactory();
            }


            ArrayList<String> keys = new ArrayList<>();
            ArrayList<String> sqllist = new ArrayList<>();

            keys.add("name");
            String keyl = "y1l";
            keys.add(keyl);
            for (int i = 1; i < 5; i++) {
                keyl = "s" + i + "l";
                keys.add(keyl);
            }

            for (int i = 1; i < 13; i++) {
                keyl = "m" + i + "l";
                keys.add(keyl);
            }
            Rows rows = e.getSheetRows(0, keys, 2);
            ArrayList<String> areanameList = new ArrayList<>();
            for (Row row : rows) {
                areanameList.add(row.getString("name"));
            }
            //查询可导入的用户信息
            SQLFactory sqlFactory = new SQLFactory(this, "人员-查询可导入区域信息");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter_in("areaname", areanameList);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            String sql = sqlFactory.getSQL();
            RowsMap areaRowsMap = dbConnect.runSqlQuery(sql).toRowsMap("areaname");

            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            // 手机号码的格式：第一位只能为1，第二位可以是3，4，5，7，8，第三位到第十一位可以为0-9中任意一个数字
            String regex = "^((13[0-9])|(14[5,7,9])|(15[0-3,5-9])|(166)|(17[3,5,6,7,8])|(18[0-9])|(19[1,8,9]))\\d{8}$";
            for (Row row : rows) {
                if (StringUtils.isEmpty(row.getString("name"))) {
                    iserr = true;
                    row.put("msg", "错误信息:必填项不能为空");
                    rowserr.add(row);
                } else {
                        Rows userRows = areaRowsMap.get(row.getString("name"));
                        if (userRows.isNotEmpty()) {
                            row.putAll(userRows.get(0));
                            rowssuc.add(row);
                        } else {
                            iserr = true;
                            row.put("msg", "错误信息:数据已导入或区域不在数据库中");
                            rowserr.add(row);
                        }
                }


            }

            String[] types = {"年", "季", "月"};

            long[] ids = createTableID("sa_salestarget", rowssuc.size() * 17);
            int index = 1;

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    SQLFactory saleFactory = new SQLFactory(this, "人员范围新增");
                    saleFactory.addParameter("siteid", siteid);
                    saleFactory.addParameter("sa_salestargethrid", createTableID("sa_salestargethr"));
                    saleFactory.addParameter("hrid", 0);
                    saleFactory.addParameter("position", "");
                    saleFactory.addParameter("sa_saleareaid", row.getString("sa_saleareaid"));
                    saleFactory.addParameter("areaname", row.getString("areaname"));
                    saleFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
                    saleFactory.addParameter("depname", "");
                    saleFactory.addParameter("departmentid",0);
                    saleFactory.addParameter("name","");

                    sqllist.add(saleFactory.getSQL());

                    for (String type : types) {
                        int point;
                        if (type.equals("年")) {
                            point = 1;
                        } else if (type.equals("季")) {
                            point = 4;
                        } else if (type.equals("月")) {
                            point = 12;
                        } else {
                            point = 0;
                        }

                        for (int i = 0; i < point; i++) {
                            double target_l = 0L;
                            double target_h = 0L;
                            if (type.equals("年")) {
                                target_l = row.getDouble("y1l");
                                target_h = row.getDouble("y1h");
                            }
                            if (type.equals("季")) {
                                target_l = row.getDouble("s" + (i + 1) + "l");
                                target_h = row.getDouble("s" + (i + 1) + "h");
                            }
                            if (type.equals("月")) {
                                target_l = row.getDouble("m" + (i + 1) + "l");
                                target_h = row.getDouble("m" + (i + 1) + "h");
                            }


                            SQLFactory targetFactory = new SQLFactory(this, "人员-目标明细新增");
                            targetFactory.addParameter("siteid", siteid);
                            targetFactory.addParameter("sa_salestargetid", ids[index - 1]);
                            targetFactory.addParameter("type", type);
                            targetFactory.addParameter("point", i + 1);
                            targetFactory.addParameter("targettype", "人员目标");
                            targetFactory.addParameter("sa_saleareaid", row.getString("sa_saleareaid"));
                            targetFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
                            targetFactory.addParameter("year", year);
                            targetFactory.addParameter("sa_projectid", "null");
                            targetFactory.addParameter("createby", username);
                            targetFactory.addParameter("target_l", target_l);
                            targetFactory.addParameter("target_h", target_h);

                            sqllist.add(targetFactory.getSQL());
                            index++;
                        }
                    }


                }
            }

            if (sqllist != null && !sqllist.isEmpty()) {
                dbConnect.runSqlUpdate("delete from sa_salestargethr where siteid='"+siteid+"' and sa_salestargetbillid="+sa_salestargetbillid);
                dbConnect.runSqlUpdate("delete from sa_salestarget where siteid='"+siteid+"' and sa_salestargetbillid="+sa_salestargetbillid);

                sqllist.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "导入", "导入人员目标").getSQL());
                dbConnect.runSqlUpdate(sqllist);
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("人员目标导入错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("name");
                String key_l = "y1l";
                colNameList.add(key_l);
                for (int a = 1; a < 5; a++) {
                    key_l = "s" + a + "l";
                    colNameList.add(key_l);
                }
                for (int a = 1; a < 13; a++) {
                    key_l = "m" + a + "l";
                    colNameList.add(key_l);
                }
                colNameList.add("msg");


                keytypemap.put("name", String.class);
                keytypemap.put("phonenumber", String.class);
                key_l = "y1l";
                keytypemap.put(key_l, String.class);
                for (int a = 1; a < 5; a++) {
                    key_l = "s" + a + "l";
                    keytypemap.put(key_l, String.class);
                }
                for (int a = 1; a < 13; a++) {
                    key_l = "m" + a + "l";
                    keytypemap.put(key_l, String.class);
                }


                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr);

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

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows) {
        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);

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

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


}