def remove_listen_group(msg):
"""
移除群组监听
命令格式：移除群监听 群组名
:param msg:
:return:
"""
info = msg.text.split(config.INTERNAL)
group = msg.bot.groups().search(info[1])[0]
logger.info("移除群监听：{0}".format(group.name))
DataBase.delete_listen_group(group.puid)
msg.reply("移除群监听：{0}成功".format(group.name))
def add_listen_group(msg):
"""
添加群组监听
命令格式：添加群监听 群组名
:param msg:
:return:
"""
info = msg.text.split(config.INTERNAL)
group = msg.bot.groups().search(info[1])[0]
logger.info("监听群：{0}".format(group.name))
DataBase.add_listen_group(group.puid, group.name)
msg.reply("监听群：{0}成功".format(group.name))
def __init__(self):
self.db = DataBase()
self.scrapper = ScheduleScrapper()
self._keyboard_before_register = [[_.ABOUT_BOT]]
self._keyboard_after_register = [[_.TODAY, _.TOMORROW],
[_.DAY_AFTER_TOMORROW, _.WEEK],
[_.TWO_WEEK, _.ABOUT_BOT]]
self._keyboard_admin_panel = [
["/" + _.GET_MY_ID, _.MAIN_MENU],
]
def add_group_admin(msg):
"""
添加群管理员
命令格式：添加群管理员 群名 管理员
:param msg:
:return:
"""
info = msg.text.split(config.INTERNAL)
chat_group = info[1]
friend = msg.bot.friends().search(info[2])[0]
logger.info("将{0}设置为群：{1}的群管理员".format(friend.name, chat_group))
DataBase.add_admin(friend.puid, chat_group, friend.name)
msg.reply("将{0}设置为群：{1}的群管理员 成功".format(friend.name, chat_group))
def remove_group_admin(msg):
"""
删除群管理员
命令格式：删除群管理员 群名 管理员
:param msg:
:return:
"""
info = msg.text.split(config.INTERNAL)
chat_group = info[1]
friend = msg.bot.friends().search(info[2])[0]
logger.info("从群：{0}移除{1}的群管理员权限".format(chat_group, friend.name))
DataBase.delete_admin(friend.puid, chat_group)
msg.reply("从群：{0}移除{1}的群管理员权限 成功".format(chat_group, friend.name))
def recupere_tous(numCommande, curseur=DataBase.cursor()):
curseur.execute("SELECT * FROM Commentaire WHERE numCommande=%s",
numCommande)
return [
Commentaire(c[0], c[1], c[2], c[3], c[4]).getDict()
for c in curseur.fetchall()
]
def devient_artiste(courriel, nom):
client = Client.trouveAvecCourriel(courriel=courriel)
if client is None:
return None
cursor = DataBase.cursor()
cursor.execute('INSERT INTO Artiste VALUE (%s, %s);', (courriel, nom))
return Artiste(client, nom)
def trouveAvecCourriel(courriel, curseur=DataBase.cursor()):
curseur.execute('SELECT * FROM Artiste WHERE courriel=%s ;', courriel)
artiste = curseur.fetchone()
if artiste is not None:
return Artiste(Client.trouveAvecCourriel(courriel=courriel),
artiste[1])
return None
def cherche_nom(nom, curseur=DataBase.cursor()):
curseur.execute('SELECT * FROM Artiste WHERE nom=%s ;', nom)
resultat = curseur.fetchone()
if resultat is not None:
return {'nom': resultat[1], 'courriel': resultat[0]}
return None
def commande_artiste(nom, curseur=DataBase.cursor()):
curseur.execute("SELECT * FROM Commande WHERE superviseur=%s", nom)
commandes = curseur.fetchall()
if commandes is not None:
return [
Commande(c[0], c[1], c[2], c[3], c[4], c[5], c[6]).toDict()
for c in commandes
]
def mes_commandes(courriel, curseur=DataBase.cursor()):
curseur.execute("SELECT * FROM Commande WHERE demandeur=%s", courriel)
commandes = curseur.fetchall()
if commandes is not None:
return [
Commande(c[0], c[1], c[2], c[3], c[4], c[5], c[6]).toDict()
for c in commandes
]
def group_message(msg):
"""
处理群聊消息
:param msg:
:return:
"""
if not config.IS_LISTEN_GROUP:
return
group = msg.chat.name
sender = msg.member.name
if group in DataBase.all_listen_group():
DataBase.save_chat(group, sender,
msg.receive_time.strftime("%Y-%m-%d %H:%M:%S"),
msg.text)
return
def pour_artiste(nom):
curseur = DataBase.cursor()
curseur.execute("SELECT * FROM Oeuvre WHERE auteur=%s;", nom)
resultat = curseur.fetchall()
if resultat is not None:
return [
Oeuvre(x[0], x[1], x[2], x[3], x[4], x[5]).toDict()
for x in resultat
]
def liste_artiste():
curseur = DataBase.cursor()
curseur.execute('SELECT * FROM Artiste;')
resultat = curseur.fetchall()
if resultat is not None:
artistes = [Artiste.cherche_nom(x[1], curseur) for x in resultat]
return artistes
return None
def ajoute(nom,
auteur,
dateCreation,
type='',
description='',
enExposition=False,
curseur=DataBase.cursor()):
oeuvre = f"INSERT INTO Oeuvre (nom, auteur, dateCreation, type, description, enExposition) VALUE ('{nom}','{auteur}',STR_TO_DATE('{dateCreation}','%Y-%m-%d'),'{type}','{description}',{enExposition})"
curseur.execute(oeuvre)
return {}
def commander_creation(superviseur,
demandeur,
prix,
adresse_livraison,
curseur=DataBase.cursor()):
curseur.execute(
"INSERT INTO Commande (superviseur, demandeur, prix, adresseLivraison, type) VALUES (%s, %s, %s, %s, 'Création')",
(superviseur, demandeur, prix, adresse_livraison))
curseur.execute("SELECT MAX(num) FROM Commande;")
return curseur.fetchone() # retourne le numero de la commande créée
def recherche():
type = request.args.get('type')
recherche = request.args.get('recherche')
if type == 'Artiste':
response = {
"status": 200,
"oeuvre": oeuvre.recherche_arti(recherche, DataBase.cursor())
}
return jsonify(response)
elif type == 'Oeuvre':
response = {
"status": 200,
"oeuvre": oeuvre.recherche_nom(recherche, DataBase.cursor())
}
return jsonify(response)
elif type == 'Type':
response = {
"status": 200,
"oeuvre": oeuvre.recherche_type(recherche, DataBase.cursor())
}
return jsonify(response)
def commander_reservation(oeuvre,
superviseur,
demandeur,
prix,
adresse_livraison,
curseur=DataBase.cursor()):
print("Reserve commande")
curseur.execute(
"INSERT INTO Commande (oeuvre, superviseur, demandeur, prix, adresseLivraison, type) VALUES (%s, %s, %s, %s, %s, 'Réservation')",
(oeuvre, superviseur, demandeur, prix, adresse_livraison))
curseur.execute("SELECT MAX(num) FROM Commande;")
num = curseur.fetchone()
print(num)
return num # retourne le numero de la commande créée
def count_member_by_data(msg):
"""
按日期统计发言成员
命令格式:按日期统计发言成员 群组 起始日期 结束日期
日期格式：2018-01-01
:param msg:
:return:
"""
info = msg.text.split(config.INTERNAL)
group = info[1]
start = datetime.strptime(info[2], "%Y-%m-%d")
end = datetime.strptime(info[3], "%Y-%m-%d")
users = config.INTERNAL.join(
DataBase.count_member_by_date(group, start, end))
logger.info("统计群：{0} 从{1} 到 {2}期间发言的成员：{3}".format(group, start, end,
users))
msg.reply(users)
def group_sharing(msg):
"""
处理群分享
:param msg:
:return:
"""
if not (config.IS_LISTEN_GROUP and config.IS_LISTEN_SHARING):
return
group = msg.chat.name
if group not in DataBase.all_listen_group():
return
sender = msg.member.name
msg.forward(bot.file_helper,
prefix='【{0}】：【{1}】群成员【{2}】分享了：'.format(
msg.receive_time.strftime("%Y-%m-%d %H:%M:%S"), group,
sender))
return
def __init__(self):
super().__init__()
# Initialize socket
self.IP = (sk.gethostbyname(sk.gethostname()), 54321)
self.server = MySocket(sk.AF_INET, sk.SOCK_STREAM)
self.server.bind(self.IP)
self.server.listen(MAXIMUM_CONNECTION) # maximum 5 client
self.clients_list = {}
# Initialize GUI
self._root = tk.Tk()
self._root.geometry("1000x800")
self._root.configure(bg='#7ed6df')
self._root.title("Online Library Server")
self._root.button_frame = tk.Frame(self._root, bg='#7ed6df')
## Create widgets
self._root.lbl_title = tk.Label(self._root,
text="ONLINE LIBRARY SERVER",
width=25,
font="Consolas 30 bold",
bg='#f9cdad',
fg="#ec2049")
self._root.lbl_address = tk.Label(self._root,
text="Address: " + str(self.IP[0]),
width=25,
font="Consolas 25 bold",
bg='#fc9d9a',
fg='#aa2e00')
self._root.lbl_logs = tk.Label(self._root,
text="Message:",
width=15,
font="Consolas 15 bold",
bg='#97c1a9',
fg='#ffffff')
self._root.btn_disconnect = tk.Button(self._root.button_frame,
text="DISCONNECT",
width=12,
activebackground="#ff8c94",
bg="#355c7d",
fg='#feffff',
font="Consolas 20 bold",
command=self.on_exit_main)
self._root.btn_manager = tk.Button(self._root.button_frame,
text="MANAGER",
width=12,
activebackground="#ff8c94",
bg="#355c7d",
fg="#feffff",
disabledforeground="#f9c859",
font="Consolas 20 bold",
command=self.on_manager)
self._root.logs = tkst.ScrolledText(self._root,
width=95,
height=25,
state="disable",
font=("Consolas 14 bold"),
wrap=tk.WORD,
bg="#c7ecee",
foreground="#2a363d")
# Setup button function
self._root.bind("<Destroy>", self.on_exit_main)
# Draw widgets
self._root.lbl_title.pack(pady=(20, 0))
self._root.lbl_address.pack(pady=(0, 10))
self._root.lbl_logs.pack(side=tk.TOP, anchor="w", padx=15)
self._root.button_frame.pack(side=tk.BOTTOM,
fill=tk.BOTH,
expand=1,
pady=40)
self._root.btn_manager.pack(side=tk.LEFT, padx=40)
self._root.btn_disconnect.pack(side=tk.RIGHT, padx=40)
self._root.logs.pack(side=tk.BOTTOM)
# Initialize Database
self.db = DataBase()
# Running server
Thread(target=self.accept_connections, daemon=True).start()
self.update_logs(Server.get_message(msg="SERVER STARTED"))
class Server:
def __init__(self):
super().__init__()
# Initialize socket
self.IP = (sk.gethostbyname(sk.gethostname()), 54321)
self.server = MySocket(sk.AF_INET, sk.SOCK_STREAM)
self.server.bind(self.IP)
self.server.listen(MAXIMUM_CONNECTION) # maximum 5 client
self.clients_list = {}
# Initialize GUI
self._root = tk.Tk()
self._root.geometry("1000x800")
self._root.configure(bg='#7ed6df')
self._root.title("Online Library Server")
self._root.button_frame = tk.Frame(self._root, bg='#7ed6df')
## Create widgets
self._root.lbl_title = tk.Label(self._root,
text="ONLINE LIBRARY SERVER",
width=25,
font="Consolas 30 bold",
bg='#f9cdad',
fg="#ec2049")
self._root.lbl_address = tk.Label(self._root,
text="Address: " + str(self.IP[0]),
width=25,
font="Consolas 25 bold",
bg='#fc9d9a',
fg='#aa2e00')
self._root.lbl_logs = tk.Label(self._root,
text="Message:",
width=15,
font="Consolas 15 bold",
bg='#97c1a9',
fg='#ffffff')
self._root.btn_disconnect = tk.Button(self._root.button_frame,
text="DISCONNECT",
width=12,
activebackground="#ff8c94",
bg="#355c7d",
fg='#feffff',
font="Consolas 20 bold",
command=self.on_exit_main)
self._root.btn_manager = tk.Button(self._root.button_frame,
text="MANAGER",
width=12,
activebackground="#ff8c94",
bg="#355c7d",
fg="#feffff",
disabledforeground="#f9c859",
font="Consolas 20 bold",
command=self.on_manager)
self._root.logs = tkst.ScrolledText(self._root,
width=95,
height=25,
state="disable",
font=("Consolas 14 bold"),
wrap=tk.WORD,
bg="#c7ecee",
foreground="#2a363d")
# Setup button function
self._root.bind("<Destroy>", self.on_exit_main)
# Draw widgets
self._root.lbl_title.pack(pady=(20, 0))
self._root.lbl_address.pack(pady=(0, 10))
self._root.lbl_logs.pack(side=tk.TOP, anchor="w", padx=15)
self._root.button_frame.pack(side=tk.BOTTOM,
fill=tk.BOTH,
expand=1,
pady=40)
self._root.btn_manager.pack(side=tk.LEFT, padx=40)
self._root.btn_disconnect.pack(side=tk.RIGHT, padx=40)
self._root.logs.pack(side=tk.BOTTOM)
# Initialize Database
self.db = DataBase()
# Running server
Thread(target=self.accept_connections, daemon=True).start()
self.update_logs(Server.get_message(msg="SERVER STARTED"))
def runApplication(self):
self._root.mainloop()
def on_exit_main(self, event=None):
for client in self.clients_list:
client.close()
self.update_logs(
Server.get_message(self.clients_list[client],
msg="QUIT command from Server"))
del self.clients_list
self.clients_list = {}
def on_manager(self, event=None):
self._root.btn_manager.config(state='disable')
self._root.manager = Manager(self._root, self.db)
def quit_win():
self._root.manager.manager.destroy()
self._root.btn_manager.config(state='normal')
self._root.manager.manager.protocol("WM_DELETE_WINDOW", quit_win)
@staticmethod
def get_message(addr=None, user=None, msg=None):
result = '[' + time.strftime("%d/%m/%Y %H:%M:%S",
time.localtime()) + ']\t'
if addr: result += str(addr) + '\t'
if user: result += "'" + user + "'\t"
result += msg
return result
def update_logs(self, msg):
if not msg: return
self._root.logs.configure(state="normal")
self._root.logs.insert(tk.END, msg + '\n')
self._root.logs.see(tk.END)
self._root.logs.configure(state="disable")
def accept_connections(self):
""" Multithreading handling for incomming clients"""
while True:
client, addr = self.server.accept()
if len(self.clients_list) >= MAXIMUM_CONNECTION:
client.send(bytes("OVERFLOW", "utf8"))
client.close()
continue
client.send(bytes("OK", "utf8"))
self.clients_list[client] = addr
self.update_logs(
Server.get_message(addr, msg="CONNECTED TO SERVER"))
Thread(target=self.handle_client, args=(
client,
addr,
)).start()
def handle_client(self, client, addr):
""" Handles a single client connection """
USER = None
while True:
try:
msg = client.recv(1024).decode("utf8")
except OSError: # client
break
if (msg == "QUIT"):
self.update_logs(Server.get_message(addr, msg="QUIT"))
client.shutdown(sk.SHUT_RDWR)
client.close()
del self.clients_list[client]
return
else:
if not USER: # chua login
cmd = msg.split('\t')
if cmd[0] == 'LOGIN':
''' ---------- Handle login ----------'''
respone = self.db.account_sign_in(cmd[1], cmd[2])
if respone == "SUCCESS":
USER = cmd[1]
client.send(bytes(respone, "utf8"))
self.update_logs(
Server.get_message(addr,
msg=("'" + cmd[1] + "' ") +
"HAS LOGIN " + respone))
''' ---------------------------------------------------------------- '''
elif cmd[0] == 'SIGNUP':
''' ---------- Handle signup ----------'''
respone = self.db.account_sign_up(cmd[1], cmd[2])
client.send(bytes(respone, "utf8"))
self.update_logs(
Server.get_message(addr,
msg="'" + cmd[1] +
"' HAS SIGN UP " + respone))
''' ---------------------------------------------------------------- '''
else: # loged-in
cmd = msg.split('\t', 1)
if cmd[0] == 'SEARCH':
result = pickle.dumps(self.db.book_query(cmd[1]))
client.send(result)
self.update_logs(Server.get_message(addr, USER, msg))
self.update_logs(
Server.get_message("SERVER",
msg="send search result to " +
str(addr)))
elif cmd[0] == 'BOOK':
try:
client.send(bytes(self.db.get_book(cmd[1]),
"utf8"))
self.update_logs(
Server.get_message(
addr, USER,
"READ BOOK with ID = " + cmd[1]))
self.update_logs(
Server.get_message("SERVER",
msg="send book to " +
str(addr)))
except FileNotFoundError:
client.send(bytes("Book not available", "utf8"))
self.update_logs(
Server.get_message(
addr, USER, "READ BOOK with ID = " +
cmd[1] + " but book not found"))
elif cmd[0] == 'LOGOUT':
self.update_logs(
Server.get_message(addr, USER, "has LOGGED OUT"))
USER = None
elif cmd[0] == 'DOWNLOAD':
self.update_logs(
Server.get_message(
addr, USER,
"DOWNLOAD BOOK with ID = " + cmd[1]))
pass
pass
pass
pass
def fin_carriere(courriel):
cursor = DataBase.cursor()
cursor.execute('DELETE FROM Artiste WHERE courriel=%s;', courriel)
return {}
def ajoute(auteur, numCommande, texte, curseur=DataBase.cursor()):
print(auteur, numCommande, texte)
curseur.execute(
"INSERT INTO Commentaire (auteur, numCommande, texte) VALUE (%s, %s, %s)",
(auteur, numCommande, texte))
import src.buttons as buttons
import src.utility as utility
from src.database import DataBase
from flask import Flask, request, render_template
app = Flask(__name__)
db = DataBase()
@app.route('/')
def main_page():
return render_template('index.html',
query_buttons=buttons.query_buttons,
to_report_button=buttons.create_report)
@app.route('/query/<int:num>', methods=['GET', 'POST'])
def exec_query(num):
if num < 0 or num > 6:
return '{"status": "error", "msg": "incorrect number"}'
if num == 3 and request.method == 'GET':
return render_template('query_form.html')
query = utility.get_query(num)
if num == 3 and request.method == 'POST':
query = str.format(query, request.form['account_id'])
res, met = db.exec(query)
return render_template('table.html',
result=res,
metadata=met,
error='Нет данных, удволетворяющих запросу')
from src.database import DataBase
from os import getenv
from dotenv import load_dotenv
from src.database.oeuvre import Oeuvre
from src.database.artiste import Artiste
load_dotenv()
db = DataBase()
curseur = db.cursor()
monArtiste = Artiste.cherche_nom('lautre', curseur)
#print(monArtiste.courriel)
#mesArtistes = Artiste.liste_artiste(curseur)
def cherche_nom(nom, curseur=DataBase.cursor()):
curseur.execute('SELECT * FROM Artiste WHERE nom=%s ;', nom)
resultat = curseur.fetchone()
if resultat is not None:
return {'nom': resultat[1], 'courriel': resultat[0]}
return None
rech = cherche_nom('alblap', curseur)
#print(rech)
def pour_artiste(nom):
curseur = DataBase.cursor()
curseur.execute("SELECT * FROM Oeuvre WHERE auteur=%s;", nom)
def facturer(numCmd,adresseFac,total, curseur=DataBase.cursor()):
curseur.execute('INSERT INTO Facture VALUES (%s,%s,%s)',(numCmd,adresseFac,total))
def listefac(arti, curseur=DataBase.cursor()):
curseur.execute('SELECT * FROM Facture F, Commande C WHERE F.numCommande=C.num AND C.superviseur =%s', arti)
return [Facture(f[0], f[1], f[2], f[3]).getDict() for f in curseur.fetchall()]
def recup_oeuvre():
response = {
"status": 200,
"oeuvre": oeuvre.expo(DataBase.cursor())
}
return jsonify(response)
def recup_fac(numCmd, curseur =DataBase.cursor()):
curseur.execute('SELECT * FROM Facture WHERE numCommande=%s',numCmd)
return [Facture(f[0],f[1],f[2],f[3]).getDict() for f in curseur.fetchall()]