USE [AMS]
GO
/****** Object:  StoredProcedure [dbo].[Action_Cleaner]    Script Date: 07/27/2017 11:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Action_Cleaner]
AS
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @RC INT
        SET @RC = 0

        SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

        BEGIN TRANSACTION

        CREATE TABLE #WorkflowActionDataProcessing
            (
              OrderItemGuid UNIQUEIDENTIFIER
            )
        SET @RC = @@ERROR

        IF @RC = 0
            BEGIN
                UPDATE dbo.WorkflowActionData
					SET Deleted = 1
                OUTPUT  INSERTED.OrderItemGuid
                        INTO #WorkflowActionDataProcessing ( OrderItemGuid )
                FROM    dbo.WorkflowActionData wad WITH ( READPAST )
                        INNER JOIN dbo.ProductOrderItem POI WITH ( ROWLOCK, XLOCK, READPAST ) ON wad.OrderItemGuid = POI.OrderItemGUID
                        OUTER APPLY dbo.Metadata_GetValueAsString(POI.OrderItemGUID,
                                                              'IngestAssetGuid') IngestAssetGuid
                        LEFT OUTER JOIN dbo.WorkflowDecision ON POI.OrderItemGUID = dbo.WorkflowDecision.NextWorkflowActionID
                        LEFT OUTER JOIN dbo.ProductOrderItem ParentPOI ON ParentPOI.OrderItemGuid = WorkflowDecision.PreviousWorkflowActionID
                        LEFT OUTER JOIN dbo.zstatus ParentPOIStatus ON ( ParentPOI.Status = ParentPOIStatus.StatusID )
                        LEFT OUTER JOIN dbo.zSubStatus ON POI.SubStatusCode = dbo.zSubStatus.SubStatusCode
                        INNER JOIN dbo.Asset A WITH ( READPAST ) ON POI.AssetGUID = A.AssetGUID
                        INNER JOIN dbo.zStatus AStatus ON A.StatusID = AStatus.StatusID
                        LEFT OUTER JOIN dbo.Asset DA ON POI.DeliverableAssetGUID = DA.AssetGUID
                        LEFT OUTER JOIN dbo.zStatus DAStatus ON DA.StatusID = DAStatus.StatusID
                WHERE   POI.IsBasket = 0
						AND wad.Deleted = 0
                        AND A.Deleted = 0
                        AND A.Basket = 0
                        AND A.Cancelled = 0
                        AND ISNULL(ParentPOI.IsBasket, 0) = 0
                        AND ISNULL(ParentPOIStatus.StatusCompleted, 1) = 1 -- parentPOI must be completed
                        AND (
					/* 24 - Transcoding */ ( POI.OrderItemTypeID = 24
                                             AND AStatus.StatusCompleted = 1
                                           )
                              OR (
					/* 1 - Yadn Ingest */ POI.OrderItemTypeID IN (1,30,34,35)
								   AND POI.Status = 1000 -- New
                                   AND A.StatusID IN ( 4 )
                                   AND a.FileExists = 1
                                 )
                              OR (
					/* 1 - Move to adbank */ POI.OrderItemTypeID = 1
                                   AND A.StatusID = 2  -- Awating Type
                                   AND IngestAssetGuid.Value IS NOT NULL
                                   AND EXISTS(SELECT * FROM dbo.Asset WHERE AssetGUID = CAST(IngestAssetGuid.Value AS UNIQUEIDENTIFIER) AND FileID IS NOT NULL)
                                 )
                              OR (
					/* 1 - Yadn Delivery */ POI.OrderItemTypeID = 2
                                   AND POI.Status = 411 -- Awaiting YADN Transfer
                                   AND A.FileID IS NOT NULL
                                 )
                              OR (
					/* 1 - GDN MG Delivery */ POI.OrderItemTypeID = 2
                                   AND POI.Status IN (12, 206, 45)
                                   AND A.FileID IS NOT NULL
                                   AND dbo.IsGdnDelivery(POI.OrderItemGUID) = 1
                                 )
                              OR (
					/* 31, 33 - Delete Asset, Reset and Terminate ODT */ POI.OrderItemTypeID IN (
                                   31, 32, 33 ) -- Delete Asset And Terminate
                                   )
                              OR ( POI.OrderItemTypeID NOT IN ( 24, 1, 2 )
                                   AND (
							/* 26 - RequestUpload */ ( DAStatus.StatusCompleted IS NOT NULL
                                                       AND ( DAStatus.StatusCompleted = 1
                                                             OR POI.OrderItemTypeID = 26
                                                           )
                                                     )
                                         OR ( DAStatus.StatusCompleted IS NULL
                                              AND AStatus.StatusCompleted = 1
                                            )
                                       )
                                 )
                            )

				-- Lock ProductOrderItem prior to locking WorkflowActionDataProcessing to aviod deadlocks
				IF EXISTS(SELECT 1 FROM #WorkflowActionDataProcessing wadp
					INNER JOIN dbo.ProductOrderItem WITH(ROWLOCK, HOLDLOCK, XLOCK) ON wadp.OrderItemGuid = dbo.ProductOrderItem.OrderItemGUID)
				BEGIN

					IF EXISTS(SELECT 1 FROM dbo.WorkflowActionDataProcessing wadp
						WHERE	EXISTS(SELECT 1 FROM #WorkflowActionDataProcessing t WHERE t.OrderItemGuid = wadp.OrderItemGuid ))
					BEGIN
						UPDATE dbo.WorkflowActionDataProcessing
						SET
							Priority = dbo.Action_GetDefaultPriority(t.OrderItemGuid),
							Lock = 0
						FROM dbo.WorkflowActionDataProcessing wadp
						INNER JOIN #WorkflowActionDataProcessing t ON wadp.OrderItemGuid = t.OrderItemGuid
					END

					INSERT  INTO dbo.WorkflowActionDataProcessing
                    ( OrderItemGuid ,
                      Lock ,
                      Priority
                    )
                    SELECT  DISTINCT
			    OrderItemGuid ,
                            0 ,
                            dbo.Action_GetDefaultPriority(OrderItemGuid)
                    FROM    #WorkflowActionDataProcessing t
                    WHERE
						NOT EXISTS(
							SELECT 1 FROM dbo.WorkflowActionDataProcessing wadp
							WHERE wadp.OrderItemGuid = t.OrderItemGuid
						)
                END

                DROP TABLE #WorkflowActionDataProcessing

                SET @RC = @@ERROR
            END

        IF @RC = 0
            BEGIN
                COMMIT
                SET @RC = @@ERROR
            END
    END