USP_MEMBERSHIP_ADDFROMSALES

CREATE procedure dbo.USP_MEMBERSHIP_ADDFROMSALES
(
    @REVENUEID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @MEMBERSHIPID uniqueidentifier output,
    @AMOUNT money,
    @TRANSACTIONDATE datetime,
    @MEMBERSHIPS xml,
    @CREATIONDATE datetime,
    @CHANGEAGENTID uniqueidentifier,
    @MEMBERSHIPTRANSACTIONID uniqueidentifier = null output,
    @BASEAMOUNT money = null,
    @ORGANIZATIONAMOUNT money = null
)
as
begin
    set nocount on;

    declare @MEMBERSHIPSPLITID uniqueidentifier;

    declare @MEMBERSHIPPROGRAMID uniqueidentifier;
    declare @MEMBERSHIPLEVELID uniqueidentifier;
    declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
    declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier;
    declare @NUMBEROFCHILDREN tinyint;
    declare @COMMENTS nvarchar(1000);
    declare @ISGIFT bit;
    declare @SENDRENEWALCODE tinyint;
    declare @EXPIRATIONDATE datetime;
    declare @MEMBERS xml;
    declare @GIVENBYID uniqueidentifier;
    declare @MEMBERSHIPCARDS xml;
    declare @ADDONS xml;

    declare @ERRORMSG nvarchar(52)

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;

    declare @MEMBERSHIPREVENUECONTRIBUTEDTRANSACTIONAMOUNT money = 0
    declare @MEMBERSHIPREVENUECONTRIBUTEDBASEAMOUNT money = 0
    declare @MEMBERSHIPREVENUECONTRIBUTEDORGANIZATIONAMOUNT money = 0

    declare @TAXDEDUCTIBLEAMOUNT money = 0

    declare @POSTSTATUSCODE tinyint;
    declare @POSTDATE date;

    -- The REVENUESPLIT view pulls these values from the parent financial transaction

    -- record so we will do the same to be consistent with the old code.

    select
        @POSTSTATUSCODE = POSTSTATUSCODE,
        @POSTDATE = POSTDATE
    from
        dbo.FINANCIALTRANSACTION
    where
        ID = @REVENUEID;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

        select
            @MEMBERSHIPPROGRAMID = T.c.value('(MEMBERSHIPPROGRAMID)[1]','uniqueidentifier'),
            @MEMBERSHIPLEVELID = T.c.value('(MEMBERSHIPLEVELID)[1]','uniqueidentifier'),
            @MEMBERSHIPLEVELTERMID = T.c.value('(MEMBERSHIPLEVELTERMID)[1]','uniqueidentifier'),
            @MEMBERSHIPLEVELTYPECODEID = T.c.value('(MEMBERSHIPLEVELTYPECODEID)[1]','uniqueidentifier'),
            @EXPIRATIONDATE = T.c.value('(EXPIRATIONDATE)[1]','datetime'),
            @NUMBEROFCHILDREN = T.c.value('(NUMBEROFCHILDREN)[1]','tinyint'),
            @COMMENTS = T.c.value('(COMMENTS)[1]','nvarchar(1000)'),
            @ISGIFT = T.c.value('(ISGIFT)[1]','bit'),
            @SENDRENEWALCODE = T.c.value('(SENDRENEWALCODE)[1]','tinyint'),            
            @GIVENBYID = T.c.value('(GIVENBYID)[1]','uniqueidentifier'),
            @MEMBERS = T.c.query('./MEMBERS'),
            @MEMBERSHIPCARDS = T.c.query('./MEMBERSHIPCARDS'),
            @ADDONS = T.c.query('./ADDONS')
        from @MEMBERSHIPS.nodes('/MEMBERSHIPFIELDS/ITEM') T(c)

        declare @MEMBERSALLOWED int;
        declare @MEMBERSHIPCARESALLOWED int;
        declare @CHILDRENALLOWED int;

        select 
            @CHILDRENALLOWED = ML.CHILDRENALLOWED,
            @MEMBERSALLOWED = ML.MEMBERSALLOWED,
            @MEMBERSHIPCARESALLOWED = ML.CARDSALLOWED,
            @TAXDEDUCTIBLEAMOUNT = case MP.DEDUCTIBILITYCODE
                                    when 0 then @AMOUNT
                                    when 1 then ML.RECEIPTAMOUNT
                                    else 0
                                   end 
        from dbo.MEMBERSHIPLEVEL ML
            inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
        where ML.ID = @MEMBERSHIPLEVELID

        if @TAXDEDUCTIBLEAMOUNT > @AMOUNT
        begin
            declare @FULLAMOUNT money
            select @FULLAMOUNT = AMOUNT 
            from dbo.MEMBERSHIPLEVELTERM
            where ID = @MEMBERSHIPLEVELTERMID

            if @FULLAMOUNT = 0 
           set @TAXDEDUCTIBLEAMOUNT = 0
            else
                set @TAXDEDUCTIBLEAMOUNT = @AMOUNT * (@TAXDEDUCTIBLEAMOUNT / @FULLAMOUNT)
        end

        set @MEMBERSALLOWED += (
            select sum(T.addons.value('(QUANTITY)[1]','int')) from @ADDONS.nodes('/ADDONS/ITEM') T(addons) where T.addons.value('(ADDONTYPECODE)[1]','tinyint') = 1
        )

        -- Validate membership fields


        -- Expiration date

        if @EXPIRATIONDATE < @TRANSACTIONDATE
            raiserror('The expiration date must be after the transaction date.', 13, 1);

        if exists (select * from dbo.MEMBERSHIPTRANSACTION 
                    where MEMBERSHIPID = @MEMBERSHIPID and 
                        TRANSACTIONDATE > @TRANSACTIONDATE)
            raiserror('Transaction date cannot be earlier than the last transaction of the membership.', 13, 1);

        -- Primary member

        if not exists ( select 1 from @MEMBERS.nodes('/MEMBERS/ITEM') T(members) where T.members.value('(ISPRIMARY)[1]','bit') = 1)
            raiserror('Please select one member as a primary member.', 13, 1);

        -- Number of members

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

        if @MEMBERS is null
            raiserror('Please enter at least one member.', 13, 1);

        if @MEMBERSALLOWED < (select count(T.members.value('(MEMBERID)[1]','uniqueidentifier')) from @MEMBERS.nodes('/MEMBERS/ITEM') T(members))
            raiserror(@ERRORMSG, 13, 1);

        -- Number of children

        set @ERRORMSG = 'The number of children may not be larger than '+convert(nvarchar(6), @CHILDRENALLOWED)+ '.'

        if @CHILDRENALLOWED < @NUMBEROFCHILDREN
            raiserror(@ERRORMSG, 13, 1);

        -- Number of membership cards

        set @ERRORMSG = 'This membership level only allows up to '+convert(nvarchar(6), @MEMBERSHIPCARESALLOWED) + ' card(s).'

        if @MEMBERSHIPCARESALLOWED < (select count(T.cards.value('(MEMBERSHIPCARDID)[1]','uniqueidentifier')) from @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(cards))
            raiserror(@ERRORMSG, 13, 1);

        -- Constituent

        if exists(
            select count(CONSTITUENTID)
            from (
                select T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID'
                from @MEMBERS.nodes('/MEMBERS/ITEM') T(members)
            ) MEMBERS
            group by CONSTITUENTID
            having count(*) > 1
        )
            raiserror('Please do not add a constituent more than once to the membership.', 13, 1);

        -- Get multicurrency values from the revenue.

        select
            @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
            @BASECURRENCYID = BASECURRENCYID,
            @BASEEXCHANGERATEID = BASEEXCHANGERATEID,
            @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
        from dbo.REVENUE
        where ID = @REVENUEID;

        -- Convert the applied amount into base and organization amounts if it is not provided by the caller

        if @BASEAMOUNT is null or @ORGANIZATIONAMOUNT is null
            exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @TRANSACTIONDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1, @BASETOORGANIZATIONEXCHANGERATEID;

        declare @MEMBERSHIPREVENUESPLITTABLE table (
            ID uniqueidentifier,
            APPLICATIONCODE tinyint,
            TYPECODE tinyint,
            DESIGNATIONID uniqueidentifier,
            AMOUNT money,
            DECLINESGIFTAID bit,
            TRANSACTIONCURRENCYID uniqueidentifier,
            ADDONID uniqueidentifier
        )

        insert into @MEMBERSHIPREVENUESPLITTABLE(
                ID,
                APPLICATIONCODE,
                TYPECODE,
                DESIGNATIONID,
                AMOUNT,
                DECLINESGIFTAID,
                TRANSACTIONCURRENCYID
        ) select newid(),
                0,
                0,
                CONTRIBUTEDAMOUNTS.DESIGNATIONID, 
                CONTRIBUTEDAMOUNTS.AMOUNT,
                0,
                @TRANSACTIONCURRENCYID
        from dbo.UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID(@MEMBERSHIPLEVELID, @TAXDEDUCTIBLEAMOUNT, 2) as CONTRIBUTEDAMOUNTS

        insert into @MEMBERSHIPREVENUESPLITTABLE(
                ID,
                APPLICATIONCODE,
                TYPECODE,
                DESIGNATIONID,
                AMOUNT,
                DECLINESGIFTAID,
                TRANSACTIONCURRENCYID,
                ADDONID
        ) select T.addons.value('(SPLITID)[1]','uniqueidentifier'),
                18,
                18,
                null,
                T.addons.value('(PRICE)[1]','money') * T.addons.value('(QUANTITY)[1]','int'),
                0,
                @TRANSACTIONCURRENCYID,
                T.addons.value('(ADDONID)[1]','uniqueidentifier')
        from @ADDONS.nodes('/ADDONS/ITEM') T(addons)

        select @MEMBERSHIPREVENUECONTRIBUTEDTRANSACTIONAMOUNT = sum(coalesce(AMOUNT, 0)),
                @MEMBERSHIPREVENUECONTRIBUTEDBASEAMOUNT = sum(coalesce(case when @BASEEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @BASEEXCHANGERATEID) end, 0)),
                @MEMBERSHIPREVENUECONTRIBUTEDORGANIZATIONAMOUNT = sum(coalesce(AMOUNT, 0))
        from @MEMBERSHIPREVENUESPLITTABLE
        where APPLICATIONCODE = 0

        set @MEMBERSHIPSPLITID = newid()

        insert into dbo.FINANCIALTRANSACTIONLINEITEM (
            ID,
            FINANCIALTRANSACTIONID,
            BASEAMOUNT,
            TRANSACTIONAMOUNT,
            ORGAMOUNT,
            TYPECODE,
            POSTSTATUSCODE,
            POSTDATE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        ) values (
            @MEMBERSHIPSPLITID,
            @REVENUEID,
            @BASEAMOUNT - coalesce(@MEMBERSHIPREVENUECONTRIBUTEDBASEAMOUNT, 0),
            @AMOUNT - coalesce(@MEMBERSHIPREVENUECONTRIBUTEDTRANSACTIONAMOUNT, 0),
            @ORGANIZATIONAMOUNT - coalesce(@MEMBERSHIPREVENUECONTRIBUTEDORGANIZATIONAMOUNT, 0),
            0,
            @POSTSTATUSCODE,
            @POSTDATE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        );

        insert into dbo.REVENUESPLIT_EXT (
            ID,
            APPLICATIONCODE,
            TYPECODE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        ) values (
            @MEMBERSHIPSPLITID,
            5,
            2,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        );

        insert into dbo.FINANCIALTRANSACTIONLINEITEM (
            ID,
            FINANCIALTRANSACTIONID,
            BASEAMOUNT,
            TRANSACTIONAMOUNT,
            ORGAMOUNT,
            TYPECODE,
            POSTSTATUSCODE,
            POSTDATE,
            SOURCELINEITEMID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select 
            ID,
            @REVENUEID,
            case when @BASEEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @BASEEXCHANGERATEID) end,
            AMOUNT,
            AMOUNT,
            0,
            @POSTSTATUSCODE,
            @POSTDATE,
            @MEMBERSHIPSPLITID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
            @MEMBERSHIPREVENUESPLITTABLE

        insert into dbo.REVENUESPLIT_EXT (
            ID,
            APPLICATIONCODE,
            TYPECODE,
            DESIGNATIONID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select 
            ID,
            APPLICATIONCODE,
            TYPECODE,
            DESIGNATIONID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
            @MEMBERSHIPREVENUESPLITTABLE

        if exists(select 1 from @MEMBERSHIPREVENUESPLITTABLE where APPLICATIONCODE = 18)
        begin        
            --Create recognitions for the addon splits

            declare @MEMBERSHIPREVENUEADDONSPLITID uniqueidentifier
            declare MEMBERSHIPREVENUEADDONCURSOR cursor local fast_forward for select ID from @MEMBERSHIPREVENUESPLITTABLE where APPLICATIONCODE = 18
            open MEMBERSHIPREVENUEADDONCURSOR
            fetch next from MEMBERSHIPREVENUEADDONCURSOR into @MEMBERSHIPREVENUEADDONSPLITID
            while @@FETCH_STATUS = 0
            begin
                exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @MEMBERSHIPREVENUEADDONSPLITID, @CHANGEAGENTID, @CURRENTDATE;

                fetch next from MEMBERSHIPREVENUEADDONCURSOR into @MEMBERSHIPREVENUEADDONSPLITID
            end
            close MEMBERSHIPREVENUEADDONCURSOR
            deallocate MEMBERSHIPREVENUEADDONCURSOR
        end

        -- create recognitions for the membership part of the payment

        exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @MEMBERSHIPSPLITID, @CHANGEAGENTID, @CURRENTDATE;

        -- create recognitions for any contributed portion of the membership payment

        insert into dbo.REVENUERECOGNITION
        (
            REVENUESPLITID,
            CONSTITUENTID,
            AMOUNT,
            EFFECTIVEDATE,
            REVENUERECOGNITIONTYPECODEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID
        )
        select
            MEMBERSHIPSPLITS.ID,
            @CONSTITUENTID,
            MEMBERSHIPSPLITS.AMOUNT,
            @TRANSACTIONDATE,
            null,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            @BASECURRENCYID,
            case when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    then dbo.UFN_CURRENCY_CONVERT(MEMBERSHIPSPLITS.AMOUNT, @BASETOORGANIZATIONEXCHANGERATEID)
                else MEMBERSHIPSPLITS.AMOUNT
            end,
            @BASETOORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID
        from @MEMBERSHIPREVENUESPLITTABLE MEMBERSHIPSPLITS
        where MEMBERSHIPSPLITS.APPLICATIONCODE = 0

        exec dbo.USP_MEMBERSHIP_ADDMEMBERSHIPTRANSACTION
            @MEMBERSHIPID output,
            @CHANGEAGENTID,
            @CONSTITUENTID,
            @MEMBERSHIPPROGRAMID,
            @MEMBERSHIPLEVELID,
            @MEMBERSHIPLEVELTERMID,
            @MEMBERSHIPLEVELTYPECODEID,
            @TRANSACTIONDATE,
            @EXPIRATIONDATE,
            @NUMBEROFCHILDREN,
            @COMMENTS,
            @ISGIFT,
            @GIVENBYID,
            @SENDRENEWALCODE,
            @MEMBERS,
            @MEMBERSHIPCARDS,
            @ADDONS,
            @MEMBERSHIPSPLITID,
            @MEMBERSHIPTRANSACTIONID output;


 insert into dbo.MEMBERSHIPCONTRIBUTIONPORTION
                (
                ID,
                FINANCIALTRANSACTIONLINEITEMID,
                MEMBERSHIPPROGRAMID,
                MEMBERSHIPTRANSACTIONID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
                )
              select
                newid(),
                ID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              from
                          @MEMBERSHIPREVENUESPLITTABLE
              where APPLICATIONCODE = 0


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

    return 0;
end