<?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.ym.mec.biz.dal.dao.SysCouponIssueRecordDao">
    <resultMap id="BaseResultMap" type="com.ym.mec.biz.dal.entity.SysCouponIssueRecord">
        <id column="id_" jdbcType="INTEGER" property="id"/>
        <result column="tenant_id_" jdbcType="INTEGER" property="tenantId"/>
        <result column="coupon_id_" jdbcType="INTEGER" property="couponId"/>
        <result column="coupon_name_" jdbcType="VARCHAR" property="couponName"/>
        <result column="num_" jdbcType="INTEGER" property="num"/>
        <result column="ops_" jdbcType="INTEGER" property="ops"/>
        <result column="created_by_" jdbcType="INTEGER" property="createdBy"/>
        <result column="created_time_" jdbcType="TIMESTAMP" property="createdTime"/>
        <result column="updated_by_" jdbcType="INTEGER" property="updatedBy"/>
        <result column="updated_time_" jdbcType="TIMESTAMP" property="updatedTime"/>
    </resultMap>

    <sql id="Base_Column_List">
        id_
        , tenant_id_, coupon_id_, coupon_name_, num_, ops_, created_by_, created_time_, updated_by_, updated_time_
    </sql>

    <insert id="insertBatch" keyColumn="id_" keyProperty="id" useGeneratedKeys="true"
            parameterType="com.ym.mec.biz.dal.entity.SysCouponIssueRecord">
        insert into sys_coupon_issue_record(tenant_id_, coupon_id_, coupon_name_, num_, ops_, created_by_,
        created_time_, updated_by_, updated_time_)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.tenantId}, #{entity.couponId}, #{entity.couponName}, #{entity.num}, #{entity.ops},
            #{entity.createdBy}, #{entity.createdTime}, #{entity.updatedBy}, #{entity.updatedTime})
        </foreach>
    </insert>

    <select id="queryIssueDetail" parameterType="map" resultType="map">
        SELECT o.name_ as organName
        , s.user_id_ as userId
        , su.username_ as userName
        , su.phone_ as phone
        , sb.name_ as subjectName
        , scc.usage_status_ as usageStatus
        , scc.id_ as couponCodeId
        FROM student s
        LEFT JOIN sys_user su ON su.id_ = s.user_id_
        LEFT JOIN organization o ON o.id_ = su.organ_id_
        LEFT JOIN subject sb ON sb.id_ = s.subject_id_list_
        join sys_coupon_code scc on s.user_id_ = scc.user_id_
        where s.tenant_id_ = #{param.tenantId}
        <if test="param.issueId !=null">
            and scc.issue_id_ = #{param.issueId}
        </if>
        <if test="param.organId !=null">
            and o.id_ = #{param.organId}
        </if>
        <if test="param.subjectId !=null">
            and sb.id_ = #{param.subjectId}
        </if>
        <if test="param.usageStatus !=null">
            and scc.usage_status_ = #{param.usageStatus}
        </if>
    </select>

    <select id="queryIssueRecord" parameterType="map" resultType="com.ym.mec.biz.dal.vo.CouponIssueRecordVo">
        select
        a.id_ as id,
        a.created_time_ as createdTime,
        a.coupon_name_ as couponName,
        a.num_ as num,
        u.real_name_ as `name`,
        a.ops_ as ops
        from sys_coupon_issue_record as a
        left join sys_user as u on a.created_by_ = u.id_
        where a.tenant_id_ = #{param.tenantId}
        <if test="param.ops !=null">
            and a.ops_ = #{param.ops}
        </if>
        <if test="param.couponId !=null">
          and a.coupon_id_ =#{param.couponId}
        </if>
    </select>

    <select id="queryCouponCode" resultType="integer">
        select count(id_)
        from sys_coupon_code
        where issue_id_ = #{issueId}
          and usage_status_ = #{state}
    </select>

    <update id="revoke">
        UPDATE sys_coupon_code
        SET usage_status_ = 2,
            used_time_    = NOW(),
            update_time_  = NOW()
        WHERE issue_id_ = #{issueId}
          and usage_status_ = 0
    </update>

    <update id="opsConsume" parameterType="object">
        UPDATE sys_coupon
        SET
            consume_num_ = consume_num_ - #{consumeNum}
        WHERE id_ = #{id}
    </update>

    <resultMap type="com.ym.mec.biz.dal.entity.SysCoupon" id="SysCoupon">
        <result column="id_" property="id" />
        <result column="name_" property="name" />
        <result column="description_" property="description" />
        <result column="status_" property="status" />
        <result column="issuance_type_" jdbcType="INTEGER" property="issuanceType"/>
        <result column="type_" property="type" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result column="type_detail_" jdbcType="VARCHAR" property="typeDetail"/>
        <result column="face_value_" property="faceValue" />
        <result column="full_amount_" property="fullAmount" />
        <result column="limit_exchange_num_" property="limitExchangeNum" />
        <result column="effective_type_" property="effectiveType" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result column="deadline_" property="deadline" />
        <result column="effective_start_time_" property="effectiveStartTime" />
        <result column="effective_expire_time_" property="effectiveExpireTime" />
        <result column="end_date_" property="endDate" />
        <result column="start_date_" property="startDate" />
        <result column="stock_count_" property="stockCount" />
        <result column="consume_num_" property="consumeNum" />
        <result column="warning_stock_num_" property="warningStockNum" />
        <result column="warning_status_" property="warningStatus" />
        <result column="create_time_" property="createTime" />
        <result column="update_time_" property="updateTime" />
        <result column="tenant_id_" property="tenantId"/>
        <result column="canBeGetNum" property="canBeGetNum"/>
    </resultMap>

    <select id="queryUserCanGetCoupon" resultMap="SysCoupon">
        SELECT DISTINCT
            id_,
            name_,
            description_,
            status_,
            issuance_type_,
            type_,
            type_detail_,
            face_value_,
            full_amount_,
            limit_exchange_num_,
            effective_type_,
            deadline_,
            effective_start_time_,
            effective_expire_time_,
            end_date_,
            start_date_,
            stock_count_,
            consume_num_,
            warning_stock_num_,
            warning_status_,
            create_time_,
            update_time_,
            tenant_id_,
            a.`limit_exchange_num_` - IFNULL(b.couponNum,0) AS canBeGetNum
        FROM
            sys_coupon AS a
                LEFT JOIN
            (SELECT
                 COUNT(coupon_id_) AS couponNum,
                 coupon_id_
             FROM
                 sys_coupon_code
             WHERE usage_status_  <![CDATA[ <> ]]> 2 AND user_id_ = #{userId}
             GROUP BY coupon_id_) AS b
            ON a.id_ = b.coupon_id_
            join
                (select organ_id_ from sys_user where id_ = #{userId}) c
        WHERE issuance_type_ = 0
          AND status_ = 1
          AND tenant_id_ = #{tenantId}
          AND a.`limit_exchange_num_` > IFNULL(b.couponNum, 0)
          AND NOW() >= start_date_ AND end_date_ >= NOW()
          AND (
                    stock_count_ > consume_num_
                OR stock_count_ = - 1
            )
        and (find_in_set(c.organ_id_,JSON_UNQUOTE(json_extract(a.use_condition_,'$.organId')))
        or JSON_UNQUOTE(json_extract(a.use_condition_,'$.organId')) is null
        or JSON_UNQUOTE(json_extract(a.use_condition_,'$.organId')) = "")

    </select>

</mapper>
