银行ATM机存取款

	--创建数据库
use master
if exists(select * from sysdatabases where name='BankDB')
drop database BankDB

create database BankDB
on primary(
name='BankDB_data',
filename='D:\project\BankDB\BankDB_data.mdf',
size=5mb,
maxsize=10mb,
filegrowth=15%
)
log on(
name='BankDB_log',
filename='D:\project\BankDB\BankDB_log.ldf',
size=5mb,
maxsize=10mb,
filegrowth=15%
)
--创建用户表
if exists(select * from sysdatabases where name='userInfo')
drop table userInfo

create table userInfo(
customerID int identity(1,1),--编号
customerName char(20) not null,--姓名
PID char(20) not null,--身份证号
telephone char(20) not null,--联系电话
address varchar(50),--住址
)



alter table userInfo
add constraint PK_customerID primary key(customerID)

alter table userInfo
add constraint UQ_PID UNIQUE(PID)

alter table userInfo
add constraint CK_PID check(len(PID)=18 or len(PID)=15)

alter table userInfo
add constraint CK_telephone check(telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
--创建银行卡信息表
if exists(select * from sysdatabases where name='cardInfo')
drop table cardInfo

create table cardInfo(
cardID char(19) not null,--卡号
curID varchar(10) not null,--币种
savingID int not null,--存款类型
openDate datetime  not null,--开户日期
openMoney money not null,--开户金额
balance money not null,--余额
pass char(6) not null,--密码
IsReportLoss bit not null,--是否挂失
customerID int not null--用户编号
)

--约束
alter table cardInfo
add constraint PK_cardID  primary key(cardID)

alter table cardInfo
add constraint CK_cardID check(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')

alter table cardInfo
add constraint DF_curID  DEFAULT('RMB') FOR curID

alter table cardInfo
add constraint  DF_openDate  DEFAULT(getdate()) FOR openDate

alter table cardInfo
add constraint CK_openMoney check(openMoney>1)

alter table cardInfo
add constraint CK_balance check(balance>=1)

alter table cardInfo
add constraint	CK_pass check(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

alter table cardInfo
add constraint DF_pass  DEFAULT('888888') FOR pass

alter table cardInfo
add constraint DF_IsReportLoss DEFAULT(0) FOR IsReportLoss

alter table cardInfo
add constraint  FK_customerID foreign key(customerID)  references userInfo(customerID)

alter table cardInfo
add constraint  FK_savingID  foreign KEY(savingID)references deposit(savingID)

--创建交易信息表
if exists(select * from sysdatabases where name='tradeInfo')
drop table tradeInfo

create table tradeInfo(
tradeDate datetime not null,--交易日期
tradeType char(4) not null,--交易类型
cardID char(19) not null,--卡号
tradeMoney money not null,--交易金额
remark text --备注
)

--约束
alter table tradeInfo
add constraint CK_tradeType check(tradeType IN ('存入','支取'))

alter table tradeInfo
add constraint FK_cardID  FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)

alter table tradeInfo
add constraint CK_tradeMoney check(tradeMoney>0)

alter table tradeInfo
add constraint DF_tradeDATE DEFAULT(getdate()) FOR tradeDate



--创建存款类型表
if exists(select * from sysdatabases where name='Deposit')
drop table Deposit

create table Deposit(
savingID int identity(1,1),--存款类型
savingName varchar(20) not null,--类型名称
descrip varchar(50) 
)
--约束
alter table Deposit
add constraint PK_savingID primary key(savingID)




--添加数据

INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')

select * from deposit 



INSERT INTO userInfo(customerName,PID,telephone,address)
     VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1234 5678',1,1000,1000,1)

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1134',2,2000,1,2)

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1130',2,3000,1,3)

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('丁六','567891321242345618','0752-43345543')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1004',2,4000,1,4)

select * from userInfo
SELECT * FROM cardInfo

INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010 3576 1212 1134',5000)  
	  INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('支取','1010 3576 1212 1130',1900) 
--用例4
--修改
update cardInfo set pass=123456 where customerID in (
select customerID from userInfo where customerName='张三'
)


update cardInfo set pass=123123 where customerID in(
select customerID from userInfo where customerName='李四'
)
--挂失
update cardInfo set IsReportLoss=1 where customerID in(
select customerID from userInfo where customerName='李四'
)

select cardID as 卡号, curID as 货币, openDate as 开户日期, openMoney as 开户金额, balance as 余额, pass as 密码, IsReportLoss=case IsReportLoss   when 0 then  '未挂失' else '挂失'end ,customerName as 姓名 from cardInfo,userInfo
where cardInfo.customerID=userInfo.customerID

--检查流通
declare @num money
declare @sum money
select @num=sum(tradeMoney) from tradeInfo where tradeType='存入'
select @sum=sum(tradeMoney) from tradeInfo where tradeType='支出'
print '资金流通总金额为'+convert(nvarchar(20),@num-@sum)
print '银行盈利结算'+convert(nvarchar(20),@sum*0.008-@num*0.003)
--查询本周开户信息
select cardID as 卡号, curID as 货币, openDate as 开户日期, openMoney as 开户金额, balance as 余额, pass as 密码, IsReportLoss=case IsReportLoss   when 0 then  '未挂失' else '挂失'end ,customerName as 姓名 from cardInfo,userInfo
where cardInfo.customerID=userInfo.customerID
and DATEDIFF(WK,openDate,GETDATE())=0
--查询本月交易最高的卡号
declare @id int
select top 1 @id =tradeMoney from tradeInfo where tradeType='存入' order by tradeMoney desc
select * from cardInfo 
where cardID in (select cardID from tradeInfo where tradeMoney=@id)
and DATEDIFF(MM,openDate,GETDATE())=0
declare
@e int
select top(1) @e=tradeMoney from tradeInfo where tradeType='支取' order by tradeMoney desc
select * from cardInfo
where cardID in (select cardID from tradeInfo where tradeMoney=@e) 
and DATEDIFF(MM,openDate,GETDATE())=0

--查询挂失客户
select  savingName, descrip,cardID, curID, openDate, openMoney, balance, pass, customerName, telephone, address from userInfo
inner join cardInfo on userInfo.customerID=cardInfo.customerID
inner join Deposit on cardInfo.savingID=Deposit.savingID
and IsReportLoss=1
--催款业务
select customerName as 客户姓名,telephone as 客户电话,balance from  userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID
and balance<200
--用例5  创建使用视图
create view vw_userInfo
as
select customerid as 客户编号,customerName as 客户姓名,PID as 身份证号,telephone as 电话号,address as 地址 from userInfo

create view vw_cardInfo
as
select cardID as 卡号,customerName as 客户 ,curID as 货币种类, savingName as 存款类型, openDate as 开户日期, balance as 余额, pass as 密码, IsReportLoss=case IsReportLoss when 0 then '未挂失' else '挂失' end  from userInfo
inner join cardInfo  on userInfo.customerID=cardInfo.customerID
inner join Deposit on cardInfo.savingID=Deposit.savingID


create view vw_tradeInfo
as
   select tradeDate as 交易日期, tradeType as 交易类型, cardID as 卡号, tradeMoney as 交易金额, remark as 备注 from tradeInfo

--用例6
create proc usp_takeMoney
 @mima char(6) output,
 @money money output
 as
 declare @antionName  char(8)
 select @antionName=customerName from userInfo where customerID=(select customerID from cardInfo where pass=@mima)
 if(@antionName='张三')
 begin
 declare @saveMoney money,@int int,@getMoney money
 select  @saveMoney=balance from cardInfo where pass=@mima
 if(@saveMoney-@mima>1)
 begin
 update cardInfo set balance-=@money where pass=@mima
 select @int =cardid from cardInfo where pass=@mima
 select @getMoney=balance from cardInfo where pass=@mima

 print '交易成功!交易金额'+@money
 print '卡号'+@int+'余额'+@getMoney
 end
 else
 begin
 print '交易失败，余额不足'
 end
 end
 if(@antionName='李四')
 begin
 update cardInfo set balance+=@money where pass=@mima
 end

 declare @ff char(6),@thisMoney money
   select @ff=123456,@thisMoney =300
   exec usp_takeMoney @ff,@thisMoney 

--2
create proc usp_randCardID
@cardId char(19) output
as
declare @r numeric(15,8),@randCardID char(19)
select @r=rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()))
print @r
set @randCardID='1010'+' '+'3576'+' '+SUBSTRING(CONVERT(nvarchar,@r),3,4)+' '+SUBSTRING(CONVERT(nvarchar,@r),7,5)
set @cardId=@randCardID

