def followsUser(id_current_user, id_user):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT * FROM follow WHERE user = '******' AND follow = '"+ str(id_user) +"'")).fetchall()
database.close(cnx)
if len(res) > 0:
return True
return False
def userFollows(user, follow):
cnx = database.init("./../data/bdd.sq3")
res = cnx[1].execute("SELECT * FROM follow WHERE user='******' AND follow='"+ str(follow) +"'").fetchall()
database.close(cnx)
if len(res) >= 1:
return True
return False
def stageEvolutionByEnterprise():
cnx = database.init()
cnx[1].execute(
"SELECT Stage.anne_universitaire_stage, Stage.nom_entreprise, COUNT(numero_stage) FROM Stage GROUP BY Stage.nom_entreprise, Stage.anne_universitaire_stage"
)
res = cnx[1].fetchall()
cnx[1].execute("SELECT anne_universitaire_stage FROM Stage")
ye = cnx[1].fetchall()
l = {"years": []}
for y in ye:
if y[0] not in l["years"]:
l["years"].append(y[0])
l["years"] = sorted(l["years"])
for re in res:
if re[1] not in l:
l[re[1]] = []
for y in l["years"]:
l[re[1]].append([y, 0])
for r in l[re[1]]:
if r[0] == re[0]:
r[1] += int(re[2])
for li in l:
li = sorted(li)
database.close(cnx)
return l
def removeCommentsForUser(ide):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM comments WHERE user='******'")
database.save(cnx)
database.close(cnx)
def getChapterInformations(id):
cnx = database.init("./../data/bdd.sq3")
res = (
cnx[1].execute("SELECT chapter.id, chapter.lesson, chapter.name FROM chapter WHERE id='" + id + "'")
).fetchone()
database.close(cnx)
return res
def getLayer(id):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT layer.id, user.name, chapter.name, layer.date, layer.validated FROM layer INNER JOIN chapter ON layer.chapter = chapter.id INNER JOIN user ON user.id = layer.user WHERE layer.id = '"+ str(id) +"'")).fetchone()
database.close(cnx)
lis = []
lis.append(res)
return lis
def getTeachersNumber():
cnx = database.init()
cnx[1].execute(
"SELECT COUNT(Enseignant.surnom_enseignant) FROM Enseignant")
res = cnx[1].fetchall()
database.close(cnx)
return res[0][0]
def stageEvolutionByEnterprise():
cnx = database.init()
cnx[1].execute("SELECT Stage.anne_universitaire_stage, Stage.nom_entreprise, COUNT(numero_stage) FROM Stage GROUP BY Stage.nom_entreprise, Stage.anne_universitaire_stage")
res = cnx[1].fetchall()
cnx[1].execute("SELECT anne_universitaire_stage FROM Stage")
ye = cnx[1].fetchall()
l = {"years" : []}
for y in ye:
if y[0] not in l["years"]:
l["years"].append(y[0])
l["years"] = sorted(l["years"])
for re in res:
if re[1] not in l:
l[re[1]] = []
for y in l["years"]:
l[re[1]].append([y, 0])
for r in l[re[1]]:
if r[0] == re[0]:
r[1]+=int(re[2])
for li in l:
li = sorted(li)
database.close(cnx)
return l
def addChapter(lesson, name):
if lesson == "" or name == "" or lesson == None or name == None:
return
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("INSERT INTO chapter(lesson, name) VALUES ('"+lesson+"','"+name+"')")
database.save(cnx)
database.close(cnx)
def stageEvolution():
cnx = database.init()
cnx[1].execute("SELECT anne_universitaire_stage, COUNT(numero_stage) FROM Stage GROUP BY anne_universitaire_stage")
res = cnx[1].fetchall()
l = sorted(list(res))
database.close(cnx)
return l
def removeLesson(ide):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM lesson WHERE id='"+ide+"'")
database.save(cnx)
cnx[1].execute("DELETE FROM chapter WHERE lesson='"+ide+"'")
database.save(cnx)
database.close(cnx)
def getEnterprisesNumber():
cnx = database.init()
cnx[1].execute(
"SELECT COUNT(Entreprise.numero_entreprise) FROM Entreprise")
res = cnx[1].fetchall()
database.close(cnx)
return res[0][0]
def addCalqueCreationNotification(_user, layer):
followers = user.getUserFollowed(_user)
cnx = database.init("./../data/bdd.sq3")
for follower in followers:
cnx[1].execute("INSERT INTO notification (user, notificator, type, layer) VALUES ('"+ str(follower[0]) +"', '"+ str(_user) +"', '3', '"+ str(layer) +"')")
database.save(cnx)
database.close(cnx)
def removeLayer(id, admin):
notification.addLayerSuppressionNotification(id, admin)
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM layer WHERE id ='"+ str(id) +"'")
#TODO: ajouter validation
database.save(cnx)
database.close(cnx)
os.remove("/data/layer/"+ str(id) +".html")
def deadminUser(id):
info = getUserInformations(id)
if info[7] == "True" and getAdminNumber() <= 1:
return
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("UPDATE user SET admin='False' WHERE id='" + id + "'")
database.save(cnx)
database.close(cnx)
def getLayersForChapter(id):
cnx = database.init("./../data/bdd.sq3")
l = (cnx[1].execute("SELECT layer.id, user.name, layer.date, layer.validated, layer.basedOn, user.id FROM layer LEFT JOIN user ON user.id = layer.user WHERE layer.chapter = '"+ str(id) +"' AND layer.validated = 'True'")).fetchall()
res = (cnx[1].execute("SELECT layer.id, user.name, layer.date, layer.validated, layer.basedOn, user.id FROM layer LEFT JOIN user ON user.id = layer.user WHERE layer.chapter = '"+ str(id) +"' AND NOT layer.validated = 'True'")).fetchall()
for re in res:
l.append(re)
database.close(cnx)
return l
def stageEvolution():
cnx = database.init()
cnx[1].execute(
"SELECT anne_universitaire_stage, COUNT(numero_stage) FROM Stage GROUP BY anne_universitaire_stage"
)
res = cnx[1].fetchall()
l = sorted(list(res))
database.close(cnx)
return l
def removeUser(id):
info = getUserInformations(id)
if info[7] == "True" and getAdminNumber() <= 1:
return
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM user WHERE id='" + id + "'")
database.save(cnx)
database.close(cnx)
comments.removeCommentsForUser(id)
notification.removeNotificationsForUser(id)
def getSagesNumber():
d = time.strftime("%Y")
lastyear = int(d)-1
firstyear = int(d)-2
d = str(firstyear)+"-"+str(lastyear)
cnx = database.init()
cnx[1].execute("SELECT COUNT(Stage.numero_stage) FROM Stage WHERE Stage.anne_universitaire_stage = '"+d+"'")
res= cnx[1].fetchall()
database.close(cnx)
return res[0][0]
def addLayer(basedOn, user, chapter, date, content):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("INSERT INTO layer(basedOn, user, chapter, date, validated) VALUES ('"+ str(basedOn) +"', '"+ str(user) +"','"+ str(chapter) +"','"+ str(date) +"', 'False')")
database.save(cnx)
database.close(cnx)
fil = open("./../data/layer/"+str(lastLayerId())+".html", "w")
render = unicode(content).encode("utf-8")
fil.write(render)
fil.close()
notification.addCalqueCreationNotification(user, lastLayerId())
def checkSession(request):
if request.get_cookie("credentials", secret=secret_cookie_key) == None or request.get_cookie("credentials", secret=secret_cookie_key) == "" :
return False
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT id, name, password FROM user WHERE admin = 'True'")).fetchall()
for re in res:
m = md5.new()
m.update(re[1]+re[2])
c = m.digest()
if c == request.get_cookie("credentials", secret=secret_cookie_key):
return re[0]
return False
def getSagesNumber():
d = time.strftime("%Y")
lastyear = int(d) - 1
firstyear = int(d) - 2
d = str(firstyear) + "-" + str(lastyear)
cnx = database.init()
cnx[1].execute(
"SELECT COUNT(Stage.numero_stage) FROM Stage WHERE Stage.anne_universitaire_stage = '"
+ d + "'")
res = cnx[1].fetchall()
database.close(cnx)
return res[0][0]
def checkSession(request):
if request.get_cookie("credentials", secret=secret_cookie_key) == None or request.get_cookie("credentials", secret=secret_cookie_key) == "" :
return False
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT id, name, password FROM user")).fetchall()
for re in res:
try:
m = md5.new()
except:
m = hashlib.md5(); #python3 correction
m.update(re[1].encode("utf-8")+re[2].encode("utf-8"))
c = m.digest()
if str(c) == str(request.get_cookie("credentials", secret=secret_cookie_key)):
return re[0]
return False
def getCommentsForLayer(ide):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT comments.id, comments.layer, chapter.name, user.name, comments.date, comments.content FROM comments INNER JOIN user ON user.id=comments.user INNER JOIN layer ON layer.id=comments.layer INNER JOIN chapter ON layer.chapter=chapter.id WHERE layer='"+str(ide)+"' ORDER BY comments.id DESC")).fetchall()
database.close(cnx)
return res
def addComment(layer, user, date, content):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("INSERT INTO comments(layer, user, date, content) VALUES ('"+layer+"', '"+user+"', '"+date+"', '"+content+"')")
database.save(cnx)
database.close(cnx)
def removeComment(ide, admin):
notification.addCommentSuppressionNotification(ide, admin)
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM comments WHERE id='"+ str(ide) +"'")
database.save(cnx)
database.close(cnx)
def addLayerValidationNotification(id, admin):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT user FROM layer WHERE id = '"+ str(id) +"'")).fetchone()
cnx[1].execute("INSERT INTO notification (user, notificator, type, layer) VALUES ('"+ str(res[0]) +"', '"+ str(admin) +"', '6', '"+ str(id) +"')")
database.save(cnx)
database.close(cnx)
def getChaptersForLesson(lesson):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT * FROM chapter WHERE lesson='" + lesson + "'")).fetchall()
database.close(cnx)
return res
def getCommentsForLayer(id):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT comments.id, user.id, user.name, comments.date, comments.content, user.picture FROM comments INNER JOIN user ON user.id = comments.user WHERE comments.layer='"+id+"'")).fetchall()
database.close(cnx)
return res
def addComment(user, layer, date, content):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("INSERT INTO comments(layer, user, date, content) VALUES ('"+str(layer)+"','"+str(user)+"','"+str(date)+"','"+str(content)+"')")
database.save(cnx)
database.close(cnx)
notification.addCommentNotification(user, layer)
def checkCredentials(user, password):
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT name, password FROM user WHERE name='"+user+"' AND password='******'")).fetchall()
if len(res) != 1:
return False
return True
def getLayersToValidate():
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT layer.id, user.name, chapter.name, layer.date, layer.validated FROM layer INNER JOIN chapter ON layer.chapter = chapter.id INNER JOIN user ON user.id = layer.user WHERE layer.validated = 'False'")).fetchall()
database.close(cnx)
return res
def validateLayer(id):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("UPDATE layer SET validated='True' WHERE id = '"+ str(id) +"'")
#TODO: ajouter notify user
database.save(cnx)
database.close(cnx)
def removeNotificationsForUser(ide):
cnx = database.init("./../data/bdd.sq3")
cnx[1].execute("DELETE FROM notification WHERE user='******'")
database.save(cnx)
database.close(cnx)
def getStudentsNumber():
cnx = database.init()
cnx[1].execute("SELECT COUNT(Etudiant.numero_etudiant) FROM Etudiant")
res = cnx[1].fetchall()
database.close(cnx)
return res[0][0]
def getChapters():
cnx = database.init("./../data/bdd.sq3")
res = (cnx[1].execute("SELECT * FROM chapter")).fetchall()
database.close(cnx)
return res
