YOS_PRSX/src/custom/restcontroller/webmanage/sale/order/Order2.java

package restcontroller.webmanage.sale.order;

import beans.CommonHepler;
import beans.accountbalance.Accountbalance;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.enterprise.Enterprise;
import beans.proxy.Proxy;
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.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
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.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

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

    @API(title = "订单管理", apiversion = R.ID20230105143002.v1.class)
    public String selectListManage() throws YosException, IOException {
        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.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                JSONArray array = whereObject.getJSONArray("status");
                if (array.size() > 0) {
                    String sql = " and(t1.status in" + array.toJSONString() + ")";
                    sql = sql.replace("[", "(").replace("]", ")");
                    where.append(sql);
                }
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("shoppingtype") && !"".equals(whereObject.getString("shoppingtype"))) {
                where.append(" and(");
                where.append("t1.shoppingtype like'%").append(whereObject.getString("shoppingtype")).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(" 23:59:59' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and t1.checkdate >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and t1.checkdate <='").append(whereObject.getString("enddate2")).append(" 23:59:59' ");
            }
            if (whereObject.containsKey("saler_name") && !"".equals(whereObject.getString("saler_name"))) {
                where.append(" and(");
                where.append("t6.name like'%").append(whereObject.getString("saler_name")).append("%' ");
                where.append("or t6.hrcode like'%").append(whereObject.getString("saler_name")).append("%' ");
                where.append(")");
            }
        }
        boolean isExport = content.getBooleanValue("isExport");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                        "sa_orderid", "sonum", "type", "status", "shoppingtype", "checkdate", "remarks", "billdate", "createdate")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid",
                "sa_customersid");
        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",
                "sa_agentsid");
        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, getAmountQuerySQL(), "t7", "t7.sa_orderid = t1.sa_orderid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t8", "t8.sa_saleareaid = t1.sa_saleareaid and t8.siteid = t1.siteid",
                "areaname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t9", "t9.contactsid = t1.rec_contactsid and t9.siteid = t1.siteid",
                "name", "province", "city", "county", "address");

        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salename", "t6.name");
        querySQL.addQueryFields("qty", "ifnull(t7.qty, 0)");
        querySQL.addQueryFields("amount", "ifnull(t7.amount, 0)");
        querySQL.addQueryFields("contactname", "ifnull(t9.name, 0)");

        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("t1.deleted = 0");
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        }

        Rows rows = querySQL.query();


        for (Row row : rows) {
            row.putIfAbsent("checkdate", "");
        }

        if (isExport) {
            //去除不需要导出项
            rows.getFieldList().remove("sa_orderid");
            rows.getFieldList().remove("submitdate");
            rows.getFieldList().remove("checkdate");
            rows.getFieldList().remove("sys_enterpriseid");
            //添加
            rows.getFieldList().add("amount");
            rows.getFieldList().add("qty");
            Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }

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

    @API(title = "订货业务", apiversion = R.ID20221111145202.v1.class)
    public String selectList() throws YosException, IOException {
        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.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                JSONArray array = whereObject.getJSONArray("status");
                if (array.size() > 0) {
                    String sql = " and(t1.status in" + array.toJSONString() + ")";
                    sql = sql.replace("[", "(").replace("]", ")");
                    where.append(sql);
                }
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("shoppingtype") && !"".equals(whereObject.getString("shoppingtype"))) {
                where.append(" and(");
                where.append("t1.shoppingtype like'%").append(whereObject.getString("shoppingtype")).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(" 23:59:59' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and t1.checkdate >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and t1.checkdate <='").append(whereObject.getString("enddate2")).append(" 23:59:59' ");
            }
            if (whereObject.containsKey("saler_name") && !"".equals(whereObject.getString("saler_name"))) {
                where.append(" and(");
                where.append("t6.name like'%").append(whereObject.getString("saler_name")).append("%' ");
                where.append("or t6.hrcode like'%").append(whereObject.getString("saler_name")).append("%' ");
                where.append(")");
            }
        }

        ArrayList<Long> userids = new ArrayList<>();
        userids.add(userid);
        //下级区域
        userids.addAll(DataTeam.getSubAreaUserIds(this));
        //获取代理
        userids.addAll(Proxy.getProxyUserids(this));

        QuerySQL agentQuerySQL = SQLFactory.createQuerySQL(this, "sys_enterprise_tradefield", "sa_agentsid").setTableAlias("t1");
        agentQuerySQL.addJoinTable(JOINTYPE.inner, "sys_hr", "t2", "t2.siteid=t1.siteid AND t2.hrid=t1.hrid");
        agentQuerySQL.setSiteid(siteid);
        agentQuerySQL.setWhere("t2.userid", userids);

        QuerySQL customerQuerySQL = SQLFactory.createQuerySQL(this, "sys_datateam", "ownerid");
        customerQuerySQL.setSiteid(siteid);
        customerQuerySQL.setWhere("ownertable", "sa_customers");
        customerQuerySQL.setWhere("userid", userids);

        String sql = " and ( t1.sa_customersid in (" + customerQuerySQL.getSQL() + ") or t1.sa_agentsid in (" + agentQuerySQL.getSQL() + "))";

        where.append(sql);


        boolean isExport = content.getBooleanValue("isExport");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                        "sa_orderid", "sonum", "type", "status", "shoppingtype", "checkdate", "remarks", "billdate", "createdate")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid",
                "sa_customersid");
        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",
                "sa_agentsid");
        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, getAmountQuerySQL(), "t7", "t7.sa_orderid = t1.sa_orderid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t8", "t8.sa_saleareaid = t1.sa_saleareaid and t8.siteid = t1.siteid",
                "areaname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t9", "t9.contactsid = t1.rec_contactsid and t9.siteid = t1.siteid",
                "name", "province", "city", "county", "address");

        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salename", "t6.name");
        querySQL.addQueryFields("qty", "ifnull(t7.qty, 0)");
        querySQL.addQueryFields("amount", "ifnull(t7.amount, 0)");
        querySQL.addQueryFields("contactname", "ifnull(t9.name, 0)");

        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setRoleDataLimit(false);
        querySQL.setWhere("t1.deleted = 0");
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        }

        Rows rows = querySQL.query();


        for (Row row : rows) {
            row.putIfAbsent("checkdate", "");
        }

        if (isExport) {
            //去除不需要导出项
            rows.getFieldList().remove("sa_orderid");
            rows.getFieldList().remove("submitdate");
            rows.getFieldList().remove("checkdate");
            rows.getFieldList().remove("sys_enterpriseid");
            //添加
            rows.getFieldList().add("amount");
            rows.getFieldList().add("qty");
            Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }
        if (content.containsKey("systemclient")) {
            systemclient = content.getStringValue("systemclient");
        }
        if (systemclient.equalsIgnoreCase("web")) {
            return getSucReturnObject().setData(rows).toString();
        }

        ArrayList<Long> sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>());
        sa_orderids.add(-1l);
        SQLFactory sqlFactory = new SQLFactory(this, "订单-查询筛选出的价格");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderid", sa_orderids);
        Rows amountrows = dbConnect.runSqlQuery(sqlFactory);

        return getSucReturnObject().setData(rows).setTips(amountrows.get(0).toJsonObject()).toString();
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("sonum", "订单编号");
        titleMap.put("brandname", "品牌名称");
        titleMap.put("type", "订单类型");
        titleMap.put("tradefield", "领域");
        titleMap.put("remarks", "备注");
        titleMap.put("status", "状态");
        titleMap.put("createdate", "创建时间");
        titleMap.put("enterprisename", "经销商名称");
        titleMap.put("abbreviation", "简称");
        titleMap.put("name", "收货人");
        titleMap.put("amount", "订单金额");
        titleMap.put("qty", "订单数量");

        return titleMap;
    }

    public QuerySQL getAmountQuerySQL() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems",
                "sa_orderid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_order", "b", "b.sa_orderid = a.sa_orderid AND b.siteid = a.siteid ");
        querySQL.addQueryFields("amount", "sum(amount)");
        querySQL.addQueryFields("qty", "sum(qty)");
        querySQL.setWhere("a.siteid", siteid);
        querySQL.setWhere("b.deleted = 0 ");
        querySQL.addGroupBy("a.sa_orderid").setTableAlias("a");
        return querySQL;
    }

    @API(title = "新增或更新", apiversion = R.ID20221108111402.v1.class, intervaltime = 2000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String insertOrUpdate() throws YosException {

        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_orderid = content.getLongValue("sa_orderid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");
        String type = sa_customersid > 0 ? "医院订单" : "经销商订单";
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");
        Long saler_hrid = content.getLong("saler_hrid");
        if (type.equals("医院订单")) {
            Rows rows = dbConnect.runSqlQuery("SELECT * from sa_customers where sa_customersid=" + sa_customersid);
            if (rows.isNotEmpty()) {
                sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
                sa_saleareaid = rows.get(0).getLong("sa_saleareaid");

            }
            rows = dbConnect.runSqlQuery("SELECT t2.hrid FROM sys_datateam t1 " +
                    "INNER JOIN sys_hr t2 ON t2.userid=t1.userid AND t2.siteid=t1.siteid " +
                    "WHERE ownertable='sa_customers' AND t1.siteid='" + siteid + "' AND t1.isleader=1 AND ownerid='" + sa_customersid + "'");
            if (rows.isNotEmpty()) {
                saler_hrid = rows.get(0).getLong("hrid");
            }
        }
        if (type.equals("经销商订单")) {
            Rows rows = dbConnect.runSqlQuery("SELECT * from sa_agents where sa_agentsid=" + sa_agentsid);
            if (rows.isNotEmpty()) {
                sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
            }
        }
        Long rec_contactsid = content.getLongValue("rec_contactsid");
        if (rec_contactsid <= 0) {
            rec_contactsid = getDefaultContactsid(sys_enterpriseid);
        }
        String billdate = content.getStringValueForDate("billdate", "yyyy-MM-dd", "null");
        if (billdate.equals("null")) {
            billdate = getDate_Str();
        }

        if (sa_orderid <= 0) {
            sa_orderid = createTableID("sa_order");
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_order");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(sa_orderid);
            insertSQL.setValue("sonum", createBillCode("orderbill"));
            insertSQL.setValue("sa_customersid", sa_customersid);
            insertSQL.setValue("sa_agentsid", sa_agentsid);
            insertSQL.setValue("shoppingtype", content.getStringValue("shoppingtype"));
            insertSQL.setValue("sa_saleareaid", sa_saleareaid);
            insertSQL.setValue("deliverydate", content.getStringValueForDate("deliverydate", "yyyy-MM-dd", "null"));
            insertSQL.setValue("saler_hrid", saler_hrid);
            insertSQL.setValue("billdate", billdate);
            insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
            insertSQL.setValue("sa_contractid", content.getLongValue("sa_contractid"));
            insertSQL.setValue("remarks", content.getStringValue("remarks"));
            insertSQL.setValue("status", "新建");
            insertSQL.setValue("type", type);
            insertSQL.setValue("rec_contactsid", rec_contactsid);
            insertSQL.setValue("sa_hospitaldepid",  content.getLongValue("sa_hospitaldepid"));
            sqlList.add(insertSQL.getSQL());
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新建", "新建订单成功").getSQL());
        } else {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_order");
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(sa_orderid);
            updateSQL.setValue("shoppingtype", content.getStringValue("shoppingtype"));
            updateSQL.setValue("deliverydate", content.getStringValueForDate("deliverydate", "yyyy-MM-dd", "null"));
            updateSQL.setValue("billdate", billdate);
            updateSQL.setValue("remarks", content.getStringValue("remarks"));
            updateSQL.setValue("rec_contactsid", rec_contactsid);
            updateSQL.setValue("sa_hospitaldepid",  content.getLongValue("sa_hospitaldepid"));
            sqlList.add(updateSQL.getSQL());
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "编辑", "编辑订单成功").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_order WHERE sa_orderid=" + sa_orderid);

        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Row()).toString();
    }

    public Long getDefaultContactsid(Long sys_enterpriseid) throws YosException {
        Long rec_contactsid = 0L;
        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商默认联系人");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_in("sys_enterpriseids", Enterprise.getUpDown(this, sys_enterpriseid));
        String sql = sqlFactory.getSQL(false);
        Rows contactsRows = dbConnect.runSqlQuery(sql);
        if (contactsRows.isNotEmpty()) {
            rec_contactsid = contactsRows.get(0).getLong("contactsid");
        }
        return rec_contactsid;
    }

    @API(title = "详情", apiversion = R.ID20221108151302.v1.class)
    public String selectDetail() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order", "*")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t2", "t2.sa_customersid=t1.sa_customersid and t2.siteid=t1.siteid",
                "sa_customersid", "billno");
        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",
                "sa_agentsid", "agentnum");
        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, getAmountQuerySQL(), "t7", "t7.sa_orderid = t1.sa_orderid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t8", "t8.sa_saleareaid = t1.sa_saleareaid and t8.siteid = t1.siteid",
                "areaname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t9", "t9.contactsid = t1.rec_contactsid and t9.siteid = t1.siteid",
                "name", "province", "city", "county", "address");
        querySQL.addJoinTable(JOINTYPE.left, "sa_contract", "t10", "t10.sa_contractid = t1.sa_contractid and t10.siteid = t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_hospitaldep", "t11", "t11.sa_hospitaldepid = t1.sa_hospitaldepid and t11.siteid = t1.siteid","hospitaldepname");

        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salename", "t6.name");
        querySQL.addQueryFields("qty", "ifnull(t7.qty, 0)");
        querySQL.addQueryFields("amount", "ifnull(t7.amount, 0)");
        querySQL.addQueryFields("contactname", "ifnull(t9.name, 0)");
        querySQL.addQueryFields("contract_billno", "t10.billno");
        querySQL.setUniqueid(sa_orderid);
        querySQL.setSiteid(siteid);

        Rows rows = querySQL.query();


        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        //收货信息
        row.put("contacts", beans.order.Order.getContactsRow(this, row.getLong("rec_contactsid")));
        //收货信息
        row.put("contactsEnterprise", beans.order.Order.getContactsEnterpriseRow(this, row.getLong("rec_contactsid")));


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

    @API(title = "订单中的选择医院列表", apiversion = R.ID2025102814083902.v1.class)
    public String hospitalList() throws YosException {

        String tablename = "sa_customers";
        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.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 t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.areaname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t8.depname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        where.append(" and ( t1.datastatus in (0,2) )");


        //当前账号负责的、参与的，以及代理账号负责的医院
        String where2 = "(" + DataTeam.getDataWhereStr(this, tablename, "t1", 8) + ")";


        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.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.addJoinTable(JOINTYPE.left, "sys_dataextend", "t10", "t1.siteid = t10.siteid and t1.sa_customersid = t10.ownerid and t10.ownertable = 'sa_customers'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere(where2);
        querySQL.setPage(pageSize, pageNumber);

        if (pageSorting.equals("''")) {
            pageSorting = "t1.createdate desc";
        }
        querySQL.setOrderBy(pageSorting);

        querySQL.setRoleDataLimit(false);
        querySQL.withDeleteData(true);
        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, tablename, ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, tablename, ids, true);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        RowsMap AgentRowsMap = CommonHepler.getAgentRowsMap(this, ids);
        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"), ","));
            //签约经销商
            Rows signagent = AgentRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("signagent", StringUtils.join(signagent.toArray("enterprisename"), ","));
            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.ID2025102814092602.v1.class)
    public String agentsList() 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.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.address like'%").append(whereObject.getString("condition")).append("%' ");