declare @mycardId char(19)
execute usp_randCardID @mycardId output
print '产生的随机卡号为：'+@mycardId

--3
 create proc usp_openAccount
 @a nvarchar(50) output,
 @b nvarchar(50) output,
 @c nvarchar(50) output,
 @d money output,
 @e nvarchar(50) output,
 @f nvarchar(50) output,
 @g datetime output,
 @cardId char(19) output
 as
 if(@a<>null and @b<>null and @c<>null and @d<>null and @f<>null)
 begin
 declare @rr numeric(15,8),@ss char(19),@ww int,@yy int
 select @rr=RAND((DATEPART(mm,GETDATE())*100000)+(DATEPART(SS,GETDATE())*1000)+DATEPART(ms,GETDATE()))
 print @rr
 set @ss='1010'+' '+'3576'+' '+substring(convert(nvarchar,@rr),3,4)+' '+substring(convert(nvarchar,@rr),7,5)
 set @cardId=@ss
 
 insert into userInfo values(@a,@b,@c,@f)
 select @ww=customerID from userInfo where customerName=@a
 select @yy=savingID from Deposit where savingName=@e
 insert into cardInfo values (@cardId,'RMB',@yy,GETDATE(),@d,@d,'888888',0,@ww)
 end


declare
@aa nvarchar(50),
@bb nvarchar(50),
@cc nvarchar(50),
@dd money , 
@ee nvarchar(50),
@ff nvarchar(50),
@gg datetime ,
@cardIds char(19)
select @aa='赵二',@bb='213445678912342222',@cc='0760-44446666',@dd=1000,@ee='活期',@ff=' ',@gg=getdate()
exec usp_openAccount @aa output,@bb output,@cc output ,@dd output ,@ee output ,@ff output ,@gg output,@cardIds output
print '尊敬的用户，开户成功！系统为你产生的随机卡号为'+@cardIds+'开户日期为：'+convert(nvarchar,@gg)+'开户金额为：'+convert(nvarchar,@dd)



