def updateDevice(self): dao=DAOClass() sql="select * from devices where owner="+str(self.owner)+" and name='"+self.name+"'" print(sql) rows=dao.getData(sql) print(rows) if(rows==[]): sql="update devices set name='"+self.name+"' where id='"+self.id+"'" print(sql) r=dao.updateData(sql) if(r==True): return r else: msg="Database Error" print(msg) return msg elif(rows==None): msg="Database Error" print(msg) return msg else: msg="Another Device having same name found." print(msg) return msg
def addDevice(self):
dao=DAOClass()
sql="select * from devices where owner="+str(self.owner)+" and name='"+self.name+"'"
print(sql)
rows=dao.getData(sql)
print(rows)
if(rows==[]):
sql="insert into devices(id,name,state,owner,authtoken) values('"+self.id+"','"+self.name+"',"+str(int(self.state))+","+str(self.owner)+",'"+self.auth_token+"')"
print(sql)
r=dao.updateData(sql)
if(r==True):
return r
else:
msg="Database Error"
print(msg)
return msg
elif(rows==None):
msg="Database Error"
print(msg)
return msg
else:
msg="Another Device having same name found."
print(msg)
return msg
def getAllSettings(cls,user_id): dao=DAOClass() sql="select * from usersettings where user_id="+str(user_id) print(sql) rows=dao.getData(sql) if(rows==None or rows==[]): return False else: return [cls.rowtoObj(row) for row in rows]
def getSetting(cls,user_id,setting): dao=DAOClass() sql="select * from usersettings where user_id="+str(user_id)+" and setting='"+setting+"'" print(sql) rows=dao.getData(sql) if(rows==None or rows==[]): return False else: return cls.rowtoObj(rows[0])
def getBillingPeriodDates(cls,user_id,billingcycle): dao=DAOClass() sql="select svalue+INTERVAL 1 DAY,svalue+INTERVAL "+str(billingcycle)+" DAY \ from usersettings where user_id="+str(user_id)+" and setting='lastbilldate'" print(sql) rows=dao.getData(sql) if(rows==None or rows==[]): return False else: return rows[0][0],rows[0][1]
def addSession(self):
dao=DAOClass()
sql="insert into sessions(device_id,start_time) values('"+self.deviceid+"',now())"
r=dao.updateData(sql)
if(r==True):
sql="select max(id) from sessions where device_id='"+self.deviceid+"' and end_time is NULL"
rows=dao.getData(sql)
if(rows==None):
return "Database Error"
else:
self.id=rows[0][0]
return int(self.id)
else:
return "Database Error"
def updateLastBillDate(cls,user_id): dao=DAOClass() sql="select svalue from usersettings where user_id="+user_id+" and setting='billingcycle'" print(sql) billingcycle=dao.getData(sql)[0][0] print(billingcycle) if(billingcycle==None): return False sql="update usersettings set svalue=svalue+INTERVAL "+billingcycle+" DAY\ where user_id="+user_id+" and setting='lastbilldate' \ and now()>svalue+INTERVAL "+billingcycle+" DAY" print(sql) r=dao.updateData(sql) return r
def getEnergyConsumedPerDeviceByOwner(cls,owner,startdate=None,enddate=None,lastbilldate=None):
def rowtoDict(row):
r={}
r['id']=row[0]
r['name']=row[1]
r['energyc']=row[2]
return r
dao=DAOClass()
if(lastbilldate!=None):
sql="select device_id,name,sum(energy_consumed) as esum \
from sessions,devices \
where sessions.device_id=devices.id \
and start_time between '"+lastbilldate+"'+INTERVAL 1 DAY and now() \
group by device_id \
having device_id in (select id from devices where owner="+str(owner)+") \
and esum is not null"
elif(startdate!=None and enddate!=None and lastbilldate==None):
sql="select device_id,name,sum(energy_consumed) as esum \
from sessions,devices \
where sessions.device_id=devices.id \
and start_time between '"+startdate+"' and '"+enddate+"' \
group by device_id \
having device_id in (select id from devices where owner="+str(owner)+") \
and esum is not null"
else:
msg="Query Error"
print(msg)
return msg
print(sql)
rows=dao.getData(sql)
print(rows)
if(rows==None):
msg="Database Error"
print(msg)
return msg
elif (rows==[]):
msg="No Sessions of any Device Found!!!"
print(msg)
return msg
else:
r=[rowtoDict(row) for row in rows]
print(r)
return r
def getSessionById(cls,id1): dao=DAOClass() sql="select * from sessions where id="+id1 print(sql) rows=dao.getData(sql) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="Session Not Found" print(msg) return msg else: r=cls.rowtoObj(rows[0]) print(r) return r
def getNotificationsByUser(cls,user_id): dao=DAOClass() sql="select * from notifications where user_id="+str(user_id) print(sql) rows=dao.getData(sql) print(rows) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="No Notifications Found" print(msg) return msg else: r=[cls.rowtoObj(row) for row in rows] print(r) return r
def getSessionsByDevice(cls,device_id): dao=DAOClass() sql="select * from sessions where device_id='"+device_id+"' and end_time is NOT NULL" print(sql) rows=dao.getData(sql) print(rows) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="No Sessions Found" print(msg) return msg else: r=[cls.rowtoObj(row) for row in rows] print(r) return r
def getUserByEmail(cls,email1):
dao=DAOClass()
sql="select * from users where email='"+email1+"'"
print("getUserByEmail: "+sql)
rows=dao.getData(sql)
if(rows==None):
msg="Database Error"
print(msg)
return msg
elif (rows==[]):
msg="User Not Found"
print(msg)
return msg
else:
#r=[cls.rowtoDict(row) for row in rows]
r=cls.rowtoObj(rows[0])
print(r)
return r
def getUserById(cls,id1):
dao=DAOClass()
sql="select * from users where id="+str(id1)
print("getUserById: "+sql)
rows=dao.getData(sql)
if(rows==None):
msg="Database Error"
print(msg)
return msg
elif (rows==[]):
msg="User Not Found"
print(msg)
return msg
else:
#r=[cls.rowtoDict(row) for row in rows]
r=cls.rowtoObj(rows[0])
print(r)
return r
def getDevicesByOwner(cls,owner1): dao=DAOClass() sql="select * from devices where owner="+str(owner1) print(sql) rows=dao.getData(sql) print(rows) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="No Devices Found" print(msg) return msg else: r=[cls.rowtoObj(row) for row in rows] print(r) return r
def getDeviceById(cls,id1): dao=DAOClass() sql="select * from devices where id='"+id1+"'" print(sql) rows=dao.getData(sql) print(rows) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="Device Not Found" print(msg) return msg else: r=cls.rowtoObj(rows[0]) print(r) return r
def getNotificationsByUserIdAndDates(cls,user_id,startdate,enddate): dao=DAOClass() sql="select * from notifications where user_id="+user_id+" \ and time between '"+startdate+"' and '"+enddate+"'+INTERVAL 1 DAY" print(sql) rows=dao.getData(sql) print(rows) if(rows==None): msg="Database Error" print(msg) return msg elif (rows==[]): msg="No Notifications Found" print(msg) return msg else: r=[cls.rowtoObj(row) for row in rows] print(r) return r