//                where.append("or t3.erpagentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        where.append(" and t1.status ='启用' ");

        Long sa_customersid = content.getLongValue("sa_customersid");
        if (sa_customersid == 0) {
            //当前账号授权的，以及代理账号授权的经销商。
            ArrayList<Long> hrids = Proxy.getProxyHrids(this);
            hrids.add(hrid);
            String sql = " and (t1.sa_agentsid IN (SELECT sa_agentsid FROM sys_enterprise_tradefield WHERE hrid IN " + hrids + "))";
            sql = sql.replace("]", ")").replace("[", "(");
            where.append(sql);

        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents",
                        "sa_agentsid", "sys_enterpriseid", "cooperatetype").
                setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename", "province", "city", "county", "address");

        if (sa_customersid > 0) {
            querySQL.setWhere("t1.sa_agentsid in (SELECT sa_agentsid from sa_agents_hospital WHERE sa_customersid=" + sa_customersid + " and siteid='" + siteid + "')");
        }
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.sa_agentsid in (SELECT sa_agentsid from sys_enterprise_tradefield WHERE siteid='"+siteid+"')");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy("t1.createdate desc");
        Rows rows = querySQL.query();

        RowsMap areaRowsMap = CommonHepler.getAreaRowsMap(this, rows.toArrayList("sa_agentsid", new ArrayList<>()));
        for (Row row : rows) {
            Rows areaRows = areaRowsMap.getOrDefault(row.getString("sa_agentsid"), new Rows());
            row.put("areanames", StringUtils.join(areaRows.toArrayList("areaname"), ","));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "合同列表", apiversion = R.ID2025102814112602.v1.class)
    public String contractList() 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_customersid = content.getLongValue("sa_customersid");
        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.setSiteid(siteid);
        querySQL.setWhere(where);
        if (sa_customersid > 0) {
            querySQL.setWhere("t2.sa_customersid", sa_customersid);
            querySQL.setWhere("t1.type='医院协议'");
        } else {
            querySQL.setWhere("t4.sa_agentsid", sa_agentsid);
            querySQL.setWhere("t1.type='经销商协议'");
        }
        querySQL.addQueryFields("hospitalname", "t3.enterprisename");
        querySQL.addQueryFields("agentname", "t5.enterprisename");
        querySQL.addQueryFields("salername", "t6.name");
        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.ID2025102815162702.v1.class)
    public String chooseSalers() throws YosException {

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


        QuerySQL querySQ = SQLFactory.createQuerySQL(this, "sys_enterprise_tradefield", "*")
                .setTableAlias("t1");
        querySQ.addJoinTable(JOINTYPE.left, "sys_hr", "t2", "t2.hrid=t1.hrid and t2.siteid=t1.siteid",
                "name", "position", "hrcode", "phonenumber","userid");
        querySQ.addJoinTable(JOINTYPE.left, "sa_salearea", "t3", "t3.sa_saleareaid=t1.sa_saleareaid and t3.siteid=t1.siteid",
                "areaname");
        querySQ.addJoinTable(JOINTYPE.left, "sys_department", "t4", "t4.departmentid=t2.departmentid and t4.siteid=t2.siteid",
                "depname");
        querySQ.setSiteid(siteid);
        querySQ.setWhere("sa_agentsid", sa_agentsid);
        querySQ.setPage(pageSize, pageNumber);
        Rows rows = querySQ.query();

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


}