def main(featList):
newLoad = False
print 'Begin creating new features from wtg.encounter'
# truncate mmi_wtg staging table
print 'Truncating wtg.encounter_stage...'
truncateWtgStaging()
# If not passed in, find a list of the new featureids
if featList == []:
newLoad = True
param = {}
sql = dbutil.sqlArgosImport.getAllEncounters
result = dbutil.dbSelect(conn, sql, param)
featList = [f[0] for f in result]
#== get from FeatureList.csv instead!
# copy new records to staging table
print 'Appending wtg.encounter_stage...'
fillStaging(sorted(featList))
# create temporary XY layer
print 'Creating in_memory XY event layer...'
scratchFile = makeXY()
#truncate SDE Encouter_Stage
print 'Truncating sde.encounter_stage...'
truncateSdeStaging()
if newLoad:
print 'WARNING!! Truncating sde.encounter...'
truncateSdeEncounter()
# copy features to staging featureclass
print "Appending sde.encounter_stage..."
copyToStage(scratchFile)
# insert staging features to Encounter
print "Loading sde.encounter from staging..."
loadEncounter()
def fillStaging(featList):
param = {'feats': tuple(featList)}
sql = dbutil.sqlArgosImport.appendStaging
try:
result = dbutil.dbSelect(conn, sql, param)
#objsList = [f[0] for f in result]
except Exception as e:
print 'Error appending wtg.encounter_stage: ' + e.message
conn.rollback()
finally:
result = None
conn.commit()
print '...Done'
def newBehaviorData(ptt,devID,inFileName):
newData = []
behDict = db_util.sqlMeasData.behavDict
with open(inFileName, 'rb') as inFile:
reader = csv.DictReader(inFile)
for row in reader:
beh = row['What']
if beh == 'Message':
# find the message time to match with DeviceTransmit
# THIS IS THE WRONG TIMEVAL!!!
# Maybe record the time of the behavior, don't link to a transmit
timeVal = util.getDateFromSerial(float(row['Start']))
param = {'device': devID, 'timeVal':timeVal}
# getTransmits returns a list of tuples[(measID,txID),(measID,txID)]
featList = db_util.dbSelect(conn, db_util.sqlMeasData.getTransmits, param)
if len(featList)<1:
print "getStatus error: no transmit found for "+timeVal.strftime('%Y-%m-%d %H:%M:%S')
# if multiple transmits found, only use the last one
measID, txID = featList[-1]
elif beh == 'Dive':
for key in behDict.keys():
if row[key]:
try:
measObj=tables.MeasuredData(behDict[key][0], # ParameterID
row[key], # DataValue
behDict[key][1], # ValueDomain
behDict[key][2], # DomainID
None, # Invalid
devID, # DeviceID
'', # MeasureType
None, # TypeID
measID) # MeasurementID
measID = db_util.dbTransact(conn,measObj.queryText(),measObj.insertSQL())
except Exception as e:
print 'newBehaviorData error: ' + e.message
conn.rollback()
finally:
measObj = None
conn.commit()
newData.append(measuredID)
else:
pass
return newData
def newStatusData(ptt,devID,inFileName):
newData = []
statDict = db_util.sqlMeasData.statusDict
with open(inFileName, 'rb') as inFile:
reader = csv.DictReader(inFile)
for row in reader:
timeVal = util.getDateFromSerial(float(row['Received']))
param = {'device': devID, 'timeVal':timeVal}
# getTransmits returns a list of tuples[(measID,txID),(measID,txID)]
featList = db_util.dbSelect(conn, db_util.sqlMeasData.getTransmits, param)
if len(featList)<1:
print "getStatus error: no transmit found for "+timeVal.strftime('%Y-%m-%d %H:%M:%S')
# if multiple transmits found, only use the last one
measID, txID = featList[-1]
for key in statDict.keys():
if row[key]:
try:
measObj=tables.MeasuredData(statDict[key][0], # ParameterID
row[key], # DataValue
statDict[key][1], # ValueDomain
statDict[key][2], # DomainID
None, # Invalid
devID, # DeviceID
'devicetransmit', # MeasureType
txID, # TypeID
measID) # MeasurementID
measID = db_util.dbTransact(conn,measObj.queryText(),measObj.insertSQL())
except Exception as e:
print 'newStatusData error: ' + e.message
conn.rollback()
finally:
measObj = None
conn.commit()
newData.append(measID)
return newData
def insertDeploys():
conn = dbutil.getDbConn('mmi_wtg')
sql = dbutil.sqlDeployments.insertDeploys
deploys = dbutil.dbSelect(conn, sql, ())
return deploys
def delDuplicates(features):
# ======== Hangs on single FeatureID b/c of parenthesis in tuple
# =========TODO: convert to proper parameter passing!!!================
# 1 fetch MeasurementID's
sql = dbutil.sqlDuplicates.getMeasurements
if len(features) == 1:
param = '(' + str(features[0]) + ')'
else:
param = tuple(features)
sql = sql + str(param) + ';'
result = dbutil.dbSelect(conn, sql, ())
measList = [f[0] for f in result]
if len(measList) > 0:
# 2 DEL from DeviceTransmit
sql = dbutil.sqlDuplicates.delDevTransmit
if len(features) == 1:
param = '(' + str(measList[0]) + ')'
else:
param = tuple(measList)
sql = sql + str(param) + ';'
result = dbutil.dbDelete(conn, sql)
if result == 'Failed':
print 'Delete from DeviceTransmit failed'
conn.rollback()
sys.exit
else:
print result
conn.commit()
# 3 DEL from Measurement
sql = dbutil.sqlDuplicates.delMeasurement
if len(features) == 1:
param = '(' + str(features[0]) + ')'
else:
param = tuple(features)
sql = sql + str(param) + ';'
result = dbutil.dbDelete(conn, sql)
if result == 'Failed':
print 'Delete from Measurement failed'
conn.rollback()
sys.exit
else:
print result
conn.commit()
else:
print 'No Measurements found'
# 4 DEL from ArgosPass
sql = dbutil.sqlDuplicates.delArgosPass
if len(features) == 1:
param = '(' + str(features[0]) + ')'
else:
param = tuple(features)
sql = sql + str(param) + ';'
result = dbutil.dbDelete(conn, sql)
if result == 'Failed':
print 'Delete from ArgosPass failed'
conn.rollback()
sys.exit
else:
print result
conn.commit()
# 5 DEL duplicate encounters
sql = dbutil.sqlDuplicates.delEncounter
if len(features) == 1:
param = '(' + str(features[0]) + ')'
else:
param = tuple(features)
sql = sql + str(param) + ';'
result = dbutil.dbDelete(conn, sql)
if result == 'Failed':
print 'Delete from Encounter failed'
conn.rollback()
sys.exit
else:
print result
conn.commit()
print '...Done with deletions'
def getDuplicates(tagDict, newFeats):
"""Return a list of duplicate featureid's to delete from encounter
and write them to a log file"""
featIDs = []
skipID = []
logger = []
for device, vals in sorted(tagDict.items()): # sorted on DevID
ptt = str(vals[0]) # int
yr = str(vals[7]) # int
cruise = vals[8] # str
outFile = outPath + '\\' + yr + '\\' + cruise + '\\DuplicatesDeleted.csv'
print "Processing duplicates for PTT: " + ptt
param = {'devID': device, 'featList': tuple(newFeats)}
featList = dbutil.dbSelect(conn, dbutil.sqlDuplicates.getPasses, param)
# iter over features to find dupes
for tup in featList:
featID = tup[0]
if featID in skipID:
skipID.remove(featID)
continue # if lastID was the duplicate skip it
tag = tup[1][:-9]
timeVal = tup[2]
sat = tup[3]
nbMes = tup[4]
timeStart = timeVal + datetime.timedelta(minutes=-6)
timeStop = timeVal + datetime.timedelta(minutes=6)
dupeList = (row for row in featList
if (row[3] == sat and row[2] > timeStart
and row[2] < timeStop and row[0] <> featID))
for row in dupeList:
if row[4] > nbMes:
featIDs.append(featID) # mark the original for deletion
skipID.append(row[0])
logger.append([
featID, tag,
timeVal.strftime('%Y-%m-%d %H:%M:%S'), sat, nbMes
])
elif row[4] < nbMes:
featIDs.append(row[0]) # mark the match for deletion
skipID.append(row[0])
logger.append([
row[0], row[1], row[2].strftime('%Y-%m-%d %H:%M:%S'),
row[2], row[3]
])
elif row[4] == nbMes:
if row[2] < timeVal:
featIDs.append(row[0]) # mark the match for deletion
skipID.append(row[0])
logger.append([
row[0], row[1],
row[2].strftime('%Y-%m-%d %H:%M:%S'), row[3],
row[4]
])
else:
featIDs.append(
featID) # mark the original for deletion
skipID.append(row[0])
logger.append([
featID, tag,
timeVal.strftime('%Y-%m-%d %H:%M:%S'), sat, nbMes
])
print ptt + '...Done'
#write deletions to log
with open(outFile, 'ab') as outputFile:
writer = csv.writer(outputFile)
writer.writerow(
["FeatureID", "TagName", "TimeValue", "Sat.", "NbMsgs"])
for f in logger:
writer.writerow(f)
return featIDs