<?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.casic.missiles.modular.system.dao.SysDictMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.modular.system.model.SysDict">
        <id column="id" property="id"/>
        <result column="num" property="num"/>
        <result column="pid" property="pid"/>
        <result column="name" property="name"/>
        <result column="tips" property="tips"/>
        <result column="code" property="code"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, num, pid, name, tips, code
    </sql>
    <!-- 简单 查询结果列 -->
    <sql id="Simple_Column_List">
        num,pid,name
    </sql>

    <!--<select id="getNameViaNameNum" parameterMap="map" resultMap="map">-->
    <!--SELECT  <include refid="Simple_Column_List"/>  FROM sys_dict  WHERE pid IN (SELECT id FROM sys_dict WHERE pid=0-->
    <!--AND name = #{statusName} )&#45;&#45;'工单状态' or statusCode-->
    <!--AND num = #{statusNum}&#45;&#45;job_status;-->
    <!--</select>-->
    <!--<include refid="Simple_Column_List"/>-->
    <select id="getTypeViaName" resultType="map">
        SELECT  * FROM sys_dict
        WHERE pid IN (SELECT id FROM sys_dict WHERE pid=0 AND name = #{typeName} ) ;
    </select>

    <select id="getTypeViaCode" resultType="map">
        SELECT  * FROM sys_dict
        WHERE pid IN (SELECT id FROM sys_dict WHERE pid=0 AND code = #{code} ) ;
    </select>


    <select id="getAlarmTypeNameByCodeNum" resultType="String">
        SELECT name FROM sys_dict WHERE
        <!--        SELECT num,name,pid FROM sys_dict WHERE-->
        pid IN (SELECT id FROM sys_dict WHERE pid=0 AND code = 'alarmType' )
        AND code = #{alarmType};
        <!--         AND num = #{alarmType};-->
    </select>

    <select id="getJobStatusNameByCodeNum" resultType="String">
        SELECT name FROM sys_dict WHERE pid IN
        (SELECT id FROM sys_dict WHERE pid=0 AND code = 'jobStatus' )
        AND code = #{jobStatus};
        <!--       AND num = #{jobStatus};-->
    </select>

    <select id="getDictNameByCodeCodeNum" resultType="String">
        SELECT name FROM sys_dict WHERE pid IN
        (SELECT id FROM sys_dict WHERE pid=0 AND code = #{code} )
        AND code = #{codeNum};
        <!--         AND num = #{num};-->
    </select>

    <!--<select id="getAlarmContentNameById" resultType="String">-->
    <!--select alarm_content as alarmContentName from base_alarm_content_type-->
    <!--where id = #{alarmContent}-->
    <!--</select>    -->
    <select id="getAlarmContent" resultType="map">
        SELECT id as value,alarm_content as name ,alarm_type as "alarmType",
        device_type as "deviceType"
        FROM base_alarm_content_type
        WHERE 1=1
        <if test="alarmType!=null and alarmType!=-1">
            AND alarm_type = #{alarmType}
        </if>
        <if test="deviceType!=null and deviceType!=-1">
            AND device_type= #{deviceType}
        </if>
        order by id;
    </select>

    <select id="testPage" resultType="map">
        select id,well_code,well_name from bus_well_info
    </select>

    <select id="getDeptNameById" resultType="String">
        select FULL_NAME as "deptName" from sys_dept where id =#{deptid};
    </select>

    <select id="getUserByDeptId" resultType="String">
        select *
        from sys_user
        where dept_id =#{deptid};
    </select>

    <select id="getSimpleDeptNameById" resultType="String">
        select SIMPLE_NAME as "deptName" from sys_dept where id =#{deptid};
    </select>


    <select id="getDeviceTypeNameById" resultType="String">
        SELECT type_name as "deviceTypeName"
         FROM base_device_type
         WHERE valid='1' AND ID = #{deviceTypeId} ;
    </select>

    <select id="getWellInfoViaCode" resultType="map">
        SELECT ID as "wellId",POSITION as position,DEPTID as deptid
         ,coalesce(STAFF,'') as staff ,coalesce(TEL,'') as tel
        FROM bus_well_info
        WHERE WELL_CODE = #{wellCode};
    </select>

    <select id="getAlarmContentName" resultType="String">
        select alarm_content as alarmContentName
        from base_alarm_content_type
        where id = #{id}
    </select>

    <sql id="nowTime_toChar" databaseId="mysql">
       DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s')
    </sql>

    <sql id="nowTime_toChar" databaseId="pgsql">
       TO_CHAR(now(),'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <sql id="nowTime_toChar" databaseId="oracle">
       TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <select id="getDBtime" resultType="String">
        select
        <include refid="nowTime_toChar"/>
        from dual
    </select>

    <select id="getAlarmContentByName" resultType="int">
        select id as "alarmContent"
        from base_alarm_content_type
        where alarm_content = #{sName}
    </select>

    <select id="getUsernameById" resultType="String">
        select name
        from sys_user
        where id =#{id}
    </select>

    <select id="getUserInfoById" resultType="Map">
        select coalesce(name,'') as staff,coalesce(phone,'') as tel from sys_user
        where id =#{id}
    </select>

    <select id="getUserByDept" resultType="java.lang.Long">
        SELECT USER_ID as "userId" FROM bus_dept_user
        WHERE DEPTID = #{deptid}
    </select>
</mapper>
