表单查询sql

最新推荐文章于 2024-07-24 16:47:58 发布

阿花落知多少

最新推荐文章于 2024-07-24 16:47:58 发布

阅读量649

点赞数

本文链接：https://blog.csdn.net/qq_39940205/article/details/84633498

版权

 SELECT
        a.order_no AS 'orderId',
        a.third_merchant_id AS "supplierId",
        si.seller_name AS "suppliers",
        a.`member_name` AS 'memberId',
        CASE
        WHEN a.ship_mobile IS NOT NULL AND a.ship_mobile != '' THEN a.ship_mobile
        ELSE a.ship_tel
        END AS 'shipTel',
        a.order_createtime AS 'createtime',
        a.payment AS 'payment',
        a.payable_amount AS 'payableAmount',
        a.payed AS 'payed',
        a.discount AS 'discount',
        a.pmt_order as pmtOrder,
        a.pmt_goods as pmtGoods,
        CASE  a.production_status
        WHEN '1'
        THEN '待审核'
        WHEN '2'
        THEN '待分配'
        WHEN '3'
        THEN '生产中'
        WHEN '4'
        THEN '生产完成'
        WHEN '5'
        THEN '已发货'
        WHEN '6'
        THEN '已完成'
        WHEN '7'
        THEN '新开单(门店)'
        WHEN '11'
        THEN '已作废'
        END AS 'productionStatus',
        a.payment_time AS 'paymentTime',
        a.project_name AS "projectName",
        a.memo AS "memo",
        a.production_request as 'productionRequest',
        (SELECT GROUP_CONCAT(`addon`) FROM `erp_order_item` WHERE order_no=a.order_no) AS addon
        FROM
        erp_order a
        LEFT JOIN seller_info si ON a.third_merchant_id = si.seller_id
        WHERE a.third_merchant_id=#{map.sellerId} and a.production_status !=11
        <if test="map.orderId != null and map.orderId != ''">
            and a.order_no LIKE concat('%', #{map.orderId}, '%')
        </if>
        <if test="map.name != null and map.name != ''">
            and a.`member_name` LIKE concat('%', #{map.name}, '%')
        </if>
        <if test="map.payment != null and map.payment != ''">
            and a.payment=#{map.payment}
        </if>
        <if test="map.payStatus != null and map.payStatus != ''">
            and a.pay_status=#{map.payStatus}
        </if>
        <if test="map.productionStatus != null and map.productionStatus != ''">
            and a.production_status=#{map.productionStatus}
        </if>
        <if test="map.beginCreatetime != null and map.beginCreatetime != ''">
            and DATE_FORMAT(a.order_createtime, '%Y-%m-%d %H:%i:%S') &gt;= DATE_FORMAT(#{map.beginCreatetime}, '%Y-%m-%d %H:%i:%S')
        </if>
        <if test="map.endCreatetime != null and map.endCreatetime != ''">
            and DATE_FORMAT(a.order_createtime, '%Y-%m-%d %H:%i:%S') &lt;= DATE_FORMAT(#{map.endCreatetime}, '%Y-%m-%d %H:%i:%S')
        </if>
        <if test="map.beginPaytime != null and map.beginPaytime != ''">
            and DATE_FORMAT(a.payment_time, '%Y-%m-%d %H:%i:%S') &gt;= DATE_FORMAT(#{map.beginPaytime}, '%Y-%m-%d %H:%i:%S')
        </if>
        <if test="map.endPaytime != null and map.endPaytime != ''">
            and DATE_FORMAT(a.payment_time, '%Y-%m-%d %H:%i:%S') &lt;= DATE_FORMAT(#{map.endPaytime}, '%Y-%m-%d %H:%i:%S')
        </if>
        ORDER BY a.order_createtime DESC