<?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.chinaweal.aiccs.org.mapper.AicorgMapper">
    <resultMap id="AICUserResultMap" type="com.chinaweal.aicorg.pojo.AICUser">
        <id column="userid" property="userId"/>
        <result column="username" property="userName"/>
        <result column="password" property="password"/>
        <result column="realname" property="realName"/>
        <result column="orgdeptid" property="orgDeptId"/>
        <result column="orgunitid" property="orgUnitId"/>
        <result column="userprop" property="userProp"/>
    </resultMap>
    <resultMap id="OrgUnitsResultMap" type="com.chinaweal.aiccs.org.entity.OrgUnits">
        <id column="orgunitid" property="orgUnitId"/>
        <result column="orgunitname" property="orgUnitName"/>
        <result column="orgnumber" property="orgNumber"/>
        <result column="parentid" property="parentId"/>
        <result column="unittype" property="unitType"/>
        <result column="orglevel" property="orgLevel"/>
    </resultMap>
    <resultMap id="AICUserMap" type="com.chinaweal.aicorg.model.AICUser">
        <id column="primaryKey" property="primaryKey"/>
        <result column="name" property="name"/>
        <result column="ename" property="ename"/>
    </resultMap>

    <!--查询所有拥有指定权限的用户-->
    <select id="selectAllAICUserWithPermissions" resultMap="AICUserResultMap">
        select userid, username, "password", realname, orgdeptid, orgunitid, userprop
        from cxaicorg.t_users tu
        where deleted = '0' and locked = '0' and exists(
                      select 1
                      from cxaicorg.t_usermenu tu2
                      where tu2.userid = tu.userid
                        and exists(
                              select 1
                              from cxaicorg.sys_menu sm
                              where sm.sysmenuid = tu2.sysmenuid
                                and sm.sysmenufiled in
                            <foreach collection="permissions" open="(" close=")" separator="," item="each">
                                #{each}
                            </foreach>
                            limit 1
                          )
                     limit 1
                  )

    </select>
    <select id="selectAllAICUserWithAreaOrglevel" resultMap="AICUserResultMap">
        select u.* from cxaicorg.t_orgunits t , cxaicorg.t_orgnodesapp app , cxaicorg.t_users u where
        t.orgunitid = app.orgunitid and t.orgunitid = u.orgunitid
        and t.deleted = '0' and app.orgunittype = #{orgUnitType}
    </select>

    <select id="selectTreeOrgAndDeptByOrgUnitType" resultMap="OrgUnitsResultMap">
        select t.* from cxaicorg.t_orgunits t , cxaicorg.t_orgnodesapp app where t.orgunitid = app.orgunitid
        and t.deleted = '0' and app.orgunittype = #{orgUnitType}
        union
        select * from cxaicorg.t_orgunits where unittype = '2' and deleted = '0' and
        parentid in(select t.orgunitid from cxaicorg.t_orgunits t , cxaicorg.t_orgnodesapp app where t.orgunitid = app.orgunitid
        and t.deleted = '0' and app.orgunittype = #{orgUnitType} and unittype = '1' and orglevel = 3)
    </select>
    <select id="selectRightsByUserId" resultType="java.lang.String">
        SELECT sm.sysMenuFiled
        from sys_menu sm
        WHERE sm.sysMenuID in (SELECT sysMenuID from t_usermenu um where um.userID = #{userId})
    </select>
    <select id="selectParentOrg" resultMap="OrgUnitsResultMap">
        select t.* from t_orgunits t
        where exists(select 1 from t_orgunits t1 where t1.orgunitid = #{curOrgId} and t1.parentid = t.orgunitid)
    </select>

    <select id="queryByOrgUnitID" resultMap="OrgUnitsResultMap">
        select * from cxaicorg.t_orgunits to2 where orgUnitID = #{orgUnitID}
    </select>

    <select id="queryAllOrg" resultMap="OrgUnitsResultMap">
        select orgUnitName as orgunitname, left(orgNumber,6) as orgnumber from cxaicorg.t_orgunits to2 where 1=1
        and orgLevel in (2,3)
        and orgUnitName like '%市场监督管理局'
        and deleted = '0'
    </select>

    <select id="queryByOrgNumber" resultMap="OrgUnitsResultMap">
        select * from cxaicorg.t_orgunits to2 where orgNumber = #{orgNumber}
    </select>

    <select id="queryByOrgNumberAndSysMenu" resultType="com.chinaweal.aicorg.model.AICUser">
        select tu.userid as primaryKey, tu.username as ename, tu.realname as name,
        tog.orgNumber as regionID , tog.orgunitid as orgID, tog.orgUnitName as orgName
        from cxaicorg.t_users tu left join cxaicorg.t_orgunits tog on tu.orgUnitID = tog.orgUnitID
        where
        tu.userid in (
              select tum.userID from cxaicorg.t_usermenu tum
              left join cxaicorg.sys_menu sm on tum.sysMenuID = sm.sysMenuID
              where sm.sysMenuFiled = #{sysMenuFiled}
        )
        and tog.orgNumber = #{orgNumber}
    </select>

    <select id="jurisdictionCheck" resultType="java.lang.String">
        select tu.userid from cxaicorg.t_usermenu tu , cxaicorg.sys_menu sm
        where
        tu.sysMenuID = sm.sysMenuID
        and sm.sysMenuFiled = #{sysMenuFiled}
        and tu.userid = #{userid}
    </select>

    <select id="queryOrgUnitID" resultMap="OrgUnitsResultMap">
        select * from aiccs.t_orgunits  where orgUnitID = #{orgUnitID}
    </select>
    <select id="queryUserByUserName" resultType="com.chinaweal.aicorg.model.AICUser">
        select to2.USERID as "primaryKey",to2.USERNAME as "name",to2.REALNAME as "ename", to2.USERPROP as "userProp",
        toga.ORGNUMBER as "regionID",
        tog.ORGUNITID as "unitID", tog.ORGUNITNAME as "unitName",
        toga.ORGUNITID as "orgID", toga.ORGUNITNAME as "orgName",
        to2.USERSTATUS as "status"
        FROM cxaicorg.T_USERS to2
        LEFT JOIN cxaicorg.T_ORGUNITS tog ON tog.ORGUNITID = to2.ORGDEPTID
        LEFT JOIN cxaicorg.T_ORGUNITS toga ON toga.ORGUNITID = to2.ORGUNITID
        WHERE USERNAME = #{username} LIMIT 1
    </select>

    <update id="updateByUserName">
        UPDATE cxaicorg.T_USERPORTAL
            JOIN CXAICORG.t_users ON  CXAICORG.t_users.realname=CXAICORG.T_USERPORTAL.realname OR CXAICORG.t_users.USERNAME =CXAICORG.T_USERPORTAL.phone
            SET LOCALUSERID=userid WHERE  cxaicorg.T_USERPORTAL.realname = #{realname}
    </update>

    <select id="selectTOrgunitsById" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        select orgUnitID,
               orgUnitName,
               deleted,
               orgNumber,
               parentID,
               unitType,
               orgLevel,
               code,
               createDate,
               orgUnitparentId,
               updateDate
        from t_orgUnits
        where orgUnitID = #{orgUnitID}
    </select>

    <select id="queryByOrgNumberMap" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        SELECT * FROM cxaicorg.t_orgunits
        WHERE orgNumber = #{params.orgNumber}
        <if test="params.deleted !=null and params.deleted !=''">
            AND deleted = #{params.deleted}
        </if>
        <if test="params.unittype !=null and params.unittype !=''">
            AND unittype = #{params.unittype}
        </if>
    </select>

    <select id="queryAlls" resultType="com.chinaweal.aiccs.org.entity.vo.CascaderVO">
        SELECT units.orgUnitID,
        units.orgUnitName AS label,
        units.parentID,
        units.orgLevel,
        units.orgNumber   AS "value"
        FROM cxaicorg.t_orgUnits units
        WHERE units.deleted = '0'
        AND units.unitType = '1'
        AND units.orgLevel IN (1, 2, 3, 4, 5)
        <if test="orgNumber != null and orgNumber != ''">
            and units.orgNumber like #{orgNumber}
        </if>
        ORDER BY units.orgLevel, units.orgNumber ASC
    </select>

    <select id="getOrgunitsByOrgId" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        select orgUnitID,
               orgUnitName,
               deleted,
               orgNumber,
               parentID,
               unitType,
               orgLevel
        from cxaicorg.t_orgUnits
        where orgUnitID = #{orgUnitID}
    </select>

    <select id="selectUsersByOrgAndPermission" resultMap="AICUserMap">
        select distinct a.userID as "primaryKey", a.userName as "ename", a.password as password, a.realName as "name",
        b.orgUnitId as "orgId",
        b.orgUnitName as "orgName", c.orgUnitID as "unitId", c.orgUnitName as "unitName", b.orgNumber as "regionId", a.userProp as "userProp"
        from cxaicorg.t_users a, cxaicorg.t_orgUnits b, cxaicorg.t_orgUnits c, cxaicorg.sys_menu d, cxaicorg.t_userMenu e
        <where>
            a.orgUnitID=b.orgUnitID and a.orgDeptID=c.orgUnitID and d.sysMenuID=e.sysMenuID and a.deleted='0' and
            a.locked='0' and a.userID=e.userID
            <if test="perm != null and perm !=''">
                and d.sysMenuFiled=#{perm}
            </if>
            <if test="unitId != null and unitId !=''">
                and b.orgUnitID = #{unitId}
            </if>
        </where>
    </select>

    <select id="selectParentOrgByDeptId" resultMap="OrgUnitsResultMap">
        select *
        from cxaicorg.t_orgUnits where orgUnitId=(select parentId from cxaicorg.t_orgUnits where orgUnitId=#{deptId} )
    </select>
</mapper>
