USP_EXCHANGECALENDARITEMBATCH_VALIDATEEXPECTEDTIMERANGE

create procedure dbo.USP_EXCHANGECALENDARITEMBATCH_VALIDATEEXPECTEDTIMERANGE
(
    @RECORDID uniqueidentifier,
    @RECORDTYPECODE integer,
    @EXPECTEDDATEUTC datetime,
    @EXPECTEDENDDATEUTC datetime,
    @ISALLDAYEVENT bit,
    @TIMEZONEENTRYID uniqueidentifier = null output,
    @EXPECTEDDATEINRECORDTIMEZONE datetime = null output,
    @EXPECTEDENDDATEINRECORDTIMEZONE datetime = null output,
    @EXPECTEDTIMERANGESPANSMULTIPLEDAYS bit = null output,
    @EXPECTEDTIMERANGEISVALID bit = null output
)
as
    declare @STARTDATENOTIME date;
    declare @ENDDATENOTIME date;

    if @ISALLDAYEVENT = 1
    begin
        set @TIMEZONEENTRYID = null;

        --Clear the time because all day events come in with UTC times that are equivalent

        -- to the midnight-to-midnight time range in the Outlook user's time zone. E.g. Mountain

        -- time all day events come in as 5:00 AM to 5:00 AM. Clearing the time here matters

        -- if we need to generate an error message based on this output.

        set @EXPECTEDDATEINRECORDTIMEZONE = cast(@EXPECTEDDATEUTC as date);
        set @EXPECTEDENDDATEINRECORDTIMEZONE = cast(@EXPECTEDENDDATEUTC as date);

        set @STARTDATENOTIME = @EXPECTEDDATEUTC;
        set @ENDDATENOTIME = @EXPECTEDENDDATEUTC;

        set @EXPECTEDTIMERANGESPANSMULTIPLEDAYS = case when (@STARTDATENOTIME <> dateadd(day, -1, @ENDDATENOTIME)) then 1 else 0 end;
        set @EXPECTEDTIMERANGEISVALID = 1;

    end
    else
    begin
        if @RECORDTYPECODE < 2
            select
                @TIMEZONEENTRYID = INTERACTION.TIMEZONEENTRYID
            from
                dbo.INTERACTION
            where
                INTERACTION.ID = @RECORDID;

        else if @RECORDTYPECODE = 2
            select
                @TIMEZONEENTRYID = STEWARDSHIPPLANSTEP.TIMEZONEENTRYID
            from
                dbo.STEWARDSHIPPLANSTEP
            where
                STEWARDSHIPPLANSTEP.ID = @RECORDID;

        -- When going from an all-day event to an event with a start and end time, use the system default time zone.

        if @TIMEZONEENTRYID is null
            set @TIMEZONEENTRYID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();

        set @EXPECTEDDATEINRECORDTIMEZONE = dbo.UFN_EXCHANGEINTEGRATION_CONVERTDATETOSYSTEMTIMEZONE(@TIMEZONEENTRYID, @EXPECTEDDATEUTC);
        set @EXPECTEDENDDATEINRECORDTIMEZONE = dbo.UFN_EXCHANGEINTEGRATION_CONVERTDATETOSYSTEMTIMEZONE(@TIMEZONEENTRYID, @EXPECTEDENDDATEUTC);

        set @STARTDATENOTIME = @EXPECTEDDATEINRECORDTIMEZONE;
        set @ENDDATENOTIME = @EXPECTEDENDDATEINRECORDTIMEZONE;

        set @EXPECTEDTIMERANGESPANSMULTIPLEDAYS = case when (@STARTDATENOTIME <> @ENDDATENOTIME) then 1 else 0 end;
        set @EXPECTEDTIMERANGEISVALID = case when (@EXPECTEDDATEINRECORDTIMEZONE > @EXPECTEDENDDATEINRECORDTIMEZONE) then 0 else 1 end;
    end