ibatis标签之iterate

工作中所写的关于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,