<?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.hex.dop.cps.dao.DopRuRecvBatchMapper" >
<resultMap id="BaseResultMap" type="com.hex.dop.cps.model.DopRuRecvBatch" >
<id column="batch_no" property="batchNo" jdbcType="VARCHAR" />
<id column="case_no" property="caseNo" jdbcType="VARCHAR" />
<result column="channel" property="channel" jdbcType="VARCHAR" />
<result column="recv_time" property="recvTime" jdbcType="TIMESTAMP" />
<result column="doc_count" property="docCount" jdbcType="INTEGER" />
<result column="sender" property="sender" jdbcType="VARCHAR" />
<result column="sender_id" property="senderId" jdbcType="VARCHAR" />
<result column="sender_addr" property="senderAddr" jdbcType="VARCHAR" />
<result column="status" property="status" jdbcType="VARCHAR" />
<result column="ocr_message" property="ocrMessage" jdbcType="VARCHAR" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
<result column="is_lock" property="isLock" jdbcType="VARCHAR" />
<result column="channel_batch_no" property="channelBatchNo" jdbcType="VARCHAR" />
<result column="assign" property="assign" jdbcType="VARCHAR" />
<result column="assign_time" property="assignTime" jdbcType="TIMESTAMP" />
<result column="BLACK_WHITE_LIST" property="blackWhiteList" jdbcType="VARCHAR" />
<result column="PK_ID" property="pkId" jdbcType="VARCHAR" />
<result column="SORT_STATUS" property="sortStatus" jdbcType="VARCHAR" />
<result column="MAIL_SUBJECT" property="mailSubject" jdbcType="VARCHAR" />
<result column="CUSTOMER_ID" property="customerId" jdbcType="VARCHAR" />
<result column="CUSTOMER_NAME" property="customerName" jdbcType="VARCHAR" />
<result column="IS_WHITE" property="isWhite" jdbcType="VARCHAR" />
<result column="project_name" property="projectName" jdbcType="TIMESTAMP" />
<result column="ocr_ref_id" property="ocrRefId" jdbcType="VARCHAR" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
delete from dop_ru_recv_batch
where batch_no = #{batchNo,jdbcType=VARCHAR}
</delete>
<insert id="insert" parameterType="com.hex.dop.cps.model.DopRuRecvBatch" >
insert into dop_ru_recv_batch (batch_no, channel, recv_time,
doc_count, sender, sender_id,
sender_addr, status, update_time,
is_lock, channel_batch_no, assign,
assign_time,IS_WHITE,CUSTOMER_NAME,CUSTOMER_ID)
values (#{batchNo,jdbcType=VARCHAR}, #{channel,jdbcType=VARCHAR}, #{recvTime,jdbcType=TIMESTAMP},
#{docCount,jdbcType=INTEGER}, #{sender,jdbcType=VARCHAR}, #{senderId,jdbcType=VARCHAR},
#{senderAddr,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP},
#{isLock,jdbcType=VARCHAR}, #{channelBatchNo,jdbcType=VARCHAR}, #{assign,jdbcType=VARCHAR},
#{assignTime,jdbcType=TIMESTAMP},#{isWhite,jdbcType=VARCHAR},#{customerName,jdbcType=VARCHAR},#{customerId,jdbcType=VARCHAR})
</insert>
<update id="updateByPrimaryKey" parameterType="com.hex.dop.cps.model.DopRuRecvBatch" >
update dop_ru_recv_batch
set channel = #{channel,jdbcType=VARCHAR},
recv_time = #{recvTime,jdbcType=TIMESTAMP},
doc_count = #{docCount,jdbcType=INTEGER},
sender = #{sender,jdbcType=VARCHAR},
sender_id = #{senderId,jdbcType=VARCHAR},
sender_addr = #{senderAddr,jdbcType=VARCHAR},
status = #{status,jdbcType=VARCHAR},
update_time = #{updateTime,jdbcType=TIMESTAMP},
is_lock = #{isLock,jdbcType=VARCHAR},
channel_batch_no = #{channelBatchNo,jdbcType=VARCHAR},
assign = #{assign,jdbcType=VARCHAR},
assign_time = #{assignTime,jdbcType=TIMESTAMP}
where batch_no = #{batchNo,jdbcType=VARCHAR}
</update>
<update id="updateOcrMessage" parameterType="com.hex.dop.cps.model.DopRuRecvBatch" >
update dop_ru_recv_batch
set ocr_message = #{ocrMessage,jdbcType=VARCHAR},
update_time = sysdate
where batch_no = #{batchNo,jdbcType=VARCHAR}
</update>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
select batch_no, channel, recv_time, doc_count, sender, sender_id, sender_addr, status,
update_time, is_lock, channel_batch_no, assign, assign_time,IS_WHITE,CUSTOMER_NAME,CUSTOMER_ID,OCR_REF_ID
from dop_ru_recv_batch
where batch_no = #{batchNo,jdbcType=VARCHAR}
</select>
<select id="selectAll" resultMap="BaseResultMap" >
select batch_no, channel, recv_time, doc_count, sender, sender_id, sender_addr, status,
update_time, is_lock, channel_batch_no, assign, assign_time,IS_WHITE,CUSTOMER_NAME,CUSTOMER_ID
from dop_ru_recv_batch
</select>
<select id="checkBlackOrWhite" parameterType="com.hex.dop.cps.model.DopReAddrBook" resultType="map">
select a.IS_WHITE flag
from DOP_RE_ADDR_BOOK a
WHERE a.IS_DEL='0'
<if test="contactId!=null and contactId!=''">
and a.contact_id=#{contactId,jdbcType=VARCHAR}
</if>
<if test="email!=null and email!=''">
and a.email IN (
SELECT b.EMAIL FROM DOP_RE_ADDR_MAIL b WHERE b.IS_DEL = '0'
AND a.CONTACT_ID = b.CONTACT_ID
)
</if>
<if test="faxNo!=null and faxNo!=''">
and a.FAX_NO=#{faxNo,jdbcType=VARCHAR}
</if>
<if test="status!=null and status!=''">
and a.status=#{status,jdbcType=VARCHAR}
</if>
</select>
<select id="queryWaitSortCase" resultMap="BaseResultMap" parameterType="com.hex.dop.cps.vo.CaseHandelQueryInfo">
select ru.case_no,r.batch_no, r.channel, r.recv_time, r.sender_addr, r.status, r.assign, r.status SORT_STATUS,
I.PK_ID, I.MAIL_SUBJECT,
<!-- info.CONTACT_NAME as sender, -->
C.name as sender,C.CUSTOMID as sender_id,
decode(r.doc_count, 0, (select count(1) from dop_ru_batch_doc d, dop_ru_doc_file f where d.doc_id=f.doc_id and d.batch_no=r.batch_no and f.extend_name!='邮件正文'),
r.doc_count) as doc_count,r.IS_WHITE,
NVL(info.IS_WHITE,'0') as BLACK_WHITE_LIST
from dop_ru_recv_batch r
<if test="channel!=null and channel !='03'">
LEFT JOIN DOP_MAIL_INFO I ON R.BATCH_NO = I.BATCH_NO
LEFT JOIN DOP_RE_ADDR_BOOK info on (info.email=r.sender_addr or info.FAX_NO=r.sender_addr)
</if>
<if test="channel!=null and channel =='03'">
INNER JOIN DOP_MAIL_INFO I ON R.BATCH_NO = I.BATCH_NO
LEFT JOIN (select m.BUSI_ORG_ID,m.CONTACT_NAME,m.IS_WHITE,
(select LISTAGG(email,',') WITHIN GROUP( ORDER BY email) from DOP_RE_ADDR_MAIL
where is_del = '0' and CONTACT_ID = m.CONTACT_ID ) AS EMAIL,
(select LISTAGG(EMAIL_SUFFIX,',') WITHIN GROUP( ORDER BY EMAIL_SUFFIX) from DOP_RE_ADDR_MAIL
where is_del = '0' and CONTACT_ID = m.CONTACT_ID ) AS EMAIL_SUFFIX,IS_DEL
from DOP_RE_ADDR_BOOK m where m.is_del ='0' ) info
on (info.EMAIL LIKE '%'||r.sender_addr||'%'
or info.EMAIL_SUFFIX LIKE '%'||SUBSTR(r.sender_addr,INSTR(r.sender_addr, '@'))||'%')
</if>
LEFT JOIN dop_ru_case ru ON r.batch_no=ru.batch_no
LEFT JOIN DOP_RE_ADDR_BOOK info on (info.email=r.sender_addr or info.FAX_NO=r.sender_addr)
left join ${pms.schema}.HEX_PMS_CUSTOM C on info.BUSI_ORG_ID=C.CUSTOMID
and info.is_del ='0'
where 1 = 1
<if test="channel!=null and channel!=''">
and r.channel = #{channel,jdbcType=VARCHAR}
</if>
<if test="status!=null and status!=''">
and r.status= #{status,jdbcType=VARCHAR}
</if>
<if test="batchNo!=null and batchNo!=''">
and r.batch_no= #{batchNo,jdbcType=VARCHAR}
</if>
<if test=" startDate!=null and startDate!='' "><![CDATA[ and r.recv_time>=to_date(#{startDate},'yyyy-mm-dd') ]]> </if>
<if test=" endDate!=null and endDate!='' "><![CDATA[ and r.recv_time<=to_date(#{endDate},'yyyy-mm-dd')+1]]></if>
ORDER BY r.recv_time desc
</select>
<update id="recvBatchSetAssign" parameterType="java.util.Map">
UPDATE dop_ru_recv_batch set assign = #{assign,jdbcType=VARCHAR},
assign_time = #{assignTime,jdbcType=TIMESTAMP}
WHERE batch_no = #{batchNo,jdbcType=VARCHAR} and status != '02' and status != '04'
<if test="userId!=null and userId!=''">
and (assign = #{userId,jdbcType=VARCHAR} or assign is null)
</if>
</update>
<update id="updateRecvBatchStatus" parameterType="java.util.Map">
update dop_ru_recv_batch set status=#{status,jdbcType=VARCHAR}
, update_time=sysdate
<if test="sortResult!=null and sortResult!=''">
,sort_result=#{sortResult, jdbcType=VARCHAR}
</if>
<if test="docCount!=null and docCount!=''">
, doc_count = #{docCount,jdbcType=INTEGER}
</if>
<if test="assign!=null and assign!=''">
,assign = #{assign,jdbcType=VARCHAR}
</if>
where batch_no = #{batchNo,jdbcType=VARCHAR}
<if test="status=='02'">
and (status='01' or status='06' or status='07' or status='08' or status='09')
</if>
<if test="assign!=null and assign!=''">
and (assign = #{assign,jdbcType=VARCHAR} or assign is null)
</if>
</update>
<select id="getRecvBatchInfoByCaseNo" parameterType="java.util.Map" resultMap="BaseResultMap">
select batch_no, channel, recv_time, doc_count, sender, sender_id, sender_addr, status,
update_time, is_lock, channel_batch_no, assign, assign_time,IS_WHITE,CUSTOMER_NAME,CUSTOMER_ID
from dop_ru_recv_batch b
where status='02' and
exists (
select 1 from dop_ru_case c where c.case_no = #{caseNo, jdbcType=VARCHAR} and c.BATCH_NO=b.BATCH_NO
)
</select>
<select id="getRecvBatchInfoByCaseNoNoLimitStatus" parameterType="java.util.Map" resultMap="BaseResultMap">
select batch_no, channel, recv_time, doc_count, sender, sender_id, sender_addr, status,
update_time, is_lock, channel_batch_no, assign, assign_time,IS_WHITE,CUSTOMER_NAME,CUSTOMER_ID
from dop_ru_recv_batch b
where 1=1 and
exists (
select 1 from dop_ru_case c where c.case_no = #{caseNo, jdbcType=VARCHAR} and c.BATCH_NO=b.BATCH_NO
)
</select>
<select id="checkTaskIsHasAssiged" resultType="java.lang.String" parameterType="java.lang.String">
select assign from dop_ru_recv_batch where batch_no = #{batchNo,jdbcType=VARCHAR}
</select>
<select id="getWaitSortCaseWithPage" resultMap="BaseResultMap" parameterType="com.hex.dop.cps.vo.FaxInfoQueryInfo">
select * from (
select r.batch_no, r.channel, r.recv_time,r.assign_time, r.sender_addr, r.status,r.ocr_message,
lu.user_name as assign,<!--r.assign,-->
r.status SORT_STATUS,
I.PK_ID, I.MAIL_SUBJECT,
C.name as sender,C.CUSTOMID as sender_id,
decode(r.doc_count, 0, (select count(1) from dop_ru_batch_doc d, dop_ru_doc_file f where d.doc_id=f.doc_id and d.batch_no=r.batch_no and f.extend_name!='邮件正文'),
r.doc_count) as doc_count,r.IS_WHITE,
NVL(info.IS_WHITE,'0') as BLACK_WHITE_LIST
<if test="type!=null and type == 2">
,
( select wm_concat(cas.project_name)
from dop_ru_case cas
where cas.batch_no = r.batch_no and project_name is not null) as project_name
,
( select wm_concat(cas.customer_name)
from dop_ru_case cas
where cas.batch_no = r.batch_no and customer_name is not null) as customer_name
</if>
from dop_ru_recv_batch r
left join t_gf_loginuser lu
on r.assign = lu.user_id
<if test="channel!=null and channel !='03'">
LEFT JOIN DOP_MAIL_INFO I ON R.BATCH_NO = I.BATCH_NO
</if>
<if test="channel!=null and channel =='03'">
INNER JOIN DOP_MAIL_INFO I ON R.BATCH_NO = I.BATCH_NO
</if>
LEFT JOIN DOP_RE_ADDR_BOOK info on (info.email=r.sender_addr or info.FAX_NO=r.sender_addr)
left join ${pms.schema}.HEX_PMS_CUSTOM C on info.BUSI_ORG_ID=C.CUSTOMID
and info.is_del ='0'
where 1 = 1
<if test="channel!=null and channel!=''">
and r.channel in
<foreach item="item" index="index" collection="channel.split(',')" open="(" separator="," close=")">
'${item}'
</foreach>
</if>
<if test="channels!=null and channels!=''">
and r.channel = #{channels,jdbcType=VARCHAR}
</if>
<choose>
<when test="type == 1">
AND r.status in('01','06','07','08','09')
</when>
<when test="type == 2">
AND r.status in('02','04')
</when>
</choose>
<if test="batchNo!=null and batchNo!=''">
and r.batch_no like CONCAT(CONCAT('%',#{batchNo,jdbcType=VARCHAR}),'%')
</if>
<if test=" startDate!=null and startDate!='' "><![CDATA[ and r.recv_time>=to_date(#{startDate},'yyyy-mm-dd') ]]> </if>
<if test=" endDate!=null and endDate!='' "><![CDATA[ and r.recv_time<=to_date(#{endDate},'yyyy-mm-dd')+1]]></if>
<if test="status!=null and status.size>0">
AND r.status IN
<foreach collection="status" item="item" open="(" close=")" separator=",">
'${item}'
</foreach>
</if>
<if test="batchNos!=null and batchNos.size>0">
AND r.batch_no IN
<foreach collection="batchNos" item="item" open="(" close=")" separator=",">
'${item}'
</foreach>
</if>
)
<where>
<if test=" projectName!=null and projectName!='' "> and project_name like '%' || #{projectName} || '%'</if>
<if test=" customName!=null and customName!='' "> and customer_name like '%' || #{customName} || '%'</if>
</where>
ORDER BY recv_time desc
</select>
<select id="queryRecvMailList" resultMap="BaseResultMap" parameterType="java.util.Map">
select * from (
select DISTINCT r.batch_no, r.channel, r.recv_time, COALESCE(r.assign_time,r.update_time) as assign_time,r.sender_addr, r.status,
r.ocr_message,
<!-- 邮件收件箱-待办任务/已办任务-领用者，r.assign,-->
(select lu.user_name from t_gf_loginuser lu where r.assign = lu.user_id) as assign,
r.status SORT_STATUS,
I.PK_ID, I.MAIL_SUBJECT,
decode(r.doc_count, 0, (select count(1) from dop_ru_batch_doc d, dop_ru_doc_file f where d.doc_id=f.doc_id and d.batch_no=r.batch_no and f.extend_name!='邮件正文'),
r.doc_count) as doc_count,r.IS_WHITE as BLACK_WHITE_LIST,r.CUSTOMER_NAME as SENDER,r.CUSTOMER_ID as SENDER_ID
<if test="type!=null and type == 2">
,
( select
LISTAGG(cas.project_name, ',') WITHIN GROUP (ORDER BY cas.project_name)
from dop_ru_case cas
where cas.batch_no = r.batch_no and project_name is not null) as project_name
,
( select
LISTAGG(cas.customer_name, ',') WITHIN GROUP (ORDER BY cas.customer_name)
from dop_ru_case cas
where cas.batch_no = r.batch_no and customer_name is not null) as customer_name
</if>
from dop_ru_recv_batch r
left JOIN DOP_MAIL_INFO I ON R.channel_batch_no = I.BATCH_NO
where 1 = 1
<if test="channel!=null and channel!=''">
and r.channel = #{channel,jdbcType=VARCHAR}
</if>
<if test="flag!=null and flag!=''">
<choose>
<when test="flag=='1'.toString()" >
AND r.status in ('01','06','07','08','09')
</when>
<when test="flag=='2'.toString()" >
AND r.status in ('02','04')
</when>
</choose>
</if>
<if test="status!=null and status!=''">
AND r.status = #{status,jdbcType=VARCHAR}
</if>
<if test="statuses != null and statuses !=''">
and r.status in
<foreach collection="statuses.split(',')" item="item" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
</if>
<if test="batchNo!=null and batchNo!=''">
and r.batch_no like CONCAT(CONCAT('%',#{batchNo,jdbcType=VARCHAR}),'%')
</if>
<if test=" startDate!=null and startDate!='' "><![CDATA[ and r.recv_time>=to_date(#{startDate},'yyyy-mm-dd') ]]> </if>
<if test=" endDate!=null and endDate!='' "><![CDATA[ and r.recv_time<=to_date(#{endDate},'yyyy-mm-dd')+1]]></if>
<if test="pkIds!=null and pkIds !=''">
AND I.PK_ID IN
<foreach collection="pkIds" item="item" open="(" close=")" separator=",">
'${item}'
</foreach>
</if>
)
<where>
<if test=" projectName!=null and projectName!='' "> and project_name like '%' || #{projectName} || '%'</if>
<if test=" customName!=null and customName!='' "> and customer_name like '%' || #{customName} || '%'</if>
</where>
ORDER BY recv_time desc
</select>
<update id="updateCountByBatchno" parameterType="java.lang.String">
update dop_ru_recv_batch
set doc_count = doc_count - 1
where batch_no = #{batchNo,jdbcType=VARCHAR} and doc_count > 0
</update>
<select id="getCustom" resultMap="BaseResultMap" parameterType="java.lang.String">
select LISTAGG(lg.ORG_NAME, ',') WITHIN GROUP(ORDER BY lg.ORG_NAME) as CUSTOMER_NAME,
LISTAGG(lg.BUSI_ORG_ID, ',') WITHIN GROUP(ORDER BY lg.BUSI_ORG_ID) as CUSTOMER_ID
from (
select DISTINCT info.ORG_NAME, info.BUSI_ORG_ID from (
SELECT DISTINCT b.CONTACT_ID,B.BUSI_ORG_ID,C.name ORG_NAME,
(select LISTAGG(EMAIL_SUFFIX,',') WITHIN GROUP( ORDER BY EMAIL_SUFFIX) from DOP_RE_ADDR_MAIL where is_del = '0' and
CONTACT_ID = B.CONTACT_ID and email is null ) AS EMAIL_SUFFIX
FROM DOP_RE_ADDR_BOOK B,${pms.schema}.HEX_PMS_CUSTOM C
WHERE B.IS_DEL ='0' AND B.BUSI_ORG_ID=C.CUSTOMID and
B.STATUS = '1' and B.IS_WHITE = '1'
) info
where (CASE WHEN instr(info.EMAIL_SUFFIX,SUBSTR(#{senderAddr,jdbcType=VARCHAR},INSTR(#{senderAddr,jdbcType=VARCHAR}, '@')))>0
THEN 1
ELSE 0
END) = 1) lg
</select>
<select id="getCustomAndFullMail" resultMap="BaseResultMap" parameterType="java.lang.String">
SELECT DISTINCT B.CONTACT_ID,B.BUSI_ORG_ID as CUSTOMER_ID,C.name as CUSTOMER_NAME,B.STATUS, B.IS_WHITE
FROM DOP_RE_ADDR_BOOK B,${pms.schema}.HEX_PMS_CUSTOM C,DOP_RE_ADDR_MAIL m
WHERE B.IS_DEL ='0' AND B.BUSI_ORG_ID = C.CUSTOMID and m.is_del = '0' and m.CONTACT_ID = B.CONTACT_ID and m.email is not null
and m.EMAIL = #{senderAddr,jdbcType=VARCHAR}
</select>
<update id="updateBlackWhite" parameterType="com.hex.dop.cps.model.DopRuRecvBatch">
update dop_ru_recv_batch set IS_WHITE=#{blackWhiteList,jdbcType=VARCHAR} where BATCH_NO =#{batchNo,jdbcType=VARCHAR}
</update>
</mapper>
本文介绍如何使用SQL查询特定时间段的数据，包括查询当天记录的方法及如何快速筛选近期内的数据记录。通过示例展示了如何针对学生表查询当日记录，并进一步介绍了如何查询过去5分钟内或5天内的记录。
扫一扫
2354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