--4
create proc usp_pagingDisplay
@sum int output,
@num int output
as
select tradeDate as 交易日期,tradeType as 交易类型,cardid as 卡号,tradeMoney as 交易金额 from (
select * , row_number() over(order by tradeDate) as myDate  from tradeInfo) as temp
where myDate between @sum and @num

exec usp_pagingDisplay 1,4

--5
create proc usp_CheckSheet
   @aaa char(19),
   @bbb datetime ,
   @ccc datetime
   as
   declare
   @qq nvarchar(50),
   @tt nvarchar(50),
   @nn nvarchar(50),
   @mm datetime
   
   select @qq=customerName from userInfo where customerID=(select customerID from cardInfo where cardID=@aaa)
   select @tt=savingName from Deposit where savingID=(select savingID from cardInfo where cardID=@aaa)
   select @nn=curID,@mm=openDate from cardInfo where cardID=@aaa
   print '卡号'+@aaa
   print '姓名'+@qq
   print '货币'+@tt
   print '存款类型'+@nn
   print '开户日期'+CONVERT(nvarchar,@mm)
   select tradeDate as 交易日, tradeType as 类型, tradeMoney as 交易金额, remark as 备注 from tradeInfo where cardID=@aaa and DATEDIFF(dd,@bbb,@ccc)>0

   exec usp_CheckSheet '1010 3576 1212 1134','2016-08-12','2017-08-21'

