oracle(天猫处方药留言sql)

" ?>
.dtd" >
<sqlMap namespace="TmallTcMessage">
<typeAlias alias="TmallTcMessage"
type="com.founder.ec.product.model.TmallTcMessage" />
<typeAlias alias="OrderTcMessage" type="com.founder.ec.order.model.OrderTcMessage" />
<typeAlias alias="ecelMessage"
type="com.founder.ec.product.model.GoodsEvaluation" />
<typeAlias alias="Goods" type="com.founder.ec.product.model.Goods" />
<resultMap id="TmallTcMessageMap" class="TmallTcMessage">
<result column="TMALL_TC_MSG_ID" property="tmallTcMsgId" />
<result column="TMALL_ORDER_ID" property="tmallOrderId" />
<result column="MEMBER_NAME" property="memberName" />
<result column="ALIPAY_USER_NAME" property="alipayUserName" />
<result column="ALIPAY_PAID_FEE" property="alipayPaidFee" />
<result column="MESSAGE" property="message" />
<result column="MESSAGE_SOURCE" property="messageSource" />
<result column="RECEIVE_USER" property="receiveUser" />
<result column="RECEIVE_FULL_ADDRESS" property="receiveFullAddress" />
<result column="RECEIVE_TEL" property="receiveTel" />
<result column="RECEIVE_MOBILE" property="receiveMobile" />
<result column="ORDER_CREATE_TIME" property="orderCreateTime" />
<result column="GOODS_NO" property="goodsNo" />
<result column="GOODS_TOPIC" property="goodsTopic" />
<result column="IS_CALLBACK" property="isCallback" />
<result column="IS_BUY" property="isBuy" />
<result column="NOT_BUY_REASON" property="notBuyReason" />
<result column="NOT_BUY_REASON_NAME" property="notBuyReasonName" />
<result column="CALLBACK_TIME" property="callbackTime" />
<result column="COMMIT_USERNAME" property="commitUserName" />
<result column="CALLBACK_USERNAME" property="callbackUserName" />
<result column="SERVICE_NAME" property="serviceName" />
<result column="DISTRIBUTION_TYPE" property="distributionType" />
<result column="USER_TYPE" property="userType" />
<result column="MESSAGE_TIME" property="messageTime" />
<result column="IMPORT_ID" property="importId" />
<result column="GOODS_ID" property="goodsId" />
<result column="PRODUCT_ID" property="productId" />
<result column="CUSTOMER_NATURE" property="customerNature" />
<result column="HAS_LOG" property="hasLog" />
<result column="ASSIGN_NUM" property="assignNum" />
<result column="NOT_BUY_REASON_NOTE" property="notBuyReasonNote" />
<result column="ASSIGN_ID" property="assignId" />
<result column="ALIPAY_ORDER_FEE" property="alipayOrderFee" />
<result column="ALIPAY_DELIVERY_FEE" property="alipayDeliveryFee" />
<result column="ALIPAY_TOTAL_FEE" property="alipayTotalFee" />
<result column="NOTE" property="note" />
<result column="ORDER_ID" property="orderId" />
</resultMap>

