USP_DATALIST_DIRECTMARKETINGEFFORTBATCHSEGMENT

CREATE procedure dbo.[USP_DATALIST_DIRECTMARKETINGEFFORTBATCHSEGMENT]
(
  @BATCHID uniqueidentifier,
  @SEGMENTID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on;

  if exists(select * from dbo.[MKTSEGMENT] where [ID] = @SEGMENTID)
    --Grab the data for the segment if it already exists.  This can return multiple rows since a 

    --mailing can contain the same segment more than once with different packages.

    select
      cast(1 as bit) as [EXISTINGSEGMENT],
      [MKTSEGMENT].[NAME],
      [MKTSEGMENT].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENT].[PARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENT].[PARTDEFINITIONVALUESID]) as [CODEREGEX],
      [MKTSEGMENT].[CODE],
      [TESTCODEPARTDEFINITIONVALUES].[ID] as [TESTCODEVALUEID],
      [TESTCODEPARTDEFINITIONVALUES].[DESCRIPTION] as [TESTCODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID]) as [TESTCODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID]) as [TESTCODEREGEX],
      [MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE] as [TESTCODE],
      [MKTSEGMENTCATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [MKTSEGMENT].[SEGMENTTYPECODE],
      [MKTSEGMENT].[SEGMENTTYPE],
      [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
      [MKTSEGMENTATIONSEGMENT].[RESPONSERATE] as [EXPECTEDRESPONSERATE],
      [MKTSEGMENTLIST].[RENTALQUANTITY],
      [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT] else [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] end) as [RENTALCOST],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENTBASISCODE] else [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] end) as [RENTALCOSTBASISCODE],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENTBASIS] else [MKTSEGMENTLIST].[RENTALCOSTBASIS] end) as [RENTALCOSTBASIS],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] else [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] end) as [EXCHANGECOST],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENTBASISCODE] else [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] end) as [EXCHANGECOSTBASISCODE],
      (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENTBASIS] else [MKTSEGMENTLIST].[EXCHANGECOSTBASIS] end) as [EXCHANGECOSTBASIS],
      [MKTSEGMENTATIONSEGMENT].[USAGECODE],
      [MKTSEGMENTATIONSEGMENT].[USAGE],
      [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENT].[SITEID]) as [SITE],
      [MKTSEGMENT].[SITEID]
    from dbo.[MKTSEGMENT]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSEGMENT].[PARTDEFINITIONVALUESID]
    left join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
    left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID] and @SEGMENTATIONID is not null and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [TESTCODEPARTDEFINITIONVALUES] on [TESTCODEPARTDEFINITIONVALUES].[ID] = [MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID]
    where [MKTSEGMENT].[ID] = @SEGMENTID
    order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE];
  else
    --If the segment does not exist yet, then grab the data from the batch table.  This should only return one row.

    select top 1
      cast(0 as bit) as [EXISTINGSEGMENT],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODEPARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODEPARTDEFINITIONVALUESID]) as [CODEREGEX],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE],
      [TESTCODEPARTDEFINITIONVALUES].[ID] as [TESTCODEVALUEID],
      [TESTCODEPARTDEFINITIONVALUES].[DESCRIPTION] as [TESTCODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODEPARTDEFINITIONVALUESID]) as [TESTCODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODEPARTDEFINITIONVALUESID]) as [TESTCODEREGEX],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODE] as [TESTCODE],
      [MKTSEGMENTCATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPE],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXPECTEDGIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXPECTEDRESPONSERATE] as [EXPECTEDRESPONSERATE],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTRENTALQUANTITY] as [RENTALQUANTITY],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXCHANGEQUANTITY] as [EXCHANGEQUANTITY],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTRENTALCOST] as [RENTALCOST],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTRENTALCOSTBASISCODE] as [RENTALCOSTBASISCODE],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTRENTALCOSTBASIS] as [RENTALCOSTBASIS],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXCHANGECOST] as [EXCHANGECOST],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXCHANGECOSTBASISCODE] as [EXCHANGECOSTBASISCODE],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTEXCHANGECOSTBASIS] as [EXCHANGECOSTBASIS],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTUSAGECODE] as [USAGECODE],
      [BATCHDIRECTMARKETINGEFFORT].[SEGMENTUSAGE] as [USAGE],
      isnull([BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGEID], [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID]) as [PACKAGEID],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([BATCHDIRECTMARKETINGEFFORTSEGMENT].[SITEID]) as [SITE],
      [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SITEID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
    left join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODEPARTDEFINITIONVALUESID]
    left join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[MKTSEGMENTCATEGORYCODEID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [TESTCODEPARTDEFINITIONVALUES] on [TESTCODEPARTDEFINITIONVALUES].[ID] = [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODEPARTDEFINITIONVALUESID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = @SEGMENTID;

  return 0;