Loop through List find Records with no entries in last 30 days

[jaykapalczynski]

I have  list that contains all the Boat Ramps I want to validate.

I want a query that loops through each boat ramp name and then determines which ones do not have a record in the last 30 days.  I need that result to be written to a CSV file.

I think I have the beginning and the end but not the part that loops and queries for Boat Ramps that do NOT have a record in the last 30 days

I need to query the FC variable (my dataset that has all the records)

"Ramp_1" is the field in the FC that has the Boat Ramp Name

Using the List for Boat Ramp names (List) 

Loop through the List and find which records in the FC do not have a record in the last 30 days.

import arcpy
import os, string
import smtplib, shutil
import email, time
import sys, csv
from email.MIMEMultipart import MIMEMultipart
from email.mime.text import MIMEText


#Variables==============================================

arcpy.env.workspace = "C:\\Users\\xxxx\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xxxx.sde"
fc = "xxxxx"

var_additionalcomments = 'additionalcomments'
var_CreationDate,var_Creator = 'CreationDate','Creator'

headers = ['comments','Creation Date','Creator']
         
QueryFields = [var_additionalcomments,var_CreationDate,var_Creator


# Get Date 30 days ago
import datetime as DT
today = DT.date.today()
month_ago = today - DT.timedelta(days=30)
print month_ago

# Get list of all Boat Ramps
myLayer = "BoatRampsWGS"
myField = "SITENAME"
myBoatRampList = [row[0] for row in arcpy.da.SearchCursor(myLayer, myField)]
print myBoatRampList

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days

# I AM LOST HERE .... THIS IS WHERE THE LOOP AND QUERY SHOULD BE BUT DONT KNOW WHERE TO STATR




# Write the results to CSV file.
outFileRegionDate = open(r"E:\ArcGISProjects\xxxx\PythonScripts\PythonSync\PythonScripts\z_outFileRegionDate.csv", "w")  

mylist=[]
with arcpy.da.SearchCursor(fc, QueryFields, where_clause=expressionDate) as cursorDate:
    outFileRegionDate.write(','.join(headers) + '\n')
    flagDate = False           
    for row in cursorDate:
        flagDate = True
        zvalDate = str('{0},{1},{2}'.format(row[0],row[1],row[2]))
        outFileRegionDate.write(zvalDate + "%s\n")
        mylist.append(zvalDate)
    if not flagDate:
        print "No rows found"
outFileRegionDate.close()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MitchHolley1]

Jay, 

In line 18, change the > to a <. 

[jaykapalczynski]

Line 18 is commented out right now.

I don't know why I am having a hard time with this.  I don't see where in the query it compares the last 30 day Boat Ramp Inspections  against ALL the available Boat Ramps to determine which ones were not visited.

[MitchHolley1]

Jay, 

I'm just getting more and more confused and I don't think I completely understand the original question. 

E-mail me if necessary mitchh300@gmail.com

[jaykapalczynski]

OK this is where I am and think I have what I need , albeit very messy...maybe someone has a more streamlined way to accomplish this.

Background:

• I have a FC that has ALL boat Ramps in our state
  • It will have records/boat rampsin the state
• I have a FC of inspections of these boat ramps
  • this FC will not have all the boat ramps in it as not all have been inspected
  • this FC will have duplicates boat ramp entries as its an inspection FC over time

1. I am writing ALL the Boat Ramps to a list
2. I am writing ALL the Inspections in the last 30 days to a list
3. I then need to find the Unmatched between the Boat Ramps and the Unique Inspections
4. This will give me a result of the ALL Boat Ramps that were not Inspected in the last 30 days.

where the MATCHED will be the Boat Ramps that were inspected in the last 30 days

where the UNMATCHED are the Boat Ramps that were NOT inspected in the last 30 days.

I think this works in it current form but am sure this can be done more efficiently!!!!! Anyone have any input or thoughts on how to streamline this?

ALL Boat Ramps:

I create a list of ALL the Boat Ramps from the FC

# Get list of all Boat Ramps
myLayer = "BoatRampsWGS"
myField = "SITENAME"
myBoatRampList = [row[0] for row in arcpy.da.SearchCursor(myLayer, myField)]‍‍‍‍

Inspections:

Query Inspection FC to get all the inspections in the last 30 days

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days
Inspected = []

dateQry="""CreationDate > '{}'""".format(month_ago)

count = 0
with arcpy.da.SearchCursor(fc, QueryFields2, dateQry) as cursor:
    for row in cursor:
        #print(row)
        count = count + 1
        #CreatevalDate = (str(row[0]),str(row[1]))
        CreatevalDate = (str(row[0]))
        Inspected.append(CreatevalDate)
del cursor

# List of boat ramps inspected in last 30 days
print Inspected
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Convert to SET and get unmatched and Matched Boat Ramps

#Convert ALL Boat Ramps to a set to get the unique values
mysetRampList = set(myBoatRampList)

#Convert Inspections from last 30 days to a set to get the unique values
mysetInspected = set(Inspected)

# Get the Matched and Unmatched values using a SET
matched = mysetRampList.intersection(mysetInspected) 
unmatched = mysetRampList.symmetric_difference(mysetInspected)

# MATCHED
# convert back to list sort and print
mynewMatchedlist = list(matched)
mynewMatchedlist.sort();
print mynewMatchedlist

# UNMATCHED
# convert back to list sort and print
mynewUnmatchedlist = list(unmatched)
mynewUnmatchedlist .sort();
print mynewUnmatchedlist
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