gaepdit / complaint-tracking

Complaint Tracking System
The Unlicense
1 stars 3 forks source link

Administratively close old inactive complaints #298

Closed dougwaldron closed 6 years ago

dougwaldron commented 6 years ago

Requested by Bonnie Pope (NE District), and approved by Derrick Williams & Bert Langley.

NOTE: This only applies to NE District complaints. Other Districts may request similar actions in the future. This is the list that will be cleaned up: Unresolved Complaints By NE District

As you know, Bert Langley has no immediate objection to our asking Doug Waldron to do a mass closure of our ancient unresolved complaints, if we decide to do so. And Doug Waldron has said this can be done.

We would ask to change designated complaints (that appear in the unresolved complaints report) from ‘Under Investigation” and “Approval Requested” status to “Date Complaint Closed: [closure date]”. At the same time, we would ask to have the comment “Administratively Closed” inserted in the Approval comment field. The remaining “Unresolved Complaints by Branch” Report would then consist of the complaints we are actively working. Our District has about 350 old complaints. Not all, but some other Districts’ Reports are in the same condition (300-400 inactive records).

Clarification: this applies to "all CTS records received prior to January 1, 2015." (Bonnie Pope)

Imported from JIRA: [CTS-340] Administratively close old inactive complaints (original by Douglas Waldron)

dougwaldron commented 6 years ago

This should do it:

DECLARE
@userId NVARCHAR(450),
@comment NVARCHAR(25);

SELECT @userId = Id
FROM AspNetUsers
WHERE Email = 'epd_it@dnr.ga.gov';

SET @comment = 'Administratively Closed';

INSERT INTO ComplaintTransitions
(Comment,
 ComplaintId,
 CreatedDate,
 DateTransferred,
 TransferredByUserId,
 TransitionType,
 UpdatedDate)
    SELECT
@comment,
Id,
getdate(),
getdate(),
@userId,
4,
getdate()
    FROM Complaints
    WHERE Id IN (NULL);

UPDATE Complaints
SET ComplaintClosed     = 1,
    DateComplaintClosed = getdate(),
    ReviewById  = @userId,
    ReviewComments      = @comment,
    Status      = 3,
    UpdatedDate = getdate()
WHERE Id IN (NULL);

Replace NULL in above code with the list of Complaint ID's to close out.

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

To get the list of Complaint IDs:

SELECT [Id]
FROM [Complaints]
WHERE [ComplaintClosed] = 0
      AND [Deleted] = 0
      AND [CurrentOfficeId] = 'f9058e6a-8e93-47d7-8377-820ececc4467'
      AND [DateReceived] < '2015-01-01'
ORDER BY [Id];

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Fix & simplify:

INSERT INTO ComplaintTransitions
(Id,
 Comment,
 ComplaintId,
 CreatedDate,
 DateTransferred,
 TransferredByUserId,
 TransitionType,
 UpdatedDate)
    SELECT
newid(),
'Administratively Closed',
Id,
getdate(),
getdate(),
'd341ce21-ff86-494c-a3c7-03e9f77d0403',
4,
getdate()
    FROM Complaints
    WHERE Id IN
  (NULL);

UPDATE Complaints
SET ComplaintClosed     = 1,
    DateComplaintClosed = getdate(),
    ReviewById  = 'd341ce21-ff86-494c-a3c7-03e9f77d0403',
    ReviewComments      = 'Administratively Closed',
    Status      = 3,
    UpdatedDate = getdate()
WHERE Id IN
      (NULL);

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Verify IDs:

SELECT Id
FROM AspNetUsers
WHERE Email = 'epd_it@dnr.ga.gov';

SELECT Id
FROM LookupOffices
WHERE Name = 'Northeast Region';

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Action taken: CTS-340.sql

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41