<?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.muchinfo.mtp3interface.mapper.INoticemsgMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Noticemsg">
        <id column="AUTOID" property="autoid" />
        <result column="MSGTYPE" property="msgtype" />
        <result column="TITLE" property="title" />
        <result column="CONTENT" property="content" />
        <result column="SCHEDULETIME" property="scheduletime" />
        <result column="PUBLISHER" property="publisher" />
        <result column="SENDTYPE" property="sendtype" />
        <result column="USERID" property="userid" />
        <result column="ISTOP" property="istop" />
        <result column="SENTSTATUS" property="sentstatus" />
        <result column="ENDTIME" property="endtime" />
        <result column="CREATORID" property="creatorid" />
        <result column="CREATETIME" property="createtime" />
        <result column="AUDITORUSERID" property="auditoruserid" />
        <result column="AUDITTIME" property="audittime" />
        <result column="AUDITREMARK" property="auditremark" />
        <result column="MSGICONURL" property="msgiconurl" />
        <result column="ISFORCEDISPLAY" property="isforcedisplay" />
        <result column="TITLEEN" property="titleen" />
        <result column="TITLETW" property="titletw" />
        <result column="TITLETH" property="titleth" />
        <result column="TITLEVI" property="titlevi" />
        <result column="CONTENT2" property="content2" />
        <result column="CONTENT2EN" property="content2en" />
        <result column="CONTENT2TW" property="content2tw" />
        <result column="CONTENT2TH" property="content2th" />
        <result column="CONTENT2VI" property="content2vi" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        AUTOID, MSGTYPE, TITLE, CONTENT, SCHEDULETIME, PUBLISHER, SENDTYPE, USERID, ISTOP, SENTSTATUS, ENDTIME, CREATORID, CREATETIME, AUDITORUSERID, AUDITTIME, AUDITREMARK, MSGICONURL, ISFORCEDISPLAY
    </sql>

    <select id="query" resultType="com.muchinfo.mtp3pojos.vo.notice.NoticeMsgQueryResult">
        select
        n.*,
        ua.accountname,
        (case when n.userid is not null then to_char(ua2.userid) || '/' || ua2.accountname end) "RECIPIENT"
        from noticemsg n
        left join useraccount ua on ua.userid = to_number(n.publisher)
        left join useraccount ua2 on ua2.userid = n.userid
        <where>
            <if test="vo.publisher != null and vo.publisher != '' ">
                and n.publisher = #{vo.publisher, jdbcType=VARCHAR}
            </if>
            <if test="vo.title != null and vo.title != ''">
                and n.title like '%'||#{vo.title,jdbcType=VARCHAR}||'%'
            </if>
            <if test="vo.msgtype != null and vo.msgtype != -1 ">
                and n.msgtype = #{vo.msgtype, jdbcType=INTEGER}
            </if>
            <if test="vo.sendtype != null and vo.sendtype != -1 ">
                and n.sendtype = #{vo.sendtype, jdbcType=INTEGER}
            </if>
            <if test="vo.content != null and vo.content != ''">
                and n.content like '%'||#{vo.content,jdbcType=VARCHAR}||'%'
            </if>
            <if test="vo.userid != null and vo.userid != '' ">
                and n.userid = #{vo.userid}
            </if>
            <if test="vo.isforcedisplay != null">
                and n.isforcedisplay = #{vo.isforcedisplay}
            </if>
        </where>
        order by n.scheduletime desc
    </select>
    <select id="smsquery" resultType="com.muchinfo.mtp3pojos.vo.notice.SmsQueryResult">
        select
        s.msgid,s.templid,s.msgtype,s.status,s.errinfo,
        s.priority,s.failedphone,s.recvphone,s.createtime,s.updatetime,
        u.userid,ua.broker, spd.tempname
        from SMSCOLLECTION s
        left join USERINFO u on s.recvphone = u.telphone
        left join USERACCOUNT ua on u.userid = ua.userid
        left join smstemplatedefine spd on s.templid = spd.templid
        <where>
            <if test="vo.recvphone != null and vo.recvphone != ''">
                and s.recvphone = #{vo.recvphone,jdbcType=VARCHAR}
            </if>
            <if test="vo.status != null">
                <choose>
                    <when test="vo.status == 1">
                        and s.status in (1)
                    </when>
                    <when test="vo.status == 3">
                        and s.status in (0,3)
                    </when>
                    <when test="vo.status == 4">
                        and s.status in (-1,4)
                    </when>
                </choose>
            </if>
            <if test="vo.userid != null and vo.userid != '' ">
                and ua.membersuserid = #{vo.userid}
            </if>
        </where>
        order by decode(s.status,'4',1,'1',2,'3',3), s.createtime desc
    </select>
    <select id="columnquery" resultType="com.muchinfo.mtp3pojos.vo.notice.ColumnQueryResult">
        select t.* from site_columnconfig t
        <where>
            <if test="vo.columntype != null">
                and columntype=#{vo.columntype}
            </if>
            <if test="vo.columnname != null and vo.columnname != ''">
                and columnname like '%'||#{vo.columnname}||'%'
            </if>
            <if test="vo.isshow != null">
                and isshow=#{vo.isshow}
            </if>
        </where>
        order by id
    </select>
    <select id="columnsourcequery" resultType="com.muchinfo.mtp3pojos.vo.notice.ColumnSourceQueryResult">
        select * from gz_memberinfo t
        where 1=1
        <if test="smembername != null and smembername != ''">
            and t.membername like '%'||#{smembername}||'%'
        </if>
        order by t.orderindex, t.membername
    </select>
    <select id="informManquery" resultType="com.muchinfo.mtp3pojos.vo.notice.InforManQueryResult">
        <choose>
            <when test=" vo.columnid != null and vo.columnid == -1">
                select d.id,d.title,d.creaedate,d.userid userid,s.username as creatoruser,
                c.id columnid, c.columnname, c.columnname,c.columnnameen,c.columnnametw,c.columnnameth ,c.columnnamevi
                from site_columnconfig c
                left join site_columndetail d on c.id = d.columnid and d.userid=#{vo.userid}
                left join systemmanager s on d.creatorid = s.autoid
                where c.columntype=100 and c.id !=3
            </when>
            <otherwise>
                select t.*,s.columnname, gm.membername srcname,sy.username as creatoruser,
                s.COLUMNNAMEEN columnnameen,
                s.COLUMNNAMETW columnnametw,
                s.COLUMNNAMETH columnnameth,
                s.COLUMNNAMEVI columnnamevi,
                gm.MEMBERNAMEEN srcnameen,
                gm.MEMBERNAMETW srcnametw,
                gm.MEMBERNAMETH srcnameth,
                gm.MEMBERNAMEVI srcnamevi
                from site_columndetail t
                left join site_columnconfig s on t.columnid = s.id
                left join gz_memberinfo gm on t.srcid = gm.memberid
                left join systemmanager sy on t.creatorid = sy.autoid
                <where>
                    <if test="vo.columnid != null">
                        and t.columnid=#{vo.columnid}
                    </if>
                    <if test="vo.title != null and vo.title != ''">
                        and t.title like '%'||#{vo.title}||'%'
                    </if>
                    <if test="vo.status != null">
                        and t.status=#{vo.status}
                    </if>
                    <if test="vo.isshow != null">
                        and t.isshow=#{vo.isshow}
                    </if>
                </where>
                order by t.istop desc, t.publishdate desc
            </otherwise>
        </choose>
    </select>


</mapper>
