增加单内序号排序

admin

2018-12-13 449e7412380eb3a166e9de762402d48a2b6fdd37

增加单内序号排序

 fanli/src/main/java/com/yeshi/fanli/entity/taobao/TaoBaoOrder.java

@@ -75,6 +75,16 @@
    private String adPositionName; // 广告位名称

    @Column(name = "to_latest_updatetime")

    private Date latestUpdateTime;// 最近一次的更新时间

    @Column(name = "to_orderby")

    private Integer orderBy;// 排序值



    public Integer getOrderBy() {

        return orderBy;

    }



    public void setOrderBy(Integer orderBy) {

        this.orderBy = orderBy;

    }



    public Long getId() {

        return id;


 fanli/src/main/java/com/yeshi/fanli/mapping/taobao/TaoBaoOrderMapper.xml

@@ -48,11 +48,13 @@
            jdbcType="VARCHAR" />
        <result column="to_latest_updatetime" property="latestUpdateTime"
            jdbcType="TIMESTAMP" />

        <result column="to_orderby" property="orderBy" jdbcType="INTEGER" />
    </resultMap>
	
    <sql id="Base_Column_List">to_id,to_create_time,to_click_time,to_title,to_auction_id,to_manager_wangwang,to_shop,to_count,to_price,to_order_state,to_order_type,to_iratio,to_sratio,to_payment,to_estimate,to_settlement,to_eIncome,to_settlement_time,to_tk_rate,to_tk_money,to_technology_support_percent,to_subsidy_ratio,to_subsidy,to_subsidy_type,to_transaction_platform,to_third_service,to_order_id,to_class_name,to_source_media_id,to_source_media_name,to_ad_position_id,to_ad_position_name,to_latest_updatetime

    <sql id="Base_Column_List">to_id,to_create_time,to_click_time,to_title,to_auction_id,to_manager_wangwang,to_shop,to_count,to_price,to_order_state,to_order_type,to_iratio,to_sratio,to_payment,to_estimate,to_settlement,to_eIncome,to_settlement_time,to_tk_rate,to_tk_money,to_technology_support_percent,to_subsidy_ratio,to_subsidy,to_subsidy_type,to_transaction_platform,to_third_service,to_order_id,to_class_name,to_source_media_id,to_source_media_name,to_ad_position_id,to_ad_position_name,to_latest_updatetime,to_orderby
    </sql>
	

    <select id="selectByPrimaryKey" resultMap="BaseResultMap"
        parameterType="java.lang.Long">
        select
@@ -79,9 +81,9 @@

    <insert id="insert" parameterType="com.yeshi.fanli.entity.taobao.TaoBaoOrder"
        useGeneratedKeys="true" keyProperty="id">insert into yeshi_ec_taobao_order
        (to_id,to_create_time,to_click_time,to_title,to_auction_id,to_manager_wangwang,to_shop,to_count,to_price,to_order_state,to_order_type,to_iratio,to_sratio,to_payment,to_estimate,to_settlement,to_eIncome,to_settlement_time,to_tk_rate,to_tk_money,to_technology_support_percent,to_subsidy_ratio,to_subsidy,to_subsidy_type,to_transaction_platform,to_third_service,to_order_id,to_class_name,to_source_media_id,to_source_media_name,to_ad_position_id,to_ad_position_name,to_latest_updatetime)
        (to_id,to_create_time,to_click_time,to_title,to_auction_id,to_manager_wangwang,to_shop,to_count,to_price,to_order_state,to_order_type,to_iratio,to_sratio,to_payment,to_estimate,to_settlement,to_eIncome,to_settlement_time,to_tk_rate,to_tk_money,to_technology_support_percent,to_subsidy_ratio,to_subsidy,to_subsidy_type,to_transaction_platform,to_third_service,to_order_id,to_class_name,to_source_media_id,to_source_media_name,to_ad_position_id,to_ad_position_name,to_latest_updatetime,to_orderby)
        values
        (#{id,jdbcType=BIGINT},#{createTime,jdbcType=VARCHAR},#{clickTime,jdbcType=VARCHAR},#{title,jdbcType=VARCHAR},#{auctionId,jdbcType=BIGINT},#{managerWangWang,jdbcType=VARCHAR},#{shop,jdbcType=VARCHAR},#{count,jdbcType=INTEGER},#{price,jdbcType=DECIMAL},#{orderState,jdbcType=VARCHAR},#{orderType,jdbcType=VARCHAR},#{iRatio,jdbcType=DECIMAL},#{sRatio,jdbcType=DECIMAL},#{payment,jdbcType=DECIMAL},#{estimate,jdbcType=DECIMAL},#{settlement,jdbcType=DECIMAL},#{eIncome,jdbcType=DECIMAL},#{settlementTime,jdbcType=VARCHAR},#{tkRate,jdbcType=DECIMAL},#{tkMoney,jdbcType=DECIMAL},#{technologySupportPercent,jdbcType=DECIMAL},#{subsidyRatio,jdbcType=DECIMAL},#{subsidy,jdbcType=DECIMAL},#{subsidyType,jdbcType=VARCHAR},#{transactionPlatform,jdbcType=VARCHAR},#{thirdService,jdbcType=VARCHAR},#{orderId,jdbcType=VARCHAR},#{className,jdbcType=VARCHAR},#{sourceMediaId,jdbcType=VARCHAR},#{sourceMediaName,jdbcType=VARCHAR},#{adPositionId,jdbcType=VARCHAR},#{adPositionName,jdbcType=VARCHAR},#{latestUpdateTime,jdbcType=TIMESTAMP})
        (#{id,jdbcType=BIGINT},#{createTime,jdbcType=VARCHAR},#{clickTime,jdbcType=VARCHAR},#{title,jdbcType=VARCHAR},#{auctionId,jdbcType=BIGINT},#{managerWangWang,jdbcType=VARCHAR},#{shop,jdbcType=VARCHAR},#{count,jdbcType=INTEGER},#{price,jdbcType=DECIMAL},#{orderState,jdbcType=VARCHAR},#{orderType,jdbcType=VARCHAR},#{iRatio,jdbcType=DECIMAL},#{sRatio,jdbcType=DECIMAL},#{payment,jdbcType=DECIMAL},#{estimate,jdbcType=DECIMAL},#{settlement,jdbcType=DECIMAL},#{eIncome,jdbcType=DECIMAL},#{settlementTime,jdbcType=VARCHAR},#{tkRate,jdbcType=DECIMAL},#{tkMoney,jdbcType=DECIMAL},#{technologySupportPercent,jdbcType=DECIMAL},#{subsidyRatio,jdbcType=DECIMAL},#{subsidy,jdbcType=DECIMAL},#{subsidyType,jdbcType=VARCHAR},#{transactionPlatform,jdbcType=VARCHAR},#{thirdService,jdbcType=VARCHAR},#{orderId,jdbcType=VARCHAR},#{className,jdbcType=VARCHAR},#{sourceMediaId,jdbcType=VARCHAR},#{sourceMediaName,jdbcType=VARCHAR},#{adPositionId,jdbcType=VARCHAR},#{adPositionName,jdbcType=VARCHAR},#{latestUpdateTime,jdbcType=TIMESTAMP},#{orderBy,jdbcType=INTEGER})
    </insert>
    <insert id="insertSelective" parameterType="com.yeshi.fanli.entity.taobao.TaoBaoOrder"
        useGeneratedKeys="true" keyProperty="id">
@@ -120,6 +122,7 @@
            <if test="adPositionId != null">to_ad_position_id,</if>
            <if test="adPositionName != null">to_ad_position_name,</if>
            <if test="latestUpdateTime != null">to_latest_updatetime,</if>
            <if test="orderBy != null">to_orderby,</if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
@@ -156,6 +159,9 @@
            <if test="adPositionId != null">#{adPositionId,jdbcType=VARCHAR},</if>
            <if test="adPositionName != null">#{adPositionName,jdbcType=VARCHAR},</if>
            <if test="latestUpdateTime != null">#{latestUpdateTime,jdbcType=TIMESTAMP},</if>
            <if test="orderBy != null">#{orderBy,jdbcType=INTEGER},</if>
			
			
        </trim>
    </insert>
    <update id="updateByPrimaryKey" parameterType="com.yeshi.fanli.entity.taobao.TaoBaoOrder">update
@@ -192,7 +198,9 @@
        #{sourceMediaName,jdbcType=VARCHAR},to_ad_position_id =
        #{adPositionId,jdbcType=VARCHAR},to_ad_position_name =
        #{adPositionName,jdbcType=VARCHAR},to_latest_updatetime =
        #{latestUpdateTime,jdbcType=TIMESTAMP} where to_id =
        #{latestUpdateTime,jdbcType=TIMESTAMP},to_orderby=
        #{orderBy,jdbcType=INTEGER}
         where to_id =
        #{id,jdbcType=BIGINT}
    </update>
    <update id="updateByPrimaryKeySelective" parameterType="com.yeshi.fanli.entity.taobao.TaoBaoOrder">
@@ -238,11 +246,13 @@
            </if>
            <if test="latestUpdateTime != null">to_latest_updatetime=#{latestUpdateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="orderBy != null">to_orderby=#{orderBy,jdbcType=INTEGER},
            </if>
        </set>
        where to_id = #{id,jdbcType=BIGINT}
    </update>
	
	


    <resultMap id="ResultMapExtral" type="com.yeshi.fanli.entity.admin.ReslutOrder">
        <id column="to_id" property="tboid" jdbcType="BIGINT" />
        <result column="to_create_time" property="createTime" jdbcType="VARCHAR" />
@@ -294,106 +304,124 @@
            <id column="uid" property="id" jdbcType="BIGINT" />
        </association>
    </resultMap>
	

    <select id="queryJoinHongBao" resultMap="ResultMapExtral">
	     	
        SELECT * FROM (SELECT DISTINCT(to_id)AS id,hb.uid FROM `yeshi_ec_taobao_order` td
                LEFT JOIN `yeshi_ec_hongbao` hb ON td.`to_order_id`= hb.`order_id` 
                WHERE hb.`version` = 2 AND hb.`type` IN (1,2,20) 
                 <if test='key != null and key != ""'>
                       AND (td.`to_order_id` like '%${key}%' OR hb.uid like '%${key}%') 
                 </if>
                 <if test="days != null">
                    <![CDATA[ AND DATE_SUB(CURDATE(), INTERVAL ${days}  DAY) <= td.`to_create_time` ]]> 
                </if>
                 <if test='startTime != null and startTime != ""'>

        SELECT * FROM (SELECT DISTINCT(to_id)AS id,hb.uid FROM
        `yeshi_ec_taobao_order` td
        LEFT JOIN `yeshi_ec_hongbao` hb ON
        td.`to_order_id`= hb.`order_id`
        WHERE hb.`version` = 2 AND hb.`type` IN
        (1,2,20)
        <if test='key != null and key != ""'>
            AND (td.`to_order_id` like '%${key}%' OR hb.uid like
            '%${key}%')
        </if>
        <if test="days != null">
                    <![CDATA[ AND DATE_SUB(CURDATE(), INTERVAL ${days}  DAY) <= td.`to_create_time` ]]>
        </if>
        <if test='startTime != null and startTime != ""'>
                    <![CDATA[
                    AND  td.to_create_time  >= #{startTime}
                       ]]>  
                 </if>
		     	
                 <if test='endTime != null and endTime != ""'>
                       ]]>
        </if>

        <if test='endTime != null and endTime != ""'>
                    <![CDATA[
                   AND  td.to_create_time  < #{endTime}
                       ]]>  
                 </if>
		     	
                 <if test='type == 1'>
                     AND (td.`to_order_state` = '订单结算' or td.`to_order_state` = '订单成功')
                 </if>
                 <if test='type == 2'>
                     AND td.`to_order_state` = '订单付款' 
                 </if>
                 <if test='type == 3'>
                     AND td.`to_order_state` = '订单失效'
                 </if>
                 ORDER BY td.`to_create_time` DESC LIMIT ${start},${count} )A  
                 LEFT JOIN yeshi_ec_taobao_order tt ON A.id = tt.to_id
					 
                       ]]>
        </if>

        <if test='type == 1'>
            AND (td.`to_order_state` = '订单结算' or td.`to_order_state` =
            '订单成功')
        </if>
        <if test='type == 2'>
            AND td.`to_order_state` = '订单付款'
        </if>
        <if test='type == 3'>
            AND td.`to_order_state` = '订单失效'
        </if>
        ORDER BY td.`to_create_time` DESC LIMIT ${start},${count} )A
        LEFT JOIN
        yeshi_ec_taobao_order tt ON A.id = tt.to_id

    </select>
	

    <select id="countQueryJoinHongBao" resultType="java.lang.Integer">
         SELECT count(tt.to_id) FROM (SELECT DISTINCT(to_id)AS id FROM `yeshi_ec_taobao_order` td 
                LEFT JOIN `yeshi_ec_hongbao` hb ON td.`to_order_id`= hb.`order_id` 
                WHERE hb.`version` = 2 AND hb.`type` IN (1,2,20) 
                 <if test='key != null and key != ""'>
                       AND (td.`to_order_id` like '%${key}%' OR hb.uid like '%${key}%') 
                 </if>
	     	
             <if test="days != null">
                <![CDATA[ AND DATE_SUB(CURDATE(), INTERVAL ${days}  DAY) <= td.`to_create_time` ]]> 
            </if>
	        
             <if test='startTime != null and startTime != ""'>
        SELECT count(tt.to_id) FROM (SELECT DISTINCT(to_id)AS id FROM
        `yeshi_ec_taobao_order` td
        LEFT JOIN `yeshi_ec_hongbao` hb ON
        td.`to_order_id`= hb.`order_id`
        WHERE hb.`version` = 2 AND hb.`type` IN
        (1,2,20)
        <if test='key != null and key != ""'>
            AND (td.`to_order_id` like '%${key}%' OR hb.uid like
            '%${key}%')
        </if>

        <if test="days != null">
                <![CDATA[ AND DATE_SUB(CURDATE(), INTERVAL ${days}  DAY) <= td.`to_create_time` ]]>
        </if>

        <if test='startTime != null and startTime != ""'>
                <![CDATA[
                AND to_create_time  >= #{startTime}
                   ]]>  
             </if>
	     	
             <if test='endTime != null and endTime != ""'>
                   ]]>
        </if>

        <if test='endTime != null and endTime != ""'>
                <![CDATA[
                AND to_create_time  < #{endTime}
                   ]]>  
             </if>
	     	
             <if test='type == 1'>
                 AND (td.`to_order_state` = '订单结算' or td.`to_order_state` = '订单成功')
             </if>
             <if test='type == 2'>
                 AND td.`to_order_state` = '订单付款' 
             </if>
             <if test='type == 3'>
                 AND td.`to_order_state` = '订单失效'
             </if>
                 )A  LEFT JOIN yeshi_ec_taobao_order tt ON A.id = tt.to_id 
                   ]]>
        </if>

        <if test='type == 1'>
            AND (td.`to_order_state` = '订单结算' or td.`to_order_state` =
            '订单成功')
        </if>
        <if test='type == 2'>
            AND td.`to_order_state` = '订单付款'
        </if>
        <if test='type == 3'>
            AND td.`to_order_state` = '订单失效'
        </if>
        )A LEFT JOIN yeshi_ec_taobao_order tt ON A.id = tt.to_id
    </select>
    <select id="countByOdrerType" resultType="java.util.HashMap">
        SELECT COUNT(to_id)AS countTotal ,IFNULL(SUM(CASE WHEN `to_order_state` = '订单结算' THEN 1 WHEN `to_order_state` = '订单成功' THEN 1 ELSE 0 END),0) AS countSettlement,
            IFNULL(SUM(CASE WHEN `to_order_state` = '订单付款' THEN 1 ELSE 0 END),0) AS countPayment,
            IFNULL(SUM(CASE WHEN `to_order_state` = '订单失效' THEN 1 ELSE 0 END),0) AS countInvalid
            FROM `yeshi_ec_taobao_order`
        SELECT COUNT(to_id)AS
        countTotal ,IFNULL(SUM(CASE WHEN `to_order_state` = '订单结算' THEN 1 WHEN
        `to_order_state` = '订单成功' THEN 1 ELSE 0 END),0) AS countSettlement,
        IFNULL(SUM(CASE WHEN `to_order_state` = '订单付款' THEN 1 ELSE 0 END),0)
        AS countPayment,
        IFNULL(SUM(CASE WHEN `to_order_state` = '订单失效' THEN 1
        ELSE 0 END),0) AS countInvalid
        FROM `yeshi_ec_taobao_order`
    </select>
	

    <select id="countToday" resultType="java.lang.Integer">
         SELECT COUNT(DISTINCT(tb.to_order_id)) FROM `yeshi_ec_taobao_order` tb 
         WHERE <![CDATA[tb.`to_order_state`  != '订单失效'  AND TO_DAYS(tb.`to_create_time`) = TO_DAYS(NOW())]]>
        SELECT COUNT(DISTINCT(tb.to_order_id)) FROM `yeshi_ec_taobao_order` tb
        WHERE <![CDATA[tb.`to_order_state`  != '订单失效'  AND TO_DAYS(tb.`to_create_time`) = TO_DAYS(NOW())]]>
    </select>
	

    <select id="countYesterday" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT(td.to_order_id))FROM `yeshi_ec_taobao_order` td 
         WHERE <![CDATA[td.`to_order_state`  != '订单失效'  AND TO_DAYS(NOW()) - TO_DAYS( td.`to_create_time`) = 1 ]]>  
        SELECT COUNT(DISTINCT(td.to_order_id))FROM `yeshi_ec_taobao_order` td
        WHERE <![CDATA[td.`to_order_state`  != '订单失效'  AND TO_DAYS(NOW()) - TO_DAYS( td.`to_create_time`) = 1 ]]>
    </select>
	
	


    <select id="countEstimate" resultType="java.lang.Double">
        SELECT IFNULL(SUM(t.to_estimate),0) FROM `yeshi_ec_taobao_order` t 
        WHERE <![CDATA[ t.`to_order_state`<> '订单失效' ]]>  
        SELECT IFNULL(SUM(t.to_estimate),0) FROM `yeshi_ec_taobao_order` t
        WHERE <![CDATA[ t.`to_order_state`<> '订单失效' ]]>
        AND DATE_FORMAT(t.`to_create_time` , '%Y-%m-%d' )= #{date}
    </select>
	
	


    <select id="getStateByOrderIdAndPayment" resultMap="BaseResultMap">
        select <include refid="Base_Column_List" /> from yeshi_ec_taobao_order 
        where to_order_id = #{orderId} and to_payment = #{payment}
        select
        <include refid="Base_Column_List" />
        from yeshi_ec_taobao_order
        where to_order_id = #{orderId} and
        to_payment = #{payment}
    </select>
</mapper>

 fanli/src/main/java/com/yeshi/fanli/util/taobao/TaoBaoOrderUtil.java

@@ -800,6 +800,8 @@
        for (TaoBaoOrder order : orderList) {

            if (map.get(order.getOrderId()) == null)

                map.put(order.getOrderId(), new ArrayList<>());

            // 设置排序值，从1开始

            order.setOrderBy(map.get(order.getOrderId()).size() + 1);

            map.get(order.getOrderId()).add(order);

        }

        return map;