<?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.yanzhu.jh.project.mapper.SurProjectBuildNodeDataMapper">
    
    <resultMap type="SurProjectBuildNodeData" id="SurProjectBuildNodeDataResult">
        <result property="id"    column="id"    />
        <result property="nodeId"    column="node_id"    />
        <result property="projectId"    column="project_id"    />
        <result property="startDate"    column="start_date"    />
        <result property="planStartDate"    column="plan_start_date"    />
        <result property="endDate"    column="end_date"    />
        <result property="planEndDate"    column="plan_end_date"    />
        <result property="status"    column="status"    />
        <result property="remark"    column="remark"    />
        <result property="files"    column="files"    />
        <result property="isDel"    column="is_del"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>
    <resultMap type="SurProjectBuildNodeData" id="SurProjectBuildNodeDataResultNode">
        <result property="id"    column="id"    />
        <result property="nodeId"    column="node_id"    />
        <result property="projectId"    column="project_id"    />
        <result property="startDate"    column="start_date"    />
        <result property="planStartDate"    column="plan_start_date"    />
        <result property="planEndDate"    column="plan_end_date"    />
        <result property="endDate"    column="end_date"    />
        <result property="status"    column="status"    />
        <result property="remark"    column="remark"    />
        <result property="files"    column="files"    />
        <result property="isDel"    column="is_del"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <association property="baseBuildNode" javaType="baseBuildNode">
            <result property="id"    column="node_id"    />
            <result property="nodeText"    column="node_text"    />
            <result property="nodeLvl"    column="node_lvl"    />
            <result property="nodeType"    column="node_type"    />
        </association>
        <association property="project" javaType="SurProject">
            <result property="id"    column="project_id"    />
            <result property="projectName" column="projectName"/>
        </association>
    </resultMap>

    <sql id="selectSurProjectBuildNodeDataVo">
        select id, node_id, project_id, start_date, plan_start_date, end_date, plan_end_date, status, remark, files, is_del, create_by, create_time, update_by, update_time from sur_project_build_node_data
    </sql>
    <sql id="selectSurProjectBuildNodeDataVoByNode">
        SELECT * FROM  vw_sur_project_build_node_data_all
    </sql>
    <select id="selectSurProjectBuildNodeDataList" parameterType="SurProjectBuildNodeData" resultMap="SurProjectBuildNodeDataResultNode">
        <include refid="selectSurProjectBuildNodeDataVoByNode"/>
        <where>  
            <if test="nodeId != null "> and node_id = #{nodeId}</if>
            <if test="projectId != null "> and project_id = #{projectId}</if>
            <if test="startDate != null "> and start_date = #{startDate}</if>
            <if test="planStartDate != null "> and plan_start_date = #{planStartDate}</if>
            <if test="endDate != null "> and end_date = #{endDate}</if>
            <if test="planEndDate != null "> and plan_end_date = #{planEndDate}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="files != null  and files != ''"> and files = #{files}</if>
            <if test="isDel != null "> and is_del = #{isDel}</if>
        </where>
    </select>
    
    <select id="selectSurProjectBuildNodeDataById" parameterType="Long" resultMap="SurProjectBuildNodeDataResult">
        <include refid="selectSurProjectBuildNodeDataVo"/>
        where id = #{id}
    </select>
        
    <insert id="insertSurProjectBuildNodeData" parameterType="SurProjectBuildNodeData" useGeneratedKeys="true" keyProperty="id">
        insert into sur_project_build_node_data
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nodeId != null">node_id,</if>
            <if test="projectId != null">project_id,</if>
            <if test="startDate != null">start_date,</if>
            <if test="planStartDate != null">plan_start_date,</if>
            <if test="endDate != null">end_date,</if>
            <if test="planEndDate != null">plan_end_date,</if>
            <if test="status != null">status,</if>
            <if test="remark != null">remark,</if>
            <if test="files != null">files,</if>
            <if test="isDel != null">is_del,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="nodeId != null">#{nodeId},</if>
            <if test="projectId != null">#{projectId},</if>
            <if test="startDate != null">#{startDate},</if>
            <if test="planStartDate != null">#{planStartDate},</if>
            <if test="endDate != null">#{endDate},</if>
            <if test="planEndDate != null">#{planEndDate},</if>
            <if test="status != null">#{status},</if>
            <if test="remark != null">#{remark},</if>
            <if test="files != null">#{files},</if>
            <if test="isDel != null">#{isDel},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateSurProjectBuildNodeData" parameterType="SurProjectBuildNodeData">
        update sur_project_build_node_data
        <trim prefix="SET" suffixOverrides=",">
            <if test="nodeId != null">node_id = #{nodeId},</if>
            <if test="projectId != null">project_id = #{projectId},</if>
            start_date = #{startDate},
            plan_start_date = #{planStartDate},
            end_date = #{endDate},
            plan_end_date = #{planEndDate},
            <if test="status != null">status = #{status},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="files != null">files = #{files},</if>
            <if test="isDel != null">is_del = #{isDel},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSurProjectBuildNodeDataById" parameterType="Long">
        delete from sur_project_build_node_data where id = #{id}
    </delete>

    <delete id="deleteSurProjectBuildNodeDataByIds" parameterType="String">
        delete from sur_project_build_node_data where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectScheduledAlerts"  parameterType="SurProjectBuildNodeData" resultMap="SurProjectBuildNodeDataResultNode">
        SELECT * FROM vw_sur_project_build_node_data_current WHERE id IN (
            SELECT MAX(id) FROM vw_sur_project_build_node_data_current
            WHERE LENGTH(node_lvl)=2
            <if test="projectId !=null and projectId !=0">and project_id=#{projectId}</if>
            <if test="id!=null and id!=0">and project_id in (SELECT id FROM sur_project WHERE deptid=#{id})</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and project_id in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
            GROUP BY project_id) order by projectSort
    </select>

    <select id="selectCurrent" parameterType="SurProjectBuildNodeData" resultMap="SurProjectBuildNodeDataResultNode">

        SELECT a.* FROM vw_sur_project_build_node_data_current a,(
        SELECT * FROM vw_sur_project_build_node_data_current WHERE id IN (
        SELECT MAX(id) FROM vw_sur_project_build_node_data_current
        WHERE LENGTH(node_lvl)=2
        <if test="projectId !=null and projectId !=0">and project_id=#{projectId}</if>
        GROUP BY project_id)) b WHERE a.project_id=b.project_id AND LEFT(a.node_lvl,2)=b.node_lvl
    </select>
    <select id="countCompletionRate" parameterType="SurProjectBuildNodeData" resultMap="SurProjectBuildNodeDataResultNode">
        SELECT IF(b.cnt,b.cnt,0) id ,IF(c.cnt,c.cnt,0) node_id,a.project_id,a.projectName,a.node_text,a.node_lvl FROM (
              SELECT * FROM vw_sur_project_build_node_data_current WHERE id IN (
                  SELECT MAX(id) FROM vw_sur_project_build_node_data_current
                  WHERE LENGTH(node_lvl)=2
                    <if test="projectId !=null and projectId !=0">and project_id=#{projectId}</if>
                    <if test="id!=null and id!=0">and project_id in (SELECT id FROM sur_project WHERE deptid=#{id})</if>
                    <if test="prjIds !=null and prjIds.size()>0">
                        and project_id in
                        <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>
                  GROUP BY project_id)) a LEFT JOIN (



            SELECT a.project_id,COUNT(1) cnt FROM vw_sur_project_build_node_data_current a,
                  (SELECT * FROM vw_sur_project_build_node_data_current WHERE id IN (
                      SELECT MAX(id) FROM vw_sur_project_build_node_data_current
                      WHERE LENGTH(node_lvl)=2
                        <if test="projectId !=null and projectId !=0">and project_id=#{projectId}</if>
                        <if test="id!=null and id!=0">and project_id in (SELECT id FROM sur_project WHERE deptid=#{id})</if>
                        <if test="prjIds !=null and prjIds.size()>0">
                            and project_id in
                            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                                #{item}
                            </foreach>
                        </if>
                      GROUP BY project_id)) b WHERE a.project_id=b.project_id AND LEFT(a.node_lvl,2)=b.node_lvl AND LENGTH(a.node_lvl)>2
            GROUP BY a.project_id) b ON a.project_id =b.project_id

            LEFT JOIN(
            SELECT a.project_id,COUNT(1) cnt FROM vw_sur_project_build_node_data_current a,
              (SELECT * FROM vw_sur_project_build_node_data_current WHERE id IN (
                  SELECT MAX(id) FROM vw_sur_project_build_node_data_current
                  WHERE LENGTH(node_lvl)=2
                    <if test="projectId !=null and projectId !=0">and project_id=#{projectId}</if>
                    <if test="id!=null and id!=0">and project_id in (SELECT id FROM sur_project WHERE deptid=#{id})</if>
                    <if test="prjIds !=null and prjIds.size()>0">
                        and project_id in
                        <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>
                  GROUP BY project_id)) b WHERE a.project_id=b.project_id AND LEFT(a.node_lvl,2)=b.node_lvl AND LENGTH(a.node_lvl)>2 AND a.end_date IS NOT NULL
            GROUP BY a.project_id
        ) c ON a.project_id=c.project_id
    </select>

    <select id="queryByProjectType" parameterType="Long" resultMap="SurProjectBuildNodeDataResultNode">
        <include refid="selectSurProjectBuildNodeDataVoByNode"/>
        WHERE is_del=0 AND project_id IN (
        SELECT id FROM sur_project WHERE isdel=0 AND projecttype=#{type})
    </select>


    <select id="queryFinishProject" parameterType="Long" resultMap="SurProjectBuildNodeDataResultNode">
        <include refid="selectSurProjectBuildNodeDataVoByNode"/>
        WHERE is_del=0 AND project_id =#{projectId} and end_date is not null
    </select>
</mapper>