USE BankDB
GO

--用例4：常规业务处理-----------------------------------------------------------------------------------------------------------

--统计银行资金流通余额和盈利结算---------------------------------
--资金流通率余额=总存入金额-总支取金额  假定 存款利率为千分之三，贷款利率为千分之八  盈利结算=总支取金额x0.008-总存入金额x0.003

DECLARE @inMoney decimal(10,2)
DECLARE @outMoney decimal(10,2)
SELECT @inMoney = SUM(TradeMoney) FROM TradeInfo WHERE TradeType = '存入'
SELECT @outMoney = SUM(TradeMoney) FROM TradeInfo WHERE TradeType = '支取'

PRINT '银行流通余额总计为：'+CONVERT(varchar(20),@inMoney-@outMoney)
PRINT '盈利结算为：'+CONVERT(varchar(20),@inMoney*0.008-@outMoney*0.003)
GO
--查询本周开户信息---------------------------------------------
SELECT CardNo AS 卡号,UserName AS 姓名,MoneyType AS 货币, DepositName AS 存款类型,
      AccountDate AS 开户日期,AccountMoney AS 开户金额,SaveMoney AS 存款余额,
      AccountState AS 账户状态
FROM CardInfo
   INNER JOIN Deposit ON CardInfo.DepositId = Deposit.DepositId
WHERE  DATEPART(yy,GETDATE())+DATEPART(wk,GETDATE()) = DATEPART(yy,AccountDate)+DATEPART(wk,AccountDate)
--DATEDIFF(dd,7,'2009-01-01') <= AccountDate AND  AccountDate<='2009-12-30'
--DATEDIFF(dd,7,GETDATE()) <= AccountDate AND  AccountDate<=GETDATE()
GO
--查询本月交易金额最高的卡号---------------------------------------------

SELECT CardNo,TradeMoney FROM TradeInfo WHERE TradeMoney = 
         (SELECT MAX(TradeMoney) FROM TradeInfo 
         WHERE DATEPART(yy,GETDATE())+DATEPART(wk,GETDATE()) = DATEPART(yy,TradeDate)+DATEPART(wk,TradeDate))
GO

--查询挂失客户------------------------------------------------------------

SELECT UserName AS 客户姓名, Telephone AS 联系电话
FROM CardInfo
WHERE  AccountState = '已挂失'
GO

--催款提醒业务------------------------------------------------------------

SELECT UserName AS 客户姓名, Telephone AS 联系电话,SaveMoney AS 存款余额
FROM CardInfo
WHERE  SaveMoney <= 200
GO

--用例5：创建、使用视图-----------------------------------------------------------------------------------------------------------

--输出银行客户记录vw_userInfo-----------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'vw_userInfo' )
   DROP VIEW vw_userInfo
GO
CREATE VIEW vw_userInfo
AS
   SELECT [UserName] AS 姓名,[IdentityCard] AS 身份证号, [Telephone] AS 电话号码,[Address] AS 居住地址
   FROM [UserInfo]
GO

SELECT * FROM vw_userInfo

--输出银行卡记录vw_cardInfo----------------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'vw_cardInfo' )
   DROP VIEW vw_cardInfo
GO
CREATE VIEW vw_cardInfo
AS
   SELECT CardNo AS 卡号,UserName AS 客户,MoneyType AS 货币, DepositName AS 存款类型,
         AccountDate AS 开户日期,SaveMoney AS 余额,[PassWord] AS 密码,
         AccountState AS 账户状态
   FROM CardInfo
      INNER JOIN Deposit ON CardInfo.DepositId = Deposit.DepositId
GO
SELECT * FROM vw_cardInfo

--输出银行卡交易记录vw_tradeInfo-----------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'vw_tradeInfo' )
   DROP VIEW vw_tradeInfo
GO
CREATE VIEW vw_tradeInfo
AS
   SELECT TradeDate AS 交易日期,TradeType AS 交易类型,CardNo AS 卡号,TradeMoney AS 交易金额, Remark AS 备注
   FROM TradeInfo
GO
SELECT * FROM vw_tradeInfo


--用例6：使用存储过程-----------------------------------------------------------------------------------------------------------

--实现存取款业务--------------------------------------------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_takeMoney' )
   DROP PROCEDURE usp_takeMoney
GO
CREATE PROCEDURE usp_takeMoney
   @cardNo nvarchar(20),
   @inMoney decimal(10,2),
   @password nvarchar(20) =NULL   --NULL就是存款 非NULL就是取款
