def deleteSessionsByDevice(cls,device_id): dao=DAOClass() sql="delete from sessions where device_id='"+device_id+"'" print(sql) r=dao.updateData(sql) print(r) return r
def addUser(self):
u=User.getUserByEmail(self.email)
if(u=="User Not Found"):
dao=DAOClass()
sql="insert into users(name,email,pwd) values('"+self.name+"','"+self.email+"','"+self.pwd+"')"
print("addUser: "******"""
if(isinstance(u,User)):
return u.getid()
else:
return u
"""
else:
return "Database Error"
elif(u=="Database Error"):
return "Database Error"
else:
msg="User Already Exists"
print(msg)
return msg
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 deleteSessionById(cls,id1): dao=DAOClass() sql="delete from sessions where id="+str(id1) print(sql) r=dao.updateData(sql) print(r) return r
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 addNotification(self):
dao=DAOClass()
sql="insert into notifications(user_id,time,type,msg,readn) \
values("+str(self.user_id)+",now(),'"+self.type+"','"+self.msg+"',0)"
print(sql)
r=dao.updateData(sql)
return r
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 deleteDeviceById(cls,id1): dao=DAOClass() sql="delete from sessions where device_id='"+id1+"'" print(sql) r=dao.updateData(sql) sql="delete from devices where id='"+id1+"'" print(sql) r=dao.updateData(sql) print(r) return r
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 updateUserById(self):
dao=DAOClass()
sql="update users set name='"+self.name+"', email='"+self.email+"', pwd='"+self.pwd+"' where id="+self.id
print("updateUserById: "+sql)
r=dao.updateData(sql)
if(r==True):
msg="User Data Updated"
print(msg)
return msg
else:
msg="Database Error"
print(msg)
return msg
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 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 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 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 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 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
def updateSession(self): dao=DAOClass() sql="update sessions set end_time=now(), energy_consumed="+str(self.energy_consumed)+" where id="+str(self.id) print(sql) r=dao.updateData(sql) return r
def readNotification(cls,nid): dao=DAOClass() sql="update notifications set readn=1 where id="+str(nid) r=dao.updateData(sql) print(sql) return r
def addSetting(self):
dao=DAOClass()
sql="insert into usersettings values("+str(self.user_id)+",'"+self.setting+"','"+self.value+"')"
print(sql)
r=dao.updateData(sql)
return r
def updateDeviceStateById(cls,id1,state1): dao=DAOClass() sql="update devices set state="+str(int(state1))+" where id='"+id1+"'" print(sql) r=dao.updateData(sql) return r
def updateSetting(self): dao=DAOClass() sql="update usersettings set svalue='"+self.value+"' where user_id="+str(self.user_id)+" and setting='"+self.setting+"'" print(sql) r=dao.updateData(sql) return r