Merge remote-tracking branch 'origin/div' into div

admin

2019-09-10 f7fc64d5ed3d19e131bc84398ae7e801ff30341e

Merge remote-tracking branch 'origin/div' into div

 fanli/src/main/java/com/yeshi/fanli/controller/admin/UserSystemCouponAdminController.java

@@ -13,7 +13,9 @@
import com.google.gson.Gson;

import com.google.gson.GsonBuilder;

import com.yeshi.fanli.controller.admin.utils.AdminUtils;

import com.yeshi.fanli.entity.bus.user.UserSystemCouponGiveRecord;

import com.yeshi.fanli.service.inter.count.UserSystemCouponCountService;

import com.yeshi.fanli.service.inter.user.UserSystemCouponGiveRecordService;

import com.yeshi.fanli.tag.PageEntity;

import com.yeshi.fanli.util.Constant;

import com.yeshi.fanli.util.StringUtil;

@@ -29,6 +31,9 @@


    @Resource

    private UserSystemCouponCountService userSystemCouponCountService;

	
    @Resource

    private UserSystemCouponGiveRecordService userSystemCouponGiveRecordService;



    /**

     * 用户奖励券列表

@@ -147,7 +152,7 @@
                return;

            }



            long count = userSystemCouponCountService.countSystemCouponFree(key);

            long count = list.size();



            int totalPage = (int) (count % pageSize == 0 ? count / pageSize : count / pageSize + 1);

            PageEntity pe = new PageEntity(pageIndex, pageSize, count, totalPage);

@@ -275,6 +280,68 @@
            e.printStackTrace();

        }

    }

	
	
	
    /**

     * 查询赠送免券

     * @param callback

     * @param pageIndex

     * @param pageSize

     * @param key

     * @param type 1赠送免单券 、2-奖励券

     * @param state

     * @param out

     */

    @RequestMapping(value = "listGiveCoupon")

    public void listGiveCoupon(String callback, Integer pageIndex, Integer pageSize, String key, Integer type,

            Integer state, PrintWriter out) {



        if (pageIndex == null || pageIndex < 1) {

            pageIndex = 1;

        }



        if (pageSize == null || pageSize < 1) {

            pageSize = Constant.PAGE_SIZE;

        }

		
        String couponType = null;

        if (type != null) {

            if (type == 1) {

                couponType = "freeCouponGive";

            } else if  (type == 2){

                couponType = "rebatePercentCoupon";

            } 
        }



        try {

            List<UserSystemCouponGiveRecord> list = userSystemCouponGiveRecordService.listGiveRecord((pageIndex - 1) * pageSize,

                    pageSize, couponType, state);



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

                JsonUtil.printMode(out, callback, JsonUtil.loadFalseResult("暂无数据"));

                return;

            }



            long count = userSystemCouponGiveRecordService.countGiveRecord(couponType, state);



            int totalPage = (int) (count % pageSize == 0 ? count / pageSize : count / pageSize + 1);

            PageEntity pe = new PageEntity(pageIndex, pageSize, count, totalPage);



            GsonBuilder gsonBuilder = new GsonBuilder();

            gsonBuilder.serializeNulls();

            Gson gson = gsonBuilder.setDateFormat("yyyy/MM/dd HH:mm:ss").create();



            JSONObject data = new JSONObject();

            data.put("pe", pe);

            data.put("result_list", gson.toJson(list));

            JsonUtil.printMode(out, callback, JsonUtil.loadTrueResult(data));

        } catch (Exception e) {

            JsonUtil.printMode(out, callback, JsonUtil.loadFalseResult("操作异常"));

            e.printStackTrace();

        }

    }

	


    /**

     * 奖励券数量曲线图

@@ -359,6 +426,8 @@
                couponType = "freeCoupon";

            } else if  (coupon == 2){

                couponType = "welfareFreeCoupon";

            } else if  (coupon == 3){

                couponType = "freeCouponBuy";

            }

            

            


 fanli/src/main/java/com/yeshi/fanli/dao/mybatis/user/UserSystemCouponGiveRecordMapper.java

@@ -43,4 +43,18 @@
     */
    UserSystemCouponGiveRecord getByReceiveId(@Param("receiveId") Long receiveId);
    
	
    /**
     * 查询赠送记录
     * @param start
     * @param count
     * @param type
     * @param state
     * @return
     */
    List<UserSystemCouponGiveRecord> listGiveRecord(@Param("start") long start, @Param("count") int count, 
            @Param("type") String type, @Param("state")Integer state);
	
    long countGiveRecord(@Param("type") String type, @Param("state")Integer state);
	
}

 fanli/src/main/java/com/yeshi/fanli/entity/bus/homemodule/Special.java

@@ -117,6 +117,14 @@
    // ios最小版本

    @Column(name = "b_min_ios_version_code")

    private Integer minIOSVersionCode;



    //起始时间

    @Column(name = "b_start_time")

    private Date startTime;

	
    //结束时间

    @Column(name = "b_end_time")

    private Date endTime;

    

    // 创建时间

    @Column(name = "b_createtime")

@@ -139,6 +147,14 @@


    // 限制最低版本

    private String version;

	
	
    // 是否定时

    private boolean timeTask;

    // 起始时间

    private String startTime_str;

    // 结束时间

    private String endTime_str;



    public Long getId() {

        return id;

@@ -357,4 +373,44 @@
        this.minIOSVersionCode = minIOSVersionCode;

    }



    public Date getStartTime() {

        return startTime;

    }



    public void setStartTime(Date startTime) {

        this.startTime = startTime;

    }



    public Date getEndTime() {

        return endTime;

    }



    public void setEndTime(Date endTime) {

        this.endTime = endTime;

    }



    public boolean isTimeTask() {

        return timeTask;

    }



    public void setTimeTask(boolean timeTask) {

        this.timeTask = timeTask;

    }



    public String getStartTime_str() {

        return startTime_str;

    }



    public void setStartTime_str(String startTime_str) {

        this.startTime_str = startTime_str;

    }



    public String getEndTime_str() {

        return endTime_str;

    }



    public void setEndTime_str(String endTime_str) {

        this.endTime_str = endTime_str;

    }

	
}


 fanli/src/main/java/com/yeshi/fanli/entity/bus/user/UserSystemCouponGiveRecord.java

@@ -46,6 +46,13 @@
    @Column(name = "cgr_state")

    private Integer state;

    

    // 券名称

    private String couponName;

    // 赠送人

    private String giveName;

    // 领取人

    private String receiveName;

	
    

    public Long getId() {

        return id;

@@ -118,4 +125,28 @@
    public void setReceiveId(Long receiveId) {

        this.receiveId = receiveId;

    }



    public String getCouponName() {

        return couponName;

    }



    public void setCouponName(String couponName) {

        this.couponName = couponName;

    }



    public String getGiveName() {

        return giveName;

    }



    public void setGiveName(String giveName) {

        this.giveName = giveName;

    }



    public String getReceiveName() {

        return receiveName;

    }



    public void setReceiveName(String receiveName) {

        this.receiveName = receiveName;

    }

}


 fanli/src/main/java/com/yeshi/fanli/mapping/homemodule/SpecialMapper.xml

@@ -21,6 +21,8 @@
        <result column="b_remark" property="remark" jdbcType="VARCHAR" />

        <result column="b_min_android_version_code" property="minAndroidVersionCode" jdbcType="INTEGER"/>

        <result column="b_min_ios_version_code" property="minIOSVersionCode" jdbcType="INTEGER"/>

        <result column="b_start_time" property="startTime" jdbcType="TIMESTAMP"/>

        <result column="b_end_time" property="endTime" jdbcType="TIMESTAMP"/>

        <result column="b_createtime" property="createtime" jdbcType="TIMESTAMP" />

        <result column="b_updatetime" property="updatetime" jdbcType="TIMESTAMP" />



