<?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.njcn.mapper.configuration.DicDataMapper">
    <resultMap type="com.njcn.pojo.configuration.DicType" id="DicDataTypeMapperMap" autoMapping="true">
        <id property="dicTypeIndex" column="DICTYPE_INDEX"/>
        <association property="uID" autoMapping="true" javaType="User">
            <id property="userIndex" column="USER_INDEX"/>
        </association>
    </resultMap>
    <resultMap type="com.njcn.sso.pojo.user.User" id="UserMapperMap" autoMapping="true">
        <id property="userIndex" column="USER_INDEX"/>
    </resultMap>
    <!-- 数据字典 -->
    <resultMap type="com.njcn.pojo.configuration.DicData" id="DicDataMapperMap" autoMapping="true">
        <id property="dicIndex" column="DIC_INDEX"/>
        <collection property="uID" resultMap="UserMapperMap"></collection>
        <collection property="dt" resultMap="DicDataTypeMapperMap"></collection>

    </resultMap>
    <!-- 增加 -->
    <insert id="insertData">
insert into Pqs_Dicdata (Dic_Index,Dic_Name,Dic_type,Dic_Number,UpdateTime,Dic_leave,User_Index,State)
values(#{dicIndex},#{dicName},#{dicType},#{dicNumber},#{updateTime},#{leave},#{dicLeave},#{state})
</insert>
    <!-- 删除 -->
    <update id="deleteData">
update Pqs_Dicdata set state=0  where dic_Index=#{dicIndex,jdbcType=VARCHAR}
</update>
    <!-- 修改 -->
    <update id="updateData">
update pqs_DicData set DIC_NAME=#{dicName},DIC_TYPE=#{dicType},UPDATETIME=#{updateTime},dic_leave=#{dicLeave} where DIC_INDEX=#{dicIndex,jdbcType=VARCHAR}
</update>
    <!-- 查询排序最大 -->
    <select id="selNumMax" resultType="int">
select   NVL(max(dic_number),0) from pqs_dicdata where state=1 order by dic_number desc
</select>
    <!-- 根據Id查詢所有 -->
    <select id="selectId" resultMap="DicDataMapperMap">
select *from pqs_dicData where DIC_INDEX=#{dicIndex,jdbcType=VARCHAR} and state=1 order by dic_index
</select>
    <!-- 根据typeId查询总条数 -->
    <select id="selCount" resultType="java.lang.Integer">
select count(*) from pqs_dicData where dic_type=#{dicTypeIndex,jdbcType=VARCHAR} and state=1
</select>
    <!-- 根据名称查询 -->
    <select id="seleAll" resultMap="DicDataMapperMap">
select t.*
from
    (
    select
      a.*
    from
      pqs_dicdata a,pqs_dictype b
    where
      a.dic_type=b.dictype_index
      and a.dic_name  like #{name}
      and a.state=1 order by a.dic_number
    ) t
left join pqs_user c on t.user_index =c.user_index
</select>
    <select id="setArea" resultMap="DicDataMapperMap">
select t.* from PQS_DICDATA t where t.dic_index not in(select area from pqs_depts where state=1) and t.STATE=1 and t.dic_type=(SELECT DICTYPE_INDEX from PQS_DICTYPE where DICTYPE_NAME = #{dicTypeName})
</select>
    <select id="setAreaNo" resultMap="DicDataMapperMap">
select t.* from PQS_DICDATA t where t.dic_index not in(select area from  pqs_depts where DEPTS_INDEX
!=#{deptIndex} and state=1 ) and t.dic_type=(SELECT DICTYPE_INDEX from PQS_DICTYPE where DICTYPE_NAME = #{dicTypeName}) and t.STATE=1
</select>
    <!-- 根据typeName查询 -->
    <select id="selAll" resultMap="DicDataMapperMap">
select t.*
from
    (
    select
      a.*
    from
      pqs_dicdata a,pqs_dictype b
    where
      a.dic_type=b.dictype_index
      and a.dic_type = #{typeId,jdbcType=VARCHAR}
      and a.state=1 order by a.dic_number
    ) t
left join pqs_user c on t.user_index =c.user_index
</select>

 <!-- 查询省份区域 -->
    <select id="queryDicPro" resultMap="DicDataMapperMap">
select t.*
from
    (
    select
      a.*
    from
      pqs_dicdata a,pqs_dictype b
    where
      a.dic_type=b.dictype_index
      and a.dic_type = #{typeId,jdbcType=VARCHAR}
      and a.dic_leave = 9
      and a.state=1 order by a.dic_number
    ) t
left join pqs_user c on t.user_index =c.user_index
</select>

    <!-- 根据name查询所有 -->
    <select id="getSelName" resultMap="DicDataMapperMap">
select * from pqs_dicdata where dic_name like #{name} and state=1
</select>
    <select id="selAllId" resultMap="DicDataMapperMap">
select t.*
from
    (
    select
      a.*
    from
      pqs_dicdata a,pqs_dictype b
    where
      a.dic_type=b.dictype_index
      and a.dic_type = #{typeId,jdbcType=VARCHAR}
      and a.state=1 order by a.dic_number
    ) t
left join pqs_user c on t.user_index =c.user_index
</select>
<select id="selAllType" resultType="String">
select distinct t.back_up
from
    (
    select
      a.*
    from
      pqs_dicdata a,pqs_dictype b
    where
      a.dic_type=b.dictype_index
      and a.dic_type in
		<foreach collection="typeId" item="item" index="index" open="("
				 separator="," close=")">
			#{item}
		</foreach>
      and a.state=1 order by a.dic_number
    ) t
left join pqs_user c on t.user_index =c.user_index
</select>
    <!-- 查询除此之外的名称 -->
    <select id="selectName" resultMap="DicDataMapperMap">
select * from pqs_Dicdata  where dic_name not in (select dic_name from pqs_Dicdata where dic_index=#{dicIndex}
and
state=1) and
DIC_TYPE=#{dicType} and state = 1
</select>
    <!-- 查询接线方式 -->
    <select id="selectWring" resultMap="DicDataMapperMap">
select  * from pqs_dicdata dt  where  dt.dic_type=(SELECT DICTYPE_INDEX from PQS_DICTYPE where DICTYPE_NAME = #{dicTypeName}) and dt.state = 1
</select>
    <select id="selectDataName" resultMap="DicDataMapperMap">
select  * from pqs_dicdata dt  where dic_index =#{dicIndex} and dt.state=1 order by dt.updatetime desc
</select>
    <select id="selectWiring" resultMap="DicDataMapperMap">
select  * from pqs_dicdata dt  where  dt.dic_type=(SELECT DICTYPE_INDEX from PQS_DICTYPE where DICTYPE_NAME = #{dicTypeName}) and dt.dic_index <![CDATA[ <> ]]>#{typeId} and dt.state=1
</select>
    <!-- 查询重复 -->
    <select id="selectdicName" resultMap="DicDataMapperMap">
select * from pqs_dicdata where dic_name=#{dicName} and state=1
</select>

    <select id="selectAllExcelData" resultType="DicDataExcelBody">
		select  * from PQS_DICDATA where STATE =1
	</select>

    <select id="getMangType" resultMap="DicDataMapperMap">
select * from PQS_DICDATA where state=1 and dic_TYPE=(SELECT DICTYPE_INDEX from PQS_DICTYPE where DICTYPE_NAME = #{dicTypeName})
</select>

    <select id="getDicDataIndex" resultType="String">
        SELECT
            T2.DIC_INDEX
        FROM
            PQS_DICDATA t2
        WHERE
            T2.DIC_TYPE = (
        SELECT
            T1.DICTYPE_INDEX
        FROM
            PQS_DICTYPE t1
        WHERE
            DICTYPE_NAME= #{type}
        )
            AND
            T2.TRIPHASE= #{temp}
    </select>

    <!-- 更新字典表根据主键-->
    <update id="updateByPrimaryKeyBySql">
        UPDATE PQS_DICDATA
        SET DIC_LEAVE = #{dicLeave}
        WHERE
            DIC_INDEX = #{dicIndex}
    </update>

    <select id="queryProIdByname"  resultMap="DicDataMapperMap">
    select *
	  from pqs_dicdata
	 where dic_name = #{dicName}
	   and dic_leave = 9
    </select>
    <select id="getDicName" resultType="String">
        select dic_Name from pqs_dicdata where dic_index = #{dicIndex}
    </select>

    <select id="getTartgetTypeForDic" resultType="com.njcn.pojo.configuration.DicData">
		select a.DIC_INDEX as dicIndex,
		 a.DIC_NAME as dicName,
		 a.DIC_TYPE as dicType
		 from PQS_DICDATA a
		 left join PQS_DICTYPE b on a.DIC_TYPE = b.DICTYPE_INDEX
		 where b.DICTYPE_NAME = #{type}
		 and a.STATE = 1
		 order by a.triphase asc
	</select>
</mapper>
