<?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.ruoyi.tc.mapper.UnitMapper">

    <resultMap id="unitResult" type="Unit">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="deptId" column="dept_id"/>
        <result property="dwjc" column="dwjc"/>
        <result property="ssqyprovince" column="ssqyprovince"/>
        <result property="ssqycity" column="ssqycity"/>
        <result property="ssqycounty" column="ssqycounty"/>
        <result property="dwxxdz" column="dwxxdz"/>
        <result property="sshy" column="sshy"/>
        <result property="dwlx" column="dwlx"/>
        <result property="dwbq" column="dwbq"/>
        <result property="ssjgdw" column="ssjgdw"/>
        <result property="dwjs" column="dwjs"/>
        <result property="dwzsxz" column="dwzsxz"/>
        <result property="gjdw" column="gjdw"/>
        <result property="rcyyfzbm" column="rcyyfzbm"/>
        <result property="dwfzrxm" column="dwfzrxm"/>
        <result property="dwfzrlxfs" column="dwfzrlxfs"/>
        <result property="dwfzryx" column="dwfzryx"/>
        <result property="dwfzrzwzc" column="dwfzrzwzc"/>
        <result property="fgfzrxm" column="fgfzrxm"/>
        <result property="fgfzrlxfs" column="fgfzrlxfs"/>
        <result property="fgfzryx" column="fgfzryx"/>
        <result property="fgfzrzwzc" column="fgfzrzwzc"/>
        <result property="bmfzrxm" column="bmfzrxm"/>
        <result property="bmfzrlxfs" column="bmfzrlxfs"/>
        <result property="bmfzryx" column="bmfzryx"/>
        <result property="bmfzrzwzc" column="bmfzrzwzc"/>
        <result property="dylxrxm" column="dylxrxm"/>
        <result property="dylxrlxfs" column="dylxrlxfs"/>
        <result property="dylxryx" column="dylxryx"/>
        <result property="dylxrzwzc" column="dylxrzwzc"/>
        <result property="gw" column="gw"/>
        <result property="dtjd" column="dtjd"/>
        <result property="dtwd" column="dtwd"/>
        <result property="sjdw" column="sjdw"/>
        <result property="createBy" column="create_by"/>
        <result property="createId" column="create_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateId" column="update_id"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <result property="delFlag" column="del_flag"/>
        <result property="deptName" column="deptName"/>
        <result property="ancestors" column="ancestors"/>

        <result property="sxsjg" column="sxsjg"/>
        <result property="sxsjglxfs" column="sxsjglxfs"/>
        <result property="sxsjgyx" column="sxsjgyx"/>
        <result property="sxsjgzwzc" column="sxsjgzwzc"/>
        <result property="sjgllr" column="sjgllr"/>
        <result property="sjgllrlxfs" column="sjgllrlxfs"/>
        <result property="sjgllryx" column="sjgllryx"/>
        <result property="sjgllrzwzc" column="sjgllrzwzc"/>

        <collection property="otherConcat" javaType="java.util.List" ofType="com.ruoyi.tc.entity.UnitOtherConcat">
            <id property="concatId" column="concat_id"/>
            <result property="qtlxrxm" column="qtlxrlxfs"/>
            <result property="qtlxrlxfs" column="qtlxrlxfs"/>
            <result property="qtlxryx" column="qtlxryx"/>
            <result property="qtlxrzwzc" column="qtlxrzwzc"/>
            <result property="createBy" column="create_by"/>
            <result property="createId" column="create_id"/>
            <result property="createTime" column="create_time"/>
            <result property="updateBy" column="update_by"/>
            <result property="updateId" column="update_id"/>
            <result property="updateTime" column="update_time"/>
            <result property="remark" column="remark"/>
        </collection>
        <collection property="jcxxList" javaType="java.util.List" ofType="com.ruoyi.tc.entity.ExamineInfo">
            <id property="jcid" column="jcid"/>
            <result property="unitId" column="unit_id"/>
            <result property="sj" column="sj"/>
            <result property="xtym" column="xtym"/>
            <result property="xthc" column="xthc"/>
            <result property="dbjb" column="dbjb"/>
            <result property="dwmc" column="dwmc"/>
            <result property="jcjg" column="jcjg"/>
            <result property="delFlag" column="del_flag"/>
            <result property="createBy" column="create_by"/>
            <result property="createId" column="create_id"/>
            <result property="createTime" column="create_time"/>
            <result property="updateBy" column="update_by"/>
            <result property="updateId" column="update_id"/>
            <result property="updateTime" column="update_time"/>
            <result property="remark" column="remark"/>
        </collection>
    </resultMap>
    <update id="updatePasswordUser" parameterType="AssetSysUserExport">
        update sys_user
        <set>
            <if test="req.password != null and req.password != ''">password = #{req.password},</if>
            update_time = sysdate()
        </set>
        where user_id = #{req.userId}
    </update>

    <select id="selectUnitList" parameterType="unit" resultMap="unitResult">
        select a.*,b.dept_name as deptName from unit_info a
            left join sys_dept b on a.dept_id = b.dept_id
        <where>
            a.del_flag = '0'
            <if test="req.nickName != null and req.nickName != '' ">
                and a.nick_name like concat('%',#{req.nickName},'%')
            </if>
            <if test="req.dwlx != null">
                and a.dwlx = #{req.dwlx}
            </if>
            <if test="req.userName != null and req.userName != '' ">
                and a.user_name like concat('%',#{req.userName},'%')
            </if>
            <if test="req.deptId != null ">
                AND (a.dept_id = #{req.deptId} OR a.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
                find_in_set(#{req.deptId}, ancestors) ))
            </if>
        </where>
        order by create_time desc
    </select>
    <select id="getById" resultMap="unitResult">
        select a.*,
               c.*,
               e.*,
               d.dept_name as deptName,
               IF(d.ancestors != 0, concat(d.ancestors, ',', d.dept_id), d.dept_id)
                           as ancestors
        from unit_info a
                 left join unit_other_contact c on a.id = c.unit_id
                 left join unit_examine_info e on a.id = e.unit_id
                 left join sys_dept d on a.dept_id = d.dept_id
        where a.id = #{id}
    </select>


    <select id="selectByIds" resultType="java.lang.String">
        select nick_name
        from unit_info
        where id = #{id} and del_flag= '0'
    </select>
    <select id="dwList" resultType="com.ruoyi.tc.entity.Unit">
        SELECT  distinct a.*
        FROM unit_info a
        <if test="req.type!= null and req.type.indexOf('0') != -1">
            INNER JOIN asset_current cr ON a.nick_name = cr.dwmc
        </if>
        <if test="req.type!= null and req.type.indexOf('1') != -1">
            INNER JOIN asset_mini_programs pr ON a.nick_name = pr.ssdw
        </if>
        <if test="req.type!= null and req.type.indexOf('2') != -1">
            INNER JOIN asset_official_account ac ON a.nick_name = ac.ssdw
        </if>
        <if test="req.type!= null and req.type.indexOf('3') != -1">
            INNER JOIN asset_email em ON a.nick_name = em.ssdw
        </if>
        <if test="req.type!= null and req.type.indexOf('4') != -1">
            INNER JOIN asset_app ap ON a.nick_name = ap.ssdw
        </if>
        <where>
            NOT EXISTS (
            SELECT 1
            FROM asset_task b
            where
            b.task_status = 1 AND FIND_IN_SET(a.nick_name, b.dwmc)
            <if test="req.type != null">
                AND (
                <foreach item="type" collection="req.type.split(',')" separator=" OR ">
                    FIND_IN_SET(#{type}, b.type)
                </foreach>
                )
            </if>
            ) AND a.del_flag = 0
            <if test="req.type!= null and req.type.indexOf('0') != -1">
                and cr.del_flag=0
            </if>
            <if test="req.type!= null and req.type.indexOf('1') != -1">
                and pr.del_flag=0
            </if>
            <if test="req.type!= null and req.type.indexOf('2') != -1">
                and ac.del_flag=0
            </if>
            <if test="req.type!= null and req.type.indexOf('3') != -1">
                and em.del_flag=0
            </if>
            <if test="req.type!= null and req.type.indexOf('4') != -1">
                and ap.del_flag=0
            </if>

            <if test="req.nickName != null and req.nickName != '' ">
                and a.nick_name like concat('%',#{req.nickName},'%')
            </if>
            <if test="req.dwlx != null">
                and a.dwlx = #{req.dwlx}
            </if>
            <if test="req.userName != null and req.userName != '' ">
                and a.user_name like concat('%',#{req.userName},'%')
            </if>
        </where>


    </select>

    <select id="selectAllUnit" resultType="com.ruoyi.tc.entity.Unit">
        select a.*,d.dept_name as deptName from unit_info a
        left join sys_dept d on a.dept_id = d.dept_id
        <where>
            a.del_flag = '0' and d.del_flag = '0'
            <if test="req.startTime != null ">
                and a.update_time &gt;= #{req.startTime}
            </if>
            <if test="req.endTime != null">
                and a.update_time &lt;= #{req.endTime}
            </if>
        </where>
        order by create_time
    </select>
    <select id="findBydwmc" resultType="java.lang.Integer" parameterType="java.lang.String">

        select count(*)
        from unit_info
        where nick_name = #{part}
    </select>
    <select id="selectTaskId" resultType="java.lang.Integer">
        select id from asset_task where dwmc like concat('%',#{dwmc},'%') and type like  concat('%',#{type},'%') and task_status = '1'
    </select>
    <select id="selectTaskIdToDwmc" resultType="com.ruoyi.tc.entity.AssetTask">
        select id from asset_task where dwmc like concat('%',#{dwmc},'%') and task_status = '1'
    </select>
    <select id="getAppSchema" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        WITH RECURSIVE category_tree AS (-- 初始查询，找出根节点或特定parentid的记录
            SELECT
                d.*
            FROM
                asset_app a
                    LEFT JOIN unit_info b ON a.ssdw = b.nick_name
                    LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
            WHERE
                d.dept_id IS NOT NULL and a.del_flag = '0' and d.del_flag = '0'
              
        GROUP BY
            d.dept_id,
            d.parent_id,
            d.ancestors,
            d.dept_name,
            d.order_num,
            d.leader,
            d.phone,
            d.email,
            d.STATUS,
            d.del_flag,
            d.create_by,
            d.create_time,
            d.update_by,
            d.update_time
        UNION ALL-- 递归查询，根据parentid为上级id继续查询下一级数据
        SELECT
            d.*
        FROM
            sys_dept d
                JOIN category_tree ct ON d.dept_id = ct.parent_id
            ) SELECT
                  *
              FROM
                  category_tree
        group by
            dept_id,
            parent_id,
            ancestors,
            dept_name,
            order_num,
            leader,
            phone,
            email,
            STATUS,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        ORDER BY dept_id desc
    </select>
    <select id="getEmailSchema" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        WITH RECURSIVE category_tree AS (-- 初始查询，找出根节点或特定parentid的记录
            SELECT
                d.*
            FROM
                asset_email a
                    LEFT JOIN unit_info b ON a.ssdw = b.nick_name
                    LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
            WHERE
                d.dept_id IS NOT NULL and a.del_flag = '0' and d.del_flag = '0'
              
        GROUP BY
            d.dept_id,
            d.parent_id,
            d.ancestors,
            d.dept_name,
            d.order_num,
            d.leader,
            d.phone,
            d.email,
            d.STATUS,
            d.del_flag,
            d.create_by,
            d.create_time,
            d.update_by,
            d.update_time
            UNION ALL-- 递归查询，根据parentid为上级id继续查询下一级数据
        SELECT
            d.*
        FROM
            sys_dept d
                JOIN category_tree ct ON d.dept_id = ct.parent_id
            ) SELECT
                  *
              FROM
                  category_tree
        group by
            dept_id,
            parent_id,
            ancestors,
            dept_name,
            order_num,
            leader,
            phone,
            email,
            STATUS,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        ORDER BY dept_id desc
    </select>
    <select id="getMiniSchema" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        WITH RECURSIVE category_tree AS (-- 初始查询，找出根节点或特定parentid的记录
            SELECT
                d.*
            FROM
                asset_mini_programs a
                    LEFT JOIN unit_info b ON a.ssdw = b.nick_name
                    LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
            WHERE
                d.dept_id IS NOT NULL and a.del_flag = '0' and d.del_flag = '0'
              
        GROUP BY
            d.dept_id,
            d.parent_id,
            d.ancestors,
            d.dept_name,
            d.order_num,
            d.leader,
            d.phone,
            d.email,
            d.STATUS,
            d.del_flag,
            d.create_by,
            d.create_time,
            d.update_time,
            d.update_by
            UNION ALL-- 递归查询，根据parentid为上级id继续查询下一级数据
        SELECT
            d.*
        FROM
            sys_dept d
                JOIN category_tree ct ON d.dept_id = ct.parent_id
            ) SELECT
                  *
              FROM
                  category_tree
        group by
            dept_id,
            parent_id,
            ancestors,
            dept_name,
            order_num,
            leader,
            phone,
            email,
            STATUS,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        ORDER BY dept_id desc
    </select>
    <select id="getGzpSchema" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        WITH RECURSIVE category_tree AS (-- 初始查询，找出根节点或特定parentid的记录
            SELECT
                d.*
            FROM
                asset_official_account a
                    LEFT JOIN unit_info b ON a.ssdw = b.nick_name
                    LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
            WHERE
                d.dept_id IS NOT NULL and a.del_flag = '0' and d.del_flag = '0'
              
        GROUP BY
            d.dept_id,
            d.parent_id,
            d.ancestors,
            d.dept_name,
            d.order_num,
            d.leader,
            d.phone,
            d.email,
            d.STATUS,
            d.del_flag,
            d.create_by,
            d.create_time,
            d.update_by,
            d.update_time

        UNION ALL-- 递归查询，根据parentid为上级id继续查询下一级数据
        SELECT
            d.*
        FROM
            sys_dept d
                JOIN category_tree ct ON d.dept_id = ct.parent_id
            ) SELECT
                  *
              FROM
                  category_tree
        group by
            dept_id,
            parent_id,
            ancestors,
            dept_name,
            order_num,
            leader,
            phone,
            email,
            STATUS,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        ORDER BY dept_id desc
    </select>
    <select id="getWebSchema" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        WITH RECURSIVE category_tree AS (-- 初始查询，找出根节点或特定parentid的记录
            SELECT
                d.*
            FROM
                asset_current a
                    LEFT JOIN unit_info b ON a.dwmc = b.nick_name
                    LEFT JOIN sys_dept d ON b.dept_id = d.dept_id
            WHERE
                d.dept_id IS NOT NULL and a.del_flag = '0' and d.del_flag = '0'
            GROUP BY
                d.dept_id,
                d.parent_id,
                d.ancestors,
                d.dept_name,
                d.order_num,
                d.leader,
                d.phone,
                d.email,
                d.STATUS,
                d.del_flag,
                d.create_by,
                d.create_time,
                d.update_by,
                d.update_time
                UNION ALL-- 递归查询，根据parentid为上级id继续查询下一级数据
            SELECT
                d.*
            FROM
                sys_dept d
                    JOIN category_tree ct ON d.dept_id = ct.parent_id
        ) SELECT
            *
        FROM
            category_tree
        group by
            dept_id,
            parent_id,
            ancestors,
            dept_name,
            order_num,
            leader,
            phone,
            email,
            STATUS,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        ORDER BY dept_id desc
    </select>
    <select id="exportAllPassword" resultType="com.ruoyi.tc.entity.response.AssetSysUserExport">
        SELECT
            b.user_id as userId,
            b.user_name as userName,
            b.nick_name as nickName,
            b.password
        FROM
            unit_info a
                LEFT JOIN sys_user b ON a.user_name = b.user_name
        GROUP BY
            b.user_id,
            b.user_name,
            b.nick_name,
            b.password
    </select>


    <delete id="deleteUnits">
        delete from unit_info
        where id = #{id}
    </delete>
    <delete id="deleteUsers">
        delete from sys_user
        where user_name = #{userNames}
    </delete>
</mapper>