def login():
conn = connect.connection()
myProjects = projects.getPrevProject()
if request.method == "POST":
mail = request.form["email"]
password = request.form["password"]
sql_stmt = ("select * from benutzer where email = '%s'") % mail
benutzerResult = ibm_db.exec_immediate(conn, sql_stmt)
ben_results = ibm_db.fetch_assoc(benutzerResult)
sql_stmt = ("select * from konto where inhaber = '%s'") % mail
kontoInfo = ibm_db.exec_immediate(conn, sql_stmt)
konto_info = ibm_db.fetch_assoc(kontoInfo)
if ben_results is not False and ben_results['EMAIL'] == mail and konto_info is not False and konto_info['GEHEIMZAHL'] == password:
session["mail"] = mail
session['logged_in'] = True
else:
session['logged_in'] = False
session['error'] = 'Please input valid information to LOGIN'
return redirect(url_for("project"))
return render_template('login.html', prevProjects=myProjects)
def get_books(number):
con, cursor = connect.connection()
sql = 'SELECT * FROM books WHERE book_number = "%s"' % (number)
res = cursor.execute(sql)
cursor.close()
con.close()
return res
def comment(id):
conn = connect.connection()
if "mail" in session:
prevProjects = set()
sql_stmt = "select * from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
if request.method == "POST":
comment = request.form["comment"]
anonymousid = request.form["anonymousid"]
mail = session["mail"]
if anonymousid == 'yes':
sql_stmt1 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','privat')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt1)
else:
sql_stmt2 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','oeffentlich')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt2)
return redirect(url_for("projectdetails", id=id))
return render_template('comment.html', prevProjects=prevProjects)
else:
return redirect(url_for("login"))
def project():
if "mail" in session:
conn = connect.connection()
openProjects = set()
sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email where ersteller = '%s' and status='offen'" % \
session["mail"]
openProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if openProjectsResult is not None:
row = ibm_db.fetch_tuple(openProjectsResult)
openProjects.add(row)
while row:
row = ibm_db.fetch_tuple(openProjectsResult)
if row:
openProjects.add(row)
closeProjects = set()
sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email where ersteller = '%s' and status='geschlossen'" % \
session["mail"]
closeProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if closeProjectsResult is not None:
row = ibm_db.fetch_tuple(closeProjectsResult)
closeProjects.add(row)
while row:
row = ibm_db.fetch_tuple(closeProjectsResult)
if row:
closeProjects.add(row)
return render_template('projects.html', openProjects=openProjects, closeProjects=closeProjects,
ses_email=session["mail"])
else:
return redirect(url_for("login"))
def adminPutawayDue(self):
tempname=self.adminPutawayNameEdit.text()
tempkind=self.adminPutawayKindEdit.text()
tempauthor=self.adminPutawayAuthor.text()
tempnum=self.adminPutawayNumEdit.text()
tempno=self.adminPutawayNoEdit.text()
if tempname == '':
QMessageBox.warning(self,"警告","书籍名不能为空",QMessageBox.Ok)
return
elif tempkind == '':
QMessageBox.warning(self, "警告""种类不能为空", QMessageBox.Ok)
return
elif tempauthor == '':
QMessageBox.warning(self, "警告","作者不能为空", QMessageBox.Ok)
return
elif tempnum == '':
QMessageBox.warning(self, "警告","数量不能为空", QMessageBox.Ok)
return
elif tempno == '':
QMessageBox.warning(self,"警告","序列号不能为空",QMessageBox.Ok)
else:
con, cursor = connect.connection()
res = get_books(tempno)
if res == 0:
try:
cursor.execute("INSERT INTO books(\
book_name,book_author, book_kind, book_number, book_left, book_lending) \
VALUES(%s, %s, %s, %s, %s, %s)", (tempname, tempauthor, tempkind, tempno, tempnum, 0))
con.commit()
reply = QMessageBox.question(self, '信息', '上架成功，是否继续？',
QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if reply == QMessageBox.Yes:
self.adminPutawayNameEdit.clear()
self.adminPutawayKindEdit.clear()
self.adminPutawayNumEdit.clear()
self.adminPutawayNoEdit.clear()
self.adminPutawayAuthor.clear()
self.putBook.emit()
return
else:
self.putBook.emit()
self.adminPutawayBack()
except Exception as e:
con.rollback()
print (e)
finally:
cursor.close()
con.close()
else:
sql = "UPDATE books SET book_left = book_left + 1 WHERE book_number = '%s'" % (tempnum)
try:
cursor.execute(sql)
con.commit()
QMessageBox.warning(self, "", "此书籍已经存在于书库，改数数目增加", QMessageBox.Ok)
except Exception as e:
con.rollback()
finally:
cursor.close()
con.close()
def insert_record(self):
#create object of class connection
con = connection()
#create cursor means create object of cursor
cur = con.cursor()
#insert record into table Employee table
q = "insert into Employee values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#input from user
eid = int(input("Enter Employee id=>"))
dno = int(input("Enter department number=>"))
desig = input("Enter designation=>")
emn = input("Enter Employee name=>")
a = int(input("Enter employee age=>"))
c = input("Enter Empolyee city=>")
email = input("Enter Email=>")
p = input("Enter password=>")
m = input("Enter mobile number=>")
bs = float(input("Enter basic salary=>"))
#create tuple
val = (eid, dno, desig, emn, a, c, email, p, m, bs)
#insert Query run
try:
cur.execute(q, val)
except:
print("Query Error")
else:
#save in database use commit()
con.commit()
print("Record Insert successfully")
#close connection
cur.close()
con.close()
def connectToServer(clientsocket):
host = ''#'10.40.72.114' #raw_input("Enter IP Address: ")
port = 1111 #int(raw_input("Enter port number: "))
clientsocket.connect((host, port))
link = connection(clientsocket)
print link.getMessage()
return link
def search(book_name = None, book_author = None, book_kind = None):
"""在数据库里查找"""
con, cursor = connect.connection()
sql = ''
if book_name is not None and book_kind !='all':
sql = 'SELECT * FROM books WHERE (book_author REGEXP "%s" OR book_name REGEXP "%s")AND book_kind REGEXP "%s"'\
% (book_name,book_name,book_kind)
elif book_name is not None and book_kind == 'all':
sql = 'SELECT * FROM books WHERE book_author REGEXP "%s" OR book_name REGEXP "%s"'\
% (book_name,book_name)
elif book_name is None and book_kind !='all':
sql = 'SELECT * FROM books WHERE book_kind REGEXP "%s"'% (book_kind)
elif book_name is None and book_kind == 'all':
sql = 'SELECT * FROM books '
try:
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
return results
except:
print ('获取失败')
cursor.close()
con.close()
return None
def getBookSum():
con, cursor = connect.connection()
sql = "SELECT COUNT(1) FROM books"
cursor.execute(sql)
sum = cursor.fetchone()
cursor.close()
con.close()
return sum[0]
def connectToClient():
global serversocket
remote_socket, addr = serversocket.accept()
link = connection(remote_socket)
print str(addr), " connected!"
link.sendMessage("Thank you for connecting.")
return (link, addr)
def get_user(name):
con, cursor = connect.connection()
sql = 'SELECT * FROM users WHERE user_account = "%s"' % (name)
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
con.close()
return res
def userdetails(email):
conn = connect.connection()
if "mail" in session:
mail = session["mail"]
userInfo = set()
sql_stmt = "select * from benutzer where email = '%s'" % mail
userInfoResult = ibm_db.exec_immediate(conn, sql_stmt)
if userInfoResult is not None:
row = ibm_db.fetch_tuple(userInfoResult)
userInfo.add(row)
while row:
row = ibm_db.fetch_tuple(userInfoResult)
if row:
userInfo.add(row)
userInfo1 = set() # number of created project
sql_stmt = "select count(kennung) from projekt where ersteller = '%s'" % mail
userInfoResult1 = ibm_db.exec_immediate(conn, sql_stmt)
if userInfoResult1 is not None:
row1 = ibm_db.fetch_tuple(userInfoResult1)
userInfo1.add(row1)
ref_info1 = row1[0]
userInfo2 = set() # number of donated project
sql_stmt = "select count(projekt) from spenden where spender = '%s'" % mail
userInfoResult2 = ibm_db.exec_immediate(conn, sql_stmt)
if userInfoResult2 is not None:
row2 = ibm_db.fetch_tuple(userInfoResult2)
userInfo2.add(row2)
ref_info2 = row2[0]
usersProject = set()
sql_stmt = (
"select kennung,titel,status,sum(spendenbetrag),ersteller,kategorie from projekt left join spenden on projekt.kennung = spenden.projekt where ersteller = '%s' group by kennung,titel,status,ersteller,kategorie ") % mail
usersProjectResult = ibm_db.exec_immediate(conn, sql_stmt)
if usersProjectResult is not None:
row = ibm_db.fetch_tuple(usersProjectResult)
usersProject.add(row)
while row:
row = ibm_db.fetch_tuple(usersProjectResult)
if row:
usersProject.add(row)
donatedProject = set()
sql_stmt = "select kennung,titel,status,finanzierungslimit,kategorie,spendenbetrag from projekt inner join spenden on projekt.kennung=spenden.projekt inner join benutzer on benutzer.email=spenden.spender where benutzer.email = '%s'" % mail
donatedProjectResult = ibm_db.exec_immediate(conn, sql_stmt)
if donatedProjectResult is not None:
row = ibm_db.fetch_tuple(donatedProjectResult)
donatedProject.add(row)
while row:
row = ibm_db.fetch_tuple(donatedProjectResult)
if row:
donatedProject.add(row)
return render_template('userdetails.html', user_info=userInfo, users_project=usersProject, ref_info1=ref_info1,
ref_info2=ref_info2, donatedProject=donatedProject)
else:
return redirect(url_for("login"))
def projectedit(id):
conn = connect.connection()
if "mail" in session:
prevProjects = set()
sql_stmt = "select * from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
sql_stmt = "select id,name from kategorie"
categoryResult = ibm_db.exec_immediate(conn, sql_stmt)
category = set()
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(categoryResult)
category.add(row)
while row:
row = ibm_db.fetch_tuple(categoryResult)
if row:
category.add(row)
mail = session["mail"]
sql_stmt = ("select kennung, titel from projekt where kennung <> '" + id + "' and ersteller = '%s'") % mail
predecessorNameResult = ibm_db.exec_immediate(conn, sql_stmt)
predecessorName = set()
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(predecessorNameResult)
predecessorName.add(row)
while row:
row = ibm_db.fetch_tuple(predecessorNameResult)
if row:
predecessorName.add(row)
if request.method == "POST":
title = request.form["titles"]
details = request.form["details"]
amount = request.form["funding_limit"]
cat_id = request.form["category_id"]
pred_id = request.form["predecessor_id"]
if pred_id == "":
sql_stmt = "update projekt set titel = '" + title + "', beschreibung= '" + details + "', finanzierungslimit = '" + amount + "', kategorie = '" + cat_id + "',vorgaenger = NULL where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
else:
sql_stmt = "update projekt set titel = '" + title + "', beschreibung= '" + details + "', finanzierungslimit = '" + amount + "', kategorie = '" + cat_id + "',vorgaenger = '" + pred_id + "' where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
return redirect(url_for("projectdetails", id=id))
return render_template('projectedit.html', prevProjects=prevProjects, category_lists=category,
predecessor_list=predecessorName)
else:
return redirect(url_for("login"))
def getRecords():
con, cursor = connect.connection()
sql = "SELECT * FROM records ORDER BY id DESC"
cursor.execute(sql)
results = list()
for _ in range(5):
res = cursor.fetchone()
res = list(res)
results.append(res)
cursor.close()
con.close()
return results
def deleteBooks(bookName):
con, cursor = connect.connection()
sql = "DELETE FROM books WHERE book_name = '%s'" % (bookName)
try:
cursor.execute(sql)
con.commit()
except Exception as e:
con.rollback()
print (e)
finally:
cursor.close()
con.close()
def delete(id):
conn = connect.connection()
#delete = "no"
if "mail" in session:
prevProjects = set()
sql_stmt = "select * from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
if request.method == "POST":
sql_stmt = ("select * from projekt where vorgaenger = '%s'") % id
predecessorInfo = ibm_db.exec_immediate(conn, sql_stmt)
predecessor_Info = ibm_db.fetch_assoc(predecessorInfo)
print(predecessor_Info)
if not predecessor_Info:
sql_stmt1 = "delete from komment where projekt = '" + id + "'"
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt1)
sql_stmt2 = "delete from spenden where projekt = '" + id + "'"
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt2)
sql_stmt3 = "delete from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt3)
else:
sql_stmt1 = "delete from komment where projekt = %s" % predecessor_Info['KENNUNG']
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt1)
sql_stmt2 = "delete from spenden where projekt = %s" % predecessor_Info['KENNUNG']
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt2)
sql_stmt3 = "delete from projekt where kennung = %s" % predecessor_Info['KENNUNG']
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt3)
sql_stmt1 = "delete from komment where projekt = '" + id + "'"
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt1)
sql_stmt2 = "delete from spenden where projekt = '" + id + "'"
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt2)
sql_stmt3 = "delete from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt3)
return redirect(url_for("project", ))
return render_template('delete.html', prevProjects=prevProjects)
else:
return redirect(url_for("login"))
def add_account(*mes):
con, cursor = connect.connection()
try:
cursor.execute("INSERT INTO users(\
user_account, user_password, user_lendingnum, user_lending, user_history) \
VALUES(%s, %s, %s, %s, %s)", (mes[0], mes[1], 0, '[]', '[]'))
con.commit()
print ('添加成功')
except Exception as e:
con.rollback()
finally:
cursor.close()
con.close()
def getdata(sql):
con, cursor = connect.connection()
try:
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
return results
except:
print ('获取失败')
cursor.close()
con.close()
return None
def userBorrow(self):
"""借阅图书"""
if self.account != None:
con, cursor = connect.connection() # 连接数据库
curRow = self.userTableWidget.currentRow() # 获取光标所点击的行数
if curRow < 0 :
QMessageBox.warning(self, "警告", "请选择你要借阅的图书！", QMessageBox.Ok)
return
cellItem1 = self.userTableWidget.item(curRow, 0) # 获取图书名称
bookName = cellItem1.text()
if bookName in self.user.lending:
QMessageBox.warning(self, "警告", "这本书你已经借了，而且还没有归还，不可重复再借同一本书。", QMessageBox.Yes)
else:
cellItem2 = self.userTableWidget.item(curRow, 4) # 获取图书剩余量
str1 = cellItem2.text()
bookNumber = int(str1)
if bookNumber <= 0:
QMessageBox.warning(self, "警告", "‘"+bookName+"’已经没有剩余量了！", QMessageBox.Ok)
else :
"""Todo 借阅图书（记录+图书，剩余量-1）"""
lendTime = time.strftime("%Y-%m-%d", time.localtime()) # 获取借阅图书的时间
self.user.lending.append(bookName)
self.user.lending.append(lendTime)
lending = str(self.user.lending)
try: #借书-用户添加信息
cursor.execute("UPDATE users SET user_lendingnum = user_lendingnum+1, user_lending = %s WHERE user_account = %s",
(lending, self.account))
cursor.execute("UPDATE books SET book_left = book_left - 1 ,book_lending = book_lending + 1 WHERE book_name = %s",
(bookName))
con.commit()
except Exception as e:
con.rollback()
print(e)
"""保存借阅记录"""
try:
cursor.execute(
"INSERT INTO records(user_name, operator, book_name, time) VALUES(%s, %s, %s, %s)",
(self.user.account, '借阅了', bookName, lendTime))
con.commit()
except Exception as e:
con.rollback()
print(e)
finally:
con.close()
cursor.close()
QMessageBox.information(self, "恭喜您", "您已成功借阅‘"+bookName+"’图书", QMessageBox.Ok)
self.load()
self.userFind()
def list(self,id):
query='SELECT * FROM `user` WHERE `id`='+str(id)+';'
obj=connection()
while 1:
if obj.lock.locked():
obj.check.wait(1)
obj.check.release()
else:
break
obj.lock.acquire()
obj.cc.execute(query)
out=obj.cc.fetchone()
obj.lock.release()
#obj.cc.close()
return(out)
def signup():
conn = connect.connection()
if request.method == "POST":
email = request.form["email"]
username = request.form["username"]
password = request.form["password"]
sql_stmt = "insert into benutzer (email, name) values ('" + email + "','" + username + "')"
signupInfo = ibm_db.exec_immediate(conn, sql_stmt)
sql_stmt = "insert into konto (inhaber, guthaben, geheimzahl) values ('" + email + "','9999','" + password + "')"
signupInfo1 = ibm_db.exec_immediate(conn, sql_stmt)
return redirect(url_for("login"))
return render_template('signup.html')
def add(self,email, passwd):
task='INSERT INTO `user`(`email`,`password`) VALUES ('+'\"'+str(email)+'\"'+','+'\"'+str(passwd)+'\"'+');'
obj=connection()
while 1:
if obj.lock.locked():
sleep(1)
else:
break
obj.lock.acquire()
obj.cc.execute(task)
obj.conn.commit()
query='SELECT `id` FROM `user` WHERE `email`="'+email+'" AND `password`="'+passwd+'";'
obj.cc.execute(query)
sid=obj.cc.fetchone()
print("The records are saved with id:%s\n" %sid['id'])
obj.lock.release()
def adminUserFindFunction(userData):
# 用户查询
con,cursor = connect.connection()
sql = 'SELECT * FROM users WHERE user_account = "%s"' % (userData)
getdata(sql)
try:
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
return results
except:
cursor.close()
con.close()
p = tuple()
return p
def newproject():
conn = connect.connection()
if "mail" in session:
if request.method == "POST":
title = request.form["titles"]
details = request.form["details"]
amount = request.form["funding_limit"]
cat_id = request.form["category_id"]
pre_id = request.form["predecessor_id"]
if pre_id == "":
sql_stmt = "insert into projekt (titel, beschreibung, finanzierungslimit,ersteller,kategorie,vorgaenger) values ('" + title + "','" + details + "','" + amount + "','" + \
session["mail"] + "','" + cat_id + "', Null)"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
else:
sql_stmt = "insert into projekt (titel, beschreibung, finanzierungslimit,ersteller,kategorie,vorgaenger) values ('" + title + "','" + details + "','" + amount + "','" + \
session["mail"] + "','" + cat_id + "','" + pre_id + "')"
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt)
return redirect(url_for("project"))
sql_stmt = "select id,name from kategorie"
prevCategoryResult = ibm_db.exec_immediate(conn, sql_stmt)
prevCategory = set()
if prevCategoryResult is not None:
row = ibm_db.fetch_tuple(prevCategoryResult)
prevCategory.add(row)
while row:
row = ibm_db.fetch_tuple(prevCategoryResult)
if row:
prevCategory.add(row)
mail = session["mail"]
sql_stmt = ("select kennung, titel from projekt where ersteller = '%s'") % mail
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
prevProjects = set()
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
return render_template('newprojects.html', category_lists=prevCategory, predecessor_list=prevProjects)
else:
return redirect(url_for("login"))
def load(self):
"""加载数据库信息"""
if self.account != None:
user = self.account
con, cursor = connect.connection()
sql = 'SELECT * FROM users WHERE user_account = "%s"' % (user)
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
if len(results)!=0:
lending = eval(results[0][4])
history = eval(results[0][5])
self.user.setdata(results[0][1], results[0][2], results[0][3], lending, history)
# self.user.show()
self.setWindowTitle(str(self.account)+"的记录")
self.setWindowIcon(QIcon('main.jpg')) # 设置窗体标题图标
def search():
conn = connect.connection()
if "mail" in session:
if request.method == "POST":
title = request.form["title"]
sql_stmt = "select kennung,titel,ersteller,status,sum(spendenbetrag),kategorie,name from projekt left join spenden on projekt.kennung = spenden.projekt inner join benutzer on projekt.ersteller=benutzer.email where upper(titel) like upper('" + title + "%') group by kennung,name,kategorie,titel,status,ersteller"
searchPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
searchProjects = set()
if searchPorjectsResult is not None:
row = ibm_db.fetch_tuple(searchPorjectsResult)
searchProjects.add(row)
while row:
row = ibm_db.fetch_tuple(searchPorjectsResult)
if row:
searchProjects.add(row)
return render_template('search.html', searchProjects=searchProjects, search_val=title)
return render_template('search.html')
else:
return redirect(url_for("login"))
def load(self):
"""加载数据库信息"""
if self.account!=None:
user = self.account
con, cursor = connect.connection()
sql = 'SELECT * FROM users WHERE user_account = "%s"' % (user)
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
lending = eval(results[0][4])
history = eval(results[0][5])
self.user.setdata(results[0][1], results[0][2], results[0][3], lending, history)
# self.user.show()
timee = int(time.strftime("%H", time.localtime()))
if timee < 5 or timee > 18:
hellostr = "晚上好！"
elif timee >5 and timee <12:
hellostr = "上午好！"
else:
hellostr = "下午好！"
self.userLabel.setText("尊敬的 " + str(self.account) + " 用户，"+hellostr)
self.setWindowIcon(QIcon('main.jpg')) # 设置窗体标题图标
def adminFindFunction(bookData=None, bookKind=None):
con, cursor = connect.connection()
sql = ''
if bookKind == '书名':
sql = 'SELECT * FROM books WHERE book_name REGEXP "%s"' % (bookData) # 在数据库里查找相应信息
elif bookKind == '作者':
sql = 'SELECT * FROM books WHERE book_author REGEXP "%s"' % (bookData)
elif bookKind == '序列号':
sql = 'SELECT * FROM books WHERE book_no REGEXP "%s"' % (bookData)
elif bookKind == '全部':
sql = 'SELECT * FROM BOOKS'
elif bookKind == '种类':
sql = 'SELECT * FROM books WHERE book_kind REGEXP "%s"' % (bookData)
try:
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
con.close()
return results
except:
cursor.close()
con.close()
return None
initial_model[time_checker][1] = x
x = []
time_checker = time_checker + 1
"Training the models, get the test accuracy and save the models"
if tracker > 0: # if this is not the stage 1
current_model = []
lenth = 0
higest_model = torch.load(
"C:\\Users\\garychenai\\Desktop\\Final project\\codes\\module\\highest_module.pth"
)
for i in range(0, population):
input_CNN = Input_CNN_block(3, 64)
model2 = CNN_final_block(initial_model[i][0], NUM_NODES[m],
initial_model[i][1], 64, 64)
model = connection(higest_model, model2)
linear = Linear_block(65536, 10)
model_results_fitness[i][0] = train_model(
model, linear, input_CNN, learning_rate, nsamples,
load_train_dataset()) # save training loss in [0]
torch.save(
input_CNN,
"C:\\Users\\garychenai\\Desktop\\Final project\\codes\\module\\rest_module\\input_CNN_s%d_module_%d.pth"
% (tracker + 1, i + 1))
torch.save(
linear,
"C:\\Users\\garychenai\\Desktop\\Final project\\codes\\module\\rest_module\\linear_s%d_module_%d.pth"
% (tracker + 1, i + 1))
torch.save(
model,
"C:\\Users\\garychenai\\Desktop\\Final project\\codes\\module\\model_s%d_module_%d.pth"
def userReturn(self):
"""归还图书"""
if self.account != None:
con, cursor = connect.connection() # 连接数据库
curRow = self.recordTableWidget.currentRow() # 获取光标所点击的行数
cellItem1 = self.recordTableWidget.item(curRow, 0)
if cellItem1 != None: # 获取是否归还信息
bookName = cellItem1.text() # 获取选中图书的信息
else:
QMessageBox.warning(self, "警告","请选择你要归还的图书！", QMessageBox.Yes)
return
"""if curRow < 0 :
QMessageBox.warning(self, "警告", "请选择你要归还的图书！", QMessageBox.Yes)"""
cellItem2 = self.recordTableWidget.item(curRow, 2) # 获取是否归还信息
str2 = cellItem2.text()
if str2 != "----":
QMessageBox().information(self, "错误", "您已归还该图书", QMessageBox.Ok)
else :
reply = QMessageBox().warning(self, "归还图书", "您确定要归还"+bookName+"图书吗？",QMessageBox.Yes|QMessageBox.No)
if reply == QMessageBox.Yes :
returnTime = time.strftime("%Y-%m-%d", time.localtime()) # 获取归还图书的时间
n = self.user.lending.index(bookName)
lentTime = self.user.lending[n+1]
self.user.lending.remove(bookName) # 移除
self.user.lending.remove(lentTime)
self.user.history.append(bookName) # 添加至history
self.user.history.append(lentTime)
self.user.history.append(returnTime)
lending = str(self.user.lending)
history = str(self.user.history)
try: # 还书--数据库信息更新
"""Tode 用户名"""
cursor.execute("UPDATE users SET user_lendingnum = user_lendingnum-1 ,user_lending = %s,"
" user_history = %s WHERE user_account = %s", (lending, history,self.account))
cursor.execute("UPDATE books SET book_left = book_left + 1 ,book_lending = book_lending - 1 WHERE book_name = %s",
(bookName))
con.commit()
except Exception as e:
print(e)
con.rollback()
"""finally:
cursor.close()
con.close()"""
"""将记录保存到数据库中"""
try:
cursor.execute("INSERT INTO records(user_name, operator, book_name, time) VALUES(%s, %s, %s, %s)",
(self.user.account, '归还了', bookName, returnTime))
con.commit()
except Exception as e:
con.rollback()
print (e)
finally:
con.close()
cursor.close()
QMessageBox().information (self, "成功", "成功归换"+bookName+"图书，欢迎您继续借阅图书", QMessageBox.Ok)
self.tableShow()
self.load()
self.ret.emit()
import ibm_db
import flask
import connect
conn = connect.connection()
def getPrevProject():
prevProjects = set()
sql_stmt = "select * from projekt"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
return prevProjects
from connect import connection
con, cur = connection(con, cur)
def create_new_table(con, cur):
"""
Creates new tables for every different kind of recommendation if table does not already exist.
:param con: makes a connection to the database.
:param cur: makes it possible to call upon SQL methods.
"""
cur.execute("""CREATE TABLE IF NOT EXISTS collaborative_recommendations_popular_test
(recom_basis VARCHAR,lst_product_id VARCHAR);""")
con.commit()
create_new_table(con, cur)
def insert_into_tables(base_name, lst_recoms):
"""
Inserts data from insert_different_tables and inserts it in the right columns etc.
:param base_name: name of the base on which a recommendation is made as a string.
:param lst_recoms: list with product id's as a string.
"""
cur.execute("INSERT INTO collaborative_recommendations_popular VALUES ('%s', '%s');" % (base_name, lst_recoms))
def insert_different_tables():
"""
Asks for the right data and calls on insert function to put data in the right columns.
def projectdetails(id):
conn = connect.connection()
if "mail" in session:
mail = session["mail"]
prevProjects = set()
sql_stmt = "select kennung,titel,name,projekt.beschreibung,finanzierungslimit,status,vorgaenger,email,kategorie,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
ref_proj_id = row[6]
ref_ersteller = row[9]
ref_status = row[5]
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
prevProjects1 = set()
if ref_proj_id is not None:
sql_stmt = "select kennung,titel from projekt where kennung = %s" % ref_proj_id
prevPorjectsResult1 = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult1 is not None:
row1 = ibm_db.fetch_tuple(prevPorjectsResult1)
prevProjects1.add(row1)
ref_proj_id = row1[0]
ref_proj_title = row1[1]
else:
ref_proj_id = ''
ref_proj_title = 'No Predecessor'
comments = set()
sql_stmt = "select name,text,sichtbarkeit from benutzer inner join komment on benutzer.email=komment.benutzer where komment.projekt = %s and sichtbarkeit='oeffentlich'" % id
commentsResult = ibm_db.exec_immediate(conn, sql_stmt)
if commentsResult is not None:
row = ibm_db.fetch_tuple(commentsResult)
comments.add(row)
while row:
row = ibm_db.fetch_tuple(commentsResult)
if row:
comments.add(row)
donationInfo = set()
sql_stmt = "select name,spendenbetrag,sichtbarkeit from spenden inner join benutzer on spenden.spender=benutzer.email where projekt = %s and sichtbarkeit='oeffentlich'" % id
donationInfoResult = ibm_db.exec_immediate(conn, sql_stmt)
if donationInfoResult is not None:
row = ibm_db.fetch_tuple(donationInfoResult)
donationInfo.add(row)
while row:
row = ibm_db.fetch_tuple(donationInfoResult)
if row:
donationInfo.add(row)
return render_template('projectdetails.html', prevProjects=prevProjects, ref_proj_title=ref_proj_title,
ref_proj_id=ref_proj_id, donationInfo=donationInfo, id=id, comments=comments,
ref_ersteller=ref_ersteller, mail=mail, ref_status=ref_status)
else:
return redirect(url_for("login"))
def donate(id):
conn = connect.connection()
noDonation = ""
tem_dcheque = ""
if "mail" in session:
mail = session["mail"]
prevProjects = set()
sql_stmt = "select * from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
sql_stmt = "select * from spenden where spender = '" + mail + "' and projekt = '" + id + "'"
donationcheque = ibm_db.exec_immediate(conn, sql_stmt)
donation_cheque = ibm_db.fetch_assoc(donationcheque)
if donation_cheque is False:
tem_dcheque = "possible"
if request.method == "POST":
donate = request.form["donate"]
deciDeoneat = Decimal(donate)
anonymousid = request.form["anonymousid"]
balanceCheque = set()
sql_stmt4 = "select * from konto where inhaber = '" + mail + "'"
balanceChequeResult = ibm_db.exec_immediate(conn, sql_stmt4)
if balanceChequeResult is not None:
row1 = ibm_db.fetch_tuple(balanceChequeResult)
balanceCheque.add(row1)
currentBalance = Decimal(row1[1])
print(currentBalance, type(currentBalance))
if currentBalance >= deciDeoneat:
print(deciDeoneat, type(deciDeoneat))
if anonymousid == 'yes':
sql_stmt = "insert into spenden (spender,projekt,spendenbetrag,sichtbarkeit) values ('" + mail + "','" + id + "' , '" + donate + "','privat')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
else:
sql_stmt1 = "insert into spenden (spender,projekt,spendenbetrag,sichtbarkeit) values ('" + mail + "','" + id + "' , '" + donate + "','oeffentlich')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt1)
spendenInfo = set()
sql_stmt2 = (
"select kennung,titel,sum(spendenbetrag),ersteller,finanzierungslimit from projekt left join spenden on projekt.kennung = spenden.projekt where kennung = '%s' group by kennung,titel,ersteller,finanzierungslimit ") % id
spendenInfoResult = ibm_db.exec_immediate(conn, sql_stmt2)
if spendenInfoResult is not None:
row1 = ibm_db.fetch_tuple(spendenInfoResult)
spendenInfo.add(row1)
ref_sum = row1[2]
ref_limit = row1[4]
tem_sum = Decimal(ref_sum)
tem_limit = Decimal(ref_limit)
if tem_sum >= tem_limit:
sql_stmt3 = "update projekt set status = 'geschlossen' where kennung = %s" % id
prorjectStatusResult = ibm_db.exec_immediate(conn, sql_stmt3)
balanceReduce = set()
sql_stmt4 = "select * from konto where inhaber = '" + mail + "'"
balanceReduceResult = ibm_db.exec_immediate(conn, sql_stmt4)
if balanceReduceResult is not None:
row1 = ibm_db.fetch_tuple(balanceReduceResult)
balanceReduce.add(row1)
prev_balance = row1[1]
temp_bal = Decimal(prev_balance)
newBalance = temp_bal - deciDeoneat
print(newBalance, type(newBalance))
tempNewBal = str(newBalance)
print(tempNewBal, type(tempNewBal))
sql_stmt3 = "update konto set guthaben = '" + tempNewBal + "' where inhaber = '" + mail + "'"
prorjectStatusResult = ibm_db.exec_immediate(conn, sql_stmt3)
return redirect(url_for("projectdetails", id=id))
else:
noDonation = "yes"
return render_template('donate.html', prevProjects=prevProjects, noDonation=noDonation, tem_dcheque=tem_dcheque)
else:
return redirect(url_for("login"))
