邮件收件与草稿箱sql
<!-- 邮件收件箱(收件箱) -->
<select id="findEmailService" resultClass="pd" parameterClass="pd">
SELECT id,
sent,
sentto,
sentto_name,
copyto,
subject,
copyto_name,
blindcopyto,
blindcopyto_name,
body,
DATE_FORMAT(sent_time,'%Y-%m-%d %T') sent_time,
sign,
isfiles,
read_sign
FROM mail_send
WHERE (status=1 or status is null) and sign=0
AND sentto LIKE CONCAT('%',(SELECT mail_address FROM mail_config WHERE user_id = #accept_id# and sign = 1),'%') //用户登录时use_id匹配才会从数据库抓取数据
<isNotEmpty property="starttime" prepend="and">
sent_time >= #starttime#
</isNotEmpty>
<isNotEmpty property="endtime" prepend="and">
<![CDATA[sent_time < date_add(#endtime#,interval 1 DAY)]]>
</isNotEmpty>
ORDER BY sent_time DESC
</select>
//
//
<!-- 查询邮件基本信息(草稿箱,未发送信箱) -->
<select id="findEmailList" parameterClass="pd" resultClass="pd">
SELECT id,
sent,
sentto,
sentto_name,
copyto,
copyto_name,
blindcopyto,
blindcopyto_name,
subject,
body,
DATE_FORMAT(sent_time,'%Y-%m-%d %T') sent_time,
isfiles,
sign
FROM mail_send
where status=#status# and sign=0
and sent LIKE CONCAT('%',(SELECT mail_address FROM mail_config WHERE user_id = #user_id# and sign = 1),'%')
<isNotEmpty property="starttime" prepend="and">
sent_time >= #starttime#
</isNotEmpty>
<isNotEmpty property="endtime" prepend="and">
<![CDATA[sent_time < date_add(#endtime#,interval 1 DAY)]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="subject">
subject = #subject#
</isNotEmpty>
order by sent_time desc
</select>