订单数量优化

yujian

2019-01-11 824919253fe0570251feddb888cfb2437e5812a5

订单数量优化

 fanli/src/main/java/com/yeshi/fanli/dao/mybatis/order/CommonOrderMapper.java

@@ -113,5 +113,33 @@
     */
    CommonOrder selectByOrderNoAndOrderTypeAndOrderBy(@Param("orderNo") String orderNo,
            @Param("orderType") int orderType, @Param("orderBy") int orderBy);
	
	
    /**
     * 订单列表查询
     * @param start
     * @param count
     * @param uid
     * @param state
     * @param type
     * @param orderState
     * @param startTime
     * @param endTime
     * @return
     */
    List<CommonOrderVO> listQueryByUid(@Param("start") long start, @Param("count") int count, 
            @Param("uid") Long uid, @Param("state") Integer state, @Param("type") Integer type, 
            @Param("orderState") Integer orderState, @Param("orderNo") String orderNo,
            @Param("startTime") String startTime, @Param("endTime") String endTime,
            @Param("day") Integer day);

    /**
     * 统计
     */
    long countQueryByUid(@Param("uid") Long uid, @Param("state") Integer state, @Param("type") Integer type, 
            @Param("orderState") Integer orderState, @Param("orderNo") String orderNo,
            @Param("startTime") String startTime, @Param("endTime") String endTime,
            @Param("day") Integer day);


}

 fanli/src/main/java/com/yeshi/fanli/mapping/order/CommonOrderMapper.xml

@@ -243,7 +243,7 @@
    <sql id="SELECT_PARAM_ORDER_STATE">
        <if test="orderState != null and orderState ==  1">
            <!-- 有效订单: 整个订单有效、 部分订单有效 -->
            AND (co.co_state_whole_order = 1 or co.co_state_whole_order = 2)  
            AND <![CDATA[co.`co_state` <> 3]]> AND (co.co_state_whole_order = 1 or co.co_state_whole_order = 2)  
        </if>
        <if test="orderState != null and orderState ==  2">
            AND co.co_state = 3 <!-- 维权订单 -->
@@ -496,7 +496,8 @@
            
            LEFT JOIN yeshi_ec_common_order co ON co.`co_id`=ho.`ho_order_id`
            WHERE (hb.hb_id IS NOT NULL OR hbp.hb_id IS NOT NULL)
                AND (co.`co_state_whole_order` =1 OR  co.`co_state_whole_order` = 2) 
                  <![CDATA[AND co.`co_state` <> 3]]>
                  AND (co.`co_state_whole_order` =1 OR co.`co_state_whole_order` = 2) 
                <include refid="SELECT_PARAM_ORDER_CREATE_TIME" />
            GROUP BY co.`co_order_no`, co.`co_source_type`)YX
            
@@ -565,16 +566,18 @@
    </select>

    <select id="countBonusOrderMoney" resultType="java.math.BigDecimal">
        <!-- 奖金统计：订单总返利金额 -->
        <!-- 奖金统计：订单总返利金额 去掉已失效 -->
        SELECT COALESCE(SUM(IF(hb.hb_id IS NULL,hbp.`hb_money`,hb.`hb_money`)),0)AS totalmoney
        FROM yeshi_ec_hongbao_order ho     
        LEFT JOIN ( 
            SELECT * FROM yeshi_ec_hongbao_v2 v2 
            WHERE v2.`hb_uid`=${uid} <include refid="SELECT_PARAM_HONGBAO_TYPE" />
                  AND (v2.hb_state = 1 or v2.hb_state = 2 or v2.hb_state = 3)
        ) hb  ON hb.hb_id=ho.`ho_hongbao_id`
        LEFT JOIN ( 
            SELECT * FROM yeshi_ec_hongbao_v2 v2 
            WHERE v2.`hb_uid`=${uid} <include refid="SELECT_PARAM_HONGBAO_TYPE" />
            AND (v2.hb_state = 1 or v2.hb_state = 2 or v2.hb_state = 3)
        ) hbp  ON hbp.hb_pid=ho.`ho_hongbao_id`
        LEFT JOIN yeshi_ec_common_order co ON co.`co_id`=ho.`ho_order_id`
        WHERE (hb.hb_id IS NOT NULL OR hbp.hb_id IS NOT NULL)
