md_YOS/src/custom/restcontroller/sale/order/Order.java

package restcontroller.sale.order;

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.data.*;
import restcontroller.R;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

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

    @API(title = "查询订单列表(经销商)", apiversion = R.ID20221224180302.v1.class)
    public String selectList() throws YosException, IOException {
        ArrayList<String> statusList = new ArrayList<>();
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                JSONArray statusArray = whereObject.getJSONArray("status");
                for (int i = 0; i < statusArray.size(); i++) {
                    statusList.add(statusArray.getString(i));
                }
            }
            //品牌
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname like'%").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("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
        boolean isExport = content.getBooleanValue("isExport");
        QuerySQL querySQL = queryList(where.toString());
        if (!statusList.isEmpty()) {
            querySQL.setWhere("t1.status", statusList);
        }
        querySQL.setOrderBy("t1.sa_orderid desc");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


        //查询数据：amount，defaultamount，qty
        ArrayList<Long> sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>());
        SQLFactory sqlFactory = new SQLFactory(this, "订单_查询数据");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderid", sa_orderids);
        RowsMap dataRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_orderid");

        RowsMap expressformrowsMap = new RowsMap();
        if (!sa_orderids.isEmpty()) {
            QuerySQL expressformQuery = SQLFactory.createQuerySQL(this, "expressform", "*").setTableAlias("t1");
            expressformQuery.addJoinTable(JOINTYPE.inner, "sa_expressform_stockbill", "t2", "t2.expressformid = t1.expressformid");
            expressformQuery.addJoinTable(JOINTYPE.inner, "st_stockbill_items", "t3", "t3.st_stockbillid = t2.st_stockbillid", "sa_orderid");
            expressformQuery.addJoinTable(JOINTYPE.inner, "st_stockbill", "t4", "t3.st_stockbillid = t4.st_stockbillid", "tracknumber");
            expressformQuery.setWhere("t3.sa_orderid", sa_orderids);
            expressformQuery.setOrderBy("t1.createdate desc");
            expressformQuery.setDistinct(true);
            expressformrowsMap = expressformQuery.query().toRowsMap("sa_orderid");
        }

        SQLFactory sqlFactory2 = new SQLFactory(this, "查询最新的快递信息");
        sqlFactory2.addParameter_in("sa_orderid",sa_orderids);
        RowsMap expressformrowsMap2=dbConnect.runSqlQuery(sqlFactory2).toRowsMap("sa_orderid");

        RowsMap ContactsRowsMap = beans.order.Order.getContactsRowsMap(this, rows.toArrayList("rec_contactsid", new ArrayList<>()));
        for (Row row : rows) {
            Rows dataRows = dataRowsMap.getOrDefault(row.getString("sa_orderid"), new Rows());
            if (dataRows.isNotEmpty()) {
                row.putAll(dataRows.get(0));
            } else {
                row.put("amount", 0);
                row.put("qty", 0);
            }
            //查询收货人信息
            if (ContactsRowsMap.containsKey(row.getString("rec_contactsid"))) {
                row.put("contacts", ContactsRowsMap.get(row.getString("rec_contactsid")).get(0));
            } else {
                row.put("contacts", new Row());
            }
            //查询快递信息
            if (expressformrowsMap2.containsKey(row.getString("sa_orderid"))) {
                row.put("expressform_mailno", expressformrowsMap2.get(row.getString("sa_orderid")).get(0).getString("mailno"));
                row.put("expressform_txlogisticid", expressformrowsMap2.get(row.getString("sa_orderid")).get(0).getString("tracknumber"));

            } else {
                row.put("expressform_mailno", "");
                row.put("expressform_txlogisticid", "");

            }
        }


        sqlFactory = new SQLFactory(this, "订单-查询筛选出的价格");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);

        Rows amountrows = dbConnect.runSqlQuery(sqlFactory);


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

    //查询订单列表(经销商)
    public QuerySQL queryList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                "sa_orderid", "sonum", "status", "type", "billdate", "tradefield", "remarks", "rec_contactsid", "checkdate", "closedate", "invoicetaxno", "invoicename", "invoiceaddress", "contact", "address", "phonenumber", "province", "city", "county");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_brand", "t3", "t3.sa_brandid = t1.sa_brandid",
                "brandname");

        querySQL.setCondition("t1.sonum", "t1.remarks");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.sys_enterpriseid = $sys_enterpriseid$");
        querySQL.setWhere("t1.deleted=0");
        querySQL.setWhere("((t1.createflag=1 and t1.status!='新建') or t1.createflag=0)");
        querySQL.addParameter("sys_enterpriseid", sys_enterpriseid);
        return querySQL;
    }

    @API(title = "获取当前经销商的所有订单的金额", apiversion = R.ID20221227161902.v1.class)
    @CACHEING
    public String getAmount() throws YosException {

        Rows rows = dbConnect.runSqlQuery("SELECT sum(amount) amount from sa_orderitems  WHERE sa_orderid in (SELECT sa_orderid from sa_order WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "') and siteid='" + siteid + "'");

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

    @API(title = "获取订单的金额", apiversion = R.ID20230105101102.v1.class)
    @CACHEING
    public String getOrderAmount() throws YosException {

        Long sa_orderid = content.getLong("sa_orderid");

        Rows rows = dbConnect.runSqlQuery("SELECT sum(amount) amount from sa_orderitems  WHERE sa_orderid = '" + sa_orderid + "'  and siteid='" + siteid + "'");

        return getSucReturnObject().setData(rows.get(0)).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("name", "收货人");
        titleMap.put("amount", "订单金额");
        titleMap.put("qty", "订单数量");

        return titleMap;
    }
}