Setup Photos Marked as Temporary Cause Rock Cleanup to Throw Exception

Please go through all the tasks below

[x] Check this box only after you have successfully completed both the above tasks

Please provide a brief description of the problem. Please do not forget to attach the relevant screenshots from your side.

Sometimes setup files attached to Room Management reservations can get marked for deletion (IsTemporary = true on the BinaryFile table).

This causes Rock Cleanup to throw Exceptions like this:

The DELETE statement conflicted with the REFERENCE constraint "FK__com_bemaservices_RoomManagement_Reservation_SetupPhoto". The conflict occurred in database "rockprod", table "dbo._com_bemaservices_RoomManagement_Reservation", column 'SetupPhotoId'. The statement has been terminated.

I'm not 100% sure, but I think this is may be caused the following scenario: A reservation is created with a room setup photo. That original reservation is later duplicated, retaining the link to the setup photo. Then, the original reservation is deleted and the file gets marked as temporary, but it's still in use with one or more duplicated reservations.

Once this error has started to happen, it will cause Rock Cleanup to throw a warning on the temporary binary file cleanup step each time it runs.

The following SQL workaround can be used to resolve the error for specific file(s). We need to set the IsTemporary flag for the affected file(s) back to false.

This SQL will find any affected reservations and their associated files:

SELECT
      [ReservationId]   = [rmr].[Id]
    , [ReservationName] = [rmr].[Name]
    , [BinaryFileId]    = [bf].[Id]
    , [BinaryFileName]  = [bf].[FileName]
FROM
    [_com_bemaservices_RoomManagement_Reservation] [rmr]
    JOIN [BinaryFile] [bf]
            ON [bf].[Id] = [rmr].[SetupPhotoId]
WHERE
    [bf].[IsTemporary] = 1

This SQL will flip the IsTemporary bit on any affected files so that Rock Cleanup will no longer attempt to remove them (and show you which files have been modified):

BEGIN TRAN [T1]
;
WITH
    [UpdateFileCTE] AS (
        SELECT
            [BinaryFileId] = [bf].[Id]
        FROM
            [_com_bemaservices_RoomManagement_Reservation] [rmr]
            JOIN [BinaryFile] [bf]
                    ON [bf].[Id] = [rmr].[SetupPhotoId]
        WHERE
            [bf].[IsTemporary] = 1
        GROUP BY
            [bf].[Id]
        )

-- SELECT
--     *
UPDATE [bf]
SET
    [IsTemporary] = 0
OUTPUT
    [inserted].[Id]
    , [inserted].[FileName]
    , [inserted].[IsTemporary]
FROM
    [UpdateFileCTE] [ufc]
    JOIN [BinaryFile] [bf]
            ON [bf].[Id] = [ufc].[BinaryFileId]

-- ROLLBACK TRAN [T1]
COMMIT TRAN T1

Expected Behavior

Binary Files for setup photos would not be marked as temporary when they are still associated with one or more reservations.

Actual Behavior

Binary Files associated with reservations can be marked as temporary, causing failures in the temporary binary files cleanup step of Rock Cleanup.

Steps to Reproduce

This may be caused by the following scenario:

A reservation is created with a room setup photo. That original reservation is later duplicated, retaining the link to the setup photo. Then, the original reservation is deleted and the file gets marked as temporary, but it's still in use with one or more duplicated reservations.

Rock Version

1.14.3

Plugin Version

2.3.7.14

Client Culture Setting

en-US

BEMADEV / Room-Management