@@ -33,7 +35,7 @@
        </association>



    </resultMap>

    <sql id="Base_Column_List">b_id,b_name,b_card,b_card_id,b_main_picture,b_icon,b_sub_picture,b_jumpid,b_params,b_jump_login,b_orderby,b_order_man,b_order_woman,b_state,b_sex,b_show_type,b_remark,b_min_android_version_code,b_min_ios_version_code,b_createtime,b_updatetime</sql>

    <sql id="Base_Column_List">b_id,b_name,b_card,b_card_id,b_main_picture,b_icon,b_sub_picture,b_jumpid,b_params,b_jump_login,b_orderby,b_order_man,b_order_woman,b_state,b_sex,b_show_type,b_remark,b_min_android_version_code,b_min_ios_version_code,b_start_time,b_end_time,b_createtime,b_updatetime</sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap"

        parameterType="java.lang.Long">

        select

@@ -44,9 +46,9 @@
        yeshi_ec_special where b_id = #{id,jdbcType=BIGINT}</delete>

    <insert id="insert" parameterType="com.yeshi.fanli.entity.bus.homemodule.Special"

        useGeneratedKeys="true" keyProperty="id">insert into yeshi_ec_special

        (b_id,b_name,b_card,b_card_id,b_main_picture,b_icon,b_sub_picture,b_jumpid,b_params,b_jump_login,b_orderby,b_order_man,b_order_woman,b_state,b_sex,b_show_type,b_remark,b_min_android_version_code,b_min_ios_version_code,b_createtime,b_updatetime)

        (b_id,b_name,b_card,b_card_id,b_main_picture,b_icon,b_sub_picture,b_jumpid,b_params,b_jump_login,b_orderby,b_order_man,b_order_woman,b_state,b_sex,b_show_type,b_remark,b_min_android_version_code,b_min_ios_version_code,b_start_time,b_end_time,b_createtime,b_updatetime)

        values

        (#{id,jdbcType=BIGINT},#{name,jdbcType=VARCHAR},#{card,jdbcType=VARCHAR},#{cardId,jdbcType=BIGINT},#{picture,jdbcType=VARCHAR},#{icon,jdbcType=VARCHAR},#{subPicture,jdbcType=VARCHAR},#{jumpDetail.id,jdbcType=BIGINT},#{params,jdbcType=VARCHAR},#{jumpLogin,jdbcType=VARCHAR},#{orderby,jdbcType=INTEGER},#{orderMan,jdbcType=INTEGER},#{orderWoman,jdbcType=INTEGER},#{state,jdbcType=BIGINT},#{sex,jdbcType=INTEGER},#{showType,jdbcType=VARCHAR},#{remark,jdbcType=VARCHAR},#{minAndroidVersionCode,jdbcType=INTEGER},#{minIOSVersionCode,jdbcType=INTEGER},#{createtime,jdbcType=TIMESTAMP},#{updatetime,jdbcType=TIMESTAMP})</insert>

        (#{id,jdbcType=BIGINT},#{name,jdbcType=VARCHAR},#{card,jdbcType=VARCHAR},#{cardId,jdbcType=BIGINT},#{picture,jdbcType=VARCHAR},#{icon,jdbcType=VARCHAR},#{subPicture,jdbcType=VARCHAR},#{jumpDetail.id,jdbcType=BIGINT},#{params,jdbcType=VARCHAR},#{jumpLogin,jdbcType=VARCHAR},#{orderby,jdbcType=INTEGER},#{orderMan,jdbcType=INTEGER},#{orderWoman,jdbcType=INTEGER},#{state,jdbcType=BIGINT},#{sex,jdbcType=INTEGER},#{showType,jdbcType=VARCHAR},#{remark,jdbcType=VARCHAR},#{minAndroidVersionCode,jdbcType=INTEGER},#{minIOSVersionCode,jdbcType=INTEGER},#{startTime,jdbcType=TIMESTAMP},#{endTime,jdbcType=TIMESTAMP},#{createtime,jdbcType=TIMESTAMP},#{updatetime,jdbcType=TIMESTAMP})</insert>

    <insert id="insertSelective" parameterType="com.yeshi.fanli.entity.bus.homemodule.Special"

        useGeneratedKeys="true" keyProperty="id">

        insert into yeshi_ec_special

@@ -70,6 +72,8 @@
            <if test="remark != null">b_remark,</if>

            <if test="minAndroidVersionCode != null">b_min_android_version_code,</if>

            <if test="minIOSVersionCode != null">b_min_ios_version_code,</if>

            <if test="startTime != null">b_start_time,</if>

            <if test="endTime != null">b_end_time,</if>

            <if test="createtime != null">b_createtime,</if>

            <if test="updatetime != null">b_updatetime,</if>

        </trim>

@@ -94,6 +98,8 @@
            <if test="remark != null">#{remark,jdbcType=VARCHAR},</if>

            <if test="minAndroidVersionCode != null">#{minAndroidVersionCode,jdbcType=INTEGER},</if>

            <if test="minIOSVersionCode != null">#{minIOSVersionCode,jdbcType=INTEGER},</if>

            <if test="startTime != null">#{startTime,jdbcType=TIMESTAMP},</if>

            <if test="endTime != null">#{endTime,jdbcType=TIMESTAMP},</if>

            <if test="createtime != null">#{createtime,jdbcType=TIMESTAMP},</if>

            <if test="updatetime != null">#{updatetime,jdbcType=TIMESTAMP},</if>

        </trim>

@@ -114,7 +120,7 @@
        #{state,jdbcType=BIGINT},b_show_type =

        #{showType,jdbcType=VARCHAR},b_remark =

        #{remark,jdbcType=VARCHAR},b_min_android_version_code = #{minAndroidVersionCode,jdbcType=INTEGER},b_min_ios_version_code = #{minIOSVersionCode,jdbcType=INTEGER},

        b_createtime =

        b_start_time = #{startTime,jdbcType=TIMESTAMP},b_end_time = #{endTime,jdbcType=TIMESTAMP},b_createtime =

        #{createtime,jdbcType=TIMESTAMP},b_updatetime =

        #{updatetime,jdbcType=TIMESTAMP},b_sex=#{sex,jdbcType=INTEGER} where

        b_id = #{id,jdbcType=BIGINT}</update>

@@ -139,6 +145,8 @@
            <if test="remark != null">b_remark=#{remark,jdbcType=VARCHAR},</if>

            <if test="minAndroidVersionCode != null">b_min_android_version_code=#{minAndroidVersionCode,jdbcType=INTEGER},</if>

              <if test="minIOSVersionCode != null">b_min_ios_version_code=#{minIOSVersionCode,jdbcType=INTEGER},</if>

              <if test="startTime != null">b_start_time=#{startTime,jdbcType=TIMESTAMP},</if>

            <if test="endTime != null">b_end_time=#{endTime,jdbcType=TIMESTAMP},</if>

            <if test="createtime != null">b_createtime=#{createtime,jdbcType=TIMESTAMP},</if>

            <if test="updatetime != null">b_updatetime=#{updatetime,jdbcType=TIMESTAMP},</if>

        </set>

@@ -351,7 +359,9 @@
        AND IF(c.`cd_end_time` IS NULL,TRUE,c.`cd_end_time`<![CDATA[>=]]>NOW())

        AND pc.`sp_key` = #{placeKey}

        LIMIT 1)c ON sp.`b_card_id` = c.`cd_id`

        WHERE sp.`b_state` = 0

        WHERE sp.`b_state` = 0  
            AND IF(sp.b_start_time IS NULL,TRUE, sp.b_start_time<![CDATA[<=]]> NOW()) 
              AND IF(sp.b_end_time IS NULL,TRUE, sp.b_end_time <![CDATA[>=]]> NOW())

        <include refid="Sex_Screen" />

    </select>

</mapper>


 fanli/src/main/java/com/yeshi/fanli/mapping/user/UserSystemCouponCountMapper.xml

@@ -1,384 +1,383 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponCountMapper">
  <resultMap id="BaseResultMap" type="com.yeshi.fanli.vo.user.UserSystemCouponCountVO">
    <id column="usc_id" property="id" jdbcType="BIGINT"/>
    <result column="usc_uid" property="uid" jdbcType="BIGINT"/>
    <result column="usc_source" property="source" jdbcType="VARCHAR"/>
    <result column="usc_state" property="state" jdbcType="INTEGER"/>
    <result column="usc_state_activate" property="stateActivated" jdbcType="INTEGER"/>
    <result column="usc_start_time" property="startTime" jdbcType="TIMESTAMP"/>
    <result column="usc_end_time" property="endTime" jdbcType="TIMESTAMP"/>
    <result column="usc_use_time" property="useTime" jdbcType="TIMESTAMP"/>
    <result column="usc_create_time" property="createTime" jdbcType="TIMESTAMP"/>
    <result column="usc_update_time" property="updateTime" jdbcType="TIMESTAMP"/>
	
    <association property="systemCoupon" column="ucr_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.SystemCouponMapper.BaseResultMap"/>
		
    <association property="userSystemCouponRecord" column="usc_coupon_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponRecordMapper.BaseResultMap"/>
  </resultMap>
  
  
   <resultMap id="BaseResultSystemMap" type="com.yeshi.fanli.vo.user.SystemCouponVO">
    <id column="sc_id" property="id" jdbcType="BIGINT"/>
    <result column="sc_name" property="name" jdbcType="VARCHAR"/>
    <result column="sc_picture" property="picture" jdbcType="VARCHAR"/>
    <result column="sc_picture_invalid" property="pictureInvalid" jdbcType="VARCHAR"/>
    <result column="sc_effect" property="effect" jdbcType="VARCHAR"/>
	
    <result column="sc_type" property="type"
            typeHandler="com.yeshi.fanli.util.mybatishandler.CouponTypeEnumHandler" />
	
    <result column="sc_percent" property="percent" jdbcType="DECIMAL"/>
    <result column="sc_amount" property="amount" jdbcType="VARCHAR"/>
    <result column="sc_expiry_day" property="expiryDay" jdbcType="INTEGER"/>
    <result column="sc_rule" property="rule" jdbcType="VARCHAR"/>
    <result column="sc_remark" property="remark" jdbcType="VARCHAR"/>
    <result column="sc_receive_count" property="receiveCount" jdbcType="INTEGER"/>
    <result column="sc_state" property="state" jdbcType="INTEGER"/>
    <result column="sc_create_time" property="createTime" jdbcType="TIMESTAMP"/>
    <result column="sc_update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    
     <result column="notUse" property="notUse" jdbcType="BIGINT"/>
     <result column="used" property="used" jdbcType="BIGINT"/>
     <result column="overdue" property="overdue" jdbcType="BIGINT"/>
     <result column="total" property="total" jdbcType="BIGINT"/>
     <result column="activateNum" property="activateNum" jdbcType="BIGINT"/>
  </resultMap>
  
  
   <resultMap id="BaseResultRecordMap" type="com.yeshi.fanli.entity.bus.user.UserSystemCouponRecord">
    <id column="ucr_id" property="id" jdbcType="BIGINT"/>
    <result column="ucr_good_id" property="goodId" jdbcType="BIGINT"/>
    <result column="ucr_good_source" property="goodSource" jdbcType="VARCHAR"/>
    <result column="ucr_coupon_type" property="couponType" jdbcType="VARCHAR"/>
    <result column="ucr_order_no" property="orderNo" jdbcType="VARCHAR"/>
    <result column="ucr_state" property="state" jdbcType="INTEGER"/>
    <result column="ucr_create_time" property="createTime" jdbcType="TIMESTAMP"/>
    <result column="ucr_update_time" property="updateTime" jdbcType="TIMESTAMP"/>
	
    <association property="userSystemCoupon" column="ucr_user_coupon_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponMapper.BaseResultMap"/>
		
  </resultMap>
  
  <select id="listRebateCoupon" resultMap="BaseResultMap">
    SELECT * FROM `yeshi_ec_user_system_coupon` uc
    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`
    LEFT JOIN `yeshi_ec_user_system_coupon_record` d ON d.`ucr_user_coupon_id` = uc.`usc_id`
    WHERE  p.`sc_type` = 'rebatePercentCoupon'
        <if test="state != null">
            AND uc.usc_state = #{state}
        </if>
		
        <if test="percent != null">
            AND p.`sc_percent` = #{percent}
        </if>
		
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_source` like '%${key}%'
            </if>
        </if>
    ORDER BY uc.`usc_create_time` DESC
    LIMIT ${start},${count}
  </select>
  
  <select id="countRebateCoupon" resultType="java.lang.Long">
    SELECT IFNULL(count(uc.`usc_id`),0)  FROM `yeshi_ec_user_system_coupon` uc
    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`
    WHERE p.`sc_type` = 'rebatePercentCoupon'
        <if test="state != null">
            AND uc.usc_state = #{state}
        </if>
        <if test="percent != null">
            AND p.`sc_percent` = #{percent}
        </if>
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_source` like '%${key}%'
            </if>
        </if>
  </select>
  
  
  <select id="listSystemCouponByRebate" resultMap="BaseResultSystemMap">
    SELECT * FROM  yeshi_ec_system_coupon p
    LEFT JOIN
        (SELECT SUM(A.notUse)AS  notUse, SUM(A.used)AS used, SUM(A.overdue)AS overdue,COUNT(*)AS total,usc_coupon_id
        FROM (SELECT IF(cp.`usc_state`=1,1,0)AS notUse,IF(cp.`usc_state`=3,1,0)AS used,IF(cp.`usc_state`=4,1,0)AS overdue,cp.`usc_coupon_id`
          FROM `yeshi_ec_user_system_coupon` cp
          )A
        GROUP BY A.usc_coupon_id)B ON p.`sc_id` = B.usc_coupon_id
    WHERE p.`sc_type` = 'rebatePercentCoupon'
        <if test="key != null and key != '' ">
            AND p.`sc_percent` = #{key}
        </if>
    ORDER BY 
        <if test="sort == null">p.`sc_percent`</if>
        <if test="sort == 1">B.notUse</if>
        <if test="sort == 2">B.notUse DESC</if>
        <if test="sort == 3">B.used</if>
        <if test="sort == 4">B.used DESC</if>
        <if test="sort == 5">B.total</if>
        <if test="sort == 6">B.total DESC</if>
	
    LIMIT ${start},${count}
  </select>
  
  <select id="countSystemCouponRebate" resultType="java.lang.Long">
    SELECT IFNULL(count(p.sc_id),0)  FROM `yeshi_ec_system_coupon` p
    WHERE p.`sc_type` = 'rebatePercentCoupon'
        <if test="key != null and key != '' ">
            AND p.`sc_percent` = #{key}
        </if>
  </select>
  
  <select id="countRebateMoneyByCouponId" resultType="java.math.BigDecimal">
    SELECT SUM(v2.`hb_money`) FROM yeshi_ec_user_system_coupon c 
    LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`
    LEFT JOIN `yeshi_ec_order` o ON o.`orderid` = pr.`ucr_order_no`
    LEFT JOIN `yeshi_ec_order_hongbaov2_map` map ON map.`ohm_order_id` = o.`id`
    LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON v2.`hb_id`= map.`ohm_hongbao_id`
    WHERE v2.`hb_id` IS NOT NULL AND c.`usc_coupon_id` = #{couponId}
  </select>
  
  <select id="listSystemCouponByFree" resultMap="BaseResultSystemMap">
    SELECT * FROM  yeshi_ec_system_coupon p
    LEFT JOIN
        (SELECT SUM(A.notUse)AS  notUse, SUM(A.used)AS used, SUM(A.overdue)AS overdue,COUNT(*)AS total, SUM(A.activateNum)AS activateNum,usc_coupon_id
        FROM (SELECT IF(cp.`usc_state`=1,1,0)AS notUse,IF(cp.`usc_state`=3,1,0)AS used,
                     IF(cp.`usc_state`=4,1,0)AS overdue,IF(cp.`usc_state_activate`=1,1,0)AS activateNum,cp.`usc_coupon_id`
          FROM `yeshi_ec_user_system_coupon` cp
          )A
        GROUP BY A.usc_coupon_id)B ON p.`sc_id` = B.usc_coupon_id
    WHERE (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon')
    ORDER BY 
        <if test="sort == null">p.sc_id</if>
        <if test="sort == 1">B.notUse</if>
        <if test="sort == 2">B.notUse DESC</if>
        <if test="sort == 3">B.used</if>
        <if test="sort == 4">B.used DESC</if>
        <if test="sort == 5">B.total</if>
        <if test="sort == 6">B.total DESC</if>
	
    LIMIT ${start},${count}
  </select>
  
  <select id="countSystemCouponFree" resultType="java.lang.Long">
    SELECT IFNULL(count(p.sc_id),0)  FROM `yeshi_ec_system_coupon` p
    WHERE p.`sc_type` = 'rebatePercentCoupon'
  </select>
  
   <select id="countFreeMoneyByCouponId" resultType="java.math.BigDecimal">
    SELECT SUM(v2.`hb_money`) FROM yeshi_ec_user_system_coupon c 
    LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`
    LEFT JOIN `yeshi_ec_common_order` co ON pr.`ucr_order_no` = co.`co_order_no`
    LEFT JOIN `yeshi_ec_hongbao_order` ho ON ho.`ho_order_id` = co.`co_id`
    LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON ho.`ho_hongbao_id` = v2.`hb_id`
    WHERE pr.`ucr_state` = 3 AND c.`usc_coupon_id` = #{couponId}
  </select>
  
  <select id="listFreeCoupon" resultMap="BaseResultMap">
    SELECT * FROM `yeshi_ec_user_system_coupon` uc
        LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`
        LEFT JOIN (SELECT * FROM `yeshi_ec_user_system_coupon_record` d WHERE d.`ucr_state` = 2 or  d.`ucr_state` = 3 )A ON A.`ucr_user_coupon_id` = uc.`usc_id`
        WHERE  (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon')
        <if test="state != null">
            AND uc.usc_state = #{state}
        </if>
        <if test="activated != null">
            AND uc.`usc_state_activate` = #{activated}
        </if>
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_source` like '%${key}%'
            </if>
        </if>
    ORDER BY uc.`usc_create_time` DESC
    LIMIT ${start},${count}
  </select>
  
  <select id="countFreeCoupon" resultType="java.lang.Long">
    SELECT IFNULL(count(uc.`usc_id`),0)  FROM `yeshi_ec_user_system_coupon` uc
    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`
    WHERE (p.`sc_type` = 'freeCoupon' or   p.`sc_type` = 'welfareFreeCoupon')
        <if test="state != null">
            AND uc.usc_state = #{state}
        </if>
        <if test="activated != null">
            AND uc.`usc_state_activate` = #{activated}
        </if>
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_source` like '%${key}%'
            </if>
        </if>
  </select>
  
  <select id="listFreeCouponRecord" resultMap="BaseResultRecordMap">
    SELECT * FROM `yeshi_ec_user_system_coupon_record` d 
        LEFT JOIN `yeshi_ec_user_system_coupon` uc  ON d.`ucr_user_coupon_id` = uc.`usc_id`
        LEFT JOIN yeshi_ec_system_coupon p  ON  p.`sc_id`= uc.`usc_coupon_id`
        WHERE  (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon')
        <if test="state != null">
            AND d.`ucr_state` = #{state}
        </if>
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_id` = #{key}
            </if>
        </if>
    ORDER BY d.`ucr_create_time` DESC
    LIMIT ${start},${count}
  </select>
  
  <select id="countFreeCouponRecord" resultType="java.lang.Long">
    SELECT IFNULL(count(d.`ucr_id`),0)  FROM  `yeshi_ec_user_system_coupon_record` d 
    LEFT JOIN `yeshi_ec_user_system_coupon` uc  ON d.`ucr_user_coupon_id` = uc.`usc_id`
    LEFT JOIN yeshi_ec_system_coupon p  ON  p.`sc_id`= uc.`usc_coupon_id`
    WHERE (p.`sc_type` = 'freeCoupon' or   p.`sc_type` = 'welfareFreeCoupon')
        <if test="state != null">
            AND d.`ucr_state` = #{state}
        </if>
        <if test="key != null and key != '' ">
            <if test="keyType == 1">
                AND uc.`usc_uid` = #{key}
            </if>
            <if test="keyType == 2">
                AND uc.`usc_id` = #{key}
            </if>
        </if>
  </select>
  
  <select id="getRebateCouponUsedNumToCharts" resultType="java.util.HashMap">
      SELECT IFNULL(COUNT(c.`usc_id`),0) AS showValue,
          <if test="dateType == 1">
            DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d') AS 'showDate'
        </if>
        <if test="dateType == 2">
            DATE_FORMAT(c.`usc_use_time`,'%m') AS 'showDate'
        </if>
        <if test="dateType == 3">
            DATE_FORMAT(c.`usc_use_time`,'%Y') AS 'showDate'
        </if>
      FROM yeshi_ec_user_system_coupon c 
      LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= c.`usc_coupon_id`
    WHERE c.`usc_state` = 3 AND  p.`sc_type` = 'rebatePercentCoupon'
          <if test="startTime != null and startTime != '' ">
            AND DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'
        </if>
        <if test="endTime != null and endTime != '' ">
            AND DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'
        </if>
        <if test="year != null and year != '' ">
            AND DATE_FORMAT(c.`usc_use_time`,'%Y') = '${year}'
        </if>
		
        <if test="dateType == 1">
            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d')
        </if>
        <if test="dateType == 2">
            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y-%m')
        </if>
        <if test="dateType == 3">
            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y')
        </if>
    ORDER BY c.`usc_use_time`
  </select>
  
  
  <select id="getRebateCouponMoneyToCharts" resultType="java.util.HashMap">
      SELECT IFNULL(SUM(c.`hb_money`),0) AS showValue,
          <if test="dateType == 1">
            DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d') AS 'showDate'
        </if>
        <if test="dateType == 2">
            DATE_FORMAT(c.`hb_get_time`,'%m') AS 'showDate'
        </if>
        <if test="dateType == 3">
            DATE_FORMAT(c.`hb_get_time`,'%Y') AS 'showDate'
        </if>
      FROM yeshi_ec_hongbao_v2 c 
    WHERE  c.`hb_type`= 10
          <if test="startTime != null and startTime != '' ">
            AND DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'
        </if>
        <if test="endTime != null and endTime != '' ">
            AND DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'
        </if>
        <if test="year != null and year != '' ">
            AND DATE_FORMAT(c.`hb_get_time`,'%Y') = '${year}'
        </if>
		
        <if test="dateType == 1">
            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d')
        </if>
        <if test="dateType == 2">
            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y-%m')
        </if>
        <if test="dateType == 3">
            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y')
        </if>
    ORDER BY c.`hb_get_time`
  </select>

  <select id="getFreeCouponMoneyToCharts" resultType="java.util.HashMap">
       SELECT IFNULL(SUM(v2.`hb_money`),0) AS showValue,
          <if test="dateType == 1">
            DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d') AS 'showDate'
        </if>
        <if test="dateType == 2">
            DATE_FORMAT(v2.`hb_get_time`,'%m') AS 'showDate'
        </if>
        <if test="dateType == 3">
            DATE_FORMAT(v2.`hb_get_time`,'%Y') AS 'showDate'
        </if>
       FROM yeshi_ec_user_system_coupon c 
       LEFT JOIN yeshi_ec_system_coupon sp ON sp.`sc_id` = c.`usc_coupon_id` 
     LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`
     LEFT JOIN `yeshi_ec_common_order` co ON pr.`ucr_order_no` = co.`co_order_no`
     LEFT JOIN `yeshi_ec_hongbao_order` ho ON ho.`ho_order_id` = co.`co_id`
     LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON ho.`ho_hongbao_id` = v2.`hb_id`
     WHERE pr.`ucr_state` = 3 AND sp.`sc_type` = #{couponType}
          <if test="startTime != null and startTime != '' ">
            AND DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'
        </if>
        <if test="endTime != null and endTime != '' ">
            AND DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'
        </if>
        <if test="year != null and year != '' ">
            AND DATE_FORMAT(v2.`hb_get_time`,'%Y') = '${year}'
        </if>
		
        <if test="dateType == 1">
            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d')
        </if>
        <if test="dateType == 2">
            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y-%m')
        </if>
        <if test="dateType == 3">
            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y')
        </if>
    ORDER BY v2.`hb_get_time`
  </select>

</mapper>
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">



<mapper namespace="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponCountMapper">

  <resultMap id="BaseResultMap" type="com.yeshi.fanli.vo.user.UserSystemCouponCountVO">

    <id column="usc_id" property="id" jdbcType="BIGINT"/>

    <result column="usc_uid" property="uid" jdbcType="BIGINT"/>

    <result column="usc_source" property="source" jdbcType="VARCHAR"/>

    <result column="usc_state" property="state" jdbcType="INTEGER"/>

    <result column="usc_state_activate" property="stateActivated" jdbcType="INTEGER"/>

    <result column="usc_start_time" property="startTime" jdbcType="TIMESTAMP"/>

    <result column="usc_end_time" property="endTime" jdbcType="TIMESTAMP"/>

    <result column="usc_use_time" property="useTime" jdbcType="TIMESTAMP"/>

    <result column="usc_create_time" property="createTime" jdbcType="TIMESTAMP"/>

    <result column="usc_update_time" property="updateTime" jdbcType="TIMESTAMP"/>

	
    <association property="systemCoupon" column="ucr_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.SystemCouponMapper.BaseResultMap"/>

		
    <association property="userSystemCouponRecord" column="usc_coupon_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponRecordMapper.BaseResultMap"/>

  </resultMap>

  
  
   <resultMap id="BaseResultSystemMap" type="com.yeshi.fanli.vo.user.SystemCouponVO">

    <id column="sc_id" property="id" jdbcType="BIGINT"/>

    <result column="sc_name" property="name" jdbcType="VARCHAR"/>

    <result column="sc_picture" property="picture" jdbcType="VARCHAR"/>

    <result column="sc_picture_invalid" property="pictureInvalid" jdbcType="VARCHAR"/>

    <result column="sc_effect" property="effect" jdbcType="VARCHAR"/>

	
    <result column="sc_type" property="type"

            typeHandler="com.yeshi.fanli.util.mybatishandler.CouponTypeEnumHandler" />

	
    <result column="sc_percent" property="percent" jdbcType="DECIMAL"/>

    <result column="sc_amount" property="amount" jdbcType="VARCHAR"/>

    <result column="sc_expiry_day" property="expiryDay" jdbcType="INTEGER"/>

    <result column="sc_rule" property="rule" jdbcType="VARCHAR"/>

    <result column="sc_remark" property="remark" jdbcType="VARCHAR"/>

    <result column="sc_receive_count" property="receiveCount" jdbcType="INTEGER"/>

    <result column="sc_state" property="state" jdbcType="INTEGER"/>

    <result column="sc_create_time" property="createTime" jdbcType="TIMESTAMP"/>

    <result column="sc_update_time" property="updateTime" jdbcType="TIMESTAMP"/>

    
     <result column="notUse" property="notUse" jdbcType="BIGINT"/>

     <result column="used" property="used" jdbcType="BIGINT"/>

     <result column="overdue" property="overdue" jdbcType="BIGINT"/>

     <result column="total" property="total" jdbcType="BIGINT"/>

     <result column="activateNum" property="activateNum" jdbcType="BIGINT"/>

  </resultMap>

  
  
   <resultMap id="BaseResultRecordMap" type="com.yeshi.fanli.entity.bus.user.UserSystemCouponRecord">

    <id column="ucr_id" property="id" jdbcType="BIGINT"/>

    <result column="ucr_good_id" property="goodId" jdbcType="BIGINT"/>

    <result column="ucr_good_source" property="goodSource" jdbcType="VARCHAR"/>

    <result column="ucr_coupon_type" property="couponType" jdbcType="VARCHAR"/>

    <result column="ucr_order_no" property="orderNo" jdbcType="VARCHAR"/>

    <result column="ucr_state" property="state" jdbcType="INTEGER"/>

    <result column="ucr_create_time" property="createTime" jdbcType="TIMESTAMP"/>

    <result column="ucr_update_time" property="updateTime" jdbcType="TIMESTAMP"/>

	
    <association property="userSystemCoupon" column="ucr_user_coupon_id" 
        resultMap="com.yeshi.fanli.dao.mybatis.user.UserSystemCouponMapper.BaseResultMap"/>

		
  </resultMap>

  
  <select id="listRebateCoupon" resultMap="BaseResultMap">

    SELECT * FROM `yeshi_ec_user_system_coupon` uc

    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`

    LEFT JOIN `yeshi_ec_user_system_coupon_record` d ON d.`ucr_user_coupon_id` = uc.`usc_id`

    WHERE  p.`sc_type` = 'rebatePercentCoupon'

        <if test="state != null">

            AND uc.usc_state = #{state}

        </if>

		
        <if test="percent != null">

            AND p.`sc_percent` = #{percent}

        </if>

		
        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_source` like '%${key}%'

            </if>

        </if>

    ORDER BY uc.`usc_create_time` DESC

    LIMIT ${start},${count}

  </select>

  
  <select id="countRebateCoupon" resultType="java.lang.Long">

    SELECT IFNULL(count(uc.`usc_id`),0)  FROM `yeshi_ec_user_system_coupon` uc

    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`

    WHERE p.`sc_type` = 'rebatePercentCoupon'

        <if test="state != null">

            AND uc.usc_state = #{state}

        </if>

        <if test="percent != null">

            AND p.`sc_percent` = #{percent}

        </if>

        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_source` like '%${key}%'

            </if>

        </if>

  </select>

  
  
  <select id="listSystemCouponByRebate" resultMap="BaseResultSystemMap">

    SELECT * FROM  yeshi_ec_system_coupon p

    LEFT JOIN

        (SELECT SUM(A.notUse)AS  notUse, SUM(A.used)AS used, SUM(A.overdue)AS overdue,COUNT(*)AS total,usc_coupon_id

        FROM (SELECT IF(cp.`usc_state`=1,1,0)AS notUse,IF(cp.`usc_state`=3,1,0)AS used,IF(cp.`usc_state`=4,1,0)AS overdue,cp.`usc_coupon_id`

          FROM `yeshi_ec_user_system_coupon` cp

          )A

        GROUP BY A.usc_coupon_id)B ON p.`sc_id` = B.usc_coupon_id

    WHERE p.`sc_type` = 'rebatePercentCoupon'

        <if test="key != null and key != '' ">

            AND p.`sc_percent` = #{key}

        </if>

    ORDER BY 
        <if test="sort == null">p.`sc_percent`</if>

        <if test="sort == 1">B.notUse</if>

        <if test="sort == 2">B.notUse DESC</if>

        <if test="sort == 3">B.used</if>

        <if test="sort == 4">B.used DESC</if>

        <if test="sort == 5">B.total</if>

        <if test="sort == 6">B.total DESC</if>

	
    LIMIT ${start},${count}

  </select>

  
  <select id="countSystemCouponRebate" resultType="java.lang.Long">

    SELECT IFNULL(count(p.sc_id),0)  FROM `yeshi_ec_system_coupon` p

    WHERE p.`sc_type` = 'rebatePercentCoupon'

        <if test="key != null and key != '' ">

            AND p.`sc_percent` = #{key}

        </if>

  </select>

  
  <select id="countRebateMoneyByCouponId" resultType="java.math.BigDecimal">

    SELECT SUM(v2.`hb_money`) FROM yeshi_ec_user_system_coupon c 
    LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`

    LEFT JOIN `yeshi_ec_order` o ON o.`orderid` = pr.`ucr_order_no`

    LEFT JOIN `yeshi_ec_order_hongbaov2_map` map ON map.`ohm_order_id` = o.`id`

    LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON v2.`hb_id`= map.`ohm_hongbao_id`

    WHERE v2.`hb_id` IS NOT NULL AND c.`usc_coupon_id` = #{couponId}

  </select>

  
  <select id="listSystemCouponByFree" resultMap="BaseResultSystemMap">

    SELECT * FROM  yeshi_ec_system_coupon p

    LEFT JOIN

        (SELECT SUM(A.notUse)AS  notUse, SUM(A.used)AS used, SUM(A.overdue)AS overdue,COUNT(*)AS total, SUM(A.activateNum)AS activateNum,usc_coupon_id

        FROM (SELECT IF(cp.`usc_state`=1,1,0)AS notUse,IF(cp.`usc_state`=3,1,0)AS used,

                     IF(cp.`usc_state`=4,1,0)AS overdue,IF(cp.`usc_state_activate`=1,1,0)AS activateNum,cp.`usc_coupon_id`

          FROM `yeshi_ec_user_system_coupon` cp

          )A

        GROUP BY A.usc_coupon_id)B ON p.`sc_id` = B.usc_coupon_id

    WHERE (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon' OR  p.`sc_type` = 'freeCouponBuy' OR  p.`sc_type` = 'freeCouponGive')

    ORDER BY 
        <if test="sort == null">p.sc_id</if>

        <if test="sort == 1">B.notUse</if>

        <if test="sort == 2">B.notUse DESC</if>

        <if test="sort == 3">B.used</if>

        <if test="sort == 4">B.used DESC</if>

        <if test="sort == 5">B.total</if>

        <if test="sort == 6">B.total DESC</if>

	
    LIMIT ${start},${count}

  </select>

  
  <select id="countSystemCouponFree" resultType="java.lang.Long">

    SELECT IFNULL(count(p.sc_id),0)  FROM `yeshi_ec_system_coupon` p

    WHERE p.`sc_type` = 'rebatePercentCoupon'

  </select>

  
   <select id="countFreeMoneyByCouponId" resultType="java.math.BigDecimal">

    SELECT SUM(v2.`hb_money`) FROM yeshi_ec_user_system_coupon c 
    LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`

    LEFT JOIN `yeshi_ec_common_order` co ON pr.`ucr_order_no` = co.`co_order_no`

    LEFT JOIN `yeshi_ec_hongbao_order` ho ON ho.`ho_order_id` = co.`co_id`

    LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON ho.`ho_hongbao_id` = v2.`hb_id`

    WHERE pr.`ucr_state` = 3 AND c.`usc_coupon_id` = #{couponId}

  </select>

  
  <select id="listFreeCoupon" resultMap="BaseResultMap">

    SELECT * FROM `yeshi_ec_user_system_coupon` uc

        LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`

        LEFT JOIN (SELECT * FROM `yeshi_ec_user_system_coupon_record` d WHERE d.`ucr_state` = 2 or  d.`ucr_state` = 3 )A ON A.`ucr_user_coupon_id` = uc.`usc_id`

        WHERE  (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon' OR  p.`sc_type` = 'freeCouponBuy')

        <if test="state != null">

            AND uc.usc_state = #{state}

        </if>

        <if test="activated != null">

            AND uc.`usc_state_activate` = #{activated}

        </if>

        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_source` like '%${key}%'

            </if>

        </if>

    ORDER BY uc.`usc_create_time` DESC

    LIMIT ${start},${count}

  </select>

  
  <select id="countFreeCoupon" resultType="java.lang.Long">

    SELECT IFNULL(count(uc.`usc_id`),0)  FROM `yeshi_ec_user_system_coupon` uc

    LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= uc.`usc_coupon_id`

    WHERE (p.`sc_type` = 'freeCoupon' or   p.`sc_type` = 'welfareFreeCoupon' OR  p.`sc_type` = 'freeCouponBuy')

        <if test="state != null">

            AND uc.usc_state = #{state}

        </if>

        <if test="activated != null">

            AND uc.`usc_state_activate` = #{activated}

        </if>

        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_source` like '%${key}%'

            </if>

        </if>

  </select>

  
  <select id="listFreeCouponRecord" resultMap="BaseResultRecordMap">

    SELECT * FROM `yeshi_ec_user_system_coupon_record` d 
        LEFT JOIN `yeshi_ec_user_system_coupon` uc  ON d.`ucr_user_coupon_id` = uc.`usc_id`

        LEFT JOIN yeshi_ec_system_coupon p  ON  p.`sc_id`= uc.`usc_coupon_id`

        WHERE  (p.`sc_type` = 'freeCoupon' OR  p.`sc_type` = 'welfareFreeCoupon' OR  p.`sc_type` = 'freeCouponBuy')

        <if test="state != null">

            AND d.`ucr_state` = #{state}

        </if>

        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_id` = #{key}

            </if>

        </if>

    ORDER BY d.`ucr_create_time` DESC

    LIMIT ${start},${count}

  </select>

  
  <select id="countFreeCouponRecord" resultType="java.lang.Long">

    SELECT IFNULL(count(d.`ucr_id`),0)  FROM  `yeshi_ec_user_system_coupon_record` d 
    LEFT JOIN `yeshi_ec_user_system_coupon` uc  ON d.`ucr_user_coupon_id` = uc.`usc_id`

    LEFT JOIN yeshi_ec_system_coupon p  ON  p.`sc_id`= uc.`usc_coupon_id`

    WHERE (p.`sc_type` = 'freeCoupon' or   p.`sc_type` = 'welfareFreeCoupon' OR  p.`sc_type` = 'freeCouponBuy')

        <if test="state != null">

            AND d.`ucr_state` = #{state}

        </if>

        <if test="key != null and key != '' ">

            <if test="keyType == 1">

                AND uc.`usc_uid` = #{key}

            </if>

            <if test="keyType == 2">

                AND uc.`usc_id` = #{key}

            </if>

        </if>

  </select>

  
  <select id="getRebateCouponUsedNumToCharts" resultType="java.util.HashMap">

      SELECT IFNULL(COUNT(c.`usc_id`),0) AS showValue,

          <if test="dateType == 1">

            DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d') AS 'showDate'

        </if>

        <if test="dateType == 2">

            DATE_FORMAT(c.`usc_use_time`,'%m') AS 'showDate'

        </if>

        <if test="dateType == 3">

            DATE_FORMAT(c.`usc_use_time`,'%Y') AS 'showDate'

        </if>

      FROM yeshi_ec_user_system_coupon c 
      LEFT JOIN yeshi_ec_system_coupon p   ON  p.`sc_id`= c.`usc_coupon_id`

    WHERE c.`usc_state` = 3 AND  p.`sc_type` = 'rebatePercentCoupon'

          <if test="startTime != null and startTime != '' ">

            AND DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'

        </if>

        <if test="endTime != null and endTime != '' ">

            AND DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'

        </if>

        <if test="year != null and year != '' ">

            AND DATE_FORMAT(c.`usc_use_time`,'%Y') = '${year}'

        </if>

		
        <if test="dateType == 1">

            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y-%m-%d')

        </if>

        <if test="dateType == 2">

            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y-%m')

        </if>

        <if test="dateType == 3">

            GROUP BY DATE_FORMAT(c.`usc_use_time`,'%Y')

        </if>

    ORDER BY c.`usc_use_time`

  </select>

  
  
  <select id="getRebateCouponMoneyToCharts" resultType="java.util.HashMap">

      SELECT IFNULL(SUM(c.`hb_money`),0) AS showValue,

          <if test="dateType == 1">

            DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d') AS 'showDate'

        </if>

        <if test="dateType == 2">

            DATE_FORMAT(c.`hb_get_time`,'%m') AS 'showDate'

        </if>

        <if test="dateType == 3">

            DATE_FORMAT(c.`hb_get_time`,'%Y') AS 'showDate'

        </if>

      FROM yeshi_ec_hongbao_v2 c 
    WHERE  c.`hb_type`= 10

          <if test="startTime != null and startTime != '' ">

            AND DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'

        </if>

        <if test="endTime != null and endTime != '' ">

            AND DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'

        </if>

        <if test="year != null and year != '' ">

            AND DATE_FORMAT(c.`hb_get_time`,'%Y') = '${year}'

        </if>

		
        <if test="dateType == 1">

            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y-%m-%d')

        </if>

        <if test="dateType == 2">

            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y-%m')

        </if>

        <if test="dateType == 3">

            GROUP BY DATE_FORMAT(c.`hb_get_time`,'%Y')

        </if>

    ORDER BY c.`hb_get_time`

  </select>



  <select id="getFreeCouponMoneyToCharts" resultType="java.util.HashMap">

       SELECT IFNULL(SUM(v2.`hb_money`),0) AS showValue,

          <if test="dateType == 1">

            DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d') AS 'showDate'

        </if>

        <if test="dateType == 2">

            DATE_FORMAT(v2.`hb_get_time`,'%m') AS 'showDate'

        </if>

        <if test="dateType == 3">

            DATE_FORMAT(v2.`hb_get_time`,'%Y') AS 'showDate'

        </if>

       FROM yeshi_ec_user_system_coupon c 
       LEFT JOIN yeshi_ec_system_coupon sp ON sp.`sc_id` = c.`usc_coupon_id` 
     LEFT JOIN `yeshi_ec_user_system_coupon_record` pr ON c.`usc_id` = pr.`ucr_user_coupon_id`

     LEFT JOIN `yeshi_ec_common_order` co ON pr.`ucr_order_no` = co.`co_order_no`

     LEFT JOIN `yeshi_ec_hongbao_order` ho ON ho.`ho_order_id` = co.`co_id`

     LEFT JOIN `yeshi_ec_hongbao_v2` v2 ON ho.`ho_hongbao_id` = v2.`hb_id`

     WHERE pr.`ucr_state` = 3 AND sp.`sc_type` = #{couponType}

          <if test="startTime != null and startTime != '' ">

            AND DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d')<![CDATA[ >= ]]>'${startTime}'

        </if>

        <if test="endTime != null and endTime != '' ">

            AND DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d') <![CDATA[ <= ]]>'${endTime}'

        </if>

        <if test="year != null and year != '' ">

            AND DATE_FORMAT(v2.`hb_get_time`,'%Y') = '${year}'

        </if>

		
        <if test="dateType == 1">

            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y-%m-%d')

        </if>

        <if test="dateType == 2">

            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y-%m')

        </if>

        <if test="dateType == 3">

            GROUP BY DATE_FORMAT(v2.`hb_get_time`,'%Y')

        </if>

    ORDER BY v2.`hb_get_time`

  </select>

</mapper>


 fanli/src/main/java/com/yeshi/fanli/mapping/user/UserSystemCouponGiveRecordMapper.xml

@@ -12,6 +12,8 @@
    <result column="cgr_receive_id" property="receiveId" jdbcType="BIGINT"/>
    <result column="cgr_receive_time" property="receiveTime" jdbcType="TIMESTAMP"/>
    <result column="cgr_state" property="state" jdbcType="INTEGER"/>
    
    <result column="couponName" property="couponName" jdbcType="VARCHAR"/>
  </resultMap>
  <sql id="Base_Column_List">cgr_id,cgr_coupon_id,cgr_give_uid,cgr_give_time,cgr_end_time,cgr_receive_uid,cgr_receive_id,cgr_receive_time,cgr_state</sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long">select
@@ -81,4 +83,25 @@
    WHERE d.cgr_receive_id = #{receiveId}
    LIMIT 1 
  </select>
  
  <select id="listGiveRecord" resultMap="BaseResultMap" >
      SELECT g.*,p.`sc_name` AS couponName FROM `yeshi_ec_user_system_coupon_give_record` g
    LEFT JOIN yeshi_ec_user_system_coupon c ON c.`usc_id` = g.`cgr_coupon_id`
    LEFT JOIN  yeshi_ec_system_coupon p ON p.`sc_id` = c.`usc_coupon_id`
    WHERE 1=1
        <if test="type != null">AND p.`sc_type` = #{type} </if>
        <if test="state != null">AND g.`cgr_state` = #{state} </if>
    ORDER BY g.`cgr_give_time` DESC
    LIMIT ${start},${count}
  </select>
  
  <select id="countGiveRecord" resultType="Long">
    SELECT IFNULL(COUNT(g.`cgr_id`),0) FROM `yeshi_ec_user_system_coupon_give_record` g
    LEFT JOIN yeshi_ec_user_system_coupon c ON c.`usc_id` = g.`cgr_coupon_id`
    LEFT JOIN  yeshi_ec_system_coupon p ON p.`sc_id` = c.`usc_coupon_id`
    WHERE 1=1
        <if test="type != null">AND p.`sc_type` = #{type} </if>
        <if test="state != null">AND g.`cgr_state` = #{state} </if>
  </select>
  
</mapper>

 fanli/src/main/java/com/yeshi/fanli/service/impl/count/UserSystemCouponCountServiceImpl.java

@@ -14,7 +14,7 @@
import com.yeshi.fanli.entity.bus.user.UserInfo;

import com.yeshi.fanli.entity.bus.user.UserSystemCoupon;

import com.yeshi.fanli.entity.bus.user.UserSystemCouponRecord;

import com.yeshi.fanli.entity.system.SystemCoupon;

import com.yeshi.fanli.entity.system.SystemCoupon.CouponTypeEnum;

import com.yeshi.fanli.service.inter.config.SystemCouponService;

import com.yeshi.fanli.service.inter.count.HongBaoV2CountService;

import com.yeshi.fanli.service.inter.count.UserSystemCouponCountService;

@@ -140,11 +140,13 @@
        }

        

        for (SystemCouponVO systemCouponVO: list) {

            BigDecimal money  = null;

            if (systemCouponVO.getType() != CouponTypeEnum.freeCouponGive) 
                money = userSystemCouponCountMapper.countFreeMoneyByCouponId(systemCouponVO.getId());

            

            BigDecimal money = userSystemCouponCountMapper.countFreeMoneyByCouponId(systemCouponVO.getId());

            if (money == null) {

            if (money == null) 
                money = new BigDecimal(0);

            }

			
            systemCouponVO.setMoney(money);

        }

        return list;


 fanli/src/main/java/com/yeshi/fanli/service/impl/homemodule/SpecialServiceImpl.java

@@ -1,6 +1,7 @@
package com.yeshi.fanli.service.impl.homemodule;



import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

@@ -21,6 +22,7 @@
import com.yeshi.fanli.entity.accept.AcceptData;

import com.yeshi.fanli.entity.bus.homemodule.Special;

import com.yeshi.fanli.entity.common.JumpDetailV2;

import com.yeshi.fanli.exception.homemodule.HomeNavbarException;

import com.yeshi.fanli.exception.homemodule.SpecialException;

import com.yeshi.fanli.service.inter.common.JumpDetailV2Service;

import com.yeshi.fanli.service.inter.config.AppVersionService;

@@ -84,6 +86,11 @@
        } else if (!StringUtil.isJson(params)) {

            throw new SpecialException(1, "跳转参数非JSON格式");

        }

		
        String startTime_str = record.getStartTime_str();

        if (record.isTimeTask() && (startTime_str == null || startTime_str.length() == 0)) {

            throw new SpecialException(1, "控制时间不能为空");

        }



        if (!StringUtil.isNullOrEmpty(jumpType)) {

            List<JumpDetailV2> listByType = jumpDetailV2Service.listByType(jumpType);

@@ -91,6 +98,9 @@
                record.setJumpDetail(listByType.get(0));

            }

        }

		
        // 时间转换

        conversionTime(record);



        Long state = record.getState();

        if (state == null) {

@@ -197,6 +207,37 @@
        }

    }



    /**

     * web段时间转换

     * @param record

     */

    public void conversionTime(Special record) throws SpecialException, Exception {

        // 是否时间控制

        if(!record.isTimeTask()) {

            record.setStartTime(null);

            record.setEndTime(null);

        } else {

            String startTime_str = record.getStartTime_str();

            String endTime_str = record.getEndTime_str();

			
            if ((startTime_str == null|| startTime_str.trim().length() == 0) 
                    && (endTime_str == null || endTime_str.trim().length() == 0)) {

                throw new HomeNavbarException(1, "请输入控制时间");

            } else {

                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");

                if (startTime_str != null && startTime_str.trim().length() > 0) {

                    startTime_str = startTime_str.replaceAll("T", " ");

                    record.setStartTime(format.parse(startTime_str));

                }

				
                if (endTime_str != null && endTime_str.trim().length() > 0) {

                    endTime_str = endTime_str.replaceAll("T", " ");

                    record.setEndTime(format.parse(endTime_str));

                }

            }

        }

    }

	
    /**

     * 上传图片

     * 

@@ -329,6 +370,32 @@


        // 跳转链接

        for (Special special : list) {

			
            Date startTime = special.getStartTime();

            Date endTime = special.getEndTime();

			
            if (startTime == null && endTime == null) {

                special.setTimeTask(false);

                special.setStartTime_str("");

                special.setEndTime_str("");

            } else {

                special.setTimeTask(true);

				
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm");

                if (startTime == null) {

                    special.setStartTime_str("");

                } else {

                    special.setStartTime_str(sdf.format(startTime));

                }

				
                if (endTime == null) {

                    special.setEndTime_str("");

                } else {

                    special.setEndTime_str(sdf.format(endTime));

                }

            }

			
			
            String params = special.getParams();

            if (StringUtil.isNullOrEmpty(params)) {

                special.setParams("");


 fanli/src/main/java/com/yeshi/fanli/service/impl/user/UserInfoExtraServiceImpl.java

@@ -405,7 +405,7 @@
        }

        

        // 获取微信信息

        WeiXinUser weiXinUser = WXLoginUtil.getWeiXinUser(code);

        WeiXinUser weiXinUser = WXLoginUtil.getWeiXinUserWithSavePortrait(code);

        if (weiXinUser == null) {

            throw new UserInfoExtraException(1, "微信授权失败");

        }


 fanli/src/main/java/com/yeshi/fanli/service/impl/user/UserSystemCouponGiveRecordServiceImpl.java

@@ -7,7 +7,9 @@
import org.springframework.stereotype.Service;



import com.yeshi.fanli.dao.mybatis.user.UserSystemCouponGiveRecordMapper;

import com.yeshi.fanli.entity.bus.user.UserInfo;

import com.yeshi.fanli.entity.bus.user.UserSystemCouponGiveRecord;

import com.yeshi.fanli.service.inter.user.UserInfoService;

import com.yeshi.fanli.service.inter.user.UserSystemCouponGiveRecordService;



@Service

@@ -16,6 +18,8 @@
    @Resource

    private UserSystemCouponGiveRecordMapper userSystemCouponGiveRecordMapper;

    

    @Resource

    private UserInfoService userInfoService;

    

    @Override

    public void insertSelective(UserSystemCouponGiveRecord record) {

@@ -51,4 +55,32 @@
    public UserSystemCouponGiveRecord getByReceiveId(Long receiveId) {

        return userSystemCouponGiveRecordMapper.getByReceiveId(receiveId);

    }

	
    @Override

    public List<UserSystemCouponGiveRecord> listGiveRecord(long start, int count, String type, Integer state) {

        List<UserSystemCouponGiveRecord> list = userSystemCouponGiveRecordMapper.listGiveRecord(start, count, type, state);

        if (list == null || list.size() == 0)			
            return list;

		
        for (UserSystemCouponGiveRecord giveRecord: list) {

            UserInfo giveUser = userInfoService.selectByPKey(giveRecord.getGiveUid());

            if (giveUser != null) 
                giveRecord.setGiveName(giveUser.getNickName());

			
            Long receiveUid = giveRecord.getReceiveUid();

            if (receiveUid == null)

                continue;

			
            UserInfo receiveUser = userInfoService.selectByPKey(receiveUid);

            if (receiveUser != null) 
                giveRecord.setReceiveName(receiveUser.getNickName());

        }

        return list;

    }

	
	
    @Override

    public long countGiveRecord(String type, Integer state) {

        return userSystemCouponGiveRecordMapper.countGiveRecord(type, state);

    }

}


 fanli/src/main/java/com/yeshi/fanli/service/inter/user/UserSystemCouponGiveRecordService.java

@@ -54,6 +54,19 @@
     * @param count

     * @return

     */

    UserSystemCouponGiveRecord getByReceiveId(Long receiveId);

    public UserSystemCouponGiveRecord getByReceiveId(Long receiveId);



	
    /**

     * 查询赠送记录

     * @param start

     * @param count

     * @param type

     * @param state

     * @return

     */

    public List<UserSystemCouponGiveRecord> listGiveRecord(long start, int count, String type, Integer state);



    public long countGiveRecord(String type, Integer state);



}