USP_REPORT_INDIVIDUALINDUSTRYSEGMENTATIONREPORT

CREATE procedure dbo.USP_REPORT_INDIVIDUALINDUSTRYSEGMENTATIONREPORT
(
  @REPORTUSERID nvarchar(128) = null,
  @CONSTITUENTQUERY uniqueidentifier = null,
  @CURRENCYCODE smallint = null, --3 = My base, (null, 1) = Organization

  @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
  set nocount on;

  declare @SQLTOEXEC nvarchar(max);

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

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

  if @CONSTITUENTQUERY is not null begin
    if not exists(select ID from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY) 
      raiserror('ID set does not exist in the database.', 15, 1);

    select 
      @DBOBJECTNAME = DBOBJECTNAME, 
      @DBOBJECTTYPE = OBJECTTYPE
    from dbo.IDSETREGISTER 
    where ID = @CONSTITUENTQUERY;

    if @DBOBJECTTYPE = 1 
      set @DBOBJECTNAME = @DBOBJECTNAME + '()';
    else if @DBOBJECTTYPE = 2 
      set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @CONSTITUENTQUERY) + ''')';
  end

  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);
  set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

  declare @ASOFDATE datetime;
  set @ASOFDATE = getdate();

  declare @SELECTEDCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @ORIGINCODE tinyint;
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @ISOCURRENCYCODE nvarchar(3);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);

  if @CURRENCYCODE = 3
  begin
    if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
    begin                    
      select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
        from dbo.CURRENCYSET
        where
          CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
    end
    else
    begin
      select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
        from dbo.CURRENCYSET
        where
          CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
    end
  end
  else
    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  select
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE,
    @ISOCURRENCYCODE = ISO4217,
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE,
    @CURRENCYSYMBOL = CURRENCYSYMBOL
  from
    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);    

  select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

  set @SQLTOEXEC = '
  select 
    CONSTITUENT_NF.NAME,
    coalesce(CORPORATIONGIVINGTOTAL.AMOUNTINCURRENCY,0) as TOTALGIVING,
    coalesce(LATESTGIFT.AMOUNT, 0) as LATESTGIFTAMOUNT,
    LATESTGIFT.DATE as LATESTGIFTDATE,
    ORGANIZATION.NAME as ORGANIZATIONNAME,
    RELATIONSHIPJOBINFO.JOBTITLE,
    JOBCATEGORYCODE.DESCRIPTION as JOBCATEGORY,
    CAREERLEVELCODE.DESCRIPTION as CAREERLEVEL,
    INDUSTRYCODE.DESCRIPTION,
    ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + convert(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
    @ISOCURRENCYCODE [ISOCURRENCYCODE],
    @CURRENCYSYMBOL [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    @DECIMALDIGITS [DECIMALDIGITS]
  from 
    dbo.CONSTITUENT
  inner join
    dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID 
  left join
    dbo.RELATIONSHIPSET on RELATIONSHIPSET.ID = RELATIONSHIP.RELATIONSHIPSETID
  left join
    dbo.RELATIONSHIPJOBINFO on (RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIPSET.ID)
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

      and (@ASOFDATE between isnull(cast(RELATIONSHIPJOBINFO.STARTDATE as date), @ASOFDATE) and isnull(dateadd(ms, -003, dateadd(d, 1, cast(cast(RELATIONSHIPJOBINFO.ENDDATE as date) as datetime))), @ASOFDATE))
  inner join
    dbo.CONSTITUENT as ORGANIZATION on ORGANIZATION.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
  inner join 
    dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = ORGANIZATION.ID
  inner join 
    dbo.INDUSTRYCODE on INDUSTRYCODE.ID = ORGANIZATIONDATA.INDUSTRYCODEID
  left join 
    dbo.JOBCATEGORYCODE on JOBCATEGORYCODE.ID = RELATIONSHIPJOBINFO.JOBCATEGORYCODEID
  left join 
    dbo.CAREERLEVELCODE on CAREERLEVELCODE.ID = RELATIONSHIPJOBINFO.CAREERLEVELCODEID
  left join
    dbo.UFN_CORPORATION_GETGIVINGTOTAL_INCURRENCY_BULK_2(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE, @CURRENTAPPUSERID, ''EE399871-9447-435A-BD08-927D15D189DF'', 21) CORPORATIONGIVINGTOTAL on CORPORATIONGIVINGTOTAL.CONSTITUENTID = CONSTITUENT.ID';

  --This has to be split from above because the sql string is over the 4000 character limit...

  set @SQLTOEXEC += '
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
  outer apply
    (
      select top 1
        R.DATE,
        sum(coalesce(RS.AMOUNTINCURRENCY, 0)) - sum(coalesce(IWO.AMOUNT,0)) as AMOUNT
      from
        dbo.REVENUE R
      inner join
        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS on RS.REVENUEID = R.ID
      left join (
        select
          INSTALLMENTSPLIT.PLEDGEID,
          INSTALLMENTSPLIT.DESIGNATIONID,
          sum(IWO.AMOUNTINCURRENCY) as AMOUNT
        from 
          dbo.INSTALLMENTSPLIT
        inner join 
          dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE) IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
        group by 
          INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID
      ) IWO on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
      left join 
        dbo.INSTALLMENTSPLITPAYMENT ISP on RS.ID = ISP.PAYMENTID
      where (
        R.TRANSACTIONTYPECODE in (1,3) --Pledge or MG claim

        or (
          R.TRANSACTIONTYPECODE = 0 
          and (
            RS.APPLICATIONCODE in (0,1,3,4,5,6) 
            or (RS.APPLICATIONCODE = 7 and ISP.ID is null) --Unapplied MG claim payments

          )
        )
      ) 
      and
        R.CONSTITUENTID = CONSTITUENT.ID 
      and
        (@ISADMIN = 1 or dbo.UFN_DESIGNATION_USERHASSITEACCESS(RS.DESIGNATIONID,@CURRENTAPPUSERID) = 1)
      group by
        R.ID, R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, IWO.AMOUNT
      order by
        R.DATE desc, R.DATEADDED desc
    ) as LATESTGIFT
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
  ' + nchar(13);

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

  set @SQLTOEXEC = @SQLTOEXEC + '
  where 
    CONSTITUENT.ISORGANIZATION = 0
    and CONSTITUENT.ISGROUP = 0
    and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

    and (@ASOFDATE between coalesce(cast(RELATIONSHIPJOBINFO.STARTDATE as date), cast(RELATIONSHIP.STARTDATE as date), @ASOFDATE) 
                       and coalesce(dateadd(ms, -003, dateadd(d, 1, cast(cast(RELATIONSHIPJOBINFO.ENDDATE as date) as datetime))), dateadd(ms, -003, dateadd(d, 1, cast(cast(RELATIONSHIP.ENDDATE as date) as datetime))), @ASOFDATE))
    and ORGANIZATION.ISORGANIZATION = 1
    and (@ISADMIN = 1 or 
      (
        (@APPUSER_IN_NONRACROLE = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
        and
        (@APPUSER_IN_NONSITEROLE = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
      ))
  order by INDUSTRYCODE.DESCRIPTION, CONSTITUENT.KEYNAME;
  '

  exec sp_executesql @SQLTOEXEC,
    N'@ASOFDATE datetime, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @APPUSER_IN_NONSITEROLE bit, @APPUSER_IN_NOSITEROLE bit, @CURRENTAPPUSERID uniqueidentifier, @SELECTEDCURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ISOCURRENCYCODE nvarchar(3), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint, @CURRENCYSYMBOL nvarchar(5), @ORIGINCODE tinyint, @CURRENCYCODE smallint',
    @ASOFDATE=@ASOFDATE, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @APPUSER_IN_NONSITEROLE=@APPUSER_IN_NONSITEROLE, @APPUSER_IN_NOSITEROLE=@APPUSER_IN_NOSITEROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @SELECTEDCURRENCYID=@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @ISOCURRENCYCODE=@ISOCURRENCYCODE, @CURRENCYSYMBOLDISPLAYSETTINGCODE=@CURRENCYSYMBOLDISPLAYSETTINGCODE, @CURRENCYSYMBOL=@CURRENCYSYMBOL, @ORIGINCODE=@ORIGINCODE, @CURRENCYCODE=@CURRENCYCODE;

  return 0;