<?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.zhongzheng.modules.inform.mapper.InformUserMapper">

    <resultMap type="com.zhongzheng.modules.inform.domain.InformUser" id="InformUserResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="sendTime" column="send_time"/>
        <result property="sendStatus" column="send_status"/>
        <result property="receiptStatus" column="receipt_status"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.inform.vo.InformUserVo" id="InformUserVo">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="sendTime" column="send_time"/>
        <result property="sendStatus" column="send_status"/>
        <result property="receiptStatus" column="receipt_status"/>
        <result property="realName" column="realname"/>
        <result property="idCard" column="id_card"/>
        <result property="telPhone" column="telphone"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.exam.vo.ExamUserApplyVo" id="ExamUserApplyVo">
        <result property="userId" column="user_id"/>
        <result property="applyId" column="apply_id"/>
        <result property="applyStatus" column="apply_status"/>
        <result property="goodsId" column="goods_id"/>
        <result property="applyEndTime" column="apply_end_time"/>
        <result property="gradeId" column="grade_id"/>
    </resultMap>

    <select id="queryUserList" parameterType="com.zhongzheng.modules.inform.bo.InformUserQueryBo" resultMap="InformUserVo">
        SELECT
            iu.id,
            iu.user_id,
            (SELECT u.realname FROM `user` u where iu.user_id=u.user_id) as realname,
            (SELECT u.telphone FROM `user` u where iu.user_id=u.user_id) as telphone,
            (SELECT u.id_card FROM `user` u where iu.user_id=u.user_id) as id_card,
            iu.send_time,
            iu.send_status
        FROM
            inform_user iu
        WHERE
            1 =1
        <if test="informId != null and informId != ''">
            and iu.inform_id =  #{informId}
        </if>
          <if test="sendStatus != null">
            and iu.send_status =  #{sendStatus}
        </if>
        <if test="receiptName != null and receiptName != ''">
            and ((SELECT u.id_card FROM `user` u where iu.user_id=u.user_id) like concat('%', #{receiptName}, '%') or
              (SELECT u.realname FROM `user` u where iu.user_id=u.user_id)like concat('%', #{receiptName}, '%') or
              (SELECT count(cg.class_name) FROM class_grade_user cgu LEFT JOIN class_grade cg on cgu.grade_id = cg.grade_id where cgu.user_id=iu.user_id and cgu.`status`=1 and cg.`status`=1 and cg.class_name like concat('%', #{receiptName}, '%')) > 0)
        </if>
    </select>

    <select id="listUserApply"  resultMap="ExamUserApplyVo">
        SELECT
        cgu.user_id,
        ea.apply_id,
        ea.apply_status,
        cgg.goods_id,
        cgg.grade_id,
        ea.apply_end_time
        FROM
        exam_apply ea
        LEFT JOIN exam_apply_goods eag on ea.apply_id = eag.apply_id
        LEFT JOIN class_grade_goods cgg on cgg.goods_id=eag.goods_id
        LEFT JOIN class_grade_user cgu on cgg.grade_id = cgu.grade_id
        LEFT JOIN class_grade cg on cg.grade_id = cgu.grade_id
        LEFT JOIN goods g on g.goods_id = cgg.goods_id
        WHERE
        ea.`status` =1
        AND cg.`status`=1
        and cgu.period_status =1
        AND (SELECT count(1) FROM inform_user iu where iu.goods_id = cgg.goods_id and iu.remind_id=13 and cgu.user_id = iu.user_id) &lt; 1
        AND  ea.apply_status='1'
        AND (SELECT COUNT(1) FROM user_subscribe us where us.apply_id = ea.apply_id and us.user_id = cgu.user_id and us.subscribe_status=1) &lt; 1
        and (SELECT COUNT(1) FROM inform_remind_business ifb where g.business_id = ifb.business_id and ifb.remind_id = 13) >0
        UNION
        SELECT
        cgu.user_id,
        ea.apply_id,
        ea.apply_status,
        cgg.goods_id,
        cgg.grade_id,
        ea.apply_end_time
        FROM
        exam_apply ea
        LEFT JOIN exam_apply_goods eag on ea.apply_id = eag.apply_id
        LEFT JOIN class_grade_goods cgg on cgg.goods_id=eag.goods_id
        LEFT JOIN class_grade_user cgu on cgg.grade_id = cgu.grade_id
        LEFT JOIN class_grade cg on cg.grade_id = cgu.grade_id
        LEFT JOIN goods g on g.goods_id = cgg.goods_id
        WHERE
        ea.`status` =1
        and cgu.period_status =1
        AND cg.`status`=1
        AND (SELECT count(1) FROM inform_user iu where iu.goods_id = cgg.goods_id and iu.remind_id=13 and cgu.user_id = iu.user_id) &lt; 1
        AND  ea.apply_status='1,2'
        and (SELECT COUNT(1) FROM inform_remind_business ifb where g.business_id = ifb.business_id and ifb.remind_id = 13) >0
        UNION
        SELECT
        cgu.user_id,
        ea.apply_id,
        ea.apply_status,
        cgg.goods_id,
        cgg.grade_id,
        ea.apply_end_time
        FROM
        exam_apply ea
        LEFT JOIN exam_apply_goods eag on ea.apply_id = eag.apply_id
        LEFT JOIN class_grade_goods cgg on cgg.goods_id=eag.goods_id
        LEFT JOIN class_grade_user cgu on cgg.grade_id = cgu.grade_id
        LEFT JOIN class_grade cg on cg.grade_id = cgu.grade_id
        LEFT JOIN goods g on g.goods_id = cgg.goods_id
        WHERE
        ea.`status` =1
        AND cg.`status`=1
        and cgu.period_status =1
        AND (SELECT count(1) FROM inform_user iu where iu.goods_id = cgg.goods_id and iu.remind_id=13 and cgu.user_id = iu.user_id) &lt; 1
        AND  ea.apply_status='2'
        AND (SELECT COUNT(1) FROM user_subscribe us where us.apply_id = ea.apply_id and us.user_id = cgu.user_id and us.exam_status in (2,3,4) and us.exam_status != 1) > 0
        and (SELECT COUNT(1) FROM inform_remind_business ifb where g.business_id = ifb.business_id and ifb.remind_id = 13) >0
    </select>
</mapper>
