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

    <!-- 通用查询映射结果 -->
    <resultMap id="wbsTreeResultMap" type="org.springblade.manager.entity.WbsTreePrivate">
        <result column="project_id" property="projectId"/>
        <result column="p_key_id" property="pKeyId"/>
        <result column="id" property="id"/>
        <result column="w_id" property="wbsId"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="status" property="status"/>
        <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="type" property="type"/>
        <result column="table_type" property="tableType"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="parent_id" property="parentId"/>
        <result column="ancestors" property="ancestors"/>
        <result column="dept_category" property="deptCategory"/>
        <result column="dept_name" property="deptName"/>
        <result column="full_name" property="fullName"/>
        <result column="sort" property="sort"/>
        <result column="remark" property="remark"/>
        <result column="unique_code" property="uniqueCode"/>
        <result column="partition_code" property="partitionCode"/>
        <result column="is_concrete" property="isConcrete"/>
        <result column="is_expernode" property="isExpernode"/>
        <result column="table_owner" property="tableOwner"/>
        <result column="major_data_type" property="majorDataType"/>
        <result column="init_table_name" property="initTableName"/>
        <result column="is_link_table" property="isLinkTable"/>
    </resultMap>

    <resultMap id="resultMap2" type="org.springblade.manager.vo.WbsTreePrivateVO2">
        <result column="pKeyId" property="pKeyId"/>
        <result column="wbs_id" property="wbsId"/>
        <result column="wbs_type" property="wbsType"/>
        <result column="project_id" property="projectId"/>
        <result column="project_name" property="projectName"/>
    </resultMap>

    <resultMap id="treeNodeResultMap" type="org.springblade.manager.vo.TreeNodeVO">
        <id column="id" property="id"/>
        <result column="parent_id" property="parentId"/>
        <result column="title" property="title"/>
        <result column="value" property="value"/>
        <result column="key" property="key"/>
        <result column="has_children" property="hasChildren"/>
        <result column="type" property="type"/>
        <result column="wbsType" property="wbsType"/>
        <result column="primaryKeyId" property="primaryKeyId"/>
        <result column="deptCategory" property="deptCategory"/>
        <result column="isExistForm" property="isExistForm"/>
    </resultMap>
    <insert id="insertCombination1">
        INSERT INTO m_wbs_tree_private(
        p_key_id,id,wbs_id,wbs_type,project_id,tenant_id,parent_id,ancestors,dept_category,dept_name,full_name,sort,remark,`type`,
        table_type,create_time,create_user,create_dept,update_user,update_time,`STATUS`,is_deleted,
        unique_code,partition_code,is_expernode,is_concrete,table_owner,major_data_type,init_table_name,is_link_table)
        VALUES(
        #{snowId},#{wbsTree.id},#{wbsTree.wbsId},#{wbsType},#{projectId},#{wbsTree.tenantId},#{wbsTree.parentId},#{wbsTree.ancestors},
        #{wbsTree.deptCategory},#{wbsTree.deptName},#{wbsTree.fullName},#{wbsTree.sort},#{wbsTree.remark},#{wbsTree.type},
        #{wbsTree.tableType},#{wbsTree.createTime},#{wbsTree.createUser},#{wbsTree.createDept},#{wbsTree.updateUser},#{wbsTree.updateTime},
        #{wbsTree.status},#{wbsTree.isDeleted},#{wbsTree.uniqueCode},#{wbsTree.partitionCode},#{wbsTree.isExpernode},#{wbsTree.isConcrete},
        #{wbsTree.tableOwner},#{wbsTree.majorDataType},#{wbsTree.initTableName},#{wbsTree.isLinkTable})
    </insert>

    <insert id="insertCombination2">
        INSERT INTO m_wbs_tree_private(
        p_key_id,id,wbs_id,wbs_type,project_id,tenant_id,parent_id,ancestors,dept_category,dept_name,full_name,sort,remark,`type`,
        table_type,create_time,create_user,create_dept,update_user,update_time,`STATUS`,is_deleted,
        unique_code,partition_code,is_expernode,is_concrete,table_owner,major_data_type,init_table_name,is_link_table)
        VALUES(
        #{snowId},#{wbsTree.id},#{wbsTree.wbsId},#{wbsTree.wbsType},#{projectId},#{wbsTree.tenantId},#{wbsTree.parentId},#{wbsTree.ancestors},
        #{wbsTree.deptCategory},#{wbsTree.deptName},#{wbsTree.fullName},#{wbsTree.sort},#{wbsTree.remark},#{wbsTree.type},
        #{wbsTree.tableType},#{wbsTree.createTime},#{wbsTree.createUser},#{wbsTree.createDept},#{wbsTree.updateUser},#{wbsTree.updateTime},
        #{wbsTree.status},#{wbsTree.isDeleted},#{wbsTree.uniqueCode},#{wbsTree.partitionCode},#{wbsTree.isExpernode},#{wbsTree.isConcrete},
        #{wbsTree.tableOwner},#{wbsTree.majorDataType},#{wbsTree.initTableName},#{wbsTree.isLinkTable})
    </insert>


    <update id="deleteByCondition">
        UPDATE m_wbs_tree_private SET is_deleted = 1 WHERE
        id = #{id}
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </update>

    <update id="deleteByCondition2">
        UPDATE m_wbs_tree_private SET is_deleted = 1 WHERE
        parent_id = #{id}
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </update>

    <update id="updateByCondition">
        UPDATE m_wbs_tree_private
        SET is_deleted = 0
        WHERE id = #{id}
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </update>

    <update id="updateTableByCondition">
        UPDATE m_wbs_tree_private
        SET is_deleted = 0
        WHERE parent_id = #{id}
        AND project_id = #{projectId}
        AND wbs_id = #{wbsId}
        AND type = 2
    </update>

    <update id="updateByCondition1">
        UPDATE m_wbs_tree_private SET is_deleted = 1
        WHERE status = 1
        AND type = 2
        AND id = #{id}
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </update>

    <update id="updateByPKeyId">
    update m_wbs_tree_private
    set
    tenant_id = #{wbsTP.tenantId},
    parent_id = #{wbsTP.parentId},
    dept_category = #{wbsTP.deptCategory},
    dept_name = #{wbsTP.deptName},
    ancestors = #{wbsTP.ancestors},
    unique_code = #{wbsTP.uniqueCode},
    partition_code = #{wbsTP.partitionCode},
    major_data_type = #{wbsTP.majorDataType},
    create_user = #{wbsTP.createUser},
    create_dept = #{wbsTP.createDept},
    create_time = #{wbsTP.createTime},
    update_user = #{wbsTP.updateUser},
    update_time = #{wbsTP.updateTime},
    status = 1 ,is_deleted = 0
    where p_key_id = #{pKeyId}
    </update>

    <update id="updateSortById">
        UPDATE m_wbs_tree_private
        SET sort = #{sort}
        WHERE p_key_id = #{pKeyId}
        AND type = 1
        AND status = 1
        AND is_deleted = 0
    </update>
    <update id="updateSortById2">
        UPDATE m_wbs_tree_private
        SET sort = #{sort}
        WHERE p_key_id = #{pKeyId}
        AND type = 2
        AND status = 1
        AND is_deleted = 0
    </update>

    <update id="updateIsDeletedByPKeyId">
        UPDATE m_wbs_tree_private
        SET is_deleted = 1
        WHERE p_key_id = #{pKeyId}
        AND status = 1
        AND is_deleted = 0
    </update>

    <update id="updateBatchByPid">
        UPDATE m_wbs_tree_private
        SET dept_name = #{wbsTreePrivates.deptName},
        full_name =#{wbsTreePrivates.deptName},
        table_type = #{wbsTreePrivates.tableType},
        table_owner = #{wbsTreePrivates.tableOwner}
        WHERE
        p_key_id = #{wbsTreePrivates.pKeyId}
    </update>


    <select id="lazyTree" resultMap="treeNodeResultMap">
        SELECT
        dept.p_key_id as "primaryKeyId",
        dept.type AS "type",
        dept.table_type,
        dept.id,
        dept.parent_id,
        dept.dept_name AS title,
        dept.dept_category AS "deptCategory",
        dept.id AS "value",
        dept.id AS "key",
        (
        SELECT
        CASE WHEN count(1) > 0 THEN 1 ELSE 0 END
        FROM
        m_wbs_tree_private
        WHERE
        parent_id = dept.id and is_deleted = 0
        ) AS "has_children"
        FROM
        m_wbs_tree_private dept
        WHERE
        dept.parent_id = #{parentId} AND dept.is_deleted = 0
        <if test="tenantId!=null and tenantId!=''">
            and dept.tenant_id = #{tenantId}
        </if>
        AND dept.project_id = #{projectId}
        AND dept.wbs_id = #{wbsId}
        AND dept.type = 1
        AND status = 1
        ORDER BY dept.sort
    </select>


    <select id="selectAll" resultMap="resultMap2">
        SELECT
        wtp.p_key_id AS "pKeyId",
        wtp.wbs_id,
        wtp.wbs_type,
        wtp.project_id,
        ( SELECT project_name FROM m_project_info pi WHERE wtp.project_id = pi.id ) AS project_name
        FROM
        m_wbs_tree_private AS wtp
        WHERE
        is_deleted = 0
        AND STATUS = 1
        AND parent_id = 0
        <if test="wbsType != null and wbsType != ''">
            AND wbs_type = #{wbsType}
        </if>
        GROUP BY
        project_id
        ORDER BY
        create_time
    </select>

    <select id="tree" resultMap="treeNodeResultMap">
        select p_key_id as "primaryKeyId",
        id, parent_id, dept_name as title,type as "type", id as "value", id as "key" ,
        dept_category AS "deptCategory", is_exist_form AS "isExistForm",
        (select wbs_type from m_wbs_info where id = #{wbsId}) as "wbsType"
        from m_wbs_tree_private
        where
        is_deleted = 0
        and status = 1
        and `type` = 1
        <if test="projectId !=null and projectId != ''">
            and project_id = #{projectId}
        </if>
        <if test="wbsId!=null and wbsId!=''">
            and wbs_id = #{wbsId}
        </if>
        ORDER BY sort
    </select>

    <select id="selectByCondition" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
        p_key_id,
        id,
        wbs_id,
        wbs_type,
        project_id,
        tenant_id,
        parent_id,
        ancestors,
        dept_category,
        dept_name,
        full_name,
        sort,
        remark,
        type,
        table_type,
        create_time,
        create_user,
        create_dept,
        update_user,
        update_time,
        status,
        is_deleted,
        unique_code,
        partition_code,
        is_expernode,
        is_concrete,
        table_owner,
        major_data_type,
        init_table_name,
        is_link_table,
        excel_id,
        is_exist_form
        FROM
            m_wbs_tree_private
        WHERE
             project_id = #{projectId}
            AND wbs_id = #{wbsId}
            AND STATUS = 1
            AND is_deleted = 0
            AND id = #{id} OR (parent_id = #{id} and type = 2)
    </select>

    <select id="selectByCondition2" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
            p_key_id
        FROM
            m_wbs_tree_private
        WHERE
            id = #{id}
            AND project_id = #{projectId}
            AND wbs_id = #{wbsId}
            AND STATUS = 1
            AND is_deleted = 1
    </select>


    <select id="selectIsTable" resultType="org.springblade.manager.entity.WbsTree">
        SELECT
            p_key_id,
            id,
            wbs_id,
            wbs_type,
            project_id,
            tenant_id,
            parent_id,
            ancestors,
            dept_category,
            dept_name,
            full_name,
            sort,
            remark,
            type,
            table_type,
            create_time,
            create_user,
            create_dept,
            update_user,
            update_time,
            status,
            is_deleted,
            unique_code,
            partition_code,
            is_expernode,
            is_concrete,
            table_owner,
            major_data_type,
            init_table_name,
            is_link_table,
            excel_id,
            is_exist_form
        FROM
            m_wbs_tree_private
        WHERE
            type = 2
            AND is_deleted = 0
            AND `status` = 1
            AND parent_id = #{tableId}
            AND project_id = #{projectId}
    </select>
    <select id="selectByNodeTable" resultType="org.springblade.manager.vo.WbsNodeTableVO">
        SELECT
            wt.p_key_id AS "pKeyId",
            wt.id AS id,
            wt.dept_name AS tableName,
            wt.table_type AS tableType,
            wt.`status` AS isCreateTable ,
            wt.table_owner as tableOwner,
            wt.is_link_table,
            wt.init_table_name,
            wt.excel_id as excelId,
            wt.sort
        FROM
            m_wbs_tree_private AS wt
        WHERE
            wt.type = 2
        AND wt.is_deleted = 0
        AND wt.parent_id = #{id}
        AND wt.wbs_id = #{wbsId}
        AND wt.project_id = #{projectId}
        ORDER BY wt.sort
    </select>
    <select id="getByCondition" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
            p_key_id,
            id,
            wbs_id,
            wbs_type,
            project_id,
            tenant_id,
            parent_id,
            ancestors,
            dept_category,
            dept_name,
            full_name,
            sort,
            remark,
            type,
            table_type,
            create_time,
            create_user,
            create_dept,
            update_user,
            update_time,
            status,
            is_deleted,
            unique_code,
            partition_code,
            is_expernode,
            is_concrete,
            table_owner,
            major_data_type,
            init_table_name,
            is_link_table,
            excel_id,
            is_exist_form
        FROM m_wbs_tree_private
        WHERE project_id = #{projectId}
        AND id = #{parentId}
        AND wbs_id = #{wbsId}
        AND is_deleted = 0
        AND status = 1
        AND type = 1
    </select>
    <select id="getByPKeyId" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
            p_key_id,
            id,
            wbs_id,
            wbs_type,
            project_id,
            tenant_id,
            parent_id,
            ancestors,
            dept_category,
            dept_name,
            full_name,
            sort,
            remark,
            type,
            table_type,
            create_time,
            create_user,
            create_dept,
            update_user,
            update_time,
            status,
            is_deleted,
            unique_code,
            partition_code,
            is_expernode,
            is_concrete,
            table_owner,
            major_data_type,
            init_table_name,
            is_link_table,
            excel_id,
            is_exist_form
        FROM m_wbs_tree_private
        WHERE p_key_id = #{pKeyId}
    </select>

    <select id="selectByCondition3" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
            p_key_id
        FROM
            m_wbs_tree_private
        WHERE
            id = #{id}
            AND project_id = #{referencePrivateWbsProjectId}
            AND wbs_id = #{wbsId}
            AND STATUS = 1
            AND is_deleted = 0
            AND type = 1
            AND parent_id = 0
    </select>
    <select id="selectByPKeyId" resultType="org.springblade.manager.entity.WbsTreePrivate">
        SELECT
            p_key_id,
            id,
            wbs_id,
            wbs_type,
            project_id,
            tenant_id,
            parent_id,
            ancestors,
            dept_category,
            dept_name,
            full_name,
            sort,
            remark,
            type,
            table_type,
            create_time,
            create_user,
            create_dept,
            update_user,
            update_time,
            status,
            is_deleted,
            unique_code,
            partition_code,
            is_expernode,
            is_concrete,
            table_owner,
            major_data_type,
            init_table_name,
            is_link_table,
            excel_id,
            is_exist_form
        FROM m_wbs_tree_private WHERE p_key_id = #{referenceWbsTemplateId}
    </select>

    <select id="selectNodeListByCondition" resultType="org.springblade.manager.dto.WbsTreePrivateDTO2">
        SELECT p_key_id as "pKeyId",dept_name AS "deptName",sort
        FROM m_wbs_tree_private
        WHERE project_id = #{projectId}
        AND parent_id = #{parentId}
        AND wbs_id = #{wbsId}
        AND status = 1
        AND is_deleted = 0
        AND type = 1
        ORDER BY sort
    </select>

    <select id="selectNodeAndTable" resultType="org.springblade.manager.entity.WbsTreePrivate">
       SELECT
            p_key_id,
            id,
            wbs_id,
            wbs_type,
            project_id,
            tenant_id,
            parent_id,
            ancestors,
            dept_category,
            dept_name,
            full_name,
            sort,
            remark,
            type,
            table_type,
            create_time,
            create_user,
            create_dept,
            update_user,
            update_time,
            status,
            is_deleted,
            unique_code,
            partition_code,
            is_expernode,
            is_concrete,
            table_owner,
            major_data_type,
            init_table_name,
            is_link_table,
            excel_id,
            is_exist_form
        FROM m_wbs_tree_private
        WHERE id = #{id}
        AND project_id = #{referencePrivateWbsProjectId}
        AND wbs_id = #{wbsId}
        AND STATUS = 1
        AND is_deleted = 0
        OR (parent_id = #{id} and type = 2)
    </select>

</mapper>