AS
   DECLARE @n int
   SET @n = 0
   DECLARE @spare decimal(10,2)
   SELECT @spare=SaveMoney FROM CardInfo WHERE CardNo = @cardNo
   DECLARE @pswd nvarchar(20)
   SELECT @pswd = PassWord  FROM CardInfo WHERE CardNo = @cardNo
   IF(@password IS NOT NULL)
      BEGIN
         IF(@pswd <> @password)
            BEGIN                 
               RAISERROR('密码不符合',16,1)
               RETURN
            END
         IF(@inMoney > @spare-1)
            BEGIN
               RAISERROR('交易失败,余额不足',16,1)
               PRINT '卡号'+CAST(@cardNo AS nvarchar(20))+'余额'+CAST(@spare AS nvarchar(20))
               RETURN
            END
      END
   DECLARE @tempMoney decimal(10,2)
   BEGIN TRANSACTION
   PRINT '交易正进行,请稍后...'
   IF(@password IS NULL)--NULL存款
      BEGIN
         SET @tempMoney = @inMoney + @spare

         INSERT INTO TradeInfo(CardNo,TradeDate,TradeType,TradeMoney,SpareMoney)
            VALUES(@cardNo,DEFAULT,'存入',@inMoney,@tempMoney)
         SET @n = @n+@@ERROR
         UPDATE CardInfo SET SaveMoney = @tempMoney WHERE CardNo = @cardNo
         SET @n = @n+@@ERROR
      END
   ELSE --非NULL取款    
      BEGIN
         SET @tempMoney = @spare - @inMoney

         INSERT INTO TradeInfo(CardNo,TradeDate,TradeType,TradeMoney,SpareMoney)
            VALUES(@cardNo,DEFAULT,'支取',@inMoney,@tempMoney)
         SET @n = @n+@@ERROR
         UPDATE CardInfo SET SaveMoney = @tempMoney WHERE CardNo = @cardNo
         SET @n = @n+@@ERROR
      END
   IF (@n = 0)
      BEGIN
         PRINT '交易成功！交易金额：' + CAST(@inMoney AS nvarchar(20))
         PRINT '卡号'+CAST(@cardNo AS nvarchar(20))+'余额'+CAST(@tempMoney AS nvarchar(20))
         COMMIT TRANSACTION
      END
   ELSE
      BEGIN
         PRINT '交易失败'
         PRINT '卡号'+CAST(@cardNo AS nvarchar(20))+'余额'+CAST(@tempMoney AS nvarchar(20))
         ROLLBACK TRANSACTION
      END
GO

EXEC usp_takeMoney '1010357612121130',1000,'888888'


--产生随机卡号------------------------------------------------

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_randCardID' )
   DROP PROCEDURE usp_randCardID
GO
CREATE PROCEDURE usp_randCardID
   @randNo nvarchar(20) OUTPUT
AS
   DECLARE @r decimal(15,8)
   DECLARE @right nvarchar(10)
   SELECT @r = RAND(DATEPART(mm,GETDATE())*100000+DATEPART(ss,GETDATE())*1000+DATEPART(ms,GETDATE()))
   SET @right = RIGHT(@r,8)
   SET @randNo = '10103576'+@right
GO

DECLARE @myCardNo nvarchar(20)
EXEC usp_randCardID @myCardNo OUTPUT
PRINT @myCardNo

--完成开户业务------------------------------------------------

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_openAccount' )
   DROP PROCEDURE usp_openAccount
GO
CREATE PROCEDURE usp_openAccount
   @identityCard nvarchar(30),
   @userName nvarchar(30),
   @phone nvarchar(30),
   @address nvarchar(50),
   @accountStartMoney decimal(10,2),
   @depositName nvarchar(20)
AS
   DECLARE @depositId int
   SELECT @depositId = DepositId FROM Deposit WHERE DepositName=@depositName
   DECLARE @newCardNo nvarchar(20)
   EXEC usp_randCardID @newCardNo OUTPUT

   BEGIN TRANSACTION
   DECLARE @n int
   SET @n = 0
   --如果开户用户已存在 直接插入CardInfo表  否则先将信息插入到UserInfo表
   IF EXISTS(SELECT * FROM UserInfo WHERE IdentityCard=@identityCard)
      BEGIN
         INSERT INTO CardInfo(CardNo,UserName,IdentityCard,Telephone,[Address],DepositId,AccountMoney,SaveMoney)
            VALUES(@newCardNo,@userName,@identityCard,@phone,@address,@depositId,@accountStartMoney,@accountStartMoney)
         SET @n = @n + @@ERROR
      END
   ELSE
      BEGIN
         INSERT INTO UserInfo(IdentityCard,UserName,Telephone,[Address])
            VALUES(@identityCard,@userName,@phone,@address)
         SET @n = @n + @@ERROR
         INSERT INTO CardInfo(CardNo,UserName,IdentityCard,Telephone,[Address],DepositId,AccountMoney,SaveMoney)
            VALUES(@newCardNo,@userName,@identityCard,@phone,@address,@depositId,@accountStartMoney,@accountStartMoney)
         SET @n = @n + @@ERROR
      END

   IF (@n = 0)
      BEGIN
         PRINT '开户成功:'+@newCardNo
         PRINT '开户日期:'+CONVERT(varchar(30),GETDATE(),102)+'  开户金额:'+CONVERT(varchar(30),@accountStartMoney)
         COMMIT TRANSACTION
      END
   ELSE
      BEGIN
         PRINT '开户失败'
         ROLLBACK TRANSACTION
      END
