def getandsaveDataWeekly(db, x, s, e, w):
cur = db.cursor()
sql = "SELECT socket_id, watt_cons FROM power_daily WHERE socket_id=%s AND date>=%s AND date<=%s"
cur.execute(sql, (x, s, e))
results = cur.fetchall()
f = 0.0000
for row in results:
f = f + float(row[1])
cur.close()
curinsert = db.cursor()
sql = "INSERT INTO power_weekly(socket_id, watt_cons, date_from, date_to, week_number) VALUES (%s, %s, %s, %s, %s)"
try:
curinsert.execute(sql, (x, f, s, e, w))
db.commit()
except:
db.rollback()
def checkLastSavedSocketWeekly(db, x, w):
cur = db.cursor()
sql = "SELECT date_from, date_to, week_number FROM power_weekly WHERE socket_id=%s AND week_number<%s ORDER BY week_number DESC"
cur.execute(sql, (x, w))
row = cur.fetchone()
cur.close()
return row
def getForgotNow(db, now):
cur = db.cursor()
sql = "SELECT id FROM forgot_table WHERE time_expire='"+now+"'"
cur.execute(sql)
result = cur.fetchall()
count = cur.rowcount
cur.close()
return (count, result)
def schedNow(db, now):
cur = db.cursor()
sql = "SELECT id, socket_id, date_time_sched, action, description FROM schedule WHERE date_time_sched='"+now +"' AND state='READY'"
cur.execute(sql)
result = cur.fetchall()
count = cur.rowcount
cur.close()
return (count, result)
def checkLastSavedSocket(db, x):
cur = db.cursor()
sql = "SELECT date FROM power_daily WHERE socket_id=%s AND date<%s ORDER BY date DESC"
d = str(dateYesterday())
cur.execute(sql, (x, d))
row = cur.fetchone()
cur.close()
return row[0]
def checkDataWeekly(db, x, s, e, w):
cur = db.cursor()
sql = "SELECT week_number FROM power_weekly WHERE socket_id=%s AND date_from=%s AND date_to=%s AND week_number=%s"
cur.execute(sql, (x, s, e, w))
count = cur.rowcount
cur.close()
if count >= 1:
return False
else:
return True
def getWatt(db, n, t, s):
cur = db.cursor()
sql = "SELECT watt_cons FROM power_con WHERE date_time>=%s AND date_time<%s AND socket_id=%s"
cur.execute(sql, (n, t, s))
count = cur.rowcount
results = cur.fetchall()
cur.close()
plist = []
for row in results:
plist.append(row[0])
return (count, plist)
def checkDataDaily(db, x):
cur = db.cursor()
sql = "SELECT date FROM power_daily WHERE socket_id=%s AND date=%s"
d = str(dateYesterday())
cur.execute(sql, (x, d))
count = cur.rowcount
cur.close()
if count >= 1:
return False
else:
return True
def saveDate(db, x, d):
cur = db.cursor()
sql = "SELECT socket_id, watt_cons FROM power_con WHERE CAST(date_time as DATE)=%s AND socket_id=%s"
cur.execute(sql, (d, x))
count = cur.rowcount
results = cur.fetchall()
f = 0.000
for row in results:
f = f + float(row[1])
if count != 0:
f = (f / count * ((count / 60) / 60))
cur.close()
curinsert = db.cursor()
sql = "INSERT INTO power_daily(socket_id, watt_cons, date) VALUES (%s, %s, %s)"
try:
curinsert.execute(sql, (x, f, d))
db.commit()
except Exception as e:
db.rollback()
curinsert.close()
def saveAppliance(db, t, s):
cur = db.cursor()
sql = """UPDATE socket SET appliance=%s WHERE id=%s"""
cur.execute(sql, (t, s))
db.commit()
def deleteForgot(db, id):
cur = db.cursor()
sql = "DELETE FROM forgot_table WHERE id="+ str(id)
cur.execute(sql)
db.commit()
cur.close()
def deleteSched(db, pid, socket_id):
cur = db.cursor()
sql = "DELETE FROM schedule WHERE id=%s AND socket_id=%s"
cur.execute(sql, (pid, socket_id))
db.commit()
cur.close()