USP_GETMERCHANTACCOUNTIDFORPART

CREATE procedure dbo.USP_GETMERCHANTACCOUNTIDFORPART(
@CONTENTID int,
@TYPE int
)
as begin

    declare @MERCHANTACCOUNTID int=0;
    declare @SHOPPINGCARTPAGEID int=0;
    --Donation

    if(@TYPE=45)
        begin
            select @MERCHANTACCOUNTID=MERCHANTACCOUNT,@SHOPPINGCARTPAGEID=ShoppingCartPageID from CLIENTDONATIONS 
            where CONTENTID=@CONTENTID
        end
    else if(@TYPE=47 or @TYPE=154)--Event & Event 2.0

        begin
            select @MERCHANTACCOUNTID=MERCHANTACCOUNT,@SHOPPINGCARTPAGEID=ShoppingCartPageID from ClientEvents 
            where CONTENTID=@CONTENTID
        end
    else if(@Type=114)--ECard

        begin
            select @MERCHANTACCOUNTID=C.MERCHANTACCOUNT,@SHOPPINGCARTPAGEID=C.ShoppingCartPageID
            from ECARDPART E inner join ClientDonations C on E.ClientDonationsID=C.ID
            where E.SiteContentID=@CONTENTID and E.ClientDonationsID>0
        end
            else if(@Type=92)--PPM

        begin
            declare @xmlOptions as xml
            declare @donationSupported as bit;
            select top 1 @xmlOptions=Options from FUNDRAISERS where SITECONTENTID=@CONTENTID;

            select @donationSupported= @xmlOptions.value('(//SupportDonations)[1]','bit')

            if(@donationSupported=1)
            begin
                    select @MERCHANTACCOUNTID=C.MERCHANTACCOUNT from FundRaisers F inner join 
                    SiteContent SC
                    on F.SiteContentID=SC.ID INNER JOIN ClientDonations C
                    on SC.ID=C.ContentID WHERE SC.ID=@CONTENTID
            end
        end
    else if(@Type=118)--Transaction Manager

        begin
            select @MERCHANTACCOUNTID=G.MerchantAccountID from GivingHistory2 G
            where G.SiteContentID=@contentId
        end
    else if(@Type=9102)--Payment 2.0

        begin
            select @MERCHANTACCOUNTID=cm.ID from SHOPPINGCARTSETTINGS st inner join 
            SiteContent sc on st.ID=sc.Guid inner join ClientMerchantAccounts cm 
            on st.MERCHANTACCOUNTID=cm.BBPSID where sc.ID=@CONTENTID
        end
    else if(@Type=9111)--ADF

        begin
                        declare @xml As XML
                    declare @data  nvarchar(max)

                    select @data=S.XMLData from SiteContent S where 
                    S.ID=@CONTENTID

                    set @xml=cast(@data as xml);

                    ;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)

                    select @MERCHANTACCOUNTID=item.value('(Value/text())[1]','INT')
                    from @xml.nodes('//Item[@Name="MerchantAccountID"]') As T(item)

                    --May be this part is using Shoppingcart

                    if(isnull(@MERCHANTACCOUNTID,0)<=0)
                    begin
                      select @xml=cast(item.value('(Value/text())[1]','nvarchar(max)') as xml)
                       from @xml.nodes('//Item[@Name="BBNCCUSTOM"]') As T(item)

                       select @SHOPPINGCARTPAGEID=@xml.value('(//PaymentPageId)[1]','int')
                    end
        end
    else if(@TYPE=9103)--Sponsorship form

        begin
           select @SHOPPINGCARTPAGEID=PAYMENTPARTPAGE from dbo.SPONSORSHIPFORMCMSSETTINGS sc
           inner join SiteContent c on sc.ID=c.Guid where c.ID=@CONTENTID
        end
    else if(@TYPE=9101)--Sponsorship status

        begin
           select @SHOPPINGCARTPAGEID=PAYMENTPARTPAGE from dbo.SPONSORSHIPSTATUSCMSSETTINGS sc
           inner join SiteContent c on sc.ID=c.Guid where c.ID=@CONTENTID
        end
    else if(@TYPE=117) --Membership BBEC

        begin
           select @SHOPPINGCARTPAGEID=ShoppingCartPageID from dbo.ClientMemberships 
             where SiteContentId=@CONTENTID
        end
    else if(@TYPE=112) --Payment 1.0

        begin
           select @MERCHANTACCOUNTID=MerchantAccountID from dbo.ShoppingCartPart 
             where SiteContentID=@CONTENTID
        end

    --If this condition matches it means part is using shoppingcart.

    if (isnull(@MERCHANTACCOUNTID,0)<=0 and isnull(@SHOPPINGCARTPAGEID,0)>0)
       begin
              declare @ContentTypeID int=0;

                    select @ContentTypeID= sc.ContentTypesID from  dbo.SitePages sp inner join  
                    dbo.PageContent pc on sp.ID=pc.SitePagesID inner join 
                    dbo.SiteContent sc on pc.SiteContentId=sc.ID where sp.ID=@SHOPPINGCARTPAGEID

                    if(@ContentTypeID=9102) --Part is using Payment 2.0

                    begin
                        select @MERCHANTACCOUNTID=cm.ID from dbo.SHOPPINGCARTSETTINGS st inner join 
                        dbo.SiteContent sc on st.ID=sc.Guid inner join dbo.PageContent pc on sc.ID=pc.SiteContentID
                        inner join dbo.ClientMerchantAccounts cm 
                        on st.MERCHANTACCOUNTID=cm.BBPSID where pc.SitePagesID=@SHOPPINGCARTPAGEID
                    end
                    else if(@ContentTypeID=112) --Part is using Payment 1.0

                    begin
                        select @MERCHANTACCOUNTID=st.MerchantAccountID from dbo.ShoppingCartPart st inner join 
                        dbo.SiteContent sc on st.SiteContentID=sc.ID inner join dbo.PageContent pc 
                        on sc.ID=pc.SiteContentID where pc.SitePagesID=@SHOPPINGCARTPAGEID
                    end
       end

 select isnull(@MERCHANTACCOUNTID,0)
end;