USP_REPORT_1099MISCACTIVITY_DIVIDED

CREATE procedure dbo.USP_REPORT_1099MISCACTIVITY_DIVIDED
(
  @STARTDATE datetime = null,
    @ENDDATE datetime = null,
  @VENDORID uniqueidentifier = null,
  @VENDORQUERYID uniqueidentifier = null,
  @INCLUDENON1099VENDORS bit = null,
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner as
  set nocount on;

  declare @CURRENTAPPUSERID uniqueidentifier;
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

  declare @SQLTOEXEC nvarchar(max);
  declare @DBOBJECTNAME nvarchar(128);
    declare @DBOBJECTTYPE smallint;

  if @VENDORQUERYID is not null
  begin
    if not exists(select ID from dbo.IDSETREGISTER where ID = @VENDORQUERYID) raiserror('ID set does not exist in the database.', 15, 1);
        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @VENDORQUERYID;
        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @VENDORQUERYID) + ''')';
  end

  set @SQLTOEXEC = '
    declare @1099DISTRIBUTION table (
      CONSTITUENTID nvarchar(200)
      ,LOOKUPID nvarchar(100)
      ,NAME nvarchar(100)
      ,BOX1 money
      ,BOX2 money
      ,BOX3 money
      ,BOX4 money
      ,BOX5 money
      ,BOX6 money
      ,BOX7 money
      ,BOX8 money
      ,BOX10 money
      ,BOX13 money
      ,BOX14 money
      ,BOX15a money
      ,BOX15b money
      ,BOX16 money
      ,BOX18STATE nvarchar(5)
    ); '

  set @SQLTOEXEC = @SQLTOEXEC + ' insert into @1099DISTRIBUTION
    select
      ''http://www.blackbaud.com?CONSTITUENTID='' + CONVERT(nvarchar(36), V.ID) as [CONSTITUENTID]
      ,C.LOOKUPID
      ,C.NAME
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''1'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX1]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''2'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX2]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''3'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX3]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''4'' and ST.ID is null THEN CASE WHEN RECORDTYPE.TYPECODE = 102 THEN -1 ELSE 1 END * FTAD.AMOUNT ELSE 0 END,0)) [BOX4]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''5'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX5]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''6'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX6]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''7'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX7]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''8'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX8]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''10'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX10]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''13'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX13]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''14'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX14]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''15a'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX15a]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''15b'' THEN FTAD.AMOUNT ELSE 0 END,0)) [BOX15b]
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''16'' and not (ST.ID is null) THEN CASE WHEN RECORDTYPE.TYPECODE = 102 THEN -1 ELSE 1 END * FTAD.AMOUNT ELSE 0 END,0)) [BOX16]
      ,ST.ABBREVIATION [BOX18STATE]
    from dbo.VENDOR V
    inner join dbo.CONSTITUENT C on C.ID = V.ID
    inner join dbo.FINANCIALTRANSACTION FT on FT.CONSTITUENTID = V.ID and FT.TYPECODE = 255 and FT.DELETEDON IS NULL
    inner join dbo.DISBURSEMENT D on FT.ID = D.ID
    inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.BANKACCOUNTTRANSACTIONID and BAT.DELETED = 0 and BAT.STATUSCODE != 4
 inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONID = FT.ID and FTA.TYPECODE = 0 and FTA.STATUSCODE = 1
    inner join dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION FTAD on FTAD.FINANCIALTRANSACTIONAPPLICATIONID = FTA.ID
    inner join dbo.FINANCIALTRANSACTION1099BOXNUMBER BOX on BOX.ID = FTAD.BOXNUMBER1099ID
    inner join (select distinct FTA1.ID, FT1.TYPECODE
      from dbo.FINANCIALTRANSACTION FT1
      inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.FINANCIALTRANSACTIONID = FT1.ID
      inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA1 on FTA1.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID) RECORDTYPE on RECORDTYPE.ID = FTA.ID
    left outer join dbo.STATE ST on ST.ID = FTAD.STATEID '

  if @VENDORQUERYID is not null
    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on V.[ID] = SELECTION.[ID]' + nchar(13);

  set @SQLTOEXEC = @SQLTOEXEC + ' where (V.INCLUDEIN1099 = 1 or @INCLUDENON1099VENDORS = 1)
    and ((BAT.TRANSACTIONDATE >= @STARTDATE or @STARTDATE is null) 
      and (BAT.TRANSACTIONDATE <= @ENDDATE or @ENDDATE is null))
    and (V.ID = @VENDORID or @VENDORID is null)
    and ((@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1) or
      dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, V.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)'

  set @SQLTOEXEC = @SQLTOEXEC + ' group by V.ID, C.LOOKUPID, C.NAME, FTAD.BOXNUMBER1099ID, ST.ABBREVIATION; '

  set @SQLTOEXEC = @SQLTOEXEC + ' insert into @1099DISTRIBUTION
    select
      ''http://www.blackbaud.com?CONSTITUENTID='' + CONVERT(nvarchar(36), V.ID) as [CONSTITUENTID]
      ,C.LOOKUPID
      ,C.NAME
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''1'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX1
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''2'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX2
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''3'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX3
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''4'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX4
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''5'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX5
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''6'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX6
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''7'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX7
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''8'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX8
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''10'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX10
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''13'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX13
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''14'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX14
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''15a'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX15a
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''15b'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX15b
      ,SUM(ISNULL(CASE WHEN BOX.BOXNUMBER=''16'' THEN ADJ.AMOUNT ELSE 0 END,0)) BOX16
      ,ST.ABBREVIATION [BOX18STATE]
    from dbo.VENDOR V
    inner join dbo.CONSTITUENT C on C.ID = V.ID
    inner join dbo.VENDOR1099ADJUSTMENT ADJ on ADJ.VENDOR1099SETTINGID = V.ID
    inner join dbo.FINANCIALTRANSACTION1099BOXNUMBER BOX on BOX.ID = ADJ.BOXNUMBER1099ID
    left outer join dbo.STATE ST on ST.ID = ADJ.STATEID '

  if @VENDORQUERYID is not null
    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on V.[ID] = SELECTION.[ID]' + nchar(13);

  set @SQLTOEXEC = @SQLTOEXEC + ' where (V.INCLUDEIN1099 = 1 or @INCLUDENON1099VENDORS = 1)
    and ((ADJ.EFFECTIVEDATE >= @STARTDATE or @STARTDATE is null) 
      and (ADJ.EFFECTIVEDATE <= @ENDDATE or @ENDDATE is null))
    and (V.ID = @VENDORID or @VENDORID is null)
    and ((@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1) or
      dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, V.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)'

  set @SQLTOEXEC = @SQLTOEXEC + ' group by V.ID, C.LOOKUPID, C.NAME, ADJ.BOXNUMBER1099ID, ST.ABBREVIATION; '

  set @SQLTOEXEC = @SQLTOEXEC + '
    select D.CONSTITUENTID
      ,D.LOOKUPID
      ,D.NAME
      ,SUM(D.BOX1) [BOX1]
      ,SUM(D.BOX2) [BOX2]
      ,SUM(D.BOX3) [BOX3]
      ,SUM(D.BOX4) [BOX4]
      ,SUM(D.BOX5) [BOX5]
      ,SUM(D.BOX6) [BOX6]
      ,SUM(D.BOX7) [BOX7]
      ,SUM(D.BOX8) [BOX8]
      ,SUM(D.BOX10) [BOX10]
      ,SUM(D.BOX13) [BOX13]
      ,SUM(D.BOX14) [BOX14]
      ,SUM(D.BOX15a) [BOX15a]
      ,SUM(D.BOX15b) [BOX15b]
      ,SUM(D.BOX16) [BOX16]
      ,D.BOX18STATE
    from @1099DISTRIBUTION D
    group by D.CONSTITUENTID,D.LOOKUPID,D.NAME,D.BOX18STATE; '

  exec sp_executesql @SQLTOEXEC,
        N'@STARTDATE datetime, @ENDDATE datetime, @VENDORID uniqueidentifier, @INCLUDENON1099VENDORS bit, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
    @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @VENDORID=@VENDORID, @INCLUDENON1099VENDORS=@INCLUDENON1099VENDORS, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;