def GenerateMonitoringPurgedFiles():
print 'Generating monitoring purged files:'
# get a list of columnArray classes
sites = GetSensorList()
for site in sites:
siteid = site.name
print siteid,
ft = dt.now() - tda(days=moninterval)
data = GetRawColumnData(siteid, ft.strftime("%Y-%m-%d %H:%M:%S"),
site.nos)
df = PurgeNonAlignedEntries(data)
if siteid == 'sinb':
df = FixOneBitChange(df)
dflgd = GenerateLastGoodData(site, df)
dflgd.to_csv(LastGoodDataFP + "" + siteid + ".csv",
index=False,
header=False,
float_format='%.0f')
#dflgd = pd.read_csv(LastGoodDataFP + siteid + ".csv", names=['ts','id','x','y','z','m'])
# get the missing node data from last good data file
df = df.append(dflgd.loc[~dflgd.id.isin(df.id.unique())])
df.to_csv(MonPurgedFP + siteid + ".csv",
index=False,
header=False,
float_format='%.0f')
print 'done'
def main():
name = sys.argv[1]
date = str(sys.argv[2])
time = str(sys.argv[3])
custom_end = pd.to_datetime(date + ' ' + time)
name = str(name)
next_timestamp = custom_end + tda(minutes=30)
try:
ad.main(name, custom_end)
if next_timestamp < dt.now():
# write_next_task(name,next_timestamp)
return 1 #baka return value may problem???
else:
print "++++++++++++++Stopping +++++++++++++++++++"
return 2
except exc.IntegrityError:
print '------------IntegrityError------------'
# if next_timestamp < dt.now():
# write_next_task(name,next_timestamp)
return 1 # okay lang di maoverwrite yung nakasulat na sa db
else:
print 'ibang error pa'
return -1
def GetRawAccelData(siteid = "", fromTime = "", toTime = "", maxnode = 40, msgid = "", targetnode ="", batt=0, returndb=True):
if not siteid:
raise ValueError('no site id entered')
if printtostdout:
PrintOut('Querying database ...')
if (len(siteid) == 5):
if not msgid:
# print "inside ----> if not msgid:"
query = " SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt FROM senslopedb.%s" % (siteid,siteid)
targetnode_query = " WHERE id IN (SELECT node_id FROM senslopedb.node_accel_table WHERE site_name = '%s' and accel = 1)" %siteid
if targetnode != '':
targetnode_query = " WHERE id IN ('%d')" %targetnode
query = query + targetnode_query
query = query + " AND msgid in (11, 32)"
if not fromTime:
fromTime = "2010-01-01"
query = query + " AND timestamp >= '%s'" %fromTime
toTime_query = ''
if toTime != '':
toTime = pd.to_datetime(toTime)+tda(hours=0.5)
toTime_query = " AND timestamp <= '%s'" %toTime
elif toTime:
toTime_query = ''
query = query + toTime_query
query = query + " UNION ALL"
query = query + " SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt FROM senslopedb.%s" % (siteid,siteid)
targetnode_query = " WHERE id IN (SELECT node_id FROM senslopedb.node_accel_table WHERE site_name = '%s' and accel = 2)" %siteid
if targetnode != '':
targetnode_query = " WHERE id IN ('%d')" %targetnode
query = query + targetnode_query
query = query + " AND msgid in (12, 33)"
query = query+ " AND timestamp > '%s'" %fromTime
query = query + toTime_query
elif msgid in (11,12,32,33):
# print "inside ----> elif msgid in (11,12,32,33):"
query = " SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt FROM senslopedb.%s WHERE msgid = %d" % (siteid,siteid,msgid)
if (targetnode != ""):
query = query + " AND id = %d" %(targetnode)
if not fromTime:
fromTime = "2010-01-01"
query = query + " AND timestamp >= '%s'" %fromTime
toTime_query = ''
if toTime != '':
toTime = pd.to_datetime(toTime)+tda(hours=0.5)
toTime_query = " AND timestamp <= '%s'" %toTime
elif toTime:
toTime_query = ''
query = query + toTime_query
elif (len(siteid) == 4):
query = "select timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue from senslopedb.%s " % (siteid,siteid)
if not fromTime:
fromTime = "2010-01-01"
query = query + " where timestamp >= '%s'" % fromTime
if toTime != '':
query = query + " and timestamp <= '%s'" % toTime
if targetnode != '':
query = query + " and id = %s" % (targetnode)
else:
query = query + " and id >= 1 and id <= %s " % (str(maxnode))
if returndb:
if (len(siteid) == 5):
df = GetDBDataFrame(query)
# df = df.groupby(['id'])
# df = df.apply(filterSensorData.volt_filter) #function in filterSensorData
# df = df.reset_index(drop=True)
if (batt == 1):
df.columns = ['ts','name','id','x','y','z','batt']
df.ts = pd.to_datetime(df.ts)
return df
else:
df = df.drop('batt',axis=1)
else:
df = GetDBDataFrame(query)
df.columns = ['ts','name','id','x','y','z']
df.ts = pd.to_datetime(df.ts)
return df
else:
return query
def GetSingleLGDPM(name, nodes, offsetstart):
lgdpm = GetRawAccelData(name, offsetstart - tda(3), offsetstart)
lgdpm = lgdpm[lgdpm.id.isin(nodes)]
return lgdpm
def GetSingleLGDPM(name, nodes, offsetstart):
lgdpm = GetRawAccelData(name, offsetstart-tda(3), offsetstart)
lgdpm = lgdpm[lgdpm.id.isin(nodes)]
return lgdpm
def GetRawAccelData(siteid = "", fromTime = "", toTime = "", maxnode = 40, msgid = "", targetnode ="", batt=0, returndb=True):
if not siteid:
raise ValueError('no site id entered')
if printtostdout:
PrintOut('Querying database ...')
if (len(siteid) == 5):
if not msgid:
# print "inside ----> if not msgid:"
query = " SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt,1 as 'accel' FROM senslopedb.%s" % (siteid,siteid)
targetnode_query = " WHERE id IN (SELECT node_id FROM senslopedb.node_accel_table WHERE site_name = '%s' and accel = 1)" %siteid
if targetnode != '':
targetnode_query = " WHERE id IN ('%d')" %targetnode
query = query + targetnode_query
query = query + " AND msgid in (11, 32)"
if not fromTime:
fromTime = "2014-12-01"
query = query + " AND timestamp >= '%s'" %fromTime
toTime_query = ''
if toTime != '':
toTime = pd.to_datetime(toTime)+tda(hours=0.5)
toTime_query = " AND timestamp <= '%s'" %toTime
elif toTime:
toTime_query = ''
query = query + toTime_query
query = query + " UNION ALL"
query = query + " SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt,2 as 'accel' FROM senslopedb.%s" % (siteid,siteid)
targetnode_query = " WHERE id IN (SELECT node_id FROM senslopedb.node_accel_table WHERE site_name = '%s' and accel = 2)" %siteid
if targetnode != '':
targetnode_query = " WHERE id IN ('%d')" %targetnode
query = query + targetnode_query
query = query + " AND msgid in (12, 33)"
query = query+ " AND timestamp > '%s'" %fromTime
query = query + toTime_query
elif msgid in (11,12,32,33):
# print "inside ----> elif msgid in (11,12,32,33):"
query = (" SELECT timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,batt,if(msgid"
" in (32,11), 1,if(msgid in (33,12),2,0)) "
" as 'accel' FROM senslopedb.%s WHERE msgid = %d" % (siteid,siteid,msgid))
if (targetnode != ""):
query = query + " AND id = %d" %(targetnode)
if not fromTime:
fromTime = "2014-12-01"
query = query + " AND timestamp >= '%s'" %fromTime
toTime_query = ''
if toTime != '':
toTime = pd.to_datetime(toTime)+tda(hours=0.5)
toTime_query = " AND timestamp <= '%s'" %toTime
elif toTime:
toTime_query = ''
query = query + toTime_query
elif (len(siteid) == 4):
query = "select timestamp,'%s' as 'name',id,xvalue,yvalue,zvalue,1 as 'accel' from senslopedb.%s " % (siteid,siteid)
if not fromTime:
fromTime = "2010-01-01"
query = query + " where timestamp >= '%s'" % fromTime
if toTime != '':
query = query + " and timestamp <= '%s'" % toTime
if targetnode != '':
query = query + " and id = %s" % (targetnode)
else:
query = query + " and id >= 1 and id <= %s " % (str(maxnode))
if returndb:
if (len(siteid) == 5):
df = GetDBDataFrame(query)
# df = df.groupby(['id'])
# df = df.apply(filterSensorData.volt_filter) #function in filterSensorData
# df = df.reset_index(drop=True)
if (batt == 1):
df.columns = ['ts','name','id','x','y','z','accel','batt']
df.ts = pd.to_datetime(df.ts)
return df
else:
df = df.drop('batt',axis=1)
else:
df = GetDBDataFrame(query)
df.columns = ['ts','name','id','x','y','z','accel']
df.ts = pd.to_datetime(df.ts)
return df
else:
return query