/****** Object:  Stored Procedure dbo.ca_insert_class    Script Date: 12/1/2005 11:50:15 PM ******/

CREATE PROCEDURE ca_insert_class
(
    @logical_asset_uuid binary(16),
    @new_class_id integer,
    @new_hierarchy_id integer,
    @override_weight integer,
    @current_time_stamp integer
)
AS
DECLARE @oldclass integer,
        @oldhierarchy integer,
        @overrideclass integer,
        @oldoverrideweight integer,
        @lasttimestamp integer,
    @rowcount integer,
        @old_uuid binary(16),
        @oldclasspath varchar(100),
        @oldlevel integer,
        @overridepath varchar(100),
        @overridelevel integer,
        @newclasspath varchar(100),
        @newlevel integer,
        @levelstocompare integer,
        @reclass integer,
        @tmppath1 varchar(100),
        @tmppath2 varchar(100),
        @x integer,
        @y integer,
        @tmp1 varchar(100),
        @tmp2 varchar(100),
        @emessage  char(255)
BEGIN
/* emessage = 'logical_asset_uuid='+text(@logical_asset_uuid)+', new_class_id='+text(@new_class_id)+
', new_hierarchy_id='+text(@new_hierarchy_id)+', override_weight='+text(override_weight)+
', current_time_stamp='+text(@current_time_stamp);
message @emessage; */

    SELECT @rowcount=COUNT(*) FROM ca_asset_class
    WHERE logical_asset_uuid = @logical_asset_uuid AND
        hierarchy_id = @new_hierarchy_id;

IF (@rowcount > 0)
BEGIN
/* test for update. */

  SELECT @old_uuid=logical_asset_uuid, @oldhierarchy=hierarchy_id,
         @oldoverrideweight=override_weight, @oldclass=class_id,
         @overrideclass=overridden_class_id,
         @lasttimestamp=last_update_date
  FROM ca_asset_class WHERE logical_asset_uuid=@logical_asset_uuid;

  SELECT @oldclasspath=hierarchy_path, @oldlevel=level FROM ca_class_hierarchy
  WHERE child_class_id=@oldclass;

  SELECT @newclasspath=hierarchy_path, @newlevel=level FROM ca_class_hierarchy
  WHERE child_class_id=@new_class_id;

  SELECT @overridepath=hierarchy_path, @overridelevel=level FROM ca_class_hierarchy
  WHERE child_class_id=@overrideclass;
  
  IF (@oldclass = @new_class_id)
  BEGIN
    /* update time stamp */
        UPDATE ca_asset_class
        SET last_update_date = @current_time_stamp
        WHERE logical_asset_uuid=@logical_asset_uuid
              AND class_id=@oldclass
              AND last_update_date < @current_time_stamp;


        set @emessage = 'ca_asset_class updated. class_id = old_class_id';