<resultMap id="TmallMessageExportResultMap" class="TmallTcMessage">
<result column="TMALL_ORDER_ID" property="tmallOrderId" />
<result column="MEMBER_NAME" property="memberName" />
<result column="ALIPAY_USER_NAME" property="alipayUserName" />
<result column="ALIPAY_PAID_FEE" property="alipayPaidFee" />
<result column="MESSAGE" property="message" />
<result column="MESSAGE_SOURCE" property="messageSource" />
<result column="MESSAGE_SOURCE_NAME" property="messageSourceName" />
<result column="RECEIVE_USER" property="receiveUser" />
<result column="RECEIVE_FULL_ADDRESS" property="receiveFullAddress" />
<result column="RECEIVE_TEL" property="receiveTel" />
<result column="ORDER_CREATE_TIME" property="orderCreateTime" />
<result column="GOODS_NO" property="goodsNo" />
<result column="GOODS_TOPIC" property="goodsTopic" />
<result column="GOODS_COUNT" property="goodsCount" />
<result column="IS_CALLBACK" property="isCallback" />
<result column="IS_CALLBACK_NAME" property="isCallbackName" />
<result column="IS_BUY" property="isBuy" />
<result column="IS_BUY_NAME" property="isBuyName" />
<result column="NOT_BUY_REASON" property="notBuyReason" />
<result column="NOT_BUY_REASON_NAME" property="notBuyReasonName" />
<result column="CALLBACK_TIME" property="callbackTime" />
<result column="COMMIT_USERID" property="commitUserid" />
<result column="CALLBACK_USERID" property="callbackUserid" />
<result column="USER_TYPE" property="userType" />
<result column="USER_TYPE_NAME" property="userTypeName" />
<result column="DISTRIBUTION_TYPE" property="distributionType" />
<result column="DISTRIBUTION_TYPE_NAME" property="distributionTypeName" />
<result column="SERVICE_NAME" property="serviceName" />
<result column="CUSTOMER_NATURE" property="customerNature" />
<result column="CUSTOMER_NATURE_NAME" property="customerNatureName" />
<result column="NOT_BUY_REASON_NOTE" property="notBuyReasonNote" />
<result column="ASSIGN_NUM" property="assignNum" />
<result column="ASSIGN_ID" property="assignId" />
<result column="ALIPAY_ORDER_FEE" property="alipayOrderFee" />
<result column="ALIPAY_DELIVERY_FEE" property="alipayDeliveryFee" />
<result column="ALIPAY_TOTAL_FEE" property="alipayTotalFee" />
<result column="NOTE" property="note" />
<result column="ORDER_ID" property="orderId" />
</resultMap>

<!-- 商品相关 -->
<resultMap class="Goods" id="goodsTcQueryRes">
<result column="GOODS_ID" property="goodsId" />
<result column="PRODUCT_ID" property="goodsId" />
<result column="GOODS_NO" property="goodsNo" />
<result column="GOODS_NAME" property="goodsName" />
<result column="MARKET_PRICE" property="marketPrice" />
<result column="EC_PRICE" property="ecPrice" />
<result column="AVAILABLE_STOCK" property="availableStock" />
<result column="UNITNAME" property="unitName" />
</resultMap>
<resultMap class="TmallTcMessage" id="tcMessageResNew">
<result column="GOODS_NO" property="goodsNo" />
</resultMap>

<resultMap id="getMessageGainMap" class="TmallTcMessage">
<result column="TMALL_TC_MSG_ID" property="tmallTcMsgId" />
</resultMap>

