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

    <select id="selectForgeProject" resultType="com.microservices.pms.specialArea.domain.ForgeProject">
        select pm_project_id,pm_issue_type from issues
        <where>
            <if test="pmIssueType != null ">and pm_issue_type = #{pmIssueType}</if>
            <if test="createTimeS != null ">and created_on &gt;= #{createTimeS}</if>
            <if test="createTimeE != null ">and created_on &lt;= #{createTimeE}</if>
            <if test="updateTimeS != null ">and updated_on &gt;= #{updateTimeS}</if>
            <if test="updateTimeE != null ">and updated_on &lt;= #{updateTimeE}</if>
            <if test="pmIssueType != null ">and pm_issue_type = #{pmIssueType}</if>
            <if test="pmProjectId != null ">and pm_project_id = #{pmProjectId}</if>
            <if test="statusIds != null and statusIds.size() > 0">and status_id in
                <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>

    <select id="countForgeProject" resultType="java.lang.Long">
        select count(id) from issues
        <where>
            <if test="createTimeS != null ">and created_on &gt;= #{createTimeS}</if>
            <if test="createTimeE != null ">and created_on &lt;= #{createTimeE}</if>
            <if test="updateTimeS != null ">and updated_on &gt;= #{updateTimeS}</if>
            <if test="updateTimeE != null ">and updated_on &lt;= #{updateTimeE}</if>
            <if test="pmIssueType != null ">and pm_issue_type = #{pmIssueType}</if>
            <if test="pmProjectId != null ">and pm_project_id = #{pmProjectId}</if>
            <if test="statusIds != null and statusIds.size() > 0">and status_id in
                <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="countForgeProjectByCUES" resultType="java.lang.Long">
        select count(id) from issues
        where  pm_project_id = #{pmProjectId} and ((created_on &gt;= #{createTimeS} and created_on &lt;= #{createTimeE})
           OR (updated_on &gt;= #{updateTimeS} and updated_on &lt;= #{updateTimeE}))
    </select>

    <select id="countForgeProjectByProject" resultType="java.lang.Long">
        select pm_project_id from (select pm_project_id, count(id) as val from issues
        where pm_project_id is not null
        <if test="pmIssueType != null ">and pm_issue_type = #{pmIssueType}</if>
        <if test="pmProjectId != null ">and pm_project_id = #{pmProjectId}</if>
        <if test="statusIds != null and statusIds.size() > 0">and status_id in
            <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        group by pm_project_id
        order by val DESC LIMIT 1) tmp
    </select>

    <select id="countForgeProjectByAssigner" resultType="java.lang.Long">
        select assigner_id from (SELECT ia.assigner_id, COUNT(i.id) AS issue_count
        FROM issue_assigners ia
        JOIN issues i ON ia.issue_id = i.id
        WHERE ia.assigner_id is not null
        <if test="pmIssueType != null ">and i.pm_issue_type = #{pmIssueType}</if>
        <if test="pmProjectId != null ">and i.pm_project_id = #{pmProjectId}</if>
        <if test="statusIds != null and statusIds.size() > 0">and i.status_id in
            <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY ia.assigner_id
        ORDER BY issue_count DESC
        LIMIT 1
        ) tmp
    </select>


</mapper>