<?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.thingsgrid.system.mapper.MailMapper">
<!-- 字段 -->
<sql id="select_mail_filed">
xsm.id,
xsm.sender,
xsm.custom,
xsm.images,
xsm.status,
xsm.createtime,
xsm.mail_type,
xsm.approval_type,
xsm.title,
xsm.main_content,
xsm.secont_content,
xsm.updatetime
</sql>
<!-- 关系映射 -->
<resultMap id="BaseResultMap"
type="com.thingsgrid.system.entity.MailEntity">
<id column="id" property="ID" jdbcType="BIGINT"
javaType="java.lang.Long" />
<result column="sender" property="sender" />
<result column="custom" property="custom" />
<result column="title" property="title" />
<result column="main_content" property="mainContent" />
<result column="secont_content" property="secontContent" />
<result column="images" property="images" jdbcType="VARCHAR"
javaType="java.lang.String" />
<result column="createtime" property="createtime"
jdbcType="TIMESTAMP" javaType="java.util.Date" />
<result column="updatetime" property="updatetime"
jdbcType="TIMESTAMP" javaType="java.util.Date" />
<result column="status" property="status" jdbcType="BIT"
javaType="java.lang.Boolean" />
<result column="mail_type" property="mailType"
jdbcType="INTEGER" javaType="java.lang.Integer" />
<result column="approval_type" property="approvalType"
jdbcType="INTEGER" javaType="java.lang.Integer" />
</resultMap>
<!-- 查询条件 -->
<sql id="Example_Where_Clause">
where 1=1
<trim suffixOverrides=",">
<if test="ID != null">
and id = #{ID}
</if>
<if test="sender != null">
and sender = #{sender}
</if>
<if test="custom != null">
and custom = #{custom}
</if>
<if test="status != null">
and status = #{status}
</if>
<if test="mailType != null">
and mail_type = #{mailType}
</if>
<if test="approvalType != null">
and approval_type = #{approvalType}
</if>
<if test="title != null and title != ''">
and title LIKE CONCAT('%',#{title},'%')
</if>
<if test="mainContent != null and mainContent != ''">
and main_content LIKE CONCAT('%',#{mainContent},'%')
</if>
<if test="secontContent != null and secontContent != ''">
and secont_content LIKE CONCAT('%',#{secontContent},'%')
</if>
<if test="images != null and images != ''">
and images = #{images}
</if>
</trim>
ORDER BY xsm.`status`,xsm.updatetime DESC
</sql>
<insert id="insertMail"
parameterType="com.thingsgrid.system.entity.MailEntity"
useGeneratedKeys="true" keyProperty="ID">
insert into
xazq_system_mail
(sender,
custom,
title,
main_content,
secont_content,
images,
createtime,
updatetime,
status,
mail_type,
approval_type
)
values
(#{sender,
jdbcType=BIGINT},
#{custom, jdbcType=BIGINT},
#{title,
jdbcType=VARCHAR},
#{mainContent, jdbcType=VARCHAR},
#{secontContent,
jdbcType=VARCHAR},
#{images, jdbcType=VARCHAR},
#{createtime,
jdbcType=VARCHAR},
#{updatetime, jdbcType=VARCHAR},
#{status,
jdbcType=VARCHAR},
#{mailType, jdbcType=VARCHAR},
#{approvalType,
jdbcType=VARCHAR})
</insert>
<insert id="batchAddMail" parameterType="java.util.List">
INSERT INTO
xazq_system_mail
(sender,
custom,
title,
main_content,
secont_content,
images,
createtime,
updatetime,
status,
mail_type,
approval_type
)
VALUES
<foreach collection="mails" item="mail" separator=",">
(#{mail.sender},
#{mail.custom},
#{mail.title},
#{mail.mainContent},
#{mail.secontContent},
#{mail.images},
#{mail.createtime},
#{mail.updatetime},
#{mail.status},
#{mail.mailType},
#{mail.approvalType})
</foreach>
</insert>
<!-- 条件查询用户持有站内信息 -->
<select id="findMailByCondition" resultMap="BaseResultMap"
parameterType="com.thingsgrid.system.dto.ConditionQueryMailDTO">
SELECT
<include refid="select_mail_filed" />
FROM xazq_system_mail xsm
<include refid="Example_Where_Clause"></include>
LIMIT #{offset},#{size}
</select>
<select id="findMailById"
resultType="com.thingsgrid.system.entity.MailEntity">
SELECT
<include refid="select_mail_filed" />
FROM xazq_system_mail xsm
WHERE xsm.id = #{id}
</select>
<update id="updateBatch" parameterType="java.util.List">
update xazq_system_mail
<trim prefix="set" suffixOverrides=",">
<trim prefix="standard_from_uuid =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.standardFromUuid!=null">
when id=#{i.id} then #{i.standardFromUuid}
</if>
</foreach>
</trim>
<trim prefix="standard_to_uuid =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.standardToUuid!=null">
when id=#{i.id} then #{i.standardToUuid}
</if>
</foreach>
</trim>
<trim prefix="gmt_modified =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.gmtModified!=null">
when id=#{i.id} then #{i.gmtModified}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i"
index="index">
id=#{i.id}
</foreach>
</update>
<!-- 读取选中信息 -->
<update id="updateStatusById">
UPDATE xazq_system_mail
SET STATUS = 1
WHERE
STATUS = 0
AND CUSTOM = #{custom}
AND id IN
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</update>
<!-- 一键全读 -->
<update id="updateAllMailStatusByCustom">
UPDATE xazq_system_mail
SET STATUS = 1
WHERE
STATUS = 0
AND CUSTOM = #{custom}
</update>
<!-- 审批信息 -->
<update id="updateApprovalTypeById">
UPDATE xazq_system_mail
SET approval_type =
#{approvalType}
WHERE
id = #{id} AND mail_type = 2
</update>
<!-- 未读信息条目数 -->
<select id="findCountMailByCustomAndStatus"
resultType="java.lang.Integer">
SELECT COUNT(1)
FROM xazq_system_mail xsm
WHERE xsm.custom =
#{custom} AND xsm.status = 0
</select>
<select id="findMail"
resultType="com.thingsgrid.system.entity.MailEntity"
parameterType="com.thingsgrid.system.entity.MailEntity"
resultMap="BaseResultMap">
SELECT
<include refid="select_mail_filed" />
FROM xazq_system_mail xsm
<include refid="Example_Where_Clause" />
</select>
<!-- 分页查询 <select id="select_page" resultMap="BaseResultMap"> select * from
( select tt.* from ( SELECT <include refid="select_mail_filed" /> FROM xazq_system_mail
xsm <include refid="Example_Where_Clause" /> ) tt <where> 1 = 1 <if test="current
!= null and size != null"> and id <![CDATA[<=]]>#{current} * #{size} </if>
</where> ) table_alias where table_alias.id > #{pageNum} </select> -->
</mapper>