def count():
db, cursor = deal.connect2db()
sql = "SELECT count(*) from MOrder"
cursor.execute(sql)
db.commit()
ordernum = cursor.fetchone() # 总订单数
return ordernum
def search(type, content):
msg = ''
db, cursor = deal.connect2db()
if type == 1:
sql = "select * from Cinema where cname like '%{}%';".format(content)
cursor.execute(sql)
db.commit()
cinemaresultlist = cursor.fetchall()
if len(cinemaresultlist) != 0:
msg = 'done'
else:
msg = 'none'
return msg, cinemaresultlist
elif type == 2:
sql = "select * from Movie where movie like '%{}%';".format(content)
cursor.execute(sql)
db.commit()
movieresultlist = cursor.fetchall()
if len(movieresultlist) != 0:
msg = 'done'
else:
sql = "select * from Movie where showtime like '%{}%';".format(
re.sub("\D", "%", content))
cursor.execute(sql)
db.commit()
movieresultlist = cursor.fetchall()
if len(movieresultlist) != 0:
msg = 'done'
else:
msg = 'none'
return msg, movieresultlist
else:
msg = 'error'
return msg, ''
def getcinemanum():
db, cursor = deal.connect2db()
sql1 = "SELECT MAX(cinemaID) from Cinema"
cursor.execute(sql1)
db.commit()
cnum = cursor.fetchone() # 总影院数
return cnum
def getinfo(self):
db, cursor = deal.connect2db()
sql = "SELECT * from Cinema WHERE cinemaID = {}".format(self.cinemaID)
cursor.execute(sql)
db.commit()
cininfo = cursor.fetchone()
return cininfo
def cinemaNum():
db, cursor = deal.connect2db()
sql = "SELECT COUNT(*) FROM Cinema"
cursor.execute(sql)
db.commit()
res = cursor.fetchone()
return res[0]
def orderNum():
db, cursor = deal.connect2db()
sql = "SELECT COUNT(*) FROM MOrder"
cursor.execute(sql)
db.commit()
res = cursor.fetchone()
return res[0]
def getnamelist():
db, cursor = deal.connect2db()
sql = "SELECT name FROM Courier"
cursor.execute(sql)
db.commit()
deliverylist = cursor.fetchall()
return deliverylist
def updatecininfo():
if request.method == 'GET':
print('updatecininfo - GET')
cinname = request.args.get('cinname')
msg, cinemaID = admin.getcinemaID(cinname) # 负责影院的ID
c = cin.Cinema(cinemaID[0])
cininfo = c.getinfo()
return render_template('updatecininfo.html', res=cininfo)
elif request.method == 'POST':
print('updatecininfo - POST')
# cinadminname = request.form.get('cinadminname')
cname = request.form.get('cname')
caddr = request.form.get('caddr')
cphone = request.form.get('cphone')
acapacity = request.form.get('acapacity')
bcapacity = request.form.get('bcapacity')
# 更新
cin.updatecinema(cname, caddr, cphone, acapacity, bcapacity)
db, cursor = deal.connect2db()
sql1 = "SELECT CinAdmin.adminname FROM CinAdmin, Cinema WHERE cname='{}' AND CinAdmin.cinemaID = Cinema.cinemaID".format(cname)
cursor.execute(sql1)
db.commit()
res = cursor.fetchone()
cinadminname = res[0]
print(cinadminname)
return render_template("cinadminPage.html", messages="done", cinadminname=cinadminname)
def delivery():
if request.method == 'GET':
print('delivery - GET')
cinname = request.args.get('cinname')
print(cinname)
msg, cinemaID = admin.getcinemaID(cinname)
msg, res = morder.getorder(cinemaID[0])
deliverylist = courier.getnamelist()
return render_template('delivery.html', cinemaID=cinemaID[0], orderlist=res, deliverylist=deliverylist, messages=msg)
elif request.method == 'POST':
print('delivery - POST')
orderID = request.form.get('orderID')
couriername = request.form.get('couriername')
print("订单{}--送票员：{}".format(orderID, couriername))
db, cursor = deal.connect2db()
sql1 = "UPDATE MOrder SET courier = '{}' WHERE orderID = '{}'".format(couriername, orderID)
cursor.execute(sql1)
db.commit()
sql2 = "SELECT CinAdmin.adminname FROM CinAdmin, MOrder WHERE MOrder.orderID = '{}' AND CinAdmin.cinemaID = MOrder.cinemaID".format(orderID)
cursor.execute(sql2)
db.commit()
res = cursor.fetchone()
cinadminname = res[0]
print(cinadminname)
return render_template("cinadminPage.html", messages="done", cinadminname=cinadminname)
def CinemaList():
msg = ''
db, cursor = deal.connect2db()
sql = "SELECT * from Cinema"
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
if request.method == 'GET':
print('GET')
if len(res) != 0:
msg = "done"
print(msg)
return render_template('CinemaList.html', result=res, messages=msg)
else:
print("NULL")
msg = "none"
return render_template('CinemaList.html', messages=msg)
elif request.method == 'POST':
print('POST')
# TODO: msg为空，待解决
cinemaID = request.form.get('cinemaID')
# print(cinemaID)
print(msg)
return render_template('CinemaDetail.html' ,messages=msg, cinemaID=cinemaID)
def insertcinema(self):
msg = ''
db, cursor = deal.connect2db()
sql = "INSERT INTO Cinema VALUES ({}, '{}', '{}', '{}', '{}', {}, {})".format(\
self.cinemaID, self.cname, self.caddr, self.cphone, self.imagesrc, self.acapacity, self.bcapacity)
cursor.execute(sql)
db.commit()
msg = 'done'
return msg
def selectbymovie(self):
msg = ''
db, cursor = deal.connect2db()
sql = "SELECT * from Movie WHERE movie = '{}'".format(self.movie)
cursor.execute(sql)
db.commit()
movieinfo = cursor.fetchone()
msg = 'done'
return msg, movieinfo
def delivery(self, morderID, msg):
db, cursor = deal.connect2db()
if msg == 'done':
sql = "UPDATE MOrder SET isFinished = 1 WHERE courier = {}".format(
self.name)
elif msg == 'fail':
sql = "DELETE FROM MOrder WHERE morderID = {}".format(morderID)
cursor.execute(sql)
db.commit()
def assigncinadmin(self, cinemaID):
db, cursor = deal.connect2db()
# 分配电影院
sql = "UPDATE CinAdmin SET cinemaID = {} WHERE adminname = '{}'".format(cinemaID, self.adminname)
print(sql)
cursor.execute(sql)
db.commit()
msg = 'done'
return msg
def getavailablecin():
# 查询空闲电影院管理员
db, cursor = deal.connect2db()
sql = "SELECT * from CinAdmin WHERE cinemaID is NULL"
print(sql)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
reslen = len(res)
return res, reslen
def getcinemaID(cinname):
# 获取负责影院的ID
msg = ''
db, cursor = deal.connect2db()
sql = "SELECT cinemaID from CinAdmin WHERE adminname = '{}'".format(cinname)
cursor.execute(sql)
db.commit()
msg = 'done'
cinemaID = cursor.fetchone()
return msg, cinemaID
def selectbycinema(cinemaID):
msg = ''
db, cursor = deal.connect2db()
sql = "SELECT * from Movie WHERE cinemaID = {}".format(cinemaID)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
reslen = len(res)
msg = 'done'
return res, reslen
def insertmorder(self):
msg = ''
db, cursor = deal.connect2db()
sql = "INSERT into MOrder values ('{}', '{}', {}, '{}', '{}', '{}', '{}', {}, {}, '{}', '{}');".format(\
self.orderID, self.movie, int(self.cinemaID), self.seatrank, self.seatnum, self.phone, self.addr, 0,
self.cost, self.tansactiontime, self.courier)
print(sql)
cursor.execute(sql)
db.commit()
msg = 'order'
return msg
def selectcinema(self):
# 查找含有该影片的影院
msg = ''
db, cursor = deal.connect2db()
sql = "SELECT DISTINCT M.cinemaID, C.cname FROM MOVIE M, CINEMA C WHERE M.movie = '{}' AND M.cinemaID = C.cinemaID;".format(
self.movie)
print(sql)
cursor.execute(sql)
db.commit()
cinemaIDlist = cursor.fetchall()
return msg, cinemaIDlist
def insertmovie(self):
msg = ''
db, cursor = deal.connect2db()
sql = "INSERT into Movie values ('{}', {}, '{}', {}, '{}', '{}', '{}', {}, {} );".format(
self.movie, self.cinemaID, self.showtime, self.duration,
self.screenshot, self.intro, self.trailer, self.afare, self.bfare)
print(sql)
cursor.execute(sql)
db.commit()
print('Insert into Movie seccessfully!')
msg = 'done'
return msg
def getorder(cinemaID):
db, cursor = deal.connect2db()
sql = "SELECT * FROM MOrder WHERE courier = '' AND cinemaID = {}".format(
cinemaID)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
reslen = len(res)
if reslen == 0:
msg = "empty"
else:
msg = "have"
return msg, res
def getorderlist(couriername):
db, cursor = deal.connect2db()
sql = "SELECT * FROM morder WHERE isFinished = 0 AND courier = '{}'".format(
couriername)
print(sql)
cursor.execute(sql)
db.commit()
orderlist = cursor.fetchall()
if len(orderlist) == 0:
msg = 'empty'
else:
msg = 'have'
return msg, orderlist
def waitforassign():
db, cursor = deal.connect2db()
# 查询待分配电影院
sql = (
'select C.cinemaID, C.cname from cinema C WHERE C.cinemaID not in '
'(select distinct CA.cinemaID from CinAdmin CA WHERE CA.cinemaID is not NULL);'
)
print(sql)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
reslen = len(res)
return res, reslen
def popularcinema():
# 统计最受欢迎电影院模块
db, cursor = deal.connect2db()
# 返回前三条结果
sql = (
'SELECT DISTINCT O.cinemaID, COUNT(distinct(orderID)) as sales, C.cname, C.imagesrc '
'FROM MOrder O, Cinema C WHERE C.cinemaID = O.cinemaID Group BY O.cinemaID ORDER BY sales DESC limit 3;'
)
cursor.execute(sql)
db.commit()
topcinema = cursor.fetchall()
return topcinema
def popularmovie():
# 统计最受欢迎影片模块
db, cursor = deal.connect2db()
# sql1 = "SELECT O.movie, COUNT(orderID) as sales FROM MOrder O Group BY O.movie ORDER BY sales DESC;"
# 返回前三条结果
sql1 = (
'SELECT O.movie as topsales, COUNT(distinct(orderID)) as sales, M.showtime, M.screenshot, M.intro, M.trailer, M.bfare '
'FROM MOrder O, Movie M WHERE M.movie = O.movie Group BY O.movie ORDER BY sales DESC limit 3;'
)
cursor.execute(sql1)
db.commit()
toppopular = cursor.fetchall()
# 创建对象
return toppopular
def CourierLogin(self):
db, cursor = deal.connect2db()
sql = "SELECT * from COURIER where name = '{}' and password='******'".format(
self.name, self.password)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
num = 0
num = len(res)
# 如果存在该电配送员且密码正确
if num == 1:
print("登录成功！欢迎配送员！")
msg = "done3"
else:
print("您没有配送员权限或登录信息出错。")
msg = "fail3"
return msg
def MovieList():
msg = ''
# 连接数据库，默认数据库用户名root，密码空
db, cursor = deal.connect2db()
sql = "SELECT * from Movie"
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
if request.method == 'GET':
print('GET')
if len(res) != 0:
msg = "done"
print(msg)
return render_template('MovieList.html', result=res, messages=msg)
else:
print("NULL")
msg = "none"
return render_template('MovieList.html', messages=msg)
def SysAdminLogin(self):
db, cursor = deal.connect2db()
if self.adminname == '':
msg = "nameempty"
elif self.password == '':
msg = "pswempty"
else:
sql = "SELECT * from SYSADMIN where adminname = '{}' and password = '******'".format(self.adminname, self.password)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
num = len(res)
# 如果存在该管理员且密码正确
if num == 1:
print("登录成功！欢迎系统管理员！")
msg = "done1"
else:
print("您没有系统管理员权限或密码错误。")
msg = "fail1"
return msg
def CinAdminLogin(self):
db, cursor = deal.connect2db()
if self.adminname == '':
msg = "nameempty"
elif self.password == '':
msg = "pswempty"
else:
sql = "SELECT * from CINADMIN where adminname = '{}' and password='******'".format(self.adminname, self.password)
cursor.execute(sql)
db.commit()
res = cursor.fetchall()
num = 0
num = len(res)
# 如果存在该电影院管理员且密码正确
if num == 1:
print("登录成功！欢迎电影院管理员！")
msg = "done2"
else:
print("您没有电影院管理员权限或登录信息出错。")
msg = "fail2"
return msg
def updatecinema(cname, caddr, cphone, acapacity, bcapacity):
db, cursor = deal.connect2db()
sql = "UPDATE Cinema SET caddr='{}', cphone='{}', acapacity={}, bcapacity={} WHERE cname = '{}'".format(
caddr, cphone, acapacity, bcapacity, cname)
cursor.execute(sql)
db.commit()