GO
EXEC usp_openAccount '567891321242345799','zz','0752-43345223','asdasd',1,'活期'


--分页显示查询交易数据---------------------------------------------------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_pagingDisplay' )
   DROP PROCEDURE usp_pagingDisplay
GO
CREATE PROCEDURE usp_pagingDisplay
   @page int =1,     --指定的第几页
   @eachPage int =2   --每页多少条
AS
   SELECT TOP (@eachPage) TradeDate AS 交易日期,TradeType AS 交易类型,CardNo AS 卡号,TradeMoney AS 交易金额 
   FROM TradeInfo
   WHERE TradeId NOT IN 
   (
      SELECT TOP ((@page-1)*@eachPage) TradeId FROM TradeInfo ORDER BY TradeId ASC
   )
   ORDER BY TradeId ASC
GO
EXEC usp_pagingDisplay 1,4


--打印客户对账单------------------------------------------------------
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_CheckSheet' )
   DROP PROCEDURE usp_CheckSheet
GO
CREATE PROCEDURE usp_CheckSheet
   @cardNo nvarchar(20),
   @searchStartDate datetime =NULL,
   @searchEndDate datetime =NULL
AS
   DECLARE @name nvarchar(20)
   SELECT @name = UserName FROM CardInfo WHERE CardNo=@cardNo
   DECLARE @depositName nvarchar(20)
   SELECT @depositName = DepositName FROM Deposit WHERE DepositId = 
            (SELECT DepositId FROM CardInfo WHERE CardNo = @cardNo)
   DECLARE @date datetime
   SELECT @date = AccountDate FROM CardInfo WHERE CardNo = @cardNo
   PRINT '卡号:'+@cardNo
   PRINT '姓名:'+@name
   PRINT '货币:RMB'
   PRINT '储存类型:'+@depositName
   PRINT '开户日期:'+CONVERT(varchar(20),@date,102)
   IF(@searchStartDate IS NULL OR @searchEndDate IS NULL)
      BEGIN
         SELECT TradeDate AS 交易日期,TradeType AS 类型,
               TradeMoney AS 交易金额,Remark AS 备注
         FROM TradeInfo WHERE CardNo=@cardNo
      END
   ELSE
      BEGIN
         SELECT TradeDate AS 交易日期,TradeType AS 类型,
               TradeMoney AS 交易金额,Remark AS 备注
         FROM TradeInfo 
         WHERE CardNo=@cardNo AND TradeDate >= @searchStartDate
              AND TradeDate <= @searchEndDate
      END
GO
EXEC usp_CheckSheet '1010357612345678','2009-11-02','2009-11-04'


--统计未发生交易的账户----------------------------------------------

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_getWithoutTrade' )
   DROP PROCEDURE usp_getWithoutTrade
GO
CREATE PROCEDURE usp_getWithoutTrade
   @startDate datetime  =NULL,
   @endDate datetime =NULL
AS
   IF(@startDate IS NULL)
      BEGIN
         SET @startDate = '2009-11-01'
      END
   IF(@endDate IS NULL)
      BEGIN
         SET @endDate = GETDATE()
      END
   SELECT UserName AS 客户姓名,CardNo AS 身份证号,Telephone AS 电话,[Address] AS 地址
   FROM CardInfo
   WHERE CardNo NOT IN (SELECT CardNo FROM TradeInfo WHERE TradeDate BETWEEN @startDate AND @endDate)
GO

EXEC usp_getWithoutTrade

--统计银行卡交易量和交易额---------------------------------------------

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_getTradeInfo' )
   DROP PROCEDURE usp_getTradeInfo
GO
CREATE PROCEDURE usp_getTradeInfo
   @district nvarchar(20) =NULL,
   @startDate datetime  =NULL,
   @endDate datetime =NULL
