def clearConnec(self):
sql = "update connection set is_alive=0"
db_lock.acquire()
cursor.execute(sql)
# 互斥访问，预防并发访问时游标被占用，结果出错
db.commit()
db_lock.release() # 释放锁
def insertItem(self):
data = self.RoomNo
sql = "select room_no,switch_cnt from servent_stat where room_no=%d and date=CURRENT_DATE " % data
# 互斥访问，预防并发访问时游标被占用，结果出错
print("database??", sql)
db_lock.acquire()
cursor.execute(sql)
res = cursor.fetchall()
print(res)
if len(res) == 0:
now = datetime.datetime.now().strftime('%Y-%m-%d')
sql = "insert into servent_stat values (%d,%d,%f,%d,%f,%f,'%s')"
data = (self.RoomNo, self.switchcnt, self.temp, self.velocity,
self.cost, self.energy, now)
cursor.execute(sql % data)
db.commit()
print("插入数据")
else:
#已存在，由于从机被重新初始化，除了开机次数和房间号、日期、消费，剩下字段都更新
self.switchcnt = res[0][1]
sql = "update servent_stat set wind_level=%d,temp=%f where room_no='%d' and date=curdate()"\
%(self.velocity,self.temp,self.RoomNo)
cursor.execute(sql)
db.commit()
print("更新数据")
db_lock.release() # 释放锁
def setConnec(self):
self.connec = []
sql = "select room_no from connection where is_alive=1"
db_lock.acquire()
cursor.execute(sql)
# 互斥访问，预防并发访问时游标被占用，结果出错
for row in cursor.fetchall():
#print(row)
self.connec.append(row[0])
db_lock.release() # 释放锁
def addCost(self, delmonye, deleng):
self.cost += delmonye
self.energy += deleng
db_lock.acquire()
sql = "UPDATE servent_stat SET cost=cost+%f, energy=energy+%f WHERE room_no='%d' and date=curdate()"\
% (delmonye,deleng,self.RoomNo)
print(sql)
cursor.execute(sql)
db.commit()
db_lock.release()
def newRequest(self, roomNo, temp, windLevel):
time = datetime.now()
#终止上个请求
self.endRequest(roomNo, temp, windLevel)
sqlquery = "insert into request(room_no,s_temp,s_wind_level,s_time,cost) values(%s,%s,%s,'%s',0)" % (
str(roomNo), str(temp), str(windLevel), str(time))
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
db_lock.release()
def setTemp(self, temp):
self.temp = temp
# 互斥访问，预防并发访问时游标被占用，结果出错
print("change temp of room %d" % (self.RoomNo))
db_lock.acquire()
sql = "update servent_stat set temp=%f where room_no='%d' and date=curdate()" \
% (self.temp, self.RoomNo)
cursor.execute(sql)
db.commit()
db_lock.release() # 释放锁
print("change temp of room %d, complete" % (self.RoomNo))
def setID(self):
sql = "SELECT name,is_alive FROM connection where room_no='%d'"
data = self.RoomNo
# 互斥访问，预防并发访问时游标被占用，结果出错
db_lock.acquire()
cursor.execute(sql % data)
for row in cursor.fetchall():
self.ID = row[0]
self.isalive = row[1]
print(row)
db_lock.release() # 释放锁
def isdie(self):
self.isalive = 0
date = datetime.datetime.now()
start = datetime.datetime(date.year, date.month, date.day, 0, 0, 0)
end = datetime.datetime(date.year, date.month, date.day, 23, 59, 59)
db_lock.acquire()
sql = "UPDATE connection SET is_alive=0 WHERE room_no=%d and login_time between '%s' and '%s'" % (
self.RoomNo, start, end)
cursor.execute(sql)
db.commit()
db_lock.release()
def insertmes(self, roomNo, name, pwd):
sql = "insert into usr(roomNo,name,pwd) value (%d,'%s','%s')" % (
int(roomNo), name, pwd)
print(sql)
# 互斥访问，预防并发访问时游标被占用，结果出错
db_lock.acquire()
cursor.execute(sql)
db_lock.release() #释放锁
db.commit()
print("插入数据")
return '登记成功'
def getRoomNo(self):
sqlquery = "select distinct room_no from request order by room_no"
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
room = []
while (row != None):
room.append(row[0])
row = cursor.fetchone()
db_lock.release()
return room
def getRequest(self, roomNo, sdate, edate):
sqlquery = "SELECT * FROM REQUEST WHERE ROOM_NO = %s AND S_TIME BETWEEN '%s 00:00:00' AND '%s 23:59:59'" % (
str(roomNo), sdate, edate)
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
request = []
while (row != None):
request.append(row)
row = cursor.fetchone()
db_lock.release()
return request
def addSwitch_cnt(self):
self.isdie()
self.cost = 0.00
self.energy = 0
sql = "update servent_stat set switch_cnt = switch_cnt+1 where room_no='%d' and date=curdate()" % (
self.RoomNo)
# 互斥访问，预防并发访问时游标被占用，结果出错
print(sql)
db_lock.acquire()
cursor.execute(sql)
db.commit()
db_lock.release() # 释放锁
print("关机次数更新完成")
def setWindLev(self, velocity):
self.velocity = velocity
# 互斥访问，预防并发访问时游标被占用，结果出错
print("change velocity of room %d as %d" %
(self.RoomNo, self.velocity))
db_lock.acquire()
sql = "update servent_stat set wind_level=%d where room_no='%d' and date=curdate()" \
% (self.velocity, self.RoomNo)
cursor.execute(sql)
db.commit()
db_lock.release() # 释放锁
print("change velocity of room %d complete" % (self.RoomNo))
def getLatestRequest(self, roomNo):
sqlquery = "select max(a.s_time) from (select * from request where room_no = %s) as a" % (
str(roomNo))
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
db_lock.release()
time = ""
if (row[0] == None):
print("no request from this room")
else:
time = str(row[0])
return time
def getSwitchCnt(self, roomNo, sdate, edate):
sqlquery = "SELECT SUM(SWITCH_CNT) FROM SERVENT_STAT WHERE ROOM_NO = %s AND DATE BETWEEN '%s' AND '%s' GROUP BY ROOM_NO" % (
str(roomNo), sdate, edate)
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
db_lock.release()
switchCnt = 0
if (row is None):
print("No such room")
else:
switchCnt = int(row[0])
return switchCnt
def setOther(self):
if self.isalive == 0:
self.temp = 0.00
self.velocity = 0
else:
sql = "SELECT temp,wind_level FROM servent_stat where room_no='%d' and date=curdate()"
data = self.RoomNo
# 互斥访问，预防并发访问时游标被占用，结果出错
db_lock.acquire()
cursor.execute(sql % data)
for row in cursor.fetchall():
self.temp = row[0]
self.velocity = row[1]
print(row)
db_lock.release() # 释放锁
def getCost(self, roomNo, sdate, edate):
sqlquery = "SELECT SUM(a.COST) FROM (select cost from servent_stat where room_no = %s and date between '%s' and '%s') as a" % (
str(roomNo), sdate, edate)
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
print(row)
db_lock.release()
cost = 0
if (row[0] is None):
print("No such room")
else:
cost = row[0]
return cost
def select(self, id, pwd):
sql = "SELECT * FROM " + self.__tablename__ + " WHERE id='%s' and pwd='%s'" % (
id, pwd)
print(sql)
# 互斥访问，预防并发访问时游标被占用，结果出错
db_lock.acquire()
cursor.execute(sql)
row = cursor.fetchone()
db_lock.release() #释放锁
print(row)
res = True
message = '登录成功'
if row == None: #没查到了管理员号
res = False
message = '账号或密码错误，请检查后重新登录'
return res, message
def costUpdate(self, roomNo, addCost):
sqlquery = "select max(s_time) from request where room_no = %s group by room_no" % (
str(roomNo))
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
db_lock.release()
if (row == None):
print("No request from this room")
else:
time = row[0]
sqlquery = "update request set cost = cost + %s where s_time = '%s' and room_no = %s" % (
str(addCost), str(time), str(roomNo))
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
db_lock.release()
def endRequest(self, roomNo, temp, windLevel):
sqlquery = "select max(s_time) from request where room_no = %s group by room_no" % (
str(roomNo))
print(sqlquery)
db_lock.acquire()
cursor.execute(sqlquery)
row = cursor.fetchone()
db_lock.release()
if (row == None):
print("No request from this room")
else:
time = row[0]
endtime = datetime.now()
sqlquery = "update request set e_time = '%s',e_temp = %s,e_wind_level = %s where room_no = %s and s_time = '%s'" % (
str(endtime), str(temp), str(windLevel), str(roomNo),
str(time))
db_lock.acquire()
cursor.execute(sqlquery)
db_lock.release()
def Check(self, Room_No, Name, Password):
print("check:", Room_No, Name, Password)
#检查房间号/用户名/身份证是否含有非法字符
numMatch = re.match(r'^[0-9][0-9]*$', Room_No, re.S | re.I)
idMatch = re.match(r'.*[\'\"\\].*', Name, re.S | re.I)
pwdMatch = re.match(r'.*[\'\"\\].*', Password, re.S | re.I)
#含有非法字符则弹回去，防止崩溃
if numMatch == None:
self.log_sig.emit(Room_No, 0, Name, Password)
return False
if idMatch != None:
self.log_sig.emit(Room_No, 0, Name, Password)
return False
elif pwdMatch != None:
self.log_sig.emit(Room_No, 0, Name, Password)
return False
db_lock.acquire()
sql = "SELECT * FROM " + self.__tablename__ + " WHERE roomNo=%d and name='%s' and pwd='%s'" % (
int(Room_No), Name, Password)
print(sql)
cursor.execute(sql)
row = cursor.fetchone()
db_lock.release()
print(row)
if row == None:
print("not connected")
self.log_sig.emit(Room_No, 0, Name, Password)
return False
#return False
else:
print("connected")
date = datetime.now()
start = datetime(date.year, date.month, date.day, 0, 0, 0)
end = datetime(date.year, date.month, date.day, 23, 59, 59)
db_lock.acquire()
sql = "SELECT is_alive FROM connection WHERE room_no=%d and login_time between '%s' and '%s'" % (
int(Room_No), start, end)
cursor.execute(sql)
row = cursor.fetchone()
db_lock.release()
if row == None:
self.log_sig.emit(Room_No, 1, Name, Password)
print("new room")
sql = "INSERT INTO connection values(%d,'%s','%s','%s',1)" % (
int(Room_No), Name, Password, date)
print(sql)
db_lock.acquire()
cursor.execute(sql)
db.commit()
db_lock.release()
print("insert done")
return True
elif row[0] == 0:
self.log_sig.emit(Room_No, 1, Name, Password)
print("room exists and not connect")
sql = "UPDATE connection SET is_alive=1 WHERE room_no=%d and name='%s' and pwd='%s'and " \
"login_time between '%s' and '%s'" % (int(Room_No), Name, Password, start, end)
print(sql)
db_lock.acquire()
cursor.execute(sql)
db.commit()
db_lock.release()
print("update done")
return True
else: #(房间已经被登录)
self.log_sig.emit(Room_No, 0, Name, Password)
return False
