USP_DATAFORMTEMPLATE_ADD_DESIGNATIONHYBRID

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DESIGNATIONHYBRID
(
    @ID uniqueidentifier = null output,
    @PARENTDESIGNATIONID uniqueidentifier,
    @DESIGNATIONLEVELTYPEID uniqueidentifier = null,
    @NAME nvarchar(100) = null,
    @VANITYNAME nvarchar(512) = null,
    @DESCRIPTION nvarchar(255) = null,
    @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
    @LOOKUPID nvarchar(100) = null,
    @DESIGNATIONREPORTCODE1ID uniqueidentifier = null,
    @DESIGNATIONREPORTCODE2ID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEID uniqueidentifier = null
)
as
    set nocount on;

    if @ID is null
        set @ID = newid()

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

    if (@SITEID is not null) 
        begin
            if dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, @SITEID) = 0 begin
                raiserror ('ERR_SITE_NOACCESS',13,1);
                return 1;
            end
        end

        -- the id returned will be that of the designation row, not the designation level row
        declare @DESIGNATIONLEVELID uniqueidentifier = newid();

        -- the levels of the parent designation
        declare @DESIGNATIONLEVEL1ID uniqueidentifier;
        declare @DESIGNATIONLEVEL2ID uniqueidentifier;
        declare @DESIGNATIONLEVEL3ID uniqueidentifier;
        declare @DESIGNATIONLEVEL4ID uniqueidentifier;
        declare @DESIGNATIONLEVEL5ID uniqueidentifier;

        declare @PARENTHIERARCHYITEMID uniqueidentifier;
        declare @PARENTLOOKUPID nvarchar(512);

        -- the designation lookup id will be created using a combination of the parent designation's lookup id and the one specified for the designation level
        declare @DESIGNATIONLOOKUPID nvarchar(512);

        select 
            @DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL1ID,
            @DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL2ID,
            @DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL3ID,
            @DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL4ID,
            @DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL5ID,
            @PARENTLOOKUPID = USERID,
            @PARENTHIERARCHYITEMID = DESIGNATIONLEVELTYPEHIERARCHYITEMID
        from 
            dbo.DESIGNATION
        where 
            DESIGNATION.ID = @PARENTDESIGNATIONID;

        -- set the path of the current designation using the parent levels and the designation level generated below
        if @DESIGNATIONLEVEL1ID is null
            set @DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
        else                            
            if @DESIGNATIONLEVEL2ID is null
                set @DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID
            else
                if @DESIGNATIONLEVEL3ID is null
                    set @DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID
                else
                    if @DESIGNATIONLEVEL4ID is null
                        set @DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID
                    else
                        set @DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID

        -- build the designation lookup id by combining the parent designation's lookup ID with the fundraising purpose's lookup ID
        if @PARENTLOOKUPID is null
            set @DESIGNATIONLOOKUPID = @LOOKUPID
        else
            set @DESIGNATIONLOOKUPID = @PARENTLOOKUPID + '-' + @LOOKUPID;


        -- create the hierarchy instance from the hierarchy template if root node
        if @PARENTHIERARCHYITEMID is null and @PARENTDESIGNATIONID = '0D47D951-8E96-4FBA-A7AA-2339B3D7B0BC'
        begin
            declare @DESIGNATIONLEVELTYPEHIERARCHYID uniqueidentifier;
            set @DESIGNATIONLEVELTYPEHIERARCHYID = newId();

            insert into dbo.DESIGNATIONLEVELTYPEHIERARCHY
            (
                ID,
                NAME,
                DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            (
                select
                    @DESIGNATIONLEVELTYPEHIERARCHYID,
                    DESIGNATIONLEVELTYPEHIERARCHYTEMPLATE.NAME,
                    DESIGNATIONLEVELTYPEHIERARCHYTEMPLATE.ID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEITEM
                inner join
                    dbo.DESIGNATIONLEVELTYPEHIERARCHYTEMPLATE on DESIGNATIONLEVELTYPEHIERARCHYTEMPLATE.ID = DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEITEM.DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEID
                where
                    DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEITEM.PARENTID is null
            );

            declare @ITEMS table (ID uniqueidentifier, PARENTID uniqueidentifier, OLDID uniqueidentifier, OLDPARENTID uniqueidentifier, DESIGNATIONLEVELTYPEID uniqueidentifier);
            with ITEMS_CTE(ID, OLDID, OLDPARENTID, DESIGNATIONLEVELTYPEID) as (
                select newId(), ID, PARENTID, DESIGNATIONLEVELTYPEID
                from dbo.DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEITEM
                where PARENTID is null
                union all
                select newId(), TEMPLATEITEM.ID, TEMPLATEITEM.PARENTID, TEMPLATEITEM.DESIGNATIONLEVELTYPEID
                from dbo.DESIGNATIONLEVELTYPEHIERARCHYTEMPLATEITEM as TEMPLATEITEM
                inner join ITEMS_CTE on ITEMS_CTE.OLDID = TEMPLATEITEM.PARENTID
            )
            insert into @ITEMS
            select ID, null, OLDID, OLDPARENTID, DESIGNATIONLEVELTYPEID from ITEMS_CTE;

            insert into dbo.DESIGNATIONLEVELTYPEHIERARCHYITEM
            (
                ID,
                PARENTID,
                DESIGNATIONLEVELTYPEHIERARCHYID,
                DESIGNATIONLEVELTYPEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            (
                select
                    ITEMS.ID,
                    ITEMSPARENT.ID,
                    @DESIGNATIONLEVELTYPEHIERARCHYID,
                    ITEMS.DESIGNATIONLEVELTYPEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from @ITEMS as ITEMS
                left join
                    @ITEMS as ITEMSPARENT on ITEMSPARENT.OLDID = ITEMS.OLDPARENTID
            );
        end

        declare @HIERARCHYITEMID uniqueidentifier;
        select
            @HIERARCHYITEMID = ID
        from
            dbo.DESIGNATIONLEVELTYPEHIERARCHYITEM
        where
        (
            (PARENTID = @PARENTHIERARCHYITEMID)
            or
            (PARENTID is null and @PARENTHIERARCHYITEMID is null and @PARENTDESIGNATIONID = '0D47D951-8E96-4FBA-A7AA-2339B3D7B0BC') 
            -- well known guid passed to indicate we're adding a root level designation
        )
        and
            DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID;

        -- validate that the designation level type is supported at this level in the hierarchy template
        if (@PARENTHIERARCHYITEMID is not null) and (@HIERARCHYITEMID is null)
            raiserror('The specified fundraising purpose type cannot be added to this level of the hierarchy.', 13, 1);

        declare @BASECURRENCYID uniqueidentifier;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        select @BASECURRENCYID = 
            (select CURRENCYSET.BASECURRENCYID from dbo.CURRENCYSET where ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID))

        if (@ORGANIZATIONCURRENCYID <> @BASECURRENCYID)
            set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

        insert into dbo.DESIGNATIONLEVEL
        (
            ID,
            NAME,
            DESCRIPTION,
            DESIGNATIONLEVELCATEGORYCODEID,
            DESIGNATIONLEVELTYPEID,
            USERID,
            DESIGNATIONREPORT1CODEID,
            DESIGNATIONREPORT2CODEID,
            ORGANIZATIONEXCHANGERATEID,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
      SITEID
        )
        values
        (
            @DESIGNATIONLEVELID,
            @NAME,
            @DESCRIPTION,
            @DESIGNATIONLEVELCATEGORYCODEID,
            @DESIGNATIONLEVELTYPEID,
            @LOOKUPID,
            @DESIGNATIONREPORTCODE1ID,
            @DESIGNATIONREPORTCODE2ID,
            @ORGANIZATIONEXCHANGERATEID,
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
      @SITEID
        );

        insert into dbo.DESIGNATION
        (
            ID,
            DESIGNATIONLEVEL1ID,
            DESIGNATIONLEVEL2ID,
            DESIGNATIONLEVEL3ID,
            DESIGNATIONLEVEL4ID,
            DESIGNATIONLEVEL5ID,
            USERID,
            VANITYNAME,
            DESIGNATIONREPORT1CODEID,
            DESIGNATIONREPORT2CODEID,
            STARTDATE,
            ENDDATE,
            DESIGNATIONLEVELTYPEHIERARCHYITEMID,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @ID,
            @DESIGNATIONLEVEL1ID,
            @DESIGNATIONLEVEL2ID,
            @DESIGNATIONLEVEL3ID,
            @DESIGNATIONLEVEL4ID,
            @DESIGNATIONLEVEL5ID,
            @DESIGNATIONLOOKUPID,
            @VANITYNAME,
            @DESIGNATIONREPORTCODE1ID,
            @DESIGNATIONREPORTCODE2ID,
            @STARTDATE,
            @ENDDATE,
            @HIERARCHYITEMID,
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0