AS
   IF(@startDate IS NULL)
      BEGIN
         SET @startDate = '2009-01-01'
      END
   IF(@endDate IS NULL)
      BEGIN
         SET @endDate = GETDATE()
      END
   PRINT '统计银行卡交易量和交易额'
   PRINT ''
   PRINT '起始日期：'+CONVERT(varchar(20),@startDate,102)+'  截止日期：'+CONVERT(varchar(20),@endDate,102)
   PRINT '-----------------------------------------------------------------------------------------'
   DECLARE @inCount int
   DECLARE @outCount int
   --DECLARE @sumCount int
   DECLARE @inMoney decimal(10,2)
   DECLARE @outMoney decimal(10,2)
   --DECLARE @spareMoney decimal(10,2)
   IF(@district IS NULL) 
      BEGIN--没选地区 统计所有的
         SELECT @inCount=COUNT(*) FROM TradeInfo 
            WHERE TradeType = '存入' AND TradeDate BETWEEN @startDate AND @endDate
         SELECT @inMoney=SUM(TradeMoney) FROM TradeInfo 
            WHERE TradeType = '存入' AND TradeDate BETWEEN @startDate AND @endDate
         SELECT @outCount=COUNT(*) FROM TradeInfo
            WHERE TradeType = '支取' AND TradeDate BETWEEN @startDate AND @endDate
         SELECT @outMoney=SUM(TradeMoney) FROM TradeInfo
            WHERE TradeType = '支取' AND TradeDate BETWEEN @startDate AND @endDate
      END 
   ELSE
      BEGIN
         SELECT @inCount=COUNT(*) FROM TradeInfo 
            WHERE TradeType = '存入' AND TradeDate BETWEEN @startDate AND @endDate
               AND CardNo IN (SELECT CardNo FROM CardInfo WHERE [Address] LIKE '%'+@district+'%')
         SELECT @inMoney=SUM(TradeMoney) FROM TradeInfo 
            WHERE TradeType = '存入' AND TradeDate BETWEEN @startDate AND @endDate
               AND CardNo IN (SELECT CardNo FROM CardInfo WHERE [Address] LIKE '%'+@district+'%')
         SELECT @outCount=COUNT(*) FROM TradeInfo
            WHERE TradeType = '支取' AND TradeDate BETWEEN @startDate AND @endDate
               AND CardNo IN (SELECT CardNo FROM CardInfo WHERE [Address] LIKE '%'+@district+'%')
         SELECT @outMoney=SUM(TradeMoney) FROM TradeInfo
            WHERE TradeType = '支取' AND TradeDate BETWEEN @startDate AND @endDate
               AND CardNo IN (SELECT CardNo FROM CardInfo WHERE [Address] LIKE '%'+@district+'%')
      END
   PRINT '存入笔数：'+CONVERT(varchar(10),@inCount)+'  存入金额：'+CONVERT(varchar(10),@inMoney)
   PRINT '支取笔数：'+CONVERT(varchar(10),@outCount)+'  支取金额：'+CONVERT(varchar(10),@outMoney)
   PRINT '-----------------------------------------------------------------------------------------'
   PRINT '发生笔数：'+CONVERT(varchar(10),@inCount+@outCount)+'  支取金额：'+CONVERT(varchar(10),@inMoney-@outMoney)
GO

EXEC usp_getTradeInfo

--用例7  利用事务实现较完整数据更新--------------------------------------------

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'usp_tradefer' )
   DROP PROCEDURE usp_tradefer
GO
CREATE PROCEDURE usp_tradefer
   @outCardNo nvarchar(20),
   @outCardPswd nvarchar(20),
   @outCardMoney decimal(10,2),
   @inCardNo nvarchar(20)
AS
   DECLARE @n int
   SET @n = 0
   BEGIN TRANSACTION
      PRINT '开始转账,请稍后....'
      EXEC usp_takeMoney @outCardNo,@outCardMoney,@outCardPswd
      SET @n=@n+@@ERROR
      EXEC usp_takeMoney @inCardNo,@outCardMoney
      SET @n=@n+@@ERROR
      PRINT '转账成功！'

      DECLARE @date datetime
      DECLARE @nowDate datetime
      SET @date = DATEADD(mi,-30,GETDATE())
      SET @nowDate = DATEADD(mi,10,GETDATE())
      PRINT '打印转出账户对账单'
      PRINT '--------------------------'
      EXEC usp_CheckSheet @outCardNo,@date,@nowDate
      SET @n=@n+@@ERROR
      PRINT '打印转入账户对账单'
      PRINT '--------------------------'
      EXEC usp_CheckSheet @inCardNo,@date,@nowDate
      SET @n=@n+@@ERROR

   IF (@n = 0)
      BEGIN
         COMMIT TRANSACTION
      END
   ELSE
      BEGIN
         ROLLBACK TRANSACTION
      END
GO

EXEC usp_tradefer '1010357612345678','123456',200,'1010357612121130'