1,fgw_proc1:
CREATE PROCEDURE fgw_proc1(@begin int , @end int)
AS
SET NOCOUNT ON
DECLARE @userid int, @handled float, @total float
CREATE TABLE #temp_proc1
(
userid int,
handled float,
total float
)
--get @total
DECLARE cur_cr CURSOR FOR SELECT count(*) FROM AHD.AHD.call_req where open_date>@begin and open_date<@end
OPEN cur_cr
FETCH cur_cr INTO @total
CLOSE cur_cr
DEALLOCATE cur_cr
DECLARE cur_ctct CURSOR FOR SELECT id FROM AHD.AHD.ctct
OPEN cur_ctct
FETCH cur_ctct INTO @userid
WHILE @@FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc2 @userid , @begin , @end , @handled output
INSERT INTO #temp_proc1 VALUES (@userid , @handled , @total)
FETCH NEXT FROM cur_ctct INTO @userid
END
CLOSE cur_ctct
DEALLOCATE cur_ctct
SELECT * FROM #temp_proc1
DROP TABLE #temp_proc1
drop procedure fgw_proc1
exec fgw_proc1 1,1
2,fgw_proc2
CREATE PROCEDURE fgw_proc2(@userid int , @begin int , @end int , @handled float OUTPUT)
AS
SET NOCOUNT ON
SET @handled = 0
DECLARE @cr_id int, @zh_id int, @status char(20), @to_status char(20), @cnt int, @open_date int
DECLARE cur_crzh CURSOR FOR SELECT * FROM AHD.dbo.FGW_CR_ZH where cnt = @userid
OPEN cur_crzh
FETCH cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @count2 int
DECLARE cur_crzh2 CURSOR FOR SELECT count(*) FROM AHD.dbo.FGW_CR_ZH where cr_id = @cr_id and open_date>@begin and open_date<@end
OPEN cur_crzh2
FETCH cur_crzh2 INTO @count2
CLOSE cur_crzh2
DEALLOCATE cur_crzh2
IF @count2 != 0
SET @handled = @handled + 1 / @count2
FETCH NEXT FROM cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
END
CLOSE cur_crzh
DEALLOCATE cur_crzh
--SELECT @handled
drop procedure fgw_proc2
exec fgw_proc2 1,1,1
3,fgw_proc3
CREATE PROCEDURE fgw_proc3(@begin int , @end int)
AS
SET NOCOUNT ON
DECLARE @cr_id int, @zh_id int, @cnt int, @sym char(30), @time_stamp int, @isOK int
CREATE TABLE #temp_proc3
(
cr_id int,
zh_id int,
cnt int,
isOK int
)
DECLARE cur_crzhsd CURSOR FOR SELECT cr.id,zh.id,zh.to_cnt,sd.sym,zh.time_stamp FROM AHD.AHD.call_req as cr LEFT OUTER JOIN AHD.AHD.ztr_his as zh ON cr.persid=zh.call_req_id LEFT OUTER JOIN AHD.AHD.srv_desc as sd ON cr.support_lev=sd.code WHERE cr.type='I' and cr.open_date>@begin and cr.open_date<@end and zh.to_status='OP'
OPEN cur_crzhsd
FETCH cur_crzhsd INTO @cr_id, @zh_id, @cnt, @sym, @time_stamp
WHILE @@FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc4 @zh_id , @sym , @time_stamp , @cnt output , @isOK output
INSERT INTO #temp_proc3 VALUES (@cr_id , @zh_id , @cnt , @isOK)
FETCH NEXT FROM cur_crzhsd INTO @cr_id, @zh_id, @cnt, @sym, @time_stamp
END
CLOSE cur_crzhsd
DEALLOCATE cur_crzhsd
SELECT * FROM #temp_proc3
DROP TABLE #temp_proc3
drop procedure fgw_proc3
EXEC fgw_proc3 1, 1111111111
4,fgw_proc4
CREATE PROCEDURE fgw_proc4(@zh_id int , @level char(30) , @time_stamp int , @cnt int OUTPUT , @isOK int OUTPUT)
AS
SET NOCOUNT ON
SET @isOK = 0
DECLARE cur_zh CURSOR FOR SELECT to_cnt,time_stamp FROM AHD.AHD.ztr_his WHERE id = @zh_id and to_status in ('L1WIP','L2WIP') and time_stamp>@time_stamp
OPEN cur_zh
DECLARE @time_stamp1 int
SET @time_stamp1=0
FETCH cur_zh INTO @cnt, @time_stamp1
IF @time_stamp1!=0
BEGIN
IF CHARINDEX('一级', @level) IS NOT NULL AND CHARINDEX('一级', @level)!=0
BEGIN
if @time_stamp1 - @time_stamp <600
SET @isOK=1
END
ELSE IF CHARINDEX('二级', @level) IS NOT NULL AND CHARINDEX('二级', @level)!=0
BEGIN
if @time_stamp1 - @time_stamp <1800
SET @isOK=1
END
ELSE IF CHARINDEX('三级', @level) IS NOT NULL AND CHARINDEX('三级', @level)!=0
BEGIN
if @time_stamp1 - @time_stamp <1800
SET @isOK=1
END
ELSE IF CHARINDEX('四级', @level) IS NOT NULL AND CHARINDEX('四级', @level)!=0
BEGIN
if @time_stamp1 - @time_stamp <1800
SET @isOK=1
END
END
CLOSE cur_zh
DEALLOCATE cur_zh
--SELECT @isOK, @time_stamp1
drop procedure fgw_proc4
exec fgw_proc4 1,'1',1,1,1