USE PLUS
GO
DECLARE @IMPACT VARCHAR (2000)
DECLARE @StorageLocation NVARCHAR(3750)
DECLARE @ITable Table (PackageID VARCHAR(36))


-- Critical, Recommended, Informational, Critical - 01, Critical - 05, Software, Virus Removal, Critical - Intl
SET @IMPACT = 'Critical'


INSERT @ITable
SELECT rp.PackageID
FROM ReportImpacts ri 
JOIN Reports r ON r.ReportImpact_ID = ri.ReportImpact_ID
JOIN Report_to_Package rp ON rp.Report_ID = r.Report_ID
JOIN PackageRequests pr ON pr.PackageRequest_PackageID = rp.PackageID
WHERE ri.ReportImpact_Name = @IMPACT

SELECT @StorageLocation = SystemConfig_Value 
FROM SystemConfig
WHERE SystemConfig_Name = 'Storage'
SET @StorageLocation = @StorageLocation +'\00000000-0000-0000-0000-000000000000\'

SELECT 'RMDIR /S /Q "' + @StorageLocation + PackageRequest_PackageID +'"' AS CommandLine
FROM ReportImpacts ri 
JOIN Reports r ON r.ReportImpact_ID = ri.ReportImpact_ID
JOIN Report_to_Package rp ON rp.Report_ID = r.Report_ID
JOIN PackageRequests pr ON pr.PackageRequest_PackageID = rp.PackageID
WHERE ri.ReportImpact_Name = @IMPACT

DELETE UA
FROM UP_ACTIONS UA
JOIN @ITable P ON P.PackageID = UA.PackageID

DELETE UPM
FROM UP_PACKAGEMODULES UPM
JOIN @ITable P ON P.PackageID = UPM.PackageID


DELETE PR
FROM PACKAGEREQUESTS PR
JOIN @ITable P ON P.PackageID = PR.PackageRequest_PackageID

UPDATE UP
SET CACHEDATE = NULL
FROM UP_PACKAGES UP
JOIN @ITable P ON P.PackageID = UP.PackageID