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

package restcontroller.sale.order;

import com.alibaba.fastjson.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 {
        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 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 like'%").append(whereObject.getString("status")).append("%' ");
                where.append(")");
            }
            //品牌
            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 DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
        boolean isExport = content.getBooleanValue("isExport");
        QuerySQL querySQL = queryList(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber);
        }
        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");
        //查询收货人信息
        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 (isExport) {
            //去除不需要导出项
            rows.getFieldList().remove("sa_orderid");
            rows.getFieldList().remove("submitdate");
            rows.getFieldList().remove("checkdate");
            rows.getFieldList().remove("typemx");
            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 (systemclient.equalsIgnoreCase("web")) {
            return getSucReturnObject().setData(rows).toString();
        }

        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");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_brand", "t3", "t3.sa_brandid = t1.sa_brandid",
                "brandname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t4", "t4.contactsid = t1.rec_contactsid and t4.siteid = t1.siteid",
                "name");
        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;
    }
}