IDC资讯中心
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
