USE [P21]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[nutech_audit_trail_deletes] AS

SET NOCOUNT ON

DECLARE @ProcedureBegin DATETIME
DECLARE @ProcedureEnd DATETIME
DECLARE @ProcessBegin DATETIME
DECLARE @ProcessEnd DATETIME
DECLARE @InfoOut NVARCHAR(4000)
DECLARE @del30 DATETIME
DECLARE @del60 DATETIME
DECLARE @del180 DATETIME
DECLARE @del1Year DATETIME
DECLARE @del2Year DATETIME
DECLARE @del5Year DATETIME

SELECT @ProcedureBegin = GETDATE()

----------------------------------------------------------------------------------------------------------------------
-- Change the Recovery Model to Bulk_Logged
----------------------------------------------------------------------------------------------------------------------

SELECT @ProcessBegin = GETDATE()

IF DATABASEPROPERTYEX('P21', 'Recovery') = 'FULL'
	ALTER DATABASE P21
		SET RECOVERY BULK_LOGGED

SELECT @ProcessEnd = GETDATE()
   SELECT @InfoOut = N'Total Time to change recovery mode = '
            + CONVERT(NVARCHAR, DATEDIFF(ms, @ProcessBegin, @ProcessEnd)) + N' ms'
   PRINT @InfoOut

----------------------------------------------------------------------------------------------------------------------
-- Perform the Batch Operation
----------------------------------------------------------------------------------------------------------------------

SET @del30 = DATEADD(DD,-30,GETDATE())
SET @del60 = DATEADD(DD,-60,GETDATE())
SET @del180 = DATEADD(DD,-180,GETDATE())
SET @del1Year = DATEADD(YY,-1,GETDATE())
SET @del2Year = DATEADD(YY,-2,GETDATE())
SET @del5Year = DATEADD(YY,-5,GETDATE())

SELECT @InfoOut = N'Later than date for this run is: ' + CAST(@del180 AS NVARCHAR)
PRINT @InfoOut


SELECT @ProcessBegin = GETDATE()

DELETE audit_trail
FROM audit_trail
WHERE created_by = 'MIDSTATESBOLT\Administrator'
  and (audit_trail.date_created < @del60)
  and audit_trail.source_area_cd in (1314, 1315, 1319)

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1314, 1315, 1319 rows were deleted from Administrator!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1314, 1315, 1319 rows were deleted from Administrator records deleted'

SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to process OE Delete = '
         + CONVERT(NVARCHAR, DATEDIFF(hh, @ProcessBegin, @ProcessEnd)) + N' hours'
PRINT @InfoOut

SELECT @ProcessBegin = GETDATE()
--
-- oe_hdr
--
DELETE audit_trail
FROM audit_trail
INNER JOIN oe_hdr on  ( oe_hdr.order_no = audit_trail.key1_value )  AND ( oe_hdr.completed = 'Y' )
WHERE (audit_trail.source_area_cd = 1319)
AND (audit_trail.date_created < @del2Year)


IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'

SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to process OE Delete = '
         + CONVERT(NVARCHAR, DATEDIFF(hh, @ProcessBegin, @ProcessEnd)) + N' hours'
PRINT @InfoOut

SELECT @ProcessBegin = getdate()
--
-- work_order
--
  DELETE
  FROM  audit_trail
  WHERE audit_trail.source_area_cd = 1250
  AND audit_trail.date_created < @del180

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1250 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1250 records deleted'


SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to process WO delete = '
            + CONVERT(NVARCHAR, DATEDIFF(hh, @ProcessBegin, @ProcessEnd)) + N' hours'
PRINT @InfoOut


SELECT @ProcessBegin = getdate()
--
-- work_order
--
  DELETE
  FROM  audit_trail
  WHERE audit_trail.source_area_cd = 1315
  AND audit_trail.date_created < @del180

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1315 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1315 records deleted'


SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to process WO delete = '
            + CONVERT(NVARCHAR, DATEDIFF(hh, @ProcessBegin, @ProcessEnd)) + N' hours'
PRINT @InfoOut


SELECT @ProcessBegin = getdate()
--
-- po_hdr
--
DELETE
FROM          audit_trail
WHERE         audit_trail.source_area_cd = 1327
AND           audit_trail.date_created < @del180    --180 days

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1327 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1327 records deleted'

-- serial number records this audit trail was setup to help troubleshoot
--
-- po_hdr
--
DELETE
FROM          audit_trail
WHERE         audit_trail.source_area_cd = 1237

 IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1237 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1237 records deleted'

