#!/usr/bin/python

import sys
import time
import datetime
import glob
import os
import subprocess
import commands
import MySQLdb

path = '/opt/sensiplicity'



def getVarFromFile(filename):
    import imp
    f = open(filename)
    global data
    data = imp.load_source('data', '', f)
    f.close()

getVarFromFile(path + '/etc/SS-L1.conf')

db = MySQLdb.connect(data.servername,data.username,data.password,data.dbname)
cursor = db.cursor()

def ssh_status():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'ssh_state'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

ssh_state = ssh_status()


def shostname():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'server_hostname'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

server_hostname = shostname()

def rhost():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_hostname'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

host = rhost()


def rport():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_port'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

port = rport()

def ruser():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_user'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

username = ruser()

def rpass():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_pass'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

password = rpass()

def rpath():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_path'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

remotepath = rpath()

def rserver_sync():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_sync'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

synctime = rserver_sync()


def usb_status():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'usb_state'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

usb_state = usb_status()

from datetime import datetime,timedelta
time = datetime.now()
deltatime = time - timedelta(days=1)
if (synctime == "hours"):
	deltatime = time - timedelta(seconds=3600)
if (synctime == "days"):
	deltatime = time - timedelta(days=1)
if (synctime == "weeks"):
	deltatime = time - timedelta(days=7)
if (synctime == "months"):
	deltatime = time - timedelta(days=30)
if (synctime == "years"):
	deltatime = time - timedelta(days=365)
uxtime = time.strftime("%s")
dltime = deltatime.strftime("%s")
#print "synctime="+synctime
#print "uxtime="+uxtime
#print "dltime="+dltime

i = 1
textdata = ''
csvdata = '"SID","SensorID","SensorName","SensorBus","SensorType","SensorGEO","TimeStamp","Data"\n'

cursor.execute("SELECT * FROM sensors_info WHERE sensor_state = 'on' ORDER BY sid ASC")
row = cursor.fetchall()
for row in cursor:
        if (row[4] == "on" and row[5] == "yes"):
                #print "ID="+str(sensor_id)+",Temp="+str(main_temp)+""
                cursor.execute("SELECT * FROM sensors_data WHERE sid = "+str(row[0])+" AND timestamp >= "+dltime+"")
                #cursor.execute("SELECT * FROM sensors_data WHERE sid = "+str(row[0])+"")
		entry = cursor.fetchall()
		for entry in cursor:
			csvdata = csvdata + ""+str(row[0])+",\""+str(row[1])+"\",\""+str(row[2])+"\",\""+str(row[3])+"\",\""+str(row[6])+"\",\""+str(row[9])+"\","+str(entry[2])+",\""+str(entry[3])+"\"\n"
        		continue
        i = i + 1
        continue

csvfilename = ''
if (server_hostname != ''):
	csvfilename = server_hostname
else:
	csvfilename = 'sensiplicity'
localpath = "/tmp/"+str(csvfilename)+"_"+str(uxtime)+".csv"
f = open(localpath, 'w')
print >> f, csvdata 
f.close()


if (usb_state == "enabled"):
	os.system("/opt/sensiplicity/bin/remote_usb.py -i '"+str(localpath)+"'")


if (ssh_state == "enabled"):
	os.system("/opt/sensiplicity/bin/remote_ssh_server.exp '"+str(host)+"' '"+str(username)+"' '"+str(password)+"' '"+str(remotepath)+"' '"+str(port)+"' '"+str(localpath)+"'")



os.system("/bin/rm "+localpath)

def rserver_store():
        cursor.execute("SELECT value FROM sensors_system WHERE name = 'rserver_store'")
        row = cursor.fetchone()
        if row:
                value = row[0]

        return value

storetime = rserver_store()

storedelta = time - timedelta(days=1)
if (storetime == "hours"):
        storedelta = time - timedelta(seconds=3600)
if (storetime == "days"):
        storedelta = time - timedelta(days=1)
if (storetime == "weeks"):
        storedelta = time - timedelta(days=7)
if (storetime == "months"):
        storedelta = time - timedelta(days=30)
if (storetime == "years"):
        storedelta = time - timedelta(days=365)
uxtime = time.strftime("%s")
sttime = storedelta.strftime("%s")
#print "storetime="+storetime
#print "uxtime="+uxtime
#print "sttime="+sttime

cursor.execute("DELETE FROM `sensors`.`sensors_data` WHERE `sensors_data`.`timestamp` < "+str(sttime)+"")
db.commit()

cursor.close ()
db.close()

commands.getoutput("/bin/rm ./c")
sys.exit()