<sqlMap namespace="egis.scms.order">
<typeAlias alias="ScmsOrderDTO" type="com.paic.egis.scms.order.dto.ScmsOrderDTO"></typeAlias>
<typeAlias alias="ScmsOrderInsureDTO" type="com.paic.egis.scms.order.dto.ScmsOrderInsureDTO"></typeAlias>
<!-- 生成序列号 action中通过这一个select查询到一个序列号，此序列号再从java中传入sql中-->
<select id="generateSCMSOrderInsureId" resultClass="string">
select nextval('scms_order_insure_seq')
</select>
<sql id="withFlag">
<isEqual prepend="and" property="flag" compareValue="2">
<![CDATA[
o.order_status in ('03','07')
]]>
</isEqual>
<isEqual prepend="and" property="flag" compareValue="3">
<![CDATA[
o.order_status in ('02','04','05','06','08')
]]>
</isEqual>
<isEqual prepend="and" property="flag" compareValue="4">
<![CDATA[
o.order_status in ('09','10','15')
]]>
</isEqual>
</sql>
<!-- 订单总数查询 -->
<select id="getCountByStatus" parameterClass="java.util.HashMap" resultClass="java.lang.Integer">
select count(0) as "orderCount"
from scms_order o
where o.user_id=#userId#
and o.pay_channel = #payChannel#
and o.order_client_type = #orderClientType#
<include refid="withFlag" />
</select>
<!-- 创建订单 -->
<insert id ="createScmsOrder" parameterClass="java.util.HashMap">
INSERT INTO scms_order(
id_scms_order,
order_date,
order_status,
is_upload,
order_client_type,
order_channel,
order_amount,
user_id,
business_type,
pay_channel,
pay_medical_no,
bank_name,
bank_no,
is_change_pay,
pay_account_no
)
VALUES (
'OD' || lpad(#idScmsOrder#, 14, '0'),
to_timestamp(#orderDate#,'yyyy-mm-dd hh24:mi:ss'),
#orderStatus#,
'01',
#orderClientType#,
#orderChannel#,
#orderAmount#,
#userId#,
#businessType#,
#payChannel#,
#payMedicalNo#,
#bankName#,
#bankNo#,
#isChangePay#,
#payAccountNo#
);
</insert>
<!-- 查询订单列表-->
<select id="orderList" resultClass="java.util.HashMap" parameterClass="java.util.HashMap">
select
o.id_scms_order as "idScmsOrder",
o.order_date as "orderDate",
o.order_status as "orderStatus",
op.id_scms_product as "idScmsProduct",
o.order_status as "orderStatus"
from scms_order o,
scms_order_product op
where o.id_scms_order=op.id_scms_order
and o.user_id=#userId#
and o.pay_channel = #payChannel#
and o.order_client_type = #orderClientType#
and o.order_status in ('02','03','04','05','06','07','08','09','10')
</select>
<!-- 分页查询订单列表-->
<select id="orderListByFlagPage" resultClass="java.util.HashMap" parameterClass="java.util.HashMap">
select
o.id_scms_order as "idScmsOrder",
o.order_date as "orderDate",
o.order_status as "orderStatus",
op.id_scms_product as "idScmsProduct",
o.order_status as "orderStatus"
from scms_order o,
scms_order_product op
where o.id_scms_order=op.id_scms_order
and o.user_id=#userId#
and o.pay_channel = #payChannel#
and o.order_client_type = #orderClientType#
<include refid="withFlag" />
order by o.id_scms_order desc
<isNotEmpty property="offset">
limit #pageSize# offset #offset#
</isNotEmpty>
</select>
<!-- 新增投保-被保人信息 -->
<insert id ="insertOrderApplicInsur" parameterClass="java.util.HashMap">
<selectKey resultClass="java.lang.String" keyProperty="idOrderIns">
select sys_guid()--生成序列号。也可以直接在列的赋值处直接写sys_guid()
</selectKey>
INSERT INTO scms_application_insured
(id_order_ins,
id_order_policy,
ins_user_name,
ins_gender,
ins_birth_date,
ins_id_type,
ins_id_no,
br_no,
applicant_relation
)
VALUES (
#idOrderIns#,// 此处可以直接写为sys_guid();不需要上面那一步
#idOrderPolicy#,
#insUserName#,
#insGender#,
to_date(#insBirthDate#,'yyyy-mm-dd'),
#insIdType#,
#insIdNo#,
#brNo#,
#applicantRelation#
)
</insert>
<update id="updateOrderInfo" parameterClass="java.util.HashMap">
UPDATE scms_order
<dynamic prepend="set">
<isNotEmpty prepend="," property="orderStatus">
order_status=#orderStatus#
</isNotEmpty>
<isNotEmpty prepend="," property="failReason">
fail_reason=#failReason#
</isNotEmpty>
<isNotEmpty prepend="," property="failCode">
fail_code=#failCode#
</isNotEmpty>
<isNotEmpty prepend="," property="transactionId">
transaction_id=#transactionId#
</isNotEmpty>
<isNotEmpty prepend="," property="transactionDate">
transaction_date=to_timestamp(#transactionDate#,'YYYY-MM-DD HH24:MI:SS')
</isNotEmpty>
<isNotEmpty prepend="," property="payDate">
pay_date=to_timestamp(#payDate#,'YYYY-MM-DD HH24:MI:SS')
</isNotEmpty>
<isNotEmpty prepend="," property="payChannel">
pay_channel=#payChannel#
</isNotEmpty>
<isNotEmpty prepend="," property="payAccountNo">
pay_account_no=#payAccountNo#
</isNotEmpty>
<isNotEmpty prepend="," property="bankName">
bank_name=#bankName#
</isNotEmpty>
<isNotEmpty prepend="," property="bankNo">
bank_no=#bankNo#
</isNotEmpty>
<isNotEmpty prepend="," property="thirdOrderId">
third_order_id=#thirdOrderId#
</isNotEmpty>
<isNotEmpty prepend="," property="reundwrtFlag">
reundwrt_flag=#reundwrtFlag#
</isNotEmpty>
<isNotEmpty prepend="," property="transactionThirdId">
transaction_third_id=#transactionThirdId#
</isNotEmpty>
<isNotEmpty prepend="," property="isUpload">
is_upload=#isUpload#
</isNotEmpty>
<isNotEmpty prepend="," property="orderAmount">
order_amount=#orderAmount#
</isNotEmpty>
</dynamic>
where id_scms_order=#idScmsOrder#
</update>
<update id="updateOrderInfoByThirdId" parameterClass="java.util.HashMap">
UPDATE scms_order
<dynamic prepend="set">
<isNotEmpty prepend="," property="orderStatus">
order_status=#orderStatus#
</isNotEmpty>
<isNotEmpty prepend="," property="failReason">
fail_reason=#failReason#
</isNotEmpty>
<isNotEmpty prepend="," property="failCode">
fail_code=#failCode#
</isNotEmpty>
<isNotEmpty prepend="," property="transactionId">
transaction_id=#transactionId#
</isNotEmpty>
<isNotEmpty prepend="," property="transactionDate">
transaction_date=to_timestamp(#transactionDate#,'YYYY-MM-DD HH24:MI:SS')
</isNotEmpty>
<isNotEmpty prepend="," property="payDate">
pay_date=to_timestamp(#payDate#,'YYYY-MM-DD HH24:MI:SS')
</isNotEmpty>
<isNotEmpty prepend="," property="payChannel">
pay_channel=#payChannel#
</isNotEmpty>
<isNotEmpty prepend="," property="payAccountNo">
pay_account_no=#payAccountNo#
</isNotEmpty>
<isNotEmpty prepend="," property="bankName">
bank_name=#bankName#
</isNotEmpty>
<isNotEmpty prepend="," property="bankNo">
bank_no=#bankNo#
</isNotEmpty>
<isNotEmpty prepend="," property="thirdOrderId">
third_order_id=#thirdOrderId#
</isNotEmpty>
<isNotEmpty prepend="," property="reundwrtFlag">
reundwrt_flag=#reundwrtFlag#
</isNotEmpty>
</dynamic>
where transaction_third_id=#transactionThirdId#
</update>
<!-- 查询订单列表，并且关联用户信息 (总记录数) -->
<select id="count_orderListForPay" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
select count(0) from (
<include refid="sql_orderListForPay" />
) as t
</select>
<!-- 查询订单列表，并且关联用户信息 (分页查询) -->
<select id="orderListForPay" resultClass="com.paic.egis.scms.pay.dto.PayOrderDTO"
parameterClass="java.util.HashMap">
<include refid="sql_orderListForPay" />
order by idScmsOrder desc
<isNotEmpty property="offset">
limit #pageSize# offset #offset#
</isNotEmpty>
</select>
<!-- 生成订单产品号 -->
<select id="generateSCMSOrderProductId" resultClass="string">
select nextval('scms_order_product_seq')
</select>
<select id="queryOrderInsureList" resultClass="com.paic.egis.scms.order.dto.ScmsOrderInsureDTO"
parameterClass="java.util.HashMap">
SELECT
i.id_order_policy AS idOrderPolicy,
i.appno AS appNo,
i.polno AS polNo,
i.certno AS certNo,
i.issure_date AS issureDate,
i.underwt_date AS underwtDate
FROM
scms_order_insure i
<dynamic prepend="where">
<isNotEmpty prepend="and" property="idOrderProduct">
i.id_order_product = #idOrderProduct#
</isNotEmpty>
<isNotEmpty prepend="and" property="idOrderPolicy">
i.id_order_policy = #idOrderPolicy#
</isNotEmpty>
</dynamic>
</select>
<update id ="updateScmsOrderProduct" parameterClass="java.util.HashMap">
update scms_order_product
<dynamic prepend="set">
<isNotEmpty prepend="," property="idScmsProduct">
id_scms_product=#idScmsProduct#
</isNotEmpty>
<isNotEmpty prepend="," property="idInsurPrdCombined">
id_insur_prd_combined=#idInsurPrdCombined#
</isNotEmpty>
<isNotEmpty prepend="," property="effDate">
eff_Date=to_timestamp(#effDate#,'yyyy-mm-dd hh24:mi:ss')
</isNotEmpty>
<isNotEmpty prepend="," property="matuDate">
matu_date=to_timestamp(#matuDate#,'yyyy-mm-dd hh24:mi:ss')
</isNotEmpty>
<isNotEmpty prepend="," property="empNo">
emp_no=#empNo#
</isNotEmpty>
<isNotEmpty prepend="," property="participatorCode">
participator_Code=#participatorCode#
</isNotEmpty>
<isNotEmpty prepend="," property="insurerName">
insurer_Name=#insurerName#
</isNotEmpty>
<isNotEmpty prepend="," property="insurerGender">
insurer_Gender=#insurerGender#
</isNotEmpty>
<isNotEmpty prepend="," property="insurerBirthDate">
insurer_birth_date=to_date(#insurerBirthDate#,'yyyy-mm-dd')
</isNotEmpty>
<isNotEmpty prepend="," property="insurerIdType">
insurer_id_type=#insurerIdType#
</isNotEmpty>
<isNotEmpty prepend="," property="insurerIdNo">
insurer_id_no=#insurerIdNo#
</isNotEmpty>
<isNotEmpty prepend="," property="sinsurPeriodType">
sinsur_period_type=#sinsurPeriodType#
</isNotEmpty>
<isNotEmpty prepend="," property="insurPeriod">
insur_period=#insurPeriod#
</isNotEmpty>
<isNotEmpty prepend="," property="memberDiscount">
member_discount=#memberDiscount#
</isNotEmpty>
<isNotEmpty prepend="," property="idInsurPrdItem">
id_insur_prd_item=#idInsurPrdItem#
</isNotEmpty>
<isNotEmpty prepend="," property="idInsurPrdItem">
base_price=#basePrice#
</isNotEmpty>
<isNotEmpty prepend="," property="coverageAmount">
coverage_amount=#coverageAmount#
</isNotEmpty>
<isNotEmpty prepend="," property="combinedPlanCode">
combined_plan_code=#combinedPlanCode#
</isNotEmpty>
</dynamic>
<dynamic prepend="where">
<isNotEmpty prepend="and" property="idOrderProduct">
id_order_product=#idOrderProduct#
</isNotEmpty>
<isNotEmpty prepend="and" property="idScmsOrder">
id_scms_order= #idScmsOrder#
</isNotEmpty>
</dynamic>
</update>
<select id="queryItemByCombinedID" resultClass="java.util.HashMap" parameterClass="java.util.List">
SELECT
ID_INSUR_PRD_ITEM "insurPrdItemId",
ID_INSUR_PRD_COMBINED "insurPrdCombinedId",
ITEM_NAME "itemName",
ITEM_DESC "itemDesc",
PLAN_CODE "planCode",
UNDWRT_RANGE "undwrtRange"
FROM
SCMS_INSUR_PRODUCT_ITEM
WHERE
ID_INSUR_PRD_COMBINED in
<iterate open="(" close=")" conjunction=","> #[]# </iterate>
</select>
以上不需要把list的name值传到sql中，跟=#name值#是不一样的。这里不需要写
// List<String> combinedParamList = new ArrayList<String>();
// for(Map<String, Object> map : combineList){
// combinedParamList.add((String)map.get(combinedIdCloumn));
// }
// List<Map<String, Object>> itemList = insuranceProductDao
// .queryItemByCombinedList(combinedParamList);