--
-- inv_mast
--
DELETE
FROM          audit_trail
WHERE         audit_trail.source_area_cd = 1314
AND           audit_trail.date_created < @del2Year

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1314 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1314 records deleted'

-- jobsite records > 365 days old -- replaced by deleting all over 365
--
-- job_site
--
DELETE
FROM          audit_trail
WHERE         audit_trail.source_area_cd = 1544
AND           audit_trail.date_created < @del1Year

 IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1544 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1544 records deleted'

--
-- oe_hdr - regardless of whether they are closed or open
--
DELETE
FROM          audit_trail
WHERE         audit_trail.source_area_cd = 1319
AND           audit_trail.date_created < @del1Year     --365 days

 IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'

-----------------------------------------------
--Changes added as part of ITSR 67517
-----------------------------------------------

--
-- oe_hdr - where column changed = 'expedite_date'
--
DELETE	audit_trail
FROM	audit_trail
WHERE	audit_trail.source_area_cd = 1319
		AND column_changed = 'expedite_date'

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'

--
-- oe_hdr - records that show an order was opened or closed
--
DELETE	audit_trail
FROM	audit_trail
WHERE	audit_trail.source_area_cd = 1319
		AND	(old_value = 'T'
			AND new_value = 'N')
		OR	(old_value = 'N'
			AND new_value = 'T')

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'

-- order entry records that show an item was added. You can get this info from the date_created field
DELETE	audit_trail
FROM	audit_trail
WHERE	audit_trail.source_area_cd = 1319
		AND old_value = '[added]'
		AND column_changed <> 'inv_mast_uid'

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'

-- order entry records for qty_allocated and qty_on_pick_tickets for items that are complete.
DELETE	audit_trail
FROM	audit_trail
		INNER JOIN oe_line ON  ( oe_line.order_no = audit_trail.key1_value )  AND (oe_line.line_no = audit_trail.line_no) 
WHERE	audit_trail.source_area_cd = 1319
		AND (column_changed = 'qty_allocated'
			OR column_changed = 'qty_on_pick_tickets')
		AND oe_line.complete = 'Y' 

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1319 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1319 records deleted'
     
-----------------------------------------------
-- Changes added as part of ITSR 102709
-----------------------------------------------    
-- message entry records. 
DELETE	audit_trail
FROM	audit_trail
WHERE	audit_trail.source_area_cd = 2782
		AND audit_trail.date_created < @del60

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 2782 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 2782 records deleted'    
     
--
-- work order - entries with no info
--
DELETE	audit_trail
FROM	audit_trail
WHERE	source_area_cd = 1250
		AND old_value = '0.000000000'
		AND new_value IS NULL
		AND audit_trail.date_created < @del60

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1250 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1250 records deleted'   
     
--
-- inv_mast - price 8 fields
--
DELETE	audit_trail
FROM	audit_trail
WHERE	source_area_cd = 1314
		AND column_changed = 'price8'
		AND audit_trail.date_created < @del180

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd 1314 rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type 1314 records deleted'   
     
-- All records > 5 years old

DELETE
FROM	audit_trail
WHERE	audit_trail.date_created < @del5Year   -- 5 years

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No source_area_cd all areas rows were deleted!'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type all areas records deleted'


-- All records > 30 days old with 0 QTY ordered

DELETE
FROM	job_price_batch_line
WHERE	ordered_qty = 0
		AND date_created < @del60

IF @@ROWCOUNT = 0
   BEGIN
     PRINT 'Warning: No job_price_batch_line with QTY = 0 deleted'
   END
   ELSE
     PRINT CONVERT(NVARCHAR, @@ROWCOUNT) + N' type all areas records deleted'

-- stored procedure to cleanup the gpor_vss and gpor_run tables

EXEC p21_gpor_cleanup 5

SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to process added deletes = '
            + CONVERT(NVARCHAR, DATEDIFF(mi, @ProcessBegin, @ProcessEnd)) + N' minutes'
PRINT @InfoOut



----------------------------------------------------------------------------------------------------------------------
-- 8) Change the Recovery Model back to Full
----------------------------------------------------------------------------------------------------------------------
SELECT @ProcessBegin = getdate()

IF DATABASEPROPERTYEX('P21', 'Recovery') = 'Bulk_Logged'
	ALTER DATABASE P21
		SET RECOVERY FULL, MULTI_USER

SELECT @ProcessEnd = GETDATE()
SELECT @InfoOut = N'Total Time to reset recovery mode = '
            + CONVERT(NVARCHAR, DATEDIFF(ms, @ProcessBegin, @ProcessEnd)) + N' ms'
PRINT @InfoOut


GO


