<?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.InformMapper">

    <resultMap type="com.zhongzheng.modules.inform.domain.Inform" id="InformResult">
        <result property="informId" column="inform_id"/>
        <result property="informType" column="inform_type"/>
        <result property="informName" column="inform_name"/>
        <result property="informWay" column="inform_way"/>
        <result property="classifyId" column="classify_id"/>
        <result property="issueStatus" column="issue_status"/>
        <result property="issueTime" column="issue_time"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="receiptStatus" column="receipt_status"/>
        <result property="affiche" column="affiche"/>
        <result property="status" column="status"/>
    </resultMap>
    <resultMap type="com.zhongzheng.modules.inform.vo.InformVo" id="InformVo">
        <result property="informId" column="inform_id"/>
        <result property="informType" column="inform_type"/>
        <result property="informName" column="inform_name"/>
        <result property="informWay" column="inform_way"/>
        <result property="classifyId" column="classify_id"/>
        <result property="classifyName" column="classify_name"/>
        <result property="issueStatus" column="issue_status"/>
        <result property="issueTime" column="issue_time"/>
        <result property="receiptStatus" column="receipt_status"/>
        <result property="affiche" column="affiche"/>
        <result property="status" column="status"/>
        <result property="userNum" column="user_num"/>
        <result property="sendTime" column="send_time"/>
        <result property="userStatus" column="user_status"/>
        <result property="receiptNum" column="receipt_num"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.inform.vo.CourseInformVo" id="CourseInformVo">
        <result property="courseId" column="course_id"/>
        <result property="prefixName" column="prefix_name"/>
        <result property="coverUrl" column="cover_url"/>
        <result property="introduction" column="introduction"/>
        <result property="status" column="status"/>
        <result property="educationTypeId" column="education_type_id"/>
        <result property="businessId" column="business_id"/>
        <result property="schoolId" column="school_id"/>
        <result property="courseName" column="course_name"/>
        <result property="publishStatus" column="publish_status"/>
        <result property="sort" column="sort"/>
        <result property="suitableObject" column="suitable_object"/>
        <result property="pcDetailHtml" column="pc_detail_html"/>
        <result property="mobileDetailHtml" column="mobile_detail_html"/>
        <result property="majorId" column="major_id"/>
        <result property="code" column="code"/>
        <result property="subjectId" column="subject_id"/>
        <result property="projectId" column="project_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="educationName" column="education_name"/>
        <result property="projectName" column="project_name"/>
        <result property="businessName" column="business_name"/>
        <result property="schoolName" column="school_name"/>
        <result property="categoryName" column="category_name"/>
        <result property="subjectName" column="subject_name"/>
    </resultMap>
    <select id="listInform" parameterType="com.zhongzheng.modules.inform.bo.InformQueryBo"  resultMap="InformVo">
        SELECT
        i.*,
        (SELECT COUNT(iu.id) FROM inform_user iu where i.inform_id=iu.inform_id) as user_num,
        (SELECT ic.classify_name FROM inform_classify ic where i.classify_id=ic.classify_id) as classify_name,
        (SELECT iu.send_time FROM inform_user iu where i.inform_id=iu.inform_id ORDER BY iu.send_time ASC limit 1) as send_time,
        IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)=0,2,
        IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)=(SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id),1,
        IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)&lt;(SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id),3,4))) as user_status,
        (SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1) as receipt_num
        FROM
        inform i where 1=1
        <if test="status != null and status.size()!=0 ">
            AND i.status in
            <foreach collection="status" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="informType != null and informType != ''">
            and i.inform_type = #{informType}
        </if>
        <if test="informName != null and informName != ''">
            and i.inform_name = #{informName}
        </if>
        <if test="classifyId != null and classifyId != ''">
            and i.classify_id = #{classifyId}
        </if>
        <if test="informId != null and informId != ''">
            and i.inform_id = #{informId}
        </if>
        <if test="startTime != null and startTime != ''">
            and (SELECT iu.send_time FROM inform_user iu where i.inform_id=iu.inform_id ORDER BY iu.send_time ASC limit 1) BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="userStatus != null and userStatus != ''">
            and 	IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)=0,2,
            IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)=(SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id),1,
            IF((SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id and iu.send_status=1)&lt;(SELECT COUNT(iu.inform_id) FROM inform_user iu where i.inform_id=iu.inform_id),3,4))) = #{userStatus}
        </if>
    </select>

    <select id="selectOrder" parameterType="long"  resultType="integer">
        SELECT
            COUNT(og.order_sn)
        FROM
            `order` o
                LEFT JOIN order_goods og ON o.order_sn = og.order_sn
        WHERE
            refund_status = 0
          AND o.user_id =#{userId}
          AND og.goods_id = #{goodsId}
    </select>

    <select id="listGoodsCourse" parameterType="com.zhongzheng.modules.goods.bo.GoodsQueryBo"  resultMap="CourseInformVo">
        SELECT
            c.*,
            cet.education_name,
            cpt.project_name,
            cb.business_name,
            s.school_name,
            m.category_name,
            cs.subject_name
        FROM
            course c
                LEFT JOIN course_education_type cet ON c.education_type_id = cet.id AND cet.status =1
                LEFT JOIN course_project_type cpt ON c.project_id = cpt.id AND cpt.status =1
                LEFT JOIN course_business cb ON c.business_id = cb.id AND cb.status =1
                LEFT JOIN school s ON s.id = c.school_id AND s.status =1
                LEFT JOIN major m ON c.major_id = m.id AND m.status =1
                LEFT JOIN course_subject cs ON cs.id = c.subject_id AND cs.status =1
                LEFT JOIN goods_course gc on gc.course_id = c.course_id
        WHERE
            1 = 1 AND c.status =1
          and gc.goods_id =#{goodsId}
        GROUP BY
            c.course_id
        ORDER BY c.course_id DESC
    </select>
</mapper>
