def send_thread():
while True:
#Sending messages
try:
time.sleep(0.005)
sql="SELECT * FROM `waterfall_2` WHERE processed = '0'"
cursor2.execute((sql))
rows=cursor2.fetchall()
if len(rows)>0:
sql="UPDATE `waterfall_2` SET processed = '1' WHERE processed = '0'"
cursor2.execute((sql))
bot.stdio_message('Waterfall succeeded!!!!')
logger.messageInfo("Waterfall_0 ==> Message sent to telephone")
connRAW2.commit()
except:
logger.messageException("Waterfall_0 ERROR selecting from DB")
def send_thread():
while True:
#Sending messages
try:
time.sleep(0.005)
sql = "SELECT * FROM `waterfall_2` WHERE processed = '0'"
cursor2.execute((sql))
rows = cursor2.fetchall()
if len(rows) > 0:
sql = "UPDATE `waterfall_2` SET processed = '1' WHERE processed = '0'"
cursor2.execute((sql))
bot.stdio_message('Waterfall succeeded!!!!')
logger.messageInfo("Waterfall_0 ==> Message sent to telephone")
connRAW2.commit()
except:
logger.messageException("Waterfall_0 ERROR selecting from DB")
def receive_thread():
global message
#Receiving messages
while True:
(i , o, e) = select.select(socketlist.keys(),[],[],1)
for each in i:
if socketlist[each] == 'xmpp':
cl.Process(1)
if message !='':
logger.messageInfo("Message is:"+message+"--")
try:
sql="INSERT INTO `test_table` (naam) VALUES ('Waterfall')"
cursor.execute((sql))
logger.messageInfo("Waterfall_0 ==> New Event Created")
connRAW.commit()
except:
logger.messageException("Waterfall_0 ERROR inserting in DB")
message=''
else:
raise Exception("Unknown socket type: %s" % repr(socketlist[each]))
def receive_thread():
global message
#Receiving messages
while True:
(i, o, e) = select.select(socketlist.keys(), [], [], 1)
for each in i:
if socketlist[each] == 'xmpp':
cl.Process(1)
if message != '':
logger.messageInfo("Message is:" + message + "--")
try:
sql = "INSERT INTO `test_table` (naam) VALUES ('Waterfall')"
cursor.execute((sql))
logger.messageInfo("Waterfall_0 ==> New Event Created")
connRAW.commit()
except:
logger.messageException(
"Waterfall_0 ERROR inserting in DB")
message = ''
else:
raise Exception("Unknown socket type: %s" %
repr(socketlist[each]))
connTAB.commit()
except mdb.Error, e:
connTAB.rollback()
logger.messageException(
str("TABS | MySQLdb error %d: %s" % (e.args[0], e.args[1])))
except:
connTAB.rollback()
logger.messageException(
"TABS | Exception in reading from __events_tabs")
try:
logger.messageInfo("TABS | new TABS event: " + str(row))
id = row["id"]
RAW_id = row["RAW_id"]
timestamp = row["timestamp"]
timestamp_delayed = timestamp + datetime.timedelta(0, 1)
type_id = row["type_id"]
user_id = row["user_id"]
location_id = row["location_id"]
tab_id = row["tab_id"]
description = row[
"description"] #The "name:" variable stays in the description. It is useless to take it out.
processed = row["processed"]
################ Create Tab
if type_id in [2]:
time.sleep(60)
try:
#Connect to database
connRAW = mdb.connect(host, usrnm, psswrd, db_name);
cursor = connRAW.cursor(mdb.cursors.DictCursor)
#Check for newly uploaded ZIP files
list = os.listdir("/home/") # returns list
list.sort()
for file in list:
try:
unzip("/home/"+file, home+"/unzipped/")
os.rename("/home/"+file, home+"/processed/"+file)
logger.messageInfo(file+" successfully unzipped and moved to PROCESSED folder")
except:
logger.messageException("Exception during Unzip - File is probably still being transferred")
#remove test message from WebBox
try:
os.remove(home+"/unzipped/Info.xml")
except:
pass
#Removing LOG files (device events), keeping MEAN files (actual data)
list = os.listdir(home+"/unzipped") # returns list
list.sort()
except mdb.Error, e:
connRAW.rollback()
logger.messageException(
str("RAW | MySQLdb error %d: %s" % (e.args[0], e.args[1])))
except:
connRAW.rollback()
logger.messageException(
"RAW | Exception in reading from __events_RAW")
# if new event ==> take action
try:
for row in rows:
check = False
logger.messageInfo("RAW | new RAW event: " + str(row))
id = row["id"]
timestamp = row["timestamp"]
type_id = row["type_id"]
user_id = row["user_id"]
location_id = row["location_id"]
tab_id = row["tab_id"]
consumption_id = row["consumption_id"]
consumption_nr = row["consumption_nr"]
description = row["description"]
amount = row["amount"]
processed = row["processed"]
if type_id in [1]:
sql = "UPDATE __events_RAW SET processed='1' WHERE id=" + str(
logging.basicConfig(
filename=LOG_FILENAME,
level=logging.DEBUG,
)
message = ''
#Connect to database
try:
connRAW = mdb.connect(host, usrnm, psswrd, db_name)
cursor = connRAW.cursor(mdb.cursors.DictCursor)
except:
logger.messageException("RAW | Except in connecting to database")
while True:
time.sleep(0.005)
try:
sql = "SELECT * FROM `waterfall_1` WHERE processed = '0'"
cursor.execute((sql))
rows = cursor.fetchall()
if len(rows) > 0:
sql = "UPDATE `waterfall_1` SET processed = '1' WHERE processed = '0'"
cursor.execute((sql))
sql = "INSERT INTO `waterfall_2` (original_id) VALUES ('12345')"
cursor.execute((sql))
logger.messageInfo("Waterfall_2 ==> Event processed")
connRAW.commit()
except:
logger.messageException("Waterfall_2 ERROR operations on DB")
#Logging
logging.basicConfig(filename=LOG_FILENAME,level=logging.DEBUG,)
message = ''
#Connect to database
try:
connRAW = mdb.connect(host, usrnm, psswrd, db_name);
cursor = connRAW.cursor(mdb.cursors.DictCursor)
except:
logger.messageException("RAW | Except in connecting to database")
while True:
time.sleep(0.005)
try:
sql="SELECT * FROM `test_table` WHERE processed = '0'"
cursor.execute((sql))
rows=cursor.fetchall()
if len(rows)>0:
sql="UPDATE `test_table` SET processed = '1' WHERE processed = '0'"
cursor.execute((sql))
sql="INSERT INTO `waterfall_1` (original_id) VALUES ('12345')"
cursor.execute((sql))
logger.messageInfo("Waterfall_1 ==> Event processed")
connRAW.commit()
except:
logger.messageException("Waterfall_1 ERROR operations on DB")
time.sleep(60)
try:
#Connect to database
connRAW = mdb.connect(host, usrnm, psswrd, db_name)
cursor = connRAW.cursor(mdb.cursors.DictCursor)
#Check for newly uploaded ZIP files
list = os.listdir("/home/") # returns list
list.sort()
for file in list:
try:
unzip("/home/" + file, home + "/unzipped/")
os.rename("/home/" + file, home + "/processed/" + file)
logger.messageInfo(
file +
" successfully unzipped and moved to PROCESSED folder")
except:
logger.messageException(
"Exception during Unzip - File is probably still being transferred"
)
#remove test message from WebBox
try:
os.remove(home + "/unzipped/Info.xml")
except:
pass
#Removing LOG files (device events), keeping MEAN files (actual data)
list = os.listdir(home + "/unzipped") # returns list
list.sort()
if not bot.xmpp_connect():
sys.stderr.write("Could not connect to server, or password mismatch!\n")
sys.exit(1)
cl.sendInitPresence()
socketlist = {cl.Connection._sock:'xmpp',sys.stdin:'stdio'}
online = 1
while online:
(i , o, e) = select.select(socketlist.keys(),[],[],1)
for each in i:
if socketlist[each] == 'xmpp':
cl.Process(1)
if message !='':
logger.messageInfo("Message is:"+message+"--")
try:
#sql="SELECT COUNT(*) FROM `test_table` WHERE naam = 'Frans van Hoogstraten'"
sql="SELECT * FROM `test_table` WHERE id LIKE '123456%'"
#sql="SELECT COUNT(*) FROM `test_table`"
if message == 'G':
sql="SELECT * FROM `test_table` WHERE id = '484848' OR id = '1455211' OR id = '8441526' OR id = '4756213' OR id = '4868752' OR id = '1166224' OR id = '8899665' OR id = '87562' OR id = '8596' OR id = '6565656' OR id = '8787856' OR id = '523652'"
elif message == 'V':
sql="SELECT * FROM `test_table` WHERE id LIKE '%12345%'"
cursor.execute((sql))
rows=cursor.fetchall()
connRAW.commit()
except:
a=1
bot.stdio_message(str(rows))
# time.sleep(timeout)
if naam=='Frans van Hoogstraten':
naam='Jan van Meurs'
else:
naam='Frans van Hoogstraten'
# write event
try:
sql="INSERT INTO `test_table` (`naam`) "+\
"VALUES ('%s')" % (naam)
cursor.execute((sql))
insert_id=connRAW.insert_id()
connRAW.commit()
logger.messageInfo("Event"+str(insert_id)+" created")
if insert_id > 10000000:
break
except mdb.Error, e:
connRAW.rollback()
logger.messageException(str("MySQLdb error %d: %s" % (e.args[0],e.args[1])))
except:
connRAW.rollback()
logger.messageException("Exception during writing to database")
except mdb.Error, e:
connRAW.rollback()
logger.messageException(str("RAW | MySQLdb error %d: %s" % (e.args[0],e.args[1])))
except:
connRAW.rollback()
logger.messageException("RAW | Exception in reading from __events_RAW")
# if new event ==> take action
try:
for row in rows:
check = False
logger.messageInfo("RAW | new RAW event: " + str(row))
id=row["id"]
timestamp=row["timestamp"]
type_id=row["type_id"]
user_id=row["user_id"]
location_id=row["location_id"]
tab_id=row["tab_id"]
consumption_id=row["consumption_id"]
consumption_nr=row["consumption_nr"]
description=row["description"]
amount=row["amount"]
processed=row["processed"]
if type_id in [1]:
sys.stderr.write(
"Could not connect to server, or password mismatch!\n")
sys.exit(1)
cl.sendInitPresence()
socketlist = {cl.Connection._sock: 'xmpp', sys.stdin: 'stdio'}
online = 1
while online:
(i, o, e) = select.select(socketlist.keys(), [], [], 1)
for each in i:
if socketlist[each] == 'xmpp':
cl.Process(1)
if message != '':
logger.messageInfo("Message is:" + message + "--")
try:
#sql="SELECT COUNT(*) FROM `test_table` WHERE naam = 'Frans van Hoogstraten'"
sql = "SELECT * FROM `test_table` WHERE id LIKE '123456%'"
#sql="SELECT COUNT(*) FROM `test_table`"
if message == 'G':
sql = "SELECT * FROM `test_table` WHERE id = '484848' OR id = '1455211' OR id = '8441526' OR id = '4756213' OR id = '4868752' OR id = '1166224' OR id = '8899665' OR id = '87562' OR id = '8596' OR id = '6565656' OR id = '8787856' OR id = '523652'"
elif message == 'V':
sql = "SELECT * FROM `test_table` WHERE id LIKE '%12345%'"
cursor.execute((sql))
rows = cursor.fetchall()
connRAW.commit()
except:
a = 1
bot.stdio_message(str(rows))
row=cursor.fetchone()
connSAL.commit()
except mdb.Error, e:
connSAL.rollback()
logger.messageException(str("SALES | MySQLdb error %d: %s" % (e.args[0],e.args[1])))
except:
connSAL.rollback()
logger.messageException(str("SALES | Exception in reading from __events_sales"))
try:
rows="" #in case the SELECT try-loop fails, 'rows' has been declared and is empty
logger.messageInfo("SALES | new SALES event: " + str(row))
id=row["id"]
RAW_id=row["RAW_id"]
timestamp=row["timestamp"]
type_id=row["type_id"]
tab_id=row["tab_id"]
consumption_id=row["consumption_id"]
consumption_nr=row["consumption_nr"]
description=row["description"]
amount=row["amount"]
processed=row["processed"]
#Conversion to Personal SALES entries
#Retrieving Tab members, and loading them into the 'rows' variable