def updateSimNumTable(name, sim_num, date_activated):
db, cur = dbio.SenslopeDBConnect('local')
while True:
try:
query = """select sim_num from senslopedb.site_column_sim_nums
where name = '%s' """ % (name)
a = cur.execute(query)
if a:
out = cur.fetchall()
if (sim_num == out[0][0]):
print ">> Number already in database", name, out[0][0]
return
break
else:
print '>> Number not in database', sim_num
return
break
except MySQLdb.OperationalError:
print '1.',
raise KeyboardInterrupt
query = """INSERT INTO senslopedb.site_column_sim_nums
(name,sim_num,date_activated)
VALUES ('%s','%s','%s')""" % (name, sim_num, date_activated)
dbio.commitToDb(query, 'updateSimNumTable')
def checkCaseBasedFromInbox(): #okay
global globaldf
db, cur = dbio.SenslopeDBConnect('gsm')
for i in range(0, len(globaldf)):
if (globaldf.health_case.loc[i] == 2):
s_number = str(globaldf.sim_num.loc[i])
query = "SELECT sms_msg from smsinbox where sms_id > (select max(sms_id)-10000 from smsinbox) and sim_num = " + s_number + """ and (sms_msg like "%NO DATA PARSED%" or sms_msg like "%NO DATA FROM SENSELOPE%" or sms_msg like "%MANUAL RESET%") order by sms_id desc"""
try:
mesdf = psql.read_sql(query, db)
except pd.io.sql.DatabaseError, e:
mesdf = 0
tempStr = str(mesdf)
tempStr = tempStr.lower()
if (tempStr.find("no data parsed")) > 0:
health = 7
if (tempStr.find("nodataparsed")) > 0:
health = 7
elif (tempStr.find("no data from senselope")) > 0:
health = 6
elif (tempStr.find("nodatafromsenselope")) > 0:
health = 6
elif (tempStr.find("nodatafromsenslope")) > 0:
health = 6
elif (tempStr.find("manual reset")) > 0:
health = 9
elif (tempStr.find("manualreset")) > 0:
health = 9
else:
health = 4
globaldf.set_value(i, 'health_case', health)
def checkLastActive(lgr_name):
db, cur = dbio.SenslopeDBConnect('local')
query = """select timestamp from """ + lgr_name + """ order by timestamp desc limit 1 """
timeNow = dt.today()
try:
a = cur.execute(query)
if a:
out = cur.fetchall()
for i in range(0, len(out)):
st = out[i]
lastTime = st[0]
tdelta = timeNow - lastTime
if lastTime > timeNow:
return 1
elif tdelta.seconds / 60 < 59:
return 1
elif tdelta.days < 2 and tdelta.seconds / 60 > 60:
return 20
else:
return 2
except MySQLdb.ProgrammingError:
return 50
db.close()
def activeSoms():
somsdf = 0
db, cur = dbio.SenslopeDBConnect('local')
query = '''SELECT name, loggers.model_id from loggers inner join (select model_id from logger_models where has_soms =1) a on a.model_id= loggers.model_id'''
try:
somsdf = psql.read_sql(query, db)
except pd.io.sql.DatabaseError, e:
somsdf = 0
def updateSimNumTable(name, sim_num, date_activated):
db, cur = dbio.SenslopeDBConnect('local')
query = """INSERT IGNORE INTO site_column_sim_nums (name,sim_num,date_activated)
VALUES ('%s','%s','%s')""" % (name.upper(), sim_num,
date_activated)
dbio.commitToDb(query, 'updateSimNumTable')
def updatedeadSites():
db, cur = dbio.SenslopeDBConnect('local')
query = "SELECT logger_id FROM senslopedb.loggers where date_deactivated IS NOT NULL"
lgrdf = psql.read_sql(query, db)
timeNow = dt.today()
for i in range(0, len(lgrdf)):
logger_id = str(lgrdf.logger_id.loc[i])
health = 5
encodeCase(timeNow, lgrdf.logger_id.loc[i], health)
def readDataframe():
# global globaldf
localdf = 0
db, cur = dbio.SenslopeDBConnect('local')
query = '''SELECT logger_health.logger_id, name, model_id, sim_num, health_case from logger_health inner join loggers on logger_health.logger_id= loggers.logger_id inner join logger_contacts on logger_health.logger_id= logger_contacts.logger_id where logger_health.health_case !=5 and logger_health.health_id IN (select max(logger_health.health_id) from logger_health group by logger_id)'''
try:
localdf = psql.read_sql(query, db)
except pd.io.sql.DatabaseError, e:
localdf = 0
def encodeCase(timestamp, logger_id, case): #okay
db, cur = dbio.SenslopeDBConnect('local')
query = '''SELECT health_id, health_case from logger_health where logger_id = ''' + str(
logger_id) + ''' order by health_id desc limit 1'''
casedf = psql.read_sql(query, db)
try:
prevState = str(casedf.health_case.loc[0])
prevCaseId = str(casedf.health_id.loc[0])
except KeyError, e:
prevState = 0
def storehealthData():
global globaldf
db, cur = dbio.SenslopeDBConnect('local')
columns = ['logger_id', 'batv1', 'batv2', 'signal', 'model']
siteHealthdf = pd.DataFrame(columns=columns)
for i in range(0, len(globaldf)):
if (globaldf.health_case.loc[i] > 20
and globaldf.health_case.loc[i] < 25):
lgr_name = str(globaldf.name.loc[i])
logger_id = int(globaldf.logger_id.loc[i])
logger_model = int(globaldf.model_id[i])
if (logger_model > 1 and logger_model < 10):
query = '''SELECT avg(batv1),avg(batv2),avg(csq) from ''' + lgr_name + 'w' + ''' order by timestamp desc limit 48'''
#average lang ito, dapat sana trendin, pero next time na powsz
try:
tempdf = psql.read_sql(query, db)
siteHealthdf.set_value(i, 'batv1',
tempdf.loc[0, 'avg(batv1)'])
siteHealthdf.set_value(i, 'batv2',
tempdf.loc[0, 'avg(batv2)'])
siteHealthdf.set_value(i, 'signal', tempdf.loc[0,
'avg(csq)'])
except pd.io.sql.DatabaseError, e:
tempdf = 0
elif (logger_model > 9 and logger_model < 35):
sitecode = lgr_name[:3]
query = '''SELECT avg(batv1),avg(csq) from ''' + lgr_name + 'w' + ''' order by timestamp desc limit 48'''
try:
tempdf = psql.read_sql(query, db)
siteHealthdf.set_value(i, 'batv1',
tempdf.loc[0, 'avg(batv1)'])
siteHealthdf.set_value(i, 'signal', tempdf.loc[0,
'avg(csq)'])
except pd.io.sql.DatabaseError, e:
tempdf = 0
siteHealthdf.set_value(i, 'model', logger_model)
siteHealthdf.set_value(i, 'logger_id', logger_id)
def checkNameOfNumber(number):
db, cur = dbio.SenslopeDBConnect('local')
while True:
try:
query = """select name from senslopedb.site_column_sim_nums
where sim_num like '%s' """ % (number)
a = cur.execute(query)
if a:
out = cur.fetchall()
return out[0][0]
else:
print '>> Number not in database', number
return ''
except MySQLdb.OperationalError:
# except KeyboardInterrupt:
print '4.',
time.sleep(2)
db.close()