USP_MKTSEGMENTATIONEXPORTPROCESSOUTPUTEMAIL_GETPACKAGES

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESSOUTPUTEMAIL_GETPACKAGES]
(
  @BUSINESSPROCESSOUTPUTID uniqueidentifier
)
as
  set nocount on;

  declare @BUSINESSPROCESSSTATUSID uniqueidentifier;
  declare @TABLEKEY nvarchar(50);
  declare @OUTPUTTABLENAME nvarchar(255);
  declare @SUMMARYTABLENAME nvarchar(255);
  declare @DATATABLENAME nvarchar(255);
  declare @SQL nvarchar(max);
  declare @STATUS xml;

  select 
    @BUSINESSPROCESSSTATUSID = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID],
    @TABLEKEY = [BUSINESSPROCESSOUTPUT].[TABLEKEY],
    @OUTPUTTABLENAME = [BUSINESSPROCESSOUTPUT].[TABLENAME],
    @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID])
  from dbo.[BUSINESSPROCESSOUTPUT]
  inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID]
  inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
  where [BUSINESSPROCESSOUTPUT].[ID] = @BUSINESSPROCESSOUTPUTID;

  select @SUMMARYTABLENAME = [TABLENAME] from dbo.[BUSINESSPROCESSOUTPUT] where [TABLEKEY] = convert(nvarchar(36), @BUSINESSPROCESSSTATUSID);

  set @SQL = 
    'declare @PACKAGES table([PACKAGEID] uniqueidentifier, [PACKAGENAME] nvarchar(100), [NETCOMMUNITYDATASOURCEID] integer, [NETCOMMUNITYTEMPLATEID] integer, [NETCOMMUNITYEMAILID] integer, [STATUSCODE] tinyint, [STATUSDATE] datetime, [STATUSMESSAGE] nvarchar(255), [EXPORTDEFINITIONID] uniqueidentifier, [EXPORTDEFINITION] nvarchar(255), [STATUSDETAIL] nvarchar(max))' + char(13);

  set @SQL = @SQL +
    'insert into @PACKAGES([PACKAGEID], [PACKAGENAME], [NETCOMMUNITYDATASOURCEID], [NETCOMMUNITYTEMPLATEID], [EXPORTDEFINITIONID], [EXPORTDEFINITION])' + char(13) +
    'select' + char(13) +
    '  [MKTPACKAGE].[ID] as [PACKAGEID],' + char(13) +
    '  [MKTPACKAGE].[NAME] as [PACKAGENAME],' + char(13) +
    '  [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID],' + char(13) +
    '  [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID],' + char(13) +
    '  [EXPORTDEFINITION].[ID] as [EXPORTDEFINITIONID],' + char(13) +
    '  [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION]' + char(13) +
    'from dbo.[MKTPACKAGE]' + char(13) +
    'inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID]' + char(13) +
    'inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]' + char(13) +
    'where [MKTPACKAGE].[ID] in (select distinct [PACKAGEID] from dbo.[' + @OUTPUTTABLENAME + ']);' + char(13);

  set @SQL = @SQL +
    'declare @STATUS xml;' + char(13) +
    'select @STATUS = [STATUS] from dbo.[' + @SUMMARYTABLENAME + '] where [TABLEKEY] = ''' + @TABLEKEY + '''' + char(13) +
    'update @PACKAGES set' + char(13) +
    '  [NETCOMMUNITYEMAILID] = T.c.value(''(NETCOMMUNITYEMAILID)[1]'', ''integer''),' + char(13) +
    '  [STATUSCODE] = T.c.value(''(STATUSCODE)[1]'', ''tinyint''),' + char(13) +
    '  [STATUSDATE] = T.c.value(''(STATUSDATE)[1]'', ''datetime''),' + char(13) +
    '  [STATUSMESSAGE] = T.c.value(''(STATUSMESSAGE)[1]'', ''nvarchar(255)''),' + char(13) +
    '  [STATUSDETAIL] = T.c.value(''(STATUSDETAIL)[1]'', ''nvarchar(max)'')' + char(13) +
    'from @STATUS.nodes(''/STATUS/ITEM'') T(c)' + char(13) +
    'left outer join @PACKAGES as [P] on [P].[PACKAGEID] = T.c.value(''(PACKAGEID)[1]'', ''uniqueidentifier'')' + char(13);

  set @SQL = @SQL +
    'if exists (select top 1 1 from @PACKAGES as [P] where [P].[STATUSCODE] is null or [P].[STATUSCODE] = 0) begin' + char(13) +
    '  declare @CHECKFORFULLNAME table ([QUERYVIEWCATALOGID] uniqueidentifier, [PACKAGEID] uniqueidentifier, [FULLNAMEFIELD] nvarchar(510))' + char(13);

  set @SQL = @SQL +
    '  insert into @CHECKFORFULLNAME' + char(13) +
    '  select distinct' + char(13) +
    '    [DATA].[DONORQUERYVIEWCATALOGID],' + char(13) +
    '    isnull([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID]) as [PACKAGEID],' + char(13) +
    '    [MKTRECORDSOURCEFIELDMAPPINGS].[FULLNAMEFIELD]' + char(13) +
    '  from dbo.[' + @DATATABLENAME + '] as [DATA]' + char(13) +
    '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID]' + char(13) +
    '  left outer join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [DATA].[TESTSEGMENTID]' + char(13) +
    '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
    '  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]' + char(13) +
    '  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = isnull([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID])' + char(13) +
    '  inner join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]' + char(13) +
    '  where [MKTPACKAGE].[CHANNELCODE] = 1 and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0' + char(13) +
    '  and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)' + char(13) +
    '  and [DATA].[DONORQUERYVIEWCATALOGID] <> [MKTSEGMENT].[QUERYVIEWCATALOGID]' + char(13);

  set @SQL = @SQL +
    '  update @PACKAGES set' + char(13) +
    '    [STATUSCODE] = 7,' + char(13) +
    '    [STATUSMESSAGE] = ''##FULLNAMEWARNING##''' + char(13) +
    '  from @PACKAGES as [PACKAGES]' + char(13) +
    '  inner join @CHECKFORFULLNAME as [CHECKFORFULLNAME] on [CHECKFORFULLNAME].[PACKAGEID] = [PACKAGES].[PACKAGEID]' + char(13) +
    '  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [CHECKFORFULLNAME].[QUERYVIEWCATALOGID]' + char(13) +
    '  where ([PACKAGES].[STATUSCODE] is null or [PACKAGES].[STATUSCODE] = 0)' + char(13) +
    '  and not exists (' + char(13) +
    '    select top 1 1' + char(13) +
    '    from [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML].nodes(''declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField'') as T(c)' + char(13) +
    '    where T.c.value(''(@Name)[1]'', ''nvarchar(510)'') = [CHECKFORFULLNAME].[FULLNAMEFIELD])' + char(13) +
    'end' + char(13);

  set @SQL = @SQL +
    'select * from @PACKAGES;';

  exec sp_executesql @SQL;

  -- use this to get the SPWrap Code Generator to update the wrapper code:

  -- 1. make the SPWRAP table's shape match that of the SQL above

  -- 2. uncomment the two lines below and load the spec

  -- 3. run the SPWrap Code Generator

  -- 4. comment the two lines below out again and load the spec

  -- make sure these lines are commented out before generating a revision for this spec!

  --declare @SPWRAP table([PACKAGEID] uniqueidentifier, [PACKAGENAME] nvarchar(100), [NETCOMMUNITYDATASOURCEID] integer, [NETCOMMUNITYTEMPLATEID] integer, [NETCOMMUNITYEMAILID] integer, [STATUSCODE] tinyint, [STATUSDATE] datetime, [STATUSMESSAGE] nvarchar(255), [EXPORTDEFINITIONID] uniqueidentifier, [EXPORTDEFINITION] nvarchar(255), [STATUSDETAIL] nvarchar(max));

  --select * from @SPWRAP;


  return 0;