MH contractor submittal location: P:\ConsentDecree\MACP\Data
S:\DPW\Restricted\Util Consultant\POSM MH Upload
Since you don’t have access to the access DBs, I put a sample acess DBs in the attached folder.
• POSM, the app where all the completed MH inspections are uploaded to.
• GIS Layer, SEWERCAP.MH_INSP layer, only if QAQC_STATUS =“Approved” are the records that should be uploaded.
• Access Database, which are submitted by the contractors.
Goal of the script:
• Determine which records in the Access Database should be uploaded to POSM. A record should only be uploaded when
• There is no duplicate record in POSM already.
• It is marked as “Approved” in the SEWERCAP.MH_INSP GIS layer
A record is defined as one manhole id, or one row of data in the MH_inspections table in the Access Database. We count two records as duplicate if manhole id and inspection date and inspection time is the same
• Change file path
• Change time date field
At the same time, I’d like a excel output.
• Run through all Access Databases in location MHUpload\MACP\Data
Records are located in MH_Inspections, get Manhole_Number, Date, Time. Please also add these 3 fields to the excel sheet.
• Run though GIS layer,
• When facilityID in the Access Database(retrieved in step 1) is found in GIS,
-if SUBMITTAL_ID in GIS layer doesn’t match Access Database file name, or the Access Database file name is not found in the GIS layer, these records should not be uploaded. On the excel, create a column named GIS_Problem and return “Submittal ID not match” for the 2 cases described above”
(Submittal ID is col P on the layer, and is the access database name in the submitted DB)
-if submittal ID matches:
QC status = ‘APPROVED’ or ‘approve, no invoice’, then for the created column GIS_Problem, return “GIS-approved”
QC status! =(not equal to) ‘APPROVED’ or ‘approve, no invoice’, return “GIS-not-approved”
• When facilityID in the Access Database(retrieve in step 1) is not found in GIS, in an excel report, the column GIS_Problem will return “GIS-Asset not exist”
• Run through POSM(sql database) to get all records for all assets.
Only a complete inspection should be counted as a valid inspection:
Complete Inspection: DI, SI, RI
In POSM, there might be a couple inspection records for the same Manhole ID.
For all assets in the Access Database, check
• If there is a duplicated record in POSM. A record is considered a duplication when Manhole ID and inspection date (do not need to compare time) is the same.
Note In POSM:
• ManholeID: Field13
• Inspection date and Time: Field3, need to separate date and time and compare only date.
• If there is a duplicate in POSM, in the excel spreadsheet, create a new column POSM_Problem, and return ‘Record already in POSM’
• If there is no duplicate in POSM,
• When same Manhole ID exist, and the Access Database Inspection date is prior to the lastest inspection date for the same manhole id in POSM, in the POSM_Problem column, return ‘Record not in POSM-not most recent’
• If same Manhole ID exist in POSM, but the Access Database record is the most recent, please return ‘Record not in POSM-most recent’ under POSM_Problem column
• If Manhole ID never existed in POSM, the Access database record is a completely new record, please return ‘New Asset added’ under POSM_Problem column
• Create a new column named To_Be_Uploaded
Return ‘Yes’ when
GIS_Problem= ‘GIS-approved‘ and (POSM_Problem =‘Record not in POSM-not most recent‘ or POSM_Problem = ‘Record not in POSM-most recent‘ or POSM_Problem =’ New Asset added‘ )
Else return ‘No’
• For each Access Databases submitted, image path(image location) in 3 tables need to be changed, and the 3 tables are MH_Condition_Media, MH_Connection_Media, MH_Inspection _Media. File path should be changed to P:\MACP\Photos\submittal_name
6. In the Access Databases, MH_Inspections table, Inspection date and inspection time column, should be only date or time.
7.
• If all records in one submittal database are to be uploaded, then copy the whole database to a new folder \MHUpload\MACP\Upload
• If not all records in one submittal database are to be uploaded, then copy these records to one new access database and put the access database in \MHUpload\MACP\Upload
• all good to upload (Add) Database: copy to folder A
• partial database: move the Add records to a database in folder A, move the Not Add records to folder B