删除web Log

来源：互联网发布：同花顺股票开户软件编辑：程序博客网时间：2024/05/17 22:40

create procedure deletemibowebtime(@id varchar(50))
as
declare @total_time float,
            @changetime float,
            @rate float,
            @webaddress varchar(200),
            @total_timemax float

declare web_cur cursor for
select web_add,total_time    from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and total_time>=3*60*1000
open web_cur

Fetch next from web_cur into @webaddress,@total_timemax

print @webaddress

while @@Fetch_status=0
begin

select @changetime=sum(total_time)   from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')

select @total_time=sum(total_time)   from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)

set @rate= (@total_time-@changetime)/@total_time

if @rate>0
begin
update    OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WB_HOURS

set H_00=H_00*@rate,H_01=H_01*@rate,
H_02=H_02*@rate,H_03=H_03*@rate,H_04=H_04*@rate,
H_05=H_05*@rate,H_06=H_06*@rate,H_07=H_07*@rate,
H_08=H_08*@rate,H_09=H_09*@rate,H_10=H_10*@rate,
H_11=H_11*@rate,H_12=H_12*@rate,H_13=H_13*@rate,
H_14=H_14*@rate,H_15=H_15*@rate,H_16=H_16*@rate,
H_17=H_17*@rate,H_18=H_18*@rate,H_19=H_19*@rate,
H_20=H_20*@rate,H_21=H_21*@rate,H_22=H_22*@rate,
H_23=H_23*@rate, total_time = total_time*@rate
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0)
and (datediff(year, LOGIN_DATE,GETDATE())=0)

delete from OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')
end

Fetch next from web_cur into @webaddress,@total_timemax
end

close web_cur
deallocate web_cur