USP_MEMBERSHIPDUESBATCH_VALIDATE_ADDRENEWPROGRAM

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_ADDRENEWPROGRAM
(
    @CURRENTAPPUSERID uniqueidentifier,
    @ROWID uniqueidentifier,
    @BATCHID uniqueidentifier,
    @MEMBERSHIPTRANSACTIONTYPECODE tinyint, -- 0 = Add, 1 = Renew

    @TRANSACTIONDATE datetime,
    @EXPIRATIONDATE datetime,
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @MEMBERSHIPLEVELID uniqueidentifier,
    @MEMBERSHIPLEVELTERMID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @REVENUETYPECODE tinyint,
    @EXISTINGMEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPPROGRAMADDON xml,
    @EXISTINGMEMBERS xml,
    @MEMBERSHIPCARDS xml,
    @EXISTINGCHILDREN xml,            -- not currently supported

    @NUMBEROFCHILDREN smallint,        -- Temporary workaround for children not being implemented in 2012 Q1

    @IMPORTADDON xml = null,
    @IMPORT bit = 0,
    @COMMITONLYCHECK bit = 0
)
as
begin
    declare @CANBEPAIDINFULL bit = 0;
    declare @CANBEPLEDGED bit = 0;
    declare @NUMBEROFMEMBERS smallint = 0;
    declare @CHILDREN smallint = 0;
    --declare @NUMBEROFCHILDREN smallint = 0;    -- Commented out until children are implemented.

    declare @ERRORMSG nvarchar(255);
    declare @MEMBERS table(ID uniqueidentifier);
    declare @CARDS table(ID uniqueidentifier, EXPIRATIONDATE datetime);
    declare @NUMBEROFCARDSALLOWED int = 0;
    declare @ADDONS table(ID uniqueidentifier, QUANTITY integer);
    declare @ISGROUP bit = 0;
    declare @OBTAINLEVELCODE smallint = 0;

    select
        @ISGROUP = ISGROUP
    from dbo.CONSTITUENT
    where 
        ID = @CONSTITUENTID;

    -- get the Batch ID for later use

    if @ROWID is not null and @BATCHID is null
        begin
            select
                @BATCHID = BATCHMEMBERSHIPDUES.BATCHID
            from dbo.BATCHMEMBERSHIPDUES
            where ID = @ROWID;
        end

    -- Make sure Level belongs to program, term belongs to level, and level is active

    if not exists (
            select ID
            from dbo.MEMBERSHIPLEVEL
            where ID = @MEMBERSHIPLEVELID
                and MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                and MEMBERSHIPLEVEL.ISACTIVE = 1
        )
        raiserror('BBERR_INVALIDLEVELFORPROGRAM', 13, 1);

    if not exists(
            select ID
            from dbo.MEMBERSHIPLEVELTERM
            where ID = @MEMBERSHIPLEVELTERMID
                and LEVELID = @MEMBERSHIPLEVELID
        )
        raiserror('BBERR_INVALIDTERMFORLEVEL', 13, 1);

    -- Expiration date required for annual programs

    declare @PROGRAMTYPECODE tinyint;

    select
        @CANBEPAIDINFULL = MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM,
        @CANBEPLEDGED = MEMBERSHIPPROGRAM.MULTIPLEPAYMENTSEACHTERM,
        @CHILDREN = MEMBERSHIPLEVEL.CHILDRENALLOWED, 
        @NUMBEROFMEMBERS = MEMBERSHIPLEVEL.MEMBERSALLOWED,
        @NUMBEROFCARDSALLOWED = MEMBERSHIPLEVEL.CARDSALLOWED,
        @PROGRAMTYPECODE = MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
        @OBTAINLEVELCODE = MEMBERSHIPLEVEL.OBTAINLEVELCODE
    from dbo.MEMBERSHIPPROGRAM
    inner join dbo.MEMBERSHIPLEVEL 
        on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
    where MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
        and MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID;

    if @PROGRAMTYPECODE = 0 and @EXPIRATIONDATE is null
        raiserror('BBERR_EXPIRATIONDATE_REQUIRED', 13, 1);

    if @ROWID is not null 
        and exists(select 1 from dbo.BATCHMEMBERSHIPDUES bmd
                inner join dbo.MEMBERSHIPPROGRAM mp on bmd.MEMBERSHIPPROGRAMID = mp.ID
                where 
                    mp.ALLOWMULTIPLEMEMBERSHIPS = 0
                    and bmd.ID <> @ROWID
                    and bmd.BATCHID = @BATCHID
                    and bmd.MEMBERSHIPTRANSACTIONTYPECODE = @MEMBERSHIPTRANSACTIONTYPECODE
                    and coalesce(bmd.MEMBERSHIPRECIPIENTID, bmd.BILLTOCONSTITUENTID) = @CONSTITUENTID
                    and mp.ID = @MEMBERSHIPPROGRAMID)
        begin
            raiserror('BBERR_CONSTITUENTALREADYINBATCH_FORPROGRAM', 13, 1);
        end

    -- If adding a new membership

    if @MEMBERSHIPTRANSACTIONTYPECODE = 0
        begin
            if dbo.UFN_MEMBERSHIPDUESBATCH_CANADDCONSTITUENTTOPROGRAM_3(@CONSTITUENTID, @MEMBERSHIPPROGRAMID, @MEMBERSHIPTRANSACTIONTYPECODE,@EXISTINGMEMBERSHIPID) = 0
                raiserror('BBERR_CONSTITUENTINVALIDFORPROGRAM', 13,1);
        end
    else
        begin
            -- Do not allow individuals to renew other individuals' memberships

            if @ISGROUP = 0
                begin
                    if @EXISTINGMEMBERSHIPID is not null and not exists(select 1 from dbo.MEMBER where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and CONSTITUENTID = @CONSTITUENTID)
                        raiserror('BBERR_INVALIDMEMBERSHIP_FORCONSTITUENT', 13,1);
                end
            else
                begin
                    -- ensure the existing membership Id is for a member that is in the group

                    if not exists(select 1
                                  from dbo.GROUPDATA g
                                    left join dbo.GROUPMEMBER gm on g.ID = gm.GROUPID
                                    left join dbo.MEMBER m on gm.MEMBERID = m.CONSTITUENTID
                                    left join dbo.MEMBER mem on g.ID = mem.CONSTITUENTID
                                  where g.ID = @CONSTITUENTID 
                                    and (m.MEMBERSHIPID = @EXISTINGMEMBERSHIPID or mem.MEMBERSHIPID = @EXISTINGMEMBERSHIPID))
                        raiserror('BBERR_INVALIDMEMBERSHIP_FORCONSTITUENT', 13,1);
                end


            -- Do not allow backdate transaction unless it is for a pending membership

            declare @CURRENTSTATUS tinyint = 0;
            declare @JOINDATE datetime;

            select 
                @CURRENTSTATUS = STATUSCODE,
                @JOINDATE = JOINDATE
            from dbo.MEMBERSHIP 
            where ID = @EXISTINGMEMBERSHIPID;

            if exists (
                    select * 
                    from dbo.MEMBERSHIPTRANSACTION 
                    where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and 
                        cast(TRANSACTIONDATE as date) > @TRANSACTIONDATE
            ) and @CURRENTSTATUS <> 2
                raiserror('BBERR_DATEEARLIERTHANLASTMEMBERSHIPTRANSACTION', 13,1);

            if @EXPIRATIONDATE < @JOINDATE
                raiserror('BBERR_EXPIRATIONDATEB4JOINDATE', 13, 1);
        end    

    if @CANBEPAIDINFULL = 0 and @REVENUETYPECODE = 0
        raiserror('BBERR_PROGRAMCANNOTBEPAIDINFULL', 13, 1);

    if @CANBEPLEDGED = 0 and @REVENUETYPECODE <> 0 and @PROGRAMTYPECODE <> 1
        raiserror('BBERR_PROGRAMCANNOTBEPLEDGED', 13, 1);

    if @PROGRAMTYPECODE = 1 and @MEMBERSHIPTRANSACTIONTYPECODE = 1 and @CURRENTSTATUS <> 1 and @CURRENTSTATUS <> 5
        begin
            raiserror('BBERR_RECURRINGPROGRAMCANNOTBERENEWED', 13, 1);    
        end

    insert into @MEMBERS(ID)
        select 
            T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') 
        from @EXISTINGMEMBERS.nodes('/EXISTINGMEMBERS/ITEM') T(members)

    declare @ADDITIONALMEMBERS integer = 0;

    if @IMPORT = 0
        insert into @ADDONS(ID, QUANTITY)
            select 
                T.c.value('ADDONID [1]', 'uniqueidentifier'),
                T.c.value('NUMBEROFADDONS [1]', 'int')
            from @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(c)
            where T.c.value('APPLY [1]', 'bit') = 1;
    else
        insert into @ADDONS(ID, QUANTITY)
            select 
                T.c.value('ADDONID [1]', 'uniqueidentifier'),
                T.c.value('NUMBEROFADDONS [1]', 'int')
            from @IMPORTADDON.nodes('/IMPORTADDON/ITEM') T(c)

    if @OBTAINLEVELCODE = 1 and exists(select 1 from @ADDONS)
        begin
            raiserror('BBERR_CANNOTUSEADDONFORCONTRIBUTIONLEVEL', 13, 1);
        end

    if exists(
        select addons.ID
        from @ADDONS addons
        left outer join dbo.MEMBERSHIPPROGRAMADDON
            on addons.ID = MEMBERSHIPPROGRAMADDON.ADDONID
                and MEMBERSHIPPROGRAMADDON.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        where MEMBERSHIPPROGRAMADDON.ID is null
    )
        raiserror('BBERR_INVALIDADDON', 13, 1);

    if exists(
        select addons.ID
        from @ADDONS addons
        left outer join dbo.MEMBERSHIPPROGRAMADDON
            on addons.ID = MEMBERSHIPPROGRAMADDON.ADDONID
                and MEMBERSHIPPROGRAMADDON.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        where MEMBERSHIPPROGRAMADDON.MULTIPLEALLOWED = 0
        and addons.QUANTITY > 1
    )
        raiserror('BBERR_MULTIPLENOTALLOWED', 13, 1);

    select 
        @ADDITIONALMEMBERS = sum(addons.QUANTITY)
    from @ADDONS addons
    inner join dbo.ADDON A 
        on addons.ID = A.ID
    where A.ADDONTYPECODE = 1;

    set @NUMBEROFMEMBERS = @NUMBEROFMEMBERS + coalesce(@ADDITIONALMEMBERS, 0);

    set @ERRORMSG = 'Only ' +convert(nvarchar(6), @NUMBEROFMEMBERS)+ ' members are allowed for this membership.'

    declare @ADDITIONALMEMBERSPRESENT integer = 0; 
    select 
        @ADDITIONALMEMBERSPRESENT = coalesce(count(ID), 0)
    from @MEMBERS

    if @NUMBEROFMEMBERS < (@ADDITIONALMEMBERSPRESENT + 1) --and @NUMBEROFMEMBERS > 0

        raiserror(@ERRORMSG, 13, 1);

