USP_MKTSEGMENTATION_GETSUMMARYINFO

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETSUMMARYINFO]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @BBECAPPEALID nvarchar(36);
  declare @BBECAPPEALGUID uniqueidentifier;
  declare @BBECAPPEALDESCRIPTION nvarchar(100);
  declare @COLLAPSESUMMARYSECTION bit;
  declare @SHOWLOCKEDFIELDS bit;

  --Make sure the appeal name and description are up-to-date in our table...

  exec dbo.[USP_MKTSEGMENTATIONACTIVATE_UPDATEAPPEALINFO] @SEGMENTATIONID;

  select 
    @BBECAPPEALID = [APPEALSYSTEMID]
  from dbo.[MKTSEGMENTATIONACTIVATE] 
  where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID
  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1;

  begin try
    set @BBECAPPEALGUID = convert(uniqueidentifier, @BBECAPPEALID);
  end try
  begin catch
     -- yum!

    set @BBECAPPEALGUID = null;
  end catch

  if not @BBECAPPEALGUID is null
    select
      @BBECAPPEALDESCRIPTION = [NAME]
    from dbo.[APPEAL] 
    where [ID] = @BBECAPPEALGUID;

  if @CURRENTAPPUSERID is not null
    select
      @COLLAPSESUMMARYSECTION = [COLLAPSESUMMARYSECTION],
      @SHOWLOCKEDFIELDS = [SHOWLOCKEDFIELDS]
    from dbo.[MKTCOMMUNICATIONEFFORTUSERSETTINGS]
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and [APPUSERID] = @CURRENTAPPUSERID;

  select
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[IDINTEGER],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[ACTIVATEDATE],
    --Get a comma delimited list of appeals

    isnull(stuff(
                  (
                    select ', ' + case when [APPEALSYSTEMID] = @BBECAPPEALID then @BBECAPPEALDESCRIPTION else [APPEALDESCRIPTION] end
                    from dbo.[MKTSEGMENTATIONACTIVATE]
                    where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                    for xml path(''), type
                  ).value('.', 'varchar(max)')
                  , 1, 2, ''
                ), '') as [APPEAL],
    [EFFORTSTATUS].[DATEREFRESHED],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMSEGMENTS],
    (select count([ID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMPACKAGES],
    [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
    [MKTSEGMENTATIONBUDGET].[FIXEDCOST],
    [MKTSEGMENTATION].[MARKETINGPLANITEMID],
    (select (case when count(*) = 0 then (select case when (select count([ID]) from dbo.[MKTGIFTRECORDSOURCE]) > 0 then 1 else 0 end) else 0 end)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
      and [MKTGIFTRECORDSOURCE].[ID] is null) as [GIFTSOURCESDEFINED],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEED] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMSEEDS],
    (select count([TEST].[ID]) from dbo.[MKTSEGMENTATION] as [TEST] where [TEST].[PARENTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMTESTMAILINGS],
    [MKTSEGMENTATION].[MAILDATE],
    (select [NAME] from dbo.[SITE] where [ID] = [MKTSEGMENTATION].[SITEID]) as [SITE],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    [MKTSEGMENTATIONBUDGET].[FIXEDCOST] as [MAILINGFIXEDCOST],
    convert(uniqueidentifier, isnull(@BBECAPPEALGUID, '00000000-0000-0000-0000-000000000000')) as [BBECAPPEALID],
    isnull(@BBECAPPEALDESCRIPTION, '') as [BBECAPPEALDESCRIPTION],
    [MKTSEGMENTATION].[BASECURRENCYID],
    dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTSEGMENTATION].[BASECURRENCYID]) [CURRENCY],
    [MKTSEGMENTATION].[ISHISTORICAL],
    [MKTSEGMENTATION].[DATEADDED],
    [EFFORTSTATUS].[CALCULATEDATE] as [LASTCALCULATEDATE],
    [EFFORTSTATUS].[EXPORTDATE] as [LASTEXPORTDATE],
    [EFFORTSTATUS].[REMOVEMEMBERSDATE],
    [EFFORTSTATUS].[CALCULATEPROCESSID],
    [EFFORTSTATUS].[EXPORTPROCESSID],
    [EFFORTSTATUS].[ACTIVATEPROCESSID],    
    [EFFORTSTATUS].[REFRESHPROCESSID],
    [EFFORTSTATUS].[REMOVEMEMBERSPROCESSID],
    [MKTSEGMENTATION].[DUEDATE],
    [MKTSEGMENTATION].[CHANNEL],
    [PARENTTEMPLATE].[NAME] as [TEMPLATENAME],
    (select [DISPLAYNAME] from dbo.[APPUSER] where [ID] = [MKTSEGMENTATION].[OWNERID]) as [OWNER],
    isnull(@COLLAPSESUMMARYSECTION, 0) as [COLLAPSESUMMARYSECTION],
    isnull(@SHOWLOCKEDFIELDS, 0) as [SHOWLOCKEDFIELDS],
    [EFFORTSTATUS].[CALCULATESTATUSCODE],
    [EFFORTSTATUS].[EXPORTSTATUSCODE],
    [EFFORTSTATUS].[ACTIVATESTATUSCODE],
    [EFFORTSTATUS].[REFRESHSTATUSCODE],
    [EFFORTSTATUS].[REMOVEMEMBERSSTATUSCODE],
    [MKTCOMMUNICATIONTEMPLATE].[INCLUDESEEDS],
    cast((case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end) as bit) [ISBBEC]

  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
  left join dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETSTATUSINFO_BULK]() as [EFFORTSTATUS] on [EFFORTSTATUS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] as [PARENTTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[PARENTCOMMUNICATIONTEMPLATEID] = [PARENTTEMPLATE].[ID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  return 0;