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 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 removeCommentsForUser(ide): cnx = database.init("./../data/bdd.sq3") cnx[1].execute("DELETE FROM comments WHERE user='******'") 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 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 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 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 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 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 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 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 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 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 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 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 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 getLessonInformations(id): cnx = database.init("./../data/bdd.sq3") res = (cnx[1].execute("SELECT * FROM lesson WHERE id='"+id+"'")).fetchone() database.close(cnx) return res
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 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 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 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 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 addCommentNotification(_user, _layer): cnx = database.init("./../data/bdd.sq3") cnx[1].execute("INSERT INTO notification (user, notificator, type, layer) VALUES ('"+ str(layers.getLayerInformations(_layer)[1]) +"', '"+ str(_user) +"', '0', '"+ str(_layer) +"')") database.save(cnx) database.close(cnx)
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 addUnfollowNotification(user, userFollowed): cnx = database.init("./../data/bdd.sq3") cnx[1].execute("INSERT INTO notification (user, notificator, type) VALUES ('"+ str(userFollowed) +"', '"+ str(user) +"', '2')") 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 getChapters(): cnx = database.init("./../data/bdd.sq3") res = (cnx[1].execute("SELECT * FROM chapter")).fetchall() database.close(cnx) return res
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]