USP_DATAFORMTEMPLATE_EDIT_DIRECTMARKETINGEFFORTBATCHROW

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_DIRECTMARKETINGEFFORTBATCHROW]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEQUENCE int,
  @SEGMENTATIONID uniqueidentifier,
  @SEGMENTATIONCODEVALUEID uniqueidentifier,
  @SEGMENTATIONCODE nvarchar(10),
  @SOURCECODE nvarchar(50),
  @RECORDSOURCEID uniqueidentifier,
  @LISTID uniqueidentifier,
  @LISTNAME nvarchar(100),
  @LISTDESCRIPTION nvarchar(255),
  @LISTCODEVALUEID uniqueidentifier,
  @LISTCODE nvarchar(10),
  @LISTCATEGORYCODEID uniqueidentifier,
  @LISTVENDORID uniqueidentifier,
  @SEGMENTID uniqueidentifier,
  @SEGMENTNAME nvarchar(100),
  @SEGMENTDESCRIPTION nvarchar(255),
  @SEGMENTCODEVALUEID uniqueidentifier,
  @SEGMENTCODE nvarchar(10),
  @SEGMENTCATEGORYCODEID uniqueidentifier,
  @SEGMENTTYPECODE tinyint,
  @SEGMENTEXPECTEDGIFTAMOUNT money,
  @SEGMENTEXPECTEDRESPONSERATE decimal(5,2),
  @SEGMENTRENTALQUANTITY int,
  @SEGMENTEXCHANGEQUANTITY int,
  @SEGMENTRENTALCOST money,
  @SEGMENTRENTALCOSTBASISCODE tinyint,
  @SEGMENTEXCHANGECOST money,
  @SEGMENTEXCHANGECOSTBASISCODE tinyint,
  @SEGMENTUSAGECODE tinyint,
  @TESTSEGMENTID uniqueidentifier,
  @TESTSEGMENTNAME nvarchar(100),
  @TESTSEGMENTDESCRIPTION nvarchar(255),
  @TESTSEGMENTCODEVALUEID uniqueidentifier,
  @TESTSEGMENTCODE nvarchar(10),
  @TESTSEGMENTEXPECTEDGIFTAMOUNT money,
  @TESTSEGMENTEXPECTEDRESPONSERATE decimal(5,2),
  @TESTSEGMENTQUANTITY int,
  @PACKAGEID uniqueidentifier,
  @PACKAGENAME nvarchar(100),
  @PACKAGEDESCRIPTION nvarchar(255),
  @PACKAGECODEVALUEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGECHANNELCODEVALUEID uniqueidentifier,
  @PACKAGECHANNELCODE nvarchar(10),
  @PACKAGECATEGORYCODEID uniqueidentifier,
  @PACKAGETYPECODE tinyint,
  @PACKAGECOST money,
  @PACKAGECOSTDISTRIBUTIONMETHODCODE tinyint
)
as
  set nocount on;

  declare @BATCHID uniqueidentifier;
  declare @BATCHSEGMENTID uniqueidentifier;
  declare @BATCHPACKAGEID uniqueidentifier;
  declare @BATCHLISTID uniqueidentifier;
  declare @BATCHTESTSEGMENTID uniqueidentifier;
  declare @OLDBATCHSEGMENTID uniqueidentifier;
  declare @OLDBATCHPACKAGEID uniqueidentifier;
  declare @OLDBATCHLISTID uniqueidentifier;
  declare @OLDBATCHTESTSEGMENTID uniqueidentifier;
  declare @SAVELISTRECORD bit = 1;
  declare @SAVETESTSEGMENTRECORD bit = 1;
  declare @CURRENTDATE datetime;

  begin try
    select
      @BATCHID = [BATCHID],
      @OLDBATCHSEGMENTID = [BATCHDIRECTMARKETINGEFFORTSEGMENTID],
      @OLDBATCHPACKAGEID = [BATCHDIRECTMARKETINGEFFORTPACKAGEID],
      @OLDBATCHLISTID = [BATCHDIRECTMARKETINGEFFORTLISTID],
      @OLDBATCHTESTSEGMENTID = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    where [ID] = @ID;


    --Since the ID fields play double duty in the UI, we need to make sure we reset the fields correctly if they are new records...

    if @OLDBATCHLISTID is not null and @LISTID = @OLDBATCHLISTID
      set @LISTID = null;
    if @OLDBATCHSEGMENTID is not null and @SEGMENTID = @OLDBATCHSEGMENTID
      set @SEGMENTID = null;
    if @OLDBATCHTESTSEGMENTID is not null and @TESTSEGMENTID = @OLDBATCHTESTSEGMENTID
      set @TESTSEGMENTID = null;
    if @OLDBATCHPACKAGEID is not null and @PACKAGEID = @OLDBATCHPACKAGEID
      set @PACKAGEID = null;



    /***************************************/
    /* Validate the data before we save it */
    /***************************************/
    exec dbo.[USP_DIRECTMARKETINGEFFORTBATCH_VALIDATEROW]
      @BATCHID = @BATCHID,
      @VALIDATEFORCOMMIT = 0,
      @BATCHSEGMENTID = @BATCHSEGMENTID output,
      @BATCHPACKAGEID = @BATCHPACKAGEID output,
      @BATCHLISTID = @BATCHLISTID output,
      @BATCHTESTSEGMENTID = @BATCHTESTSEGMENTID output,
      @SAVELISTRECORD = @SAVELISTRECORD output,
      @SAVETESTSEGMENTRECORD = @SAVETESTSEGMENTRECORD output,
      @SEGMENTATIONID = @SEGMENTATIONID output,
      @SEGMENTATIONCODEVALUEID = @SEGMENTATIONCODEVALUEID output,
      @SEGMENTATIONCODE = @SEGMENTATIONCODE output,
      @SOURCECODE = @SOURCECODE output,
      @RECORDSOURCEID = @RECORDSOURCEID output,
      @LISTID = @LISTID output,
      @LISTNAME = @LISTNAME output,
      @LISTDESCRIPTION = @LISTDESCRIPTION output,
      @LISTCODEVALUEID = @LISTCODEVALUEID output,
      @LISTCODE = @LISTCODE output,
      @LISTCATEGORYCODEID = @LISTCATEGORYCODEID output,
      @LISTVENDORID = @LISTVENDORID output,
      @SEGMENTID = @SEGMENTID output,
      @SEGMENTNAME = @SEGMENTNAME output,
      @SEGMENTDESCRIPTION = @SEGMENTDESCRIPTION output,
      @SEGMENTCODEVALUEID = @SEGMENTCODEVALUEID output,
      @SEGMENTCODE = @SEGMENTCODE output,
      @SEGMENTCATEGORYCODEID = @SEGMENTCATEGORYCODEID output,
      @SEGMENTTYPECODE = @SEGMENTTYPECODE output,
      @SEGMENTEXPECTEDGIFTAMOUNT = @SEGMENTEXPECTEDGIFTAMOUNT output,
      @SEGMENTEXPECTEDRESPONSERATE = @SEGMENTEXPECTEDRESPONSERATE output,
      @SEGMENTRENTALQUANTITY = @SEGMENTRENTALQUANTITY output,
      @SEGMENTEXCHANGEQUANTITY = @SEGMENTEXCHANGEQUANTITY output,
      @SEGMENTRENTALCOST = @SEGMENTRENTALCOST output,
      @SEGMENTRENTALCOSTBASISCODE = @SEGMENTRENTALCOSTBASISCODE output,
      @SEGMENTEXCHANGECOST = @SEGMENTEXCHANGECOST output,
      @SEGMENTEXCHANGECOSTBASISCODE = @SEGMENTEXCHANGECOSTBASISCODE output,
      @SEGMENTUSAGECODE = @SEGMENTUSAGECODE output,
      @TESTSEGMENTID = @TESTSEGMENTID output,
      @TESTSEGMENTNAME = @TESTSEGMENTNAME output,
      @TESTSEGMENTDESCRIPTION = @TESTSEGMENTDESCRIPTION output,
      @TESTSEGMENTCODEVALUEID = @TESTSEGMENTCODEVALUEID output,
      @TESTSEGMENTCODE = @TESTSEGMENTCODE output,
      @TESTSEGMENTEXPECTEDGIFTAMOUNT = @TESTSEGMENTEXPECTEDGIFTAMOUNT output,
      @TESTSEGMENTEXPECTEDRESPONSERATE = @TESTSEGMENTEXPECTEDRESPONSERATE output,
      @TESTSEGMENTQUANTITY = @TESTSEGMENTQUANTITY output,
      @PACKAGEID = @PACKAGEID output,
      @PACKAGENAME = @PACKAGENAME output,
      @PACKAGEDESCRIPTION = @PACKAGEDESCRIPTION output,
      @PACKAGECODEVALUEID = @PACKAGECODEVALUEID output,
      @PACKAGECODE = @PACKAGECODE output,
      @PACKAGECHANNELCODEVALUEID = @PACKAGECHANNELCODEVALUEID output,
      @PACKAGECHANNELCODE = @PACKAGECHANNELCODE output,
      @PACKAGECATEGORYCODEID = @PACKAGECATEGORYCODEID output,
      @PACKAGETYPECODE = @PACKAGETYPECODE output,
      @PACKAGECOST = @PACKAGECOST output,
      @PACKAGECOSTDISTRIBUTIONMETHODCODE = @PACKAGECOSTDISTRIBUTIONMETHODCODE output;







    /*******************************************************/
    /* Update the batch row data in the appropriate tables */
    /*******************************************************/
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();


    --Update the segment record (only if we need to), or insert a new segment record (only if we need to)...

    if @OLDBATCHSEGMENTID is not null and (@BATCHSEGMENTID is null or @BATCHSEGMENTID <> @OLDBATCHSEGMENTID)
      begin
        --The user either deleted the record or replaced it with a different record that exists in the batch.

        --So, we need to delete the old row, but only if it is not being used by another row in the batch.

        if not exists(select * from dbo.[BATCHDIRECTMARKETINGEFFORT] where [BATCHID] = @BATCHID and [BATCHDIRECTMARKETINGEFFORTSEGMENTID] = @OLDBATCHSEGMENTID and [ID] <> @ID)
          exec dbo.[USP_BATCHDIRECTMARKETINGEFFORTSEGMENT_DELETEBYID_WITHCHANGEAGENTID] @OLDBATCHSEGMENTID, @CHANGEAGENTID;
      end

    --Update the segment record...

    if @BATCHSEGMENTID is not null
      update dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] set
        [MKTSEGMENTID] = @SEGMENTID,
        [NAME] = @SEGMENTNAME,
        [DESCRIPTION] = @SEGMENTDESCRIPTION,
        [CODE] = @SEGMENTCODE,
        [CODEPARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
        [MKTSEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID,
        [SEGMENTTYPECODE] = @SEGMENTTYPECODE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @BATCHSEGMENTID;
    else
      begin
        --Insert a new segment record for the batch...

        set @BATCHSEGMENTID = newid();
        insert into dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] (
          [ID],
          [MKTSEGMENTID],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [CODEPARTDEFINITIONVALUESID],
          [MKTSEGMENTCATEGORYCODEID],
          [SEGMENTTYPECODE],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @BATCHSEGMENTID,
          @SEGMENTID,
          @SEGMENTNAME,
          @SEGMENTDESCRIPTION,
          @SEGMENTCODE,
          @SEGMENTCODEVALUEID,
          @SEGMENTCATEGORYCODEID,
          @SEGMENTTYPECODE,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end



    --Update the package record (only if we need to), or insert a new package record (only if we need to)...

    if @OLDBATCHPACKAGEID is not null and (@BATCHPACKAGEID is null or @BATCHpackageID <> @OLDBATCHPACKAGEID)
      begin
        --The user either deleted the record or replaced it with a different record that exists in the batch.

        --So, we need to delete the old row, but only if it is not being used by another row in the batch.

        if not exists(select * from dbo.[BATCHDIRECTMARKETINGEFFORT] where [BATCHID] = @BATCHID and [BATCHDIRECTMARKETINGEFFORTPACKAGEID] = @OLDBATCHPACKAGEID and [ID] <> @ID)
          exec dbo.[USP_BATCHDIRECTMARKETINGEFFORTPACKAGE_DELETEBYID_WITHCHANGEAGENTID] @OLDBATCHPACKAGEID, @CHANGEAGENTID;
      end

    if @BATCHPACKAGEID is not null
      --Update the package record...

      update dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] set
        [MKTPACKAGEID] = @PACKAGEID,
        [NAME] = @PACKAGENAME,
        [DESCRIPTION] = @PACKAGEDESCRIPTION,
        [CODE] = @PACKAGECODE,
        [CODEPARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
        [CHANNELCODE] = @PACKAGECHANNELCODE,
        [CHANNELCODEPARTDEFINITIONVALUESID] = @PACKAGECHANNELCODEVALUEID,
        [MKTPACKAGECATEGORYCODEID] = @PACKAGECATEGORYCODEID,
        [PACKAGETYPECODE] = @PACKAGETYPECODE,
        [COST] = @PACKAGECOST,
        [COSTDISTRIBUTIONMETHODCODE] = @PACKAGECOSTDISTRIBUTIONMETHODCODE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @BATCHPACKAGEID;
    else
      begin
        --Insert a new package record for the batch...

        set @BATCHPACKAGEID = newid();
        insert into dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] (
          [ID],
          [MKTPACKAGEID],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [CODEPARTDEFINITIONVALUESID],
          [CHANNELCODE],
          [CHANNELCODEPARTDEFINITIONVALUESID],
          [MKTPACKAGECATEGORYCODEID],
          [PACKAGETYPECODE],
          [COST],
          [COSTDISTRIBUTIONMETHODCODE],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @BATCHPACKAGEID,
          @PACKAGEID,
          @PACKAGENAME,
          @PACKAGEDESCRIPTION,
          @PACKAGECODE,
          @PACKAGECODEVALUEID,
          @PACKAGECHANNELCODE,
          @PACKAGECHANNELCODEVALUEID,
          @PACKAGECATEGORYCODEID,
          @PACKAGETYPECODE,
          @PACKAGECOST,
          @PACKAGECOSTDISTRIBUTIONMETHODCODE,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end



    --Update the list record (only if we need to), or insert a new list record (only if we need to)...

    if @OLDBATCHLISTID is not null and (@BATCHLISTID is null or @BATCHLISTID <> @OLDBATCHLISTID)
      begin
        --The user either deleted the record or replaced it with a different record that exists in the batch.

        --So, we need to delete the old row, but only if it is not being used by another row in the batch.

        if not exists(select * from dbo.[BATCHDIRECTMARKETINGEFFORT] where [BATCHID] = @BATCHID and [BATCHDIRECTMARKETINGEFFORTLISTID] = @OLDBATCHLISTID and [ID] <> @ID)
          exec dbo.[USP_BATCHDIRECTMARKETINGEFFORTLIST_DELETEBYID_WITHCHANGEAGENTID] @OLDBATCHLISTID, @CHANGEAGENTID;
      end

    if @SAVELISTRECORD = 1
      begin
        if @BATCHLISTID is not null
          --Update the list record...

          update dbo.[BATCHDIRECTMARKETINGEFFORTLIST] set
            [MKTLISTID] = @LISTID,
            [NAME] = @LISTNAME,
            [DESCRIPTION] = @LISTDESCRIPTION,
            [CODE] = @LISTCODE,
            [CODEPARTDEFINITIONVALUESID] = @LISTCODEVALUEID,
            [MKTLISTCATEGORYCODEID] = @LISTCATEGORYCODEID,
            [VENDORID] = @LISTVENDORID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @BATCHLISTID;
        else if @LISTID is not null or @LISTNAME <> ''
          begin
            --Insert a new list record for the batch...

            set @BATCHLISTID = newid();
            insert into dbo.[BATCHDIRECTMARKETINGEFFORTLIST] (
              [ID],
              [MKTLISTID],
              [NAME],
              [DESCRIPTION],
              [CODE],
              [CODEPARTDEFINITIONVALUESID],
              [MKTLISTCATEGORYCODEID],
              [VENDORID],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            ) values (
              @BATCHLISTID,
              @LISTID,
              @LISTNAME,
              @LISTDESCRIPTION,
              @LISTCODE,
              @LISTCODEVALUEID,
              @LISTCATEGORYCODEID,
              @LISTVENDORID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            );
          end
      end



    --Update the test segment record (only if we need to), or insert a new test segment record (only if we need to)...

    if @OLDBATCHTESTSEGMENTID is not null and (@BATCHTESTSEGMENTID is null or @BATCHTESTSEGMENTID <> @OLDBATCHTESTSEGMENTID)
      begin
        --The user either deleted the record or replaced it with a different record that exists in the batch.

        --So, we need to delete the old row, but only if it is not being used by another row in the batch.

        if not exists(select * from dbo.[BATCHDIRECTMARKETINGEFFORT] where [BATCHID] = @BATCHID and [BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID] = @OLDBATCHTESTSEGMENTID and [ID] <> @ID)
          exec dbo.[USP_BATCHDIRECTMARKETINGEFFORTTESTSEGMENT_DELETEBYID_WITHCHANGEAGENTID] @OLDBATCHTESTSEGMENTID, @CHANGEAGENTID;
      end

    if @SAVETESTSEGMENTRECORD = 1
      begin
        if @BATCHTESTSEGMENTID is not null
          update dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] set
            [MKTSEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID,
            [NAME] = @TESTSEGMENTNAME,
            [DESCRIPTION] = @TESTSEGMENTDESCRIPTION,
            [CODE] = @TESTSEGMENTCODE,
            [CODEPARTDEFINITIONVALUESID] = @TESTSEGMENTCODEVALUEID,
            [EXPECTEDGIFTAMOUNT] = @TESTSEGMENTEXPECTEDGIFTAMOUNT,
            [EXPECTEDRESPONSERATE] = @TESTSEGMENTEXPECTEDRESPONSERATE,
            [QUANTITY] = @TESTSEGMENTQUANTITY,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @BATCHTESTSEGMENTID;
        else if @TESTSEGMENTID is not null or @TESTSEGMENTNAME <> ''
          begin
            --Insert a new test segment record for the batch...

            set @BATCHTESTSEGMENTID = newid();
            insert into dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] (
              [ID],
              [MKTSEGMENTATIONTESTSEGMENTID],
              [NAME],
              [DESCRIPTION],
              [CODE],
              [CODEPARTDEFINITIONVALUESID],
              [EXPECTEDGIFTAMOUNT],
              [EXPECTEDRESPONSERATE],
              [QUANTITY],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            ) values (
              @BATCHTESTSEGMENTID,
              @TESTSEGMENTID,
              @TESTSEGMENTNAME,
              @TESTSEGMENTDESCRIPTION,
              @TESTSEGMENTCODE,
              @TESTSEGMENTCODEVALUEID,
              @TESTSEGMENTEXPECTEDGIFTAMOUNT,
              @TESTSEGMENTEXPECTEDRESPONSERATE,
              @TESTSEGMENTQUANTITY,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            );
          end
      end



    --Update the main batch row...

    update dbo.[BATCHDIRECTMARKETINGEFFORT] set
      [SEQUENCE] = @SEQUENCE,
      [SEGMENTATIONID] = @SEGMENTATIONID,
      [SEGMENTATIONCODE] = @SEGMENTATIONCODE,
      [SEGMENTATIONCODEPARTDEFINITIONVALUESID] = @SEGMENTATIONCODEVALUEID,
      [SOURCECODE] = @SOURCECODE,
      [RECORDSOURCEID] = @RECORDSOURCEID,
      [BATCHDIRECTMARKETINGEFFORTSEGMENTID] = @BATCHSEGMENTID,
      [BATCHDIRECTMARKETINGEFFORTPACKAGEID] = @BATCHPACKAGEID,
      [BATCHDIRECTMARKETINGEFFORTLISTID] = @BATCHLISTID,
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID] = @BATCHTESTSEGMENTID,
      [SEGMENTTESTCODE] = (case when @SAVETESTSEGMENTRECORD = 1 then '' else @TESTSEGMENTCODE end),
      [SEGMENTTESTCODEPARTDEFINITIONVALUESID] = (case when @SAVETESTSEGMENTRECORD = 1 then null else @TESTSEGMENTCODEVALUEID end),
      [SEGMENTEXPECTEDGIFTAMOUNT] = @SEGMENTEXPECTEDGIFTAMOUNT,
      [SEGMENTEXPECTEDRESPONSERATE] = @SEGMENTEXPECTEDRESPONSERATE,
      [SEGMENTRENTALQUANTITY] = @SEGMENTRENTALQUANTITY,
      [SEGMENTEXCHANGEQUANTITY] = @SEGMENTEXCHANGEQUANTITY,
      [SEGMENTRENTALCOST] = @SEGMENTRENTALCOST,
      [SEGMENTRENTALCOSTBASISCODE] = @SEGMENTRENTALCOSTBASISCODE,
      [SEGMENTEXCHANGECOST] = @SEGMENTEXCHANGECOST,
      [SEGMENTEXCHANGECOSTBASISCODE] = @SEGMENTEXCHANGECOSTBASISCODE,
      [SEGMENTUSAGECODE] = @SEGMENTUSAGECODE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;