CUCU2/src/custom/restcontroller/webmanage/sale/salestarget/project.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 project extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public project(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新建年度或添加人员", apiversion = R.ID20220903162902.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = project.class, method = {"queryList", "queryDetailList"})})
    public String insert() throws YosException {
        Long year = content.getLong("year");
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        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("targettype", "项目目标");
            sqlFactory.addParameter("tradefield", "");
            sqlList.add(sqlFactory.getSQL());
        }

        for (Object obj : salesArray) {
            JSONObject saleObj = (JSONObject) obj;
            SQLFactory saleFactory = new SQLFactory(this, "人员范围新增");
            saleFactory.addParameter("siteid", siteid);
            saleFactory.addParameter("sa_salestargethrid", createTableID("sa_salestargethr"));
            saleFactory.addParameter("hrid", saleObj.getString("hrid"));
            saleFactory.addParameter("position", saleObj.getString("position"));
            saleFactory.addParameter("areaname", saleObj.getString("areaname"));
            saleFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            saleFactory.addParameter("depname", saleObj.getString("depname"));
            saleFactory.addParameter("departmentid", saleObj.getString("departmentid"));
            saleFactory.addParameter("name", saleObj.getString("name"));
            sqlList.add(saleFactory.getSQL());


        }

        sqlList.add(DataContrlLog.createLog(this, "sa_salestargetbill", sa_salestargetbillid, "新建", "新建项目目标" + year + "年度项目目标").getSQL());

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

    @API(title = "删除", apiversion = R.ID20220903172302.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = project.class, method = {"queryList", "queryDetailList"})})
    public String deletePeople() throws YosException {
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        JSONArray salesArray = content.getJSONArray("hrid");


        String sql = "SELECT 1 FROM sa_salestarget WHERE hrid 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("hrid", salesArray.toArray());
        sqlList.add(sqlFactory.getSQL());
        //删除人员目标
        SQLFactory sqlFactory2 = new SQLFactory(this, "项目-删除目标明细");
        sqlFactory2.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory2.addParameter("siteid", siteid);
        sqlFactory2.addParameter_in("hrid", salesArray.toArray());
        sqlList.add(sqlFactory2.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "年度目标列表", apiversion = R.ID20220903170002.v1.class)
    @CACHEING
    public String queryList() 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.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", "项目目标");
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


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

    @API(title = "目标详情", apiversion = R.ID20220909155202.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());
        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Rows()).toString();
    }

    @API(title = "目标详情列表", apiversion = R.ID20220909152802.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);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


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


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

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

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        projectexcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        projectexcel.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.ID20220914163602.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220909152802.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"));
            } else {
                e = getPostExcelFactory();
            }


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

            keys.add("name");
            keys.add("phonenumber");
            keys.add("projectname");
            keys.add("target_l");
            keys.add("target_h");
            keys.add("month");

            Rows rows = e.getSheetRows(0, keys, 2);
            ArrayList<String> phoneNumberList = new ArrayList<>();
            ArrayList<String> projectnameList = new ArrayList<>();
            for (Row row : rows) {
                phoneNumberList.add(row.getString("phonenumber"));
                projectnameList.add(row.getString("projectname"));
            }

            SQLFactory projectFactory = new SQLFactory(this, "项目-查询项目信息");
            projectFactory.addParameter("siteid", siteid);
            projectFactory.addParameter_in("projectname", projectnameList);
            String projectSql = projectFactory.getSQL();
            RowsMap projectRowsMap = dbConnect.runSqlQuery(projectSql).toRowsMap("projectname");
            for (Row row : rows) {
                if (projectRowsMap.get(row.getString("projectname")).isNotEmpty()) {
                    row.put("sa_projectid", projectRowsMap.get(row.getString("projectname")).get(0).getLong("sa_projectid"));
                } else {
                    row.put("sa_projectid", 0);
                }

            }

            //查询可导入的用户信息
            SQLFactory sqlFactory = new SQLFactory(this, "项目-查询可导入用户信息");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter_in("phonenumber", phoneNumberList);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            String sql = sqlFactory.getSQL();
            RowsMap userRowsMap = dbConnect.runSqlQuery(sql).toRowsMap("phonenumber");
            //查询项目情况

            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"))
                        || StringUtils.isEmpty(row.getString("phonenumber"))) {
                    iserr = true;
                    row.put("msg", "错误信息:必填项不能为空");
                    rowserr.add(row);
                } else {
                    if (!row.getString("phonenumber").matches(regex)) {
                        iserr = true;
                        row.put("msg", "错误信息:手机号格式不正确");
                        rowserr.add(row);
                    } else {
                        Rows userRows = userRowsMap.get(row.getString("phonenumber"));
                        if (userRows.isNotEmpty()) {
                            row.putAll(userRows.get(0));
                            rowssuc.add(row);
                        } else {
                            iserr = true;
                            row.put("msg", "错误信息:数据已导入或人员手机号不在数据库中");
                            rowserr.add(row);
                        }

                    }
                }


            }


            long[] ids = createTableID("sa_salestarget", rowssuc.size() * 12);
            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", row.getString("hrid"));
                    saleFactory.addParameter("position", row.getString("position"));
                    saleFactory.addParameter("areaname", row.getString("areaname"));
                    saleFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
                    saleFactory.addParameter("depname", row.getString("depname"));
                    saleFactory.addParameter("departmentid", row.getString("departmentid"));
                    saleFactory.addParameter("name", row.getString("name"));
                    sqllist.add(saleFactory.getSQL());

                    for (int i = 1; i < 13; i++) {
                        SQLFactory targetFactory = new SQLFactory(this, "项目-目标明细新增");
                        targetFactory.addParameter("siteid", siteid);
                        targetFactory.addParameter("sa_salestargetid", ids[index - 1]);
                        targetFactory.addParameter("type", "月");
                        targetFactory.addParameter("point", i);
                        targetFactory.addParameter("targettype", "项目目标");
                        targetFactory.addParameter("hrid", row.getString("hrid"));
                        targetFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
                        targetFactory.addParameter("year", year);
                        targetFactory.addParameter("sa_projectid", row.getString("sa_projectid"));
                        targetFactory.addParameter("createby", username);
                        targetFactory.addParameter("target_l", 0);
                        targetFactory.addParameter("target_h", 0);
                        sqllist.add(targetFactory.getSQL());
                        index++;
                    }

                    String target_l = row.getString("target_l");
                    String target_h = row.getString("target_h");
                    String sa_projectid = row.getString("sa_projectid");
                    String month = row.getString("month");
                    String sqlUpdate = "UPDATE sa_salestarget SET target_l=" + target_l + ",target_h=" + target_h + " WHERE hrid = " + row.getString("hrid") + " AND point =" + month + " AND `year` = " + year + " AND sa_projectid = " + sa_projectid + " AND siteid ='" + siteid + "' ";
                    sqllist.add(sqlUpdate);
                }

            }

            if (sqllist != null && !sqllist.isEmpty()) {
                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");
                colNameList.add("phonenumber");
                colNameList.add("projectname");
                colNameList.add("target_l");
                colNameList.add("target_h");
                colNameList.add("month");
                colNameList.add("msg");
                keytypemap.put("name", String.class);
                keytypemap.put("phonenumber", String.class);
                keytypemap.put("projectname", String.class);
                keytypemap.put("target_l", String.class);
                keytypemap.put("target_h", String.class);
                keytypemap.put("month", 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);

        projectexcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        projectexcel.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)));
                } 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 == 6) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
}