工作中所写的关于ibatis循环标签iterate的实例：
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Deliver">
<!-- 查询配送责任人 -->
<select id="DeliverOwnner.search" parameterClass="com.tpaic.callCenter.dto.UserDTO" resultClass="com.tpaic.callCenter.dto.UserDTO">
<![CDATA[
select USER_ID as userId,
USER_NAME as userName
from T_USER u
where u.user_id in
(select ur.user_id
from t_user_role ur
where ur.role_id = #roleId#
and ur.valid_flag='Y'
)
]]>
</select>
<!-- 配送处理：查询配送信息 -->
<!-- 2007-7 nzf modify 增加了一个被保险人(insured),配送类型(deliver_type)的信息与查询条件和一个配送ID的条件 -->
<select id="deliver.searchManagementInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select
*
from (select rownum rn,
a.*,
decode(sign((to_date(a.insuranceBeginTime,
'yyyy-mm-dd hh24:mi:ss') - sysdate) - 2),
1,
'moreThan',
-1,
'lessThan') as twoDaysFlag
from (select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.PROVINCE_CODE and cc.VALID_FLAG='Y' and rownum=1) provinceName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.CITY_CODE and cc.VALID_FLAG='Y' and rownum=1) cityName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.TOWN_CODE and cc.VALID_FLAG='Y' and rownum=1) townName,
t.CONTACT_MODE as contactMode,
t.isSelfGet,
t.remark,
t.insured,
t.by_user_id as byUserId,
dpo.isCheckVehicle,
t.deliver_type as deliverType,
ct1.param_value as deliverTypeName,
t.contact_people as partyName,
dc.company_name deliverCompanyName,
cd3.param_value as deliverState,
t.ISURGENCY isUrgency,
dpo.policy_number as policyNumber,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
cd1.param_value as payModeName,
nvl(decode(t.deliver_type,
'01',
pi.INSURANCE_PREMIUM,
'03',
oi.totalagreepremium),
0) as insurancePremium,
nvl(decode(t.deliver_type,
'01',
pi.VEHICLE_TAX_AMOUNT,
'03',
oi.VEHICLE_TAX_AMOUNT),
0) as carBoatPremium,
u1.user_name userName,
oi.ispayment isPayMent,
t.got_time gotTime,
to_char(t.created_date, 'yyyy-mm-dd hh24:mi') createdDate,
t.is_deliver_present isDeliverPresent,
t.deliver_company companyId,
t.RELATION_POLICY relationPolicy,
to_char(decode(t.deliver_type,
'01',
pi.insurance_begin_time,
'03',
oi.insurance_begin_time),
'yyyy-mm-dd hh24:mi:ss') as insuranceBeginTime,
to_char(decode(t.deliver_type,
'01',
pi.Insurance_End_Time,
'03',
oi.insurance_end_time),
'yyyy-mm-dd hh24:mi:ss') as insuranceEndTime,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'deliverMode'
and ct.param_id = t.deliver_mode) deliverMode,
CASE
WHEN t.isselfget = 'N' and t.deliver_time <= sysdate THEN
'1'
WHEN t.isselfget = 'N' and
to_char(t.created_date, 'D') - 1 <= 4 and
t.deliver_time < sysdate + 1 and
(select ct.param_value
from t_create_deliver_time_rule r,
t_code_table ct
where r.area_code = t.area_code
and r.valid_flag = 'Y'
and ct.code_type = '24hours'
and ct.param_id = r.late1to4_time) <
to_char(t.created_date, 'HH24:mi') THEN
'1'
WHEN t.isselfget = 'N' and
to_char(t.created_date, 'D') - 1 = 5 and
t.deliver_time < next_day(sysdate - 1, 3) and
(select ct.param_value
from t_create_deliver_time_rule r,
t_code_table ct
where r.area_code = t.area_code
and r.valid_flag = 'Y'
and ct.code_type = '24hours'
and ct.param_id = r.late5_time) <
to_char(t.created_date, 'HH24:mi') THEN
'1'
WHEN t.isselfget = 'N' and
to_char(t.created_date, 'D') - 1 >= 6 and
t.deliver_time < next_day(sysdate - 1, 3) THEN
'1'
ELSE
'0'
END ruleFlag,
(select tci.vehicle_number
from t_car_info tci
where dpo.car_id = tci.car_id
and tci.valid_flag = 'Y' and rownum=1) vehicleNumber,
dpo.bussiness_type bussinessType
from T_DELIVER_INFOMATION t,
t_deliver_policy dpo,
T_POLICY_INFORMATION pi,
t_out_insurance_info oi,
t_deliver_company dc,
t_sys_param cd3,
T_CODE_TABLE cd1,
t_user u1,
T_CODE_TABLE ct1
where t.deliver_id = dpo.deliver_id(+)
and dpo.policy_number = pi.policy_id(+)
and oi.policy_pact_id(+) = dpo.policy_number
and dc.company_id(+) = t.deliver_company
and cd3.PARAM_TYPE = 'deliverState'
and cd3.PARAM_ID = t.deliver_state_code
and cd1.CODE_TYPE = 'payMode'
and cd1.PARAM_ID = t.pay_mode
and ct1.CODE_TYPE = 'deliverType'
and ct1.PARAM_ID = t.deliver_type
and u1.user_id = t.by_user_id
and t.area_code = #areaCode#
and t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
<![CDATA[
t.DELIVER_COMPANY = #deliverCompany#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="currentUserID">
<![CDATA[
t.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<!-- 配送类型 -->
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdStart">
<![CDATA[
t.deliver_id >= #deliverIdStart#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdEnd">
<![CDATA[
t.deliver_id <= #deliverIdEnd#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="byUserId">
<![CDATA[
t.BY_USER_ID = #byUserId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="byUserIdName">
<![CDATA[
t.BY_USER_ID = (select user_id from t_user where user_name = #byUserIdName#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="underWriteTimeFrom">
<![CDATA[
oi.UNDERWRITETIME >= to_date(#underWriteTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="underWriteTimeTo">
<![CDATA[
oi.UNDERWRITETIME <= to_date(#underWriteTimeTo#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insuranceBeginTimeFrom">
<![CDATA[
((oi.insurance_begin_time >= to_date(#insuranceBeginTimeFrom#,'YYYY-MM-DD') and
oi.insurance_begin_time < to_date(#insuranceBeginTimeTo#,'YYYY-MM-DD')+1)
OR
(pi.insurance_begin_time >= to_date(#insuranceBeginTimeFrom#,'YYYY-MM-DD') and
pi.insurance_begin_time < to_date(#insuranceBeginTimeTo#,'YYYY-MM-DD')+1))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
<![CDATA[
t.pay_mode = #payMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isPayMent">
<![CDATA[
oi.ispayment = #isPayMent#
]]>
</isNotEmpty>
<!-- add by lc 2010-09-10 CALLCENTERSALES-339 配送处理中增加支付方式的查询功能 -->
<isNotEmpty prepend="AND" property="paymentIds">
t.pay_mode in
<iterate close=")" open="(" conjunction="," property="paymentIds">#paymentIds[]#</iterate>
</isNotEmpty>
<!-- 车牌 add by lc 2010-11-29 -->
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1 from t_car_info tci where dpo.car_id=tci.car_id and tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
dpo.bussiness_type = #bussinessType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="paymentBatchNo">
<![CDATA[
(select max(p.payment_batch_no)
from t_deliver_policy_payment p
where p.policy_pact_id = oi.policy_pact_id) = #paymentBatchNo#
]]>
</isNotEmpty>
<!-- 二次配送 -->
<isEqual prepend="and" property="secondDeliver" compareValue="Y" >
<![CDATA[
t.deliver_mode='02'
]]>
</isEqual>
<isEqual prepend="and" property="secondDeliver" compareValue="N" >
t.deliver_mode!='02'
</isEqual>
<!-- 超起保日期的天数 -->
<isNotEmpty prepend="AND" property="overDays">
<!-- 01为保单配送 -->
<isEqual prepend="" property="deliverType" compareValue="01">
<![CDATA[
floor(sysdate - pi.insurance_begin_time) >= to_number(#overDays#)
]]>
</isEqual>
<!-- 03为投保单配送 -->
<isEqual prepend="" property="deliverType" compareValue="03">
<![CDATA[
floor(sysdate - oi.insurance_begin_time) >= to_number(#overDays#)
]]>
</isEqual>
</isNotEmpty>
<![CDATA[
order by t.deliver_time,
t.deliver_id )a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 查询配送处理信息合计 -->
<!-- 2007-7 nzf modify 增加一个insured,deliverid,delivertype的条件 -->
<select id="deliver.searchManagementNum" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_DELIVER_INFOMATION t,
t_deliver_policy dpo,
T_POLICY_INFORMATION pi,
t_out_insurance_info oi
where t.deliver_id = dpo.deliver_id(+)
and dpo.policy_number = pi.policy_id(+)
and oi.policy_pact_id(+) = dpo.policy_number
and t.area_code = #areaCode#
and t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
<![CDATA[
t.DELIVER_COMPANY = #deliverCompany#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="currentUserID">
<![CDATA[
t.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<!-- 配送类型 -->
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdStart">
<![CDATA[
t.deliver_id >= #deliverIdStart#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdEnd">
<![CDATA[
t.deliver_id <= #deliverIdEnd#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="byUserId">
<![CDATA[
t.BY_USER_ID = #byUserId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="byUserIdName">
<![CDATA[
t.BY_USER_ID = (select user_id from t_user where user_name = #byUserIdName#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="underWriteTimeFrom">
<![CDATA[
oi.UNDERWRITETIME >= to_date(#underWriteTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="underWriteTimeTo">
<![CDATA[
oi.UNDERWRITETIME <= to_date(#underWriteTimeTo#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insuranceBeginTimeFrom">
<![CDATA[
((oi.insurance_begin_time >= to_date(#insuranceBeginTimeFrom#,'YYYY-MM-DD') and
oi.insurance_begin_time < to_date(#insuranceBeginTimeTo#,'YYYY-MM-DD')+1)
OR
(pi.insurance_begin_time >= to_date(#insuranceBeginTimeFrom#,'YYYY-MM-DD') and
pi.insurance_begin_time < to_date(#insuranceBeginTimeTo#,'YYYY-MM-DD')+1))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
<![CDATA[
t.pay_mode = #payMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isPayMent">
<![CDATA[
oi.ispayment = #isPayMent#
]]>
</isNotEmpty>
<!-- add by lc 2010-09-10 CALLCENTERSALES-339 配送处理中增加支付方式的查询功能 -->
<isNotEmpty prepend="AND" property="paymentIds">
t.pay_mode in
<iterate close=")" open="(" conjunction="," property="paymentIds">#paymentIds[]#</iterate>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1 from t_car_info tci where dpo.car_id=tci.car_id and tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
dpo.bussiness_type = #bussinessType#
]]>
</isNotEmpty>
<!-- 超起保日期的天数 -->
<isNotEmpty prepend="AND" property="overDays">
<!-- 01为保单配送 -->
<isEqual prepend="" property="deliverType" compareValue="01">
<![CDATA[
floor(sysdate - pi.insurance_begin_time) >= to_number(#overDays#)
]]>
</isEqual>
<!-- 03为投保单配送 -->
<isEqual prepend="" property="deliverType" compareValue="03">
<![CDATA[
floor(sysdate - oi.insurance_begin_time) >= to_number(#overDays#)
]]>
</isEqual>
</isNotEmpty>
<isNotEmpty prepend="AND" property="paymentBatchNo">
<![CDATA[
(select max(p.payment_batch_no)
from t_deliver_policy_payment p
where p.policy_pact_id = oi.policy_pact_id) = #paymentBatchNo#
]]>
</isNotEmpty>
<!-- 二次配送 -->
<isEqual prepend="and" property="secondDeliver" compareValue="Y" >
<![CDATA[
t.deliver_mode='02'
]]>
</isEqual>
<isEqual prepend="and" property="secondDeliver" compareValue="N" >
t.deliver_mode!='02'
</isEqual>
</select>
<!-- 2007-7-13 nzf modify 增加被保险人字段,被保险人,配送单号的查询条件,增加了一个配送类型的返回信息 -->
<!-- 配送分配：查询“配送信息表”数据 -->
<select id="deliver.searchInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.*,
(select tci.vehicle_number
from t_deliver_policy dp, t_car_info tci
where dp.car_id = tci.car_id
and dp.deliver_id = a.deliverID
and tci.valid_flag = 'Y'
and rownum = 1) vehicleNumber
from
(select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.PROVINCE_CODE and cc.VALID_FLAG='Y' and rownum=1) provinceName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.CITY_CODE and cc.VALID_FLAG='Y' and rownum=1) cityName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.TOWN_CODE and cc.VALID_FLAG='Y' and rownum=1) townName,
t.CONTACT_MODE as contactMode,
t.isSelfGet,
t.insured,
t.deliver_type deliverType,
cd5.param_value as deliverTypeName,
u.user_name as currentUserName,
t.contact_people as partyName,
t.area_code as areaCode,
cd1.param_value as areaName,
dc.company_name deliverCompanyName,
cd3.param_value as deliverState,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
cd4.param_value as payModeName,
nvl(sum(pi.INSURANCE_PREMIUM),0) as insurancePremium,
t.created_date createdDate,
k.USER_NAME byUserIdName,
nvl(t.deliver_expense,0) deliverExpense,
t.party_id partyID
from T_DELIVER_INFOMATION t
left outer join t_deliver_policy dpo on t.deliver_id = dpo.deliver_id
left outer join T_POLICY_INFORMATION pi on dpo.policy_number =
pi.policy_id
left outer join t_Out_Insurance_Info oi on dpo.policy_number =
oi.policy_pact_id
left outer join t_deliver_company dc on dc.company_id = t.deliver_company
left outer join t_user u on t.current_user_id = u.user_id
left outer join t_user k on t.by_user_id = k.user_id,
T_CODE_TABLE cd1, t_sys_param cd3,
T_CODE_TABLE cd4, T_CODE_TABLE cd5
where cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID = t.AREA_CODE
and cd3.PARAM_TYPE = 'deliverState'
and cd3.PARAM_ID = t.deliver_state_code
and cd4.CODE_TYPE = 'payMode'
and cd4.PARAM_ID = t.pay_mode
and cd5.CODE_TYPE = 'deliverType'
and cd5.PARAM_ID = t.deliver_type
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.deliver_ID=#deliverID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="currentUserID">
<![CDATA[
t.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<!-- 配送类型 -->
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType#
]]>
</isNotEmpty>
<!-- 被保险人 -->
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.INSURED = #insured#
]]>
</isNotEmpty>
<!-- 客户号 -->
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
t.party_id = #partyID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1 from t_car_info tci where dpo.car_id=tci.car_id and tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y')
]]>
</isNotEmpty>
<![CDATA[
group by t.DELIVER_ID,t.DELIVER_ADDRESS,t.CONTACT_MODE,t.isSelfGet,u.user_name,t.insured,t.area_code,cd1.param_value,dc.company_name,
cd3.param_value,to_char(t.DELIVER_TIME, 'yyyy-mm-dd'),cd4.param_value, t.created_date,
t.contact_people,t.deliver_type,cd5.param_value,k.USER_NAME,t.party_id,t.deliver_expense,t.PROVINCE_CODE,t.CITY_CODE,t.TOWN_CODE
order by t.deliver_id desc,to_char(t.DELIVER_TIME, 'yyyy-mm-dd') desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 查询“配送信息表”数据 -->
<!-- 2007-7-16 nzf 修改,增加两个条件 insured,deliver_type和返回值 -->
<select id="deliver.searchAllStateInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.PROVINCE_CODE and cc.VALID_FLAG='Y' and rownum=1) provinceName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.CITY_CODE and cc.VALID_FLAG='Y' and rownum=1) cityName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.TOWN_CODE and cc.VALID_FLAG='Y' and rownum=1) townName,
t.CONTACT_MODE as contactMode,
t.area_code as areaCode,
t.insured,
t.deliver_type deliverType,
dtn.param_value deliverTypeName,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
t.contact_people as partyName,
cd1.param_value as areaName,
cd3.param_value as deliverState,
t.ischeckvehicle as isCheckVehicle,
dc.company_name deliverCompanyName,
t.is_deliver_present isDeliverPresent,
nvl(t.carboat_premium,0) carboatPremium,
nvl(t.all_premium,0) allPremium ,
t.deliver_state_code deliverStateCode,
(select tci.vehicle_number
from t_deliver_policy dp, t_car_info tci
where dp.car_id = tci.car_id
and dp.deliver_id = t.deliver_id
and tci.valid_flag = 'Y'
and rownum = 1) vehicleNumber,
(select tp.bussiness_type
from t_deliver_policy tp
where tp.deliver_id = t.deliver_id
and rownum = 1) bussinessType
from T_DELIVER_INFOMATION t ,t_deliver_company dc, T_CODE_TABLE cd1
,t_sys_param cd3 ,t_user u , t_code_table dtn
where dc.company_id(+) = t.deliver_company
and cd1.CODE_TYPE='area' and cd1.PARAM_ID=t.AREA_CODE
and cd3.PARAM_TYPE='deliverState' and cd3.PARAM_ID=t.deliver_state_code
and t.current_user_id=u.user_id(+)
and dtn.code_type='deliverType' and dtn.param_id=t.deliver_type
]]>
<isNotEmpty prepend="AND" property="currentUserID">
<![CDATA[
t.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured = #insured:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
exists (select 1
from t_deliver_policy tp
where tp.deliver_id = t.deliver_id
and tp.bussiness_type = #bussinessType#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
t.deliver_Mode = (#deliverMode#)
]]>
</isNotEmpty>
<![CDATA[
order by t.deliver_id desc) a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 查询需打印的配送信息数据 --><!-- 使用分公司的地址 -->
<select id="deliver.searchPrintList" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
t.CONTACT_MODE as contactMode,
u.user_name as currentUserName,
t.contact_people as partyName,
t.contact_people as contactPeople,
t.area_code as areaCode,
t.insured,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
bt.name as senderCompany,
bt.address as senderAddress,
bt.PHONE_CUSTOMER_SERVICE as senderContactMode,
cd1.param_value as areaName,
cd2.param_value as payModeName,
nvl(sum(pi.insurance_premium),0) as insurancePremium,
nvl(sum(pi.VEHICLE_TAX_AMOUNT),0) as vechileTaxAmount,
getdeliverpresentinfo(t.deliver_id) as presentName,
GetPolicyNumber(t.deliver_id) policyNumber,
get_insuranceData(t.deliver_id) insuranceData,
pi.policy_type policyType,
i.param_value as deliverTypeName,
t.isSelfGet as isSelfGet,
dp.isCheckVehicle,
cu.user_name as processName,
t.deliver_type deliverType,
di.company_name deliverCompany
from t_branch_team bt,T_DELIVER_INFOMATION t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area' and cd1.PARAM_ID = t.AREA_CODE
left outer join t_user cu on cu.user_id=t.by_user_id
left outer join t_user u on t.current_user_id = u.user_id
left outer join t_deliver_policy dp on dp.deliver_id = t.deliver_id
left outer join t_policy_information pi on pi.policy_id = dp.policy_number
left outer join t_code_table cd2 on cd2.code_type='payMode' and cd2.param_id=t.pay_mode
left outer join t_code_table i on t.deliver_type = i.param_id
and i.code_type = 'deliverType'
left outer join t_deliver_company di on di.company_id= t.deliver_company
where
t.DELIVER_ID =#deliverID#
and bt.branch_id=(select branch_id from t_user where um_code=#createdBy# and valid_flag='Y' and rownum=1)
group by t.DELIVER_ID ,
t.DELIVER_ADDRESS,
t.CONTACT_MODE,
u.user_name,
t.insured,
t.contact_people,
t.area_code,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd'),
bt.name,
bt.address,
bt.PHONE_CUSTOMER_SERVICE,
cd1.param_value,
cd2.param_value,
t.insured,
pi.policy_type,
cu.user_name,
t.deliver_type,
i.param_value,
t.isSelfGet,
dp.isCheckVehicle,
di.company_name
]]>
</select>
<select id="deliver.searchOutPrintList" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
t.CONTACT_MODE as contactMode,
u.user_name as currentUserName,
t.contact_people as partyName,
t.contact_people as contactPeople,
t.area_code as areaCode,
t.insured,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
bt.name as senderCompany,
bt.address as senderAddress,
bt.PHONE_CUSTOMER_SERVICE as senderContactMode,
cd1.param_value as areaName,
cd2.param_value as payModeName,
cu.user_name as processName,
sum(oii.totalagreepremium) as insurancePremium,
sum(oii.vehicle_tax_amount) as vechileTaxAmount,
getdeliverpresentinfo(t.deliver_id) as presentName,
GET_POLICY_PACT_NUMBER(t.deliver_id) policyNumber,
get_insuranceData(t.deliver_id) insuranceData,
i.param_value as deliverTypeName,
t.isSelfGet as isSelfGet,
t.deliver_type deliverType,
dp.isCheckVehicle,
cu.user_name as processName,
di.company_name deliverCompany
from t_branch_team bt,T_DELIVER_INFOMATION t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area' and cd1.PARAM_ID = t.AREA_CODE
left outer join t_user cu on cu.user_id=t.by_user_id
left outer join t_user u on t.current_user_id = u.user_id
left outer join t_deliver_policy dp on dp.deliver_id = t.deliver_id
left outer join t_out_insurance_info oii on oii.policy_pact_id= dp.policy_number
left outer join t_code_table cd2 on cd2.code_type='payMode' and cd2.param_id=t.pay_mode
left outer join t_code_table i on t.deliver_type = i.param_id
and i.code_type = 'deliverType'
left outer join t_deliver_company di on di.company_id= t.deliver_company
where t.DELIVER_ID =#deliverID#
and bt.branch_id=(select branch_id from t_user where um_code=#createdBy# and valid_flag='Y' and rownum=1)
group by t.DELIVER_ID ,
t.DELIVER_ADDRESS,
t.CONTACT_MODE,
u.user_name,
t.insured,
t.contact_people,
t.area_code,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd'),
bt.name,
bt.address,
bt.PHONE_CUSTOMER_SERVICE,
cd1.param_value,
cd2.param_value,
t.insured,
cu.user_name,
i.param_value,
t.deliver_type,
t.isSelfGet,
dp.isCheckVehicle,
di.company_name
]]>
</select>
<!-- 查询配送信息表记录条数 2009-11-17 fanxl 加入客户号，被保险人 -->
<select id="deliver.searchAmount" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(1) from
T_DELIVER_INFOMATION t
where 1=1
]]>
<isNotEmpty prepend="AND" property="createdBy">
<![CDATA[
t.area_code = (select area_code from t_user where um_code=#createdBy#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people=#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="currentUserID">
<![CDATA[
t.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.DELIVER_TYPE = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResult">
<![CDATA[
t.deliver_result = #deliverResult:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="byUserId">
t.by_user_id in (select distinct user_id from t_user a where a.user_id=to_number(#byUserId:varchar#)
or a.um_code = (select parent_um_code from t_user b where b.user_id=to_number(#byUserId:varchar#)))
</isNotEmpty>
<!-- 加入客户号，被保险人 -->
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
t.party_id=#partyID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.DELIVER_ID in (select deliver_id from t_deliver_policy where INSURED=#insured#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
exists (select 1
from t_deliver_policy tp
where tp.deliver_id = t.deliver_id
and tp.bussiness_type = #bussinessType#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
t.deliver_Mode = (#deliverMode#)
]]>
</isNotEmpty>
</select>
<!-- 查询特定配送单对应的保单 -->
<select id="deliver.searchPolicyList" parameterClass="com.tpaic.callCenter.dto.PolicyInfoDTO" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select p.policy_id as policyID,
p.insurance_premium as insurancePremium,
(select m.party_name from t_customer m where party_id = p.party_id) as partyName,
p.party_id as partyID,
di.contact_people as contactPeople,
di.deliver_company as deliverCompany,
di.contact_mode as contactMode,
di.deliver_address as deliverAddress,
p.policy_state as policyState,
(select sp.param_value
from t_sys_param sp
where sp.param_id = p.policy_state
and sp.param_type = 'policyState') policyStateValue,
'' as deliverStateValue
from t_policy_Information p, t_deliver_infomation di, t_deliver_policy dp
where di.deliver_id = dp.deliver_id
and dp.policy_number = p.policy_id
and di.deliver_id = #deliverID#
union
select oi.policy_pact_id,
to_char(oi.totalagreepremium),
(select cu.party_name from t_customer cu where party_id = oi.party_id),
oi.party_id,
di.contact_people as contactPeople,
di.deliver_company as deliverCompany,
di.contact_mode as contactMode,
di.deliver_address as deliverAddress,
oi.insurance_state as policyState,
(select sp.param_value
from t_sys_param sp
where sp.param_id = oi.insurance_state
and sp.param_type = 'outPolicyState') policyStateValue,
(select sp.param_value
from t_sys_param sp
where sp.param_id = oi.deliver_state
and sp.param_type = 'deliverOutState') deliverStateValue
from t_out_insurance_info oi,
t_deliver_infomation di,
t_deliver_policy dp
where di.deliver_id = dp.deliver_id
and dp.policy_number = oi.policy_pact_id
and di.deliver_id = #deliverID#
]]>
</select>
<!-- 修改配送信息表部分数据 -->
<update id="deliver.updateInfo" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update T_DELIVER_INFOMATION d
set d.updated_date = sysdate,
d.CHANGE_TIME = sysdate
<isNotEmpty prepend="," property="deliverExpense">
<![CDATA[
d.deliver_expense = #deliverExpense#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverCompanyResponse">
<![CDATA[
d.delivercompany_response = #deliverCompanyResponse#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverResult">
<![CDATA[
d.DELIVER_RESULT = #deliverResult#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="failureReason">
<![CDATA[
d.FAILURE_REASON = #failureReason#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverStateCode">
<![CDATA[
d.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="actualPayMode">
<![CDATA[
d.actual_pay_mode = #actualPayMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="actualPremium">
<![CDATA[
d.actual_premium = #actualPremium:NUMBER#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="carBoatPremium">
<![CDATA[
d.carBoat_Premium = #carBoatPremium:NUMBER#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="allPremium">
<![CDATA[
d.all_Premium = #allPremium:NUMBER#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="remark">
<![CDATA[
d.remark = #remark#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="untreadCause">
<![CDATA[
d.UNTREAD_CAUSE = #untreadCause#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverMode">
<![CDATA[
d.deliver_mode = #deliverMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="updatedBy">
<![CDATA[
d.updated_by = #updatedBy#
]]>
</isNotEmpty>
<isEqual prepend="," property="updateDeliverTimeFlag" compareValue="1" >
<![CDATA[
d.deliver_time=trunc(sysdate)
]]>
</isEqual>
<isEqual prepend="," property="updateDeliverTimeFlag" compareValue="1" >
<![CDATA[
d.DELIVER_STATE_CODE = '02'
]]>
</isEqual>
<isNotEmpty prepend="," property="localTrackState">
<![CDATA[
d.local_TRACK_state = #localTrackState#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="appointTrackTime">
<![CDATA[
d.appoint_track_time = to_date(#appointTrackTime#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="localTrackRemark">
<![CDATA[
d.local_track_remark=#localTrackRemark#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="contactPeople">
<![CDATA[
d.contact_people=#contactPeople#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverTime">
<![CDATA[
d.deliver_time = to_date(#deliverTime#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="contactMode">
<![CDATA[
d.contact_mode=#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="payMode">
<![CDATA[
d.pay_mode=#payMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="insured">
<![CDATA[
d.insured=#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverType">
<![CDATA[
d.deliver_type=#deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="otherProvince">
<![CDATA[
d.other_province=#otherProvince#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="provinceCode">
<![CDATA[
d.province_code=#provinceCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="cityCode">
<![CDATA[
d.city_code=#cityCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="townCode">
<![CDATA[
d.town_code=#townCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverAddress">
<![CDATA[
d.deliver_address=#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="insuranceData">
<![CDATA[
d.insurance_Data=#insuranceData#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isDeliverPresent">
<![CDATA[
d.is_deliver_present=#isDeliverPresent#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isSelfGet">
<![CDATA[
d.isSelfGet = #isSelfGet#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isLost">
<![CDATA[
d.IS_LOST = #isLost#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="lostParts">
<![CDATA[
d.LOST_PARTS = #lostParts#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="lostHandleResult">
<![CDATA[
d.LOST_HANDLE_RESULT = #lostHandleResult#
]]>
</isNotEmpty>
<isEqual prepend="," property="isResultInputTime" compareValue="1">
<![CDATA[
d.RESULT_INPUT_TIME=sysdate
]]>
</isEqual>
where d.DELIVER_ID = #deliverID:NUMBER#
</update>
<!-- 标准件事前验车申请核保时上传了附件,配送任务自动结束 -->
<statement id="deliver.updateInfoByDeliverID" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
UPDATE t_deliver_infomation t
SET t.deliver_state_code = '04',
t.delivercompany_response = '0',
t.deliver_result = '0',
t.result_input_time = sysdate,
t.updated_date = sysdate,
t.updated_by = #updatedBy#
WHERE t.deliver_type = '06'
AND t.deliver_state_code <> '04'
AND t.deliver_id = #deliverID#
AND EXISTS (SELECT 1
FROM t_insurance_info ti, t_deliver_policy tp
WHERE ti.policy_pact_id = tp.policy_number
AND ti.check_car_flag = '1'
AND tp.deliver_id = t.deliver_id)
]]>
</statement>
<!-- 修改配送单状态，配送责任人 -->
<update id="deliver.updateState" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update T_DELIVER_INFOMATION d set d.DELIVER_STATE_CODE = #deliverStateCode:VARCHAR#, d.CURRENT_USER_ID = to_number(#currentUserID:VARCHAR#), d.ROLE_ID = to_number(#roleID:VARCHAR#),d.updated_date=sysdate,d.deliver_Company
=(#deliverCompany:VARCHAR#) where d.DELIVER_ID = to_number(#deliverID:VARCHAR#)
</update>
<!-- 修改配送单状态 -->
<update id="deliver.updateDeliverStatus" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update T_DELIVER_INFOMATION d set d.DELIVER_STATE_CODE = #deliverStateCode:VARCHAR#,d.updated_date=sysdate
<isNotEmpty prepend="," property="updatedBy">
<![CDATA[
d.updated_by = #updatedBy:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="gotTime">
<![CDATA[
d.got_time = sysdate
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="currentUserID">
<![CDATA[
d.current_user_id = #currentUserID#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="roleID">
<![CDATA[
d.role_id = #roleID#
]]>
</isNotEmpty>
where d.DELIVER_ID = to_number(#deliverID:VARCHAR#)
</update>
<!-- 已废弃 配送预约：查询客户列表 2007-7-24wjz修改 用函数将多行电话合并到同一行，解决原来翻页行数错误的问题 -->
<select id="deliverBook.searchCustomerList" parameterClass="com.tpaic.callCenter.dto.CustomerDTO" resultClass="com.tpaic.callCenter.dto.CustomerDTO">
<![CDATA[
select t.*,
(select tct.param_value
from T_CODE_TABLE tct
where tct.CODE_TYPE = 'area'
and tct.PARAM_ID = areaCode) areaName
from (select rownum rn, a.*
from (select distinct c.party_id as partyId,
c.party_name as partyName,
c.contact_address as contactAddress,
c.area_code areaCode
from t_customer c,
(select p.party_id, p.car_id
from t_policy_information p
where p.policy_state = #policyState:VARCHAR#) b
where c.party_id = b.party_id
and exists (select 1
from t_customer_task ct
where ct.party_id = c.party_id
and ct.car_id = b.car_id
and ct.status = '1'
and ct.current_user_id = to_number(#currentUserId:VARCHAR#))
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
c.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.AREA_CODE = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
)a
) t
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 已废弃 配送预约：查询客户列表长度 -->
<select id="deliverBook.searchCustomerAmount" parameterClass="com.tpaic.callCenter.dto.CustomerDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from (select distinct c.party_id as partyId,
c.party_name as partyName,
c.area_code as areaCode,
c.contact_address as contactAddress
from t_customer c,
(select p.party_id, p.car_id
from t_policy_information p
where p.policy_state = #policyState:VARCHAR#) b
where c.party_id = b.party_id
and exists (select 1
from t_customer_task ct
where ct.party_id = c.party_id
and ct.car_id = b.car_id
and ct.status = '1'
and ct.current_user_id = to_number(#currentUserId:VARCHAR#))
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
c.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.AREA_CODE = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
)
]]>
</select>
<!-- 配送预约：查询客户对应的保单 ，2007-4-3wjz修改，添加显示保单状态列 -->
<!-- 2007-8-3 nzf 再次修改 增加提交时间、批改/注销原因、申请人 -->
<select id="deliverBook.searchCustomerPolicy" parameterClass="com.tpaic.callCenter.dto.PolicyInfoDTO" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select * from
(
select
p.policy_id as policyID,
p.insurance_premium as insurancePremium,
p.policy_state policyState,
s.param_value policyStateDesc,
c.party_name as partyName,
c.party_id as partyID,
cd.param_value as areaName,
cd.param_id as areaCode,
c.contact_phone as contactPhone,
c.contact_address as contactAddress,
ct.param_value as correctCauseName,
p.correct_cause as correctCause,
to_char(p.correct_time,'yyyy-mm-dd HH24:MI:SS') as correctTime,
u.user_name as applicants,
'01' as deliverType
from t_customer c
left outer join T_CODE_TABLE cd on cd.PARAM_ID = c.area_code
left outer join t_policy_information p on p.party_id = c.party_id
left outer join t_sys_param s on s.param_id=p.policy_state
left outer join t_code_table ct on p.correct_cause=ct.param_id and ct.code_type='correctCause'
left outer join t_user u on u.um_code=p.applicants
where 1=1 and cd.CODE_TYPE='area' and s.param_type='policyState'
]]>
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
c.party_id = to_number(#partyID:VARCHAR#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyState">
<![CDATA[
p.policy_state = #policyState:VARCHAR#
]]>
</isNotEmpty>
<![CDATA[
union
select
p.policy_pact_id as policyID,
to_char(p.TOTALAGREEPREMIUM) as insurancePremium,
'' policyState,
'' policyStateDesc,
c.party_name as partyName,
c.party_id as partyID,
cd.param_value as areaName,
cd.param_id as areaCode,
c.contact_phone as contactPhone,
c.contact_address as contactAddress,
'' as correctCauseName,
'' as correctCause,
'' as correctTime,
'' as applicants,
'03' as deliverType
from t_customer c
left outer join T_CODE_TABLE cd on cd.PARAM_ID = c.area_code
left outer join t_out_insurance_info p on p.party_id = c.party_id and p.insurance_state='9' and p.paymentmode not in ('7','8')
where 1=1 and cd.CODE_TYPE='area'
]]>
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
c.party_id = to_number(#partyID:VARCHAR#)
]]>
</isNotEmpty>
<![CDATA[
) where policyID is not null
]]>
</select>
<!-- 查询配送信息表ID -->
<select id="deliver.searchSequance" resultClass="java.lang.String">
<![CDATA[
select seq_deliver_id.nextVal
from DUAL
]]>
</select>
<!-- 添加配送信息表 -->
<!-- 2007-7-4 nzf 修改 增加一个被保险人的字段insured和配送类型字段deliver_type以及当前责任人by_user_id 2009-12-28一次配送单号 是滞 -->
<insert id="deliver.insertInfo" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
insert into T_DELIVER_INFOMATION
(DELIVER_ID,
DELIVER_STATE_CODE,
AREA_CODE,
DELIVER_ADDRESS,
DELIVER_TIME,
CONTACT_MODE,
PAY_MODE,
CONTACT_PEOPLE,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
remark,
isSelfGet,
insured,
deliver_type,
by_user_id,
DELIVER_COMPANY,
role_id,
CURRENT_USER_ID,
province_code,
city_code,
town_code,
insurance_Data,
OLD_DELIVER_ID,
is_deliver_present,
ischeckvehicle,
deliver_Mode,
other_Province,
ALL_PREMIUM,
relation_policy,
party_id
)
values
(to_number(#deliverID:VARCHAR#),
#deliverStateCode:VARCHAR#,
#areaCode:VARCHAR#,
#deliverAddress:VARCHAR#,
to_date(#deliverTime#, 'yyyy-mm-dd'),
#contactMode:VARCHAR#,
to_number(#payMode:VARCHAR#),
#contactPeople:VARCHAR#,
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
#remark:varchar#,
#isSelfGet:varchar#,
trim(#insured:varchar#),
#deliverType:varchar#,
#byUserId:varchar#,
#deliverCompany:VARCHAR#,
#roleID:VARCHAR#,
#currentUserID:VARCHAR#,
trim(#provinceCode:varchar#),
trim(#cityCode:varchar#),
trim(#townCode:varchar#),
trim(#insuranceData#),
to_number(#oldDeliverId:VARCHAR#),
trim(#isDeliverPresent#),
trim(#isCheckVehicle#),
trim(#deliverMode#),
trim(#otherProvince#),
trim(#allPremium#),
#relationPolicy:VARCHAR#,
#partyID#
)
</insert>
<insert id="deliver.insertInfoNew" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
insert into T_DELIVER_INFOMATION
(DELIVER_ID,
DELIVER_STATE_CODE,
AREA_CODE,
DELIVER_ADDRESS,
DELIVER_TIME,
CONTACT_MODE,
PAY_MODE,
CONTACT_PEOPLE,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
remark,
isSelfGet,
insured,
deliver_type,
by_user_id,
DELIVER_COMPANY,
role_id,
CURRENT_USER_ID,
province_code,
city_code,
town_code,
insurance_Data,
OLD_DELIVER_ID,
is_deliver_present,
ischeckvehicle,
deliver_Mode,
other_Province,
check_id,
ALL_PREMIUM,
relation_policy,
party_id
)
values
(to_number(#deliverID:VARCHAR#),
#deliverStateCode:VARCHAR#,
#areaCode:VARCHAR#,
#deliverAddress:VARCHAR#,
to_date(#deliverTime#, 'yyyy-mm-dd'),
#contactMode:VARCHAR#,
to_number(#payMode:VARCHAR#),
#contactPeople:VARCHAR#,
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
#remark:varchar#,
#isSelfGet:varchar#,
trim(#insured:varchar#),
#deliverType:varchar#,
(
select t.user_id byUserId
from t_user t
where t.um_code=#umCode# and rownum = 1
),
#deliverCompany:VARCHAR#,
#roleID:VARCHAR#,
#currentUserID:VARCHAR#,
trim(#provinceCode:varchar#),
trim(#cityCode:varchar#),
trim(#townCode:varchar#),
trim(#insuranceData#),
to_number(#oldDeliverId:VARCHAR#),
trim(#isDeliverPresent#),
trim(#isCheckVehicle#),
trim(#deliverMode#),
trim(#otherProvince#),
trim(#checkId#),
trim(#allPremium#),
#relationPolicy:VARCHAR#,
#partyID#
)
</insert>
<!-- 添加配送保单表 -->
<!--
<insert id="deliverPolicy.insertInfo" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
insert into T_DELIVER_POLICY (DELIVER_ID, POLICY_NUMBER, UPDATED_DATE, UPDATED_BY, CREATED_DATE, CREATED_BY,isCheckVehicle,insurance_PREMIUM) values (to_number(#deliverID:VARCHAR#),#policyID:VARCHAR#,
sysdate,#updatedBy:VARCHAR#,sysdate,#createdBy:VARCHAR#,#isCheckVehicle#,#insurancePremium#)
</insert>
-->
<!-- 添加配送保单表 -->
<insert id="deliverPolicy.insertInfo" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
insert into t_deliver_policy
(deliver_id,
policy_number,
updated_date,
updated_by,
created_date,
created_by,
ischeckvehicle,
insurance_premium,
insured,
relation_policy,
car_id,
insurance_type,
vehicle_tax_amount,
deliver_type,
BUSSINESS_TYPE)
values
(to_number(#deliverID:VARCHAR#),
#policyID:VARCHAR#,
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
#isCheckVehicle:VARCHAR#,
to_number(#insurancePremium:VARCHAR#),
#insured:VARCHAR#,
#relationPolicy:VARCHAR#,
to_number(#carId:VARCHAR#),
#insuranceType:VARCHAR#,
#vechileTaxAmount:VARCHAR#,
#deliverType:VARCHAR#,
(select decode(#deliverType#,
'01',
(select oi.BUSSINESS_TYPE
from t_policy_information pi, t_out_insurance_info oi
where oi.policy_id = #policyID#
and oi.policy_pact_id = pi.policy_pact_id
and rownum = 1),
'03',
(select BUSSINESS_TYPE
from t_out_insurance_info
where policy_pact_id = #policyID#
and rownum = 1),
'06',
(select BUSSINESS_TYPE
from t_out_insurance_info
where policy_pact_id = #policyID#
and rownum = 1),
'4')
from dual t
where rownum = 1
)
)
</insert>
<!-- 拆分配送需要修改原来的配送任务 -->
<update id="deliverPolicy.updatedDeliverPolicyInfo" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
update t_deliver_policy
set
deliver_id=(to_number(#deliverID:VARCHAR#)),
policy_number=#policyID:VARCHAR#,
updated_date=sysdate,
updated_by=#updatedBy:VARCHAR#,
created_date=sysdate,
created_by=#createdBy:VARCHAR#,
ischeckvehicle=#isCheckVehicle:VARCHAR#,
insurance_premium=to_number(#insurancePremium:VARCHAR#),
insured=#insured:VARCHAR#,
relation_policy=#relationPolicy:VARCHAR#,
car_id=to_number(#carId:VARCHAR#),
insurance_type=#insuranceType:VARCHAR#,
vehicle_tax_amount=#vechileTaxAmount:VARCHAR#,
deliver_type=#deliverType:VARCHAR#,
BUSSINESS_TYPE=(select decode(#deliverType#,
'01',
(select oi.BUSSINESS_TYPE
from t_policy_information pi, t_out_insurance_info oi
where oi.policy_id = #policyID#
and oi.policy_pact_id = pi.policy_pact_id
and rownum = 1),
'03',
(select BUSSINESS_TYPE
from t_out_insurance_info
where policy_pact_id = #policyID#
and rownum = 1),
'06',
(select BUSSINESS_TYPE
from t_out_insurance_info
where policy_pact_id = #policyID#
and rownum = 1),
'4')
from dual t
where rownum = 1
)
where deliver_id=(to_number(#oldDeliverID:VARCHAR#)) and policy_number=#policyID:VARCHAR#
</update>
<!-- 查询同一个配送单下挂着多少个配送任务 -->
<select id="deliverPolicy.deliverPolicyByDeliverID" parameterClass="com.tpaic.callCenter.dto.PoliceStateDTO" resultClass="com.tpaic.callCenter.dto.PoliceStateDTO">
select dp.policy_number policePactID
from t_deliver_policy dp,T_OUT_INSURANCE_INFO oii
where dp.policy_number=oii.policy_pact_id
and oii.deliver_state='04'
and oii.insurance_date >=#insuranceDate#
and dp.deliver_id=#oiiDeliverID#
</select>
<update id="deliverPolicy.updatedInfo" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">update t_deliver_infomation set isCheckVehicle=#isCheckVehicle# where deliver_id=#deliverID#</update>
<!-- 添加配送名单表 -->
<insert id="deliverList.insert" parameterClass="com.tpaic.callCenter.dto.DeliverListDTO">
insert into T_DELIVER_LIST (DELIVER_ID, PARTY_ID, UPDATED_DATE, UPDATED_BY, CREATED_DATE, CREATED_BY) values (to_number(#deliverID:VARCHAR#),to_number(#partyID:VARCHAR#), sysdate,#updatedBy:VARCHAR#,sysdate,#createdBy:VARCHAR#)
</insert>
<!-- 查询配送保单表信息 -->
<select id="deliver.searchDeliverPolicy" resultClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
<![CDATA[
select dp.policy_number as policyID
from t_deliver_policy dp
where 1=1
]]>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
dp.deliver_id = to_number(#deliverID:VARCHAR#)
]]>
</isNotEmpty>
</select>
<!-- 2007-7-5 nzf alter 新增老一个被保险人字段insured,deliver_type,deliver_state_code,untread_cause -->
<update id="deliver.updateDeliverInfoAG" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
update T_DELIVER_INFOMATION
set contact_People = #contactPeople:VARCHAR#,
deliver_time = to_date(#deliverTime:VARCHAR#, 'yyyy-mm-dd'),
contact_mode = #contactMode:VARCHAR#,
pay_mode = #payMode:VARCHAR#,
DELIVER_ADDRESS = #deliverAddress:VARCHAR#,
province_code = trim(#provinceCode:varchar#),
city_code = trim(#cityCode:varchar#),
town_code = trim(#townCode:varchar#),
insurance_Data = trim(#insuranceData#),
updated_date = sysdate,
remark = #remark:varchar#,
deliver_company=null
]]>
<isNotEmpty prepend="," property="deliverStateCode">
<![CDATA[
deliver_state_code = #deliverStateCode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="untreadRemark">
<![CDATA[
untread_remark = #untreadRemark:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="updatedBy">
<![CDATA[
updated_by = #updatedBy:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isSelfGet">
<![CDATA[
isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="insured">
<![CDATA[
insured = #insured:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverType">
<![CDATA[
deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isDeliverPresent">
<![CDATA[
is_deliver_present = #isDeliverPresent:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="areaCode">
<![CDATA[
area_code = #areaCode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverMode">
<![CDATA[
deliver_Mode = #deliverMode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="otherProvince">
<![CDATA[
other_province = #otherProvince:varchar#
]]>
</isNotEmpty>
where deliver_id=to_number(#deliverID#)
</update>
<!-- 2007-7-16 nzf 增加被保险人与配送类型,配送状态 -->
<select id="deliver.browserDeliverInfo" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select (select ct.param_value from t_code_table ct
where ct.code_type = 'untreadRemark'
and ct.param_id = t.untread_remark) untreadRemark,
t.deliver_id as deliverID,
t.area_code areaCode,
t.CONTACT_PEOPLE as contactPeople,
to_char(t.deliver_time, 'yyyy-mm-dd') as deliverTime,
t.contact_mode as contactMode,
t.pay_mode as payMode,
t.deliver_address as deliverAddress,
cd1.param_value as areaName,
cd2.param_value as payModeName,
c.party_id as partyID,
c.party_name as partyName,
t.remark,
t.isSelfGet,
t.insured,
t.deliver_type deliverType,
t.deliver_state_code deliverStateCode,
t.by_user_id byUserId,
t.province_code provinceCode,
t.city_code cityCode,
t.town_code townCode,
t.is_deliver_present isDeliverPresent,
t.deliver_Mode deliverMode,
t.other_province otherProvince,
t.insurance_data insuranceData
from T_DELIVER_INFOMATION t,
T_CODE_TABLE cd1,
T_CODE_TABLE cd2,
t_customer c
where t.deliver_id in (select deliver_id
from T_DELIVER_POLICY
where policy_number = #value#)
and t.deliver_state_code <> '04'
and cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID = t.AREA_CODE
and cd2.CODE_TYPE = 'payMode'
and cd2.PARAM_ID = t.pay_mode
and c.party_id = t.party_id
and rownum = 1
]]>
</select>
<update id="deliver.updateCustomerFlag" parameterClass="java.lang.String">update t_customer set priority='1' where party_id =#value#</update>
<!-- 2007-5-29wjz 用于保单处理页面的修改配送单信息时获得配送单状态和保单状态，以便后面的重新保存 -->
<select id="deliver.searchStateByPolicyId" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyStateDTO" resultClass="com.tpaic.callCenter.dto.DeliverPolicyStateDTO">
select distinct pi.policy_id policyID, pi.policy_state policyState, sp.param_value policyStateDesc, di.deliver_state_code deliverState, sp2.param_value deliverStateDesc from t_policy_information pi, t_sys_param sp, t_deliver_infomation di,
t_deliver_policy dp, t_sys_param sp2 where pi.policy_state = sp.param_id and pi.policy_id=dp.policy_number(+) and dp.deliver_id=di.deliver_id(+) and sp2.param_type= 'deliverState' and sp2.param_id=di.deliver_state_code and sp.param_type =
'policyState' and pi.policy_id= #policyId:varchar#
</select>
<!-- 2007-6-5wjz 用于礼品补送预约时 先从数据库查该客户是否有待分配和待配送的配送单，如果有，则将信息带出来修改，如果没有则新增配送单 -->
<!-- 2007-7-16 nzf 增加两个返回值insured,deliver_type,增加了一个配送单号的条件 -->
<select id="deliver.searchDeliverInfoByPartyId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select rownum,
deliverID,
deliverStateCode,
areaCode,
deliverAddress,
deliverTime,
payMode,
contactPeople,
remark,
isSelfGet,
contactMode,
partyName,
insured,
deliver_type deliverType,
areaName,
deliverMode,
otherProvince,
insuranceData
from (select di.deliver_id deliverID,
di.deliver_state_code deliverStateCode,
di.area_code areaCode,
di.deliver_address deliverAddress,
to_char(di.deliver_time, 'yyyy-mm-dd') deliverTime,
di.pay_mode payMode,
di.contact_people contactPeople,
di.remark,
di.isSelfGet isSelfGet,
di.contact_mode contactMode,
c.party_name partyName,
di.insured,
di.deliver_type,
d.param_value areaName,
di.deliver_mode deliverMode,
di.other_province otherProvince,
di.insurance_data insuranceData
from t_deliver_infomation di,
t_customer c,
t_code_table d
where c.party_id = di.party_id
and di.deliver_state_code in ('02', '05')
and d.code_type = 'area'
and di.area_code(+) = d.param_id
and di.deliver_type <> '03'
]]>
<isNotEmpty prepend="and" property="partyID">
<![CDATA[
c.party_id = #partyID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="deliverID">
<![CDATA[
di.deliver_id = #deliverID:varchar#
]]>
</isNotEmpty>
<![CDATA[
) t
where rownum = 1
]]>
</select>
<!-- 2007-6-5wjz 礼品补送时，如果有待分配和待配送的配送单，则修改配送单信息，合并配送任务 -->
<!-- 2007-7-4 nzf alter 增加了被保险人insured字段 -->
<statement id="deliver.updateDeliverInfoByPartyId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update t_deliver_infomation set deliver_State_Code = #deliverStateCode:varchar#, area_code = #areaCode:varchar#, deliver_address = #deliverAddress:varchar#, deliver_time = to_date(#deliverTime#,'yyyy-mm-dd'), contact_people =
#contactPeople:varchar#, remark = #remark:varchar#, isSelfGet = #isSelfGet:varchar#, insured=#insured:varchar#, by_user_id=#byUserId:varchar#
<isNotEmpty prepend="," property="deliverType">
<![CDATA[
deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
where deliver_id= to_number(#deliverID#)
</statement>
<!-- 2007-6-17wjz 用于打印修改配送单状态时判断该配送单的状态是否是待配送的，如果是则不需再修改并扣礼品了 -->
<select id="deliver.searchDeliverStateCodeByDeliverId" parameterClass="java.lang.String" resultClass="java.lang.String">select deliver_State_Code deliverStateCode from t_deliver_infomation where deliver_id=to_number(#value#)</select>
<!-- 2007-6-29 nzf 三期 根据配送ID查看配送单明细信息 -->
<!-- 2007-7-27 nzf 修改 去掉了当前用户ID的条件 -->
<select id="deliver.searchDeliverInfoDetailByDeliverId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
t.CONTACT_MODE as contactMode,
t.area_code as areaCode,
t.pay_mode payMode,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'payMode'
and t.pay_mode = ct.param_id) payModeName,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'payMode'
and t.ACTUAL_PAY_MODE = ct.param_id) actualPayMode,
nvl(t.ACTUAL_PREMIUM, 0) as actualPremium,
t.DELIVER_EXPENSE as deliverExpense,
t.UPDATED_DATE as updatedDate,
t.isselfget as isSelfGet,
t.insured,
t.deliver_type as deliverType,
(select ct.param_value
from T_CODE_TABLE ct
where ct.code_type = 'deliverType'
and t.deliver_type = ct.param_id) deliverTypeName,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
t.contact_people as partyName,
(select ct.param_value
from T_CODE_TABLE ct
where ct.CODE_TYPE = 'area'
and ct.PARAM_ID = t.AREA_CODE) areaName,
(select sp.param_value
from t_sys_param sp
where sp.param_type = 'deliverState'
and sp.param_id = t.deliver_state_code) deliverState,
t.deliver_state_code deliverStateCode,
(select ct.param_value
from T_CODE_TABLE ct
where ct.code_type = 'deliverResult'
and t.deliver_result = ct.param_id) deliverResult,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'deliverfUnsuccReason'
and ct.param_id = t.failure_reason) failureReason,
t.by_user_id byUserId,
t.role_id roleID,
t.CURRENT_USER_ID currentUserID,
t.deliver_company companyId,
(select di.company_name
from t_deliver_company di
where di.company_id = t.deliver_company) deliverCompany,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'withDrawalReason'
and ct.param_id = t.untread_cause) untreadCause,
t.remark,
t.ischeckvehicle isCheckVehicle,
t.insurance_data insuranceData,
t.province_code provinceCode,
t.city_code cityCode,
t.town_code townCode,
t.is_deliver_present isDeliverPresent,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'untreadRemark'
and ct.param_id = t.untread_remark) untreadRemark,
t.contact_people contactPeople,
(select ct.param_value
from t_deliver_present_detail dpd, t_code_table ct
where dpd.deliver_id = t.deliver_id
and ct.code_type = 'againdeliver'
and ct.param_id = dpd.deliver_reason
and rownum = 1) sendPresentReason,
nvl(t.carboat_premium, 0) carboatPremium,
nvl(t.all_premium, 0) allPremium,
t.deliver_mode deliverMode,
(select ct.param_value
from t_code_table ct
where ct.code_type = 'deliverMode'
and ct.param_id = t.deliver_mode) deliverModeName,
t.local_track_state localTrackState,
(select co.param_value
from t_code_table co
where co.param_id = t.local_track_state
and co.code_type = 'localTrackState'
and co.valid_flag = 'Y'
and rownum = 1) localTrackStateName,
t.local_track_remark localTrackRemark,
to_char(sysdate, 'YYYY-MM-DD') appointTrackTime,
t.check_id checkId,
(select u.user_name
from t_user u, t_customer_task ct, t_deliver_policy dp
where ct.party_id = t.party_id
and t.deliver_id = dp.deliver_id
and dp.car_id = ct.car_id
and ct.status = '1'
and u.user_id = ct.current_user_id
and rownum = 1) userName,
substr((select casc.agentid
from CC_AGENT_SOFTPHONE_CONF casc,
t_user u,
t_customer_task ct,
t_deliver_policy dp
where casc.um_code = u.um_code
and t.party_id = ct.party_id
and t.deliver_id = dp.deliver_id
and dp.car_id = ct.car_id
and ct.status = '1'
and u.user_id = ct.current_user_id
and rownum = 1),
6) agentid,
(select u.user_name
from t_user uu, t_customer_task ct, t_user u,t_deliver_policy dp
where ct.party_id = t.party_id
and t.deliver_id = dp.deliver_id
and dp.car_id = ct.car_id
and ct.status = '1'
and uu.user_id = ct.current_user_id
and u.um_code = uu.parent_um_code
and rownum = 1) teamLeaderName,
(select u.telephone
from t_user uu, t_customer_task ct, t_user u,t_deliver_policy dp
where ct.party_id = t.party_id
and t.deliver_id = dp.deliver_id
and dp.car_id = ct.car_id
and ct.status = '1'
and uu.user_id = ct.current_user_id
and u.um_code = uu.parent_um_code
and rownum = 1) teamLeaderPhone,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.PROVINCE_CODE
and cc.collection_code = 'SFBM0000'
and cc.VALID_FLAG = 'Y'
and rownum = 1) provinceName,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.CITY_CODE
and cc.collection_code = 'CSBM0000'
and cc.superior_value_code = t.province_code
and cc.VALID_FLAG = 'Y'
and rownum = 1) cityName,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.TOWN_CODE
and cc.collection_code = 'QYBM0000'
and cc.superior_value_code = t.city_code
and cc.VALID_FLAG = 'Y'
and rownum = 1) townName
from T_DELIVER_INFOMATION t
where t.DELIVER_ID = #deliverID#
]]>
</select>
<!-- 2007-7-18 nzf 查询特定配送单对应的保单 -->
<select id="deliver.searchPolicyListByDeliverId" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select * from (
select tdp.deliver_id deliverID,
tpi.policy_id policyID,
nvl(tpi.insurance_premium,0) insurancePremium,
nvl(tpi.vehicle_tax_amount,0) carBoatPremium,
ps.param_value policyState
from t_deliver_policy tdp,
t_policy_information tpi,
(select param_id,param_value from t_sys_param where param_type='policyState') ps
where tdp.policy_number=tpi.policy_id
and deliver_id=#value#
and tpi.policy_state=ps.param_id
union
select deliver_id deliverID,
oi.policy_pact_id policyID,
nvl( to_char(oi.totalagreepremium-oi.vehicle_tax_amount),0) insurancePremium,
nvl( oi.vehicle_tax_amount,0) carBoatPremium,
ps.param_value policyState
from t_deliver_policy tdp,
t_out_insurance_info oi,
(select param_id,param_value from t_sys_param where param_type='outPolicyState') ps
where tdp.policy_number=oi.policy_pact_id
and deliver_id=#value#
and oi.insurance_state=ps.param_id
)
]]>
</select>
<!-- 2007-7-18 nzf 配送退回 -->
<update id="deliver.updateDeliverStateUntread" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
update t_deliver_infomation set updated_date=sysdate
]]>
<isNotEmpty prepend="," property="deliverStateCode">
<![CDATA[
deliver_state_code = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="untreadCause">
<![CDATA[
untread_cause = #untreadCause#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverType">
<![CDATA[
deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverResult">
<![CDATA[
DELIVER_RESULT = #deliverResult:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="untreadRemark">
<![CDATA[
UNTREAD_REMARK = #untreadRemark#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="localTrackState">
<![CDATA[
local_TRACK_state = #localTrackState#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="appointTrackTime">
<![CDATA[
appoint_track_time = to_date(#appointTrackTime#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="remark">
<![CDATA[
remark = remark||#remark#
]]>
</isNotEmpty>
<![CDATA[
where deliver_id=#deliverID#
]]>
</update>
<!-- 2007-7-20 nzf 保单处理里面的补品补送信息 -->
<select id="deliver.searchPolicyPresentDeliverList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select b.*,
(select c.party_name
from t_customer c
where c.party_id = b.partyID) partyName,
(select tci.vehicle_number
from t_deliver_policy dp, t_car_info tci
where dp.car_id = tci.car_id
and dp.deliver_id = b.deliverID
and tci.valid_flag = 'Y'
and rownum = 1) vehicleNumber,
(select tp.bussiness_type
from t_deliver_policy tp
where tp.deliver_id = b.deliverID
and rownum = 1) bussinessType,
(select t.param_value
from T_CODE_TABLE t
where t.param_id = b.areaCode
and t.code_type = 'area') areaName,
(select t.param_value
from T_CODE_TABLE t
where t.param_id = b.deliverType
and t.code_type = 'deliverType') deliverTypeName,
(select t.param_value
from t_sys_param t
where t.PARAM_TYPE = 'deliverState'
and t.PARAM_ID = b.deliverStateCode) deliverState
from (select rownum rn, a.*
from (select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
t.CONTACT_MODE as contactMode,
t.area_code as areaCode,
t.insured,
t.deliver_state_code deliverStateCode,
t.deliver_type deliverType,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
ct.party_id partyID,
t.by_user_id as byUserId,
case
when t.deliver_state_code = '05' then
'0'
when to_char(t.DELIVER_TIME, 'yyyyMMdd') < to_char(sysdate, 'yyyyMMdd')
and t.deliver_state_code != '04' and t.deliver_state_code != '03' then
'1'
else
'2'
end isTimeOut,
t.failure_Reason failureReason,
t.untread_cause untreadCause,
t.ischeckvehicle as isCheckVehicle,
t.is_deliver_present isDeliverPresent
from T_DELIVER_INFOMATION t, t_customer_task ct, t_user u
where ct.party_id = t.party_id
and ct.current_user_id = u.user_id(+)
and ct.status = '1'
and ct.dispatched = '08'
and t.by_user_id= to_number(#byUserId#)
]]>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
exists (select 1
from t_customer c
where c.party_id = ct.party_id
and c.PARTY_NAME =#partyName#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured = #insured:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID =#deliverID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResult">
<![CDATA[
t.deliver_result = #deliverResult:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
exists (select 1
from t_deliver_policy tp
where tp.deliver_id = t.deliver_id
and tp.bussiness_type = #bussinessType#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
t.deliver_Mode = (#deliverMode#)
]]>
</isNotEmpty>
<![CDATA[
order by t.deliver_time desc) a) b
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
order by isTimeOut asc
]]>
</select>
<select id="deliver.searchAmountQuery" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(1) from
T_DELIVER_INFOMATION t
where 1=1
]]>
<isNotEmpty prepend="AND" property="byUserId">
<![CDATA[
t.by_user_id= to_number(#byUserId#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
exists (select 1
from t_customer c
where c.party_id = t.party_id
and c.PARTY_NAME =#partyName#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#, 'yyyy-mm-dd')+1
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.DELIVER_ADDRESS =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet = #isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.DELIVER_TYPE = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="bussinessType">
<![CDATA[
exists (select 1
from t_deliver_policy tp
where tp.deliver_id = t.deliver_id
and tp.bussiness_type = #bussinessType#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
t.deliver_Mode = (#deliverMode#)
]]>
</isNotEmpty>
</select>
<parameterMap id="deliverPresentParams" class="map">
<parameter property="deliverId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="updatedBy" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="flag" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
<parameter property="desc" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
</parameterMap>
<procedure id="deliver.insertDeliverPresent" parameterMap="deliverPresentParams">{call tss_pkg.present_rule(?,?,?,?)}</procedure>
<select id="Deliver.searchPresentDetail" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverPresentDetailDTO">
<![CDATA[
select dpd.deliver_id deliverID,
dpd.present_id presentID,
dpd.present_quantity presentQuantity,
dpd.deliver_type deliverType,
p.area_code areaCode
from t_deliver_present_detail dpd, t_present p
where p.present_id = dpd.present_id
and dpd.deliver_id = #deliverID#
and dpd.valid_flag = 'Y'
]]>
</select>
<select id="deliver.searchSerialNumberByContactId" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.datainterface.BankDTO">
<![CDATA[
select a.serial_number telNo,oii.policy_pact_id applyPolicyNo,tlp.preauthority_order_id telSaleSeqNo,'5' docType,oii.paymentmode paymentmode,oii.ISPREAUTHFLAG isPreAuthorityFlag
from t_insurance_info a,
t_record_insurance ri,
t_out_insurance_info oii,
tel_pay_preauthority tlp
where a.policy_pact_id = ri.policy_pact_id
and a.policy_pact_id = oii.policy_pact_id
and a.policy_pact_id=tlp.relatepolicypackid(+)
and a.policy_pact_id =#value#
]]>
</select>
<select id="Deliver.searchIsCheckValueAndInsurancePremium" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO" resultClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
<![CDATA[
select ischeckvehicle,
insurancePremium,
carId,
relationPolicy,
bussinessType,
vechileTaxAmount,
insuranceType
from (select t.ischeckvehicle,
to_number(t.insurance_premium) insurancePremium,
t.car_id carId,
t.relation_policy relationPolicy,
(select oii.bussiness_type
from t_out_insurance_info oii
where oii.policy_pact_id = t.policy_pact_id
and rownum = 1) bussinessType,
t.vehicle_tax_amount vechileTaxAmount,
t.insurance_type insuranceType
from t_policy_information t
where t.policy_id = #policyID#
union
select decode(oi.check_car_flag,'2',oi.ischeckvehicle,'0') ischeckvehicle,
oi.totalagreepremium insurancePremium,
oi.car_id carId,
oi.relation_policy relationPolicy,
oi.bussiness_type bussinessType,
oi.vehicle_tax_amount vechileTaxAmount,
substr(oi.policy_pact_id, 5, 3) insuranceType
from t_out_insurance_info oi
where policy_pact_id = #policyID#)
where rownum = 1
]]>
</select>
<!-- 批量置配送单成功-01 保单 实收保费、车船税 实收保费需要后台对商业险和交强险合并计算 -->
<select id="deliver.searchDeliverInfoByPartyId_01" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select
nvl(sum(tpi.insurance_premium),0) insurancePremium,
nvl(sum(tpi.vehicle_tax_amount),0) carBoatPremium
from t_deliver_infomation tdi,
t_deliver_policy tdp,
t_policy_information tpi
where tdp.policy_number=tpi.policy_id
and tdi.deliver_id=tdp.deliver_id
and tdi.deliver_id=#deliverID:varchar#
]]>
</select>
<!-- 批量置配送单成功-03 投保单 实收保费、车船税 实收保费需要后台对商业险和交强险合并计算 -->
<select id="deliver.searchOutInsuranceDeliverInfoByDeliverId" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select
nvl(sum(oii.totalagreepremium),0) insurancePremium,
nvl(sum(oii.vehicle_tax_amount),0) carBoatPremium
from t_deliver_infomation tdi,
t_deliver_policy tdp,
t_out_insurance_info oii
where tdp.policy_number=oii.policy_pact_id
and tdi.deliver_id=tdp.deliver_id
and tdi.deliver_id=#deliverID:varchar#
]]>
</select>
<!-- 置配送结果页面数据 -->
<select id="deliver.searchPolicyAndDeliverInfoByDeliverId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select tdi.deliver_id deliverID,
tdi.deliver_company deliverCompany,
nvl(sum(oii.totalagreepremium),0) actualPremium,
nvl(sum(oii.vehicle_tax_amount),0) carBoatPremium,
tdi.DELIVER_TYPE deliverType,
tdi.isselfget isSelfGet,
nvl(tdi.all_premium,0) allPremium
from t_deliver_infomation tdi,
t_deliver_policy tdp,
t_out_insurance_info oii
where tdp.policy_number=oii.policy_pact_id
and tdi.deliver_id=tdp.deliver_id
and (oii.insurance_state ='2' or oii.insurance_state ='9')
and oii.deliver_state='01'
and tdi.deliver_id=#deliverID:varchar#
group by tdi.deliver_id,
tdi.deliver_company ,
tdi.DELIVER_TYPE,
tdi.isselfget,
tdi.all_premium
union
select tdi.deliver_id deliverID,
tdi.deliver_company deliverCompany,
nvl(sum(tpi.insurance_premium),0) actualPremium,
nvl(sum(tpi.vehicle_tax_amount),0) carBoatPremium,
tdi.DELIVER_TYPE deliverType,
tdi.isselfget isSelfGet,
nvl(tdi.all_premium,0) allPremium
from t_deliver_infomation tdi,
t_deliver_policy tdp,
t_policy_information tpi
where tdp.policy_number=tpi.policy_id
and tdi.deliver_id=tdp.deliver_id
and tpi.policy_state<>'05'
and tdi.deliver_id=#deliverID:varchar#
group by tdi.deliver_id,
tdi.deliver_company,
tdi.DELIVER_TYPE,
tdi.isselfget,
tdi.all_premium
union
select tdi.deliver_id deliverID,
tdi.deliver_company deliverCompany,
0 actualPremium,
nvl(tdi.Carboat_Premium, 0) carBoatPremium,
tdi.DELIVER_TYPE deliverType,
tdi.isselfget isSelfGet,
nvl(tdi.all_premium, 0) allPremium
from t_deliver_infomation tdi
where tdi.deliver_type='05'
and tdi.deliver_id=#deliverID:varchar#
]]>
</select>
<select id="deliver.getSeqDeliverId" resultClass="java.lang.String">
<![CDATA[
select SEQ_DELIVER_ID.NEXTVAL from dual
]]>
</select>
<select id="deliver.searchPartyIdByDeliverId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select party_id partyID
from T_DELIVER_INFOMATION
where deliver_id = to_number(#deliverID#)
]]>
</select>
<select id="deliver.searchVisitBusinessInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select t.visit_business_id visitBusinessID,
t.party_name partyName,
t.contact_mode contactMode,
t.deliver_address deliverAddress,
to_char(t.visit_time,'yyyy-mm-dd hh24:mi:ss') visitTime,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updateDate,
t.created_by createBy,
t.created_date createDate,
t.area_code areaCode,
ct.param_value areaName,
to_char(t.end_time,'yyyy-mm-dd hh24:mi:ss') endTime,
t.visit_business_result visitBusinessResult
from t_visit_business t,
t_code_table ct
where t.valid_flag='Y'
and ct.param_id =t.area_code
and ct.code_type='area'
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code =#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.contact_mode = #contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.deliver_address = #deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="visitTime">
<![CDATA[
t.visit_time between to_date(#visitTime#,'YYYY-MM-DD') and to_date(#visitTime#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<![CDATA[
order by t.visit_business_id desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.searchVisitBusinessInfoNum" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from t_visit_business t
where t.valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code =#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.contact_mode = #contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAddress">
<![CDATA[
t.deliver_address = #deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="visitTime">
<![CDATA[
t.visit_time = to_date(#visitTime#,'YYYY-MM-DD')
]]>
</isNotEmpty>
</select>
<select id="deliver.getSeqPartyID" resultClass="java.lang.String">
<![CDATA[
select seq_party_id.nextval from dual
]]>
</select>
<select id="deliver.getSeqDeliverAppointID" resultClass="java.lang.String">
<![CDATA[
select seq_deliver_appoint_id.nextval from dual
]]>
</select>
<select id="deliver.getSeqVisitBusinessID" resultClass="java.lang.String">
<![CDATA[
select SEQ_VISIT_BUSINESS_ID.Nextval from dual
]]>
</select>
<select id="deliver.getSeqCarID" resultClass="java.lang.String">
<![CDATA[
select SEQ_CAR_ID.Nextval from dual
]]>
</select>
<select id="deliver.getContactPointID" resultClass="java.lang.String">
<![CDATA[
select seq_cust_phone_id.nextVal from dual
]]>
</select>
<statement id="deliver.newVisitBusinessInfoInsert" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
begin
insert into T_VISIT_BUSINESS
(visit_business_id,
party_name,
contact_mode,
deliver_address,
visit_time,
valid_flag,
area_code,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
contact_point_id,
deliver_appoint_id,
party_id,
car_id)
values
(to_number(#visitBusinessID:VARCHAR#),
#partyName:VARCHAR#,
#contactMode:VARCHAR#,
#deliverAddress:VARCHAR#,
sysdate,
'Y',
#areaCode#,
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
to_number(#contactPointID:VARCHAR#),
to_number(#deliverAppointID:VARCHAR#),
to_number(#partyID:VARCHAR#),
to_number(#carID:VARCHAR#));
insert into T_CAR_INFO
(car_id, party_id, UPDATED_DATE, UPDATED_BY, CREATED_DATE, CREATED_BY)
values
(to_number(#carID:VARCHAR#),
to_number(#partyID:VARCHAR#),
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#);
insert into t_customer
(party_id,
party_name,
AREA_CODE,
CONTACT_PHONE,
DISPATCHED,
CURRENT_USER_ID,
valid_flag,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
list_source,
old_list_source,
last_updated_time)
values
(to_number(#partyID:VARCHAR#),
#partyName:VARCHAR#,
#areaCode:VARCHAR#,
#contactMode:VARCHAR#,
'06',
(select user_id
from t_user
where group_by = '09'
and valid_flag = 'Y'
and is_agent = '0'
and rownum = 1),
'Y',
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
'SM',
'SM',
sysdate);
insert into t_deliver_appointment
(deliver_appoint_id,
area_code,
deliver_address,
contact_mode,
party_id,
party_name,
contact_people,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
ISSELFGET,
deliver_time)
values
(to_number(#deliverAppointID:VARCHAR#),
#areaCode:VARCHAR#,
#deliverAddress:VARCHAR#,
#contactMode:VARCHAR#,
to_number(#partyID:VARCHAR#),
#partyName:VARCHAR#,
#partyName:VARCHAR#,
sysdate,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
'Y',
trunc(sysdate));
insert into T_CUSTOMER_PHONE
(CONTACT_POINT_ID,
PARTY_ID,
PHONE_NUMBER,
VALID_FLAG,
UPDATED_BY,
UPDATED_DATE,
CREATED_BY,
CREATED_DATE,
OUTBOUND_SHIELD_TYPE,
MESSAGE_SHIELD_TYPE
)
values
(to_number(#contactPointID#),
to_number(#partyID#),
#contactMode:VARCHAR#,
'Y',
trim(#updatedBy:varchar#),
sysdate,
trim(#createdBy:varchar#),
sysdate
'N',
'N'
);
end;
]]>
</statement>
<statement id="deliver.saveVisitBusinessEnd" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
update t_visit_business set
updated_by = #updatedBy#,
updated_date = sysdate,
end_time = sysdate,
visit_business_result = #visitBusinessResult#
where visit_business_id = to_number(#visitBusinessID:VARCHAR#)
]]>
</statement>
<statement id="Deliver.insertDeliverCompany" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
insert into t_deliver_company(
company_id,
company_name,
start_time,
end_time,
ispriority,
ischeckvehicle,
is_charge_agency_services,
province_code,
city_code,
town_code,
valid_flag,
updated_by,
updated_date,
created_by,
created_date,
area_code,
relation_Policy,
deliver_mode,
deliver_type)
values
(trim(#companyId#),
#companyName:VARCHAR#,
to_date(#startTime:VARCHAR#,'yyyy-mm-dd'),
to_date(#endTime:VARCHAR#,'yyyy-mm-dd'),
#isPriority:VARCHAR#,
#isCheckVehicle:VARCHAR#,
#isChargeAgencyServices:VARCHAR#,
trim(#provinceCode:varchar#),
trim(#cityCode:varchar#),
trim(#townCode:varchar#),
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate,
trim(#areaCode:varchar#),
trim(#relationPolicy:varchar#),
#deliverMode#,
#deliverType#
)
]]>
</statement>
<select id="Deliver.getDeliverCompanyID" resultClass="java.lang.String">
<![CDATA[
select SEQ_COMPANY_ID.nextVal from dual
]]>
</select>
<statement id="Deliver.deliverRuleInsert" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
insert into t_deliver_rule (
rule_id,
deliver_times,
over_days,
email_address,
per_days,
email_send_times,
deliver_again_to,
area_code,
valid_flag,
updated_by,
updated_date,
created_by,
created_date)
values
(seq_deliver_rule_id.NEXTVAL,
#deliverTimes:VARCHAR#,
#overDays:VARCHAR#,
#emailAddress:VARCHAR#,
#perDays:VARCHAR#,
#emailSendTimes:VARCHAR#,
#deliverAgainTo:VARCHAR#,
#areaCode:VARCHAR#,
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate
)
]]>
</statement>
<select id="Deliver.searchDeliverRuleList" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select
t.rule_id ruleId,
t.deliver_times deliverTimes,
t.over_days overDays,
t.email_address emailAddress,
t.per_days perDays,
t.email_send_times emailSendTimes,
t.deliver_again_to deliverAgainTo,
t.area_code areaCode,
cd1.param_value areaCodeValue,
t.valid_flag validFlag ,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate
from t_deliver_rule t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID = AREA_CODE
where t.valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="ruleId">
<![CDATA[
t.rule_id =#ruleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimes">
<![CDATA[
t.deliver_times =#deliverTimes#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAgainTo">
<![CDATA[
t.deliver_again_to = #deliverAgainTo#
]]>
</isNotEmpty>
<![CDATA[
order by t.rule_id desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="Deliver.searchDeliverRuleForNum" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from t_deliver_rule
where valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="ruleId">
<![CDATA[
rule_id =#ruleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimes">
<![CDATA[
deliver_times =#deliverTimes#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverAgainTo">
<![CDATA[
deliver_again_to = #deliverAgainTo#
]]>
</isNotEmpty>
</select>
<select id="Deliver.searchDeliverRuleInfo" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select
rule_id ruleId,
deliver_times deliverTimes,
over_days overDays,
email_address emailAddress,
per_days perDays,
email_send_times emailSendTimes,
deliver_again_to deliverAgainTo,
area_code areaCode,
valid_flag validFlag ,
updated_by updatedBy,
updated_date updatedDate,
created_by createdBy,
created_date createDate
from t_deliver_rule
where valid_flag='Y'
and rule_id=#ruleId#
]]>
</select>
<statement id="Deliver.deliverRuleUpdate" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
update t_deliver_rule set
deliver_times = #deliverTimes:VARCHAR#,
over_days = #overDays:VARCHAR#,
email_address = #emailAddress:VARCHAR#,
per_days = #perDays:VARCHAR#,
email_send_times = #emailSendTimes:VARCHAR#,
deliver_again_to = #deliverAgainTo:VARCHAR#,
area_code = #areaCode:VARCHAR#,
valid_flag = #validFlag:VARCHAR#,
updated_by = #updatedBy:VARCHAR#,
updated_date = sysdate
where rule_id=#ruleId#
]]>
</statement>
<select id="Deliver.searchDeliverCompanyList" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select
company_id companyId,
company_name companyName,
to_char(start_time,'yyyy-mm-dd') startTime ,
to_char(end_time,'yyyy-mm-dd') endTime,
valid_flag validFlag ,
updated_by updatedBy,
updated_date updatedDate,
created_by createdBy,
created_date createDate,
ispriority isPriority ,
area_code areaCode,
relation_Policy relationPolicy
from t_deliver_company t
where 1=1
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
area_code =#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="companyName">
<![CDATA[
company_name like '%'||#companyName#||'%'
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="startTime">
<![CDATA[
start_time >= to_date(#startTime#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="endTime">
<![CDATA[
end_time <= to_date(#endTime#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isPriority">
<![CDATA[
ispriority = #isPriority#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="validFlag">
<![CDATA[
valid_flag = #validFlag#
]]>
</isNotEmpty>
<![CDATA[
order by company_id desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="Deliver.searchDeliverCompanyForNum" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from t_deliver_company
where 1=1
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
area_code =#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="companyName">
<![CDATA[
company_name like '%'||#companyName#||'%'
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="startTime">
<![CDATA[
start_time >= to_date(#startTime#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="endTime">
<![CDATA[
end_time <= to_date(#endTime#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isPriority">
<![CDATA[
ispriority = #isPriority#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="validFlag">
<![CDATA[
valid_flag = #validFlag#
]]>
</isNotEmpty>
</select>
<select id="Deliver.searchDeliverCompanyInfo" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
select
company_id companyId,
company_name companyName,
to_char(start_time,'yyyy-mm-dd') startTime ,
to_char(end_time,'yyyy-mm-dd') endTime,
ischeckvehicle isCheckVehicle,
is_charge_agency_services isChargeAgencyServices,
province_code provinceCode,
city_code cityCode,
town_code townCode,
valid_flag validFlag ,
updated_by updatedBy,
updated_date updatedDate,
created_by createdBy,
created_date createDate,
ispriority isPriority ,
area_code areaCode,
relation_Policy relationPolicy,
deliver_mode deliverMode,
deliver_type deliverType
from t_deliver_company
where company_id=#companyId#
]]>
</select>
<statement id="Deliver.searchDeliverCompanyDelete" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update t_deliver_company set
updated_by = #updatedBy#,
updated_date = sysdate,
valid_flag =#validFlag#
where company_id = trim(#companyId#)
]]>
</statement>
<statement id="Deliver.searchDeliverExpenseRuleDtailDelete" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update t_Deliver_Expense_Rule_Detail set
updated_by = #updatedBy#,
updated_date = sysdate,
valid_flag = #validFlag#
where company_id=trim(#companyId#)
]]>
</statement>
<select id="Deliver.getDeliverExpenseRuleId" resultClass="java.lang.String">
<![CDATA[
select SEQ_DELIVER_EXPENSE_RULE_ID.NEXTVAL from dual
]]>
</select>
<select id="Deliver.getDeliverExpenseDetailRuleId" resultClass="java.lang.String">
<![CDATA[
select EXPENSE_RULE_DETAIL_ID.NEXTVAL from dual
]]>
</select>
<statement id="Deliver.insertDeliverExpenseRuleDetail" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
insert into T_DELIVER_EXPENSE_RULE_Detail (
detail_id,
company_id,
deliver_result,
deliver_expense,
province_code,
city_code,
town_code,
expense_percent,
valid_flag,
updated_by,
updated_date,
created_by,
created_date,
urgency_ExpenseRule,
checkVehicle_ExpenseRule,
agencyServices_ExpenseRule,
deliver_OneTime_Expense,
deliver_TwoTimes_Expense,
field_Work_Expense
)
values
(#detailId#,
#companyId#,
#isSucceedRule:VARCHAR#,
#deliverExpenseRule:VARCHAR#,
trim(#provinceCode:varchar#),
trim(#cityCodeRule:varchar#),
trim(#townCodeRule:varchar#),
trim(#expensePercent:varchar#),
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate,
to_number(#urgencyExpenseRule:varchar#),
to_number(#checkVehicleExpenseRule:varchar#),
to_number(#agencyServicesExpenseRule:varchar#),
to_number(#deliverOneTimeExpense:varchar#),
to_number(#deliverTwoTimesExpense:varchar#),
to_number(#fieldWorkExpense:varchar#)
)
]]>
</statement>
<statement id="deliver.insertDeliverPresentExpense" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
insert into T_DELIVER_PRESENT_EXPENSE (
PRESENT_EXPENSE_ID,
detail_id,
PRESENT_ID,
PRESENT_EXPENSE,
valid_flag,
updated_by,
updated_date,
created_by,
created_date)
values
(SEQ_PRESENT_EXPENSE_ID.Nextval,
#detailId#,
#presentID:VARCHAR#,
#presentExpense#,
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate
)
]]>
</statement>
<statement id="Deliver.updateDeliverCompany" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update t_deliver_company set
company_name = #companyName:VARCHAR#,
start_time = to_date(#startTime:VARCHAR#,'yyyy-mm-dd'),
end_time = to_date(#endTime:VARCHAR#,'yyyy-mm-dd'),
ischeckvehicle = #isCheckVehicle:VARCHAR#,
is_charge_agency_services = #isChargeAgencyServices:VARCHAR#,
city_code = #cityCode:VARCHAR#,
town_code = #townCode:VARCHAR#,
updated_by = #updatedBy:VARCHAR#,
updated_date = sysdate,
ispriority = trim(#isPriority:varchar#),
relation_Policy = trim(#relationPolicy:varchar#),
deliver_mode=#deliverMode#,
deliver_type=#deliverType#
where company_id = trim(#companyId#)
]]>
</statement>
<statement id="Deliver.updateDeliverExpenseRuleDetail" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update T_DELIVER_EXPENSE_RULE_Detail set
city_code = #cityCodeRule:VARCHAR#,
town_code = #townCodeRule:VARCHAR#,
deliver_result = #isSucceedRule:VARCHAR#,
expense_percent = #expensePercent#,
deliver_expense = #deliverExpenseRule:VARCHAR#,
urgency_ExpenseRule = to_number(#urgencyExpenseRule:varchar#),
checkVehicle_ExpenseRule = to_number(#checkVehicleExpenseRule:varchar#),
agencyServices_ExpenseRule = to_number(#agencyServicesExpenseRule:varchar#),
deliver_OneTime_Expense = to_number(#deliverOneTimeExpense:varchar#),
deliver_TwoTimes_Expense = to_number(#deliverTwoTimesExpense:varchar#),
field_Work_Expense = to_number(#fieldWorkExpense:varchar#),
valid_flag=#validFlag:VARCHAR#,
updated_by=#updatedBy:VARCHAR#,
updated_date= sysdate
where detail_id = trim(#detailId#)
]]>
</statement>
<select id="Deliver.searchDeliverExpenseRuleDetailList" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
select derd.detail_id detailId,
derd.company_id companyId,
derd.deliver_result isSucceedRule,
derd.deliver_expense deliverExpenseRule,
derd.city_code cityCodeRule,
derd.town_code townCodeRule,
derd.valid_flag validFlag,
derd.updated_by updatedBy,
derd.updated_date updatedDate,
get_city_name(derd.detail_id) cityName,
get_town_name(derd.detail_id) townName,
TO_CHAR(derd.expense_percent, 'FM99999999990.0099') expensePercent,
TO_CHAR(derd.urgency_ExpenseRule, 'FM99999999990.0099') urgencyExpenseRule,
TO_CHAR(derd.checkVehicle_ExpenseRule, 'FM99999999990.0099') checkVehicleExpenseRule,
TO_CHAR(derd.agencyServices_ExpenseRule, 'FM99999999990.0099') agencyServicesExpenseRule,
p.present_name presentName,
t.present_expense presentExpense,
i.PARAM_VALUE isSucceedRuleValue,
TO_CHAR(nvl(deliver_OneTime_Expense, 0), 'FM99999999990.0099') deliverOneTimeExpense,
TO_CHAR(nvl(deliver_TwoTimes_Expense, 0), 'FM99999999990.0099') deliverTwoTimesExpense,
TO_CHAR(nvl(field_Work_Expense, 0), 'FM99999999990.0099') fieldWorkExpense
from T_DELIVER_EXPENSE_RULE_Detail derd
left outer join t_deliver_present_expense t on t.valid_flag = 'Y'
and t.detail_id =
derd.detail_id
left outer join t_present p on p.valid_flag = 'Y'
and p.present_id = t.present_id
left outer join t_code_table i on derd.deliver_result = i.param_id
and i.code_type = 'deliverResult'
where derd.company_id=trim(#companyId#)
]]>
</select>
<statement id="Deliver.deliverExpenseRuleDetailDelete" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update t_Deliver_Expense_Rule_Detail set
updated_by = #updatedBy#,
updated_date = sysdate,
valid_flag = 'N'
where valid_flag='Y'
and detail_id = to_number(#detailId:VARCHAR#)
]]>
</statement>
<select id="Deliver.searchDeliverCompany" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
select
company_id companyId,
company_name companyName,
to_char(start_time,'yyyy-mm-dd') startTime ,
to_char(end_time,'yyyy-mm-dd') endTime,
valid_flag validFlag ,
updated_by updatedBy,
updated_date updatedDate,
created_by createdBy,
created_date createDate,
ispriority isPriority ,
area_code areaCode,
relation_Policy relationPolicy
from t_deliver_company
where valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
area_code =#areaCode#
]]>
</isNotEmpty>
</select>
<select id="Deliver.searchDeliverArea" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
select c.value_code deliverAreaCode,c.value_name deliverAreaName
from t_code_city c, t_code_table t
where c.superior_value_code = t.province_code
and t.code_type = 'area'
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.param_id =#areaCode#
]]>
</isNotEmpty>
</select>
<select id="Deliver.searchDeliverDetailList" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select
a.carId,
a.deliverID,
a.processName,
a.deliverTime,
a.contactPeople,
max(a.shyPolicyPactNumber) shyPolicyPactNumber,
max(a.jpPolicyPactNumber) jpPolicyPactNumber,
max(a.shyPolicyNumber) shyPolicyNumber,
max(a.jpPolicyNumber) jpPolicyNumber,
a.insured,
a.provinceName || a.cityName || a.townName || a.deliverAddress deliverAddress,
a.payMode,
a.deliverType,
sum(a.businessInsurancePremium) businessInsurancePremium,
sum(a.compulsoryInsurancePremium) compulsoryInsurancePremium,
sum(a.vechileTaxAmount) vechileTaxAmount,
a.isSelfGet,
a.isCheckVehicle,
a.remark,
a.gotTime,
a.deliverCompany,
a.presentName,
a.insuranceData,
a.deliverState,
max(a.paymentBatchNo) paymentBatchNo
from (select t.deliver_id deliverID,
(select u.user_name
from t_user u
where u.user_id = t.by_user_id
and rownum = 1) processName,
to_char(t.deliver_time, 'yyyy-mm-dd') deliverTime,
t.contact_people contactPeople,
decode(p.insurance_type,
'104',
decode(p.deliver_type, '03', p.policy_number, '01', ''),
'108',
decode(p.deliver_type, '03', p.policy_number, '01', ''),
'105',
+ '') as shyPolicyPactNumber,
decode(p.insurance_type,
'105',
decode(p.deliver_type, '03', p.policy_number, '01', ''),
'104',
'',
'108',
'') as jpPolicyPactNumber,
decode(p.insurance_type,
'104',
decode(p.deliver_type, '03', '', '01', p.policy_number),
'108',
decode(p.deliver_type, '03', '', '01', p.policy_number),
'105',
'') as shyPolicyNumber,
decode(p.insurance_type,
'105',
decode(p.deliver_type, '03', '', '01', p.policy_number),
'104',
'',
'108',
'') as jpPolicyNumber,
t.insured,
t.deliver_address deliverAddress,
(select ct.param_value
from t_code_table ct
where ct.param_id = t.pay_mode
and ct.code_type = 'payMode') payMode,
(select dt.param_value
from t_code_table dt
where dt.param_id = t.deliver_type
and dt.code_type = 'deliverType') deliverType,
decode(p.insurance_type,
'104',
p.insurance_premium,
'108',
p.insurance_premium,
'105',
'') as businessInsurancePremium,
decode(p.insurance_type,
'105',
decode(p.deliver_type,
'03',
p.insurance_premium - p.vehicle_tax_amount,
'01',
p.insurance_premium),
'104',
'',
'108',
'') as compulsoryInsurancePremium,
p.vehicle_tax_amount vechileTaxAmount,
t.isSelfGet isSelfGet,
t.isCheckVehicle isCheckVehicle,
t.remark,
to_char(t.got_time, 'YYYY-MM-DD hh24:mi:ss') gotTime,
(select dc.company_name
from t_deliver_company dc
where dc.company_id = t.deliver_company) deliverCompany,
getdeliverpresentinfo(t.deliver_id) as presentName,
get_insuranceData(t.deliver_id) insuranceData,
(select sp.param_value
from t_sys_param sp
where sp.param_id = t.deliver_state_code
and sp.param_type = 'deliverState') deliverState,
(select max(dp.payment_batch_no)
from t_deliver_policy_payment dp
where dp.policy_pact_id = p.policy_number) paymentBatchNo,
p.car_id carId,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.PROVINCE_CODE
and cc.VALID_FLAG = 'Y'
and rownum = 1) provinceName,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.CITY_CODE
and cc.VALID_FLAG = 'Y'
and rownum = 1) cityName,
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = t.TOWN_CODE
and cc.VALID_FLAG = 'Y'
and rownum = 1) townName
from t_deliver_infomation t, t_deliver_policy p
where t.deliver_id = p.deliver_id
and t.deliver_type <> '02'
and t.area_code = #areaCode#
]]>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.deliver_time >= to_date(#deliverTimeFrom#, 'yyyy-mm-dd')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.deliver_time < to_date(#deliverTimeTo#, 'yyyy-mm-dd')+1
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
<![CDATA[
t.deliver_company=#deliverCompany:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="isSelfGet">
<![CDATA[
t.isSelfGet=#isSelfGet:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type=#deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="paymentIds">
t.pay_mode in
<iterate close=")" open="(" conjunction="," property="paymentIds">#paymentIds[]#</iterate>
</isNotEmpty>
<isNotEmpty prepend="AND" property="paymentBatchNo">
<![CDATA[
(select max(dp.payment_batch_no)
from t_deliver_policy_payment dp
where dp.policy_pact_id = p.policy_number ) = #paymentBatchNo:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
t.deliver_Mode = #deliverMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="gotTimeFrom">
<![CDATA[
t.got_time >= to_date(#gotTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="gotTimeTo">
<![CDATA[
t.got_time < to_date(#gotTimeTo#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResultTimeFrom">
<![CDATA[
t.deliver_result is not null and
trunc(t.UPDATED_DATE) >= to_date(#deliverResultTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResultTimeTo">
<![CDATA[
t.deliver_result is not null and
trunc(t.UPDATED_DATE) < to_date(#deliverResultTimeTo#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<isEqual prepend="and" property="isOverTime" compareValue="1">
<![CDATA[
t.deliver_time < trunc(t.got_time)
]]>
</isEqual>
<isEqual prepend="and" property="isOverTime" compareValue="0">
<![CDATA[
t.deliver_time >= trunc(t.got_time)
]]>
</isEqual>
<isNotEmpty prepend="AND" property="overDays">
<![CDATA[
t.deliver_result is null and
trunc(t.UPDATED_DATE)< trunc(sysdate)-#overDays#
]]>
</isNotEmpty>
<![CDATA[
) a
group by a.deliverID,
a.carId,
a.processName,
a.deliverTime,
a.contactPeople,
a.insured,
a.deliverAddress,
a.payMode,
a.deliverType,
a.isSelfGet,
a.isCheckVehicle,
a.remark,
a.gotTime,
a.deliverCompany,
a.presentName,
a.insuranceData,
a.deliverState,
a.provinceName,
a.cityName,
a.townName
order by a.deliverID
]]>
</select>
<update id="update.deliverSheetCompany" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update T_DELIVER_INFOMATION i set i.deliver_company = #changeDeliverCompany#,i.updated_by=#updatedBy#,i.updated_date =sysdate where i.deliver_id = to_number(#deliverID#)
</update>
<statement id="Deliver.searchOutInsuranceInfoDeliverId" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select tdi.deliver_id deliverID,
nvl(oii.totalagreepremium,0) insurancePremium,
nvl(oii.vehicle_tax_amount,0) carBoatPremium,
oii.policy_pact_id policyNumber
from t_deliver_infomation tdi,
t_deliver_policy tdp,
t_out_insurance_info oii
where tdp.policy_number=oii.policy_pact_id
and tdi.deliver_id=tdp.deliver_id
]]>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
tdi.deliver_id=#deliverID:varchar#
]]>
</isNotEmpty>
</statement>
<select id="Deliver.searchDeliverExpenseRuleInfo" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select detail_id detailId,
company_id companyId,
deliver_result isSucceedRule,
deliver_expense deliverExpenseRule,
province_code provinceCode,
city_code cityCodeRule,
town_code townCodeRule,
valid_flag validFlag,
updated_by updatedBy,
updated_date updatedDate,
TO_CHAR(nvl(expense_percent,0), 'FM99999999990.0099') expensePercent,
TO_CHAR(nvl(urgency_ExpenseRule,0), 'FM99999999990.0099') urgencyExpenseRule,
TO_CHAR(nvl(checkVehicle_ExpenseRule,0), 'FM99999999990.0099') checkVehicleExpenseRule,
TO_CHAR(nvl(agencyServices_ExpenseRule,0), 'FM99999999990.0099') agencyServicesExpenseRule,
TO_CHAR(nvl(deliver_OneTime_Expense,0), 'FM99999999990.0099') deliverOneTimeExpense,
TO_CHAR(nvl(deliver_TwoTimes_Expense,0), 'FM99999999990.0099') deliverTwoTimesExpense,
TO_CHAR(nvl(field_Work_Expense,0), 'FM99999999990.0099') fieldWorkExpense
from t_deliver_expense_rule_detail
where detail_id=#detailId#
]]>
</select>
<!-- 新增投保单礼品配送信息 -->
<insert id="deliverPresentDetail.insert" parameterClass="com.tpaic.callCenter.dto.DeliverPresentDetailDTO">
<![CDATA[
insert into t_deliver_present_detail
(detail_id,deliver_id,present_id,present_quantity,policy_number,updated_by,updated_date,created_by,created_date,deliver_type)
( select SEQ_DELIVER_DETAIL_ID.Nextval,
#deliverID#,
b.present_id,
b.present_numbe,
a.policy_pact_id,
a.created_by,
sysdate,
a.created_by,
sysdate,
#deliverType#
from T_PRESENT_POLICY a, t_present_policy_item b
where a.present_batch_no = b.present_batch_no
and a.policy_pact_id =#policyNumber#
and b.valid_flag='Y'
and a.valid_flag='Y'
)
]]>
</insert>
<insert id="deliverPresentDetail.update" parameterClass="com.tpaic.callCenter.dto.DeliverPresentDetailDTO">
<![CDATA[
begin
update T_PRESENT_POLICY set valid_flag='N' where policy_pact_id=#policyNumber# and valid_flag='Y';
update T_PRESENT_POLICY_ITEM set valid_flag='N' where present_batch_no in
(select present_batch_no from T_PRESENT_POLICY where policy_pact_id=#policyNumber#)
and valid_flag='Y';
end;
]]>
</insert>
<statement id="deliver.deleteDeliverPresentExpense" parameterClass="com.tpaic.callCenter.dto.DeliverCompanyInfoDTO">
<![CDATA[
update T_DELIVER_PRESENT_EXPENSE set
updated_by = #updatedBy#,
updated_date = sysdate,
valid_flag = 'N'
where valid_flag='Y'
and detail_id = to_number(#detailId:VARCHAR#)
]]>
</statement>
<select id="Deliver.searchDeliverPresentList" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select
t.PRESENT_EXPENSE_ID presentExpenseId,
t.detail_id detailId,
t.PRESENT_ID presentID,
p.present_name presentName,
TO_CHAR( t.PRESENT_EXPENSE,'FM99999999990.0099') presentExpense
from t_deliver_present_expense t ,
t_present p
where t.valid_flag='Y'
and t.present_id = p.present_id
and t.detail_id=#detailId#
]]>
</select>
<parameterMap id="deliverExpenseParams" class="map">
<parameter property="deliverId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="dlrrs" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="deliverExpense" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
<parameter property="isMatchSuccess" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
</parameterMap>
<procedure id="deliver.updateDeliverExpense" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
select
pkg_calculate_deliver_expense.calculate_expense(#deliverID#,#deliverCompanyResponse#,#actualPayMode#) from dual </procedure>
<statement id="deliver.updateDeliverInfo" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">update t_deliver_infomation set ISURGENCY ='Y' where DELIVER_ID = #deliverID#</statement>
<statement id="deliver.updateDeliverInfo_N" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
update t_deliver_infomation set ISURGENCY = 'N'
where DELIVER_ID = #deliverID#
and ISURGENCY <>'Y'
]]>
</statement>
<select id="deliverBook.searchCustomerList_03" parameterClass="com.tpaic.callCenter.dto.CustomerDTO" resultClass="com.tpaic.callCenter.dto.CustomerDTO">
<![CDATA[
select t.*,
(select param_value
from T_CODE_TABLE
where CODE_TYPE = 'area'
and PARAM_ID = t.areaCode) areaName
from (select rownum rn, a.*
from (select distinct c.party_id as partyId,
c.party_name as partyName,
c.area_code as areaCode,
c.contact_address as contactAddress
from t_customer c,
(select oi.party_id, oi.car_id
from t_out_insurance_info oi
where oi.insurance_state = '9'
and (oi.deliver_state = '00' or
oi.deliver_state = '03')) b
where c.party_id = b.party_id
and exists (select 1
from t_customer_task ct
where ct.party_id = c.party_id
and ct.car_id = b.car_id
and ct.status = '1'
and ct.current_user_id = to_number(#currentUserId:VARCHAR#))
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
c.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.AREA_CODE = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
)a
) t
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 已废弃 配送预约：查询客户列表长度 -->
<select id="deliverBook.searchCustomerAmount_03" parameterClass="com.tpaic.callCenter.dto.CustomerDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from (select distinct c.party_id as partyId,
c.party_name as partyName,
c.area_code as areaCode,
c.contact_address as contactAddress
from t_customer c,
(select oi.party_id, oi.car_id
from t_out_insurance_info oi
where oi.insurance_state = '9'
and (oi.deliver_state = '00' or oi.deliver_state = '03')) b
where c.party_id = b.party_id
and exists (select 1
from t_customer_task ct
where ct.party_id = c.party_id
and ct.car_id = b.car_id
and ct.status = '1'
and ct.current_user_id = to_number(#currentUserId:VARCHAR#))
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
c.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.AREA_CODE = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
)
]]>
</select>
<select id="deliverBook.searchCustomerPolicy_01" parameterClass="com.tpaic.callCenter.dto.PolicyInfoDTO" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select * from
(
select
p.policy_id as policyID,
p.insurance_premium as insurancePremium,
p.policy_state policyState,
s.param_value policyStateDesc,
c.party_name as partyName,
c.party_id as partyID,
cd.param_value as areaName,
cd.param_id as areaCode,
c.contact_phone as contactPhone,
c.contact_address as contactAddress,
ct.param_value as correctCauseName,
p.correct_cause as correctCause,
to_char(p.correct_time,'yyyy-mm-dd HH24:MI:SS') as correctTime,
u.user_name as applicants,
'01' as deliverType
from t_customer c
left outer join T_CODE_TABLE cd on cd.PARAM_ID = c.area_code
left outer join t_policy_information p on p.party_id = c.party_id
left outer join t_sys_param s on s.param_id=p.policy_state
left outer join t_code_table ct on p.correct_cause=ct.param_id and ct.code_type='correctCause'
left outer join t_user u on u.um_code=p.applicants
where 1=1 and cd.CODE_TYPE='area' and s.param_type='policyState'
]]>
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
c.party_id = to_number(#partyID:VARCHAR#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyState">
<![CDATA[
p.policy_state = #policyState:VARCHAR#
]]>
</isNotEmpty>
<![CDATA[
) where policyID is not null
]]>
</select>
<select id="deliverBook.searchCustomerPolicy_03" parameterClass="com.tpaic.callCenter.dto.PolicyInfoDTO" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select * from
(
select
p.policy_pact_id as policyID,
to_char(p.TOTALAGREEPREMIUM) as insurancePremium,
'' policyState,
'' policyStateDesc,
c.party_name as partyName,
c.party_id as partyID,
cd.param_value as areaName,
cd.param_id as areaCode,
c.contact_phone as contactPhone,
c.contact_address as contactAddress,
'' as correctCauseName,
'' as correctCause,
'' as correctTime,
'' as applicants,
'03' as deliverType
from t_customer c
left outer join T_CODE_TABLE cd on cd.PARAM_ID = c.area_code
left outer join t_out_insurance_info p on p.party_id = c.party_id and p.insurance_state='9' and p.paymentmode<>'7'
and p.deliver_state='00' and isordeliver='N'
where 1=1 and cd.CODE_TYPE='area'
]]>
<isNotEmpty prepend="AND" property="partyID">
<![CDATA[
c.party_id = to_number(#partyID:VARCHAR#)
]]>
</isNotEmpty>
<![CDATA[
) where policyID is not null
]]>
</select>
<update id="DeliverInfo.updateDeliverExpense" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update t_deliver_infomation set deliver_expense=#deliverExpenseNew#, CHANGE_TIME =sysdate, updated_by=#updatedBy# where deliver_id=#deliverID#
</update>
<statement id="Deliver.searchDeliverPrintNew" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select deliverID,processName,deliverMode,vehicleNumbers,deliverTime,contactPeople,documentLog,policyNumber,insuranceData,
insured,deliverAddress,payMode,deliverType,sum(compulsoryInsurancePremium) compulsoryInsurancePremium,sum(businessInsurancePremium) businessInsurancePremium,sum(vechileTaxAmount) vechileTaxAmount,
isSelfGet,isCheckVehicle,remark,gotTime,deliverCompany,presentName,senderCompany,senderAddress,senderContactMode,payModeName,contactMode,
areaName,areaCode,companyId,sum(allPremium) allPremium,sum(insurancePremium) insurancePremium,agentid,checkId,isPrized
from(
select a.deliver_id deliverID,
dt.param_value deliverType,
a.deliver_mode deliverMode,
get_insuranceData(a.deliver_id) insuranceData,
GET_POLICY_DocumentLog(a.deliver_id) as documentLog,
decode(a.deliver_type,
'01',
GetPolicyNumber(a.deliver_id),
'03',
GET_POLICY_PACT_NUMBER(a.deliver_id)) as policyNumber,
a.insured insured,a.contact_mode contactMode,to_char(a.deliver_time,'yyyy-mm-dd') deliverTime,
a.deliver_address deliverAddress,
nvl(decode(a.deliver_type,
'01',
c.INSURANCE_PREMIUM+c.vehicle_tax_amount,
'03',
oi.totalagreepremium),0) allPremium,
nvl(decode(a.deliver_type,
'01',
c.INSURANCE_PREMIUM,
'03',
oi.totalagreepremium-oi.vehicle_tax_amount),0) insurancePremium,
nvl(decode(a.deliver_type,
'01',
decode(c.policy_type,
'2',
'',
'1',
c.insurance_premium),
'03',
decode(subStr(oi.policy_pact_id, 5, 3),
'105',
'',
'104',
(oi.totalagreepremium - oi.vehicle_tax_amount),
'108',
(oi.totalagreepremium - oi.vehicle_tax_amount))),0) as businessInsurancePremium,
nvl(decode(a.deliver_type,
'01',
decode(c.policy_type,
'1',
'',
'2',
c.insurance_premium),
'03',
decode(subStr(oi.policy_pact_id, 5, 3),
'105',
(oi.totalagreepremium - oi.vehicle_tax_amount),
'104',
'',
'108',
'')),0) as compulsoryInsurancePremium,
nvl(decode(a.deliver_type,
'01',
c.vehicle_tax_amount,
'03',
oi.vehicle_tax_amount),0) as vechileTaxAmount,
getdeliverpresentinfo(a.deliver_id) as presentName,
f_call_qry_vehiclenum(a.deliver_id) vehicleNumbers,
ct.param_value as payMode,dc.company_name as deliverCompany,
uu.user_name as processName,
a.created_date createdTime,
a.contact_people contactPeople,
a.isSelfGet isSelfGet,
a.isCheckVehicle isCheckVehicle,
a.remark,
a.got_time gotTime,
bt.name as senderCompany,
bt.address as senderAddress,
bt.PHONE_CUSTOMER_SERVICE as senderContactMode,
cd1.param_value as areaName,
cd2.param_value as payModeName,
a.area_code as areaCode,
a.deliver_company companyId,
(select casc.agentid
from CC_AGENT_SOFTPHONE_CONF casc,
t_user u where casc.um_code=u.um_code
and u.user_id=a.by_user_id
and rownum=1 )agentid,
a.check_id checkId,
a.is_prized isPrized
from t_branch_team bt,
t_deliver_infomation a
left outer join t_deliver_policy b on a.deliver_id=b.deliver_id
left outer join t_code_table dt on dt.code_type='deliverType' and a.deliver_type=dt.param_id
left outer join t_policy_information c on b.policy_number=c.policy_id
left outer join t_out_insurance_info oi on b.policy_number=oi.policy_pact_id
left outer join t_code_table ct on ct.code_type='payMode' and ct.param_id=a.pay_mode
left outer join t_deliver_company dc on dc.company_id=a.deliver_company
inner join t_user uu on uu.user_id=a.by_user_id
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area' and cd1.PARAM_ID = a.AREA_CODE
left outer join t_user cu on cu.user_id=a.by_user_id
left outer join t_code_table cd2 on cd2.code_type='payMode' and cd2.param_id=a.pay_mode
where bt.branch_id=(select branch_id from t_user where um_code=#createdBy# and valid_flag='Y' and rownum=1)
and a.DELIVER_ID =#deliverID#
)
group by deliverID,deliverMode,processName,deliverTime,contactPeople,vehicleNumbers,deliverType,documentLog,policyNumber,insuranceData,senderCompany,senderAddress,
insured,deliverAddress,payMode,deliverType,isSelfGet,IsCheckVehicle,remark,gotTime,deliverCompany,presentName,senderContactMode,payModeName,contactMode,areaName,
areaCode,companyId,agentid,checkId,isPrized
]]>
</statement>
<select id="sysdate.getOneMonthTime" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select to_char(sysdate,'yyyy-mm-dd') deliverTimeTo,to_char(add_months(sysdate,-1),'yyyy-mm-dd') deliverTimeFrom from dual
]]>
</select>
<select id="DeliverRule.searchDeliverAgainTo" parameterClass="java.lang.String" resultClass="java.lang.String">
<![CDATA[
select t.deliver_again_to deliverAgainTo
from t_deliver_rule t
where 1=1
and
t.area_code =#areaCode#
]]>
</select>
<!-- 2010-03-12 fanxl -->
<select id="deliver.searchPolicyListByDeliverId_01" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select tdp.deliver_id deliverID,
tdp.policy_number policyID,
(select nvl(tpi.insurance_premium, 0)
from t_policy_information tpi
where tpi.policy_id = tdp.policy_number) insurancePremium,
(select nvl(tpi.vehicle_tax_amount, 0)
from t_policy_information tpi
where tpi.policy_id = tdp.policy_number) carBoatPremium,
(select param_value
from t_sys_param
where param_type = 'policyState'
and param_id in
(select tpi.policy_state
from t_policy_information tpi
where tpi.policy_id = tdp.policy_number)) policyState
from t_deliver_policy tdp
where deliver_id=#value#
]]>
</select>
<!-- 2010-03-12 fanxl -->
<select id="deliver.searchPolicyListByDeliverId_03" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.PolicyInfoDTO">
<![CDATA[
select tdp.deliver_id deliverID,
tdp.policy_number policyID,
(select nvl(to_char(oi.totalagreepremium -
oi.vehicle_tax_amount),
0)
from t_out_insurance_info oi
where tdp.policy_number = oi.policy_pact_id) insurancePremium,
(select nvl(oi.vehicle_tax_amount, 0)
from t_out_insurance_info oi
where tdp.policy_number = oi.policy_pact_id) carBoatPremium,
(select param_value
from t_sys_param
where param_type = 'outPolicyState'
and param_id in
(select oi.insurance_state
from t_out_insurance_info oi
where tdp.policy_number = oi.policy_pact_id)) policyState
from t_deliver_policy tdp
where deliver_id=#value#
]]>
</select>
<update id="deliver.updateSelfGet" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
update t_deliver_infomation
set
ISSELFGET=#isSelfGet#,
DELIVER_COMPANY=#deliverCompany#,
UPDATED_BY = #updatedBy#
where 1=1
]]>
<isNotEmpty prepend="and" property="deliverIdStrs">
deliver_id in
<iterate close=")" open="(" conjunction="," property="deliverIdStrs">#deliverIdStrs[]#</iterate>
</isNotEmpty>
</update>
<!-- 20110104 tg 添加 isTelPay 电话支付条件 -->
<statement id="deliver.searchPresentByDeliverId" parameterClass="com.tpaic.callCenter.dto.PresentDTO" resultClass="com.tpaic.callCenter.dto.PresentDTO">
<![CDATA[
select p.present_id presentId,
p.present_name presentName,
p.present_price presentPrice,
(select y.present_type_name
from t_present_type y
where y.present_type_id = p.present_kind_code) presentTypeName,
nvl(s.stock_number,0) stockNumber
,v2.presentRuleName,v2.presentRuleId,p.present_attribute presentAttribute,v2.autoMatch,v2.condition,v2.allowNum
from (
select t.present_id,v.presentRuleName,v.presentRuleId,v.autoMatch,condition,t.allownum allowNum
from (
select execute_sql('select 1 from (select
nvl(sum(decode(subStr(i.policy_pact_id, 5, 3),
''105'',
'''',
''104'',
(i.totalagreepremium - i.vehicle_tax_amount),
''108'',
(i.totalagreepremium - i.vehicle_tax_amount))),0) as premiumSy,
nvl(sum(decode(subStr(i.policy_pact_id, 5, 3),
''105'',
(i.totalagreepremium - i.vehicle_tax_amount),
''104'',
'''',
''108'',
'''')),0) as premiumJq,
nvl(sum(i.TOTALAGREEPREMIUM-i.vehicle_tax_amount),0) totalagreepremium,
nvl(sum(decode(subStr(i.policy_pact_id, 5, 3),
''105'',
'''',
''104'',
i.claimrecordbizsize,
''108'',
i.claimrecordbizsize)),0) as claimrecordbizsize,
MIN((select c.renews_flag from t_customer c where c.party_id = i.party_id)) renewsFlag,
MIN((select decode(c.list_source,''IB'',''1'',''2'') from t_customer c where c.party_id = i.party_id ))source,
MIN((select oii.is_tel_pay from t_out_insurance_info oii where oii.policy_pact_id=''' || #lossRatioPolicePactID# ||''')) isTelPay,
''' ||#isselfget# ||''' isselfget,' ||
'getPolicyType(''' ||
#policePactIDsStr# ||
''',''jq'') jq,' || 'getPolicyType(''' ||
#policePactIDsStr# ||
''',''sy'') sy,'
|| 'min((select t.lossRatio from t_out_insurance_info t where t.policy_pact_id=''' ||
#lossRatioPolicePactID# || ''')) lossRatio'
||
' from t_out_insurance_info i where i.policy_pact_id in ('
||getPolicypactIdWhereIn(#policePactIDsStr#)
|| ')' || ' )where 1=1 ' ||
r.present_rule_expression) isRuleMatch,
r.present_rule_id presentRuleId,r.present_rule_name presentRuleName,r.auto_match autoMatch,r.condition condition
from t_present_rule r
where 1=1
and to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') between r.effect_date_begin and r.effect_date_end
and r.valid_flag='Y' and r.area_code=#areaCode#
) v,
t_present_rule_type t
where v.isRuleMatch = 1
and t.present_rule_id = v.presentRuleId ) v2,
t_present p,t_present_stock_sale s
where
v2.present_id = p.present_id
and p.present_state='1'
and p.area_code=#areaCode#
and s.present_id = p.present_id
and s.area_code = #areaCode#
and s.stock_number>0
order by v2.presentRuleId
]]>
</statement>
<select id="deliver.searchPolicyPactIdsByDeliverId" parameterClass="java.lang.String" resultClass="java.lang.String">
<![CDATA[
select GET_POLICY_PACT_NUMBER(#value#) from dual
]]>
</select>
<select id="deliver.searchPolicyPactIdsByDeliverId_01" parameterClass="java.lang.String" resultClass="java.lang.String">
<![CDATA[
select GetPolicyPactNumber_01(#value#) from dual
]]>
</select>
<insert id="deliverPresent.andNewPresent" parameterClass="com.tpaic.callCenter.dto.PresentDTO">
insert into t_deliver_present_detail
(detail_id,
deliver_id,
present_id,
present_quantity,
policy_number,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
DELIVER_TYPE,
PHONE,
OVER_SALES_AMOUNT,
USE_OVER_SALES_AMOUNT)
values
(seq_deliver_detail_id.nextval,
to_number(#deliverID#),
to_number(#presentId#),
to_number(#presentNumber#),
#policyPactId:varchar#,
sysdate,
#umCode:VARCHAR#,
sysdate,
#umCode:VARCHAR#,
'01',
#phone:varchar#,
#salesAmount:varchar#,
#useOverSalesAmount:varchar#)
</insert>
<statement id="deliverPresent.updateThePresentStock" parameterClass="com.tpaic.callCenter.dto.PresentDTO">
update t_present_stock_sale s set s.stock_number = s.stock_number - #presentNumber#, UPDATED_DATE = sysdate where s.present_id=#presentId#
</statement>
<select id="present.oldPresentListByDeliverId" parameterClass="com.tpaic.callCenter.dto.PresentDTO" resultClass="com.tpaic.callCenter.dto.PresentDTO">
<![CDATA[
select a.present_id as presentId,
a.present_quantity as presentNumber,
a.deliver_id as deliverID,
a.deliver_reason deliverReason,
a.USE_OVER_SALES_AMOUNT useOverSalesAmount
from t_deliver_present_detail a
where a.valid_flag = 'Y'
and a.deliver_id = to_number(#deliverID:VARCHAR#)
]]>
</select>
<update id="deliver.updateNotUrgency" parameterClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
update t_deliver_infomation
set
ISURGENCY=#isUrgency#,
UPDATED_BY = #updatedBy#
where 1=1
]]>
<isNotEmpty prepend="and" property="deliverIdStrs">
deliver_id in
<iterate close=")" open="(" conjunction="," property="deliverIdStrs">#deliverIdStrs[]#</iterate>
</isNotEmpty>
</update>
<statement id="deliver.updateDeliverInfoRelationPolicy" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO">
<![CDATA[
update t_deliver_infomation set relation_policy = #relationPolicy#
where deliver_id=#deliverID#
]]>
</statement>
<select id="deliver.searchModifyDeliverCompanyNameList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select *
from (select rownum rn, a.*
from (select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.PROVINCE_CODE and cc.VALID_FLAG='Y' and rownum=1) provinceName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.CITY_CODE and cc.VALID_FLAG='Y' and rownum=1) cityName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.TOWN_CODE and cc.VALID_FLAG='Y' and rownum=1) townName,
t.insured,
t.remark,
dc.company_name deliverCompanyName,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
(select ct.param_value
from t_code_table ct
where ct.valid_flag = 'Y'
and ct.code_type = 'deliverMode'
and ct.param_id = t.deliver_mode) deliverModeName
from T_DELIVER_INFOMATION t
left outer join t_deliver_company dc on dc.company_id =
t.deliver_company
where t.area_code = #areaCode#
and t.deliver_state_code= #deliverStateCode#
and t.isSelfGet= #isSelfGet#
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdStart">
<![CDATA[
t.DELIVER_ID >=#deliverIdStart#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdEnd">
<![CDATA[
t.DELIVER_ID <=#deliverIdEnd#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
<![CDATA[
t.pay_mode = #payMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
<![CDATA[
t.DELIVER_COMPANY = #deliverCompany#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<![CDATA[
order by t.deliver_id desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.searchModifyDeliverCompanyNameNum" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from T_DELIVER_INFOMATION t
where t.area_code = #areaCode#
and t.deliver_state_code= #deliverStateCode#
and t.isSelfGet= #isSelfGet#
]]>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
t.contact_people =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdStart">
<![CDATA[
t.DELIVER_ID >=#deliverIdStart#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIdEnd">
<![CDATA[
t.DELIVER_ID <=#deliverIdEnd#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
<![CDATA[
t.pay_mode = #payMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured =#insured#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
<![CDATA[
t.DELIVER_COMPANY = #deliverCompany#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
</select>
<insert id="deliverAgain.insertDeliverPolicy" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
insert into T_DELIVER_POLICY
(DELIVER_ID,
POLICY_NUMBER,
UPDATED_DATE,
UPDATED_BY,
CREATED_DATE,
CREATED_BY,
isCheckVehicle,
insurance_PREMIUM)
(select #deliverID#,
dp.policy_number,
sysdate,
#updatedBy#,
sysdate,
#createdBy#,
dp.ischeckvehicle,
dp.insurance_premium
from t_deliver_policy dp
where dp.deliver_id = #oldDeliverId#)
</insert>
<!-- 配送过程记录-总页数 -->
<select id="deliver.trace.notes.num" parameterClass="com.tpaic.callCenter.dto.DeliverTraceDTO" resultClass="com.tpaic.callCenter.dto.DeliverTraceDTO">
select count(de.deliver_id) from
t_deliver_infomation de
where de.party_id=#partyID#
<isNotEmpty prepend="AND" property="deliverTimeB">
<![CDATA[
de.DELIVER_TIME >= to_date(#deliverTimeB#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeE">
<![CDATA[
to_char(de.DELIVER_TIME,'YYYY-MM-DD') <= #deliverTimeE#
]]>
</isNotEmpty>
</select>
<select id="deliver.trace.notes.list" parameterClass="com.tpaic.callCenter.dto.DeliverTraceDTO" resultClass="com.tpaic.callCenter.dto.DeliverTraceDTO">
select * from (
select rownum rn,d.* from (
select de.deliver_id deliverID,
de.insured partyName,
de.deliver_type deliverType,
de.updated_date deliverTime,
(select t.param_value
from T_CODE_TABLE t
where t.CODE_TYPE = 'untreadRemark' and de.untread_cause=t.param_id) untreadCauseAndRemark,
de.remark remark,
(select param_value
from t_sys_param
where param_type = 'deliverState'
and PARAM_ID = de.deliver_state_code) deliverState,
(select param_value
from t_code_table
where code_type = 'deliverResult'
and param_id = de.deliver_result) deliverResult
from t_deliver_infomation de
where de.party_id=#partyID#
<isNotEmpty prepend="AND" property="deliverTimeB">
<![CDATA[
de.DELIVER_TIME >= to_date(#deliverTimeB#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeE">
<![CDATA[
to_char(de.DELIVER_TIME,'YYYY-MM-DD') <= #deliverTimeE#
]]>
</isNotEmpty>
<![CDATA[
) d)b
where b.rn > (#pageNo#-1)*#pageSize# and b.rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 配送跟踪 -->
<select id="deliver.trace.sum" parameterClass="com.tpaic.callCenter.dto.DeliverTraceDTO" resultClass="java.lang.String">
select count(distinct de.deliver_id)
from t_deliver_policy dp,
t_deliver_infomation de
where de.deliver_id = dp.deliver_id
and de.by_user_id=#currentUserId#
<isNotEmpty prepend="AND" property="partyName">
de.insured =#partyName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyNumber">
dp.policy_number =#policyNumber#
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyPactID">
dp.policy_number = #policyPactID#
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
de.area_code=#areaCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
de.deliver_state_code=#deliverStateCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeBegin">
<![CDATA[
de.DELIVER_TIME >= #deliverTimeBegin#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeEnd">
<![CDATA[
de.DELIVER_TIME <= #deliverTimeEnd#
]]>
</isNotEmpty>
</select>
<select id="deliver.trace.list" parameterClass="com.tpaic.callCenter.dto.DeliverTraceDTO" resultClass="com.tpaic.callCenter.dto.DeliverTraceDTO">
select * from (
select rownum rn,d.* from (
select distinct dp.deliver_id deliverID,
de.insured partyName,
de.deliver_type deliverType,
de.deliver_time deliverTime,
(de.untread_cause ||','|| de.remark) untreadCauseAndRemark,
de.remark remark,
(select param_value
from t_sys_param
where param_type = 'deliverState'
and PARAM_ID = de.deliver_state_code) deliverState,
(select param_value
from t_code_table
where code_type = 'deliverResult'
and param_id = de.deliver_result) deliverResult
from t_deliver_policy dp,
t_deliver_infomation de
where de.deliver_id = dp.deliver_id
and de.by_user_id=#currentUserId#
<isNotEmpty prepend="AND" property="partyName">
de.insured =#partyName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyNumber">
dp.policy_number =#policyNumber#
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyPactID">
dp.policy_number = #policyPactID#
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
de.area_code=#areaCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
de.deliver_state_code=#deliverStateCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeBegin">
<![CDATA[
de.DELIVER_TIME >= #deliverTimeBegin#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeEnd">
<![CDATA[
de.DELIVER_TIME <= #deliverTimeEnd#
]]>
</isNotEmpty>
<![CDATA[
) d)b
where b.rn > (#pageNo#-1)*#pageSize# and b.rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 配送结果录入规则 -->
<select id="deliver.result.rule.sum" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" resultClass="java.lang.String">
select count(t.rule_id)
from t_deliver_result_rule t
where 1=1
<isNotEmpty prepend="AND" property="ruleID">
t.rule_id= #ruleID#
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
t.area_code=#areaCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
t.deliver_company_id =#deliverCompany#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
t.deliver_type = #deliverType#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResult">
t.deliver_result=#deliverResult#
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
t.pay_mode=#payMode#
</isNotEmpty>
</select>
<select id="deliver.result.rule.page" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" resultClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO">
select * from (
select rownum rn,d.* from (
select t.rule_id ruleID,
t.area_code areaCode,
(select param_value
from t_code_table
where code_type = 'area'
and param_id = t.area_code) areaName,
t.deliver_company_id deliverCompany,
(select company_name from t_deliver_company
where company_id= t.deliver_company_id) deliverCompanyName,
t.is_self_get isSelfGet,
t.deliver_type deliverType,
(select param_value
from t_code_table
where code_type = 'deliverType'
and param_id = t.deliver_type) deliverTypeName,
t.deliver_result deliverResult,
(select param_value
from t_code_table
where code_type = 'deliverResult'
and param_id = t.deliver_result) deliverResultName,
t.pay_mode payMode,
(select param_value
from t_code_table
where code_type = 'payMode'
and param_id = t.pay_mode) payModeName
from t_deliver_result_rule t
where 1=1
<isNotEmpty prepend="AND" property="ruleID">
t.rule_id=#ruleID#
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
t.area_code=#areaCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
t.deliver_company_id =#deliverCompany#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
t.deliver_type = #deliverType#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResult">
t.deliver_result=#deliverResult#
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
t.pay_mode=#payMode#
</isNotEmpty>
<![CDATA[
) d )b
where b.rn > (#pageNo#-1)*#pageSize# and b.rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.result.rule.list" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" resultClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO">
select t.rule_id ruleID,
t.area_code areaCode,
t.deliver_company_id deliverCompany,
t.is_self_get isSelfGet,
t.deliver_type deliverType,
t.deliver_result deliverResult,
t.pay_mode payMode
from t_deliver_result_rule t
where 1=1
<isNotEmpty prepend="AND" property="ruleID">
t.rule_id=#ruleID#
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
t.area_code=#areaCode#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverCompany">
t.deliver_company_id =#deliverCompany#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
t.deliver_type = #deliverType#
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverResult">
t.deliver_result=#deliverResult#
</isNotEmpty>
<isNotEmpty prepend="AND" property="payMode">
t.pay_mode=#payMode#
</isNotEmpty>
</select>
<insert id="deliver.result.rule.insert" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" >
insert into t_deliver_result_rule
(created_by,
rule_id,
area_code,
deliver_company_id,
is_self_get,
deliver_type,
deliver_result,
pay_mode)
values
(#createdBy#,
SEQ_T_DELIVER_RESULT_RULE.nextVal,
#areaCode#,
#deliverCompany#,
#isSelfGet#,
#deliverType#,
#deliverResult#,
#payMode#)
</insert>
<update id="deliver.result.rule.update" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" >
update t_deliver_result_rule t
set
t.updated_by=#updatedBy#,
t.area_code=#areaCode#,
t.deliver_company_id =#deliverCompany#,
t.is_self_get =#isSelfGet#,
t.deliver_type =#deliverType#,
t.deliver_result =#deliverResult#,
t.pay_mode =#payMode#
where t.rule_id=#ruleID#
</update>
<delete id="deliver.result.rule.delete" parameterClass="com.tpaic.callCenter.dto.DeliverResultRuleDTO" >
delete from t_deliver_result_rule t where t.rule_id=#ruleID#
</delete>
<select id="deliver.searchOldDeliverInfo" parameterClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO" resultClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO">
<![CDATA[
select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
t.CONTACT_MODE as contactMode,
t.area_code as areaCode,
t.pay_mode payMode,
t.isselfget as isSelfGet,
t.insured,
t.deliver_type as deliverType,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
t.contact_people as partyName,
t.by_user_id byUserId,
t.role_id roleID,
t.CURRENT_USER_ID currentUserID,
t.deliver_company companyId,
t.remark,
t.ischeckvehicle isCheckVehicle,
t.insurance_data insuranceData,
t.province_code provinceCode,
t.city_code cityCode,
t.town_code townCode,
t.is_deliver_present isDeliverPresent,
t.contact_people contactPeople,
t.deliver_mode deliverMode,
(select ct.param_value from t_code_table ct
where ct.code_type = 'untreadRemark'
and ct.param_id = t.untread_remark) untreadRemark,
t.other_Province otherProvince,
(select ct.param_value
from t_deliver_present_detail dpd, t_code_table ct
where dpd.deliver_id = t.deliver_id
and ct.code_type = 'againdeliver'
and ct.param_id = dpd.deliver_reason
and rownum =1) sendPresentReason,
(select ct.param_Id
from t_deliver_present_detail dpd, t_code_table ct
where dpd.deliver_id = t.deliver_id
and ct.code_type = 'againdeliver'
and ct.param_id = dpd.deliver_reason
and rownum =1 ) sendPresentReasonCode
from T_DELIVER_INFOMATION t
left outer join t_user u on t.current_user_id = u.user_id
left outer join t_code_table cddc on t.deliver_company = cddc.param_id
and cddc.code_type = 'deliverCompany'
left outer join t_deliver_company di on di.company_id = t.deliver_company
where t.DELIVER_ID = trim(#deliverId#)
order by t.deliver_id asc
]]>
</select>
<!-- 快u-28654 超u26399 反u-26232 提u-28270 规u21017 -->
<statement id="deliver.insertDeliverReportOverTimeAlertRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
insert into T_DELIVER_REPORT_ALERT_RULE (
DELIVER_ALERT_RULE_ID,
over_days,
email_address,
area_code,
valid_flag,
updated_by,
updated_date,
created_by,
created_date)
values
(SEQ_DELIVER_ALERT_RULE_ID.NEXTVAL,
#overDays:VARCHAR#,
#emailAddress:VARCHAR#,
#areaCode:VARCHAR#,
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate
)
]]>
</statement>
<select id="deliver.searchDeliverReportOverTimeAlertRuleList" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select
t.DELIVER_ALERT_RULE_ID ruleId,
t.over_days overDays,
t.email_address emailAddress,
t.area_code areaCode,
cd1.param_value areaCodeValue,
t.valid_flag validFlag ,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate
from T_DELIVER_REPORT_ALERT_RULE t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID = AREA_CODE
where t.valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="ruleId">
<![CDATA[
t.DELIVER_ALERT_RULE_ID =#ruleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
order by t.DELIVER_ALERT_RULE_ID desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.searchDeliverReportOverTimeAlertRuleNum" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_DELIVER_REPORT_ALERT_RULE
where valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="ruleId">
<![CDATA[
DELIVER_ALERT_RULE_ID =#ruleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
area_code = #areaCode#
]]>
</isNotEmpty>
</select>
<select id="deliver.searchDeliverReportOverTimeAlertRuleIsExist" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_DELIVER_REPORT_ALERT_RULE
where valid_flag='Y'
and area_code = #areaCode#
]]>
</select>
<select id="Deliver.searchDeliverReportOverTimeRuleInfo" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select
DELIVER_ALERT_RULE_ID ruleId,
over_days overDays,
email_address emailAddress,
area_code areaCode,
valid_flag validFlag ,
updated_by updatedBy,
updated_date updatedDate,
created_by createdBy,
created_date createDate
from T_DELIVER_REPORT_ALERT_RULE
where DELIVER_ALERT_RULE_ID =#ruleId#
]]>
</select>
<statement id="deliver.updateDeliverReportOverTimeAlertRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
update T_DELIVER_REPORT_ALERT_RULE set
over_days = #overDays:VARCHAR#,
email_address = #emailAddress:VARCHAR#,
area_code = #areaCode:VARCHAR#,
valid_flag = #validFlag:VARCHAR#,
updated_by = #updatedBy:VARCHAR#,
updated_date = sysdate
where DELIVER_ALERT_RULE_ID =#ruleId#
]]>
</statement>
<statement id="deliver.searchSelectedInsuranceData" parameterClass="java.util.HashMap" resultClass="com.tpaic.callCenter.dto.CodeTableDTO">
<![CDATA[
select CODE_TYPE codeType,
CODE_DESC codeDesc,
PARAM_ID paramId,
PARAM_VALUE paramValue,
VALID_FLAG validFlag
from t_code_table
where CODE_TYPE=#codeType#
and valid_flag='Y'
]]>
<isNotEmpty prepend="and" property="paramId">
PARAM_ID in
<iterate close=")" open="(" conjunction="," property="paramId">#paramId[]#</iterate>
</isNotEmpty>
</statement>
<select id="deliver.searchPresentVerifyList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select * from
(select rownum rn, a.* from
(select t.DELIVER_ID as deliverID,
t.DELIVER_ADDRESS as deliverAddress,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.PROVINCE_CODE and cc.VALID_FLAG='Y' and rownum=1) provinceName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.CITY_CODE and cc.VALID_FLAG='Y' and rownum=1) cityName,
(select cc.VALUE_NAME from t_code_city cc where cc.value_code=t.TOWN_CODE and cc.VALID_FLAG='Y' and rownum=1) townName,
t.CONTACT_MODE as contactMode,
t.area_code as areaCode,
t.insured,
t.deliver_type deliverType,
dtn.param_value deliverTypeName,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
t.contact_people as partyName,
cd1.param_value as areaName,
cd3.param_value as deliverState,
t.ischeckvehicle as isCheckVehicle,
dc.company_name deliverCompanyName,
t.is_deliver_present isDeliverPresent,
nvl(t.carboat_premium,0) carboatPremium,
nvl(t.all_premium,0) allPremium,
(select tci.vehicle_number
from t_deliver_policy dp, t_car_info tci
where dp.car_id = tci.car_id
and dp.deliver_id = t.deliver_id
and tci.valid_flag = 'Y'
and rownum = 1) vehicleNumber
from T_DELIVER_INFOMATION t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE='area' and cd1.PARAM_ID=t.AREA_CODE
left outer join t_deliver_company dc on dc.company_id = t.deliver_company
left outer join t_sys_param cd3 on cd3.PARAM_TYPE='deliverState' and cd3.PARAM_ID=t.deliver_state_code
left outer join t_user u on t.current_user_id=u.user_id
left outer join t_code_table dtn on dtn.code_type='deliverType' and dtn.param_id=t.deliver_type
where t.area_Code=#areaCode#
]]>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactPeople">
<![CDATA[
t.contact_people =#contactPeople#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured = #insured:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIDArr">
t.deliver_id in
<iterate close=")" open="(" conjunction="," property="deliverIDArr">#deliverIDArr[]#</iterate>
</isNotEmpty>
<![CDATA[
order by t.deliver_id desc) a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.searchPresentVerifyNum" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_DELIVER_INFOMATION t
where t.area_Code=#areaCode#
]]>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
t.DELIVER_STATE_CODE = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeFrom">
<![CDATA[
t.DELIVER_TIME >= to_date(#deliverTimeFrom#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTimeTo">
<![CDATA[
t.DELIVER_TIME <= to_date(#deliverTimeTo#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactPeople">
<![CDATA[
t.contact_people =#contactPeople#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="contactMode">
<![CDATA[
t.CONTACT_MODE =#contactMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insured">
<![CDATA[
t.insured = #insured:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.DELIVER_ID = #deliverID:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliver_type = #deliverType:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
exists (select 1
from t_deliver_policy dp
where dp.deliver_id = t.deliver_id
and dp.car_id in (select tci.car_id from t_car_info tci where tci.vehicle_number=#vehicleNumber# and tci.valid_flag='Y' ))
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverIDArr">
t.deliver_id in
<iterate close=")" open="(" conjunction="," property="deliverIDArr">#deliverIDArr[]#</iterate>
</isNotEmpty>
</select>
<!-- -->
<update id="DeliverInfo.updateDeliverPresentState" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update T_DELIVER_INFOMATION d
set d.DELIVER_STATE_CODE =#deliverStateCode:VARCHAR#,
d.updated_date= sysdate,
d.updated_by = #updatedBy:varchar#
where d.DELIVER_ID = to_number(#deliverID:VARCHAR#)
</update>
<select id="deliver.searchDeliverByDeliverTypeForNum" parameterClass="com.tpaic.callCenter.dto.deliver.DeliverReviewMainDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)num
from t_deliver_infomation i, t_customer cm,t_user u
where
cm.party_id = i.party_id
and i.by_user_id = u.user_id
and exists (select 1
from t_customer_task ct
where u.user_id = ct.current_user_id
and ct.status = '1')
and i.created_by=u.um_code
and u.um_code=#umCode#
]]>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
i.deliver_type = #deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
i.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
cm.party_name like #partyName#||'%'
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverId">
<![CDATA[
i.deliver_id = #deliverId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
i.deliver_state_code = #deliverStateCode#
]]>
</isNotEmpty>
</select>
<select id="deliver.searchDeliverByDeliverTypeList" parameterClass="com.tpaic.callCenter.dto.deliver.DeliverReviewMainDTO" resultClass="com.tpaic.callCenter.dto.deliver.DeliverReviewMainDTO">
<![CDATA[
select *
from (select rownum rn,
i.deliver_id deliverId,
i.party_id partyId,
cm.party_name partyName,
i.deliver_state_code deliverStateCode,
(select sp.param_value
from t_sys_param sp
where sp.param_type = 'deliverState'
and sp.param_id = i.deliver_state_code) deliverState,
(select ct.param_value
from t_deliver_review_main m, t_code_table ct
where m.valid_flag = 'Y'
and m.review_type = '02'
and m.review_state = ct.param_id
and ct.code_type = 'auditStatus'
and ct.valid_flag = 'Y'
and to_number(m.review_obj) = i.deliver_id) reviewStateName,
(select s.flow_state
from t_deliver_review_main m, t_deliver_review_sub s
where m.review_main_id = s.review_main_id
and s.review_flow_point = '01'
and m.valid_flag = 'Y'
and to_number(m.review_obj) = i.deliver_id
and m.review_type = '02') flowState,
(select s.flow_state
from t_deliver_review_main m, t_deliver_review_sub s
where m.review_main_id = s.review_main_id
and s.review_flow_point = '02'
and m.valid_flag = 'Y'
and to_number(m.review_obj) = i.deliver_id
and m.review_type = '02') flowState2
from t_deliver_infomation i, t_customer cm, t_user u
where
cm.party_id = i.party_id
and i.by_user_id = u.user_id
and exists (select 1
from t_customer_task ct
where u.user_id = ct.current_user_id
and ct.status = '1')
and i.created_by = u.um_code
and u.um_code=#umCode#
]]>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
i.deliver_type = #deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
cm.party_name like #partyName#||'%'
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverId">
<![CDATA[
i.deliver_id = #deliverId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverStateCode">
<![CDATA[
i.deliver_state_code = #deliverStateCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
i.area_code = #areaCode#
]]>
</isNotEmpty>
<![CDATA[
)
where
rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliver.searchDeliverPresents" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.PresentDTO">
<![CDATA[
select d.present_id presentId,
d.policy_number policyPactId
from t_deliver_present_detail d
where d.deliver_id=#deliverId#
]]>
</select>
<statement id="present.searchPresentExpense" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="java.lang.String">
<![CDATA[
select nvl(sum(p.present_price),0)
from
t_deliver_present_detail dpd,
t_deliver_infomation di,
t_present p
where dpd.deliver_id = di.deliver_id
and dpd.present_id=p.present_id
and dpd.valid_flag='Y'
and p.present_attribute='1'
and di.deliver_id=#deliverID#
]]>
</statement>
<update id="deliver.updateOutInsuranceStateByPolicyId" parameterClass="com.tpaic.callCenter.dto.datainterface.InsuranceDTO" >
update t_out_insurance_info
set deliver_state = #deliverState#,
updated_date=sysdate,
updated_by=#updatedBy:varchar#,
sql_id='deliver.updateOutInsuranceStateByPolicyId'
where policy_pact_id =
(select policy_pact_id
from t_policy_information
where policy_id = #policyId#)
</update>
<update id="deliver.updateOutInsuranceStateByAppPolicyId" parameterClass="com.tpaic.callCenter.dto.DeliverPolicyDTO" >
update t_out_insurance_info
set deliver_state = '00',
updated_date=sysdate,
updated_by=#updatedBy:varchar#,
sql_id='deliver.updateOutInsuranceStateByAppPolicyId'
where policy_pact_id = #policyID#
</update>
<!-- 配送预约时间控制规则 20101206 tg 添加配送类型字段 deliverType -->
<statement id="deliverAppoint.saveDeliverAppointTimeRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
insert into T_DELIVER_APPOINT_TIME_RULE
(APPOINT_TIME_RULE_ID,
AREA_CODE,
BEFORE1TO5_TIME,
BEFORE1TO5_DAYS,
LATE1TO4_TIME,
LATE1TO4_DAYS,
LATE5_TIME,
LATE5_DAYS,
SATURDAYDAYS,
SUNDAYDAYS,
HOLIDAYFROM,
HOLIDAYTO,
LATEMAXHOLIDAYDAYS,
VALID_FLAG,
UPDATED_BY,
UPDATED_DATE,
CREATED_BY,
CREATED_DATE,
deliverType,
DELIVER_TIME_DELAY_DAYS,
EXCEED_PAY_END_TIME_FLAG)
values(
SEQ_APPOINT_TIME_RULE_ID.Nextval,
#areaCode:VARCHAR#,
#before1to5Time:VARCHAR#,
#before1to5Days:VARCHAR#,
#late1to4Time:VARCHAR#,
#late1to4Days:VARCHAR#,
#late5Time:VARCHAR#,
#late5Days:VARCHAR#,
#saturdayDays:VARCHAR#,
#sundayDays:VARCHAR#,
to_date(#holidayFrom:VARCHAR#,'yyyy-mm-dd'),
to_date(#holidayTo:VARCHAR#,'yyyy-mm-dd'),
#lateMaxHolidayDays:VARCHAR#,
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate,
#deliverType#,
#deliverTimeDays#,
#overPayEndTime#)
]]>
</statement>
<!-- 20101206 tg 添加配送类型字段 deliverType -->
<select id="deliverAppoint.searchDeliverAppointTimeRuleList" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select *
from (select rownum rn, a.*
from (select t.APPOINT_TIME_RULE_ID appointTimeRuleId,
t.AREA_CODE areaCode,
t.BEFORE1TO5_TIME before1to5Time,
t.before1to5_Days before1to5Days,
t.LATE1TO4_TIME late1to4Time,
t.LATE1TO4_DAYS late1to4Days,
t.LATE5_TIME late5Time,
t.LATE5_DAYS late5Days,
t.SATURDAYDAYS saturdayDays,
t.SUNDAYDAYS sundayDays,
to_char(t.HOLIDAYFROM,'yyyy-mm-dd') holidayFrom,
to_char(t.HOLIDAYTO,'yyyy-mm-dd') holidayTo,
t.LATEMAXHOLIDAYDAYS lateMaxHolidayDays,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate,
cd1.param_value areaCodeValue,
t.deliverType deliverType,
t.DELIVER_TIME_DELAY_DAYS deliverTimeDays,
t.EXCEED_PAY_END_TIME_FLAG overPayEndTime
from T_DELIVER_APPOINT_TIME_RULE t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID =
t.AREA_CODE
where t.area_code = #areaCode#
and t.deliverType=#deliverType#
]]>
<![CDATA[
order by t.APPOINT_TIME_RULE_ID desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 20101206 tg 添加配送类型字段 deliverType 为条件 -->
<select id="deliverAppoint.searchDeliverAppointTimeRuleForNum" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_DELIVER_APPOINT_TIME_RULE t
where t.area_code = #areaCode#
and t.deliverType=#deliverType#
]]>
</select>
<!-- 20121022 wuly 添加配送类型字段 deliverType -->
<select id="Deliver.searchDeliverAppointRuleInfo1" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select t.APPOINT_TIME_RULE_ID appointTimeRuleId,
t.AREA_CODE areaCode,
t.BEFORE1TO5_TIME before1to5Time,
t.before1to5_Days before1to5Days,
t.LATE1TO4_TIME late1to4Time,
t.LATE1TO4_DAYS late1to4Days,
t.LATE5_TIME late5Time,
t.LATE5_DAYS late5Days,
t.SATURDAYDAYS saturdayDays,
t.SUNDAYDAYS sundayDays,
to_char(t.HOLIDAYFROM, 'yyyy-mm-dd') holidayFrom,
to_char(t.HOLIDAYTO, 'yyyy-mm-dd') holidayTo,
t.LATEMAXHOLIDAYDAYS lateMaxHolidayDays,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate,
t.deliverType deliverType,
t.DELIVER_TIME_DELAY_DAYS deliverTimeDays,
t.EXCEED_PAY_END_TIME_FLAG overPayEndTime,
to_char(sysdate + t.DELIVER_TIME_DELAY_DAYS, 'yyyy-mm-dd') deliverTimeDaysDate,
(select to_char(last_day(sysdate)-re.before_monthend,'yyyy-MM-dd')
from t_deliver_appoint_time_rule_ex re
where re.appoint_time_rule_id = t.appoint_time_rule_id
and re.VALID_FLAG='Y'
and re.deliver_mode='01' and rownum = 1) deliverTimeMonthEndDate,
(select td.before_insurbegin
from t_deliver_appoint_time_rule_ex td
where td.appoint_time_rule_id = t.appoint_time_rule_id
and td.VALID_FLAG='Y'
and td.deliver_mode = '01'
and rownum = 1) beforeInsurBegin
from t_deliver_appoint_time_rule t
]]>
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="appointTimeRuleId">
<![CDATA[
t.APPOINT_TIME_RULE_ID=#appointTimeRuleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliverType=#deliverType#
]]>
</isNotEmpty>
</dynamic>
</select>
<!-- 20101206 tg 添加配送类型字段 deliverType -->
<select id="Deliver.searchDeliverAppointTimeRuleInfo" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select t.APPOINT_TIME_RULE_ID appointTimeRuleId,
t.AREA_CODE areaCode,
t.BEFORE1TO5_TIME before1to5Time,
t.before1to5_Days before1to5Days,
t.LATE1TO4_TIME late1to4Time,
t.LATE1TO4_DAYS late1to4Days,
t.LATE5_TIME late5Time,
t.LATE5_DAYS late5Days,
t.SATURDAYDAYS saturdayDays,
t.SUNDAYDAYS sundayDays,
to_char(t.HOLIDAYFROM, 'yyyy-mm-dd') holidayFrom,
to_char(t.HOLIDAYTO, 'yyyy-mm-dd') holidayTo,
t.LATEMAXHOLIDAYDAYS lateMaxHolidayDays,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate,
t.deliverType deliverType,
t.DELIVER_TIME_DELAY_DAYS deliverTimeDays,
t.EXCEED_PAY_END_TIME_FLAG overPayEndTime,
to_char(sysdate + t.DELIVER_TIME_DELAY_DAYS, 'yyyy-mm-dd') deliverTimeDaysDate,
(select to_char(last_day(sysdate)-re.before_monthend,'yyyy-MM-dd')
from t_deliver_appoint_time_rule_ex re
where re.appoint_time_rule_id = t.appoint_time_rule_id
and re.VALID_FLAG ='Y'
and re.deliver_mode='01' and rownum = 1) deliverTimeMonthEndDate,
(select td.before_insurbegin
from t_deliver_appoint_time_rule_ex td
where td.appoint_time_rule_id = t.appoint_time_rule_id
and td.VALID_FLAG ='Y'
and td.deliver_mode = '01'
and rownum = 1) beforeInsurBegin
from t_deliver_appoint_time_rule t
]]>
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="appointTimeRuleId">
<![CDATA[
t.APPOINT_TIME_RULE_ID=#appointTimeRuleId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
t.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverType">
<![CDATA[
t.deliverType=#deliverType#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
exists (select 1
from t_deliver_appoint_time_rule_ex t1
where t1.appoint_time_rule_id = t.appoint_time_rule_id
and t1.VALID_FLAG ='Y'
and t1.deliver_mode = #deliverMode#
and (t1.apply_town_code like '%' || #cityCode# || '%' or t1.apply_town_code like '%' || #townCode# || '%'))
]]>
</isNotEmpty>
</dynamic>
</select>
<statement id="deliverAppoint.updateDeliverAppointTimeRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
update T_DELIVER_APPOINT_TIME_RULE set
BEFORE1TO5_TIME = #before1to5Time:VARCHAR#,
before1to5_Days = #before1to5Days:VARCHAR#,
LATE1TO4_TIME = #late1to4Time:VARCHAR#,
LATE1TO4_DAYS = #late1to4Days:VARCHAR#,
LATE5_TIME = #late5Time:VARCHAR#,
LATE5_DAYS = #late5Days:VARCHAR#,
SATURDAYDAYS = #saturdayDays#,
SUNDAYDAYS = #sundayDays:VARCHAR#,
HOLIDAYFROM = to_date(#holidayFrom#,'YYYY-MM-DD'),
HOLIDAYTO = to_date(#holidayTo#,'YYYY-MM-DD'),
LATEMAXHOLIDAYDAYS = #lateMaxHolidayDays:VARCHAR#,
DELIVER_TIME_DELAY_DAYS=#deliverTimeDays#,
EXCEED_PAY_END_TIME_FLAG=#overPayEndTime#,
area_code = #areaCode:VARCHAR#,
valid_flag = #validFlag:VARCHAR#,
updated_by = #updatedBy:VARCHAR#,
updated_date = sysdate
where APPOINT_TIME_RULE_ID=to_number(#appointTimeRuleId#)
]]>
</statement>
<!-- 20101206 tg 添加配送类型字段 deliverType 为条件 -->
<statement id="deliver.searchDeliverAppointIsExist" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*) from T_DELIVER_APPOINT_TIME_RULE t
where t.area_code = #areaCode:VARCHAR#
and t.deliverType=#deliverType#
and t.valid_flag = 'Y'
]]>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
exists (select 1
from t_deliver_appoint_time_rule_ex t1
where t1.appoint_time_rule_id = t.appoint_time_rule_id
and t1.deliver_mode = #deliverMode#
and t1.valid_flag = 'Y'
and (t1.apply_town_code like '%' || #cityCode# || '%' or t1.apply_town_code like '%' || #townCode# || '%'))
]]>
</isNotEmpty>
</statement>
<!-- 20121022 wuly 添加配送类型字段 deliverType 为条件 -->
<statement id="deliver.searchDeliverAppointIsExistOther" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*) from T_DELIVER_APPOINT_TIME_RULE t
where t.area_code = #areaCode:VARCHAR#
and t.deliverType=#deliverType#
and t.valid_flag = 'Y'
]]>
</statement>
<statement id="deliver.searchCreateDeliverTimeRuleIsExist" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*) from T_CREATE_DELIVER_TIME_RULE
where area_code = #areaCode:VARCHAR#
]]>
</statement>
<!-- 配送流转时间点控制 -->
<statement id="deliverAppoint.saveCreateDeliverTimeRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
insert into T_CREATE_DELIVER_TIME_RULE
(CREATE_DELIVER_TIMERULE_ID,
AREA_CODE,
LATE1TO4_TIME,
LATE5_TIME,
VALID_FLAG,
UPDATED_BY,
UPDATED_DATE,
CREATED_BY,
CREATED_DATE)
values(
SEQ_CREATE_DELIVER_TIMERULE_ID.Nextval,
#areaCode:VARCHAR#,
#late1to4Time:VARCHAR#,
#late5Time:VARCHAR#,
'Y',
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate)
]]>
</statement>
<select id="deliverAppoint.searchCreateDeliverTimeRuleList" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select *
from (select rownum rn, a.*
from (select t.CREATE_DELIVER_TIMERULE_ID createDeliverTimeRuleId,
t.AREA_CODE areaCode,
t.LATE1TO4_TIME late1to4Time,
t.LATE5_TIME late5Time,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate,
cd1.param_value areaCodeValue
from T_CREATE_DELIVER_TIME_RULE t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID =
t.AREA_CODE
where t.area_code = #areaCode#
]]>
<![CDATA[
order by t.CREATE_DELIVER_TIMERULE_ID desc)a)
where rn >(#pageNo#-1)*#pageSize# and rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="deliverAppoint.searchCreateDeliverTimeRuleForNum" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_CREATE_DELIVER_TIME_RULE t
where t.area_code = #areaCode#
]]>
</select>
<select id="Deliver.searchCreateDeliverTimeRuleInfo" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
select t.CREATE_DELIVER_TIMERULE_ID createDeliverTimeRuleId,
t.AREA_CODE areaCode,
t.LATE1TO4_TIME late1to4Time,
t.LATE5_TIME late5Time,
t.valid_flag validFlag,
t.updated_by updatedBy,
t.updated_date updatedDate,
t.created_by createdBy,
t.created_date createDate,
cd1.param_value areaCodeValue
from T_CREATE_DELIVER_TIME_RULE t
left outer join T_CODE_TABLE cd1 on cd1.CODE_TYPE = 'area'
and cd1.PARAM_ID =
t.AREA_CODE
where t.CREATE_DELIVER_TIMERULE_ID=#createDeliverTimeRuleId#
]]>
</select>
<statement id="deliverAppoint.updateCreateDeliverTimeRule" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO">
<![CDATA[
update T_CREATE_DELIVER_TIME_RULE set
LATE1TO4_TIME = #late1to4Time:VARCHAR#,
LATE5_TIME = #late5Time:VARCHAR#,
area_code = #areaCode:VARCHAR#,
valid_flag = #validFlag:VARCHAR#,
updated_by = #updatedBy:VARCHAR#,
updated_date = sysdate
where CREATE_DELIVER_TIMERULE_ID=to_number(#createDeliverTimeRuleId#)
]]>
</statement>
<!-- 20101206 tg 添加配送类型字段 deliverType 及 保单生成日期＋各机构规则日期＝配送预约日期，当机构不存在对应规则默认保单生成日期＋1。 -->
<select id="deliver.searchDeliverRuleDate" parameterClass="com.tpaic.callCenter.dto.DeliverRuleInfoDTO" resultClass="java.lang.String">
<![CDATA[
select to_char(decode(count(deliverRuleDate),
0,
sysdate+1,
min(deliverRuleDate)),
'yyyy-mm-dd') deliverRuleDate
from (select CASE
WHEN sysdate between r.holidayfrom and r.holidayto THEN
r.holidayto + r.latemaxholidaydays
WHEN to_char(sysdate-1, 'D') = 7 THEN
sysdate + sundaydays
WHEN to_char(sysdate-1, 'D') = 6 THEN
sysdate + saturdaydays
WHEN to_char(sysdate-1, 'D') = 5 and
(select ct.param_value
from t_code_table ct
where ct.code_type = '24hours'
and ct.param_id = r.late5_time) <
to_char(sysdate, 'HH24:mi') THEN
sysdate + late5_days
WHEN to_char(sysdate-1, 'D') <= 4 and
(select ct.param_value
from t_code_table ct
where ct.code_type = '24hours'
and ct.param_id = r.late1to4_time) <
to_char(sysdate, 'HH24:mi') THEN
sysdate + late1to4_days
WHEN to_char(sysdate-1, 'D') <= 5 and
(select ct.param_value
from t_code_table ct
where ct.code_type = '24hours'
and ct.param_id = r.before1to5_time) >=
to_char(sysdate, 'HH24:mi') THEN
sysdate + before1to5_days
ELSE
sysdate+1
END deliverRuleDate
from t_deliver_appoint_time_rule r
where r.valid_flag = 'Y'
and r.area_code = #areaCode#
and r.deliverType=#deliverType#
]]>
<isNotEmpty prepend="AND" property="deliverMode">
<![CDATA[
exists (select 1
from t_deliver_appoint_time_rule_ex t1
where t1.appoint_time_rule_id = r.appoint_time_rule_id
and t1.deliver_mode = #deliverMode#
and t1.valid_flag = 'Y'
and (t1.apply_town_code like '%' || #cityCode# || '%' or t1.apply_town_code like '%' || #townCode# || '%'))
]]>
</isNotEmpty>
<![CDATA[
)
]]>
</select>
<!-- 注释 by lc 2010-08-03
<select id="deliverAppoint.isHasAppreciation" parameterClass="java.lang.String" resultClass="java.lang.String">
<![CDATA[
select count(*)
from T_POLICY_APPRECIATION a, t_deliver_policy p
where a.policy_number = p.policy_number
and a.valid_flag = 'Y'
and appreciation_code='1'
and p.deliver_id = #deliverId#
]]>
</select>
-->
<!-- add by lc 2010-08-03 查询增值服务 -->
<select id="deliverAppoint.SearchAppreciationForList" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.DeliverPrintDTO">
<![CDATA[
select (select c.param_value from t_code_table c where c.code_type='appreciationType' and c.param_id=pa.appreciation_code and c.valid_flag='Y' and rownum=1) appreciationCode
from T_POLICY_APPRECIATION pa, t_deliver_policy p
where pa.policy_number = p.policy_number
and pa.valid_flag = 'Y'
and p.deliver_id = #value#
union
select (select c.param_value from t_code_table c where c.code_type='appreciationType' and c.param_id=pa.appreciation_code and c.valid_flag='Y' and rownum=1) appreciationCode
from T_POLICY_APPRECIATION pa,
t_deliver_policy p,
t_policy_information pi
where pi.policy_id = p.policy_number
and pa.policy_number = pi.policy_pact_id
and pa.valid_flag = 'Y'
and p.deliver_id = #value#
]]>
</select>
<update id="deliver.updateAppointmentByDeliverID" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update t_deliver_appointment a
set a.updated_date = sysdate
<isNotEmpty prepend="," property="deliverAddress">
<![CDATA[
a.deliver_address =#deliverAddress#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isSelfGet">
<![CDATA[
a.isselfget =#isSelfGet#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="deliverMode">
<![CDATA[
a.deliver_mode =#deliverMode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="areaCode">
<![CDATA[
a.area_code =#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="mainSalesAmount">
<![CDATA[
a.main_sales_amount =#mainSalesAmount#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="rewardsAmountTotal">
<![CDATA[
a.rewards_amount_total =#rewardsAmountTotal#
]]>
</isNotEmpty>
where a.party_id = #partyID#
</update>
<update id="deliver.updateAppointmentByPartyId" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
update t_deliver_appointment a
set a.updated_date = sysdate,
a.updated_by = #updatedBy#
<isNotEmpty prepend="," property="mainSalesAmount">
<![CDATA[
a.main_sales_amount =#mainSalesAmount#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="rewardsAmountTotal">
<![CDATA[
a.rewards_amount_total =#rewardsAmountTotal#
]]>
</isNotEmpty>
where a.party_id = #partyID#
</update>
<select id="deliver.searchCurrentDateDeliverInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
<![CDATA[
select t.DELIVER_ID as deliverID,
GET_POLICY_PACT_NUMBER(t.deliver_id) as policyNumber,
t.contact_people contactPeople,
t.insured,
t.isselfget isSelfGet,
t.isurgency isUrgency,
to_char(t.DELIVER_TIME, 'yyyy-mm-dd') as deliverTime,
cd3.param_value as deliverState,
to_char(t.updated_date,'yyyy-mm-dd HH24:MI:SS') updatedDate,
(select param_value from t_code_table dtn
where dtn.code_type='deliverType' and dtn.param_id=t.deliver_type and rownum=1)deliverTypeName,
u.user_name userName,
(select param_value from t_code_table dcre
where dcre.code_type='deliverResult' and dcre.param_id=t.deliver_result and rownum=1)deliverResultName,
(select param_value from t_code_table utr
where utr.code_type='untreadRemark' and utr.param_id=t.untread_remark and rownum=1) untreadRemarkDes,
(select param_value from t_code_table unsr
where unsr.code_type='withDrawalReason' and unsr.param_id=t.untread_cause and rownum=1)untreadCauseDes,
t.remark
from T_DELIVER_INFOMATION t ,t_sys_param cd3 ,t_user u
where cd3.PARAM_TYPE='deliverState' and cd3.PARAM_ID=t.deliver_state_code
and t.by_user_id=u.user_id(+)
and ((t.deliver_state_code ='04' and t.deliver_result='2') or t.deliver_state_code ='05')
and t.area_code = #areaCode:varchar#
and t.updated_date >=to_date(#updatedDate#,'yyyy-mm-dd')
and t.updated_date <=to_date(#updatedDate#,'yyyy-mm-dd')+1
order by t.updated_date
]]>
</select>
<!-- 20111126 tg 添加 机构工作人员申请开通电销用户权限 -->
<select id="department.searchSalePolicyInfo" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
SELECT * FROM(
SELECT rownum rn,app.* FROM
(
SELECT
MAX(DECODE(substr(oii.policy_pact_id,5,3),'105',oii.policy_pact_id))jqPolicyPactId,
MAX(DECODE(substr(oii.policy_pact_id,5,3),'108',oii.policy_pact_id,'104',oii.policy_pact_id))tradePolicyPactId,
oii.party_id partyId,
(select t.party_name from t_customer t where t.party_id=oii.party_id) partyName,
oii.car_id carId,
(SELECT ci.vehicle_number FROM t_car_info ci WHERE ci.party_id=oii.party_id AND rownum=1) vehicleNumber,
oii.CREATED_BY createdBy
FROM t_out_insurance_info oii WHERE 1=1 AND oii.insurance_state='0'
AND (substr(oii.policy_pact_id,5,3)='105'or substr(oii.policy_pact_id,5,3)='108' or substr(oii.policy_pact_id,5,3)='104')
AND
EXISTS (
SELECT 1
FROM t_customer tc
WHERE tc.party_id = oii.party_id
AND tc.area_code = #areaCode#
)
]]>
<dynamic>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
EXISTS(
SELECT 1
FROM t_customer tc
WHERE oii.party_id = tc.party_id
AND tc.party_name =#partyName#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer_phone tc
WHERE oii.party_id = tc.party_id
AND tc.phone_number = #phone#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
EXISTS (
SELECT 1
FROM t_car_info ci
WHERE ci.car_id = oii.car_id
AND ci.vehicle_number = #vehicleNumber#
)
]]>
</isNotEmpty>
<!--
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer tc
WHERE tc.party_id = oii.party_id
AND tc.area_code = #areaCode#
)
]]>
</isNotEmpty>
-->
</dynamic>
<![CDATA[
GROUP BY oii.party_id, oii.car_id,oii.CREATED_BY
]]>
<![CDATA[
order by oii.party_id desc
)app )
WHERE rn >(#pageNo#-1)*#pageSize# AND rn <= #pageNo#*#pageSize#
]]>
</select>
<!-- 20111126 tg 添加 机构工作人员申请开通电销用户权限 -->
<select id="nbdepartment.searchSalePolicyNum" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="java.lang.String">
<![CDATA[
SELECT count(1)
FROM (
SELECT
MAX(DECODE(substr(oii.policy_pact_id,5,3),'105',oii.policy_pact_id))jqPolicyPactId,
MAX(DECODE(substr(oii.policy_pact_id,5,3),'108',oii.policy_pact_id,'104',oii.policy_pact_id))tradePolicyPactId,
oii.party_id partyId,
(select t.party_name from t_customer t where t.party_id=oii.party_id) partyName,
oii.car_id carId,
(SELECT ci.vehicle_number FROM t_car_info ci WHERE ci.party_id=oii.party_id AND rownum=1) vehicleNumber,
oii.CREATED_BY createdBy
FROM t_out_insurance_info oii WHERE 1=1 and oii.insurance_state='0'
AND (substr(oii.policy_pact_id,5,3)='105'or substr(oii.policy_pact_id,5,3)='108' or substr(oii.policy_pact_id,5,3)='104')
AND
EXISTS (
SELECT 1
FROM t_customer tc
WHERE tc.party_id = oii.party_id
AND tc.area_code = #areaCode#
)
]]>
<dynamic>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
EXISTS(
SELECT 1
FROM t_customer tc
WHERE oii.party_id = tc.party_id
AND tc.party_name =#partyName#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer_phone tc
WHERE oii.party_id = tc.party_id
AND tc.phone_number = #phone#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
EXISTS (
SELECT 1
FROM t_car_info ci
WHERE ci.car_id = oii.car_id
AND ci.vehicle_number = #vehicleNumber#
)
]]>
</isNotEmpty>
<!--
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer tc
WHERE tc.party_id = oii.party_id
AND tc.area_code = #areaCode#
)
]]>
</isNotEmpty>
-->
</dynamic>
<![CDATA[
GROUP BY oii.party_id, oii.car_id,oii.CREATED_BY)aa
]]>
</select>
<select id="applySalePolicy.searchSalePolicyInfo" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="java.lang.String">
<![CDATA[
SELECT count(1)
FROM T_ORG_APPLY_POLICYSALE tn
WHERE (tn.apply_state='A' or tn.apply_state='B' or tn.apply_state='C' )
]]>
<dynamic>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
tn.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer_phone tc
WHERE tn.party_id = tc.party_id
AND tc.phone_number = #phone#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
tn.vehiclenumber=#vehicleNumber#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
tn.area_code=#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="jqPolicyPactId">
<![CDATA[
tn.jqpolicypactid=#jqPolicyPactId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="tradePolicyPactId">
<![CDATA[
tn.tradepolicypactid=#tradePolicyPactId#
]]>
</isNotEmpty>
<![CDATA[
order by tn.id desc
]]>
</dynamic>
</select>
<insert id="applyOpenAgent.insert" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
INSERT INTO T_ORG_APPLY_POLICYSALE
(id,
party_id,
car_id,
updated_by,
updated_date,
created_by,
created_date,
apply_state,
party_name,
vehiclenumber,
tradepolicypactid,
jqpolicypactid,
area_code,
phone)
VALUES
(SEQ_NB_APPLY_POLICYSALEID.Nextval,
#partyId#,
#carId#,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate,
'A',
trim(#partyName:VARCHAR#),
trim(#vehicleNumber:VARCHAR#),
trim(#tradePolicyPactId:VARCHAR#),
trim(#jqPolicyPactId:VARCHAR#),
trim(#areaCode:VARCHAR#),
#phone#
)
</insert>
<select id="applyOpenAgent.searchInfoList" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
SELECT * FROM(
SELECT rownum rn,app.* FROM
(
SELECT
tn.id id,
tn.party_id partyId,
tn.party_name partyName,
tn.car_id carId,
tn.vehiclenumber vehicleNumber,
tn.jqpolicypactid jqPolicyPactId,
tn.tradepolicypactid tradePolicyPactId,
tn.apply_state applyState,
tn.created_by createdBy,
(select user_name from t_user where um_code=tn.created_by)userName
FROM T_ORG_APPLY_POLICYSALE tn
WHERE tn.area_code=#areaCode#
]]>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
<![CDATA[
order by tn.id desc
)app )
WHERE rn >(#pageNo#-1)*#pageSize# AND rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="applyOpenAgent.searchNum" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="java.lang.String">
<![CDATA[
SELECT count(1)
FROM T_ORG_APPLY_POLICYSALE tn
WHERE tn.area_code = #areaCode#
]]>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
</select>
<update id="applyOpenAgent.updateInfo" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
UPDATE T_ORG_APPLY_POLICYSALE
SET apply_state=#applyState#,
CALLLISTTYPE=#callListType#
WHERE id=#id#
]]>
</update>
<select id="policyInsurance.searchSalePolicyInfo" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
SELECT * FROM(
SELECT rownum rn,app.* FROM
(
SELECT
tn.id id,
tn.party_id partyId,
tn.party_name partyName,
tn.car_id carId,
tn.vehiclenumber vehicleNumber,
tn.jqpolicypactid jqPolicyPactId,
tn.tradepolicypactid tradePolicyPactId,
tn.apply_state applyState
FROM T_ORG_APPLY_POLICYSALE tn
WHERE 1=1
]]>
<dynamic>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
tn.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
tn.phone=#phone#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
tn.vehiclenumber=#vehicleNumber#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
tn.area_code=#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
<![CDATA[
order by tn.id desc )app )
WHERE rn >(#pageNo#-1)*#pageSize# AND rn <= #pageNo#*#pageSize#
]]>
</dynamic>
</select>
<select id="policyInsurance.searchNum" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="java.lang.String">
<![CDATA[
SELECT count(1)
FROM T_ORG_APPLY_POLICYSALE tn
WHERE 1=1
]]>
<dynamic>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
tn.party_name =#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
EXISTS (
SELECT 1
FROM t_customer_phone tc
WHERE tn.party_id = tc.party_id
AND tc.phone_number = #phone#
)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
tn.vehiclenumber=#vehicleNumber#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
tn.area_code=#areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
</dynamic>
</select>
<select id="taskState.codeTable" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.CodeTableDTO">
<![CDATA[
SELECT PARAM_ID paramId,PARAM_VALUE paramValue
FROM T_CODE_TABLE
WHERE 1=1 AND VALID_FLAG='Y' AND code_type=#value# ORDER BY PARAM_VALUE DESC
]]>
</select>
<update id="deliverAppointment.update" parameterClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO">
<![CDATA[
UPDATE t_deliver_appointment
SET updated_date = sysdate,
ISSELFGET=#isSelfGet#,
DELIVER_TIME=trunc(sysdate)
]]>
<isNotEmpty prepend="," property="deliverType">
<![CDATA[
DELIVER_TYPE=#deliverType#
]]>
</isNotEmpty>
<![CDATA[
WHERE PARTY_ID=#partyId#
]]>
</update>
<!-- 查询电销、电服 用户email地址 user_system 1 电销 2 电服 -->
<select id="senduserEmail.search" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.UserDTO">
<![CDATA[
SELECT tu.um_code umCode,tu.email email,tu.user_name userName,tu.user_id userId
FROM t_user tu
WHERE tu.email is not null
AND tu.user_system=#userSystem#
AND rownum=1
]]>
</select>
<select id="Area.searchByUmCode" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.CodeTableDTO">
<![CDATA[
select distinct c.CODE_TYPE codeType,
c.CODE_DESC codeDesc,
c.PARAM_ID paramId,
c.PARAM_VALUE paramValue,
c.VALID_FLAG validFlag
from t_user t, t_deliver_role_area a, t_code_table c
where t.user_id = a.user_id
and a.area_code = c.param_id
and c.code_type = 'area'
and c.valid_flag = 'Y'
and t.valid_flag = 'Y'
and t.um_code = #value#
]]>
</select>
<select id="list.searchDeliverHoliday" parameterClass="com.tpaic.callCenter.dto.DeliverHolidayDTO" resultClass="com.tpaic.callCenter.dto.DeliverHolidayDTO">
<![CDATA[
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
select t.id id,
t.holiday_name holidayName,
to_char(t.start_time, 'YYYY-MM-DD') startDate,
to_char(t.end_time, 'YYYY-MM-DD') endDate,
t.area_codes areacodes,
t.valid_flag validFlag,
t.created_by createdBy,
t.created_date createdDate,
t.updated_by updatedBy,
t.updated_date updatedDate
from t_deliver_holiday t
]]>
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
',' || t.area_codes || ',' like '%,' || #areaCode# || ',%'
]]>
</isNotEmpty>
</dynamic>
<![CDATA[
order by t.created_by desc
) A WHERE ROWNUM <= #pageNo#*#pageSize#)
WHERE RN > (#pageNo#-1)*#pageSize#
]]>
</select>
<select id="num.searchDeliverHoliday" parameterClass="com.tpaic.callCenter.dto.DeliverHolidayDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from t_deliver_holiday t
]]>
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
',' || t.area_codes || ',' like '%,' || #areaCode# || ',%'
]]>
</isNotEmpty>
</dynamic>
</select>
<select id="deliverHoliday.queryById" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.DeliverHolidayDTO">
<![CDATA[
select t.id id,
t.holiday_name holidayName,
to_char(t.start_time, 'YYYY-MM-DD') startDate,
to_char(t.end_time, 'YYYY-MM-DD') endDate,
t.area_codes areacodes,
t.valid_flag validFlag,
t.created_by createdBy,
t.created_date createdDate,
t.updated_by updatedBy,
t.updated_date updatedDate
from t_deliver_holiday t
where t.id = #value#
]]>
</select>
<update id="deliverHoliday.updateInfo" parameterClass="com.tpaic.callCenter.dto.DeliverHolidayDTO">
update t_deliver_holiday t set t.updated_date=sysdate,t.updated_by=#updatedBy#
<isNotEmpty prepend="," property="holidayName">
<![CDATA[
t.holiday_name = #holidayName#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="startDate">
<![CDATA[
t.start_time = to_date(#startDate#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="endDate">
<![CDATA[
t.end_time = to_date(#endDate#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="areacodes">
<![CDATA[
t.area_codes = #areacodes#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="validFlag">
<![CDATA[
t.valid_flag = #validFlag#
]]>
</isNotEmpty>
where t.id = #id#
</update>
<insert id="deliverHoliday.insert" parameterClass="com.tpaic.callCenter.dto.DeliverHolidayDTO">
insert into t_deliver_holiday t (
t.id,
t.holiday_name,
t.start_time,
t.end_time,
t.area_codes,
t.valid_flag,
t.created_by,
t.created_date,
t.updated_by,
t.updated_date
)
values(
SEQ_deliver_holiday_id.nextVal,
#holidayName#,
to_date(#startDate#,'YYYY-MM-DD'),
to_date(#endDate#,'YYYY-MM-DD'),
#areacodes#,
#validFlag#,
#createdBy#,
sysdate,
#updatedBy#,
sysdate
)
</insert>
<select id="area.queryAreaName" parameterClass="java.util.HashMap" resultClass="java.lang.String">
select t.param_value
from t_code_table t
where t.code_type = 'area'
and t.valid_flag = 'Y'
and t.param_id in
<iterate close=")" open="(" conjunction="," property="areaCodeArray">#areaCodeArray[]#</iterate>
</select>
<select id="batchInfo.searchOrgPolicy" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
SELECT * FROM(
SELECT rownum rn,app.* FROM
(
SELECT
tn.id id,
tn.party_id partyId,
tn.party_name partyName,
tn.car_id carId,
tn.vehiclenumber vehicleNumber,
tn.policyid policyId,
tn.apply_state applyState,
tn.created_by createdBy,
tn.phone phone,
tn.umcode umCode,
(select cc.user_name from t_user cc where cc.um_code=tn.umcode)userName,
tn.deliver_warn deliverWarn,
tn.deliver_address deliverAddress,
tn.batch_type batchType,
tn.batch_remark batchRemark,
dd.department_chinese_name departmentName,
tn.upload_url uploadUrl
FROM T_ORG_APPLY_POLICYSALE tn,t_department_define dd
WHERE tn.department=dd.internal_department_code
]]>
<isNotEmpty prepend="AND" property="department">
<![CDATA[
tn.department=#department#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyId">
<![CDATA[
tn.policyid=#policyId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
tn.phone=#phone#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
tn.party_name=#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="umCode">
<![CDATA[
tn.umcode=#umCode#
]]>
</isNotEmpty>
<![CDATA[
order by tn.id desc
)app )
WHERE rn >(#pageNo#-1)*#pageSize# AND rn <= #pageNo#*#pageSize#
]]>
</select>
<select id="batchInfo.searchOrgPolicyNum" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO" resultClass="java.lang.String">
<![CDATA[
SELECT count(1) FROM T_ORG_APPLY_POLICYSALE tn,t_department_define dd
WHERE tn.department=dd.internal_department_code
]]>
<isNotEmpty prepend="AND" property="department">
<![CDATA[
tn.department=#department#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applyState">
<![CDATA[
tn.apply_state=#applyState#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyId">
<![CDATA[
tn.policyid=#policyId#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="phone">
<![CDATA[
tn.phone=#phone#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="partyName">
<![CDATA[
tn.party_name=#partyName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="umCode">
<![CDATA[
tn.umcode=#umCode#
]]>
</isNotEmpty>
</select>
<insert id="batchInfo.insert" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
insert into t_org_apply_policysale
(id,
updated_by,
updated_date,
created_by,
created_date,
apply_state,
phone,
umcode,
deliver_warn,
deliver_address,
batch_type,
batch_remark,
upload_url,
department,
policyid)
values
(SEQ_NB_APPLY_POLICYSALEID.Nextval,
#updatedBy:VARCHAR#,
sysdate,
#createdBy:VARCHAR#,
sysdate,
#applyState#,
trim(#phone#),
#umCode#,
trim(#deliverWarn#),
trim(#deliverAddress#),
#batchType#,
trim(#batchRemark#),
trim(#uploadUrl#),
#department#,
trim(#policyId#)
)
]]>
</insert>
<update id="update.policybatchById" parameterClass="com.tpaic.callCenter.dto.ConfirmSalePolicyDTO">
<![CDATA[
UPDATE t_org_apply_policysale
SET apply_state=#applyState#
WHERE policyid=#policyId#
]]>
</update>
<select id="Num.searchOutInsurance" parameterClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from t_out_insurance_info t
where t.insurance_state = 0
and exists (select 1
from t_record_succeed rs, t_record_insurance ri
where t.policy_pact_id = ri.policy_pact_id
and rs.contact_id = ri.record_insurance_id
and rs.record_state !='03'
and rs.record_state !='04')
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
EXISTS (SELECT 1
FROM t_customer tc
WHERE tc.party_id = t.party_id
AND tc.area_code = #areaCode#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
EXISTS (SELECT 1
FROM t_car_info c
WHERE c.car_id = t.car_id
AND c.vehicle_number = #vehicleNumber#)
]]>
</isNotEmpty>
</select>
<select id="List.searchOutInsurance" parameterClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO" resultClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO">
<![CDATA[
select * from
(select A.*,ROWNUM rn from
(select c.party_name partyName,
i.vehicle_number vehicleNumber,
t.policy_pact_id policyPactId,
t.party_id partyId,
t.car_id carId,
t.BUSSINESS_TYPE businessType,
t.IS_TEL_PAY isTelPay,
t.insurance_state insuranceStatus,
(select p.PARAM_VALUE from t_sys_param p where p.valid_flag = 'Y' and p.PARAM_TYPE = 'outPolicyState' and t.insurance_state=p.PARAM_ID and rownum=1) insuranceStatusDesc,
t.DEPARTMENT_CODE deptCode,
(select ii.plan_code_type from t_insurance_info ii where ii.policy_pact_id=t.policy_pact_id) planCodeType
from t_out_insurance_info t, t_customer c, t_car_info i
where t.party_id = c.party_id
and t.car_id = i.car_id
and t.insurance_state='0'
and exists (select 1
from t_record_succeed rs, t_record_insurance ri
where t.policy_pact_id = ri.policy_pact_id
and rs.contact_id = ri.record_insurance_id
and rs.record_state !='03'
and rs.record_state !='04')
]]>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
i.vehicle_number = #vehicleNumber#
]]>
</isNotEmpty>
<![CDATA[
order by t.policy_pact_id desc) A
where rownum <= #pageNo#*#pageSize#)
where rn>(#pageNo#-1)*#pageSize#
]]>
</select>
<select id="Num.searchInsurance" parameterClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from t_insurance_info t
where t.policy_pact_id is null
and t.plan_code_type in ('104','105','108')
]]>
<isNotEmpty prepend="AND" property="planFlag">
<![CDATA[
t.plan_flag=#planFlag#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
EXISTS (SELECT 1
FROM t_customer tc
WHERE tc.party_id = t.party_id
AND tc.area_code = #areaCode#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
EXISTS (SELECT 1
FROM t_car_info c
WHERE c.car_id = t.car_id
AND c.vehicle_number = #vehicleNumber#)
]]>
</isNotEmpty>
</select>
<select id="List.searchInsurance" parameterClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO" resultClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO">
<![CDATA[
select * from
(select A.*,ROWNUM rn from
(select c.party_name partyName,
i.vehicle_number vehicleNumber,
t.quotation_number policyPactId,
t.party_id partyId,
t.car_id carId,
t.IS_TEL_PAY isTelPay,
t.PLAN_CODE_TYPE planCodeType,
t.DEPARTMENT_CODE deptCode
from t_insurance_info t, t_customer c, t_car_info i
where t.party_id = c.party_id
and t.car_id = i.car_id
and t.policy_pact_id is null
and t.plan_code_type in ('104','105','108')
]]>
<isNotEmpty prepend="AND" property="planFlag">
<![CDATA[
t.plan_flag=#planFlag#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="areaCode">
<![CDATA[
c.area_code = #areaCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
i.vehicle_number = #vehicleNumber#
]]>
</isNotEmpty>
<![CDATA[
order by t.policy_pact_id desc) A
where rownum <= #pageNo#*#pageSize#)
where rn>(#pageNo#-1)*#pageSize#
]]>
</select>
<update id="OutInsurance.updateExpeditedState" parameterClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO">
update t_out_insurance_info
set ISEXPEDITED=#isExpedited#,
updated_date=sysdate,
updated_by=#updatedBy:varchar#,
sql_id='OutInsurance.updateExpeditedState'
where policy_pact_id =#policyPactId#
</update>
<update id="Insurance.updateExpeditedState" parameterClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO">
update t_insurance_info set ISEXPEDITED=#isExpedited#
where QUOTATION_NUMBER =#quotationNumber#
</update>
<select id="sendEmail.search" parameterClass="com.tpaic.callCenter.dto.UserDTO" resultClass="com.tpaic.callCenter.dto.UserDTO">
<![CDATA[
SELECT tu.um_code umCode,tu.email email,tu.user_name userName,tu.user_id userId
FROM t_user tu
WHERE tu.email is not null
and tu.valid_flag=#validFlag#
and tu.is_agent=#isAgent#
and tu.agentdistrict_code=#agentdistrictCode#
]]>
</select>
<select id="list.searchRevisit" parameterClass="com.tpaic.callCenter.dto.RevisitDTO" resultClass="com.tpaic.callCenter.dto.RevisitDTO">
<![CDATA[
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
select t.TASK_ID taskId,
to_char(p.created_date, 'yyyymmddhh24miss') revisitNo,
c.party_name custName,
ci.vehicle_number vehicleNumber,
(select ui.user_name from t_user ui where t.updated_by=ui.um_code and ui.valid_flag='Y' and rownum=1) revisitPerson,
(select param_value from t_code_table d where d.param_id=t.revisit_result and d.code_type= 'policyRevisit' and d.valid_flag = 'Y' and rownum=1) revisitResult,
t.revisit_result revisitResultCode,
u.user_name userName,
(select tab.param_value from t_code_table tab where tab.param_id=u.group_by and tab.code_type='agentgroup' and tab.valid_flag='Y' and rownum=1) groupBy,
t.IS_FILED isField,
t.CONVERTTO convertTo
from t_revisit_task t,
t_Policy_Information p,
t_customer c,
t_car_info ci,
t_insurance_info i,
t_user u
where t.policy_id = p.policy_id
and t.party_id = c.party_id
and t.car_id = ci.car_id
and p.policy_pact_id=i.policy_pact_id
and i.created_by=u.um_code
and u.valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="revisitResultCode">
<![CDATA[
t.revisit_result=#revisitResultCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="revisitFlag">
<![CDATA[
t.revisit_flag=#revisitFlag#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="custName">
<![CDATA[
c.party_name=#custName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
ci.vehicle_number=#vehicleNumber#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="groupBy">
<![CDATA[
u.group_by=#groupBy#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="userName">
<![CDATA[
u.user_name=#userName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="convertTo">
<![CDATA[
t.CONVERTTO=#convertTo#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="createDateStart">
<![CDATA[
p.created_date>=to_date(#createDateStart#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="createDateEnd">
<![CDATA[
p.created_date<to_date(#createDateEnd#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
<![CDATA[
) A
WHERE ROWNUM <= #pageNo#*#pageSize#)
WHERE RN > (#pageNo#-1)*#pageSize#
]]>
</select>
<select id="num.searchRevisit" parameterClass="com.tpaic.callCenter.dto.RevisitDTO" resultClass="java.lang.String">
<![CDATA[
select count(1)
from t_revisit_task t,
t_Policy_Information p,
t_customer c,
t_car_info ci,
t_insurance_info i,
t_user u
where t.policy_id = p.policy_id
and t.party_id = c.party_id
and t.car_id = ci.car_id
and p.policy_pact_id=i.policy_pact_id
and i.created_by=u.um_code
and u.valid_flag='Y'
]]>
<isNotEmpty prepend="AND" property="revisitResultCode">
<![CDATA[
t.revisit_result=#revisitResultCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="revisitFlag">
<![CDATA[
t.revisit_flag=#revisitFlag#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="custName">
<![CDATA[
c.party_name=#custName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="vehicleNumber">
<![CDATA[
ci.vehicle_number=#vehicleNumber#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="groupBy">
<![CDATA[
u.group_by=#groupBy#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="userName">
<![CDATA[
u.user_name=#userName#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="convertTo">
<![CDATA[
t.CONVERTTO=#convertTo#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="createDateStart">
<![CDATA[
p.created_date>=to_date(#createDateStart#,'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="createDateEnd">
<![CDATA[
p.created_date<to_date(#createDateEnd#,'YYYY-MM-DD')+1
]]>
</isNotEmpty>
</select>
<update id="revisit.updateById" parameterClass="com.tpaic.callCenter.dto.RevisitDTO">
<![CDATA[
update t_revisit_task set updated_date = sysdate
]]>
<isNotEmpty prepend="," property="updatedBy">
<![CDATA[
updated_by = #updatedBy:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isField">
<![CDATA[
IS_FILED = #isField#
]]>
</isNotEmpty>
<![CDATA[
where TASK_ID=#taskId#
]]>
</update>
<select id="deliver.searchValidationFlag" parameterClass="string" resultClass="string">
select t.policy_pact_id polocyIdAndFlag from t_out_insurance_info t
where t.party_id = #value#
and t.insurance_state='11'
and t.check_car_flag='1'
</select>
<select id="deliver.checkInsuranceIsPass" resultClass="com.tpaic.callCenter.dto.OutInsuranceInfoDTO"
parameterClass="com.tpaic.callCenter.dto.DeliverAppointmentDTO">
select oii.insurance_state insuranceStatus,
oii.policy_pact_id policyPactId,
oii.paymentmode payMode,
oii.department_code departmentCode,
oii.policy_id policyId,
oii.car_id carId,
oii.ISCHECKVEHICLE isCheckVehicle
from t_out_insurance_info oii
where oii.party_id = #partyId#
and oii.insurance_state in (2, 9)
and oii.updated_date >= sysdate - 30
and oii.bussiness_type = '8'
and not exists (select 1 from t_deliver_policy dp where dp.policy_number=oii.policy_pact_id)
</select>
<select id="deliver.checkDeliverinfoIsGenerated" parameterClass="java.lang.String"
resultClass="int">
<![CDATA[
select count(1) cou
from t_deliver_policy dp
where dp.policy_number=#value#
]]>
</select>
<!-- ,退单 -->
<select id="deliver.searchSaleTask" parameterClass="com.tpaic.callCenter.dto.CustomerSaleMainTaskDTO" resultClass="java.lang.String">
<![CDATA[
SELECT st.task_id
FROM t_customer_salemain_task st
WHERE st.um_code = (select tu.um_code
from t_user tu,t_customer tc
where tu.user_id = tc.current_user_id
and tc.party_id=#partyId#
and rownum = 1)
AND st.task_type = '4'
AND rownum = 1
]]>
</select>
<select id="deliver.searchUserInfo" parameterClass="java.util.Map" resultClass="com.tpaic.callCenter.dto.UserDTO">
<![CDATA[
SELECT
t.user_id userId,
t.um_code umCode,
t.user_name userName,
t.area_code areaCode,
t.group_by groupBy,
t.agentdistrict_Code agentdistrictCode
FROM t_user t ,t_customer tc
WHERE t.user_id=tc.current_user_id
AND tc.party_id=#partyId#
AND tc.priority = 1
AND tc.valid_flag = 'Y'
AND rownum=1
]]>
</select>
<select id="Deliver.searchSleepDeliverNum" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="int">
<![CDATA[
select count(*)
from T_DELIVER_INFOMATION t,
t_deliver_policy dpo,
t_out_insurance_info oi
where t.deliver_id = dpo.deliver_id(+)
and oi.policy_pact_id(+) = dpo.policy_number
]]>
<isNotEmpty prepend="AND" property="deliverState">
<![CDATA[
t.deliver_state_code =#deliverState#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.deliver_id =#deliverID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyNo">
<![CDATA[
dpo.policy_number = #policyNo#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applicationNo">
<![CDATA[
oi.policy_pact_id = #applicationNo#
]]>
</isNotEmpty>
</select>
<select id="Deliver.searchSleepDeliverInfoList" parameterClass="com.tpaic.callCenter.dto.DeliverInfoDTO" resultClass="com.tpaic.callCenter.dto.DeliverInfoDTO">
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
select
t.deliver_id deliverID,
(select param_value from t_code_table i where t.deliver_type = i.param_id
and i.code_type = 'deliverType' ) deliverType,
(select cd3.param_value from t_sys_param cd3 where cd3.PARAM_TYPE = 'deliverState' and cd3.PARAM_ID = t.deliver_state_code) deliverState,
oi.policy_id policyNo,
oi.policy_pact_id applicationNo,
(select cd.param_value
from t_code_table cd
where t.area_code = cd.param_id
and cd.code_type = 'area') areaName,
(select m.party_name from t_customer m where party_id = t.party_id) partyName,
t.deliver_address deliverAddress
from t_deliver_infomation t,
t_deliver_policy dpo,
t_out_insurance_info oi
where t.deliver_id = dpo.deliver_id(+)
and oi.policy_pact_id(+) = dpo.policy_number
<isNotEmpty prepend="AND" property="deliverState">
<![CDATA[
t.deliver_state_code =#deliverState#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverID">
<![CDATA[
t.deliver_id =#deliverID#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="policyNo">
<![CDATA[
oi.policy_id = #policyNo#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="applicationNo">
<![CDATA[
oi.policy_pact_id = #applicationNo#
]]>
</isNotEmpty>
) A
<![CDATA[
WHERE rownum<=100)
WHERE RN <= #pageNo#*#pageSize# and RN > (#pageNo#-1)*#pageSize#
]]>
</select>
<update id="Deliver.updateSleepInfoListBatch" parameterClass="java.util.List">
update t_deliver_infomation di set di.deliver_state_code = '02' where di.deliver_id in
<iterate open="(" close=")" conjunction=",">
#deliverIds[]#
</iterate>
</update>
<select id="Deliver.searchDeliverStateList" parameterClass="java.lang.String" resultClass="com.tpaic.callCenter.dto.SysParamDTO">
<![CDATA[
select
sp.param_id paramId,
sp.param_value paramValue
from t_sys_param sp where sp.param_type = 'deliverState'
]]>
</select>
<!-- 配送追踪表 -->
<select id="Deliver.searchDeliverTrackList" parameterClass="com.tpaic.callCenter.dto.DeliverTrackDTO" resultClass="com.tpaic.callCenter.dto.DeliverTrackDTO">
<![CDATA[
select
to_char(di.deliver_time, 'yyyy-mm-dd') deliverTime,
oii.insurance_date insuranceDate,
u.um_code umCode,
u.user_name userName,
oii.policy_pact_id policyPactId,
substr(oii.policy_pact_id,5,3) insuranceType,
pi.policy_id policyId,
to_char(pi.insurance_begin_time, 'yyyy-mm-dd hh24:mi:ss') insuranceBeginTime,
to_char(oii.insurance_begin_time, 'yyyy-mm-dd hh24:mi:ss') insuranceBegin,
to_char(pi.created_date, 'yyyy-mm-dd hh24:mi:ss') appointDate,
pi.insurance_premium insurancePremium,
oii.TOTALAGREEPREMIUM-oii.VEHICLE_TAX_AMOUNT ActualPremium,
decode(oii.insured, null,
(select c.party_name
from t_customer c
where c.party_id = oii.party_id),oii.insured) insured,
(select t.param_value
from t_code_table t
where t.code_type = 'deliverMode'
and t.valid_flag = 'Y'
and t.param_id=di.deliver_mode) deliverMode,
(select t.param_id
from t_sys_param t
where valid_flag = 'Y'
and PARAM_TYPE = 'policyState'
and t.param_id=pi.policy_state) policyState,
(select t.param_value
from t_sys_param t
where valid_flag = 'Y'
and PARAM_TYPE = 'outPolicyState'
and t.param_id=oii.insurance_state) outPolicyState,
(select PARAM_VALUE paramValue
from T_CODE_TABLE t
where t.code_type = 'agentdistrict'
and VALID_FLAG = 'Y'
and t.param_id = u.agentdistrict_code) agentdistrictVale,
(select PARAM_VALUE paramValue
from T_CODE_TABLE t
where t.code_type = 'agentgroup'
and VALID_FLAG = 'Y'
and t.param_id = u.group_by) groupBy,
(select PARAM_VALUE paramValue
from T_CODE_TABLE t
where t.code_type = 'agentdepart'
and VALID_FLAG = 'Y'
and t.param_id = u.agent_depart) department,
(select PARAM_VALUE paramValue
from T_CODE_TABLE t
where t.code_type = 'agentcenter'
and VALID_FLAG = 'Y'
and t.param_id = u.center_flag) centerflag,
(select ci.vehicle_number
from t_car_info ci
where ci.car_id = dp.car_id) vehicleNumber,
(select dd.department_abbr_name
from t_department_define dd
where dd.internal_department_code=oii.department_code) areaValue,
(select ccc.VALUE_NAME
from t_code_city ccc
where ccc.value_code = di.PROVINCE_CODE
and ccc.collection_code = 'SFBM0000'
and ccc.VALID_FLAG = 'Y'
and rownum = 1) ||
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = di.CITY_CODE
and cc.collection_code = 'CSBM0000'
and cc.VALID_FLAG = 'Y'
and rownum = 1) ||
(select cc.VALUE_NAME
from t_code_city cc
where cc.value_code = di.TOWN_CODE
and cc.collection_code = 'QYBM0000'
and cc.VALID_FLAG = 'Y'
and rownum = 1) || di.deliver_address deliverAddress
from t_deliver_infomation di,
t_policy_information pi,
t_deliver_policy dp,
t_out_insurance_info oii,
t_user u
where di.deliver_id = dp.deliver_id
and pi.policy_pact_id(+) = oii.policy_pact_id
and dp.policy_number = oii.policy_pact_id
and u.um_code=decode(oii.business_assistant_code,
null,
oii.created_by,
oii.business_assistant_code)
and u.valid_flag = 'Y' and rownum<1001
]]>
<isNotEmpty prepend="AND" property="groupBy">
<![CDATA[
u.group_by = trim(#groupBy:varchar#)
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="agentdistrictCode">
<![CDATA[
u.agentdistrict_code = #agentdistrictCode:varchar#
]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="umCode">
<![CDATA[
u.um_code = #umCode#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insuranceBeginTime">
<![CDATA[
to_date(oii.insurance_date, 'YYYY-MM-DD') >= to_date(#insuranceBeginTime#, 'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="insuranceDate">
<![CDATA[
to_date(oii.insurance_date, 'YYYY-MM-DD') <= to_date(#insuranceDate#, 'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverBeginTime">
<![CDATA[
di.deliver_time >= to_date(#deliverBeginTime#, 'YYYY-MM-DD')
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="deliverTime">
<![CDATA[
di.deliver_time <= to_date(#deliverTime#, 'YYYY-MM-DD')
]]>
</isNotEmpty>
order by oii.insurance_date desc
</select>
<!-- 根据PartyId获取网销保单配送时间 -->
<select id="Deliver.searchDeliverDateForNetSale" parameterClass="java.lang.String" resultClass="java.lang.String">
<![CDATA[
SELECT TO_CHAR(TI.INSURANCE_BEGIN_TIME+7,'yyyy-mm-dd') deliverRuleLastDate FROM T_OUT_INSURANCE_INFO TI WHERE TI.POLICY_PACT_ID=
(SELECT POLICY_PACT_ID FROM (SELECT T.POLICY_PACT_ID FROM T_OUT_INSURANCE_INFO T
WHERE T.INSURANCE_STATE NOT IN ('-999', '101', '7', '99', '100')
AND T.DELIVER_STATE NOT IN ('01','02','03')
AND T.PARTY_ID=#partyId:VARCHAR#
AND T.BUSSINESS_TYPE='8' ORDER BY T.INSURANCE_BEGIN_TIME ASC) WHERE ROWNUM=1)
]]>
</select>
<select id="deliver.searchDeliverManagerMailList" parameterClass="com.tpaic.callCenter.dto.UserDTO" resultClass="com.tpaic.callCenter.dto.UserDTO">
<![CDATA[
SELECT *
FROM (SELECT ROWNUM RN,
DRA.USER_ID AS USERID,
DRA.AREA_CODE AS AREACODE,
DRA.UPDATED_BY UPDATEDBY,
(SELECT USER_NAME FROM T_USER
WHERE UM_CODE = DRA.UPDATED_BY) USERNAME,
CT.AREA_NAME AREANAME,
(select mail1 from t_deliver_owner_set where dra.user_id = user_id and dra.area_code = area_code) mail1,
(select mail2 from t_deliver_owner_set where dra.user_id = user_id and dra.area_code = area_code) mail2,
(select decode(concat(mail1,mail2),NULL,0,1) FROM T_DELIVER_OWNER_SET where dra.user_id = user_id and dra.area_code = area_code ) flag
FROM T_DELIVER_ROLE_AREA DRA, AREADEPARTMENT CT
WHERE 1 = 1
AND DRA.VALID_FLAG = 'Y'
AND DRA.AREA_CODE = CT.AREA_CODE
AND USER_ID = #userId#
AND ROWNUM <= #pageSize# * #pageNo#) T
WHERE T.rn > #pageSize# * (#pageNo#-1)
]]>
</select>
<select id="deliver.searchDeliverManagerMailTotalAmount" parameterClass="com.tpaic.callCenter.dto.UserDTO" resultClass="java.lang.String">
<![CDATA[
SELECT ceil(count(DRA.USER_ID)/#pageSize#) pageTotal
FROM T_DELIVER_ROLE_AREA DRA, AREADEPARTMENT CT
WHERE 1 = 1
AND DRA.VALID_FLAG = 'Y'
AND DRA.AREA_CODE = CT.AREA_CODE
AND USER_ID = #userId#
]]>
</select>
<!-- 配送负责人邮件管理 -->
<update id="deliver.deliverOwnerSetDTOListForUpdate" parameterClass="java.util.Map">
<isEmpty property="deliverOwnerSetDTOListForUpdate">select 1 from dual</isEmpty>
<isNotEmpty property="deliverOwnerSetDTOListForUpdate">
begin
<iterate conjunction=";" property="deliverOwnerSetDTOListForUpdate">
update t_deliver_owner_set set
updated_date = to_date(#deliverOwnerSetDTOListForUpdate[].updatedDate#,'YYYY-MM-DD HH24:MI:SS'),
updated_by = #deliverOwnerSetDTOListForUpdate[].updatedBy#,
mail1 = #deliverOwnerSetDTOListForUpdate[].mail1#,
mail2 = #deliverOwnerSetDTOListForUpdate[].mail2#
where
user_id = #userId#
and
area_code = #deliverOwnerSetDTOListForUpdate[].areaCode#
</iterate>
;end;
</isNotEmpty>
</update>
<insert id="deliver.deliverOwnerSetDTOListForAdd" parameterClass="java.util.Map">
<isEmpty property="deliverOwnerSetDTOListForAdd">select 1 from dual</isEmpty>
<isNotEmpty property="deliverOwnerSetDTOListForAdd">
begin
<iterate conjunction=";" property="deliverOwnerSetDTOListForAdd">
insert into t_deliver_owner_set(id,user_id,area_code,valid_flag,created_date,created_by,mail1,mail2)
values
(seq_deliver_owner_set_id.nextval,
#userId#,
#deliverOwnerSetDTOListForAdd[].areaCode#,
'1',
to_date(#deliverOwnerSetDTOListForAdd[].createdDate#,'YYYY-MM-DD HH24:MI:SS'),
#deliverOwnerSetDTOListForAdd[].createdBy#,
#deliverOwnerSetDTOListForAdd[].mail1#,
#deliverOwnerSetDTOListForAdd[].mail2#)
</iterate>
;end;
</isNotEmpty>
</insert>
</sqlMap>
1310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
