USP_EXCHANGECALENDARITEMBATCH_ADDBATCHROW_2

CREATE procedure [dbo].[USP_EXCHANGECALENDARITEMBATCH_ADDBATCHROW_2]
(
    @BATCHID uniqueidentifier = null,
    @RECORDID uniqueidentifier = null,
    @RECORDTYPECODE integer = null,
    @CONSTITUENTID uniqueidentifier = null,
    @OWNERID uniqueidentifier = null,
    @EXPECTEDDATE datetime = null,
    @OBJECTIVE nvarchar(256) = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @EXPECTEDENDDATE datetime = null,
    @TIMEZONENAME nvarchar(255) = null,
    @ISALLDAYEVENT bit = 0,
    @BATCHSYSTEMMESSAGETEXT as nvarchar(2000) = null,
    @INVALIDEXPECTEDENDDATEVALUE as nvarchar(100) = null
)
as        
    declare @CHANGEDATE datetime;
    set @CHANGEDATE = getdate();

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;            

    declare @ID uniqueidentifier;
    set @ID = newid();

    declare @SEQUENCE int
    set @SEQUENCE = coalesce((select max(SEQUENCE) from dbo.EXCHANGECALENDARITEMBATCH where BATCHID = @BATCHID),0) + 1;

    declare @EXPECTEDSTARTTIME UDT_HOURMINUTE;
    declare @EXPECTEDENDTIME UDT_HOURMINUTE;
    declare @TIMEZONEENTRYID uniqueidentifier;
    declare @STARTDATEOFFSET datetime;
    declare @ENDDATEOFFSET datetime;
    declare @DATENOTIME date;
    declare @EXPECTEDTIMERANGESPANSMULTIPLEDAYS bit;
    declare @EXPECTEDTIMERANGEISVALID bit;

    --TommyVe 2012-03-07 Bug 192095, Bug 192102. Use the time zone from the record rather than the

    -- one from the Exchange item. Exchange always provides UTC but the user wants to see the data

    -- in the original time zone.

    exec dbo.USP_EXCHANGECALENDARITEMBATCH_VALIDATEEXPECTEDTIMERANGE
        @RECORDID,
        @RECORDTYPECODE,
        @EXPECTEDDATE,
        @EXPECTEDENDDATE,
        @ISALLDAYEVENT,
        @TIMEZONEENTRYID output,
        @STARTDATEOFFSET output,
        @ENDDATEOFFSET output,
        @EXPECTEDTIMERANGESPANSMULTIPLEDAYS output,
        @EXPECTEDTIMERANGEISVALID output;

    set @DATENOTIME = @STARTDATEOFFSET;

    if @ISALLDAYEVENT = 1
    begin
        set @EXPECTEDSTARTTIME = N'';
        set @EXPECTEDENDTIME = N'';
        set @TIMEZONEENTRYID = null;
    end
    else
    begin
        set @EXPECTEDSTARTTIME = coalesce(dbo.UFN_EXCHANGEINTEGRATION_GETTIMEFROMDATE(@STARTDATEOFFSET), N'');
        set @EXPECTEDENDTIME = coalesce(dbo.UFN_EXCHANGEINTEGRATION_GETTIMEFROMDATE(@ENDDATEOFFSET), N'');
    end

  if @RECORDTYPECODE < 2
    begin
      update 
        dbo.EXCHANGECALENDARITEMBATCH
      set
        PROCESS = 0
      where
        EXCHANGECALENDARITEMBATCH.INTERACTIONID = @RECORDID and
        EXCHANGECALENDARITEMBATCH.OWNERID = @OWNERID and
        EXCHANGECALENDARITEMBATCH.BATCHID = @BATCHID;

      insert into dbo.EXCHANGECALENDARITEMBATCH
        (ID, BATCHID, RECORDTYPECODE, INTERACTIONID, STEWARDSHIPSTEPID, CONSTITUENTID, OWNERID, SEQUENCE, EXPECTEDDATE, OBJECTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, EXPECTEDSTARTTIME, EXPECTEDENDTIME,TIMEZONEENTRYID)
      values
        (@ID, @BATCHID, @RECORDTYPECODE, @RECORDID, null, @CONSTITUENTID, @OWNERID, @SEQUENCE, @DATENOTIME, case when len(@OBJECTIVE) > 0 then replace(@OBJECTIVE, dbo.ufn_constituent_buildname(@CONSTITUENTID) + ': ', '') else '<no subject>' end, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @EXPECTEDSTARTTIME, @EXPECTEDENDTIME, @TIMEZONEENTRYID)

    end
  else if @RECORDTYPECODE = 2
    begin
      update 
        dbo.EXCHANGECALENDARITEMBATCH
      set
        PROCESS = 0
      where
        EXCHANGECALENDARITEMBATCH.STEWARDSHIPSTEPID = @RECORDID and
        EXCHANGECALENDARITEMBATCH.OWNERID = @OWNERID and
        EXCHANGECALENDARITEMBATCH.BATCHID = @BATCHID;

      insert into dbo.EXCHANGECALENDARITEMBATCH
        (ID, BATCHID, RECORDTYPECODE, INTERACTIONID, STEWARDSHIPSTEPID, CONSTITUENTID, OWNERID, SEQUENCE, EXPECTEDDATE, OBJECTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, EXPECTEDSTARTTIME, EXPECTEDENDTIME,TIMEZONEENTRYID)
      values
        (@ID, @BATCHID, @RECORDTYPECODE, null, @RECORDID, @CONSTITUENTID, @OWNERID, @SEQUENCE, @DATENOTIME, case when len(@OBJECTIVE) > 0 then replace(@OBJECTIVE, dbo.ufn_constituent_buildname(@CONSTITUENTID) + ': ', '') else '<no subject>' end, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @EXPECTEDSTARTTIME, @EXPECTEDENDTIME, @TIMEZONEENTRYID)

    end

    if (@BATCHSYSTEMMESSAGETEXT is null) or (len(@BATCHSYSTEMMESSAGETEXT) = 0)
    begin
        --Set the error message here, in case a localized version was not passed in.

        if (@EXPECTEDTIMERANGESPANSMULTIPLEDAYS = 1) and (@EXPECTEDTIMERANGEISVALID = 0)
            set @BATCHSYSTEMMESSAGETEXT = N'The expected start time must be before or the same as the expected end time and both times must be on the same day.';
        else if (@EXPECTEDTIMERANGESPANSMULTIPLEDAYS = 1)
            set @BATCHSYSTEMMESSAGETEXT = N'The expected start time and expected end time must be on the same day.';
        else if (@EXPECTEDTIMERANGEISVALID = 0)
            set @BATCHSYSTEMMESSAGETEXT = N'The expected start time must be before or the same as the expected end time.';
    end

    if len(@BATCHSYSTEMMESSAGETEXT) > 0
    begin
        declare @MESSAGETYPECODE tinyint = 0; --General error

        declare @ORIGINCODE tinyint = 3; --Import


        insert into dbo.EXCHANGECALENDARITEMBATCHBATCHSYSTEMMESSAGES (EXCHANGECALENDARITEMBATCHID, MESSAGETEXT, MESSAGETYPECODE, INVALIDFIELDID, INVALIDFIELDVALUE, ORIGINCODE, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
        values (@ID, @BATCHSYSTEMMESSAGETEXT, @MESSAGETYPECODE, N'EXPECTEDENDTIME', @INVALIDEXPECTEDENDDATEVALUE, @ORIGINCODE, @CHANGEDATE, @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID);

    end