📄 newaccbak.txt
字号:
CREATE PROCEDURE newCalAccount
(@ihand char(4),
@isex char(1),
@itime datetime,
@iiszd bit,
@ilftime datetime )
as
declare @lsleft bit
declare @lflf bit
declare @lsacctime datetime
declare @ktime datetime
declare @iAccount char(6)
declare @lswelid char(6)
declare @lssex char(1)
declare @lsroomno char(6)
declare @lsbegtime datetime
declare @lsendtime datetime
declare @lsisbj bit
declare @lsiszd bit
declare @lssanprice dec(10,2)
declare @lswomansanprice dec(10,2)
declare @lssanturntime integer
declare @lssancsprice dec(10,2)
declare @lsRoomprice dec(10,2)
declare @lsCsprice dec(10,2)
declare @lshalftime integer
declare @lsFulltime integer
declare @lsturntime integer
declare @lscstime integer
declare @lszdroomprice dec(10,2)
declare @lszdcsprice dec(10,2)
declare @lszdturntime integer
declare @lsunit integer
declare @lsyy integer
declare @lscs dec(10,2)
declare @mmmm dec(10,2)
declare @csmmmm dec(10,2)
declare @lscal bit
Begin
delete nowinls
select @mmmm=0,@csmmmm=0
select @iaccount=accno from nowin where (handno=@ihand and sex=@isex)
select @lssanprice=sanprice,@lswomansanprice=womansanprice,@lssanturntime=santurntime,@lssancsprice=sancsprice from syssetup
insert into nowinls select * FROM nowin where Accno=@iaccount
Declare Rmcursor CURSOR FOR SELECT welid,handno,isleft,begtime,acctime,Roomprice,truntime,isbj,istime,sex FROM nowinls where Accno=@iaccount
open Rmcursor
Fetch NEXT FROM Rmcursor INTO @lswelid,@lsroomno,@lsleft,@lsbegtime,@lsacctime,@lsRoomprice,@lsturntime,@lsisbj,@lsiszd,@lssex
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
Begin
execute GetRoomPara @lsroomno,
@lscstime output, @lsCsprice output, /* @lsroomprice output ,@lsturntime output,*/
@lshalftime output, @lsFulltime output,
@lszdroomprice output, @lszdCsprice output, @lszdturntime output
/* select @lsCsprice=@lsroomprice/10,@lszdCsprice=@lsroomprice/10,@lszdroomprice=@lsRoomprice*/
select @lssancsprice=@lsRoomprice
if @lsisbj =1
begin
select @lflf=0,@ktime=@itime
if (@lsroomno in (select roomno from endaccpara where sex=@lssex))
select @lflf=1,@ktime=@ilftime
if (@lsleft=1) and (@lsroomno not in (select roomno from endaccpara where sex=@lssex))
select @lflf=1,@ktime=@itime
if ((@lflf=1) and (@lsiszd=1)) or (@lflf=0 and ((@lsiszd=1)or(@iiszd=1))) /* zd */
begin
execute caltime @lsbegtime,@ktime,@lszdturntime,@lsunit output,@lsyy output
if (@lsunit=0)
begin
select @lsunit=1
select @lsyy=0
end
select @mmmm=@lsunit*@lszdroomprice,@csmmmm=@lszdcsprice*@lsyy
if (@lsacctime<@ktime) and (@lsleft=1) /* zd room left and have new money */
begin
execute caltime @lsbegtime,@lsacctime,@lszdturntime,@lsunit output,@lsyy output
if (@lsunit=0)
begin
select @lsunit=1
select @lsyy=0
end
select @mmmm=@mmmm-@lsunit*@lszdroomprice,@csmmmm=@csmmmm-@lszdcsprice*@lsyy
end
else
begin
if @lsleft=1
select @mmmm=0.00,@csmmmm=0.00
end
update nowin set money=@mmmm,csmoney=@csmmmm,endtime=@ktime
where welid=@lswelid
end
else /* else means not iszd */
begin
execute caltime @lsbegtime,@ktime,@lsturntime,@lsunit output,@lsyy output
if (@lsunit=0)
begin
select @lsunit=1
select @lsyy=0
end
select @lscs=0
if (@lsyy<@lsfulltime)
select @lscs=@lsyy*@lscsprice
if (@lsyy>=@lshalftime) and (@lsyy<@lsfulltime)
select @lscs=@lszdroomprice
if (@lsyy>=@lsfulltime)
select @lsunit=@lsunit+1
/* select @lscs=@lsroomprice */
select @mmmm=@lsunit*@lsroomprice,@csmmmm=@lscs
select mm=@mmmm,cs=@csmmmm
if (@lsacctime<@ktime) and (@lsleft=1) /* room left and have new money */
begin
execute caltime @lsbegtime,@lsacctime,@lsturntime,@lsunit output,@lsyy output
if (@lsunit=0)
begin
select @lsunit=1
select @lsyy=0
end
select @lscs=0
if (@lsyy<@lsfulltime)
select @lscs=@lsyy*@lscsprice
if (@lsyy>=@lshalftime) and (@lsyy<@lsfulltime)
select @lscs=@lszdroomprice
if (@lsyy>=@lsfulltime)
select @lsunit=@lsunit+1
/* select @lscs=@lsroomprice */
select @mmmm=@mmmm-@lsunit*@lsroomprice,@csmmmm=@csmmmm-@lscs
end
else
begin
if @lsleft=1
select @mmmm=0.00,@csmmmm=0.00
end
select mm=@mmmm,cs=@csmmmm
update nowin set money=@mmmm,csmoney=@csmmmm,endtime=@ktime
where welid=@lswelid
end
end
else /* mean san */
begin
select @lflf=0,@ktime=@itime
if (@lsroomno in (select roomno from endaccpara where sex=@lssex))
select @lflf=1,@ktime=@ilftime
if (@lsleft=1) and (@lsroomno not in (select roomno from endaccpara where sex=@lssex))
select @lflf=1,@ktime=@itime
execute caltime @lsbegtime,@ktime,@lssanturntime,@lsunit output,@lsyy output
if @lsunit=0
begin
select @lsunit=1
select @lsyy=0
end
if @lssex='0'
select @mmmm=@lsunit*@lswomansanprice,@csmmmm=@lssancsprice*@lsyy
if @lssex='1'
select @mmmm=@lsunit*@lssanprice,@csmmmm=@lssancsprice*@lsyy
if @lssex='2'
select @mmmm=@lsunit*@lsroomprice,@csmmmm=@lscs
if @lssex='3'
select @mmmm=0,@csmmmm=0
if (@lsacctime<@ktime) and (@lsleft=1) /* san left and have money */
begin
execute caltime @lsbegtime,@lsacctime,@lssanturntime,@lsunit output,@lsyy output
if @lsunit=0
begin
select @lsunit=1
select @lsyy=0
end
if @lssex='0'
select @mmmm=@mmmm-@lsunit*@lswomansanprice,@csmmmm=@csmmmm-@lssancsprice*@lsyy
if @lssex='1'
select @mmmm=@mmmm-@lsunit*@lssanprice,@csmmmm=@csmmmm-@lssancsprice*@lsyy
end
else
begin
if @lsleft=1
select @mmmm=0.00,@csmmmm=0.00
end
update nowin set money=@mmmm,csmoney=@csmmmm,endtime=@ktime
where welid=@lswelid
end
end
Fetch NEXT FROM Rmcursor INTO @lswelid,@lsroomno,@lsleft,@lsbegtime,@lsacctime,@lsroomprice,@lsturntime,@lsisbj,@lsiszd,@lssex
End
close Rmcursor
DEALLOCATE Rmcursor
delete nowinls
Declare cRmcursor CURSOR FOR SELECT handno,begtime,endtime,isbj,istime,sex,cal,roomprice,turntime FROM changeroom where Accno=@iaccount
open cRmcursor
Fetch NEXT FROM cRmcursor INTO @lsroomno,@lsbegtime,@lsendtime,@lsisbj,@lsiszd,@lssex ,@lscal,@lsroomprice,@lsturntime
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
Begin
if @lscal=1
begin
execute GetRoomPara @lsroomno,
@lscstime output, @lsCsprice output,
@lshalftime output, @lsFulltime output,
@lszdroomprice output, @lszdCsprice output, @lszdturntime output
/* select @lsCsprice= @lsroomprice/10, @lszdCsprice=@lsroomprice/10 */
select @lssancsprice=@lsroomprice
if @lsisbj =1
begin
if (@lsiszd=1) or (@iiszd=1)
begin
execute caltime @lsbegtime,@lsendtime,@lszdturntime,@lsunit output,@lsyy output
if @lsunit=0
begin
select @lsunit=1
select @lsyy=0
end
update changeroom set money=@lsunit*@lszdroomprice,
csmoney=@lszdcsprice*@lsyy
where current OF cRmcursor
end
else
begin
execute caltime @lsbegtime,@lsendtime,@lsturntime,@lsunit output,@lsyy output
if @lsunit=0
begin
select @lsunit=1
select @lsyy=0
end
select @lscs=0
if (@lsyy<=@lsfulltime) and ( @lsyy>@lscstime)
select @lscs=@lscsprice
if (@lsyy>@lshalftime) and (@lsyy<=@lsfulltime)
select @lscs=@lszdroomprice
if (@lsyy>@lsfulltime)
select @lsunit=@lsunit+1
/* select @lscs=@lsroomprice */
update changeroom set money=@lsunit*@lsroomprice,
csmoney=@lscs
where current OF cRmcursor
end
end
else /* else mean not isbj */
begin
execute caltime @lsbegtime,@lsendtime,@lssanturntime,@lsunit output,@lsyy output
if @lsunit=0
begin
select @lsunit=1
select @lsyy=0
end
if @lssex='0'
begin
update changeroom set money=@lsunit*@lswomansanprice,
csmoney=@lssancsprice*@lsyy
where current OF cRmcursor
end
if @lssex='1'
begin
update changeroom set money=@lsunit*@lssanprice,
csmoney=@lssancsprice*@lsyy
where current OF cRmcursor
end
end
end
else
update changeroom set money=0.00,csmoney=0.00 where current OF cRmcursor
end
Fetch NEXT FROM cRmcursor INTO @lsroomno,@lsbegtime,@lsendtime,@lsisbj,@lsiszd,@lssex ,@lscal,@lsroomprice,@lsturntime
End
close cRmcursor
DEALLOCATE cRmcursor
end
GO
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -