银行ATM存取款机

--修改客户密码
update cardinfo set pass='123456'
where cardid='1010 3576 1234 5678'
update cardinfo set pass='123123'
where cardid='1010 3576 1212 1134'
--申请挂失
update cardinfo set IsReportLoss='true'
where cardid='1010 3576 1212 1134'
--盈利计算
declare @money money
declare @zhichu money
select @money=SUM(tradeMoney)from tradeinfo
where tradeType='存入'
print @money
select @zhichu=SUM(tradeMoney)from tradeinfo
where tradetype='支取'
print @zhichu
print'银行流通余额总计为:'+convert(varchar(20),@money-@zhichu)+'RMB'
print '盈利结算为:'+convert(varchar(20),@money*0.008-@zhichu*0.003)+'RMB'
--开户信息

declare @day int
select @day=DATEPART(DW,GETDATE())
print @day
select * from cardinfo where DateDiff(dd,opendate,getdate())<=@day

--本月交易金额最高
declare @month int
select @month=DATEPART(DD,GETDATE())
print @month

select * from cardinfo
where cardid=(
select cardid from tradeinfo
where trademoney=(select MAX(trademoney) from tradeinfo ) and 
DATEDIFF(DD,tradedate,GETDATE())<@month
)
--查询挂失客户
select ui.customerID ,ui.customerName,ui.PID,ui.telephone,ui.address,ci.IsReportLoss from userinfo as ui,cardinfo as ci
where ui.customerID=ci.customerID and ci.IsReportLoss='true'
--催款提醒业务


select ui.customerName,ui.telephone,ci.balance from userinfo as ui,cardinfo as ci
where ui.customerID=ci.customerID and ci.cardid in(
select cardid from cardinfo
where balance<200)


--创建视图
go
create view vw_userInfo
as
SELECT     customerID as 客户编号, customerName as 开户名,PID as 身份证号, telephone as 电话号码, address as 居住地址
FROM         dbo.userInfo

go
create VIEW vw_cardInfo  --银行卡信息表视图
  AS 
    select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期,
       c.balance as 余额,c.pass 密码,
       case c.IsReportLoss when 0 then '挂失'
                           when 1 then '正常'
       end as 是否挂失
    from cardInfo c, deposit d,userinfo u
    where c.savingID=d.savingID and c.customerID=u.customerID
GO
create VIEW vw_tradeInfo  --交易信息表视图
  AS 
    select tradeDate as 交易日期,tradeType as 交易类型, cardID as 卡号,tradeMoney as 交易金额,
      remark as 备注  from tradeInfo 
GO

--
--
--
if exists (select * from sysobjects where name = 'usp_takeMoney')
	drop proc usp_takeMoney
go
create procedure usp_takeMoney 
  @card char(19),
  @m money,
  @type char(4),
  @inputPass char(6)=''
 AS
   print '交易正进行,请稍后......'
   if (@type='支取')
      if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass )
         begin
           raiserror ('密码错误!',16,1)
           return -1
         end

    DECLARE @mytradeType char(4),@outMoney MONEY,@myCardID char(19)
    SELECT @mytradeType=tradeType,@outMoney=tradeMoney ,@myCardID=cardID FROM tradeInfo where cardID=@card
    DECLARE @mybalance money
    SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
    if (@type='支取') 
       if (@mybalance>=@m+1)
           update cardInfo set balance=balance-@m WHERE cardID=@myCardID
       else
          begin
            raiserror ('交易失败！余额不足！',16,1)
            
            print '卡号'+@card+'  余额：'+convert(varchar(20),@mybalance)   
            return -2
          end
    else
         update cardInfo set balance=balance+@m WHERE cardID=@card

    print '交易成功！交易金额：'+convert(varchar(20),@m)
    SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
    print '卡号'+@card+'  余额：'+convert(varchar(20),@mybalance) 
	INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(@type,@card,@m) 
    RETURN 0
GO
--调用存储过程取钱或存钱 张三取300，李四存500
 --现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON 
   cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC usp_takeMoney @card,10 ,'支取','123456' 
GO

select * from cardInfo Inner Join userInfo ON 
   cardInfo.customerID=userInfo.customerID where customerName='张三'

declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON 
   cardInfo.customerID=userInfo.customerID where customerName='李四'
EXEC usp_takeMoney @card,500 ,'存入'
select * from vw_cardInfo
select * from vw_tradeInfo
GO


/*--2.产生随机卡号的存储过程(一般用当前月份数\当前秒数\当前毫秒数乘以一定的系数作为随机种子) --*/
if exists (select * from sysobjects where name = 'usp_randCardID')
	drop proc usp_randCardID
go
create procedure usp_randCardID @randCardID char(19) OUTPUT
  AS
    DECLARE @r numeric(15,8) 
    DECLARE @tempStr  char(10)
    SELECT  @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )
                  + DATEPART(ms, GETDATE()) )
    set @tempStr=convert(char(10),@r) --产生0.xxxxxxxx的数字,我们需要小数点后的八位数字 
    set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)  --组合为规定格式的卡号
GO
--测试产生随机卡号
DECLARE @mycardID char(19) 
EXECUTE usp_randCardID @mycardID OUTPUT
print '产生的随机卡号为：'+@mycardID
GO