/*        MESSAGE @emessage; */
        RETURN 0;
  END
   
  --ELSEIF (@override_weight>0)

  IF (@override_weight>0)
  BEGIN
        IF (@lasttimestamp > @current_time_stamp)
    BEGIN
            set @current_time_stamp = @lasttimestamp;
        END --IF  (lastt;
        IF (@overrideclass IS NULL)
    BEGIN
            UPDATE ca_asset_class
            SET override_weight=@override_weight,
                overridden_class_id=@oldclass,
                class_id=@new_class_id,
                last_update_date=@current_time_stamp
            WHERE logical_asset_uuid=@logical_asset_uuid AND class_id=@oldclass;   
    END -- IF (overri
       
        ELSE
    BEGIN
            UPDATE ca_asset_class
            SET override_weight=@override_weight,
                class_id=@new_class_id,
                last_update_date=@current_time_stamp
            WHERE logical_asset_uuid=@logical_asset_uuid AND class_id=@oldclass;

        END --ELSE
        set @emessage = 'ca_asset_class updated. class_id overridden';    
/*        MESSAGE @emessage; */
        RETURN 0;

  END --ELSEIF (over
  ELSE
  BEGIN
    /* complicated hierarchy checks */
    /* parse hierarchy paths */
        IF (@newlevel > @oldlevel)
    BEGIN
           set @levelstocompare=@newlevel+1;
    END --IF (newlevel > oldlevel)
        ELSE
    BEGIN
           set @levelstocompare=@oldlevel+1;
        END --ELSE
        set @reclass=0;
        set @tmppath1 = @newclasspath;
        set @tmppath2 = @oldclasspath;
    
        WHILE (@levelstocompare>=0)
    BEGIN
           set @x=CHARINDEX('.',@tmppath1)
           set @y=CHARINDEX('.',@tmppath2)
        /*   set @tmp1=LEFT(@tmppath1,@x-1);
           set @tmp2=LEFT(@tmppath2,@y-1);*/
    if(@x>0)
               set @tmp1=LEFT(@tmppath1,@x-1);
           else
               set @tmp1='';
           if(@y>0)
               set @tmp2=LEFT(@tmppath2,@y-1);
           else
               set @tmp2='';

           IF (@tmp1=@tmp2)
       BEGIN
               set @levelstocompare=@levelstocompare-1;
               set @tmppath1 = SUBSTRING(@tmppath1,@x+1,len(@tmppath1))
                 set @tmppath2 = SUBSTRING(@tmppath2,@y+1,len(@tmppath1))
       END --IF (tmp1=tmp2)
           ELSE
       BEGIN
               set @reclass=1;
               set @levelstocompare=-1;
           END --ELSE
        END --WHILE;

        IF (@reclass=0)
    BEGIN
           /* different classes update */
            IF (@newlevel > @oldlevel)
        BEGIN
               /* more specific class may need reclass check overrideclass first*/
                set @reclass=1;         
            END --IF (newlevel > oldlevel;
        END --IF(reclass=0);
    
        /* If (new_class_id is higher in hierarchy than old class id) //less specific */
        IF (@reclass=0)
    BEGIN
            IF (@current_time_stamp > @lasttimestamp)
        BEGIN
                UPDATE ca_asset_class
                SET last_update_date=@current_time_stamp
                WHERE logical_asset_uuid=@logical_asset_uuid AND class_id=@oldclass;
                set @emessage = 'ca_asset_class last_update_date updated';
        END --(current_time_stam
            ELSE
        BEGIN
                set @emessage = 'ca_asset_class class_id is higher than existing class_id. no change';
            END --ELSE;
/*            MESSAGE @emessage; */
            RETURN 0;
    END --(reclass=0)
        --ELSEIF (NOT (@overrideclass IS NULL))
    IF (NOT (@overrideclass IS NULL))
    BEGIN
            /* Else if class was previously overridden */
            IF (@newlevel > @overridelevel)
        BEGIN
               set @levelstocompare=@newlevel+1;
        END
            ELSE
        BEGIN
               set @levelstocompare=@overridelevel+1;
            END --ELSE
            set @reclass=0;
            set @tmppath1 = @newclasspath;
            set @tmppath2 = @overridepath;
    
            WHILE (@levelstocompare>=0)
            BEGIN
                set @x=CHARINDEX('.',@tmppath1)
                set @y=CHARINDEX('.',@tmppath2)
           /*     set @tmp1=LEFT(@tmppath1,@x-1);
                set @tmp2=LEFT(@tmppath2,@y-1);*/
    if(@x>0)
               set @tmp1=LEFT(@tmppath1,@x-1);
           else
               set @tmp1='';
           if(@y>0)
               set @tmp2=LEFT(@tmppath2,@y-1);
           else
               set @tmp2='';

                IF (@tmp1=@tmp2)
        BEGIN
                  set @levelstocompare=@levelstocompare-1;
                  set @tmppath1 = SUBSTRING(@tmppath1,@x+1,len(@tmppath1))
                      set @tmppath2 = SUBSTRING(@tmppath2,@y+1,len(@tmppath1))
        END
                ELSE
        BEGIN
                set @reclass=1;
                set @levelstocompare=-1;
                END --ELSE
            END --WHILE;
            IF ((@reclass=0) AND (@newlevel > @overridelevel))
        BEGIN
                 set @reclass=1;
            END --IF;
            IF (@reclass>0)
        BEGIN
             /* if newclass is lower in hierarchy than overrideclass, update overrideclass*/
                UPDATE ca_asset_class
                SET overridden_class_id=@new_class_id,
                    last_update_date=@current_time_stamp
                WHERE logical_asset_uuid=@logical_asset_uuid AND class_id=@oldclass;
                set @emessage ='ca_asset_class overridden_class_id updated';
        END
            ELSE
        BEGIN
                        /* else no change */
                set @emessage ='ca_asset_class new class_id is higher in hierarchy than overridden_class_id. no change';
            END --ELSe
/*            MESSAGE @emessage;  */
            RETURN 0;
    END --ELSEIF
        ELSE
    BEGIN
    /* Else If last_update_date < current_time_stamp */
            IF (@current_time_stamp > @lasttimestamp)
        BEGIN
                UPDATE ca_asset_class
                SET class_id=@new_class_id,
                    last_update_date=@current_time_stamp
                WHERE logical_asset_uuid=@logical_asset_uuid AND class_id=@oldclass;

                set @emessage ='ca_asset_class class_id updated';
        END
            ELSE
        BEGIN
             /* else no change */
                set @emessage ='ca_asset_class no change';
            END --ELSE

/*            MESSAGE @emessage; */
            RETURN 0;
     
        END --else
  END --ELSE
END --IF

ELSE
BEGIN
    INSERT INTO ca_asset_class (logical_asset_uuid, hierarchy_id, class_id, override_weight,
                            overridden_class_id, last_update_date)
    VALUES (@logical_asset_uuid, @new_hierarchy_id, @new_class_id, NULL, NULL, @current_time_stamp);
    set @emessage = 'ca_asset_class record added';
    /*    MESSAGE @emessage;  */
    RETURN 0;
END --ELSE


END --BEGIN
GO
GRANT EXECUTE ON  [dbo].[ca_insert_class] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ca_insert_class] TO [regadmin]
GO