<sql id="be_fy">
select * from (select row_.*, rownum rownum_ from (
</sql>
<sql id="ed_fy">
) row_
)
<isNotEmpty prepend="and" property="endRow">
<![CDATA[ rownum_ <= #endRow# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="startRow">
<![CDATA[ rownum_ >= #startRow# ]]>
</isNotEmpty>
</sql>
<!-- 动态的拼接查询条件 -->
<sql id="queryTmallTcMessageParam">
<isEqual property="isRegister" compareValue="Y">
and exists (select
 from member m where m.is_delete='N' and
m.mobile_state='Y' and
receive_mobile = m.mobile)
</isEqual>
<isEqual property="isRegister" compareValue="N">
and not exists
( from member m where m.is_delete='N' and
m.mobile_state='Y'
and receive_mobile = m.mobile)
</isEqual>
<isNotEmpty prepend="and" property="assignId">
<![CDATA[ mai.ASSIGN_ID=#assignId# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="receiveUser">
<![CDATA[ mai.RECEIVE_USER=#receiveUser# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="receiveMobile">
<![CDATA[ mai.RECEIVE_MOBILE=#receiveMobile# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="isCallback">
<![CDATA[ mai.IS_CALLBACK=#isCallback# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="isBuy">
<![CDATA[ mai.IS_BUY=#isBuy# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="goodsNo">
<![CDATA[ mai.GOODS_NO=#goodsNo# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="receiveFullAddress">
<![CDATA[ mai.RECEIVE_FULL_ADDRESS like '%$receiveFullAddress$%' ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="bgMessageTime">
<![CDATA[ mai.MESSAGE_TIME>=#bgMessageTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="endMessageTime">
<![CDATA[ mai.MESSAGE_TIME<=#endMessageTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="callbackUserName">
<![CDATA[ mai.CALLBACK_USERNAME=#callbackUserName# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="bgCallBackTime">
<![CDATA[ mai.CALLBACK_TIME>=#bgCallBackTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="endCallBackTime">
<![CDATA[ mai.CALLBACK_TIME<=#endCallBackTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="userType">
<![CDATA[ mai.USER_TYPE=#userType# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="commitUserName">
<![CDATA[ mai.COMMIT_USERNAME=#commitUserName# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="messageSource">
<![CDATA[ mai.MESSAGE_SOURCE=#messageSource# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="notBuyReason">
<![CDATA[ mai.NOT_BUY_REASON=#notBuyReason# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="codeTypeNo">
<![CDATA[ mai.CODE_TYPE_NO=#codeTypeNo# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="distributionType">
<![CDATA[ mai.DISTRIBUTION_TYPE=#distributionType# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="customerNature">
<![CDATA[ mai.CUSTOMER_NATURE=#customerNature# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="serviceName">
<![CDATA[ mai.SERVICE_NAME=#serviceName# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="assignNum">
<![CDATA[ mai.ASSIGN_NUM=#assignNum# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="importId">
<![CDATA[ mai.IMPORT_ID=#importId# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="orderId">
<![CDATA[ mai.ORDER_ID like '%$orderId$%' ]]>
</isNotEmpty>
</sql>
<select id="getTmallTcMessagePage" resultMap="TmallTcMessageMap"
parameterClass="TmallTcMessage">
<include refid="be_fy" />
select *
from (select ttm.ORDER_ID,
ttm.TMALL_TC_MSG_ID,
ttm.TMALL_ORDER_ID,
ttm.MEMBER_NAME,
ttm.ALIPAY_USER_NAME,
ttm.ALIPAY_PAID_FEE,
ttm.MESSAGE,
MESSAGE_SOURCE,
USER_TYPE,
MESSAGE_TIME,
DISTRIBUTION_TYPE,
CUSTOMER_NATURE,
RECEIVE_USER,
RECEIVE_FULL_ADDRESS,
RECEIVE_TEL,
RECEIVE_MOBILE,
ORDER_CREATE_TIME,
GOODS_NO,
GOODS_TOPIC,
IS_CALLBACK,
IS_BUY,
NOT_BUY_REASON,
NOT_BUY_REASON_NOTE,
CALLBACK_TIME,
ASSIGN_NUM,
(select user_name
from fecerp.sys_user
where user_id = commit_userid
and is_delete = 'N') AS COMMIT_USERNAME,
(select user_name
from fecerp.sys_user
where user_id = CALLBACK_USERID
and is_delete = 'N') AS CALLBACK_USERNAME,
(select user_name
from fecerp.sys_user
where user_id = SERVICE_NUM
and is_delete = 'N') AS SERVICE_NAME,
IMPORT_ID,
(select s.code_value
from sys_code s
where s.code_id = ttm.NOT_BUY_REASON
and s.is_delete = 'N') as NOT_BUY_REASON_NAME,
(SELECT GD.PRODUCT_ID
FROM GOODS GD
WHERE gd.goods_no = ttm.goods_no
AND gd.is_delete = 'N'
) AS PRODUCT_ID,
(SELECT GD.GOODS_ID
FROM GOODS GD
WHERE gd.goods_no = ttm.goods_no
AND gd.is_delete = 'N'
) AS GOODS_ID,
(), , 'N', 'Y') as cnt
from TMALL_TC_MESSAGE_LOG ttml
where ttml.is_delete = 'N'
and ttml.tmall_tc_msg_id =
ttm.tmall_tc_msg_id) as has_log,
(select user_id
from fecerp.sys_user
where user_name = ttm.ASSIGN_NUM
and is_delete = 'N') as ASSIGN_ID,
ALIPAY_ORDER_FEE,
ALIPAY_DELIVERY_FEE,
ALIPAY_TOTAL_FEE,
(select s.code_type_no
from sys_code s
where s.code_id = ttm.NOT_BUY_REASON
and s.is_delete = 'N') as CODE_TYPE_NO,
NOTE
from TMALL_TC_MESSAGE ttm
where IS_DELETE = 'N') mai

<include refid="queryTmallTcMessageParam" />
order by MESSAGE_TIME DESC
<include refid="ed_fy" />
</select>

<select id="getTmallTcMessageCount" resultClass="Integer"
parameterClass="TmallTcMessage">
) from (select ORDER_ID,
TMALL_TC_MSG_ID,TMALL_ORDER_ID,MEMBER_NAME,ALIPAY_USER_NAME,ALIPAY_PAID_FEE,MESSAGE,MESSAGE_SOURCE,RECEIVE_USER,
RECEIVE_FULL_ADDRESS,RECEIVE_TEL,RECEIVE_MOBILE,ORDER_CREATE_TIME,GOODS_NO,GOODS_TOPIC,IS_CALLBACK,
IS_BUY,NOT_BUY_REASON,CALLBACK_TIME,ASSIGN_NUM,(select user_name from
fecerp.sys_user where user_id = commit_userid and
is_delete='N') AS
COMMIT_USERNAME,
(select user_name from fecerp.sys_user where user_id =
CALLBACK_USERID and
is_delete='N') AS CALLBACK_USERNAME,
(select
user_name from fecerp.sys_user where user_id = SERVICE_NUM and
is_delete='N') AS
SERVICE_NAME,USER_TYPE,MESSAGE_TIME,DISTRIBUTION_TYPE,IMPORT_ID,CUSTOMER_NATURE,
(select user_id from fecerp.sys_user where user_name = ASSIGN_NUM and is_delete = 'N') as ASSIGN_ID,
(select s.code_type_no
from sys_code s
where s.code_id = NOT_BUY_REASON
and s.is_delete = 'N') as CODE_TYPE_NO
from
TMALL_TC_MESSAGE
where IS_DELETE='N') mai

<include refid="queryTmallTcMessageParam" />
</select>
<!-- 改为已回访 -->
<update id="updateVisited" parameterClass="TmallTcMessage">
update TMALL_TC_MESSAGE
<dynamic prepend="set">
<isNotEmpty prepend="," property="editUserId">
<![CDATA[ EDIT_USER_ID=#editUserId# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="isCallback">
<![CDATA[ IS_CALLBACK=#isCallback# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="callbackTime">
<![CDATA[ CALLBACK_TIME=#callbackTime# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="callbackUserid">
<![CDATA[ CALLBACK_USERID=#callbackUserid# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="editTime">
<![CDATA[ EDIT_TIME=#editTime# ]]>
</isNotEmpty>
</dynamic>
where TMALL_TC_MSG_ID=#tmallTcMsgId#
</update>
<!-- 改为已购买 -->
<update id="updateBuy" parameterClass="TmallTcMessage">
update TMALL_TC_MESSAGE
<dynamic prepend="set">
<isNotEmpty prepend="," property="editUserId">
<![CDATA[ EDIT_USER_ID=#editUserId# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="isBuy">
<![CDATA[ IS_BUY=#isBuy# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="editTime">
<![CDATA[ EDIT_TIME=#editTime# ]]>
</isNotEmpty>
</dynamic>
where TMALL_TC_MSG_ID=#tmallTcMsgId#
</update>
<!-- 更新操作 -->
<update id="update" parameterClass="TmallTcMessage">
update TMALL_TC_MESSAGE
<dynamic prepend="set">
<isNotEmpty prepend="," property="orderId">
<![CDATA[ ORDER_ID= ORDER_ID || #orderId# || ',']]>
</isNotEmpty>
<isNotEmpty prepend="," property="addUserId">
<![CDATA[ ADD_USER_ID=#addUserId# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="messageSource">
<![CDATA[ MESSAGE_SOURCE=#messageSource# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="receiveMobile">
<![CDATA[ RECEIVE_MOBILE=#receiveMobile# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="receiveUser">
<![CDATA[ RECEIVE_USER=#receiveUser# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="editUserId">
<![CDATA[ EDIT_USER_ID=#editUserId# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="commitUserid">
<![CDATA[ COMMIT_USERID=#commitUserid# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="memberName">
<![CDATA[ MEMBER_NAME=#memberName# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="orderCreateTime">
<![CDATA[ ORDER_CREATE_TIME=#orderCreateTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="goodsTopic">
<![CDATA[ GOODS_TOPIC=#goodsTopic# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isCallback">
<![CDATA[ IS_CALLBACK=#isCallback# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="callbackUserid">
<![CDATA[ CALLBACK_USERID=#callbackUserid# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="editTime">
<![CDATA[ EDIT_TIME=#editTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="receiveFullAddress">
<![CDATA[ RECEIVE_FULL_ADDRESS=#receiveFullAddress# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="receiveTel">
<![CDATA[ RECEIVE_TEL=#receiveTel# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isDelete">
<![CDATA[ IS_DELETE=#isDelete# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="addTime">
<![CDATA[ ADD_TIME=#addTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="userType">
<![CDATA[ USER_TYPE=#userType# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="goodsCount">
<![CDATA[ GOODS_COUNT=#goodsCount# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="goodsNo">
<![CDATA[ GOODS_NO=#goodsNo# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="alipayUserName">
<![CDATA[ ALIPAY_USER_NAME=#alipayUserName# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="message">
<![CDATA[ MESSAGE=#message# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="callbackTime">
<![CDATA[ CALLBACK_TIME=#callbackTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="notBuyReason">
<![CDATA[ NOT_BUY_REASON=#notBuyReason# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="notBuyReasonNote">
<![CDATA[ NOT_BUY_REASON_NOTE=#notBuyReasonNote# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="customerNature">
<![CDATA[ CUSTOMER_NATURE=#customerNature# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="tmallOrderId">
<![CDATA[ TMALL_ORDER_ID=#tmallOrderId# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="alipayPaidFee">
<![CDATA[ ALIPAY_PAID_FEE=#alipayPaidFee# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="isBuy">
<![CDATA[ IS_BUY=#isBuy# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="assignNum">
<![CDATA[ ASSIGN_NUM=#assignNum# ]]>
</isNotEmpty>
<isNotEmpty prepend="," property="distributionType">
<![CDATA[ DISTRIBUTION_TYPE=#distributionType# ]]>
</isNotEmpty>
</dynamic>

<isNotEmpty prepend="and" property="tmallTcMsgId">
<![CDATA[ TMALL_TC_MSG_ID=#tmallTcMsgId# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="importId">
<![CDATA[ IMPORT_ID=#importId# ]]>
</isNotEmpty>
</update>

<select id="TmallMessageExportExcel" resultMap="TmallMessageExportResultMap"
parameterClass="TmallTcMessage">
select * from(
SELECT
TMM.ORDER_ID,
TMM.IMPORT_ID,
TMM.TMALL_ORDER_ID,
TMM.MEMBER_NAME,
TMM.ALIPAY_USER_NAME,
TMM.ALIPAY_PAID_FEE,
TMM.MESSAGE,
TMM.MESSAGE_SOURCE,
CASE TMM.MESSAGE_SOURCE
WHEN 'app' THEN
'app端'
WHEN 'pc' THEN
'PC端'
WHEN 'wap' THEN
'wap端'
WHEN 'tm' THEN
'天猫'
END AS MESSAGE_SOURCE_NAME,
TMM.RECEIVE_USER,
replace(TMM.RECEIVE_FULL_ADDRESS,substr(TMM.RECEIVE_FULL_ADDRESS,round(length(TMM.RECEIVE_FULL_ADDRESS)
, ),nvl(#strLength#,)),'*****') RECEIVE_FULL_ADDRESS,
replace(
TMM.RECEIVE_TEL, substr( TMM.RECEIVE_TEL, , nvl(#strLength#,)),
'****') AS RECEIVE_TEL,
TMM.ORDER_CREATE_TIME,
TMM.GOODS_NO,
TMM.GOODS_TOPIC,
TMM.GOODS_COUNT,
TMM.IS_CALLBACK,
DECODE(TMM.IS_CALLBACK,'N','未回访','Y','已回访') as IS_CALLBACK_NAME,
TMM.IS_BUY,
DECODE(tmm.IS_BUY,'N','未购买','Y','已购买') as IS_BUY_NAME,
TMM.NOT_BUY_REASON,
TMM.NOT_BUY_REASON_NOTE,
TMM.CALLBACK_TIME,
TMM.COMMIT_USERID,
TMM.CALLBACK_USERID,
TMM.USER_TYPE,
DECODE(tmm.USER_TYPE,'N','一次购买用户','Y','二次购买用户') as USER_TYPE_NAME,
TMM.CUSTOMER_NATURE,
CASE TMM.CUSTOMER_NATURE
WHEN 'dg' THEN
'代购'
WHEN 'zy' THEN
'自用'
WHEN 'yf' THEN
'药贩'
END AS CUSTOMER_NATURE_NAME,
DECODE(tmm.DISTRIBUTION_TYPE,'N','未分配','A','系统自动分配','H','手动分配') as DISTRIBUTION_TYPE_NAME,
TMM.DISTRIBUTION_TYPE,
(select user_name from fecerp.sys_user where user_id = tmm.SERVICE_NUM and
is_delete='N') AS SERVICE_NAME,
TMM.MESSAGE_TIME,
(select s.code_value from sys_code s where s.code_id=TMM.NOT_BUY_REASON and s.is_delete='N') as NOT_BUY_REASON_NAME,
tmm.ASSIGN_NUM,
(select user_id from fecerp.sys_user where user_name = TMM.ASSIGN_NUM and is_delete = 'N') as ASSIGN_ID,
ALIPAY_ORDER_FEE,
ALIPAY_DELIVERY_FEE,
ALIPAY_TOTAL_FEE,
(select s.code_type_no
from sys_code s
where s.code_id = NOT_BUY_REASON
and s.is_delete = 'N') as CODE_TYPE_NO,
NOTE
FROM TMALL_TC_MESSAGE TMM
where TMM.Is_Delete='N') mai

<include refid="queryTmallTcMessageParam" />
order by mai.MESSAGE_TIME desc
</select>
<insert id="save" parameterClass="TmallTcMessage">
<selectKey resultClass="Long" keyProperty="tmallTcMsgId">
SELECT
TMALL_TC_MESSAGE_SEQ.nextval AS id from dual
</selectKey>
insert into TMALL_TC_MESSAGE (
ADD_USER_ID,
MESSAGE_SOURCE,
TMALL_TC_MSG_ID,
RECEIVE_MOBILE,
RECEIVE_USER,
COMMIT_USERID,
MEMBER_NAME,
ORDER_CREATE_TIME,
GOODS_TOPIC,
IMPORT_ID,
CALLBACK_USERID,
RECEIVE_FULL_ADDRESS,
RECEIVE_TEL,
ADD_TIME,
USER_TYPE,
GOODS_COUNT,
GOODS_NO,
ALIPAY_USER_NAME,
MESSAGE,
CALLBACK_TIME,
NOT_BUY_REASON,
TMALL_ORDER_ID,
ALIPAY_PAID_FEE,
SERVICE_NUM,
MESSAGE_TIME,
CUSTOMER_NATURE
<isNotEmpty prepend="," property="alipayOrderFee">
ALIPAY_ORDER_FEE
</isNotEmpty>
<isNotEmpty prepend="," property="alipayDeliveryFee">
ALIPAY_DELIVERY_FEE
</isNotEmpty>
<isNotEmpty prepend="," property="alipayTotalFee">
ALIPAY_TOTAL_FEE
</isNotEmpty>
<isNotEmpty prepend="," property="distributionType">
DISTRIBUTION_TYPE
</isNotEmpty>
<isNotEmpty prepend="," property="note">
NOTE
</isNotEmpty>
)
values
(
#addUserId#,
#messageSource#,
#tmallTcMsgId#,
#receiveMobile#,
#receiveUser#,
#commitUserid#,
#memberName#,
#orderCreateTime#,
#goodsTopic#,
#importId#,
#callbackUserid#,
#receiveFullAddress#,
#receiveTel#,
#addTime#,
#userType#,
#goodsCount#,
#goodsNo#,
#alipayUserName#,
#message#,
#callbackTime#,
#notBuyReason#,
#tmallOrderId#,
#alipayPaidFee#,
#serviceNum#,
#messageTime#,
#customerNature#
<isNotEmpty prepend="," property="alipayOrderFee">
#alipayOrderFee#
</isNotEmpty>
<isNotEmpty prepend="," property="alipayDeliveryFee">
#alipayDeliveryFee#
</isNotEmpty>
<isNotEmpty prepend="," property="alipayTotalFee">
#alipayTotalFee#
</isNotEmpty>
<isNotEmpty prepend="," property="distributionType" >
#distributionType#
</isNotEmpty>
<isNotEmpty prepend="," property="note">
#note#
</isNotEmpty>
)
</insert>

<select id="querySelectGoods" resultMap="goodsTcQueryRes"
parameterClass="TmallTcMessage">
SELECT G.GOODS_ID,
G.PRODUCT_ID,
G.GOODS_NO,
G.GOODS_NAME,
G.MARKET_PRICE,
G.EC_PRICE,
G.AVAILABLE_STOCK,
PU.UNIT_NAME AS UNITNAME,
P.IS_ONSALE AS ISONSALE
FROM GOODS G
LEFT JOIN PRODUCT P
ON G.PRODUCT_ID
= P.PRODUCT_ID
AND P.IS_DELETE = 'N'
LEFT JOIN PRODUCT_UNIT PU
ON
P.UNIT_ID = PU.UNIT_ID
LEFT JOIN PRODUCT_YW_CATALOG_GOODS PYC
ON
G.GOODS_NO = PYC.GOODS_NO
WHERE G.IS_DELETE = 'N'
AND P.IS_ONSALE = 'Y'
AND G.IS_DEFAULT = 'Y'
<isNotEmpty prepend="and" property="goodsNos">
G.goods_no in
($goodsNos$)
</isNotEmpty>
</select>

<select id="getIsBuy" parameterClass="TmallTcMessage"
resultClass="Integer">
select count(*) from order_info oi where oi.is_delete='N'
and oi.receive_mobile = #receiveMobile#
</select>
<select id="getMessageGain" resultMap="getMessageGainMap" parameterClass="TmallTcMessage">

<isNotEmpty prepend="and" property="importId">
ee.import_id=#importId#
</isNotEmpty>
order by MESSAGE_TIME desc
</select>

<select id="getUnProcessedMsgCount" parameterClass="TmallTcMessage"
resultClass="Integer">
select count(*)
from TMALL_TC_MESSAGE t
where t.is_delete = 'N'
<isNotEmpty prepend="and" property="importId">
t.import_id = #importId#
</isNotEmpty>
and t.is_buy = 'N'
and (t.is_callback = 'N' or t.not_buy_reason is null)
and t.assign_num = #assignNum#
</select>

<select id="checkOrderInfo" parameterClass="java.util.HashMap" resultClass="Integer">
)
from ORDER_INFO t
where t.IS_DELETE='N'
<isNotEmpty prepend="and" property="orderId">
t.ORDER_ID = #orderId#
</isNotEmpty>
,,)
</select>
<select id="checkTmallTcMessage" parameterClass="java.util.HashMap" resultClass="Integer">
)
from TMALL_TC_MESSAGE ttm
where ttm.IS_DELETE='N'
<isNotEmpty prepend="and" property="orderId">
ttm.ORDER_ID like '%$orderId$%'
</isNotEmpty>
</select>

</sqlMap>