<?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="org.springblade.manager.mapper.ProjectInfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="projectInfoResultMap" type="org.springblade.manager.entity.ProjectInfo">
        <result column="id" property="id"/>
        <result column="create_time" property="createTime"/>
        <result column="create_user" property="createUser"/>
        <result column="create_dept" property="createDept"/>
        <result column="update_user" property="updateUser"/>
        <result column="update_time" property="updateTime"/>
        <result column="status" property="status"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="project_name" property="projectName"/>
        <result column="project_alias" property="projectAlias"/>
        <result column="project_number" property="projectNumber"/>
        <result column="project_gist" property="projectGist"/>
        <result column="project_grade" property="projectGrade"/>
        <result column="plan_start_time" property="planStartTime"/>
        <result column="plan_end_time" property="planEndTime"/>
        <result column="actual_start_time" property="actualStartTime"/>
        <result column="actual_end_time" property="actualEndTime"/>
        <result column="construcetion_date" property="construcetionDate"/>
        <result column="estimated_amount" property="estimatedAmount"/>
        <result column="project_mileage" property="projectAllMileage"/>
        <result column="project_pavement" property="projectPavement"/>
        <result column="project_subgrade" property="projectSubgrade"/>
        <result column="project_remark" property="projectRemark"/>
        <result column="state" property="state"/>
        <result column="is_open_sign" property="isOpenSign"/>
        <result column="reference_wbs_template_id" property="referenceWbsTemplateId"/>
        <result column="reference_wbs_template_type" property="referenceWbsTemplateType"/>
    </resultMap>

    <resultMap id="singPfxManagementResultMap" type="org.springblade.manager.vo.SingPfxManagementVO">
        <result column="id" property="projectId"/>
        <result column="project_name" property="projectName"/>
        <result column="personalCount" property="personalCount"/>
        <result column="project_name" property="projectName"/>
        <result column="contractId" property="contractId"/>
    </resultMap>

    <select id="singPfxManagementPage" resultMap="singPfxManagementResultMap">
        select
            pi.id,
            pi.project_name,
            count(
              (
                select
                  spf.id
                from
                  m_sign_pfx_file AS spf
                left join m_project_assignment_user AS pau on spf.certificate_user_id = pau.user_id
                where
                  spf.is_deleted = 0
                and pau.is_deleted = 0
                and pau.project_id = pi.id
                and spf.certificate_type = 1
              )
            ) AS personalCount,
            count(
              (
                select
                  spf.id
                from m_sign_pfx_file AS spf
                left join m_sign_pfx_deputy AS spd ON spf.id = spd.sign_pfx_file_id
                where
                  spf.is_deleted = 0
                and spd.is_deleted = 0
                and spd.project_id = pi.id
                and spf.certificate_type = 2
              )
            ) AS enterpriseCount,
            (select id from m_contract_info where is_deleted = 0 and p_id = pi.id limit 1) AS contractId
        from m_project_info AS pi where pi.is_deleted = 0
        <if test="vo.projectId != null">
            and pi.id = #{vo.projectId}
        </if>
        limit #{current},#{size}
    </select>

    <select id="selectProjectList" resultMap="projectInfoResultMap">
        select id, project_name, project_alias, project_number from m_project_info where is_deleted = 0 and id in
        <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
            #{projectId}
        </foreach>
    </select>

    <update id="updateTemplateIdById">
        UPDATE  m_project_info
        SET reference_wbs_template_id = #{referencePrivateWbsProjectId},
            reference_wbs_template_type = #{type}
        WHERE
        id = #{projectId}
        AND is_deleted = 0
        AND status = 1
    </update>


    <select id="selectProjectInfoPage" resultMap="projectInfoResultMap">
        select * from m_project_info where is_deleted = 0
    </select>
    <select id="selectOneAndWbsTypeById" resultType="org.springblade.manager.entity.ProjectInfo">
        SELECT *,
        (select wbs_type from m_wbs_info )
        FROM m_project_info
        WHERE id = #{id}
    </select>

</mapper>
