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)