<?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.specialArea.mapper.SoftwareFactStatisPmsMapper">

    <select id="selectPmsProjectList" resultType="com.microservices.pms.specialArea.domain.ZonePmsProject">
        select id,
               project_name as projectName,
               project_assignee_id projectAssigneeId,
               pms_enterprise_id pmsEnterpriseId,
               dept_id deptId,
               `status`,
               (select enterprise_name from pms_enterprise where id = pms_enterprise_id)    as pmsEnterpriseName,
               (select nick_name from sys_user where gitlink_user_id = project_assignee_id) as projectAssigneeName
        from pms_project
        where is_deleted = 0
    </select>

    <select id="selectPmsProjectListByEnterpriseId"
            resultType="com.microservices.pms.specialArea.domain.ZonePmsProject">
        select id,
               project_name                                                                    as projectName,
               project_assignee_id                                                                projectAssigneeId,
               pms_enterprise_id                                                                  pmsEnterpriseId,
               dept_id                                                                            deptId,
               `status`,
               (select enterprise_name from pms_enterprise where id = pp.pms_enterprise_id)    as pmsEnterpriseName,
               (select nick_name from sys_user where gitlink_user_id = pp.project_assignee_id) as projectAssigneeName,
               (select count(*)
                from pms_project_members
                where pms_project_id = pp.id)                                                  as projectMemberCount
        from pms_project pp
        where is_deleted = 0
          and pms_enterprise_id = #{enterpriseId}
    </select>

    <select id="countSprintByStatus" resultType="com.microservices.pms.specialArea.domain.vo.KeyValVO">
        select `status` as `key`, count(id) as val
        from pms_project_sprint
        where `status`  is not null
        <if test="projectId != null">
            and pms_project_id = #{projectId}
        </if>
        group by `status`
    </select>

    <select id="countSprintByCUES" resultType="java.lang.Long">
        select  count(id)
        from pms_project_sprint
        where  pms_project_id = #{pmProjectId} and ((create_time &gt;= #{createTimeS} and create_time &lt;= #{createTimeE})
           OR (update_time &gt;= #{updateTimeS} and update_time &lt;= #{updateTimeE}))
    </select>

    <select id="countTestCaseInSheet" resultType="java.lang.Long">
        select count(id) from pms_project_testsheet_cases
        <where>
            <if test="statusIds != null and statusIds.size() > 0">and test_status in
                <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="pmProjectId != null">and project_testsheet_id in (
                select id from pms_project_testsheet where pms_project_id = #{pmProjectId}
                )
            </if>
            <if test="createTimeS != null ">and create_time &gt;= #{createTimeS}</if>
            <if test="createTimeE != null ">and create_time &lt;= #{createTimeE}</if>
            <if test="updateTimeS != null ">and update_time &gt;= #{updateTimeS}</if>
            <if test="updateTimeE != null ">and update_time &lt;= #{updateTimeE}</if>
        </where>
    </select>

    <select id="countTestCaseBy" resultType="java.lang.Long">
        select count(id) from pms_project_testcase
        <where>
            <if test="pmsProjectId != null">pms_project_id=#{pmsProjectId}</if>
        </where>
    </select>

    <select id="selectPmsProjectById" resultType="com.microservices.pms.specialArea.domain.ZonePmsProject">
        select id,
               project_name as projectName,
               project_assignee_id projectAssigneeId,
               pms_enterprise_id pmsEnterpriseId,
               dept_id deptId,
               `status`,
               (select enterprise_name from pms_enterprise where id = pms_enterprise_id)    as pmsEnterpriseName,
               (select nick_name from sys_user where gitlink_user_id = project_assignee_id) as projectAssigneeName
        from pms_project
        where is_deleted = 0 and id = #{id}
    </select>

    <select id="selectPmsUserByGitlinkId" resultType="java.lang.String">
        select nick_name from sys_user where gitlink_user_id = #{gitlinkId}
    </select>

    <select id="countTestCaseByCUES" resultType="java.lang.Long">
        select count(pptc.id) from pms_project_testsheet_cases pptc
            left join pms_project_testcase ppt on pptc.project_testcase_id = ppt.id
        where  ppt.pms_project_id = #{pmProjectId} and ((pptc.create_time &gt;= #{createTimeS} and pptc.create_time &lt;= #{createTimeE})
            OR (pptc.update_time &gt;= #{updateTimeS} and pptc.update_time &lt;= #{updateTimeE}))
    </select>
    <select id="selectPmsProjectListByDict" resultType="java.lang.Long">
        select dict_value from sys_dict_data where dict_type = 'xjy_zone_project_list'
    </select>
</mapper>