SQL Procedure示例程序04

最新推荐文章于 2025-08-08 12:35:56 发布
原创最新推荐文章于 2025-08-08 12:35:56 发布 · 531 阅读
0 ·
CC 4.0 BY-SA版权
文章标签：
#sql #date #payment #null #delete #join
SQL SERVER 专栏收录该内容
9 篇文章
订阅专栏
本文概要介绍了信息技术领域的核心概念，包括开发工具、大数据、AI音视频处理等细分技术领域，帮助开发者深入理解并掌握现代技术趋势。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
CREATE procedure [dbo].[eosp_CreateEPOS_CARD]
(
@cC_NO VarChar(30),
@cC_DATE DateTime,
@sTATUS Char(1),
@lAST_PAY_TIME DateTime,
@oRDER_NUMBER VarChar(12),
@pAN VarChar(19),
@eXPIREDDATE Char(10),
@cVV2 VarChar(3),
@cHMOBILE VarChar(15),
@cALLMOBILE VarChar(15),
@cHIDNUM NVarChar(30),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_CARD] (CC_NO, CC_DATE, STATUS, LAST_PAY_TIME, ORDER_NUMBER, PAN, EXPIREDDATE, CVV2, CHMOBILE, CALLMOBILE, CHIDNUM, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@cC_NO, @cC_DATE, @sTATUS, @lAST_PAY_TIME, @oRDER_NUMBER, @pAN, @eXPIREDDATE, @cVV2, @cHMOBILE, @cALLMOBILE, @cHIDNUM, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity
GO

create procedure [dbo].[eosp_CreateEPOS_CHECKACCOUNT_HEAD]
(
@tRANS_ID Int,
@s_POSTIME NVarChar(14),
@r_POSTIME NVarChar(14),
@s_POSID Int,
@r_POSID Int,
@r_TRANSTIME NVarChar(8),
@r_TRANSDATE NVarChar(8),
@r_RETCODE NVarChar(7),
@s_TERMINALID NVarChar(8),
@r_TERMINALID NVarChar(8),
@s_MERCHANTID NVarChar(15),
@r_MERCHANTID NVarChar(15),
@r_MERCHANTNAME NVarChar(40),
@s_BATCHNO Int,
@r_BATCHNO Int,
@s_TERMINALFLAG NChar(1),
@r_TERMINALFLAG NChar(1),
@r_COMMENTRES NVarChar(100),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_CHECKACCOUNT_HEAD] (TRANS_ID, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_BATCHNO, R_BATCHNO, S_TERMINALFLAG, R_TERMINALFLAG, R_COMMENTRES, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_BATCHNO, @r_BATCHNO, @s_TERMINALFLAG, @r_TERMINALFLAG, @r_COMMENTRES, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity
GO

create procedure [dbo].[eosp_CreateEPOS_CHECKACCOUNT_LINE]
(
@tRANS_ID Int,
@cHECK_ID Int,
@lINE_TYPE NChar(1),
@pAYMENT_ID Int,
@oRDER_NUMBER NVarChar(8),
@mERCHANTID NVarChar(15),
@tERMINALID NVarChar(8),
@bATCHNO Int,
@tRANSDATE NVarChar(8),
@tRANSTIME NVarChar(8),
@pAN NVarChar(19),
@tRANSAMOUNT Decimal(13,2),
@cURRCODE NVarChar(3),
@sYSTEMREFCODE NVarChar(12),
@sERIALNO Int,
@aUTHORIZECODE NVarChar(6),
@pRODUCTCODE NVarChar(6),
@dIVIDEDMONTHS NVarChar(2),
@rEFUNDFLG NChar(1),
@cHECKFLG NChar(1),
@fILLER NVarChar(36),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_CHECKACCOUNT_LINE] (TRANS_ID, CHECK_ID, LINE_TYPE, PAYMENT_ID, ORDER_NUMBER, MERCHANTID, TERMINALID, BATCHNO, TRANSDATE, TRANSTIME, PAN, TRANSAMOUNT, CURRCODE, SYSTEMREFCODE, SERIALNO, AUTHORIZECODE, PRODUCTCODE, DIVIDEDMONTHS, REFUNDFLG, CHECKFLG, FILLER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @cHECK_ID, @lINE_TYPE, @pAYMENT_ID, @oRDER_NUMBER, @mERCHANTID, @tERMINALID, @bATCHNO, @tRANSDATE, @tRANSTIME, @pAN, @tRANSAMOUNT, @cURRCODE, @sYSTEMREFCODE, @sERIALNO, @aUTHORIZECODE, @pRODUCTCODE, @dIVIDEDMONTHS, @rEFUNDFLG, @cHECKFLG, @fILLER, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity
GO

CREATE procedure [dbo].[eosp_CreateEPOS_TRANS_HEADER]
(
@iP_ADDRESS NVarChar(20),
@s_TRANS_DATE DateTime,
@tRANS_TYPE NVarChar(50),
@rESULT NChar(1),
@r_TRANS_DATE DateTime,
@eRROR_MSG NVarChar(80),
@sXML_BODY VarChar(4000),
@rXML_BODY VarChar(4000),
@mODULE_NAME NVarChar(50),
@fILE_NAME NVarChar(50),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
begin
declare @sXML_BODYXML xml
declare @rXML_BODYXML xml

SET @sXML_BODYXML = CONVERT(XML, @sXML_BODY)
SET @rXML_BODYXML = CONVERT(XML, @rXML_BODY)

insert into [EPOS_TRANS_HEADER] (IP_ADDRESS, S_TRANS_DATE, 
TRANS_TYPE, RESULT, R_TRANS_DATE, ERROR_MSG, SXML_BODY, 
RXML_BODY, MODULE_NAME, FILE_NAME, CREATION_DATE, CREATED_BY, 
LAST_UPDATE_DATE, LAST_UPDATED_BY) values 
(@iP_ADDRESS, @s_TRANS_DATE, 
@tRANS_TYPE, @rESULT, @r_TRANS_DATE, @eRROR_MSG, 
@sXML_BODYXML,
@rXML_BODYXML, 
@mODULE_NAME, @fILE_NAME, @cREATION_DATE, @cREATED_BY, 
@lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity
end 
GO

CREATE procedure [dbo].[eosp_CreateEPOS_TRANS_LOGIN]
(
@tRANS_ID Int,
@tRANS_TYPE NVarChar(10),
@s_POSTIME NVarChar(14),
@r_POSTIME NVarChar(14),
@s_POSID Int,
@r_POSID NVarChar(6),
@r_TRANSTIME NVarChar(8),
@r_TRANSDATE NVarChar(8),
@r_RETCODE NVarChar(7),
@s_TERMINALID NVarChar(8),
@r_TERMINALID NVarChar(8),
@s_MERCHANTID NVarChar(15),
@r_MERCHANTID NVarChar(15),
@r_MERCHANTNAME NVarChar(40),
@s_PASSWORD NVarChar(8),
@s_NEWPASSWORD NVarChar(8),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_TRANS_LOGIN] (TRANS_ID, TRANS_TYPE, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_PASSWORD, S_NEWPASSWORD, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @tRANS_TYPE, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_PASSWORD, @s_NEWPASSWORD, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity

GO

create procedure [dbo].[eosp_CreateEPOS_TRANS_PAYMENT]
(
@tRANS_ID Int,
@oRDER_NUMBER NVarChar(12),
@tRANS_TYPE NVarChar(20),
@rESULT NChar(1),
@rETURN_CONFIRM NChar(1),
@rETURN_CON_DATE DateTime,
@rETURN_CON_BY NVarChar(30),
@s_PAN NVarChar(19),
@r_PAN NVarChar(19),
@s_PROCESSCODE NVarChar(6),
@r_PROCESSCODE NVarChar(6),
@s_TRANSAMOUNT Decimal(13,2),
@r_TRANSAMOUNT Decimal(13,2),
@s_POSTIME NVarChar(14),
@r_POSTIME NVarChar(14),
@s_POSID Int,
@r_POSID NVarChar(6),
@r_TRANSTIME NVarChar(8),
@r_TRANSDATE NVarChar(8),
@s_EXPIREDDATE NVarChar(4),
@r_EXPIREDDATE NVarChar(4),
@s_AUTHORIZECODE NVarChar(6),
@r_AUTHORIZECODE NVarChar(6),
@s_ORGPOSID Int,
@r_ORGPOSID Int,
@r_RETCODE NVarChar(7),
@s_TERMINALID NVarChar(8),
@r_TERMINALID NVarChar(8),
@s_MERCHANTID NVarChar(15),
@r_MERCHANTID NVarChar(15),
@r_MERCHANTNAME NVarChar(40),
@r_COMMENTRES NVarChar(100),
@s_CURRCODE NVarChar(3),
@r_CURRCODE NVarChar(3),
@s_CHIDNUM NVarChar(18),
@r_CHIDNUM NVarChar(18),
@s_CHMOBILE NVarChar(15),
@r_CHMOBILE NVarChar(15),
@s_BATCHNO Int,
@r_BATCHNO Int,
@s_DIVIDEDNUM NVarChar(2),
@s_PRODUCTTYPE NVarChar(2),
@r_DIVIDEDFEE Decimal(13,2),
@r_TOTALAMT Decimal(13,2),
@r_DIVIDEDAMT Decimal(13,2),
@s_CVV2 NVarChar(3),
@r_CVV2 NVarChar(3),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_TRANS_PAYMENT] (TRANS_ID, ORDER_NUMBER, TRANS_TYPE, RESULT, RETURN_CONFIRM, RETURN_CON_DATE, RETURN_CON_BY, S_PAN, R_PAN, S_PROCESSCODE, R_PROCESSCODE, S_TRANSAMOUNT, R_TRANSAMOUNT, S_POSTIME, R_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, S_EXPIREDDATE, R_EXPIREDDATE, S_AUTHORIZECODE, R_AUTHORIZECODE, S_ORGPOSID, R_ORGPOSID, R_RETCODE, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, R_COMMENTRES, S_CURRCODE, R_CURRCODE, S_CHIDNUM, R_CHIDNUM, S_CHMOBILE, R_CHMOBILE, S_BATCHNO, R_BATCHNO, S_DIVIDEDNUM, S_PRODUCTTYPE, R_DIVIDEDFEE, R_TOTALAMT, R_DIVIDEDAMT, S_CVV2, R_CVV2, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @oRDER_NUMBER, @tRANS_TYPE, @rESULT, @rETURN_CONFIRM, @rETURN_CON_DATE, @rETURN_CON_BY, @s_PAN, @r_PAN, @s_PROCESSCODE, @r_PROCESSCODE, @s_TRANSAMOUNT, @r_TRANSAMOUNT, @s_POSTIME, @r_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @s_EXPIREDDATE, @r_EXPIREDDATE, @s_AUTHORIZECODE, @r_AUTHORIZECODE, @s_ORGPOSID, @r_ORGPOSID, @r_RETCODE, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @r_COMMENTRES, @s_CURRCODE, @r_CURRCODE, @s_CHIDNUM, @r_CHIDNUM, @s_CHMOBILE, @r_CHMOBILE, @s_BATCHNO, @r_BATCHNO, @s_DIVIDEDNUM, @s_PRODUCTTYPE, @r_DIVIDEDFEE, @r_TOTALAMT, @r_DIVIDEDAMT, @s_CVV2, @r_CVV2, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity

GO

create procedure [dbo].[eosp_CreateEPOS_TRANS_SETTLEMENT]
(
@tRANS_ID Int,
@s_POSTIME NVarChar(14),
@s_POSID Int,
@r_POSID NVarChar(6),
@r_TRANSTIME NVarChar(8),
@r_TRANSDATE NVarChar(8),
@r_RETCODE NVarChar(7),
@rESULT Char(1),
@s_TERMINALID NVarChar(8),
@r_TERMINALID NVarChar(8),
@s_MERCHANTID NVarChar(15),
@r_MERCHANTID NVarChar(15),
@r_MERCHANTNAME NVarChar(40),
@s_BATCHNO Int,
@r_BATCHNO Int,
@s_TOTALTRSCNT Int,
@r_TOTALTRSCNT Int,
@s_SIGN NChar(1),
@r_SIGN NChar(1),
@s_TRASUMAMT Decimal(13,2),
@r_TRASUMAMT Decimal(13,2),
@r_SUCCESSFLAG NChar(1),
@r_COMMENTRES NVarChar(100),
@rESERVED NVarChar(100),
@cREATION_DATE DateTime,
@cREATED_BY NVarChar(50),
@lAST_UPDATE_DATE DateTime,
@lAST_UPDATED_BY NVarChar(50)
)
as
insert into [EPOS_TRANS_SETTLEMENT] (TRANS_ID, S_POSTIME, S_POSID, R_POSID, R_TRANSTIME, R_TRANSDATE, R_RETCODE, RESULT, S_TERMINALID, R_TERMINALID, S_MERCHANTID, R_MERCHANTID, R_MERCHANTNAME, S_BATCHNO, R_BATCHNO, S_TOTALTRSCNT, R_TOTALTRSCNT, S_SIGN, R_SIGN, S_TRASUMAMT, R_TRASUMAMT, R_SUCCESSFLAG, R_COMMENTRES, RESERVED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) values (@tRANS_ID, @s_POSTIME, @s_POSID, @r_POSID, @r_TRANSTIME, @r_TRANSDATE, @r_RETCODE, @rESULT, @s_TERMINALID, @r_TERMINALID, @s_MERCHANTID, @r_MERCHANTID, @r_MERCHANTNAME, @s_BATCHNO, @r_BATCHNO, @s_TOTALTRSCNT, @r_TOTALTRSCNT, @s_SIGN, @r_SIGN, @s_TRASUMAMT, @r_TRASUMAMT, @r_SUCCESSFLAG, @r_COMMENTRES, @rESERVED, @cREATION_DATE, @cREATED_BY, @lAST_UPDATE_DATE, @lAST_UPDATED_BY)
return @@identity
GO

create procedure [dbo].[eosp_DeleteEPOS_CARD]
(

@cC_NO VarChar(30))
 AS 
DELETE FROM  [EPOS_CARD] 
 WHERE CC_NO=@cC_NO
GO


create procedure [dbo].[eosp_DeleteEPOS_CHECKACCOUNT_HEAD]
(

@s_POSID Int)
 AS 
DELETE FROM  [EPOS_CHECKACCOUNT_HEAD] 
 WHERE S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_DeleteEPOS_CHECKACCOUNT_LINE]
(

@cHECK_ID Int, 
@lINE_TYPE NChar(1))
 AS 
DELETE FROM  [EPOS_CHECKACCOUNT_LINE] 
 WHERE CHECK_ID=@cHECK_ID AND LINE_TYPE=@lINE_TYPE
GO

create procedure [dbo].[eosp_DeleteEPOS_TRANS_HEADER]
(

@tRANS_ID Int)
 AS 
DELETE FROM  [EPOS_TRANS_HEADER] 
 WHERE TRANS_ID=@tRANS_ID
GO

create procedure [dbo].[eosp_DeleteEPOS_TRANS_LOGIN]
(

@s_POSID Int)
 AS 
DELETE FROM  [EPOS_TRANS_LOGIN] 
 WHERE S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_DeleteEPOS_TRANS_PAYMENT]
(

@s_POSID Int)
 AS 
DELETE FROM  [EPOS_TRANS_PAYMENT] 
 WHERE S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_DeleteEPOS_TRANS_SETTLEMENT]
(

@s_POSID Int)
 AS 
DELETE FROM  [EPOS_TRANS_SETTLEMENT] 
 WHERE S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_UpdateEPOS_CARD]
(

@cC_NO VarChar(30), 
@cC_DATE DateTime, 
@sTATUS Char(1), 
@lAST_PAY_TIME DateTime, 
@oRDER_NUMBER VarChar(12), 
@pAN VarChar(19), 
@eXPIREDDATE Char(10), 
@cVV2 VarChar(3), 
@cHMOBILE VarChar(15), 
@cALLMOBILE VarChar(15), 
@cHIDNUM NVarChar(30), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
update [EPOS_CARD] set CC_DATE=@cC_DATE, STATUS=@sTATUS, LAST_PAY_TIME=@lAST_PAY_TIME, ORDER_NUMBER=@oRDER_NUMBER, PAN=@pAN, EXPIREDDATE=@eXPIREDDATE, CVV2=@cVV2, CHMOBILE=@cHMOBILE, CALLMOBILE=@cALLMOBILE, CHIDNUM=@cHIDNUM, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where CC_NO=@cC_NO
GO

create procedure [dbo].[eosp_UpdateEPOS_CHECKACCOUNT_HEAD]
(

@s_POSID Int, 
@tRANS_ID Int, 
@s_POSTIME NVarChar(14), 
@r_POSTIME NVarChar(14), 
@r_POSID Int, 
@r_TRANSTIME NVarChar(8), 
@r_TRANSDATE NVarChar(8), 
@r_RETCODE NVarChar(7), 
@s_TERMINALID NVarChar(8), 
@r_TERMINALID NVarChar(8), 
@s_MERCHANTID NVarChar(15), 
@r_MERCHANTID NVarChar(15), 
@r_MERCHANTNAME NVarChar(40), 
@s_BATCHNO Int, 
@r_BATCHNO Int, 
@s_TERMINALFLAG NChar(1), 
@r_TERMINALFLAG NChar(1), 
@r_COMMENTRES NVarChar(100), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
update [EPOS_CHECKACCOUNT_HEAD] set TRANS_ID=@tRANS_ID, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, S_BATCHNO=@s_BATCHNO, R_BATCHNO=@r_BATCHNO, S_TERMINALFLAG=@s_TERMINALFLAG, R_TERMINALFLAG=@r_TERMINALFLAG, R_COMMENTRES=@r_COMMENTRES, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_UpdateEPOS_CHECKACCOUNT_LINE]
(

@cHECK_ID Int, 
@lINE_TYPE NChar(1), 
@tRANS_ID Int, 
@pAYMENT_ID Int, 
@oRDER_NUMBER NVarChar(8), 
@mERCHANTID NVarChar(15), 
@tERMINALID NVarChar(8), 
@bATCHNO Int, 
@tRANSDATE NVarChar(8), 
@tRANSTIME NVarChar(8), 
@pAN NVarChar(19), 
@tRANSAMOUNT Decimal(13,2), 
@cURRCODE NVarChar(3), 
@sYSTEMREFCODE NVarChar(12), 
@sERIALNO Int, 
@aUTHORIZECODE NVarChar(6), 
@pRODUCTCODE NVarChar(6), 
@dIVIDEDMONTHS NVarChar(2), 
@rEFUNDFLG NChar(1), 
@cHECKFLG NChar(1), 
@fILLER NVarChar(36), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
update [EPOS_CHECKACCOUNT_LINE] set TRANS_ID=@tRANS_ID, PAYMENT_ID=@pAYMENT_ID, ORDER_NUMBER=@oRDER_NUMBER, MERCHANTID=@mERCHANTID, TERMINALID=@tERMINALID, BATCHNO=@bATCHNO, TRANSDATE=@tRANSDATE, TRANSTIME=@tRANSTIME, PAN=@pAN, TRANSAMOUNT=@tRANSAMOUNT, CURRCODE=@cURRCODE, SYSTEMREFCODE=@sYSTEMREFCODE, SERIALNO=@sERIALNO, AUTHORIZECODE=@aUTHORIZECODE, PRODUCTCODE=@pRODUCTCODE, DIVIDEDMONTHS=@dIVIDEDMONTHS, REFUNDFLG=@rEFUNDFLG, CHECKFLG=@cHECKFLG, FILLER=@fILLER, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where CHECK_ID=@cHECK_ID and LINE_TYPE=@lINE_TYPE
GO

CREATE procedure [dbo].[eosp_UpdateEPOS_TRANS_HEADER]
(

@tRANS_ID Int, 
@iP_ADDRESS NVarChar(20), 
@s_TRANS_DATE DateTime, 
@tRANS_TYPE NVarChar(50), 
@rESULT NChar(1), 
@r_TRANS_DATE DateTime, 
@eRROR_MSG NVarChar(80), 
@sXML_BODY VarChar(4000), 
@rXML_BODY VarChar(4000), 
@mODULE_NAME NVarChar(50), 
@fILE_NAME NVarChar(50), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
begin

declare @sXML_BODYXML xml
declare @rXML_BODYXML xml

SET @sXML_BODYXML = CONVERT(XML, @sXML_BODY)
SET @rXML_BODYXML = CONVERT(XML, @rXML_BODY)
update [EPOS_TRANS_HEADER] set IP_ADDRESS=@iP_ADDRESS, S_TRANS_DATE=@s_TRANS_DATE, TRANS_TYPE=@tRANS_TYPE, RESULT=@rESULT, R_TRANS_DATE=@r_TRANS_DATE, ERROR_MSG=@eRROR_MSG, SXML_BODY=@sXML_BODYXML, RXML_BODY=@rXML_BODYXML, MODULE_NAME=@mODULE_NAME, FILE_NAME=@fILE_NAME, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where TRANS_ID=@tRANS_ID
end
GO

CREATE procedure [dbo].[eosp_UpdateEPOS_TRANS_LOGIN]
(

@s_POSID Int, 
@tRANS_ID Int, 
@tRANS_TYPE NVarChar(10), 
@s_POSTIME NVarChar(14), 
@r_POSTIME NVarChar(14), 
@r_POSID NVarChar(6), 
@r_TRANSTIME NVarChar(8), 
@r_TRANSDATE NVarChar(8), 
@r_RETCODE NVarChar(7), 
@s_TERMINALID NVarChar(8), 
@r_TERMINALID NVarChar(8), 
@s_MERCHANTID NVarChar(15), 
@r_MERCHANTID NVarChar(15), 
@r_MERCHANTNAME NVarChar(40), 
@s_PASSWORD NVarChar(8), 
@s_NEWPASSWORD NVarChar(8), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
update [EPOS_TRANS_LOGIN] set TRANS_ID=@tRANS_ID, TRANS_TYPE=@tRANS_TYPE, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, S_PASSWORD=@s_PASSWORD, S_NEWPASSWORD=@s_NEWPASSWORD, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where S_POSID=@s_POSID
GO

create procedure [dbo].[eosp_UpdateEPOS_TRANS_PAYMENT]
(

@s_POSID Int, 
@tRANS_ID Int, 
@oRDER_NUMBER NVarChar(12), 
@tRANS_TYPE NVarChar(20), 
@rESULT NChar(1), 
@rETURN_CONFIRM NChar(1), 
@rETURN_CON_DATE DateTime, 
@rETURN_CON_BY NVarChar(30), 
@s_PAN NVarChar(19), 
@r_PAN NVarChar(19), 
@s_PROCESSCODE NVarChar(6), 
@r_PROCESSCODE NVarChar(6), 
@s_TRANSAMOUNT Decimal(13,2), 
@r_TRANSAMOUNT Decimal(13,2), 
@s_POSTIME NVarChar(14), 
@r_POSTIME NVarChar(14), 
@r_POSID NVarChar(6), 
@r_TRANSTIME NVarChar(8), 
@r_TRANSDATE NVarChar(8), 
@s_EXPIREDDATE NVarChar(4), 
@r_EXPIREDDATE NVarChar(4), 
@s_AUTHORIZECODE NVarChar(6), 
@r_AUTHORIZECODE NVarChar(6), 
@s_ORGPOSID Int, 
@r_ORGPOSID Int, 
@r_RETCODE NVarChar(7), 
@s_TERMINALID NVarChar(8), 
@r_TERMINALID NVarChar(8), 
@s_MERCHANTID NVarChar(15), 
@r_MERCHANTID NVarChar(15), 
@r_MERCHANTNAME NVarChar(40), 
@r_COMMENTRES NVarChar(100), 
@s_CURRCODE NVarChar(3), 
@r_CURRCODE NVarChar(3), 
@s_CHIDNUM NVarChar(18), 
@r_CHIDNUM NVarChar(18), 
@s_CHMOBILE NVarChar(15), 
@r_CHMOBILE NVarChar(15), 
@s_BATCHNO Int, 
@r_BATCHNO Int, 
@s_DIVIDEDNUM NVarChar(2), 
@s_PRODUCTTYPE NVarChar(2), 
@r_DIVIDEDFEE Decimal(13,2), 
@r_TOTALAMT Decimal(13,2), 
@r_DIVIDEDAMT Decimal(13,2), 
@s_CVV2 NVarChar(3), 
@r_CVV2 NVarChar(3), 
@cREATION_DATE DateTime, 
@cREATED_BY NVarChar(50), 
@lAST_UPDATE_DATE DateTime, 
@lAST_UPDATED_BY NVarChar(50))
as
update [EPOS_TRANS_PAYMENT] set TRANS_ID=@tRANS_ID, ORDER_NUMBER=@oRDER_NUMBER, TRANS_TYPE=@tRANS_TYPE, RESULT=@rESULT, RETURN_CONFIRM=@rETURN_CONFIRM, RETURN_CON_DATE=@rETURN_CON_DATE, RETURN_CON_BY=@rETURN_CON_BY, S_PAN=@s_PAN, R_PAN=@r_PAN, S_PROCESSCODE=@s_PROCESSCODE, R_PROCESSCODE=@r_PROCESSCODE, S_TRANSAMOUNT=@s_TRANSAMOUNT, R_TRANSAMOUNT=@r_TRANSAMOUNT, S_POSTIME=@s_POSTIME, R_POSTIME=@r_POSTIME, R_POSID=@r_POSID, R_TRANSTIME=@r_TRANSTIME, R_TRANSDATE=@r_TRANSDATE, S_EXPIREDDATE=@s_EXPIREDDATE, R_EXPIREDDATE=@r_EXPIREDDATE, S_AUTHORIZECODE=@s_AUTHORIZECODE, R_AUTHORIZECODE=@r_AUTHORIZECODE, S_ORGPOSID=@s_ORGPOSID, R_ORGPOSID=@r_ORGPOSID, R_RETCODE=@r_RETCODE, S_TERMINALID=@s_TERMINALID, R_TERMINALID=@r_TERMINALID, S_MERCHANTID=@s_MERCHANTID, R_MERCHANTID=@r_MERCHANTID, R_MERCHANTNAME=@r_MERCHANTNAME, R_COMMENTRES=@r_COMMENTRES, S_CURRCODE=@s_CURRCODE, R_CURRCODE=@r_CURRCODE, S_CHIDNUM=@s_CHIDNUM, R_CHIDNUM=@r_CHIDNUM, S_CHMOBILE=@s_CHMOBILE, R_CHMOBILE=@r_CHMOBILE, S_BATCHNO=@s_BATCHNO, R_BATCHNO=@r_BATCHNO, S_DIVIDEDNUM=@s_DIVIDEDNUM, S_PRODUCTTYPE=@s_PRODUCTTYPE, R_DIVIDEDFEE=@r_DIVIDEDFEE, R_TOTALAMT=@r_TOTALAMT, R_DIVIDEDAMT=@r_DIVIDEDAMT, S_CVV2=@s_CVV2, R_CVV2=@r_CVV2, CREATION_DATE=@cREATION_DATE, CREATED_BY=@cREATED_BY, LAST_UPDATE_DATE=@lAST_UPDATE_DATE, LAST_UPDATED_BY=@lAST_UPDATED_BY
    where S_POSID=@s_POSID
GO

CREATE PROCEDURE [dbo].[usp_GetPaymentOrder]
	-- Add the parameters for the stored procedure here
	@orderNO	nvarchar(50),
	@dateFrom	datetime,
	@dateTo		datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SELECT     dbo.EPOS_CARD.ROW_ID, dbo.EPOS_CARD.CC_NO, dbo.EPOS_CARD.CC_DATE, dbo.EPOS_CARD.STATUS, dbo.EPOS_CARD.LAST_PAY_TIME, 
                      dbo.EPOS_CARD.ORDER_NUMBER, dbo.EPOS_CARD.PAN, dbo.EPOS_CARD.EXPIREDDATE, dbo.EPOS_CARD.CVV2, dbo.EPOS_CARD.CHMOBILE, 
                      dbo.EPOS_CARD.CALLMOBILE, dbo.EPOS_CARD.CHIDNUM, dbo.EPOS_CARD.CREATION_DATE, dbo.EPOS_CARD.CREATED_BY, 
                      dbo.EPOS_CARD.LAST_UPDATE_DATE, dbo.EPOS_CARD.LAST_UPDATED_BY,dbo.OrderH.hCreateDate,dbo.OrderH.hSum,
					  dbo.OrderCredit.ocCardOwner,dbo.OrderCredit.ocCardModth
	FROM         dbo.EPOS_CARD INNER JOIN
						  dbo.OrderH ON dbo.EPOS_CARD.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN
						  dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo 
	WHERE     (dbo.EPOS_CARD.STATUS = 'W') AND (dbo.OrderH.hStatus = 'WC') and
				(dbo.OrderCredit.ocActive = 'NEED_POS') AND (dbo.OrderCredit.ocAttribute1 <> 'D') AND 
				(dbo.OrderH.hCustID IN (select pValue from parameter where pActive='Y' and pName='EPOS_CUST_ID'))
				And (dbo.OrderH.hSO = @orderNO or @orderNO='' ) and dbo.OrderH.hCreateDate>=@dateFrom
				and dbo.OrderH.hCreateDate<@dateTo
END
GO

CREATE PROCEDURE [dbo].[usp_GetPaymentOrderByRowID]
	@rowID	int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SELECT     dbo.EPOS_CARD.ROW_ID, dbo.EPOS_CARD.CC_NO, dbo.EPOS_CARD.CC_DATE, dbo.EPOS_CARD.STATUS, dbo.EPOS_CARD.LAST_PAY_TIME, 
                      dbo.EPOS_CARD.ORDER_NUMBER, dbo.EPOS_CARD.PAN, dbo.EPOS_CARD.EXPIREDDATE, dbo.EPOS_CARD.CVV2, dbo.EPOS_CARD.CHMOBILE, 
                      dbo.EPOS_CARD.CALLMOBILE, dbo.EPOS_CARD.CHIDNUM, dbo.EPOS_CARD.CREATION_DATE, dbo.EPOS_CARD.CREATED_BY, 
                      dbo.EPOS_CARD.LAST_UPDATE_DATE, dbo.EPOS_CARD.LAST_UPDATED_BY,dbo.OrderH.hCreateDate,dbo.OrderH.hSum,
					  dbo.OrderCredit.ocCardOwner,dbo.OrderCredit.ocCardModth
	FROM         dbo.EPOS_CARD INNER JOIN
						  dbo.OrderH ON dbo.EPOS_CARD.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN
						  dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo 
	WHERE     (dbo.EPOS_CARD.ROW_ID = @RowID)
END
GO

CREATE PROCEDURE [dbo].[usp_GetReversalPayment]
	@orderNO	nvarchar(50),
	@dateFrom	nvarchar(20),
	@dateTo		nvarchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SELECT     dbo.EPOS_TRANS_PAYMENT.ROW_ID, dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER, 
		dbo.EPOS_TRANS_PAYMENT.S_PAN,dbo.EPOS_TRANS_PAYMENT.S_TRANSAMOUNT,
		dbo.EPOS_TRANS_PAYMENT.S_EXPIREDDATE,dbo.EPOS_TRANS_PAYMENT.S_POSID,
		dbo.EPOS_TRANS_PAYMENT.S_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_CHMOBILE,
		dbo.EPOS_TRANS_PAYMENT.S_DIVIDEDNUM,
		dbo.OrderH.hCreateDate, dbo.OrderCredit.ocAttribute11, 
		dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwner
	FROM dbo.EPOS_TRANS_PAYMENT INNER JOIN
		dbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN
		dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo
	WHERE (dbo.OrderH.hStatus = 'WC') AND (dbo.OrderCredit.ocActive = 'NEED_SETTLE') AND (dbo.EPOS_TRANS_PAYMENT.TRANS_TYPE = 'PAYMENT') AND 
		(dbo.EPOS_TRANS_PAYMENT.RESULT = 'S') AND (dbo.OrderCredit.ocAttribute1 <> 'D')
		 and (dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = @orderNO or @orderNO = '')
		and dbo.OrderCredit.ocAttribute11<@dateTo and dbo.OrderCredit.ocAttribute11>=@dateFrom
END
GO

CREATE PROCEDURE [dbo].[usp_GetReversalPaymentByRowID]
	@rowID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT     dbo.EPOS_TRANS_PAYMENT.ROW_ID, dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER, 
		dbo.EPOS_TRANS_PAYMENT.S_PAN,dbo.EPOS_TRANS_PAYMENT.S_TRANSAMOUNT,
		dbo.EPOS_TRANS_PAYMENT.S_EXPIREDDATE,dbo.EPOS_TRANS_PAYMENT.S_POSID,
		dbo.EPOS_TRANS_PAYMENT.S_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_CHMOBILE,
		dbo.EPOS_TRANS_PAYMENT.S_DIVIDEDNUM,dbo.EPOS_TRANS_PAYMENT.S_CVV2,
		dbo.EPOS_TRANS_PAYMENT.R_CHIDNUM,dbo.EPOS_TRANS_PAYMENT.S_AUTHORIZECODE,
		dbo.EPOS_TRANS_PAYMENT.R_AUTHORIZECODE,dbo.EPOS_TRANS_PAYMENT.S_BATCHNO,
		dbo.OrderH.hCreateDate, dbo.OrderCredit.ocAttribute11, 
		dbo.OrderH.hSum, dbo.OrderCredit.ocCardOwner
	FROM dbo.EPOS_TRANS_PAYMENT INNER JOIN
		dbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSO INNER JOIN
		dbo.OrderCredit ON dbo.OrderH.hSO = dbo.OrderCredit.ocSo
	WHERE dbo.EPOS_TRANS_PAYMENT.ROW_ID=@rowID
END
GO

CREATE PROCEDURE [dbo].[usp_CheckReversalPayment]
	@rowID int,
	@resultMsg	nvarchar(100) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @OrderNumber nvarchar(50)
	declare @CREATION_DATE nvarchar(50)

	select @OrderNumber = ORDER_NUMBER from EPOS_TRANS_PAYMENT where ROW_ID=@rowID
	

		select @CREATION_DATE = CREATION_DATE  from EPOS_TRANS_PAYMENT 
		where TRANS_TYPE = 'REVERSAL' and RESULT ='S'
			and ORDER_NUMBER = @OrderNumber

	if @CREATION_DATE is not null
		set @resultMsg = '订单'+@OrderNumber+'在'+ +'时间已成功取消刷卡，请选择其他记录取消刷卡。'
	
	else
	
		set @resultMsg = ''
	
END
GO

CREATE PROCEDURE [dbo].[usp_GetSettledPayment]
	@batchNo nvarchar(50),
	@beginDate nvarchar(20),
	@endDate nvarchar(20),
	@successFlag char(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     S_BATCHNO, S_TOTALTRSCNT, S_TRASUMAMT, S_POSTIME, R_SUCCESSFLAG,
		R_COMMENTRES
FROM         dbo.EPOS_TRANS_SETTLEMENT
where (S_BATCHNO=@batchNo or @batchNo='') and 
	(R_SUCCESSFLAG=@successFlag or @successFlag='') and 
	cast(left(S_POSTIME,8) as datetime) >=@beginDate and
	cast(left(S_POSTIME,8) as datetime) <@endDate
END
GO

CREATE PROCEDURE [dbo].[usp_GetSettlePayment]
	@batchNO nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT     S_BATCHNO,COUNT(S_BATCHNO) AS S_TOTALTRSCNT,  
		SUM(S_TRANSAMOUNT) AS S_TRASUMAMT,
		'' AS S_POSTIME, '' AS R_SUCCESSFLAG, '' AS R_COMMENTRES
from 
(select S_BATCHNO, case TRANS_TYPE WHEN 'REVERSAL' THEN -S_TRANSAMOUNT ELSE S_TRANSAMOUNT END AS S_TRANSAMOUNT
	from EPOS_TRANS_PAYMENT
	WHERE  (RESULT = 'S') and (S_BATCHNO=@batchNO or @batchNO='')
		 and S_BATCHNO not in (select S_BATCHNO from EPOS_TRANS_SETTLEMENT)
) as subtable
GROUP BY S_BATCHNO
END
GO

CREATE PROCEDURE [dbo].[usp_GetSettlePaymentDetail]
	@batchNO nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT     dbo.EPOS_TRANS_PAYMENT.R_BATCHNO, dbo.EPOS_TRANS_PAYMENT.TRANS_TYPE, dbo.OrderH.hSO, dbo.OrderH.hCreateDate, dbo.OrderH.hSum, 
		dbo.EPOS_TRANS_PAYMENT.R_POSTIME
	FROM         dbo.EPOS_TRANS_PAYMENT INNER JOIN
		dbo.OrderH ON dbo.EPOS_TRANS_PAYMENT.ORDER_NUMBER = dbo.OrderH.hSO
	WHERE dbo.EPOS_TRANS_PAYMENT.R_BATCHNO = @batchNO
END
GO


CREATE PROCEDURE [dbo].[usp_GetCheckAccount]
	@batchNo nvarchar(50),
	@beginDate nvarchar(20),
	@endDate nvarchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT     S_BATCHNO, S_TOTALTRSCNT, S_TRASUMAMT, S_POSTIME, R_SUCCESSFLAG,R_COMMENTRES
	FROM         dbo.EPOS_TRANS_SETTLEMENT
	where R_SUCCESSFLAG='C' AND (S_BATCHNO=@batchNo or @batchNo='') and 
		cast(left(S_POSTIME,8) as datetime) >=@beginDate and
		cast(left(S_POSTIME,8) as datetime) <@endDate
END
GO

CREATE PROCEDURE [dbo].[usp_GetCheckAccountDetail]
	@batchNo nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT      TRANS_ID, ORDER_NUMBER,S_BATCHNO, R_TRANSDATE, R_TRANSTIME, S_PAN, R_TRANSAMOUNT, R_CURRCODE,
		 S_DIVIDEDNUM, CASE TRANS_TYPE WHEN 'RETURN' THEN 'Y' ELSE 'N' END AS TRANS_TYPE,S_TERMINALID,
         S_MERCHANTID,S_AUTHORIZECODE,S_POSID,R_AUTHORIZECODE
	FROM         dbo.EPOS_TRANS_PAYMENT
	WHERE S_BATCHNO=@batchNo
	order by TRANS_ID
END
GO

CREATE PROCEDURE [dbo].[usp_GetCheckAccountViewDetail] 
	@batchNo nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT     0 as [NO],dbo.EPOS_CHECKACCOUNT_HEAD.CHECK_ID, dbo.EPOS_CHECKACCOUNT_HEAD.S_BATCHNO, dbo.EPOS_CHECKACCOUNT_HEAD.R_RETCODE, 
                      dbo.EPOS_CHECKACCOUNT_LINE.ORDER_NUMBER, dbo.EPOS_CHECKACCOUNT_LINE.MERCHANTID, 
                      dbo.EPOS_CHECKACCOUNT_LINE.TERMINALID, dbo.EPOS_CHECKACCOUNT_LINE.TRANSDATE, dbo.EPOS_CHECKACCOUNT_LINE.TRANSTIME, 
                      dbo.EPOS_CHECKACCOUNT_LINE.PAN, dbo.EPOS_CHECKACCOUNT_LINE.TRANSAMOUNT, dbo.EPOS_CHECKACCOUNT_LINE.CURRCODE, 
                      dbo.EPOS_CHECKACCOUNT_LINE.SYSTEMREFCODE, dbo.EPOS_CHECKACCOUNT_LINE.SERIALNO, 
                      dbo.EPOS_CHECKACCOUNT_LINE.AUTHORIZECODE, dbo.EPOS_CHECKACCOUNT_LINE.PRODUCTCODE, 
                      dbo.EPOS_CHECKACCOUNT_LINE.DIVIDEDMONTHS, dbo.EPOS_CHECKACCOUNT_LINE.REFUNDFLG, dbo.EPOS_CHECKACCOUNT_LINE.CHECKFLG, 
                      dbo.EPOS_CHECKACCOUNT_LINE.FILLER
FROM         dbo.EPOS_CHECKACCOUNT_HEAD INNER JOIN
                      dbo.EPOS_CHECKACCOUNT_LINE ON dbo.EPOS_CHECKACCOUNT_HEAD.CHECK_ID = dbo.EPOS_CHECKACCOUNT_LINE.CHECK_ID
WHERE     (dbo.EPOS_CHECKACCOUNT_HEAD.S_BATCHNO = @batchNo)
END
GO

create  procedure [dbo].[usp_CreateImportEPOS_CARD]
(
	@CREATED_BY VarChar(50)
)
as

DECLARE @DateFrom varchar(10)
DECLARE @DateTo varchar(10)

DECLARE @CallID varchar(20)
DECLARE @CallerID varchar(20)
DECLARE @CalleeID varchar(20) 
DECLARE @CardNO varchar(50)
DECLARE @CertificateNO varchar(50)
DECLARE @UsefulLife varchar(50)
DECLARE @CVV2 varchar(50) 
DECLARE @RegisterPhone varchar(50) 
DECLARE @OrderNO varchar(50)
DECLARE @Create_Date datetime 
DECLARE @STATUS varchar(1)

DECLARE @tempEPOS_CARD table(
	CallID varchar(32) NOT NULL,
	CallerID varchar(20) NOT NULL DEFAULT (''),
	CalleeID varchar(20) NOT NULL DEFAULT (''),
	CardNO varchar(50) NOT NULL DEFAULT (''),
	CardType varchar(50) NOT NULL DEFAULT (''),
	CertificateNO varchar(50) NOT NULL DEFAULT (''),
	CertificateType varchar(50) NOT NULL DEFAULT (''),
	UsefulLife varchar(50) NOT NULL DEFAULT (''),
	CVV2 varchar(50) NOT NULL DEFAULT (''),
	RegisterPhone varchar(50) NOT NULL DEFAULT (''),
	OrderNO varchar(50) NOT NULL DEFAULT (''),
	Create_Date datetime NOT NULL DEFAULT (getdate())
)

--SET @CREATED_BY='Robbie'
SET @DateFrom=convert(varchar(10),DATEADD(month,-1,getdate()),121)
SET @DateTo=convert(varchar(10),getdate(),121)
--PRINT @DateFrom
--PRINT @DateTo

--将最近一个月的数据放入临时表
INSERT INTO @tempEPOS_CARD EXEC CCDB..usp_QueryCreditCard_IVRToB2C_ePOS
DECLARE MyCursor CURSOR FOR
	SELECT CallID,CallerID,CalleeID,CardNO,CertificateNO,
		UsefulLife,CVV2,RegisterPhone,OrderNO,
		Create_Date 
    FROM @tempEPOS_CARD 
    WHERE Create_Date>=@DateFrom and Create_Date<=@DateTo 

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @CallID,@CallerID,@CalleeID,@CardNO,@CertificateNO,
		@UsefulLife,@CVV2,@RegisterPhone,@OrderNO,
		@Create_Date 

WHILE @@FETCH_STATUS = 0
BEGIN
	--CallID CC_NO Call NO 
	--Create_Date CC_DATE CallCenter呼入时间 
	--OrderNO ORDER_NUMBER DMS订单号码 
	--CardNO PAN 信用卡号码 
	--UsefulLife EXPIREDDATE 有效期 
	--CVV2 CVV2 CVV2 
	--RegisterPhone CHMOBILE 注册号码 
	--CallerID CALLMOBILE 主叫号码 
	--CertificateNO CHIDNUM 证件号码 
	--将一个月来没有导过的call信息 INSERT INTO EPOS_CARD
	--并检查每个新导入的call信息中的订单之前是否有成功刷卡的记录,
	--如有 本条新增记录的status置为’ F’;否则检查之前是否有待刷卡的记录,
	--如有则将之前该订单的status置为’D’
	IF NOT exists(SELECT 1 FROM EPOS_CARD WHERE CC_NO=@CallID)
		BEGIN 
			SET @STATUS='W'
			IF exists(SELECT 1 FROM EPOS_CARD WHERE ORDER_NUMBER=@OrderNO and STATUS='S') 
				BEGIN
					SET @STATUS='F'	
				END 
			ELSE IF exists(SELECT 1 FROM EPOS_CARD WHERE ORDER_NUMBER=@OrderNO and STATUS='W')
				BEGIN		
					UPDATE EPOS_CARD SET STATUS='D' WHERE ORDER_NUMBER=@OrderNO and STATUS='W'
				END 	
			ELSE
				BEGIN 
					PRINT ' '
				END 	
			INSERT INTO EPOS_CARD(CC_NO,CC_DATE,[STATUS],LAST_PAY_TIME,ORDER_NUMBER,
									  PAN,EXPIREDDATE,CVV2,CHMOBILE,CALLMOBILE,
									  CHIDNUM,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY)
				VALUES(@CallID,@Create_Date,@STATUS,'1900-01-01',@OrderNO,
					   @CardNO,@UsefulLife,@CVV2,@RegisterPhone,@CallerID,
					   @CertificateNO,getdate(),@CREATED_BY,getdate(),@CREATED_BY)	
		END 		

	--PRINT 'Contact Name: ' + @CallerID + ' ' +  @CalleeID
	FETCH NEXT FROM MyCursor
	INTO @CallID,@CallerID,@CalleeID,@CardNO,@CertificateNO,
		 @UsefulLife,@CVV2,@RegisterPhone,@OrderNO,
		 @Create_Date 
END

CLOSE MyCursor
DEALLOCATE MyCursor
GO

CREATE PROCEDURE [dbo].[usp_CheckEPOS_CARDPaymentReturn]
	@rowID int,
	@resultMsg	nvarchar(200) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @OrderNumber varchar(50)
	declare @CREATION_DATE varchar(50)
	declare @CANCEL_DATE varchar(50)
	declare @BatchNo varchar(50)

	set @resultMsg =''
	SELECT @OrderNumber = ORDER_NUMBER,@BatchNo=S_BATCHNO
	FROM EPOS_TRANS_PAYMENT 
	WHERE ROW_ID=@rowID
	
	SELECT @CREATION_DATE = CREATION_DATE FROM EPOS_TRANS_PAYMENT 
			  WHERE TRANS_TYPE='RETURN' AND RESULT='S' AND ORDER_NUMBER =@OrderNumber
	
	if @CREATION_DATE is not null or @CREATION_DATE=''
		set @resultMsg = '订单'+@OrderNumber+'在'+ @CREATION_DATE +'时间已成功取消刷卡，请选择其他记录取消刷卡。'
	else
		BEGIN 
			SELECT @CANCEL_DATE =S_POSTIME FROM EPOS_TRANS_PAYMENT 
					WHERE TRANS_TYPE= 'REVERSAL' AND RESULT='S' AND ORDER_NUMBER = @OrderNumber
			if @CANCEL_DATE is not null or @CANCEL_DATE=''
				set @resultMsg = '订单'+@OrderNumber+'在'+ @CREATION_DATE +'时间已成功取消刷卡，请选择其他记录取消刷卡。'
			else
				BEGIN
					if exists(SELECT 1 FROM EPOS_TRANS_PAYMENT 
								WHERE TRANS_TYPE='PAYMENT' AND RESULT='S' AND ORDER_NUMBER =@OrderNumber)
						BEGIN 
							if not exists(SELECT 1 FROM EPOS_TRANS_SETTLEMENT WHERE S_BATCHNO=@BatchNo AND RESULT='S')
								set @resultMsg = '订单'+@OrderNumber+'在'+ +'刷卡还未结算,请选择其它记录退款。'
						END
					else
						set @resultMsg = '订单'+@OrderNumber+'在'+ +'未做过刷卡,请选择其它记录退款。'
					
				END
		END

END
GO

CREATE PROCEDURE [dbo].[usp_GetEPOS_CARDQuery]
	@OrderNo	varchar(50),
        @CardStauts	varchar(50),
        @DateFrom	varchar(50),
        @DateEnd	varchar(50)
AS
BEGIN

declare @exeSql varchar(8000)
--W: 等待刷卡;
--S: 刷卡成功;
--E: 刷卡失败;
--D: 信息失效(未刷卡)
--F: 已刷卡


set @exeSql = 'SELECT EPOS_CARD.CC_NO,EPOS_CARD.CC_DATE,EPOS_CARD.STATUS,PARAMETER.pDescription AS STATUS_CN,
			   EPOS_CARD.LAST_PAY_TIME,EPOS_CARD.ORDER_NUMBER,EPOS_CARD.PAN,EPOS_CARD.EXPIREDDATE,EPOS_CARD.CVV2,
			   EPOS_CARD.CHMOBILE,EPOS_CARD.CALLMOBILE,EPOS_CARD.CHIDNUM,EPOS_CARD.CREATION_DATE,EPOS_CARD.CREATED_BY,
			   EPOS_CARD.LAST_UPDATE_DATE,EPOS_CARD.LAST_UPDATED_BY
			   FROM EPOS_CARD LEFT JOIN PARAMETER ON EPOS_CARD.STATUS=PARAMETER.pValue
		WHERE PARAMETER.pName=''EPOS_CARD_STATUS'' '

if(@OrderNo<>'')
set @exeSql=@exeSql+' AND ORDER_NUMBER='''+@OrderNo+''''

if(@CardStauts<>'')
set @exeSql=@exeSql+' AND STATUS='''+@CardStauts+''''

if(@DateFrom<>'')
set @exeSql=@exeSql+' AND CC_DATE>=cast('''+@DateFrom+''' as datetime)'

if(@DateEnd<>'')
set @exeSql=@exeSql+' AND CC_DATE<cast('''+@DateEnd+''' as datetime)'

print @exeSql
exec(@exeSql)
END
GO

CREATE  PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnByPosID]
		@PosID int
AS
BEGIN

declare @exeSql varchar(8000)

set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner,
               Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO,
               Credit.ocAttribute13,PAY.RETURN_CONFIRM,PAY.RETURN_CON_DATE,
               PAY.S_PAN,PAY.S_EXPIREDDATE,PAY.S_CHIDNUM,PAY.S_CHMOBILE,PAY.S_CVV2,
               PAY.S_AUTHORIZECODE,PAY.R_AUTHORIZECODE,PAY.S_TRANSAMOUNT,Credit.ocCardModth
			   FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit
			   WHERE PAY.ORDER_NUMBER=ORD.HSO 
			   AND PAY.ORDER_NUMBER =Credit.ocSO
               AND Credit.OCATTRIBUTE1<>''D''			
			   AND (PAY.S_BATCHNO IN(SELECT S_BATCHNO FROM EPOS_TRANS_SETTLEMENT 
                                     WHERE S_TRASUMAMT>0 AND RESULT=''S'') )'

if(@PosID>0)
   set @exeSql=@exeSql+' AND PAY.S_POSID =' + cast(@PosID as varchar)

--print @exeSql
exec(@exeSql)
END        
GO

CREATE PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnConfirmQuery]
		@OrderNo	varchar(50),
		@DateFrom	varchar(10),
		@DateEnd	    varchar(10),
		@ConfirmStatus   varchar(50)
AS
BEGIN

/*
@OrderNo	varchar(50),          -- 订单号
@DateFrom	varchar(10),          -- 退款开始日期
@Datend	    varchar(10),          -- 退款结束日期 
@ConfirmStatus   varchar(50)      -- 确认状态
*/

declare @exeSql varchar(8000)

set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner,
               Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO,
               Credit.ocAttribute13,
               case PAY.RETURN_CONFIRM when ''Y'' then ''已确认'' else ''未确认'' end as RETURN_CONFIRM,
               PAY.RETURN_CON_DATE
			   FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit 
			   WHERE PAY.ORDER_NUMBER=ORD.HSO 
			   AND PAY.ORDER_NUMBER =Credit.ocSO  
			   AND PAY.TRANS_TYPE=''RETURN'' AND PAY.RESULT=''S'' AND Credit.OCATTRIBUTE1<>''D'' '

if(@OrderNo<>'')
set @exeSql=@exeSql+' AND PAY.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''

if(@DateFrom<>'')
set @exeSql=@exeSql+' AND cast(Credit.ocAttribute13 as datetime)>=cast('''+ @DateFrom + ''' as datetime)'

if(@DateEnd<>'')
set @exeSql=@exeSql+' AND cast(Credit.ocAttribute13 as datetime)<cast('''+ @DateEnd + ''' as datetime)'

if(@ConfirmStatus<>'')
set @exeSql=@exeSql+' AND PAY.RETURN_CONFIRM='''+@ConfirmStatus+''''

--print @exeSql
exec(@exeSql)
END         
GO

CREATE PROCEDURE [dbo].[usp_GetEPOS_PaymentReturnQuery]
		@OrderNo	varchar(50),
		@DateFrom	varchar(10),
		@DateEnd	varchar(10),
		@ocActive   varchar(50)
AS
BEGIN

/*
@OrderNo	varchar(50),          -- 订单号
@DateFrom	varchar(10),          -- 结算开始日期
@Datend	    varchar(10),          -- 结算结束日期 
@ocActive   varchar(50)           -- 结算状态
*/

declare @exeSql varchar(8000)

set @exeSql = 'SELECT PAY.S_POSID,ORD.HSO,ORD.hCreateDate,ORD.hSum,Credit.ocCardOwner,
               Credit.ocAttribute11,Credit.ocAttribute14,PAY.S_BATCHNO,
               Credit.ocAttribute13,PAY.RETURN_CONFIRM,PAY.RETURN_CON_DATE
			   FROM EPOS_TRANS_PAYMENT PAY,OrderH ORD, OrderCredit Credit
			   WHERE PAY.ORDER_NUMBER=ORD.HSO 
			   AND PAY.ORDER_NUMBER =Credit.ocSO
               AND Credit.OCATTRIBUTE1<>''D''			
			   AND (PAY.S_BATCHNO IN(SELECT S_BATCHNO FROM EPOS_TRANS_SETTLEMENT 
                                     WHERE S_TRASUMAMT>0 AND RESULT=''S'') )'

--AND ORD.hstatus in(''WM'',''NS'',''PS'')  

if(@OrderNo<>'')
set @exeSql=@exeSql+' AND PAY.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''

if(@DateFrom<>'')
set @exeSql=@exeSql+' AND cast(Credit.ocAttribute14 as datetime)>=cast('''+ @DateFrom + ''' as datetime)'

if(@DateEnd<>'')
set @exeSql=@exeSql+' AND cast(Credit.ocAttribute14 as datetime)<cast('''+ @DateEnd + ''' as datetime)'

if(@ocActive<>'')
set @exeSql=@exeSql+' AND Credit.ocActive='''+@ocActive+''''

--print @exeSql
exec(@exeSql)
END        
GO

CREATE PROCEDURE [dbo].[usp_GetEPOS_TRANSQuery]
		@OrderNo	varchar(50),
		@DateFrom	varchar(50),
		@DateEnd	varchar(50),
		@BacthNo	varchar(50),
		@TransType	varchar(50),
		@TransStatus	varchar(50)
AS
BEGIN

declare @exeSql varchar(8000)
--W: 等待刷卡;
--S: 刷卡成功;
--E: 刷卡失败;
--D: 信息失效(未刷卡)
--F: 已刷卡

set @exeSql = 'SELECT OrderH.HSO,OrderH.hCreateDate,OrderCredit.ocCardOwner,
			   EPOS_TRANS_PAYMENT.S_POSTIME,EPOS_TRANS_PAYMENT.TRANS_TYPE,
               PARAMETER.pDescription AS TRANS_TYPE_CN,
			   EPOS_TRANS_PAYMENT.RESULT,
			   CASE EPOS_TRANS_PAYMENT.RESULT WHEN ''E'' THEN ''错误'' WHEN ''S'' THEN ''成功'' ELSE '''' END as RESULT_CN,
			   EPOS_TRANS_PAYMENT.S_BATCHNO		
			   FROM OrderH inner join OrderCredit 
			    ON OrderH.HSO =OrderCredit.ocSO inner join EPOS_TRANS_PAYMENT  
				ON EPOS_TRANS_PAYMENT.ORDER_NUMBER = OrderH.HSO 
				LEFT JOIN PARAMETER ON EPOS_TRANS_PAYMENT.TRANS_TYPE=PARAMETER.pValue
				WHERE PARAMETER.pName=''EPOS_TRANS_TYPE'' '

if(@OrderNo<>'')
set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.ORDER_NUMBER LIKE ''%'+@OrderNo+'%'''

if(@DateFrom<>'')
set @exeSql=@exeSql+' AND cast(EPOS_TRANS_PAYMENT.S_POSTIME as datetime)>=cast('''+ @DateFrom+''' as datetime) '

if(@DateEnd<>'')
set @exeSql=@exeSql+' AND cast(EPOS_TRANS_PAYMENT.S_POSTIME as datetime)<cast('''+ @DateEnd+''' as datetime)'

if(@BacthNo<>'')
set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.S_BATCHNO LIKE ''%'+@BacthNo+'%'''

if(@TransType<>'')
set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.TRANS_TYPE='''+@TransType+''''

if(@TransStatus<>'')
set @exeSql=@exeSql+' AND EPOS_TRANS_PAYMENT.RESULT='''+@TransStatus+''''

--print @exeSql
exec(@exeSql)
END
GO

CREATE PROCEDURE [dbo].[usp_UpdateConfirmEPOS_TRANS_PAYMENT]
(
@sPOSID Int, 
@sRETURN_CONFIRM varchar(50),
@sRETURN_CON_BY NVarChar(50)
)
as
update [EPOS_TRANS_PAYMENT] set RETURN_CONFIRM=@sRETURN_CONFIRM,
	   RETURN_CON_BY=@sRETURN_CON_BY,RETURN_CON_DATE=getdate()
    where S_POSID=@sPOSID
GO