--6
create proc usp_getWithoutTrade
   @a datetime,
   @b datetime
   as
   declare @ll nvarchar(50),@kk int,@vv int 
   select @ll=cardID from cardInfo where cardID not in
   (
   select cardID from tradeInfo where tradeDate>@a and tradeDate<@b
   )
   select customerID as 客户号, customerName as 客户姓名, PID as 身份证号, telephone as 电话, address as 地址 from userInfo where customerID in (select customerID from cardInfo where cardID=@ll)
   select @kk=count(*),@vv=sum(balance) from cardInfo where cardID=@ll
   print '统计未发生交易的客户'
   print '-----------------------------------'
   print '客户人数'+CONVERT(nvarchar,@kk)+'客户总余额'+CONVERT(nvarchar,@vv)

   declare
   @jj datetime
   set @jj=getdate()
   exec usp_getWithoutTrade '2016-08-01',@jj
--用例7

  alter proc usp_tradefer
  @a nvarchar(50),
  @b nvarchar(50),
  @c money
  as
  begin transaction
  declare
  @a1 money,@a2 nvarchar(50),@a3 nvarchar(50),@a4 nvarchar(50),@a5 nvarchar(50) 
  print '开始转账，请稍后......'
  print '交易正在进行，请稍后....'
  update cardInfo set balance-=@c where cardID=@a
  select @a1=balance from cardInfo where cardID=@a
  print '交易成功！交易金额'+CONVERT(nvarchar,@c)
  print '卡号'+@a+'余额'+CONVERT(nvarchar,@a1)
  insert into tradeInfo (tradeDate, tradeType, cardID, tradeMoney) values(GETDATE(),'支取',@a,@c)
  print '交易正在进行，请稍后......'
  update cardInfo set balance+=@c where cardID=@b
  select @a1=balance from cardInfo where cardID=@b
  print '交易成功！交易金额'+CONVERT(nvarchar,@c)
  print '卡号'+@b+'余额'+CONVERT(nvarchar,@a1)
  insert into tradeInfo (tradeDate, tradeType, cardID, tradeMoney) values(GETDATE(),'存入',@b,@c)
  print '转账成功！'
  select  @a2=customerName,@a3=curID,@a4=savingName,@a5=openDate from cardInfo,Deposit,userInfo where userInfo.customerID=cardInfo.customerID and cardInfo.savingID=Deposit.savingID and cardID=@a
  print '打印转出账户对账单'
  print '-------------------------------'
  print '卡号:'+@a
  print '姓名:'+@a2
  print '货币:'+@a3
  print '存款类型:'+@a4
  print '开户日期:'+CONVERT(nvarchar,@a5)
  select tradeDate as 交易日, tradeType as 类型, cardID as卡号, tradeMoney as 交易金额, remark as备注 from tradeInfo where cardID=@a and tradeMoney=@c and tradeType='支取'
  select  @a2=customerName,@a3=curID,@a4=savingName,@a5=openDate from cardInfo,Deposit,userInfo where userInfo.customerID=cardInfo.customerID and cardInfo.savingID=Deposit.savingID and cardID=@b
  print '打印转入账户对账单'
  print '-------------------------------'
  print '卡号:'+@b
  print '姓名:'+@a2
  print '货币:'+@a3
  print '存款类型:'+@a4
  print '开户日期:'+CONVERT(nvarchar,@a5)
  select tradeDate as 交易日, tradeType as 类型, cardID as卡号, tradeMoney as 交易金额, remark as备注 from tradeInfo where cardID=@b and tradeMoney=@c and tradeType='存入'
  rollback transaction

  exec usp_tradefer '1010 3576 1212 1134','1010 3576 1234 5678',2000