CUCU2/src/custom/restcontroller/webmanage/sale/project/Project.java

package restcontroller.webmanage.sale.project;

import beans.contract.Contract;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import restcontroller.R;

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.ID20221114133502.v1.class)
    public String changeAgent() throws YosException {
        String tablename = "sa_project";
        Long sa_projectid = content.getLong("sa_projectid");
        //经销商团队负责人id
        Long userid = content.getLong("userid");

        String sql = "SELECT sys_datatagid from sys_datatag WHERE ownertable = '" + tablename + "' and ownerid = " + sa_projectid + " and siteid='" + siteid + "' AND tag='直销'";
        Rows rows = dbConnect.runSqlQuery(sql);

        if (rows.isEmpty()) {
            if (dbConnect.runSqlQuery("SELECT 1 from sys_datatag WHERE ownertable = '" + tablename + "' and ownerid = " + sa_projectid + " and siteid='" + siteid + "' AND tag='经销'").isEmpty()) {
                DataTag.createTag(this, tablename, sa_projectid, "经销");
            }
        } else {
            Long sys_datatagid = rows.get(0).getLong("sys_datatagid");
            dbConnect.runSqlUpdate("UPDATE sys_datatag SET tag='经销' WHERE sys_datatagid =" + sys_datatagid);
        }

        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("delete from sys_datateam WHERE ownertable = '" + tablename + "' and siteid = '" + siteid + "' and ownerid = " + sa_projectid + " AND sys_enterpriseid >0");
        //数据团队新增
        sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_projectid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_project", sa_projectid, userid));
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "撤回", apiversion = R.ID20221114133602.v1.class)
    public String revoke() throws YosException {
        String tablename = "sa_project";
        Long sa_projectid = content.getLong("sa_projectid");
        //1：变更为经销，2：变更为直销
        Long type = content.getLong("type");
        if (content.containsKey("userid")) {
            userid = content.getLong("userid");
        }

        String oldTag = type == 1 ? "直销" : "经销";
        String newTag = type == 1 ? "经销" : "直销";

        String sql = "SELECT sys_datatagid from sys_datatag WHERE ownertable = '" + tablename + "' and ownerid = " + sa_projectid + " and siteid='" + siteid + "' AND tag='" + oldTag + "'";
        Rows rows = dbConnect.runSqlQuery(sql);

        if (rows.isEmpty()) {
            if (dbConnect.runSqlQuery("SELECT 1 from sys_datatag WHERE ownertable = '" + tablename + "' and ownerid = " + sa_projectid + " and siteid='" + siteid + "' AND tag='" + newTag + "'").isEmpty()) {
                DataTag.createTag(this, tablename, sa_projectid, newTag);
            }
        } else {
            Long sys_datatagid = rows.get(0).getLong("sys_datatagid");
            dbConnect.runSqlUpdate("UPDATE sys_datatag SET tag='" + newTag + "' WHERE sys_datatagid =" + sys_datatagid);
        }
        ArrayList<String> sqlList = new ArrayList<>();
        if (type == 1) {
            sqlList.add("delete from sys_datateam WHERE ownertable = '" + tablename + "' and siteid = '" + siteid + "' and ownerid = " + sa_projectid + " AND sys_enterpriseid >0");
        } else if (type == 2) {
            sqlList.add("delete from sys_datateam WHERE ownertable = '" + tablename + "' and siteid = '" + siteid + "' and ownerid = " + sa_projectid);
        }

        //数据团队新增
        sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_projectid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_project", sa_projectid, userid));
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "获取变更经销商或业务员人员列表", apiversion = R.ID20221114141902.v1.class)
    public String getUserList() throws YosException {

        String sql = "";
        switch (usertype) {
            case 1:
                sql = "SELECT name,userid from sys_hr WHERE hrid in (SELECT hrid from sa_salearea_hr WHERE siteid='" + siteid + "') AND userid is not null";
                break;
            case 21:
            case 22:
                sql = "SELECT name,userid from sys_enterprise_hr WHERE sys_enterpriseid in (SELECT sys_enterpriseid from sys_enterprise_tradefield  WHERE sa_saleareaid in (SELECT sa_saleareaid from sa_salearea_hr WHERE hrid IN (SELECT hrid from sys_hr WHERE userid = " + userid + "))) AND isleader";
                break;
        }

        Rows rows = dbConnect.runSqlQuery(sql);

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

    @API(title = "更改模式（经销或直销）", apiversion = R.ID20221114142002.v1.class)
    public String changeModel() throws YosException {

        Long sa_projectid = content.getLong("sa_projectid");
        String newtag = content.getString("tag");
        String oldtag = newtag.equals("直销") ? "经销" : "直销";

        String where1 = "where ownertable = 'sa_project' and ownerid = " + sa_projectid + " and tag='" + newtag + "'";
        String where2 = "where ownertable = 'sa_project' and ownerid = " + sa_projectid + " and tag='" + oldtag + "'";

        if (dbConnect.runSqlQuery("SELECT * from sys_datatag " + where1).isNotEmpty()) {
            dbConnect.runSqlUpdate("UPDATE  sys_datatag  set tag='" + newtag + "' " + where1);
        } else {
            if ((dbConnect.runSqlQuery("SELECT * from sys_datatag " + where2).isNotEmpty())) {
                dbConnect.runSqlUpdate("UPDATE  sys_datatag  set tag='" + newtag + "' " + where2);
            } else {
                dbConnect.runSqlUpdate("INSERT INTO sys_datatag VALUES('" + siteid + "', " + createTableID("sys_datatag") + ",'" + newtag + "'," + sa_projectid + ",'sa_project',1)");
            }
        }


        return getSucReturnObject().toString();
    }

    @API(title = "根据项目查报价单", apiversion = R.ID20221124190702.v1.class)
    public String getQuotedPriceList() 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.projectname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.projectnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.scale like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        long sa_projectid = content.getLongValue("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "根据项目查询报价单", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

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

    @API(title = "根据项目查线索", apiversion = R.ID20221124110002.v1.class)
    public String getOrderClueList() 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("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        long sa_projectid = content.getLongValue("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "根据项目查线索", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap leaderRows = DataTeam.getLeader(this, "sat_orderclue", rows.toArrayList("sat_orderclueid")).toRowsMap("ownerid");
        for (Row row : rows) {
            row.put("leader", leaderRows.get(row.getString("sat_orderclueid")));
        }

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

    @API(title = "根据项目查询关联合同列表", apiversion = R.ID20221223102102.v1.class)
    @CACHEING
    public String getContractList() 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 t1.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            //类型
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }

            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.createdate >='").append(whereObject.getString("begindate")).append("'");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.createdate <='").append(whereObject.getString("enddate")).append("'");
            }

        }
        String tablename = "sa_contract";
        Long sa_projectid = content.getLong("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "根据项目查关联合同", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, tablename, ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, tablename, ids, true);
        //查询价格
        RowsMap signAmountRowsMap = Contract.getContractSignAmount(this, ids);

        for (Row row : rows) {
            Long id = row.getLong("sa_contractid");
            //非系统标签
            row.put("tag", tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>());
            //系统标签
            row.put("tag_sys", sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>());

            if (signAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("signamount", signAmountRowsMap.get(id.toString()).get(0).getBigDecimal("signamount").toPlainString());
            } else {
                row.put("signamount", 0);
            }
        }

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

    /**
     * 获取合同中的项目
     *
     * @return
     */
    @API(title = "合同管理中使用到的项目列表", apiversion = R.ID20221201090802.v1.class)
    public String getContractProject() throws YosException {

        Long type = content.getLong("type");
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }
        String sql = "SELECT ownerid from sys_datatag WHERE  ownertable = 'sa_project' and ";

        ArrayList<Long> ids = new ArrayList<>();
        ids.add(userid);
        switch (type.toString()) {
            //查询我的直销项目
            case "1":
                sql = sql + "tag in ('直销')";
                break;
            //查询我负责的范围内的经销项目
            case "2":
                sql = sql + "tag in ('经销')";
                break;
            case "3":
                sql = sql + "tag in ('经销','直销')";
                break;
            default:
        }

        ArrayList<Long> ownerids = dbConnect.runSqlQuery(sql).toArrayList("ownerid", new ArrayList<>());

        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(")");
            }
        }
        if (type == 1 || type == 2) {
            where.append(" and(");
            where.append("t1.sa_projectid in ( SELECT sa_projectid from sa_project_parties WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "')");
            where.append(")");
        }
        SQLFactory sqlFactory = new SQLFactory(this, "合同管理中使用到的项目列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter_in("sa_projectid", ownerids);

        sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);


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

    }

    @API(title = "项目查重", apiversion = R.ID20221208184202.v1.class)
    public String checkRepeat() throws YosException {
        Long sa_projectid = content.getLong("sa_projectid");
        SQLFactory sqlFactory = new SQLFactory(this, "项目查重");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("projectname", content.getStringValue("projectname"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

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


}