Daily Partner Status Update

Step 1

Recieve Status Update from partner

• This is usually an email from our Partner with a txt file named resultMMDD.txt attached.

Step 2

Save the results file to the same directory with the new and improved parserecord.rb helper.

• Download here parsestatus.rb

Step 3

• In the Command Line, excute the statement below:

ruby parsestatus.rb resultMMDD.txt > google_responseMMDDYYYY.csv

• Remove the last '|' character in the resulting csv file.

Step 4

Import google_responseMMDDYYYY.csv into tempgooglestatus table on Snyder database

Steps using SqlYog below:

• Delete Yesterday's backups

DROP TABLE appeals_MMDDYYYY;

DROP TABLE audits_MMDDYYYY;

• Create Today's backups

CREATE TABLE appeals_MMDDYYYY
 SELECT * from appeals;

CREATE TABLE audits_MMDDYYYY
 SELECT * from audits;

• Run code below to drop old table:

DROP TABLE IF EXISTS tempgooglestatus;

• Run code below to create table:

  CREATE TABLE tempgooglestatus ( 
  `teststatus` INT default NULL,
  `googleurl` text NOT NULL,
  `googlenotes` text NOT NULL) 
  DEFAULT CHARSET=latin1;

• Right mouse click on tempgooglestatus table and select [Export/Import] [Import Data from CSV]

• Select the newly created google_responseMMDDYYYY.csv file and confirm that escape characters match the image below.

• Click the Import Button

• When you see the 3 warnings below, there may be an extra empty row in tempgooglestatus. Delete this row.

Step 5

Execute the Status Update stored procedure by running the code below:

• Be sure to fill in the correct MMDDYYYY values

1. GOOGLE-MMDDYYYY - This is the date of the result file received from our partner: resultMMDD.txt
2. rundate: MMDDYYYY - This is the date of the stored procedure is run

CALL BulkStatusUpdate('GOOGLE-MMDDYYYY','rundate: MMDDYYYY');

Step 6

Run the SQL below to confirm that the Google Statuses were correctly updated in the Appeals and Audit tables.

• If Google statuses and Appeal/Audit statuses do not match, check the notes fields for more info

select r.url,
   gappstat.status as google_appeal_status, gaudstat.status as google_audit_status,
   appstat.status as appeal_status, audstat.status as audit_status, a.notes as appeal_notes, d.notes as audit_notes
from googlestatus g
 left outer join reviews as r on g.review_id = r.id
 left outer join appeals as a on g.appeal_id = a.id and a.review_id = r.id
 left outer join statuses as appstat on a.status_id = appstat.id
 left outer join statuses as gappstat on g.appeal_status_id = gappstat.id
 left outer join audits as d on d.appeal_id = a.id
 left outer join statuses as audstat on d.status_id = audstat.id
 left outer join statuses as gaudstat on g.test_status_id = gaudstat.id
group by r.id
order by r.url

Step 7

Run the SQL below to rename the GoogleStatus table for archival purposes

• Be sure to use the MMDDYYYY value from the result file so the table can be matched with the correct Google Results flat file

RENAME TABLE googlestatus TO googlestatusMMDDYYYY;

RENAME TABLE tempgooglestatus TO googleresultsMMDDYYYY;