-- Commented out until children are implemented.

--    select @NUMBEROFCHILDREN = count(*)

--    from @EXISTINGCHILDREN.nodes('/EXISTINGCHILDREN/ITEM') T(c)

--


    -- Number of children validation

    if @CHILDREN < @NUMBEROFCHILDREN --and @CHILDREN > 0

    begin
        raiserror('BBERR_TOOMANYCHILDREN', 13, 1);
    end

    if exists (
            select ID
            from @MEMBERS
            where ID = @CONSTITUENTID
        )
        or exists (
            select count(CONSTITID)
            from
            (
                select ID as CONSTITID
                from @MEMBERS

                union all

                select @CONSTITUENTID as CONSTITID
            ) Result
            group by CONSTITID
            having count(*) > 1
        )
        raiserror('BBERR_DUPLICATEMEMBER', 13,1);

    insert into @CARDS(ID, EXPIRATIONDATE)
        select 
            T.membershipcards.value('(CONSTITUENTID)[1]','uniqueidentifier'),
            T.membershipcards.value('(EXPIRATIONDATE)[1]','datetime')
        from @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(membershipcards);

    if @NUMBEROFCARDSALLOWED < (select count(ID) from @CARDS)
        begin

            set @ERRORMSG = 
                case 
                    when @NUMBEROFCARDSALLOWED = 1 then 'Only 1 membership card is allowed for this membership.'
                    else 'Only ' + convert(nvarchar(6), @NUMBEROFCARDSALLOWED) + ' membership cards are allowed for this membership.'
                end;
            raiserror(@ERRORMSG, 13, 1)
        end
    if @COMMITONLYCHECK = 1
    begin
        if @ISGROUP = 0 
        begin
            -- individuals cannot have people on the cards that aren't on the membership

            if exists (
                select cards.ID
                from @CARDS cards
                left outer join @MEMBERS members
                    on cards.ID = members.ID
                where members.ID is null 
                    and cards.ID <> @CONSTITUENTID
            )
                raiserror('BBERR_CARDSFORMEMBERSONLY', 13, 1);
        end
        else
        begin
            -- groups however, can renew a membership for someone and not be on the card.

            if exists (
                select cards.ID
                from @CARDS cards
                left outer join @MEMBERS members
                    on cards.ID = members.ID
                left outer join dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, 'aef629df-9698-41b4-97c3-a83a40f87b18', 0) groupmembers
                    on cards.ID = groupmembers.ID
                where 
                    members.ID is null 
                    and groupmembers.ID is null
                    and cards.ID <> @CONSTITUENTID
            )
                raiserror('BBERR_CARDSFORMEMBERSONLY', 13, 1);
        end
    end

    if exists (
        select ID
        from @CARDS cards        
        where cards.EXPIRATIONDATE < @TRANSACTIONDATE
    )
        raiserror('BBERR_INVALIDCARDEXPIRATIONDATE', 13, 1);

    -- If renewing for a program

    if @MEMBERSHIPTRANSACTIONTYPECODE = 1 and exists (
        select 1
        from dbo.MEMBERSHIP M
            inner join dbo.MEMBER MB on M.ID = MB.MEMBERSHIPID
            inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
            inner join dbo.MEMBERSHIP existing on existing.ID = @EXISTINGMEMBERSHIPID
        where 
            M.STATUSCODE <> 1 -- membership is not cancelled

            and existing.STATUSCODE = 1 -- selected to renew is cancelled

            and MP.ALLOWMULTIPLEMEMBERSHIPS = 0
            and MP.ID = @MEMBERSHIPPROGRAMID
            and M.ID <> @EXISTINGMEMBERSHIPID
            and MB.CONSTITUENTID = @CONSTITUENTID
    )
        raiserror('BBERR_MUSTRENEW_ACTIVEMEMBERSHIP', 13, 1);
end