YOS_PRSX/src/custom/restcontroller/webmanage/sale/agents/agents2.java

package restcontroller.webmanage.sale.agents;

import beans.CommonHepler;
import beans.datacontrllog.DataContrlLog;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.recycle.Recycle;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import jdk.internal.org.objectweb.asm.tree.LineNumberNode;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import restcontroller.R;
import restcontroller.sale.customer.ExportExcel;

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

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

    @API(title = "关联查询医院", apiversion = R.ID2025102409284502.v1.class)
    public String queryHospitalList() throws YosException {

        Long sa_agentsid = content.getLongValue("sa_agentsid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid", "createby", "createdate",
                "sys_enterpriseid", "status", "type", "tradingstatus", "datastatus", "billno", "province", "city", "county", "address", "stagename", "grade");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid AND t2.siteid = t1.siteid", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t3", "t3.sa_saleareaid = t1.sa_saleareaid AND t3.siteid = t1.siteid", "areaname");

        QuerySQL t5 = SQLFactory.createQuerySQL(this, "sys_datafollowup", "ownerid");
        t5.setWhere("ownertable", "sa_customers");
        t5.setSiteid(siteid);
        t5.addGroupBy("ownerid");
        t5.addQueryFields("followdate", "max(createdate)");
        t5.setRoleDataLimit(false);
        querySQL.addJoinTable(JOINTYPE.left, t5, "t5", "t5.ownerid = t1.sa_customersid", "followdate");
        querySQL.addJoinTable(JOINTYPE.left, "sys_datateam", "t6", "t6.ownerid = t1.sa_customersid and t6.siteid = t1.siteid and t6.ownertable = 'sa_customers' and t6.isleader = 1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t7", "t7.userid = t6.userid and t7.siteid = t6.siteid ");
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t8", "t8.departmentid = t7.departmentid and t8.siteid = t7.siteid ");

        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_customersid in (SELECT sa_customersid from sa_agents_hospital WHERE sa_agentsid=" + sa_agentsid + " and siteid='" + siteid + "')");
        querySQL.setWhere("t1.datastatus in (0,2) ");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);

        querySQL.setRoleDataLimit(false);
        querySQL.withDeleteData(true);
        querySQL.setCondition("t1.billno", "t2.enterprisename");
        Rows rows = querySQL.query();
        RowsMap leaderRows = DataTeam.getLeaderWithoutHeadpic(this, "sa_customers", rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        ArrayList<Long> ids = rows.toArrayList("sa_customersid", new ArrayList<>());
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_customers", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_customers", ids, true);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        RowsMap KeyDoctorsRowsMap = CommonHepler.getKeyDoctorsRowsMap(this, ids);
        for (Row row : rows) {
            Long id = row.getLong("sa_customersid");
            row.put("leader", leaderRows.get(String.valueOf(id)));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("name", leaderRows.get(String.valueOf(id)).get(0).getString("name"));
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            //预估手术总量
            row.putIfAbsent("totalop", 0);
            //关键人
            Rows KeyDoctors = KeyDoctorsRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("keyperson", StringUtils.join(KeyDoctors.toArray("doctorname"), ","));
            String stagename = row.getString("stagename");
            for (Row stageRow : stageRows) {
                if (stagename.equals(stageRow.getString("stagename"))) {
                    stageRow.put("active", 1);
                }
                stageRow.putIfAbsent("active", 0);
            }
            row.put("stages", stageRows);
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "关联查询科室", apiversion = R.ID2025111909582402.v1.class)
    public String queryhospitaldepList() throws YosException {

        Long sa_agentsid = content.getLongValue("sa_agentsid");


        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.hospitaldepname 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("or t5.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.doctors like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询科室负责人");
        sqlFactory.addParameter("siteid", siteid);


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_hospitaldep", "*").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid and t3.siteid=t2.siteid", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_agents_hospital", "t4", "t4.sa_hospitaldepid=t1.sa_hospitaldepid and t4.siteid=t1.siteid", "sa_agents_hospitalid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_datateam", "t5", "t5.ownerid = t1.sa_hospitaldepid and t5.siteid = t1.siteid and t5.ownertable = 'sa_hospitaldep' and t5.isleader = 1");
        querySQL.addJoinTable(JOINTYPE.left, sqlFactory, "t6", "t6.sa_hospitaldepid = t1.sa_hospitaldepid", "doctors");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t4.sa_agentsid", sa_agentsid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_hospitaldepid", new ArrayList<>());
        RowsMap leaderRows = DataTeam.getLeader(this, "sa_hospitaldep", rows.toArrayList("sa_hospitaldepid")).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_hospitaldep", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_hospitaldep", ids, true);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        for (Row row : rows) {
            Long id = row.getLong("sa_hospitaldepid");
            row.put("leader", leaderRows.get(row.getString("sa_hospitaldepid")));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            String stagename = row.getString("stagename");
            for (Row stageRow : stageRows) {
                if (stagename.equals(stageRow.getString("stagename"))) {
                    stageRow.put("active", 1);
                }
                stageRow.putIfAbsent("active", 0);
            }
            row.put("stages", stageRows);
        }

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


    @API(title = "关联查询合同", apiversion = R.ID2025102710011202.v1.class)
    public String queryContractList() 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.billno 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("or t5.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        Long sa_agentsid = content.getLongValue("sa_agentsid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract",
                "sa_contractid", "billno", "type", "begdate", "enddate", "createdate", "createby", "remarks", "signdate");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid and t3.siteid=t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t4.sa_agentsid=t1.sa_agentsid and t4.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid=t4.sys_enterpriseid and t5.siteid=t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t6", "t6.hrid=t1.saler_hrid and t6.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t7", "t1.siteid = t7.siteid and t1.sa_contractid = t7.ownerid and t7.ownertable = 'sa_contract'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t4.sa_agentsid", sa_agentsid);
        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salername", "t6.name");
        querySQL.addQueryFields("deletechangedate", "t7.changedate");
        querySQL.addQueryFields("deletechangeby", "t7.changeby");
        querySQL.addQueryFields("status", "if(current_date < t1.begdate,'未生效',if(current_date > t1.enddate,'结束','生效'))");
        querySQL.setPage(pageSize, pageNumber);
        if (pageSorting.equals("''")) {
            pageSorting = "t1.createdate desc";
        }
        querySQL.setOrderBy(pageSorting);

        querySQL.withDeleteData(true);

        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());
        RowsMap leaderRows = DataTeam.getLeader(this, "sa_contract", rows.toArrayList("sa_contractid", new ArrayList<>())).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_contract", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_contract", ids, true);

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

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

    @API(title = "添加科室", apiversion = R.ID2025111914313002.v1.class)
    public String addhospitaldep() throws YosException {


        Long sa_agentsid = content.getLongValue("sa_agentsid");
        JSONArray sa_hospitaldepids = content.getJSONArray("sa_hospitaldepids");


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

        for (Object object : sa_hospitaldepids) {
            Long sa_hospitaldepid = Long.valueOf(object.toString());
            Rows rows = dbConnect.runSqlQuery("SELECT * from sa_hospitaldep WHERE sa_hospitaldepid=" + sa_hospitaldepid);
            Long sa_customersid = 0L;
            if (rows.isNotEmpty()) {
                sa_customersid = rows.get(0).getLong("sa_customersid");
            }

            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_agents_hospital");
            insertSQL.setUniqueid(createTableID("sa_agents_hospital"));
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("sa_agentsid", sa_agentsid);
            insertSQL.setValue("sa_hospitaldepid", sa_hospitaldepid);
            insertSQL.setValue("sa_customersid", sa_customersid);
            insertSQL.setWhere(" not exists(select 1 from sa_agents_hospital where sa_agentsid=" + sa_agentsid + " and siteid='" + siteid + "'  and sa_hospitaldepid='" + sa_hospitaldepid + "')");
            sqlList.add(insertSQL.getSQL());
            sqlList.add(DataContrlLog.createLog(this, "sa_hospitaldep", sa_hospitaldepid, "关联经销商", "关联经销商ID:" + sa_agentsid).getSQL());
            sqlList.add(DataContrlLog.createLog(this, "sa_agents", sa_agentsid, "签约科室", "签约科室ID:" + sa_hospitaldepid).getSQL());
            DataTag.createSystemTag(this, "sa_customers", sa_customersid, "经销");
        }
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "选择科室", apiversion = R.ID2025111914425902.v1.class)
    public String chooseHospitalDep() 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.hospitaldepname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        Long sa_agentsid = content.getLongValue("sa_agentsid");
        where.append(" and t1.sa_hospitaldepid in (SELECT ownerid from sys_enterprise_tradefield t1 " +
                "INNER JOIN sys_hr t2 ON t2.hrid=t1.hrid and t2.siteid=t1.siteid " +
                "INNER JOIN sys_datateam t3 ON t3.userid=t2.userid and t3.ownertable='sa_hospitaldep'  and t3.siteid=t2.siteid " +
                "WHERE sa_agentsid=" + sa_agentsid + ")");
        where.append(" and t1.sa_hospitaldepid not in (SELECT sa_hospitaldepid from sa_agents_hospital " +
                "WHERE sa_agentsid=" + sa_agentsid + ")");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_hospitaldep", "*").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid and t3.siteid=t2.siteid", "enterprisename");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_hospitaldepid", new ArrayList<>());
        RowsMap leaderRows = DataTeam.getLeader(this, "sa_hospitaldep", rows.toArrayList("sa_hospitaldepid")).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_hospitaldep", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_hospitaldep", ids, true);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        RowsMap doctorRowsMap = CommonHepler.getDoctorRowsMap(this, ids);
        for (Row row : rows) {
            Long id = row.getLong("sa_hospitaldepid");
            row.put("leader", leaderRows.get(row.getString("sa_hospitaldepid")));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            //科室负责人
            Rows doctorRows = doctorRowsMap.getOrDefault(id.toString(), new Rows());
            row.putIfAbsent("doctors", StringUtils.join(doctorRows.toArray("doctorname"), ","));
            String stagename = row.getString("stagename");
            for (Row stageRow : stageRows) {
                if (stagename.equals(stageRow.getString("stagename"))) {
                    stageRow.put("active", 1);
                }
                stageRow.putIfAbsent("active", 0);
            }
            row.put("stages", stageRows);
        }

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


}