<?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.qmth.distributed.print.business.mapper.ExamCardMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.distributed.print.business.entity.ExamCard">
        <result column="id" property="id"/>
        <result column="school_id" property="schoolId"/>
        <result column="org_id" property="orgId"/>
        <result column="course_code" property="courseCode"/>
        <result column="course_name" property="courseName"/>
        <result column="title" property="title"/>
        <result column="make_method" property="makeMethod"/>
        <result column="status" property="status"/>
        <result column="create_id" property="createId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_id" property="updateId"/>
        <result column="update_time" property="updateTime"/>
        <result column="type" property="type"/>
        <result column="create_method" property="createMethod"/>
        <result column="attachment_id" property="attachmentId"/>
        <result column="sync_status" property="syncStatus"/>
        <result column="enable" property="enable"/>
        <result column="remark" property="remark"/>
        <result column="card_rule_id" property="cardRuleId"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        school_id,org_id, course_code, course_name, title, make_method, status, create_id, create_time, update_id, update_time, type, create_method, attachment_id, sync_status, enable, remark, card_rule_id
    </sql>
    <select id="listCardCust" resultType="com.qmth.distributed.print.business.bean.dto.CardCustDto">
        SELECT
            c.id examTaskId,
            a.id cardId,
            c.card_rule_id cardRuleId,
            a.course_code courseCode,
            a.course_name courseName,
            a.status,
            c.paper_number paperNumber,
            d.school_id schoolId,
            e.name schoolName,
            d.org_id orgId,
            f.name orgName,
            a.create_id createId,
            a.create_time createTime,
            g.real_name createName,
            a.update_time updateTime
        FROM
            exam_card a
                LEFT JOIN
            exam_task_detail b ON FIND_IN_SET(a.id, REPLACE(substring_index(substring_index(CONVERT((b.paper_attachment_ids ->>'$[*].cardId') USING utf8), ']', 1), '[',-1),'"',''))
                LEFT JOIN
            exam_task c ON b.exam_task_id = c.id
                LEFT JOIN
            sys_user d ON c.user_id = d.id
                LEFT JOIN
            basic_school e ON d.school_id = e.id
                LEFT JOIN
            sys_org f ON d.org_id = f.id
                LEFT JOIN
            sys_user g ON a.create_id = g.id
        <where>
            a.make_method = 'CUST'
            <if test="schoolId != null and schoolId != ''">
                and a.school_id = #{schoolId}
            </if>
            <if test="status != null and status != ''">
                and a.status = #{status}
            </if>
            <if test="paperNumber != null and paperNumber != ''">
                and c.paper_number = #{paperNumber}
            </if>
            <if test="userId != null and userId != ''">
                and a.create_id = #{userId}
            </if>
            <if test="applyStartTime != null and applyStartTime != ''">
                and a.create_time &gt; #{applyStartTime}
            </if>
            <if test="applyEndTime != null and applyEndTime != ''">
                and a.create_time &lt; #{applyEndTime}
            </if>
            <if test="finishStartTime != null and finishStartTime != ''">
                and a.update_time &gt; #{finishStartTime}
            </if>
            <if test="finishEndTime != null and finishEndTime != ''">
                and a.update_time &lt; #{finishEndTime}
            </if>
        </where>
        order by a.create_time desc
    </select>
    <select id="getCardDetail" resultType="com.qmth.distributed.print.business.bean.dto.CardDetailDto">
        SELECT
            a.id cardId,
            a.title,
            a.create_method createMethod,
            a.make_method makeMethod,
            a.type,
            b.content,
            b.html_content htmlContent,
            c.paper_type paperType
        FROM
            exam_card a
                LEFT JOIN
            exam_card_detail b ON a.id = b.card_id
                LEFT JOIN
            exam_task_detail c ON FIND_IN_SET(a.id, REPLACE(substring_index(substring_index(CONVERT((c.paper_attachment_ids ->>'$[*].cardId')
                                                                                                    USING utf8), ']', 1), '[',-1),'"',''))
        where a.id = #{cardId}
    </select>
    <select id="listGenericCard" resultType="com.qmth.distributed.print.business.entity.ExamCard">
        SELECT
            distinct a.id, a.title, a.type, a.create_method createMethod
        FROM
            exam_card a
        WHERE
            a.school_id = #{schoolId}
            AND a.type = #{type}
            AND a.enable = TRUE
            <if test="cardRuleId == '-1'">
                AND a.create_method != 'STANDARD'
            </if>
        order by a.id desc
    </select>
    <select id="listCustom" resultType="com.qmth.distributed.print.business.entity.ExamCard">
        SELECT
            a.id, a.title, a.type, a.create_id createId, a.create_method createMethod
        FROM
            exam_card a
        WHERE
            a.school_id = #{schoolId}
            AND a.type = #{type}
            AND a.course_code = #{courseCode}
            AND NOT EXISTS( SELECT
                1
            FROM
                exam_task_detail b
                    LEFT JOIN exam_task et on b.exam_task_id = et.id
                    LEFT JOIN t_f_flow_approve e ON et.flow_id = e.flow_id
            WHERE
                e.status != 'CANCEL' and FIND_IN_SET(a.id, REPLACE(substring_index(substring_index(CONVERT((b.paper_attachment_ids ->>'$[*].cardId') USING utf8), ']', 1), '[',-1),'"','')))
        order by a.create_time desc
    </select>
    <select id="getCardDetailBySelect" resultType="com.qmth.distributed.print.business.bean.dto.CardDetailDto">
        SELECT
            a.id cardId,
            a.title,
            a.create_method createMethod,
            a.make_method makeMethod,
            a.type,
            b.content,
            b.html_content htmlContent
        FROM
            exam_card a
                LEFT JOIN
            exam_card_detail b ON a.id = b.card_id
        where a.id = #{cardId}
    </select>
    <select id="listSyncCardByCourseCodeAndPaperNumber"
            resultType="com.qmth.distributed.print.business.bean.dto.SyncExamCardDto">
        SELECT
            c.id,
            a.school_id schoolId,
            a.course_code courseCode,
            a.paper_number paperNumber,
            a.sequence,
            c.title,
            d.content
        FROM
            exam_task a
                left JOIN
            exam_task_detail b ON a.id = b.exam_task_id
                JOIN
            exam_card c ON FIND_IN_SET(c.id, REPLACE(substring_index(substring_index(CONVERT((b.paper_attachment_ids ->>'$[*].cardId')
                                                                                             USING utf8), ']', 1), '[',-1),'"',''))
                left JOIN
            exam_card_detail d ON c.id = d.card_id
        WHERE
            a.school_id = #{schoolId}
                AND a.course_code = #{courseCode}
                AND a.paper_number = #{paperNumber}
                AND c.status = 'SUBMIT'
                AND (c.make_method != 'SELECT'
                    or (c.make_method != 'SELECT' and c.type = 'GENERIC' and c.create_method != 'UPLOAD'))
    </select>
    <select id="listPage" resultMap="BaseResultMap">
        select * from exam_card
        <where>
            school_id = #{schoolId}
            <if test="type != null and type != ''">
                and type = #{type}
            </if>
            <if test="title != null and title != ''">
                and title like concat('%', #{title}, '%')
            </if>
            <if test="createMethod != null and createMethod != ''">
                and create_method = #{createMethod}
            </if>
            <if test="enable != null">
                and enable = #{enable}
            </if>
            <if test="createStartTime != null and createStartTime != ''">
                and create_time &gt;= #{createStartTime}
            </if>
            <if test="createEndTime != null and createEndTime != ''">
                and create_time &lt;= #{createEndTime}
            </if>
        </where>
    </select>
</mapper>
