USP_SIMPLEDATALIST_GENERATETRANSACTIONSPROCESSBATCHTEMPLATE

CREATE procedure dbo.USP_SIMPLEDATALIST_GENERATETRANSACTIONSPROCESSBATCHTEMPLATE 
(
  @CURRENTAPPUSERID uniqueidentifier,
  @ISFORWEBSHELL bit = 0,
  @PAYMENTTYPECODE tinyint = 0,
  @EFTTYPECODE tinyint = 0,
  @FILTERINACTIVE tinyint = 0
)
as
set nocount on;

  declare @MULTICURRENCYENABLED as bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');

  declare @SQL nvarchar(max);
  declare @WHERE nvarchar(max);
  declare @WHEREOPTION nvarchar(500);
  declare @ISSYSADMIN bit = 0;
  select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;
  declare @GRANTEDBATCHTEMPLATEXML xml;
  set @SQL = ' 
    --return all batch templates with the required fields.

    with xmlnamespaces (
      ''bb_appfx_commontypes'' as c
    )
    select @GRANTEDBATCHTEMPLATEXML = 
    (select distinct 
      BATCHTEMPLATE.ID as VALUE, 
      BATCHTEMPLATE.NAME as LABEL,
      BATCHTEMPLATE.SITEID as SITEID
    from 
      dbo.BATCHTEMPLATE 
      left join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID ';

if @FILTERINACTIVE = 1
  begin
    set @WHEREOPTION = ' and BATCHTEMPLATE.ACTIVE = 1 '
  end
else
  begin
    set @WHEREOPTION = ' '
  end

set @WHERE = ' 
    where
      BATCHTEMPLATE.CUSTOM = 0' +
      @WHEREOPTION +
      'and BATCHTEMPLATE.TEMPLATEUSECODE in (0,1)
      and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
        (
          BATCHTYPECATALOG.SPECXML.query
            (
              ''declare namespace common="bb_appfx_commontypes";
              /*/common:InstalledProductList''
            )
        )
      and
        (
          isnull(@ISFORWEBSHELL, 0) = 0
          --Exclude batch types that have handlers but no web shell conversion when the list is loaded for web shell

          or 0 = BATCHTYPECATALOG.SPECXML.value
            (
              ''declare namespace batch="bb_appfx_batchtype";
              empty(/batch:BatchTypeSpec/batch:WebEventHandlers/batch:BatchEventHandler)
              and not(empty(/batch:BatchTypeSpec/batch:EventHandlers/batch:BatchEventHandler))'',
              ''bit''
            )
        ) 

      --Fields required by all EFT processes

      and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="DATE"])'') = 1
      and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="PAYMENTMETHODCODE"])'') = 1
      and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="POSTSTATUSCODE"])'') = 1 
      and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="SEQUENCE"])'') = 1';

     --ERB

     if @PAYMENTTYPECODE = 0
    begin
      set @WHERE = @WHERE + ' 
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="AMOUNT"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="TYPECODE"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="GIVENANONYMOUSLY"])'') = 1 
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REVENUESTREAMS"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="RECEIPTAMOUNT"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CONSTITUENTID"])'') = 1 '
    end

      --MDB

      if @PAYMENTTYPECODE = 1
      begin
      set @WHERE = @WHERE + ' 
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="WHATPAYINGFOR"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="MEMBERSHIPPROGRAMID"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="MEMBERSHIPLEVELID"])'') = 1 
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="BILLTOCONSTITUENTID"])'') = 1 
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="MEMBERSHIPLEVELTERMID"])'') = 1 '
      end

     if @EFTTYPECODE = 2 or @EFTTYPECODE = 0
     begin
      set @WHERE = @WHERE + ' 
       --Fields required specifically by the credit card processing process

        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CARDHOLDERNAME"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITCARDNUMBER"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITCARDTOKEN"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITTYPECODEID"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="EXPIRESON"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="AUTHORIZATIONCODE"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REJECTIONMESSAGE"])'') = 1 '
     end

     if @EFTTYPECODE = 1 or @EFTTYPECODE = 0
     begin
      set @WHERE = @WHERE + '
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CONSTITUENTACCOUNTID"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REFERENCENUMBER"])'') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REFERENCEDATE"])'') = 1 '
     end

    set @WHERE = @WHERE + '
      and (    
        ( --if not enhanced revenue batch, then multicurrency must be turned off

          (BATCHTYPECATALOG.ID <> ''326c43a6-d162-4fd4-8d61-fef9a0ee8c5e'' --Enhanced revenue batch

          or BATCHTYPECATALOG.ID <> ''196A2540-005A-4547-91A7-B301C464E28C'' --Membership Dues Batch)

          and @MULTICURRENCYENABLED = 0
        )
        or 
        ( --if enhanced revenue batch, the fields below are required

          (BATCHTYPECATALOG.ID = ''326c43a6-d162-4fd4-8d61-fef9a0ee8c5e'' --Enhanced revenue batch

          or BATCHTYPECATALOG.ID = ''196A2540-005A-4547-91A7-B301C464E28C'' --Membership Dues Batch)

          and
          BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="PDACCOUNTSYSTEMID"])'') = 1
          and (
            @MULTICURRENCYENABLED = 0
            or (
              BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="TRANSACTIONCURRENCYID"])'') = 1
              and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="BASECURRENCYID"])'') = 1
              and    BATCHTEMPLATE.FORMDEFINITIONXML.exist(''(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="BASEEXCHANGERATEID"])'') = 1
               )
          )
        )
        )
        )
        )
    for xml raw(''GRANTEDBATCHTEMPLATE''),root(''GRANTEDBATCHTEMPLATES'')); ';   

    set @SQL = @SQL + @WHERE;
    exec sp_executesql @SQL, N'@CURRENTAPPUSERID uniqueidentifier,    @ISFORWEBSHELL bit,  @PAYMENTTYPECODE tinyint,  @EFTTYPECODE tinyint, @MULTICURRENCYENABLED bit,@GRANTEDBATCHTEMPLATEXML xml output',
          @CURRENTAPPUSERID = @CURRENTAPPUSERID, @ISFORWEBSHELL=@ISFORWEBSHELL,@PAYMENTTYPECODE=@PAYMENTTYPECODE,@EFTTYPECODE=@EFTTYPECODE,@MULTICURRENCYENABLED=@MULTICURRENCYENABLED,@GRANTEDBATCHTEMPLATEXML=@GRANTEDBATCHTEMPLATEXML output


  if @ISSYSADMIN = 1
  begin

    select 
      GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') as VALUE,
      GRANTEDBATCHTEMPLATE.NODE.value('@LABEL', 'nvarchar(120)') as LABEL
    from @GRANTEDBATCHTEMPLATEXML.nodes('/GRANTEDBATCHTEMPLATES/GRANTEDBATCHTEMPLATE') GRANTEDBATCHTEMPLATE(NODE)
    order by GRANTEDBATCHTEMPLATE.NODE.value('@LABEL', 'nvarchar(120)');

  end
  else
  begin

    declare @USP_SIMPLEDATALIST_GENERATETRANSACTIONSPROCESSBATCHTEMPLATE table (
    VALUE uniqueidentifier,
    LABEL varchar(1000),
    GRANTED bit
    );

    insert into @USP_SIMPLEDATALIST_GENERATETRANSACTIONSPROCESSBATCHTEMPLATE
    exec dbo.USP_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR_BULK @GRANTEDBATCHTEMPLATEXML, @CURRENTAPPUSERID;

    select 
        VALUE,
        LABEL
    from @USP_SIMPLEDATALIST_GENERATETRANSACTIONSPROCESSBATCHTEMPLATE
    where GRANTED = 1
    order by LABEL;

  end