USP_REPORT_ORGANIZATIONINDUSTRYSEGMENTATIONREPORT

        CREATE procedure dbo.USP_REPORT_ORGANIZATIONINDUSTRYSEGMENTATIONREPORT
        (
            @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 @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.NAME,
                    coalesce(ORGANIZATIONDATA.NUMEMPLOYEES, 0) as NUMEMPLOYEES,
                    coalesce(CORPORATIONGIVINGTOTAL.AMOUNTINCURRENCY,0) as TOTALGIVING,
                    coalesce(LATESTGIFT.AMOUNT, 0) as LATESTGIFTAMOUNT,
                    LATESTGIFT.DATE as LATESTGIFTDATE,
                    (select count(ID) from dbo.UFN_CORPORATION_GETEMPLOYEES_WITHDATE(CONSTITUENT.ID, getdate())) as CONSTITUENTEMPLOYEES,
                    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
                left join 
                    dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
                left join 
                    dbo.INDUSTRYCODE on INDUSTRYCODE.ID = ORGANIZATIONDATA.INDUSTRYCODEID
                left join
                    dbo.UFN_CORPORATION_GETGIVINGTOTAL_INCURRENCY_BULK_2(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE, @CURRENTAPPUSERID, ''3B3BE8D7-F429-4437-AF3E-47CC2B5035F3'', 21) CORPORATIONGIVINGTOTAL on CORPORATIONGIVINGTOTAL.CONSTITUENTID = CONSTITUENT.ID
                outer apply
                    (
                        select top 1
                            R.DATE,
                            sum(RS.AMOUNTINCURRENCY) - 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) or  --Pledge or MG claim

                                (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 = 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'@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',
                    @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;