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

    <!-- 通用查询映射结果 -->
    <resultMap id="wbsTreeResultMap" type="org.springblade.manager.entity.WbsTree">
        <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"/>
        <result column="is_exist_form" property="isExistForm"/>
    </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="deptCategory" property="deptCategory"/>
        <result column="wbsType" property="wbsType"/>
        <result column="isExistForm" property="isExistForm"/>
        <result column="ancestors" property="ancestors"/>
    </resultMap>

    <resultMap id="selectByNodeTableMap" type="org.springblade.manager.vo.WbsNodeTableVO">
        <id column="id" property="id"/>
        <result column="tableName" property="tableName"/>
        <result column="tableType" property="tableType"/>
        <result column="isCreateTable" property="isCreateTable"/>
        <result column="elementTotal" property="elementTotal"/>
        <result column="tableOwner" property="tableOwner"/>
        <result column="initTableName" property="initTableName"/>
        <result column="sort" property="sort"/>
    </resultMap>

    <resultMap id="wbsFormElementMap" type="org.springblade.manager.vo.WbsFormElementVO">
        <id column="id" property="id"/>
        <result column="fId" property="fId"/>
        <result column="e_key" property="eKey"/>
        <result column="e_name" property="eName"/>
        <result column="e_type" property="eType"/>
        <result column="e_length" property="eLength"/>
        <result column="e_allow_deviation" property="eAllowDeviation"/>
        <result column="e_inspection_method" property="eInspectionMethod"/>
        <result column="tableElementKey" property="tableElementKey"/>
        <result column="initTableName" property="initTableName"/>
    </resultMap>

    <insert id="addFormElement" parameterType="org.springblade.manager.dto.WbsFormElementDTO">
        insert into m_wbs_form_element(f_id,e_name,e_type,e_length)
        values (#{f_id},#{e_name},#{e_type},#{e_length})
    </insert>
    <insert id="insertForm">
        insert into m_wbs_tree
    </insert>

    <insert id="insertWbsTreePrivate">
        INSERT INTO m_wbs_tree_private(
        id,wbs_id,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(
        #{wbsTree.id},#{wbsTree.wbsId},#{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="insertWbsTreeContract">
        INSERT INTO m_wbs_tree_contract(
        id,wbs_id,project_id,contract_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(
        #{wbsTree.id},#{wbsTree.wbsId},#{projectId},#{contractId},#{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="removeTableById">
        UPDATE m_wbs_tree AS wt SET is_deleted = 1 WHERE wt.type = 2 AND wt.id = #{id}
    </update>
    <update id="updateById2">
        UPDATE m_wbs_tree AS wt SET wt.ancestors = #{ancestors} WHERE is_deleted = 0 AND wt.id = #{id}
    </update>
    <update id="updateById3">
        UPDATE m_wbs_tree AS wt SET wt.parent_id = #{parentId} WHERE is_deleted = 0 AND wt.id = #{id}
    </update>

    <update id="createWbsTreeTable">
        create table ${tableName} as select * from m_wbs_tree WHERE 1=2
    </update>

    <update id="updateByCondition">
        UPDATE m_wbs_tree
        <trim prefix="set" suffixOverrides=",">
            <if test="wbsName != null and wbsName != '' ">
                `dept_name` = #{wbsName},
            </if>
            <if test="status != null and status != '' ">
                `status` = #{status}
            </if>
        </trim>
        WHERE wbs_id = #{id}
        AND parent_id = 0
        AND is_deleted = 0
    </update>

    <update id="updateSortById">
        UPDATE m_wbs_tree
        SET sort = #{sort}
        WHERE id = #{id}
        AND type = 1
        AND status = 1
        AND is_deleted = 0
    </update>

    <delete id="deleteBatchProjectNodeId">
        delete from m_wbs_tree where project_node_id = #{projectNodeId} and id &lt;&gt; #{projectNodeId}
    </delete>

    <delete id="deleteBatch" parameterType="java.util.List">
        UPDATE m_wbs_tree_private SET is_deleted = 1
        WHERE id IN
        <foreach item="ids" collection="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </delete>

    <delete id="deleteBatch2" parameterType="java.util.List">
        UPDATE m_wbs_tree_private SET is_deleted = 1
        WHERE parent_id IN
        <foreach item="ids" collection="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
        AND wbs_id = #{wbsId}
        AND project_id = #{projectId}
    </delete>

    <update id="deleteLogicByWbsId">
        update m_wbs_tree set is_deleted = 1 where wbs_id = #{ids} and status = 1
    </update>

    <update id="updateIsExistFormById">
        UPDATE m_wbs_tree SET is_exist_form = 1 WHERE id = #{id} AND status = 1 AND is_deleted = 0
    </update>

    <update id="createTable">
        create table ${newTableName} (
        `id` bigint(20) NOT NULL,
         PRIMARY KEY (`id`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    </update>

    <update id="alterTableFiled">
        ALTER TABLE ${initTableName} ADD COLUMN ${eKey} ${eType}(${eLength})
    </update>

    <select id="selectWbsTreePage" resultMap="wbsTreeResultMap">
        select * from m_wbs_tree where is_deleted = 0
    </select>

    <select id="tree" resultMap="treeNodeResultMap">
        select 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 where
        is_deleted = 0
        and status = 1
        <if test="wbsId!=null and wbsId!=''">
            and wbs_id = #{wbsId}
        </if>
        <if test="tenantId!=null and tenantId!=''">
            and tenant_id = #{tenantId}
        </if>
        <if test="type!=null and type!=''">
            and `type` = #{type}
        </if>
        ORDER BY sort
    </select>


    <select id="lazyTree" resultMap="treeNodeResultMap">
        SELECT
        dept.type AS "type",
        dept.dept_category AS "deptCategory",
        dept.is_exist_form AS "isExistForm",
        dept.table_type,
        dept.id,
        dept.parent_id,
        dept.dept_name AS title,
        dept.id AS "value",
        dept.id AS "key",
        dept.ancestors AS "ancestors",
        (
        SELECT
        CASE WHEN count(1) > 0 THEN 1 ELSE 0 END
        FROM
        m_wbs_tree
        WHERE
        parent_id = dept.id and is_deleted = 0
        ) AS "has_children"
        FROM
        m_wbs_tree 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.wbs_id = #{wbsId}
        AND dept.type = 1
        AND status = 1
        ORDER BY dept.sort
    </select>

    <select id="selectByNodeTable" resultMap="selectByNodeTableMap">
        SELECT
            wt.id AS id,
            wt.dept_name AS tableName,
            wt.sort AS sort,
            wt.table_type AS tableType,
            wt.`status` AS isCreateTable ,
            wt.table_owner as tableOwner,
            wt.is_link_table,
            wt.init_table_name as "initTableName",
            (SELECT count(*) FROM m_wbs_form_element AS wfe WHERE wfe.f_id=wt.id and wfe.is_deleted = 0) AS elementTotal
        FROM
            m_wbs_tree AS wt
        WHERE
            wt.type = 2
        AND wt.is_deleted = 0
        AND wt.parent_id = #{id}
        ORDER BY wt.sort
    </select>

    <select id="selectByElementsTotal" resultType="java.lang.String">
        SELECT f_id,count(*) as elementTotal FROM m_wbs_form_element where f_id in #{ids} and is_deleted=0 GROUP BY f_id
    </select>


    <select id="selectFormElements" resultMap="wbsFormElementMap">
        SELECT
            w.f_id as fId,w.e_key,w.id,w.e_name,e_type,e_length,e_allow_deviation,e_Inspection_method,
            (select CONCAT(dept_name,'_',w.e_name) from m_wbs_tree where id = #{id} AND status = 1 AND is_deleted = 0) AS "tableElementKey",
            (select init_table_name from m_wbs_tree where id = w.f_id AND type = 2 AND status = 1 AND is_deleted = 0 ) AS "initTableName"
        FROM
            m_wbs_form_element w
        WHERE
            is_deleted = 0 and f_id = #{id}
    </select>
    <select id="selectRepeatByProjectId" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM m_wbs_tree_private WHERE project_id = #{projectId} AND status = 1 AND is_deleted = 0
    </select>

    <select id="findAllNodeTableList" resultType="org.springblade.manager.entity.WbsTree">
        SELECT
            *
        FROM
            m_wbs_tree
        WHERE
            type = 2
            AND is_deleted = 0
            AND `status` = 1
            AND wbs_id = #{wbsId}
    </select>

    <select id="selectIsTable" resultType="org.springblade.manager.entity.WbsTree">
        SELECT
            id, wbs_id, tenant_id, parent_id, dept_category , type, dept_name, full_name, table_type, sort , ancestors, remark, unique_code, partition_code, is_expernode , is_concrete, table_owner, major_data_type, init_table_name, project_node_id , is_link_table, is_exist_form, create_user, create_dept, create_time , update_user, update_time, status, is_deleted
        FROM
            m_wbs_tree
        WHERE
            type = 2
            AND is_deleted = 0
            AND `status` = 1
            AND parent_id = #{tableParentId}
    </select>

    <select id="selectNodeAndTable" resultType="org.springblade.manager.entity.WbsTree">
        SELECT
            id, wbs_id, tenant_id, parent_id, dept_category , type, dept_name, full_name, table_type, sort , ancestors, remark, unique_code, partition_code, is_expernode , is_concrete, table_owner, major_data_type, init_table_name, project_node_id , is_link_table, is_exist_form, create_user, create_dept, create_time , update_user, update_time, status, is_deleted
        FROM
            m_wbs_tree
        WHERE
            id = #{id}
        OR (parent_id = #{id} and type = 2)
    </select>

    <select id="selectByWbsId" resultType="org.springblade.manager.entity.WbsTree">
        SELECT * FROM m_wbs_tree
        WHERE wbs_id = #{id}
        AND status = 1
        AND is_deleted = 0
        AND parent_id = 0
        AND type = 1
    </select>

    <select id="selectListByUserId" resultType="org.springblade.manager.vo.SaveUserInfoByProjectVO2">
        SELECT
            mpau.id AS "id"
            ,pi.project_name as "projectName"
            ,ci.contract_name as "contractName"
            ,(SELECT role_name  FROM blade_role WHERE id = r.parent_id  ) as "roleType"
            ,r.role_name as "roleName"
        FROM
            m_project_assignment_user mpau
        INNER JOIN blade_role r ON r.id = mpau.role_id
        INNER JOIN m_contract_info ci ON ci.id = mpau.contract_id
        INNER JOIN m_project_info pi ON pi.id = mpau.project_id
        WHERE mpau.is_deleted = 0 AND mpau.`status` = 1
        AND mpau.user_id = #{userId}
    </select>

    <select id="selectWbsTreeListByParentId" resultType="org.springblade.manager.dto.WbsTreeDTO2">
        SELECT id,dept_name AS "deptName",sort
        FROM m_wbs_tree
        WHERE parent_id = #{parentId}
        AND status = 1
        AND is_deleted = 0
        AND type = 1
        ORDER BY sort
    </select>


</mapper>
