<?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="cn.com.oa.mapper.ProjectInfoMapper">

    <select id="getProjectCount" resultType="java.lang.Integer">
        SELECT
            count( ppi.universalid ) AS projectCount
        FROM
            pm_project_info ppi
        WHERE
            1 = 1
          AND ppi.state IN ( 3, 5, 6, 7, 8 )
        <if test="projectInfo.unitId!=null">
            AND ppi.unit_id = #{projectInfo.unitId}
        </if>
    </select>

    <select id="getWeekProjectTaskCount" resultType="java.util.Map">
        SELECT
            CASE DAYOFWEEK(DATE_ADD(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, INTERVAL day_offset DAY))
                WHEN 2 THEN '周一'
                WHEN 3 THEN '周二'
                WHEN 4 THEN '周三'
                WHEN 5 THEN '周四'
                WHEN 6 THEN '周五'
                ELSE '未知'
                END AS dayOfWeek,
            SUM(CASE
                    WHEN DATE(pt.plan_end_date) = DATE_ADD(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, INTERVAL day_offset DAY)
                    <if test="projectInfo.unitId!=null">
                        AND ppi.unit_id = #{projectInfo.unitId}
                    </if>
                THEN 1
                ELSE 0
                END) AS planEndCount,
            SUM(CASE
                    WHEN DATE(pt.prac_end_date) = DATE_ADD(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, INTERVAL day_offset DAY)
                    <if test="projectInfo.unitId!=null">
                        AND ppi.unit_id = #{projectInfo.unitId}
                    </if>
                THEN 1
                ELSE 0
                END) AS pracEndCount
        FROM
            (SELECT 0 AS day_offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS days_of_week
                LEFT JOIN
            pm_task pt
            ON (DATE(pt.plan_end_date) = DATE_ADD(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, INTERVAL day_offset DAY)
                OR DATE(pt.prac_end_date) = DATE_ADD(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, INTERVAL day_offset DAY))
                LEFT JOIN
            pm_project_info ppi
            ON pt.project_id = ppi.universalid
        GROUP BY
            day_offset
        ORDER BY
            day_offset;
    </select>

    <select id="getProjectTaskCount" resultType="java.util.Map">
        SELECT
            pi.project_name AS projectName,
            COUNT(t.universalid) AS taskCount
        FROM
            pm_project_info pi
                LEFT JOIN
            pm_task t ON pi.universalid = t.project_id
        WHERE 1 = 1 AND pi.state IN ( 3, 5, 6, 7, 8 )
        <if test="projectInfo.unitId!=null">
            AND pi.unit_id = #{projectInfo.unitId}
        </if>
        GROUP BY
            pi.project_name, pi.universalid
        ORDER BY
            pi.project_name;
    </select>
</mapper>
