<?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.microservices.pms.project.mapper.PmsProjectTestcaseMapper">

    <resultMap type="PmsProjectTestcase" id="PmsProjectTestcaseResult">
        <result property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="assigneeGitlinkId" column="assignee_gitlink_id"/>
        <result property="pmsProjectId" column="pms_project_id"/>
        <result property="typeId" column="type_id"/>
        <result property="tagIds" column="tag_ids"/>
        <result property="pmsModuleId" column="pms_module_id"/>
        <result property="remark" column="remark"/>
        <result property="fileIdentifiers" column="file_identifiers"/>
        <result property="preconditions" column="preconditions"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="delFlag" column="del_flag"/>
        <result property="index" column="index"/>
        <result property="reservedField1" column="reserved_field_1"/>
        <result property="reservedField2" column="reserved_field_2"/>
        <result property="reservedField3" column="reserved_field_3"/>
        <result property="identifier" column="identifier"/>
        <result property="productReqSpecsId" column="product_req_specs_id"/>
    </resultMap>

    <sql id="selectPmsProjectTestcaseVo">
        select id,
               title,
               assignee_gitlink_id,
               pms_project_id,
               type_id,
               tag_ids,
               pms_module_id,
               remark,
               file_identifiers,
               preconditions,
               update_time,
               update_by,
               create_time,
               create_by,
               del_flag,
               `index`,
               reserved_field_1,
               reserved_field_2,
               reserved_field_3,
               identifier,
               product_req_specs_id
        from pms_project_testcase
    </sql>

    <select id="selectPmsProjectTestcaseList" parameterType="PmsProjectTestcase" resultMap="PmsProjectTestcaseResult">
        <include refid="selectPmsProjectTestcaseVo"/>
        <include refid="selectWhere"/>
        order by create_time desc
    </select>

    <sql id="selectWhere">
        <where>
            <if test="title != null  and title != ''">and title like concat('%', #{title}, '%')</if>
            <if test="assigneeGitlinkId != null ">and assignee_gitlink_id = #{assigneeGitlinkId}</if>
            <if test="pmsProjectId != null ">and pms_project_id = #{pmsProjectId}</if>
            <if test="typeId != null ">and type_id = #{typeId}</if>
            <if test="tagId != null  and tagId != ''">and FIND_IN_SET(#{tagId},tag_ids)</if>
            <if test="index != null ">and `index` = #{index}</if>
            <if test="productReqSpecsId != null ">and `product_req_specs_id` = #{productReqSpecsId}</if>
            <choose>
                <when test="childrenModuleIdList != null">
                    <if test="childrenModuleIdList != null ">
                        and pms_module_id in
                        <foreach item="moduleId" collection="childrenModuleIdList" open="(" separator="," close=")">
                            #{moduleId}
                        </foreach>
                    </if>
                </when>
                <when test="idsList != null">
                    <if test="idsList != null ">
                        and id in
                        <foreach item="id" collection="idsList" open="(" separator="," close=")">
                            #{id}
                        </foreach>
                    </if>
                </when>
                <otherwise>
                    <if test="pmsModuleId != null ">
                        <if test="pmsModuleId != 0">and pms_module_id = #{pmsModuleId}</if>
                        <if test="pmsModuleId == 0">and pms_module_id is null</if>
                    </if>
                </otherwise>
            </choose>
        </where>
    </sql>

    <select id="selectPmsProjectTestcaseById" parameterType="Long" resultMap="PmsProjectTestcaseResult">
        <include refid="selectPmsProjectTestcaseVo"/>
        where id = #{id}
    </select>
    <select id="selectPmsProjectTestcaseMaxIndexByProjectId" resultType="java.lang.Long">
        select max(`index`)
        from pms_project_testcase
        where pms_project_id = #{pmsProjectId}
    </select>
    <select id="selectPmsProjectTestcaseCount" resultType="java.lang.Integer">
        select count(0) from pms_project_testcase
        <include refid="selectWhere"/>
    </select>

    <insert id="insertPmsProjectTestcase" parameterType="PmsProjectTestcase" useGeneratedKeys="true" keyProperty="id">
        insert into pms_project_testcase
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title != null and title != ''">title,</if>
            <if test="assigneeGitlinkId != null">assignee_gitlink_id,</if>
            <if test="pmsProjectId != null">pms_project_id,</if>
            <if test="typeId != null">type_id,</if>
            <if test="tagIds != null">tag_ids,</if>
            <if test="pmsModuleId != null">pms_module_id,</if>
            <if test="remark != null">remark,</if>
            <if test="fileIdentifiers != null">file_identifiers,</if>
            <if test="preconditions != null">preconditions,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="index != null">`index`,</if>
            <if test="reservedField1 != null">reserved_field_1,</if>
            <if test="reservedField2 != null">reserved_field_2,</if>
            <if test="reservedField3 != null">reserved_field_3,</if>
            <if test="identifier != null">identifier,</if>
            <if test="productReqSpecsId != null">product_req_specs_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="title != null and title != ''">#{title},</if>
            <if test="assigneeGitlinkId != null">#{assigneeGitlinkId},</if>
            <if test="pmsProjectId != null">#{pmsProjectId},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="tagIds != null">#{tagIds},</if>
            <if test="pmsModuleId != null">#{pmsModuleId},</if>
            <if test="remark != null">#{remark},</if>
            <if test="fileIdentifiers != null">#{fileIdentifiers},</if>
            <if test="preconditions != null">#{preconditions},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="index != null">#{index},</if>
            <if test="reservedField1 != null">#{reservedField1},</if>
            <if test="reservedField2 != null">#{reservedField2},</if>
            <if test="reservedField3 != null">#{reservedField3},</if>
            <if test="identifier != null">#{identifier},</if>
            <if test="productReqSpecsId != null">#{productReqSpecsId},</if>
        </trim>
    </insert>

    <update id="updatePmsProjectTestcase" parameterType="PmsProjectTestcase">
        update pms_project_testcase
        <trim prefix="SET" suffixOverrides=",">
            <if test="title != null and title != ''">title = #{title},</if>
            <if test="assigneeGitlinkId != null">assignee_gitlink_id = #{assigneeGitlinkId},</if>
            <if test="pmsProjectId != null">pms_project_id = #{pmsProjectId},</if>
            <if test="typeId != null">type_id = #{typeId},</if>
            tag_ids = #{tagIds},
            pms_module_id = #{pmsModuleId},
            <if test="remark != null">remark = #{remark},</if>
            <if test="fileIdentifiers != null">file_identifiers = #{fileIdentifiers},</if>
            <if test="preconditions != null">preconditions = #{preconditions},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="index != null">`index` = #{index},</if>
            <if test="reservedField1 != null">reserved_field_1 = #{reservedField1},</if>
            <if test="reservedField2 != null">reserved_field_2 = #{reservedField2},</if>
            <if test="reservedField3 != null">reserved_field_3 = #{reservedField3},</if>
            <if test="identifier != null">identifier = #{identifier},</if>
            <if test="productReqSpecsId != null">product_req_specs_id = #{productReqSpecsId},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updatePmsProjectTestcaseToNoModule">
        update pms_project_testcase
        set pms_module_id=null,
            update_by=#{currentUsername},
            update_time=#{nowDate}
        where pms_module_id = #{moduleId}
    </update>
    <update id="batchUpdatePmsProjectTestcase">
        update pms_project_testcase
        <trim prefix="SET" suffixOverrides=",">
            <if test="assigneeGitlinkIdIsChange">assignee_gitlink_id = #{assigneeGitlinkId},</if>
            <if test="typeIdIsChange">type_id = #{typeId},</if>
            <if test="tagIdsIsChange">tag_ids = #{tagIds},</if>
            <if test="pmsModuleIdIsChange">pms_module_id = #{pmsModuleId},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="productReqSpecsId != null">product_req_specs_id = #{productReqSpecsId},</if>
        </trim>
        where id in
        <foreach item="id" collection="idList" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <delete id="deletePmsProjectTestcaseById" parameterType="Long">
        delete
        from pms_project_testcase
        where id = #{id}
    </delete>

    <delete id="deletePmsProjectTestcaseByIds" parameterType="String">
        delete from pms_project_testcase where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <delete id="deletePmsProjectTestcaseByProjectId">
        delete from pms_project_testcase where pms_project_id = #{projectId}
    </delete>

    <select id="selectPmsProjectTestcaseForTestsheetList" parameterType="PmsProjectTestcase" resultMap="PmsProjectTestcaseResult">
        select DISTINCT a.*
        from pms_project_testcase a
        LEFT JOIN pms_project_testsheet_cases b ON b.project_testcase_id = a.id
        <where>
            <if test="params.projectTestsheetId != null "><!-- 测试单检索 -->
                AND b.project_testsheet_id = #{params.projectTestsheetId}
            </if>
            <if test="title != null  and title != ''">and a.title like concat('%', #{title}, '%')</if>
            <if test="assigneeGitlinkId != null ">and a.assignee_gitlink_id = #{assigneeGitlinkId}</if>
            <if test="pmsProjectId != null ">and a.pms_project_id = #{pmsProjectId}</if>
            <if test="typeId != null ">and a.type_id = #{typeId}</if>
            <if test="tagIds != null ">and FIND_IN_SET(#{tagIds}, a.tag_ids)</if>
            <if test="index != null ">and a.`index` = #{index}</if>
            <if test="params.testerId != null ">and b.tester_id = #{params.testerId}</if>
            <if test="params.testStatus != null ">and b.test_status = #{params.testStatus}</if>
            <choose>
                <when test="pmsModuleId  == 0">
                    and a.pms_module_id is null
                </when>
                <otherwise>
                    <if test="childrenModuleIdList != null ">
                        and a.pms_module_id in
                        <foreach item="moduleId" collection="childrenModuleIdList" open="(" separator="," close=")">
                            #{moduleId}
                        </foreach>
                    </if>
                </otherwise>
            </choose>
        </where>
        order by a.create_time desc
    </select>

    <select id="selectPmsProjectTestcaseForSelectList" parameterType="PmsProjectTestcase" resultMap="PmsProjectTestcaseResult">
        select DISTINCT a.*
        from pms_project_testcase a
        <where>
            a.pms_project_id = #{pmsProjectId} and not exists(SELECT * from pms_project_testsheet_cases b where b.project_testsheet_id = #{params.projectTestsheetId}  and b.project_testcase_id = a.id)
            <if test="title != null  and title != ''">and a.title like concat('%', #{title}, '%')</if>
            <if test="assigneeGitlinkId != null ">and a.assignee_gitlink_id = #{assigneeGitlinkId}</if>
            <if test="typeId != null ">and a.type_id = #{typeId}</if>
            <if test="tagIds != null ">and FIND_IN_SET(#{tagIds}, a.tag_ids)</if>
            <if test="index != null ">and a.`index` = #{index}</if>
            <choose>
                <when test="pmsModuleId  == 0">
                    and a.pms_module_id is null
                </when>
                <otherwise>
                    <if test="childrenModuleIdList != null ">
                        and a.pms_module_id in
                        <foreach item="moduleId" collection="childrenModuleIdList" open="(" separator="," close=")">
                            #{moduleId}
                        </foreach>
                    </if>
                </otherwise>
            </choose>
        </where>
        order by a.create_time desc
    </select>
    <select id="selectPmsProjectTestcaseIdsByTestSheetIds" resultType="java.lang.Long">
        select DISTINCT(project_testcase_id)
        from pms_project_testsheet_cases
        where project_testsheet_id in
        <foreach item="testSheetId" collection="testSheetIds" open="(" separator="," close=")">
            #{testSheetId}
        </foreach>
    </select>
    <select id="selectProjectIdListInTestcaseIdList" resultType="java.lang.Long">
        SELECT distinct pms_project_id FROM `pms_project_testcase` where id in
        <foreach item="id" collection="idList" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>