@@ -583,5 +586,64 @@
                AND co.`co_state` = 2 <!-- 上月 :统计已收货 -->
            </if>
    </select>
	
    <select id="listQueryByUid" resultMap="ResultMap">   
        <!-- 查询用户订单 -->
        SELECT  IF(hb.hb_id IS NULL,hbp.`hb_money`,hb.`hb_money`) AS totalMoney,
                IF(hb.hb_id IS NULL,hbp.`hb_state`,hb.hb_state) AS hongBaoState,
                IF(hb.hb_id IS NULL,hbp.`hb_type`,hb.`hb_type`) AS hongBaoType,
                IF(hb.hb_id IS NULL,hbp.`hb_get_time`,hb.`hb_get_time`)  AS accountTime,
                IF(hb.hb_id IS NULL,hbp.`hb_pre_get_time`,hb.`hb_pre_get_time`) AS preAccountTime,
                co.*
        FROM yeshi_ec_hongbao_order ho 
		
        LEFT JOIN (SELECT * FROM yeshi_ec_hongbao_v2 v2 
                    WHERE v2.`hb_uid`= #{uid} 
                        <include refid="SELECT_PARAM_HONGBAO_TYPE"/>  <!-- 红包类型 -->
                        <include refid="SELECT_PARAM_HONGBAO_STATE"/> <!-- 红包状态 -->
                )hb ON hb.hb_id=ho.`ho_hongbao_id`
				
        LEFT JOIN (SELECT * FROM yeshi_ec_hongbao_v2 v2 
                    WHERE v2.`hb_uid`= #{uid} 
                        <include refid="SELECT_PARAM_HONGBAO_TYPE"/>  <!-- 红包类型 -->
                        <include refid="SELECT_PARAM_HONGBAO_STATE"/> <!-- 红包状态 -->
                )hbp ON  hbp.hb_pid=ho.`ho_hongbao_id`
				
        LEFT JOIN yeshi_ec_common_order co ON co.`co_id`=ho.`ho_order_id`
        WHERE (hb.hb_id IS NOT NULL OR hbp.hb_id IS NOT NULL)
              <include refid="SELECT_PARAM_ORDER_STATE"/>        <!-- 订单状态 -->
              <include refid="SELECT_PARAM_ORDER_CREATE_TIME"/>  <!-- 订单时间 -->
              <if test="orderNo != null and orderNo !='' ">
                AND co.co_order_no = #{orderNo}
              </if>
        ORDER BY co.co_create_time DESC
        LIMIT ${start},${count}
    </select>

    <select id="countQueryByUid" resultType="java.lang.Long">   
        <!-- 统计用户订单数量 -->
        SELECT IFNULL(COUNT(ho_id),0) FROM 
            (SELECT ho.ho_id FROM yeshi_ec_hongbao_order ho 
             LEFT JOIN (SELECT * FROM yeshi_ec_hongbao_v2 v2 
                    WHERE v2.`hb_uid`= #{uid} 
                        <include refid="SELECT_PARAM_HONGBAO_TYPE"/>  <!-- 红包类型 -->
                        <include refid="SELECT_PARAM_HONGBAO_STATE"/> <!-- 红包状态 -->
                )hb ON hb.hb_id=ho.`ho_hongbao_id`
				
            LEFT JOIN (SELECT * FROM yeshi_ec_hongbao_v2 v2 
                    WHERE v2.`hb_uid`= #{uid} 
                        <include refid="SELECT_PARAM_HONGBAO_TYPE"/>  <!-- 红包类型 -->
                        <include refid="SELECT_PARAM_HONGBAO_STATE"/> <!-- 红包状态 -->
                )hbp ON hbp.hb_pid=ho.`ho_hongbao_id`
				
              LEFT JOIN yeshi_ec_common_order co ON co.`co_id`=ho.`ho_order_id`
             WHERE (hb.hb_id IS NOT NULL OR hbp.hb_id IS NOT NULL)
                     <include refid="SELECT_PARAM_ORDER_STATE"/>        <!-- 订单状态 -->
                   <include refid="SELECT_PARAM_ORDER_CREATE_TIME"/>  <!-- 订单时间 -->
                   <if test="orderNo != null and orderNo !='' ">
                        AND co.co_order_no = #{orderNo}
                   </if>
        )A
    </select>

