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 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 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 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