md_YOS/src/custom/restcontroller/sale/project/Project.java

package restcontroller.sale.project;

import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.saletool.orderclue.ExportExcel;

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

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

    @API(title = "我的项目商机", apiversion = R.ID20221020143502.v1.class)
    @CACHEING
    public String selectMyList() throws YosException {
        //  1：我负责的；2：我参与的；3：我下属负责的；4：我下属参与的
        int type = content.getIntValue("type");
        Long deleted = 0L;
        if (content.containsKey("deleted") && !"".equals(content.getString("deleted"))) {
            deleted = content.getLong("deleted");
        }
        String tablename = "sa_project";
        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.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.projectname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.projectnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("startdate") && !"".equals(whereObject.getString("startdate"))) {
                where.append(" and(");
                where.append("t1.createdate >='").append(whereObject.getString("startdate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and(");
                where.append("t1.createdate <='").append(whereObject.getString("enddate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("projecttype") && !"".equals(whereObject.getString("projecttype"))) {
                where.append(" and(");
                where.append("t1.projecttype ='").append(whereObject.getString("projecttype")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("grade") && !"".equals(whereObject.getString("grade"))) {
                where.append(" and(");
                where.append("t1.grade ='").append(whereObject.getString("grade")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("stagename") && !"".equals(whereObject.getString("stagename"))) {
                where.append(" and(");
                where.append("t2.stagename ='").append(whereObject.getString("stagename")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname ='").append(whereObject.getString("brandname")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' ");
                where.append(")");
            }

            //缔约方
            if (whereObject.containsKey("tag") && !"".equals(whereObject.getString("tag"))) {
                where.append(" and( ");
                where.append("t1.sa_projectid in ( SELECT ownerid from sys_datatag WHERE ownertable = 'sa_project' and tag like'%" + whereObject.getString("tag") + "%' )");
                where.append(")");
            }
            if (whereObject.containsKey("sys_enterpriseid") && !"".equals(whereObject.getString("sys_enterpriseid"))) {
                where.append(" and(");
                where.append("exists(select * from sa_project_parties where t1.siteid=sa_project_parties.siteid and t1.sa_projectid=sa_project_parties.sa_projectid and sa_project_parties.sys_enterpriseid='" + whereObject.getString("sys_enterpriseid") + "' )");
                where.append(")");
            }
        }
        where.append(" and(").append("t1.deleted = ").append(deleted).append(")");
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机列表");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("username", username);
        String where2;
        if (type == 0) {
            where2 = DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( 1))
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( 2))
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( 3))
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( 4))
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( 5));
        } else {
            where2 = DataTeam.getDataWhereStr(this, tablename, "t1", DataTeam.FilterType.valueOf( type));
        }

        sqlFactory.addParameter_SQL("where2", where2);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        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> sa_projectids = rows.toArrayList("sa_projectid", new ArrayList<>());
        sqlFactory = new SQLFactory(this, "查询项目缔约方客户类型");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_projectid", sa_projectids);
        RowsMap typeRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_projectid");

        RowsMap leaderRows = DataTeam.getLeader(this, tablename, rows.toArrayList("sa_projectid", new ArrayList<>())).toRowsMap("ownerid");
        sqlFactory = new SQLFactory(this, "阶段排序");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_projectids", rows.toArray("sa_projectid"));
        RowsMap stageRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("projectid");

        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_project", sa_projectids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_project", sa_projectids, true);

        RowsMap deletereasonRowsMap = null;
        if (deleted == 1) {
            deletereasonRowsMap = DataExtend.getDeleteReasonSql(this, "sa_project", rows.toArrayList("sa_projectid", new ArrayList<Long>()));
        }
        for (Row row : rows) {
            Long sa_projectid = row.getLong("sa_projectid");
            if (!stageRowsMap.isEmpty() && stageRowsMap.containsKey(String.valueOf(sa_projectid))) {
                if (stageRowsMap.get(String.valueOf(sa_projectid)).toRowsMap("active").containsKey("1")) {
                    String rowNum = stageRowsMap.get(String.valueOf(sa_projectid)).toRowsMap("active").get("1").get(0).getString("rowNum");
                    row.put("stage", rowNum);
                } else {
                    row.put("stage", 0);
                }
            } else {
                row.put("stage", 0);
            }
            row.put("types", typeRowsMap.get(String.valueOf(sa_projectid)));
            row.put("winrate", 0);
            row.put("leader", leaderRows.get(String.valueOf(sa_projectid)));
            //非系统标签
            row.put("tag", tagList.get(sa_projectid) != null ? tagList.get(sa_projectid) : new ArrayList<String>());
            //系统标签
            row.put("tag_sys", sysTagList.get(sa_projectid) != null ? sysTagList.get(sa_projectid) : new ArrayList<String>());

            if (deletereasonRowsMap != null && deletereasonRowsMap.containsKey(row.getString("sa_projectid"))) {
                Row dataextendrow = deletereasonRowsMap.get(row.getString("sa_projectid")).get(0);
                row.put("deletereason", dataextendrow.getString("deletereason"));
                row.put("deletechangeby", dataextendrow.getString("changeby"));
                row.put("deletechangedate", dataextendrow.getString("changedate"));
            } else {
                row.put("deletereason", "");
                row.put("deletechangeby", "");
                row.put("deletechangedate", "");
            }
        }


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

    }


    @API(title = "新增或更新", apiversion = R.ID20221020144202.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class, R.ID20221018102003.v1.class})
    public String insertOrUpdate() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        String projectname = content.getString("projectname");
        String projecttype = content.getString("projecttype");
        Long sa_brandid = content.getLongValue("sa_brandid");
        String tradefield = content.getStringValue("tradefield");
        String tablename = "sa_project";
        RowsMap stageRowsMap = dbConnect.runSqlQuery("select t1.sa_projstagemagid ,t1.stagename,t1.remarks,t1.sequence,t2.projecttype from sa_projstagemag t1 inner join  sa_projstagemag_projecttype t2 on t1.siteid=t2.siteid and t1.sa_projstagemagid=t2.sa_projstagemagid where t1.siteid='" + siteid + "'").toRowsMap("projecttype");
        Rows projectRows = dbConnect.runSqlQuery("select projecttype from sa_project where siteid='" + siteid + "' and sa_projectid=" + sa_projectid);
        String projectnum = content.getStringValue("projectnum");
        if (projectnum.isEmpty()) {
            projectnum = createBillCode("projectbill");
        }

        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机新增");
        if (sa_projectid <= 0 || projectRows.isEmpty()) {
            sa_projectid = createTableID(tablename);
            //数据团队新增
            sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_projectid, userid));
            sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_project", sa_projectid, userid));
            //添加直销标签
            DataTag.createTag(this, tablename, sa_projectid, "直销");

            String sql = DataContrlLog.createLog(this, "sa_project", sa_projectid, "新建", "新建项目商机" + projectnum).getSQL();
            sqlList.add(sql);
        } else {
            sqlFactory = new SQLFactory(this, "项目商机更新");
            String sql = DataContrlLog.createLog(this, "sa_project", sa_projectid, "编辑", "编辑项目商机" + projectnum).getSQL();
            sqlList.add(sql);
        }


        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter("departmentid", departmentid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("projectname", projectname);
        sqlFactory.addParameter("projectnum", createBillCode("projectbill"));
        sqlFactory.addParameter("grade", content.getStringValue("grade"));
        sqlFactory.addParameter("budgetary", content.getStringValue("budgetary"));
        sqlFactory.addParameter("remarks", content.getStringValue("remarks"));
        sqlFactory.addParameter("projecttype", projecttype);
        sqlFactory.addParameter("signdate_due", content.getStringValue("signdate_due"));
        sqlFactory.addParameter("scale", content.getStringValue("scale"));
        sqlFactory.addParameter("signamount_due", content.getBigDecimal("signamount_due"));
        sqlFactory.addParameter("province", content.getStringValue("province"));
        sqlFactory.addParameter("city", content.getStringValue("city"));
        sqlFactory.addParameter("county", content.getStringValue("county"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        sqlFactory.addParameter("discountrate", content.getBigDecimal("discountrate"));
        sqlFactory.addParameter("costofconstruction", content.getBigDecimal("costofconstruction"));
        sqlFactory.addParameter("totalinvestment", content.getBigDecimal("totalinvestment"));
        sqlFactory.addParameter("begdate_due", content.getStringValue("begdate_due", false, "null"));
        sqlFactory.addParameter("enddate_due", content.getStringValue("enddate_due", false, "null"));
        sqlFactory.addParameter("sa_brandid", sa_brandid);
        sqlFactory.addParameter("tradefield", tradefield);
        sqlList.add(sqlFactory.getSQL());

        if (dbConnect.runSqlQuery("SELECT 1 from sa_project_stage WHERE sa_projectid = " + sa_projectid + " AND siteid = '" + siteid + "'").isEmpty()) {
            Rows rows = stageRowsMap.get(projecttype);
            for (int i = 0; i < rows.size(); i++) {
                Row row = rows.getRow(i);
                sqlFactory = new SQLFactory(this, "工程项目阶段信息新增");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_project_stageid", createTableID("sa_project_stage"));
                sqlFactory.addParameter("sa_projectid", sa_projectid);
                sqlFactory.addParameter("sa_projstagemagid", row.getLong("sa_projstagemagid"));
                sqlFactory.addParameter("stagename", row.getString("stagename"));
                sqlFactory.addParameter("remarks", row.getString("remarks"));
                sqlFactory.addParameter("sequence", row.getLong("sequence"));
                if (i == 0) {
                    sqlFactory.addParameter("active", 1);
                } else {
                    sqlFactory.addParameter("active", 0);
                }
                sqlList.add(sqlFactory.getSQL());
            }

        }

        dbConnect.runSqlUpdate(sqlList);

        content.put("sa_projectid", sa_projectid);


        return selectDetail();
    }


    @API(title = "作废", apiversion = R.ID20221020144302.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class, R.ID20221018102003.v1.class})
    public String delete() throws YosException {
        JSONArray sa_projectids = content.getJSONArray("sa_projectids");

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

        SQLFactory sqlFactory = new SQLFactory(this, "项目商机作废");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_projectid", sa_projectids.toArray());
        sqlList.add(sqlFactory.getSQL());

        //新增删除或作废原因
        DataExtend.createDeleteReasonSql(this, "sa_project", sa_projectids.toJavaList(Long.class), content.getStringValue("deletereason"));

        for (Object obj : sa_projectids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, "sa_project", id, "作废", "作废项目商机至 回收站:" + id).getSQL());
        }

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

    @API(title = "报备", apiversion = R.ID20221021092302.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class})
    public String report() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        String reportby = content.getString("reportby");
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机报备");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("reportby", reportby);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        dbConnect.runSqlUpdate(sqlFactory);
        dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_project", sa_projectid, "报备", "项目商机报备成功").getSQL());
        return getSucReturnObject().toString();
    }

    @API(title = "报备", apiversion = R.ID20221021092402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class, R.ID20221018102003.v1.class})
    public String reportcheck() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机报备审核");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        dbConnect.runSqlUpdate(sqlFactory);
        dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_project", sa_projectid, "报备审核", "项目商机报备审核成功").getSQL());
        return getSucReturnObject().toString();
    }

    @API(title = "可报备人员列表", apiversion = R.ID20221021093202.v1.class)
    public String reportList() 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, "报备人列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("userid", userid);
