def checkoutSingleRoom(self, id, rid, income):
"""个人结账"""
data = {"success": False, "message": ""}
conn, cursor = database()
cursor.execute("SELECT state FROM roomstate WHERE uid=%s AND rid=%s",
(self.uid, rid))
result = cursor.fetchone()
if result[0]:
SQL = """UPDATE roomcheck SET price='{i}',`user`='{ur}' WHERE uid = '{u}' AND rid = '{r}' AND sdate = (SELECT * FROM (SELECT sdate FROM roomcheck WHERE uid = '{u}' AND rid = '{r}' ORDER BY sdate DESC LIMIT 1) AS t)""".format(
u=self.uid, r=rid, ur=self.user, i=income)
cursor.execute(SQL)
cursor.execute(
"UPDATE roomstate SET state=0,roomername='',roomertel='',remark='' WHERE uid=%s AND rid=%s",
(self.uid, rid))
conn.commit()
data["success"] = True
data["message"] = "结账成功"
cursor.close()
conn.close()
else:
data["message"] = "结账失败,该房间不是未完结状态"
cursor.close()
conn.close()
return json.dumps(data, ensure_ascii=False)
def financeData(self, sdate, edate):
"""财务报表查看"""
data = {"success": True, "totalincome": 0, "data": []}
sTime = datetime.datetime.strptime(sdate, '%Y-%m-%d')
eTime = datetime.datetime.strptime(edate, '%Y-%m-%d')
totalincome = 0
conn, cursor = database()
cursor.execute(
"SELECT * FROM roomcheck WHERE uid=%s AND edate BETWEEN %s AND %s AND price IS NOT NULL ",
(self.uid, sTime, eTime))
result = cursor.fetchall()
for roomdata in result:
data["data"].append({
"rid": roomdata[1],
"sdate": roomdata[2],
"edate": roomdata[3],
"income": roomdata[9],
"user": roomdata[4],
"roomername": roomdata[6],
"roomertel": roomdata[7]
})
totalincome += roomdata[9]
data["totalincome"] = int(totalincome)
cursor.close()
conn.close()
return json.dumps(data, ensure_ascii=False, cls=CJsonEncoder)
def checkoutTeamRoom(self, id, rid, income):
# 团队结账
data = {"success": False, "message": ""}
conn, cursor = database()
cursor.execute(
"select state from roomstate where uid=%s and rid in %s",
(self.uid, rid))
result = cursor.fetchall()
judge = 0
for state in result:
if state[0] == 0:
judge = 1
break
if judge == 0:
income = income / len(id)
for nid in id:
cursor.execute(
"update roomcheck set price=%s,user=%s where id=%s",
(income, self.user, int(nid)))
for nrid in rid:
cursor.execute(
"update roomstate set state=0,roomername='',roomertel='',remark='' where uid=%s and rid=%s",
(self.uid, nrid))
conn.commit()
data["success"] = True
data["message"] = "结账成功"
cursor.close()
conn.close()
else:
data["message"] = "结账失败,房间中" + str(rid) + "有的不是未完结状态"
cursor.close()
conn.close()
return json.dumps(data, ensure_ascii=False)
def setUser(self, user, password):
"""新增用户"""
data = {"success": False, "message": ""}
conn, cursor = database()
try:
cursor.execute("INSERT INTO users VALUES (%s,%s,%s,2)",
(self.uid, user, password))
conn.commit()
data["success"] = True
data[
"message"] = "成功新增用户：" + user + ",密码：" + password + "。请牢记账号密码！"
cursor.close()
conn.close()
except:
data["message"] = "新增失败，请检查贵公司是否已存在该用户"
cursor.close()
conn.close()
return json.dumps(data, ensure_ascii=False)
def availableRoom(self, sdate, edate):
"""时间段内可用的房间"""
data = {"success": True, "message": "", "data": []}
sdate = datetime.datetime.strptime(
sdate, '%Y-%m-%d') + datetime.timedelta(hours=15)
edate = datetime.datetime.strptime(
edate, '%Y-%m-%d') + datetime.timedelta(hours=11)
conn, cursor = database()
inavailableroom = []
# 判断该时间段因登记不可用的房间
cursor.execute(
"select rid from roomcheck where uid=%s and ((sdate between %s and %s) or (edate between %s and %s) or (sdate<%s and edate>%s))",
(self.uid, sdate, edate, sdate, edate, sdate, edate))
result = cursor.fetchall()
for room in result:
if room[0] not in inavailableroom:
inavailableroom.append(room[0])
# 判断该时间段因预定不可用的房间
cursor.execute(
"select rid from roombooking where uid=%s and ((sdate between %s and %s) or (edate between %s and %s) or (sdate<%s and edate>%s))",
(self.uid, sdate, edate, sdate, edate, sdate, edate))
result = cursor.fetchall()
for room in result:
if room[0] not in inavailableroom:
inavailableroom.append(room[0])
print inavailableroom
cursor.execute("select * from roominfo where uid=%s", self.uid)
result = cursor.fetchall()
for room in result:
if room[1] in inavailableroom:
pass
# data["data"].append({"rid":room[1],"roomtype":room[2],"roomprice":room[3],"identity":0})
else:
data["data"].append({
"rid": room[1],
"roomtype": room[2],
"roomprice": room[3],
"identity": 1
})
return json.dumps(data, ensure_ascii=False)
def todayRoom(self):
"""今日客房情况查询"""
data = {"success": False, "message": "", "data": []}
conn, cursor = database()
cursor.execute(
"select i.rid,i.roomprice,i.roomtype,i.remark,s.state from roominfo as i left join roomstate as s on i.uid=s.uid and s.rid=i.rid where i.uid=%s",
self.uid)
result = cursor.fetchall()
for room in result:
data["data"].append({
"rid": room[0],
"roomtype": room[2],
"roomprice": room[1],
"remark": room[3],
"state": room[4]
})
data["success"] = True
data["message"] = "查询成功"
return json.dumps(data, ensure_ascii=False)
def getUser(self):
"""查询当前用户"""
data = {"success": False, "message": "", "data": []}
conn, cursor = database()
try:
cursor.execute(
"SELECT `user`,password FROM users WHERE roles=2 AND uid=%s",
(self.uid))
result = cursor.fetchall()
for user in result:
data["data"].append({"user": user[0], "password": user[1]})
data["success"] = True
data["message"] = "成功"
cursor.close()
conn.close()
except Exception as e:
data["message"] = "查询失败" + str(e)
cursor.close()
conn.close()
return json.dumps(data, ensure_ascii=False)
def bookTeamRoom(self, rid, sdate, edate, roomername, roomertel, remark):
"""团队预约函数"""
data = {"success": False, "message": ""}
sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d') + datetime.timedelta(hours=14)
edate = datetime.datetime.strptime(edate, '%Y-%m-%d') + datetime.timedelta(hours=12)
if edate > sdate:
conn, cursor = database()
# 判断该房间该是简单是否已经有预约
cursor.execute(
"select * from roombooking where uid=%s and rid in %s and ((sdate between %s and %s) or (edate between %s and %s) or (sdate<%s and edate>%s))",
(self.uid, rid, sdate, edate, sdate, edate, sdate, edate))
judge = cursor.fetchall()
if judge:
data["message"] = "预约失败，请检查房间" + str(rid) + "中的已预约情况"
cursor.close()
conn.close()
else:
# 判断该房间该是简单是否已经有住户
cursor.execute(
"select * from roomcheck where uid=%s and rid in %s and ((sdate between %s and %s) or (edate between %s and %s) or (sdate<%s and edate>%s))",
(self.uid, rid, sdate, edate, sdate, edate, sdate, edate))
judge = cursor.fetchall()
if not judge:
for ridnum in rid:
cursor.execute(
"insert into roombooking(uid,rid,sdate,edate,user,remark,roomername,roomertel) values(%s,%s,%s,%s,%s,%s,%s,%s)",
(self.uid, ridnum, sdate, edate, self.user, remark, roomername, roomertel))
conn.commit()
data["success"] = True
data["message"] = "预约成功，时间：" + str(sdate) + "-" + str(edate) + "。房间：" + str(rid)
cursor.close()
conn.close()
else:
data["message"] = "预约失败，请检查房间" + str(rid) + "中的已登记情况"
cursor.close()
conn.close()
else:
data["message"] = "起始时间不可大于终止时间"
return json.dumps(data, ensure_ascii=False)
def roomCheck(self):
"""登记客房查询"""
data = {"success": False, "message": "", "data": []}
conn, cursor = database()
cursor.execute(
"SELECT * FROM roomcheck WHERE uid=%s AND price is not null AND rid in (SELECT rid FROM roomstate WHERE state = 1)",
self.uid)
result = cursor.fetchall()
for roomer in result:
data["data"].append({
"id": roomer[0],
"remark": roomer[5],
"rid": roomer[1],
"roomername": roomer[6],
"roomertel": roomer[7],
"sdate": roomer[2],
"edate": roomer[3]
})
data["success"] = True
data["message"] = "查询成功"
return json.dumps(data, ensure_ascii=False, cls=CJsonEncoder)