</mapper>

 fanli/src/main/java/com/yeshi/fanli/service/impl/order/CommonOrderServiceImpl.java

@@ -455,7 +455,68 @@


    @Override

    public long countByUidAndOrderStateWithOrderBalanceTime(Long uid, int state, Date minDate, Date maxDate) {



        return commonOrderMapper.countByUidAndOrderStateWithOrderBalanceTime(uid, state, minDate, maxDate);

    }

	
	
    @Override

    public List<CommonOrderVO> listQueryByUid(long start, int count, Long uid, Integer state, Integer type,

            Integer orderState, String orderNo, String startTime, String endTime, Integer dateType)

            throws CommonOrderException {

		
			
        List<CommonOrderVO> list = commonOrderMapper.listQueryByUid(start, count, uid, state, type, 
                orderState, orderNo, startTime, endTime, dateType);

		
        if (list == null) {

            list = new ArrayList<CommonOrderVO>();

        }

		
        if (list.size() == 0) {

            return list;

        }



        for (CommonOrderVO order : list) {



            /* 订单返利类型 转换 */

            Integer hongBaoType = order.getHongBaoType();

            if (HongBaoV2.TYPE_ZIGOU == hongBaoType || 2 == hongBaoType) {

                // 自购

                order.setHongBaoType(1);

            } else if (HongBaoV2.TYPE_SHARE_GOODS == hongBaoType) {

                // 分享

                order.setHongBaoType(2);

            } else if (HongBaoV2.TYPE_YAOQING == hongBaoType || HongBaoV2.TYPE_YIJI == hongBaoType

                    || HongBaoV2.TYPE_ERJI == hongBaoType || HongBaoV2.TYPE_SHARE_YIJI == hongBaoType

                    || HongBaoV2.TYPE_SHARE_ERJI == hongBaoType) {

                // 邀请

                order.setHongBaoType(3);

            }



            Integer hongBaoState = order.getHongBaoState();

            if (HongBaoV2.STATE_KELINGQU == hongBaoState || HongBaoV2.STATE_BUKELINGQU == hongBaoState) {

                // 未到账

                order.setHongBaoState(1);



            } else if (HongBaoV2.STATE_YILINGQU == hongBaoState) {

                // 已到账

                order.setHongBaoState(2);



            } else if (HongBaoV2.STATE_SHIXIAO == hongBaoState) {

                // 已失效

                order.setHongBaoState(3);

            }

        }

		
        return list;

			
    }



    @Override

    public long countQueryByUid(Long uid, Integer state, Integer type, Integer orderState, String orderNo,

            String startTime, String endTime, Integer dateType) throws CommonOrderException {

        return commonOrderMapper.countQueryByUid(uid, state, type, orderState, orderNo, startTime, endTime, dateType);

    }

	
	
}


 fanli/src/main/java/com/yeshi/fanli/service/inter/order/CommonOrderService.java

@@ -137,6 +137,28 @@
     * @param maxDate

     * @return

     */

    long countByUidAndOrderStateWithOrderBalanceTime(Long uid, int state, Date minDate, Date maxDate);

    public long countByUidAndOrderStateWithOrderBalanceTime(Long uid, int state, Date minDate, Date maxDate);



    /**

     * 后端查询订单列表

     * @param start

     * @param count

     * @param uid

     * @param state

     * @param type

     * @param orderState

     * @param orderNo

     * @param startTime

     * @param endTime

     * @param dateType 1昨天  2今天 3本月 4 上月

     * @return

     * @throws CommonOrderException

     */

    public List<CommonOrderVO> listQueryByUid(long start, int count, Long uid, Integer state, Integer type,

            Integer orderState, String orderNo, String startTime, String endTime, Integer dateType)

            throws CommonOrderException;



    public long countQueryByUid(Long uid, Integer state, Integer type, Integer orderState, String orderNo, String startTime,

            String endTime, Integer dateType) throws CommonOrderException;



}