/****** Object:  Stored Procedure dbo.tng_setup_status    Script Date: 12/1/2005 11:49:46 PM ******/

CREATE procedure tng_setup_status (
            @bNeedQueryStatus        int=1,
        @dsc_name            varchar(255),
        @ipaddr_str            varchar(16)=null,
        @ipaddr_hex            binary(4)=null,
        @macaddr            varchar(15),
        @class_name            varchar(30),
        @bIpInDHCPRange        int=0,
        @bDelUncls            int=0,
        @iDHCPStatus        int=0
        
         )

as set nocount on                       
declare  @msg varchar(255), @old_name varchar(255), @old_ipaddr_hex binary(4), @old_ipaddr_str varchar(16)
declare  @old_mac  varchar(15) , @old_class_name varchar(30), @upper_mac varchar(30)
declare @status_no int
/*it may return multiple rows*/


  if( @bIpInDHCPRange<>0 and @bDelUncls=0)
  begin
    begin transaction updateinvaliddhcpip
      update tng_managedobject set status_no=@iDHCPStatus, address_type=2
        where address=@ipaddr_str and status_no<>@iDHCPStatus
    commit transaction updateinvaliddhcpip
  end
  else if( @bIpInDHCPRange<>0 and @bDelUncls<>0)
  begin
    begin transaction updateinvaliddhcpip1
      update tng_managedobject set status_no=@iDHCPStatus, address_type=2
        where class_name<>'Unclassified_TCP' and address=@ipaddr_str and status_no<>@iDHCPStatus
    commit transaction updateinvaliddhcpip1
  end




if(@bNeedQueryStatus<>0)
begin
  select @upper_mac=upper(@macaddr), @status_no=0
  select  @old_name=null, @old_ipaddr_hex=null, @old_ipaddr_str=null, @old_class_name=null,@old_mac=null

  if(@ipaddr_str is not null)
    select @old_name = name ,@old_ipaddr_str = address, @old_class_name = class_name, @old_mac = upper(mac_address)
      from tng_managedobject        
      where class_name !='Unclassified_TCP' and address_type <= 2 and
         (name = @dsc_name  or address=@ipaddr_str  or upper(mac_address)=@upper_mac )
      
  else
    select @old_name = name ,@old_ipaddr_hex = ip_address_hex, @old_class_name = class_name, @old_mac = upper(mac_address)
      from tng_managedobject        
      where class_name !='Unclassified_TCP' and address_type <= 2 and
         (name = @dsc_name  or ip_address_hex=@ipaddr_hex  or upper(mac_address)=@upper_mac )
      
  /*normal way should do select query tng_managedobject 4 times to set status, but too expansive.*/
  if(@ipaddr_str is null)
  begin
    if(@old_ipaddr_hex!=@ipaddr_hex and @old_name=@dsc_name)
      select @status_no=21
    if(@old_ipaddr_hex!=@ipaddr_hex and @old_mac=@upper_mac and @upper_mac!='000000000000' )
      select @status_no=14
    if(@old_class_name!=@class_name and @old_mac=@upper_mac and @upper_mac!='000000000000' )
      select @status_no=15
    if(@old_class_name!=@class_name and @old_ipaddr_hex=@ipaddr_hex)
      select @status_no=16
  end
  else
  begin
    if(@old_ipaddr_str!=@ipaddr_str and @old_name=@dsc_name)
      select @status_no=21
    if(@old_ipaddr_str!=@ipaddr_str and @old_mac=@upper_mac and @upper_mac!='000000000000' )
      select @status_no=14
    if(@old_class_name!=@class_name and @old_mac=@upper_mac and @upper_mac!='000000000000' )
      select @status_no=15
    if(@old_class_name!=@class_name and @old_ipaddr_str=@ipaddr_str)
      select @status_no=16
  end

  select @status_no
end
else select 0
GO
GRANT EXECUTE ON  [dbo].[tng_setup_status] TO [uniadmin]
GRANT EXECUTE ON  [dbo].[tng_setup_status] TO [wvadmin]
GO