USP_EVENTJOBOCCURRENCES_COPY

CREATE procedure dbo.USP_EVENTJOBOCCURRENCES_COPY
(
    @SOURCEEVENTID uniqueidentifier,
    @DESTINATIONEVENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
with execute as caller
as

    set nocount on;

    -- Cannot copy if the source event does not exist

    if not exists (select ID from dbo.EVENT where ID = @SOURCEEVENTID)
        raiserror('The source event specified does not exist.',13,1);

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    declare @SOURCEEVENTSTARTDATE datetime;
    declare @EVENTDATEDIFFINDAYS int;
    declare @DESTINATIONEVENTBASECURRENCYID uniqueidentifier;

    select 
        @SOURCEEVENTSTARTDATE = STARTDATE,
        @EVENTDATEDIFFINDAYS = datediff(day, STARTDATE,    (select STARTDATE from dbo.EVENT where ID = @DESTINATIONEVENTID))
    from dbo.EVENT 
    where ID = @SOURCEEVENTID;

    select 
        @DESTINATIONEVENTBASECURRENCYID = BASECURRENCYID
    from 
        dbo.EVENT 
    where 
        ID = @DESTINATIONEVENTID;

    insert into dbo.JOBOCCURRENCE
        (
            ID,
            JOBID,
            EVENTID,
            DESCRIPTION,
            DEPARTMENTCODEID,
            LOCATIONCODEID,
            TYPECODE,
            STARTDATE,
            ENDDATE,
            STARTMONTHDAY,
            ENDMONTHDAY,
            STARTTIME,
            ENDTIME,
            DAYOFWEEKCODE,
            VOLUNTEERSNEEDED,
            COMMENTS,
            ISACTIVE,
            SITEID,
            ADDEDBYID,
            DATEADDED,
            CHANGEDBYID,
            DATECHANGED,
            RECURRENCESTARTDATE,
            RECURRENCEENDDATE
        )
        select
            newID(),
            JOBID,
            @DESTINATIONEVENTID,
            DESCRIPTION,
            DEPARTMENTCODEID,
            LOCATIONCODEID,
            TYPECODE,
            dateadd(day, @EVENTDATEDIFFINDAYS, STARTDATE),
            dateadd(day, @EVENTDATEDIFFINDAYS, ENDDATE),            

            case TYPECODE
                when 1 then    dbo.UFN_MONTHDAY_ADDDAYS(STARTMONTHDAY, Year(@SOURCEEVENTSTARTDATE), @EVENTDATEDIFFINDAYS)
                else '0000'
            end,

            case TYPECODE
                when 1 then    dbo.UFN_MONTHDAY_ADDDAYS(ENDMONTHDAY, Year(@SOURCEEVENTSTARTDATE), @EVENTDATEDIFFINDAYS)        -- recurring

                else '0000'
            end,

            STARTTIME,
            ENDTIME,
            DAYOFWEEKCODE,
            VOLUNTEERSNEEDED,
            COMMENTS,
            ISACTIVE,
            SITEID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CHANGEAGENTID,
            @CURRENTDATE,
            RECURRENCESTARTDATE,
            RECURRENCEENDDATE

        from 
            dbo.JOBOCCURRENCE
        where 
            EVENTID = @SOURCEEVENTID
            and ISACTIVE = 1
            and JOBOCCURRENCE.BASECURRENCYID = @DESTINATIONEVENTBASECURRENCYID;