CREATE PROCEDURE Sp_fygz_a @rcsj DATETIME,
@ccsj DATETIME,
@sfbz INTEGER,
@sfje NUMERIC(18, 2) OUTPUT,
@result INTEGER OUTPUT,
@tcsj INTEGER OUTPUT
AS
DECLARE @qssj VARCHAR(4),
@zzsj VARCHAR(4),
@strrcsj VARCHAR(4),
@strccsj VARCHAR(20),
@sjc INTEGER,
@temptcsj INTEGER,
@maxqssj INTEGER,
@minqssj INTEGER,
@signstart INTEGER,
@signend INTEGER,
@signsjc INTEGER,
@signid INTEGER,
@signzgxe NUMERIC(18, 2),
@tempzzd INTEGER,
@tempqsd INTEGER,
@tempdw INTEGER,
@tempdj NUMERIC(18, 2),
@tempid INTEGER,
@templx BIT,
@tempsfje NUMERIC(18, 2),
@Fid INTEGER
SELECT @qssj = Min(qssj),
@zzsj = Max(zzsj)
FROM sfsjfdb
WHERE sflx = @sfbz
SET @tcsj=Datediff(MINUTE, @rcsj, @ccsj)
SET @temptcsj=@tcsj
EXEC Sp_calcstrdatediff
@qssj,
@zzsj,
@sjc OUTPUT ----------计算周期时间
EXEC Sp_datetimetostr
@rcsj,
@ccsj,
@strrcsj OUTPUT,
@strccsj OUTPUT -----------将时间格式转换为4位字符格式
--if datediff(second,@rcsj,@ccsj)<=60
--begin
-- @tcsj=1
--@sjc=1
--end
-------计算一共几个收费周期，并且计算这些周期内费用
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET @sfje=0
select @signzgxe=zgxe
FROM sflxb
WHERE id = @sfbz
WHILE @temptcsj >= @sjc
BEGIN
SET @sfje=@sfje + (SELECT zgxe
FROM sflxb
WHERE id = @sfbz)
SET @temptcsj=@temptcsj - @sjc
IF @temptcsj < @sjc
SET @strccsj=Substring('0000' + Cast(@strccsj - ( 100 * @sjc / 60 ) AS VARCHAR(4)), Len('0000' + Cast(@strccsj - ( 100 * @sjc / 60 ) AS VARCHAR(4))) - 3, 4)
ELSE
SET @strccsj=@strccsj - ( 100 * @sjc / 60 )
END
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempsfsjfdb
(
[id] [INT] NOT NULL,
[sflx] [INT] NULL,
[qssj] [INT] NULL,
[zzsj] [INT] NULL,
[zgxe] [NUMERIC](18, 2) NULL,
[rcmf] [BIT] NULL,
[rcmfsj] [INT] NULL,
[ccmf] [BIT] NULL,
[ccmfsj] [INT] NULL,
[Fid] [INT] IDENTITY (1, 1) NOT NULL
)
CREATE TABLE #tempsfbzb
(
[id] [INT] NOT NULL,
[sfsjd] [INT] NULL,
[lx] [BIT] NULL,
[zzd] [INT] NULL,
[dj] [NUMERIC](18, 2) NULL,
[dw] [INT] NULL
)
INSERT INTO #tempsfsjfdb
SELECT id,
sflx,
( qssj - 2400 ),
( zzsj - 2400 ),
zgxe,
rcmf,
rcmfsj,
ccmf,
ccmfsj
FROM sfsjfdb
WHERE sflx = @sfbz
UNION
SELECT id,
sflx,
( qssj - 0 ),
( zzsj - 0 ),
zgxe,
rcmf,
rcmfsj,
ccmf,
ccmfsj
FROM sfsjfdb
WHERE sflx = @sfbz
UNION
SELECT id,
sflx,
( qssj + 2400 ),
( zzsj + 2400 ),
zgxe,
rcmf,
rcmfsj,
ccmf,
ccmfsj
FROM sfsjfdb
WHERE sflx = @sfbz
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------根据实际时间缩小计算范围
SELECT @minqssj = Max(qssj)
FROM #tempsfsjfdb
WHERE qssj <= @strrcsj
SELECT @maxqssj = Max(qssj)
FROM #tempsfsjfdb
WHERE qssj <= @strccsj
DELETE FROM #tempsfsjfdb
WHERE qssj < @minqssj
OR qssj > @maxqssj
SET @tempqsd=0
SET @tempsfje=0
declare
@tempsfsjfdcount int,
@temptcsj1 integer,
@tempzzsj1 varchar(4),
@temptcsj2 integer,
@Je1 numeric(18,2),
@je2 numeric(18,2)
----------------------------判断相离、相交、包含-----------------------------
--------------------1、判断相离，条件为#tempsfsjfdb 数量=1为相离------------
--------------------2、判断相交，条件为#tempsfsjfdb 数量=2为相交------------
--------------------3、判断包含，条件为#tempsfsjfdb 数量>2为相交------------
select @tempsfsjfdcount=count(*) from #tempsfsjfdb
if @tempsfsjfdcount=1
begin
set @temptcsj1=@temptcsj
set @temptcsj2=0
set @je2=0
end
if @tempsfsjfdcount=2
begin
select top 1 @temptcsj2=(@strccsj/100-zzsj/100)*60+(@strccsj%100-zzsj%100) from #tempsfsjfdb order by qssj asc
set @temptcsj1=@temptcsj-@temptcsj2
end
if @tempsfsjfdcount=3
begin
select top 1 @temptcsj2=(zzsj/100-qssj/100)*60+(zzsj%100-qssj%100) from (select top 2 * from #tempsfsjfdb order by qssj asc) a order by a.qssj desc
set @temptcsj1=@temptcsj-@temptcsj2
end
SELECT TOP 1 @signid = id
FROM #tempsfsjfdb
ORDER BY qssj ASC
INSERT INTO #tempsfbzb
SELECT *
FROM sfbzb
WHERE sfsjd = @signid
AND zzd > 0
ORDER BY zzd ASC
WHILE ( EXISTS(SELECT 1
FROM #tempsfbzb)
AND ( @temptcsj1 != 0 ) )
BEGIN
SELECT TOP 1 @tempzzd = zzd,
@tempdj = dj,
@tempdw = dw,
@tempid = id
FROM #tempsfbzb
IF @tempzzd <= @temptcsj1
BEGIN
SET @tempsfje=@tempsfje + @tempdj * ( @tempzzd - @tempqsd ) / @tempdw
SET @tempqsd=@tempzzd
DELETE FROM #tempsfbzb
WHERE id = @tempid
END
ELSE
BEGIN
SET @tempsfje=@tempsfje + @tempdj * Ceiling(( @temptcsj1 - @tempqsd ) / ( @tempdw + 0.0 ))
SET @temptcsj1=0
END
END
set @Je1=@tempsfje
if @tempsfsjfdcount=2
begin
-----------------------------计算在相交的时间段所产生的费用----------------------------------------------
SET @tempqsd=0
SET @tempsfje=0
SELECT TOP 1 @signid = id,
@strrcsj=qssj
FROM #tempsfsjfdb
ORDER BY qssj desc
delete from #tempsfbzb
INSERT INTO #tempsfbzb
SELECT *
FROM sfbzb
WHERE sfsjd = @signid
AND zzd > 0
ORDER BY zzd ASC
WHILE ( EXISTS(SELECT 1
FROM #tempsfbzb)
AND ( @temptcsj2 != 0 ) )
BEGIN
SELECT TOP 1 @tempzzd = zzd,
@tempdj = dj,
@tempdw = dw,
@tempid = id
FROM #tempsfbzb
IF @tempzzd <= @temptcsj2
BEGIN
SET @tempsfje=@tempsfje + @tempdj * ( @tempzzd - @tempqsd ) / @tempdw
SET @tempqsd=@tempzzd
DELETE FROM #tempsfbzb
WHERE id = @tempid
END
ELSE
BEGIN
SET @tempsfje=@tempsfje + @tempdj * Ceiling(( @temptcsj2 - @tempqsd ) / ( @tempdw + 0.0 ))
SET @temptcsj2=0
END
END
set @je2=@tempsfje
-------------------------------------------------------------------------------------------------------
end
if @tempsfsjfdcount=3
begin
select top 1 @je2=zgxe from (select top 2 * from #tempsfsjfdb order by qssj asc) a order by a.qssj desc
end
--------------------------------------------
-----------------------------------------------
-----------------------以下为计算按照出场时间来话费计算范围----------------------------------------------
----------------------------判断相离、相交、包含-----------------------------
--------------------1、判断相离，条件为#tempsfsjfdb 数量=1为相离------------
--------------------2、判断相交，条件为#tempsfsjfdb 数量=2为相交------------
--------------------3、判断包含，条件为#tempsfsjfdb 数量>2为相交------------
----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
set @tempsfje=@je1+@je2
if @tempsfje>=@signzgxe
begin
set @tempsfje=@signzgxe
end
--------------------------------------------------------------------------------------------------------
SET @sfje=@sfje + @tempsfje
DROP TABLE #tempsfbzb
DROP TABLE #tempsfsjfdb
IF @@ERROR != 0
BEGIN
SET @result=1
RETURN
END
ELSE
BEGIN
SET @result=0
RETURN
END
GO
