USP_DATAFORMTEMPLATE_EDIT_MOVEDESIGNATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MOVEDESIGNATION (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@PARENTDESIGNATIONID uniqueidentifier
  ,@PURPOSELOCATION tinyint
  )
as
set nocount on;

declare @CURRENTDATE datetime;

set @CURRENTDATE = getdate();

begin try
  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @EMPTY uniqueidentifier = '00000000-0000-0000-0000-000000000000';
  declare @ORIGPARENTDESIGNATIONID uniqueidentifier;
  declare @ORIGDESIGNATIONLEVELID uniqueidentifier;
  declare @CHILDLEVELS int;
  declare @OLDLEVEL int;
  declare @NEWLEVEL int;
  declare @OLDPATH nvarchar(200);
  declare @NEWPATH nvarchar(200);
  declare @OLDLEVEL1ID uniqueidentifier;
  declare @OLDLEVEL2ID uniqueidentifier;
  declare @OLDLEVEL3ID uniqueidentifier;
  declare @OLDLEVEL4ID uniqueidentifier;
  declare @OLDLEVEL5ID uniqueidentifier;
  declare @OLDSITEID uniqueidentifier;
  declare @NEWSITEID uniqueidentifier;

  select @ORIGPARENTDESIGNATIONID = P.ID
    ,@ORIGDESIGNATIONLEVELID = C.DESIGNATIONLEVELID
    ,@OLDLEVEL = C.[LEVEL]
    ,@OLDPATH = C.PATHID
    ,@OLDLEVEL1ID = C.DESIGNATIONLEVEL1ID
    ,@OLDLEVEL2ID = C.DESIGNATIONLEVEL2ID
    ,@OLDLEVEL3ID = C.DESIGNATIONLEVEL3ID
    ,@OLDLEVEL4ID = C.DESIGNATIONLEVEL4ID
    ,@OLDLEVEL5ID = C.DESIGNATIONLEVEL5ID
    ,@OLDSITEID = C.DESIGNATIONLEVELSITEID
  from dbo.UFN_DESIGNATION_BUILDNAME_BULK() C
  left join dbo.UFN_DESIGNATION_BUILDNAME_BULK() P on C.PARENTPATHID = P.PATHID
  where C.ID = @ID

  if COALESCE(@ORIGPARENTDESIGNATIONID, @EMPTY) = COALESCE(@PARENTDESIGNATIONID, @EMPTY)
    return 0;

  select @CHILDLEVELS = COALESCE(MAX([LEVEL]), @OLDLEVEL) - @OLDLEVEL
  from dbo.UFN_DESIGNATION_BUILDNAME_BULK()
  where PATHID like @OLDPATH + '%'

  select @NEWLEVEL = COALESCE(level, 0) + 1
    ,@NEWPATH = PATHID
  from dbo.UFN_DESIGNATION_BUILDNAME_BULK()
  where ID = @PARENTDESIGNATIONID

  set @NEWLEVEL = COALESCE(@NEWLEVEL, 1);

  if CHARINDEX(@OLDPATH, @NEWPATH) = 1
    raiserror (
        'ERR_CANNOTMAKECHILDOFITSELF'
        ,13
        ,1
        );

  if (@NEWLEVEL + @CHILDLEVELS) > 5
    raiserror (
        'ERR_MAXIMUMNUMLEVELSEXCEDED'
        ,13
        ,1
        );

  if (COALESCE(@ORIGPARENTDESIGNATIONID, @EMPTY) <> COALESCE(@PARENTDESIGNATIONID, @EMPTY))
  begin
    declare @NEWLEVEL1ID uniqueidentifier;
    declare @NEWLEVEL2ID uniqueidentifier;
    declare @NEWLEVEL3ID uniqueidentifier;
    declare @NEWLEVEL4ID uniqueidentifier;
    declare @NEWLEVEL5ID uniqueidentifier;

    if @NEWLEVEL = 1
    begin
      set @NEWLEVEL1ID = @ORIGDESIGNATIONLEVELID

      select @NEWSITEID = SITEID
      from dbo.DESIGNATIONLEVEL
      where ID = @ORIGDESIGNATIONLEVELID
    end
    else
      select @NEWLEVEL1ID = D.DESIGNATIONLEVEL1ID
        ,@NEWLEVEL2ID = D.DESIGNATIONLEVEL2ID
        ,@NEWLEVEL3ID = D.DESIGNATIONLEVEL3ID
        ,@NEWLEVEL4ID = D.DESIGNATIONLEVEL4ID
        ,@NEWLEVEL5ID = D.DESIGNATIONLEVEL5ID
        ,@NEWSITEID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)
      from dbo.DESIGNATION D
      left join dbo.DESIGNATIONLEVEL DL1 on D.DESIGNATIONLEVEL1ID = DL1.ID
      left join dbo.DESIGNATIONLEVEL DL2 on D.DESIGNATIONLEVEL2ID = DL2.ID
      left join dbo.DESIGNATIONLEVEL DL3 on D.DESIGNATIONLEVEL3ID = DL3.ID
      left join dbo.DESIGNATIONLEVEL DL4 on D.DESIGNATIONLEVEL4ID = DL4.ID
      left join dbo.DESIGNATIONLEVEL DL5 on D.DESIGNATIONLEVEL5ID = DL5.ID
      where D.ID = @PARENTDESIGNATIONID

    if (
        @NEWSITEID is null
        and @OLDSITEID is not null
        )
      or (
        @NEWSITEID is not null
        and @OLDSITEID is null
        )
      or (
        @NEWSITEID is not null
        and @NEWSITEID <> @OLDSITEID
        )
      raiserror (
          'ERR_CANNOTCHANGESITE'
          ,13
          ,1
          );

    declare @SQLCRITERIA nvarchar(1000) = ' where ';
    declare @SQL nvarchar(1000) = 'update dbo.DESIGNATION set ';
    declare @i int = 1;
    declare @LEVELFIELD nvarchar(50);
    declare @LEVELFIELDVALUE nvarchar(50);
    declare @ii int = @OLDLEVEL;

    while @i <= @OLDLEVEL
    begin
      if @i > 1
        set @SQLCRITERIA = @SQLCRITERIA + ' and ';
      set @LEVELFIELD = 'DESIGNATIONLEVEL' + cast(@i as nvarchar(1)) + 'ID';
      set @LEVELFIELDVALUE = '@OLDLEVEL' + cast(@i as nvarchar(1)) + 'ID';
      set @SQLCRITERIA = @SQLCRITERIA + @LEVELFIELD + '=' + @LEVELFIELDVALUE;
      set @i = @i + 1;
    end

    set @i = 1;

    if @OLDLEVEL < @NEWLEVEL
    begin
      --now move the old level values into their new position
      set @i = @NEWLEVEL;

      while @i <= 5
      begin
        if @i > @NEWLEVEL
          set @SQL = @SQL + ',';
        set @LEVELFIELD = 'DESIGNATIONLEVEL' + cast(@i as nvarchar(1)) + 'ID';

        if @ii > 5
          set @LEVELFIELDVALUE = 'null'
        else
          set @LEVELFIELDVALUE = 'DESIGNATIONLEVEL' + cast(@ii as nvarchar(1)) + 'ID';

        set @SQL = @SQL + @LEVELFIELD + '=' + @LEVELFIELDVALUE;
        set @i = @i + 1;
        set @ii = @ii + 1;
      end

      set @i = 1;

      --set the new parent level fields
      while @i < @NEWLEVEL
      begin
        set @SQL = @SQL + ',';
        set @LEVELFIELD = 'DESIGNATIONLEVEL' + cast(@i as nvarchar(1)) + 'ID';
        set @LEVELFIELDVALUE = '@NEWLEVEL' + cast(@i as nvarchar(1)) + 'ID';
        set @SQL = @SQL + @LEVELFIELD + '=' + @LEVELFIELDVALUE;
        set @i = @i + 1;
      end
    end
    else
    begin
      --set the new parent level fields
      while @i < @NEWLEVEL
      begin
        if @i > 1
          set @SQL = @SQL + ',';
        set @LEVELFIELD = 'DESIGNATIONLEVEL' + cast(@i as nvarchar(1)) + 'ID';
        set @LEVELFIELDVALUE = '@NEWLEVEL' + cast(@i as nvarchar(1)) + 'ID';
        set @SQL = @SQL + @LEVELFIELD + '=' + @LEVELFIELDVALUE;
        set @i = @i + 1;
      end

      --now move the old level values into their new position
      while @i <= 5
      begin
        if @i > 1
          set @SQL = @SQL + ',';
        set @LEVELFIELD = 'DESIGNATIONLEVEL' + cast(@i as nvarchar(1)) + 'ID';

        if @ii > 5
          set @LEVELFIELDVALUE = 'null'
        else
          set @LEVELFIELDVALUE = 'DESIGNATIONLEVEL' + cast(@ii as nvarchar(1)) + 'ID';

        set @SQL = @SQL + @LEVELFIELD + '=' + @LEVELFIELDVALUE;
        set @i = @i + 1;
        set @ii = @ii + 1;
      end
    end

    set @SQL = @SQL + @SQLCRITERIA;

    exec sp_executesql @SQL
      ,N'@NEWLEVEL1ID uniqueidentifier, @NEWLEVEL2ID uniqueidentifier, @NEWLEVEL3ID uniqueidentifier, @NEWLEVEL4ID uniqueidentifier, @NEWLEVEL5ID uniqueidentifier, @OLDLEVEL1ID uniqueidentifier, @OLDLEVEL2ID uniqueidentifier, @OLDLEVEL3ID uniqueidentifier, @OLDLEVEL4ID uniqueidentifier, @OLDLEVEL5ID uniqueidentifier'
      ,@NEWLEVEL1ID
      ,@NEWLEVEL2ID
      ,@NEWLEVEL3ID
      ,@NEWLEVEL4ID
      ,@NEWLEVEL5ID
      ,@OLDLEVEL1ID
      ,@OLDLEVEL2ID
      ,@OLDLEVEL3ID
      ,@OLDLEVEL4ID
      ,@OLDLEVEL5ID;
  end
end try

begin catch
  exec dbo.USP_RAISE_ERROR;

  return 1;
end catch

return 0;