USP_DATAFORMTEMPLATE_EDIT_DESIGNATIONHYBRID_2

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

    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

        declare @DESIGNATIONLEVELID uniqueidentifier;

        declare @DESIGNATIONLOOKUPIDOLD nvarchar(512);
        declare @DESIGNATIONLOOKUPID nvarchar(512);

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

        select
            @DESIGNATIONLEVELID = case 
                when DESIGNATIONLEVEL2ID is null then DESIGNATIONLEVEL1ID
                when DESIGNATIONLEVEL3ID is null then DESIGNATIONLEVEL2ID
                when DESIGNATIONLEVEL4ID is null then DESIGNATIONLEVEL3ID
                when DESIGNATIONLEVEL5ID is null then DESIGNATIONLEVEL4ID
                else DESIGNATIONLEVEL5ID
            end,
            @DESIGNATIONLOOKUPIDOLD = USERID
        from
            dbo.DESIGNATION
        where
            ID = @ID;

        -- get the parent designation's id and template id to perform validation
        select 
            @PARENTID = PARENTDESIGNATION.ID,
            @PARENTLOOKUPID = PARENTDESIGNATION.USERID,
            @PARENTHIERARCHYITEMID = PARENTDESIGNATION.DESIGNATIONLEVELTYPEHIERARCHYITEMID
        from
            dbo.DESIGNATION
        left outer join
            dbo.DESIGNATION [PARENTDESIGNATION] on DESIGNATION.DESIGNATIONLEVEL1ID = PARENTDESIGNATION.DESIGNATIONLEVEL1ID
                and (DESIGNATION.DESIGNATIONLEVEL2ID = PARENTDESIGNATION.DESIGNATIONLEVEL2ID or (PARENTDESIGNATION.DESIGNATIONLEVEL2ID is null and DESIGNATION.DESIGNATIONLEVEL3ID is null))
                and (DESIGNATION.DESIGNATIONLEVEL3ID = PARENTDESIGNATION.DESIGNATIONLEVEL3ID or (PARENTDESIGNATION.DESIGNATIONLEVEL3ID is null and DESIGNATION.DESIGNATIONLEVEL4ID is null))
                and (DESIGNATION.DESIGNATIONLEVEL4ID = PARENTDESIGNATION.DESIGNATIONLEVEL4ID or (PARENTDESIGNATION.DESIGNATIONLEVEL4ID is null and DESIGNATION.DESIGNATIONLEVEL5ID is null))
        where
            DESIGNATION.ID = @ID
        and
            DESIGNATION.ID <> PARENTDESIGNATION.ID;

        -- 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;

        declare @HIERARCHYITEMID uniqueidentifier;
        select
            @HIERARCHYITEMID = ID
        from
            dbo.DESIGNATIONLEVELTYPEHIERARCHYITEM
        where
        (
            (PARENTID = @PARENTHIERARCHYITEMID)
            or
            (PARENTID is null and @PARENTHIERARCHYITEMID is null and @PARENTID is null)
        )
        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);

        -- update the fundraising purpose
        update
            dbo.DESIGNATIONLEVEL
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            USERID = @LOOKUPID,
            DESIGNATIONLEVELCATEGORYCODEID = @DESIGNATIONLEVELCATEGORYCODEID,
            DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID,
            DESIGNATIONREPORT1CODEID = @DESIGNATIONREPORT1CODEID,
            DESIGNATIONREPORT2CODEID = @DESIGNATIONREPORT2CODEID,
            SITEID = @SITEID
        where
            DESIGNATIONLEVEL.ID = @DESIGNATIONLEVELID;

        -- update the designation
        update 
            dbo.DESIGNATION
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            VANITYNAME = @VANITYNAME,
            USERID = @DESIGNATIONLOOKUPID,
            DESIGNATIONREPORT1CODEID = @DESIGNATIONREPORT1CODEID,
            DESIGNATIONREPORT2CODEID = @DESIGNATIONREPORT2CODEID,
            DESIGNATIONLEVELTYPEHIERARCHYITEMID = @HIERARCHYITEMID,
            STARTDATE = @STARTDATE,
            ENDDATE = @ENDDATE
        where 
            DESIGNATION.ID = @ID;

        -- todo bwj 8/10/09 actually chase down the children instead of depending on uniqueness
        -- update child designation lookup ID's
        update
            dbo.DESIGNATION
        set
            USERID = @DESIGNATIONLOOKUPID + right(USERID, len(USERID) - len(@DESIGNATIONLOOKUPIDOLD))
        where
            left(USERID, len(@DESIGNATIONLOOKUPIDOLD)) = @DESIGNATIONLOOKUPIDOLD
        and
            ID <> @ID;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;