//        sqlFactory.addParameter_SQL("where", where);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_users", "userid", "name");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_usersite", "t2", "t2.userid = t1.userid");
        querySQL.setWhere("t2.siteid", siteid);
        querySQL.setWhere("t1.sysadministrator = 0 AND t1.createby != 'system'");
        querySQL.setWhere("t1.userid!=" + userid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "我负责的客户", apiversion = R.ID20221021095802.v1.class)
    public String myCustomerList() 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.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");

                where.append(")");
            }

        }

//        SQLFactory sqlFactory = new SQLFactory(this, "我的客户", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter_SQL("where2", DataTeam.getDataWhereStr(this, "sa_customers", "t1", 1));
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid", "sys_enterpriseid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid AND t2.siteid = t1.siteid", "enterprisename");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere(DataTeam.getDataWhereStr(this, "sa_customers", "t1", DataTeam.FilterType.valueOf( 1)));
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


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

    @API(title = "项目商机详情", apiversion = R.ID20221021103902.v1.class)
    public String selectDetail() throws YosException {

        Long sa_projectid = content.getLong("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        RowsMap leaderRows = DataTeam.getLeader(this, "sa_project", rows.toArrayList("sa_projectid", new ArrayList<>())).toRowsMap("ownerid");

        for (Row row : rows) {
            if (leaderRows.get(row.getString("sa_projectid")) == null) {
                row.put("leader", new Rows());
            } else {
                row.put("leader", leaderRows.get(row.getString("sa_projectid")));
            }

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

        sqlFactory = new SQLFactory(this, "查询最近跟进信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("ownerid", sa_projectid);
        Rows followRows = dbConnect.runSqlQuery(sqlFactory);

        if (followRows.isNotEmpty()) {
            row.putAll(followRows.get(0));
        } else {
            row.put("followby", "");
            row.put("followdate", "");
        }

        //转移次数
        row.put("changecount", 0);

        row.put("winrate", 0);


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

    @API(title = "项目商机导入模板", apiversion = R.ID20221110151704.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 = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();
    }

    @API(title = "项目商机导入", apiversion = R.ID20221110152504.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class, R.ID20221018102003.v1.class})
    public String importExcel() throws YosException {

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

            ArrayList<String> keys = new ArrayList<>();
            ArrayList<String> sqlList = new ArrayList<>();
            keys.add("projectname");
            keys.add("budgetary");
            keys.add("projecttype");
            keys.add("signdate_due");
            keys.add("scale");
            keys.add("signamount_due");
            keys.add("province");
            keys.add("city");
            keys.add("county");
            keys.add("address");

            Rows rows = e.getSheetRows(0, keys, 2);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            RowsMap stageRowsMap = dbConnect.runSqlQuery("select t1.sa_projstagemagid ,t1.stagename,t1.remarks,t1.sequence,t2.projecttype from sa_projstagemag t1 inner join  sa_projstagemag_projecttype t2 on t1.siteid=t2.siteid and t1.sa_projstagemagid=t2.sa_projstagemagid where t1.siteid='" + siteid + "'").toRowsMap("projecttype");
            Rows task = dbConnect.runSqlQuery("select t1.onstage,t2.sa_projstagemagid,t3.projecttype from sa_projtaskmag t1 left join sa_projtaskmag_optionscore t2 on t1.siteid=t2.siteid and t1.sa_projtaskmagid=t2.sa_projtaskmagid left join sa_projstagemag_projecttype t3 on t2.siteid=t3.siteid and t2.sa_projstagemagid=t3.sa_projstagemagid  where t1.siteid='" + siteid + "'");
            RowsMap onstageRowsMap = task.toRowsMap("onstage");
            RowsMap taskStageRowsMap = onstageRowsMap.get("1").toRowsMap("projecttype");

            for (Row row : rows) {
                StringBuffer err = new StringBuffer();
                if (StringUtils.isEmpty(row.getString("projectname"))) {
                    iserr = true;
                    err.append("错误信息:项目名称不能为空;");
                }
                if (StringUtils.isEmpty(row.getString("projecttype"))) {
                    iserr = true;
                    err.append("错误信息:项目类型不能为空;");
                } else {
                    if (onstageRowsMap.get("0").isEmpty()) {
                        if (!taskStageRowsMap.containsKey(row.getString("projecttype"))) {
                            iserr = true;
                            err.append("错误信息:该项目类型下没有项目任务,无法创建项目商机");
                        }
                    }
                }
                if (err.length() > 0) {
                    row.put("msg", err);
                    rowserr.add(row);
                } else {
                    rowssuc.add(row);
                }

            }

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    String tablename = "sa_project";
                    Long sa_projectid = createTableID(tablename);
                    //数据团队新增
                    sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_projectid, userid));
                    sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_project", sa_projectid, userid));

                    if (usertype == 1 || usertype == 0) {
                        DataTag.createTag(this, tablename, sa_projectid, "直销");
                    }

                    sa_projectid = createTableID("sa_project");
                    SQLFactory sqlFactoryupload = new SQLFactory(this, "项目商机新增");
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("sa_projectid", sa_projectid);
                    sqlFactoryupload.addParameter("departmentid", departmentid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("projectname", row.getString("projectname"));
                    sqlFactoryupload.addParameter("projectnum", createBillCode("projectbill"));
                    sqlFactoryupload.addParameter("grade", "null");
                    sqlFactoryupload.addParameter("budgetary", row.getString("budgetary"));
                    sqlFactoryupload.addParameter("remarks", "");
                    sqlFactoryupload.addParameter("projecttype", row.getString("projecttype"));
                    sqlFactoryupload.addParameter("signdate_due", row.getString("signdate_due"));
                    sqlFactoryupload.addParameter("scale", "");
                    sqlFactoryupload.addParameter("signamount_due", row.getBigDecimal("signamount_due"));
                    sqlFactoryupload.addParameter("province", row.getString("province"));
                    sqlFactoryupload.addParameter("city", row.getString("city"));
                    sqlFactoryupload.addParameter("county", row.getString("county"));
                    sqlFactoryupload.addParameter("address", row.getString("address"));
                    sqlList.add(sqlFactoryupload.getSQL());
                    DataContrlLog.createLog(this, "sa_project", sa_projectid, "新增", "项目商机导入:" + sa_projectid);
                    //创建项目阶段
                    Rows rows1 = stageRowsMap.get(row.getString("projecttype"));
                    for (int i = 0; i < rows1.size(); i++) {
                        Row row1 = rows1.getRow(i);
                        sqlFactoryupload = new SQLFactory(this, "工程项目阶段信息新增");
                        sqlFactoryupload.addParameter("siteid", siteid);
                        sqlFactoryupload.addParameter("sa_project_stageid", createTableID("sa_project_stage"));
                        sqlFactoryupload.addParameter("sa_projectid", sa_projectid);
                        sqlFactoryupload.addParameter("sa_projstagemagid", row1.getLong("sa_projstagemagid"));
                        sqlFactoryupload.addParameter("stagename", row1.getString("stagename"));
                        sqlFactoryupload.addParameter("remarks", row1.getString("remarks"));
                        sqlFactoryupload.addParameter("sequence", row1.getLong("sequence"));
                        if (i == 0) {
                            sqlFactoryupload.addParameter("active", 1);
                        } else {
                            sqlFactoryupload.addParameter("active", 0);
                        }
                        sqlList.add(sqlFactoryupload.getSQL());

                    }


                }
            }


            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("项目商机导入错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("projectname");
                colNameList.add("budgetary");
                colNameList.add("projecttype");
                colNameList.add("signdate_due");
                colNameList.add("scale");
                colNameList.add("signamount_due");
                colNameList.add("province");
                colNameList.add("city");
                colNameList.add("county");
                colNameList.add("address");
                colNameList.add("msg");
                keytypemap.put("projectname", String.class);
                keytypemap.put("budgetary", String.class);
                keytypemap.put("projecttype", String.class);
                keytypemap.put("signdate_due", String.class);
                keytypemap.put("scale", String.class);
                keytypemap.put("signamount_due", String.class);
                keytypemap.put("province", String.class);
                keytypemap.put("city", String.class);
                keytypemap.put("county", String.class);
                keytypemap.put("address", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }

            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) {
        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 == 10) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    @API(title = "解锁/锁定", apiversion = R.ID20221024094502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.v1.class})
    public String isLocked() throws YosException {

        JSONArray sa_projectids = content.getJSONArray("sa_projectids");

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

        boolean locked = content.getBooleanValue("locked");
        SQLFactory sqlFactory = new SQLFactory(this, "项目商机锁定");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("locked", locked);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_projectid", sa_projectids.toArray());

        sqlList.add(sqlFactory.getSQL());

        //操作记录
        for (Object obj : sa_projectids) {
            Long id = Long.valueOf(obj.toString());
            if (locked) {
                sqlList.add(DataContrlLog.createLog(this, "sa_project", id, "锁定", "项目商机锁定:" + id).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_project", id, "解锁", "项目商机解锁:" + id).getSQL());
            }

        }

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

    @API(title = "查询竞争对手", apiversion = R.ID20221027165402.v1.class)
    public String selectCompete() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        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.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.brandname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.advantage like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.inferiority like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

//        SQLFactory sqlFactory = new SQLFactory(this, "竞争对手列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("sa_projectid", sa_projectid);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_project_compete", "sa_competitorid", "sa_projectid",
                "advantage", "inferiority", "remarks", "important");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_competitor", "t2", "t1.sa_competitorid = t2.sa_competitorid and t1.siteid = t2.siteid", "brandname", "createby", "createdate");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t2.sys_enterpriseid = t3.sys_enterpriseid and t2.siteid = t3.siteid", "enterprisename", "industry", "sys_enterpriseid");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.deleted = 0");
        querySQL.setWhere("t1.sa_projectid", sa_projectid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "报备审核", apiversion = R.ID20221114163402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220929085401.v1.class})
    public String addTag() throws YosException {

        Long sa_projectid = content.getLong("sa_projectid");
        Long type = content.getLong("type");

        String projectnum = "";
        Rows rows = dbConnect.runSqlQuery("SELECT projectnum from sa_project WHERE sa_projectid = " + sa_projectid + " and siteid='" + siteid + "'");
        if (rows.isNotEmpty()) {
            projectnum = rows.get(0).getString("projectnum");
        }

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


        switch (type.toString()) {
            case "1":
                //提交报备
                if (dbConnect.runSqlQuery("SELECT * from sys_datatag WHERE tag in ('报备中','已报备') and ownerid = " + sa_projectid + " and siteid = '" + siteid + "' and ownertable ='sa_project'").isNotEmpty()) {
                    return getErrReturnObject().setErrMsg("当前状态无法提交报备").toString();
                }
                DataTag.createTag(this, "sa_project", sa_projectid, "报备中");
                sqlList.add("UPDATE sa_project SET reportby='" + username + "',reportdate=CURRENT_TIME WHERE sa_projectid = " + sa_projectid + " and siteid='" + siteid + "'");
                //操作记录
                sqlList.add(DataContrlLog.createLog(this, "sa_project", sa_projectid, "提交报备", "提交报备项目商机" + projectnum).getSQL());
                break;
            case "2":
                //报备审核
                if (dbConnect.runSqlQuery("SELECT * from sys_datatag WHERE tag in ('报备中') and ownerid = " + sa_projectid + " and siteid = '" + siteid + "' and ownertable ='sa_project'").isNotEmpty()) {
                    dbConnect.runSqlUpdate("UPDATE sys_datatag SET tag='已报备' WHERE  tag in ('报备中') and ownerid = " + sa_projectid + " and siteid = '" + siteid + "' and ownertable ='sa_project'");

                    sqlList.add("UPDATE sa_project SET reportcheckby='" + username + "',reportcheckdate=CURRENT_TIME WHERE sa_projectid = " + sa_projectid + " and siteid='" + siteid + "'");
                    //操作记录
                    sqlList.add(DataContrlLog.createLog(this, "sa_project", sa_projectid, "报备审核", "报备审核项目商机" + projectnum).getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("当前状态无法报备审核").toString();
                }

                break;
            case "3":
                //取消审核
                if (dbConnect.runSqlQuery("SELECT * from sys_datatag WHERE tag in ('已报备','报备中') and ownerid = " + sa_projectid + " and siteid = '" + siteid + "' and ownertable ='sa_project'").isNotEmpty()) {
                    sqlList.add("delete from sys_datatag WHERE  tag in ('已报备','报备中') and ownerid = " + sa_projectid + " and siteid = '" + siteid + "' and ownertable ='sa_project'");
                    sqlList.add("UPDATE sa_project SET reportby=null,reportdate=null,reportcheckby=null,reportcheckdate=null WHERE sa_projectid = " + sa_projectid + " and siteid='" + siteid + "'");
                    //操作记录
                    sqlList.add(DataContrlLog.createLog(this, "sa_project", sa_projectid, "报备驳回(退回)", "报备驳回(退回)项目商机" + projectnum).getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("当前状态无法取消审核").toString();
                }
                break;
            default:
                break;
        }

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();

    }

    @API(title = "获取所有阶段名称", apiversion = R.ID20221116104502.v1.class)
    public String getStageName() throws YosException {

        Rows rows = dbConnect.runSqlQuery("SELECT DISTINCT  stagename from sa_project_stage  WHERE siteid ='" + siteid + "'");
        return getSucReturnObject().setData(rows).toString();

    }

    @API(title = "结束:在跟进中状态下，操作【结束】功能，项目状态变更为：已失败", apiversion = R.ID20221215163702.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.class})
    public String endProject() throws YosException {

        Long sa_projectid = content.getLong("sa_projectid");
        String sql = "SELECT `status`,projectnum from sa_project WHERE sa_projectid = " + sa_projectid + " and siteid = '" + siteid + "'";
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("项目不存在").toString();
        }
        String projectnum = "";
        if (rows.isNotEmpty()) {
            if (!rows.get(0).getString("status").equals("跟进中")) {
                return getErrReturnObject().setErrMsg("只有【跟进中】项目才能结束").toString();
            }
            projectnum = rows.get(0).getString("projectnum");
        }

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

        sql = "UPDATE sa_project SET status='已失败' WHERE sa_projectid = " + sa_projectid + " and siteid = '" + siteid + "'";
        sqlList.add(sql);
        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_project", sa_projectid, "结束", "结束项目商机" + projectnum).getSQL());

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }

    @API(title = "恢复:在已失败状态下，操作【恢复】功能，项目状态变更为：跟进中", apiversion = R.ID20221215164002.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221020143502.class})
    public String recoveryProject() throws YosException {

        Long sa_projectid = content.getLong("sa_projectid");
        String sql = "SELECT `status`,projectnum from sa_project WHERE sa_projectid = " + sa_projectid + " and siteid = '" + siteid + "'";
        Rows rows = dbConnect.runSqlQuery(sql);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("项目不存在").toString();
        }
        String projectnum = "";
        if (rows.isNotEmpty()) {
            if (!rows.get(0).getString("status").equals("已失败")) {
                return getErrReturnObject().setErrMsg("只有【已失败】项目才能恢复").toString();
            }
            projectnum = rows.get(0).getString("projectnum");
        }

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

        sql = "UPDATE sa_project SET status='跟进中' WHERE sa_projectid = " + sa_projectid + " and siteid = '" + siteid + "'";
        sqlList.add(sql);
        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_project", sa_projectid, "恢复", "恢复项目商机" + projectnum).getSQL());

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }


}