USP_NON_CHECKOUT_PARTS_EXISTS

CREATE procedure dbo.USP_NON_CHECKOUT_PARTS_EXISTS(
@SITEID int,
@NONCHECKOUTMERCHANTS varchar(250)
)
as
begin

  --Logic for converting , separated merchants to rows.

  declare @delimeter CHAR(1)=',';
  declare @pos int;
  declare @intVal int;
  declare @item varchar(25);

  declare @tblMerchants table(Merchants int);


  while len(@NONCHECKOUTMERCHANTS)>0
  begin
       set @pos=charindex(@delimeter,@NONCHECKOUTMERCHANTS)
       if @pos=0
          set @pos=len(@NONCHECKOUTMERCHANTS)+1

       set @item= LEFT(@NONCHECKOUTMERCHANTS,@pos-1)
       set @NONCHECKOUTMERCHANTS=substring(@NONCHECKOUTMERCHANTS,@pos+1,len(@NONCHECKOUTMERCHANTS))
       set @intVal=cast(@item as int)

       insert into @tblMerchants values(@intVal)   
  end;


  drop table if exists #tmpNonCheckoutParts;

  --Insert logic begins


  --Donation Classic

      select top 1 SC.ID into #tmpNonCheckoutParts from 
      dbo.SiteContent SC inner join dbo.ClientDonations PT on SC.ID=PT.ContentID
      inner join @tblMerchants TM on PT.MerchantAccount=TM.Merchants
      where  SC.ClientSitesID=@SITEID and SC.Deleted=0 and SC.ContentTypesID=45
  union--Event,Event 2.0

      select top 1 SC.ID from dbo.SiteContent SC inner join 
      dbo.ClientEvents PT on SC.ID=PT.ContentID
      inner join @tblMerchants TM on PT.MerchantAccount=TM.Merchants
      where SC.ClientSitesID=@SITEID and SC.Deleted=0 and SC.ContentTypesID in(47,154)
  union--Transaction Manager

      select top 1 SC.ID from dbo.SiteContent SC inner join 
      dbo.GivingHistory2 PT on SC.ID=PT.SiteContentID
      inner join @tblMerchants TM on PT.MerchantAccountID=TM.Merchants
      where SC.Deleted=0 and SC.ContentTypesID=118
      and SC.ClientSitesID=@SITEID 
  union--Payment 2.0

      select top 1 SC.ID from dbo.SHOPPINGCARTSETTINGS ST inner join 
      dbo.SiteContent SC on ST.ID=SC.Guid inner join dbo.ClientMerchantAccounts CM
      inner join @tblMerchants TM on CM.ID=TM.Merchants
      on ST.MERCHANTACCOUNTID=CM.BBPSID 
      where SC.ClientSitesID=@SITEID and SC.Deleted=0 and SC.ContentTypesID=9102
  union --Ecard

      select top 1 SC.ID from dbo.ECARDPART E inner join dbo.ClientDonations C
      on E.ClientDonationsID=C.ID inner join SiteContent SC on E.SiteContentID=SC.ID
      inner join @tblMerchants TM on C.MerchantAccount=TM.Merchants
      where  E.ClientDonationsID>0 and SC.Deleted=0 and SC.ContentTypesID=114
  union --PPM

     select top 1 SC.ID from dbo.FUNDRAISERS F 
     inner join dbo.SiteContent SC on F.SiteContentID=SC.ID
     inner join dbo.ClientDonations C on SC.ID=C.ContentID
     inner join @tblMerchants TM on C.MerchantAccount=TM.Merchants
     where CONVERT(xml,options).value('(//SupportDonations)[1]','bit')=1 
     and SC.Deleted=0 and SC.ClientSitesID=@SITEID and SC.ContentTypesID=92
    union --Payment 1.0

    select top 1 SC.ID from dbo.ShoppingCartPart ST inner join 
      dbo.SiteContent SC on ST.SiteContentID=SC.ID inner join 
      @tblMerchants TM on ST.MerchantAccountID=TM.Merchants
      where SC.ClientSitesID=@SITEID and
      SC.Deleted=0 and SC.ContentTypesID=112

     --Code for ADF begins

     declare @prtADF AS table(PartID INT)
     declare @tblADFMerchants AS table(MerchantId INT)

     insert into @prtADF
     select ID from dbo.SiteContent SC where SC.ContentTypesID=9111 and SC.Deleted=0 and 
     SC.ClientSitesID=@SITEID


     declare @MAX int,@COUNTER INT=1

     select @MAX=count(*) from @prtADF

        while @COUNTER<=@MAX
        begin

                declare @PartId INT

                select @PartId=PartId from
                (
                    select *,row_number() over (order by (select null)) as RowNo from @prtADF
                ) AS T
                where RowNo=@COUNTER

                    insert into @tblADFMerchants
                    exec dbo.USP_GETMERCHANTACCOUNTIDFORPART @partId,9111--ADF ContentTypeId


                set @COUNTER=@COUNTER+1;

        end

  insert into #tmpNonCheckoutParts(ID)
  select top 1 1 from @tblADFMerchants TA inner join @tblMerchants TM on TA.MerchantId=TM.Merchants
 --ADF Code Ends--



  select count(*) from #tmpNonCheckoutParts;

  drop table #tmpNonCheckoutParts;

end;