def get(self, *args, **kwargs):
try:
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
query = """SELECT %s FROM %s WHERE 1 """ % (self.__que__,
self.__tabla__)
# campos en clase
for campo in self.__where__:
if campo[1] == "LIKE": campo[2] = "%" + campo[2] + "%"
query += "AND %s %s '%s' " % (campo[0], campo[1], campo[2])
# campos en uri
for articulo, valor in kwargs.iteritems():
query += "AND %s = '%s' " % (articulo, valor)
# campos en metrodo get
for campo in self.__where_args__:
if campo[1] == "LIKE":
q = "%" + request.query.get(campo[0]) + "%"
else:
q = request.query.get(campo[0])
query += "AND %s %s '%s'" % (campo[0], campo[1], q)
if self.__group__:
query += " GROUP BY %s " % self.__group__
pagina = request.query.get("desde")
anterior = -1
siguiente = 50
if pagina:
query += " LIMIT %s, 50" % pagina
siguiente = int(pagina) + 50
anterior = int(pagina) - 50
elif self.__limit__:
query += " LIMIT %s " % self.__limit__
else:
query += " LIMIT 50"
datos = c.execute(query)
resultado = template(
self.__template__, {
"datos": datos.fetchall(),
"siguiente": siguiente,
"anterior": anterior
})
conn.close()
return resultado
except Exception as e:
conn.close()
return template('ERROR!<br /> {{error}}', error=str(e))
def get(self, *args, **kwargs):
try:
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
query = """SELECT %s FROM %s WHERE 1 """ % (self.__que__,self.__tabla__)
# campos en clase
for campo in self.__where__:
if campo[1] == "LIKE": campo[2] = "%" + campo[2] + "%"
query += "AND %s %s '%s' " % (campo[0],campo[1],campo[2])
# campos en uri
for articulo, valor in kwargs.iteritems():
query += "AND %s = '%s' " % (articulo, valor)
# campos en metrodo get
for campo in self.__where_args__:
if campo[1] == "LIKE":
q = "%" + request.query.get(campo[0]) + "%"
else:
q = request.query.get(campo[0])
query += "AND %s %s '%s'" % (campo[0],campo[1],q)
if self.__group__:
query += " GROUP BY %s " % self.__group__
pagina = request.query.get("desde")
anterior = -1
siguiente = 50
if pagina:
query += " LIMIT %s, 50" % pagina
siguiente = int(pagina) + 50
anterior = int(pagina) - 50
elif self.__limit__:
query += " LIMIT %s " % self.__limit__
else:
query += " LIMIT 50"
datos = c.execute(query)
resultado = template(self.__template__, {
"datos":datos.fetchall(),
"siguiente": siguiente,
"anterior": anterior
})
conn.close()
return resultado
except Exception as e:
conn.close()
return template('ERROR!<br /> {{error}}', error = str(e))
def xmeses():
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
query = """SELECT fecha FROM facturas ORDER by fecha ASC LIMIT 1 """
datos = c.execute(query)
import time
fecha = time.gmtime(int(datos.fetchone()[0]))
conn.close()
return template("templates/xmes.html", {"datos":fecha, "hoy":datetime.now()} )
def do_index():
# Connection to the database
conn = sqlite3.connect('base.db')
cursor = conn.cursor()
resultat = []
# Run the check_requete method
requete = check_requete(request.forms.get('activite'), request.forms.get('installation'))
for row in cursor.execute(requete):
resultat.append(row)
return template('tpl/result', resultat=resultat)
def xmeses():
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
query = """SELECT fecha FROM facturas ORDER by fecha ASC LIMIT 1 """
datos = c.execute(query)
import time
fecha = time.gmtime(int(datos.fetchone()[0]))
conn.close()
return template("templates/xmes.html", {
"datos": fecha,
"hoy": datetime.now()
})
def xmes(ano,mes,orden):
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
import calendar
from dateutil.relativedelta import relativedelta
primer_dia_de_mes_fecha = datetime.strptime("%s-%s-%s" % (ano, mes,1), "%Y-%m-%d")
ultimo_dia_de_mes_fecha = primer_dia_de_mes_fecha + relativedelta(months=1)
primer_dia_de_mes = calendar.timegm(primer_dia_de_mes_fecha.utctimetuple())
ultimo_dia_de_mes = calendar.timegm(ultimo_dia_de_mes_fecha.utctimetuple())
query = """SELECT * FROM facturas WHERE fecha > "%s" AND fecha < "%s" ORDER by %s ASC""" % \
(primer_dia_de_mes, ultimo_dia_de_mes, orden)
datos = c.execute(query)
return template("templates/xmes_listado.html", {"datos":datos} )
def xmes(ano, mes, orden):
conn = sqlite3.connect(config.getVar("dir_vault") + "facturas.db")
c = conn.cursor()
import calendar
from dateutil.relativedelta import relativedelta
primer_dia_de_mes_fecha = datetime.strptime("%s-%s-%s" % (ano, mes, 1),
"%Y-%m-%d")
ultimo_dia_de_mes_fecha = primer_dia_de_mes_fecha + relativedelta(months=1)
primer_dia_de_mes = calendar.timegm(primer_dia_de_mes_fecha.utctimetuple())
ultimo_dia_de_mes = calendar.timegm(ultimo_dia_de_mes_fecha.utctimetuple())
query = """SELECT * FROM facturas WHERE fecha > "%s" AND fecha < "%s" ORDER by %s ASC""" % \
(primer_dia_de_mes, ultimo_dia_de_mes, orden)
datos = c.execute(query)
return template("templates/xmes_listado.html", {"datos": datos})
def show_id(item):
retour = ""
ret = []
conn = mysql.connector.connect(host="infoweb", user="******", password="******", database="E145425W") #connexion a la base de donnees
c = conn.cursor()
c.execute("SELECT e.nom FROM equipement e JOIN equip_activ ea ON e.id = ea.id_equip JOIN activite a ON ea.id_activ = a.idAct WHERE a.nom LIKE '%"+item+"%'")
#on cherche le nom des equipements pour l'activite recherchee
result = c.fetchall()
#cela nous renvoie un tableau des differentes reponses
for row in result:
if row[0] not in ret:
retour = retour + str(row[0]) + " | \n"
#on ajoute a la reponse chaque valeur du tableau
ret.append(row[0])
c.close()
if not result:
#si la valeur de la recherche n'est pas presente dans la base de donnees
return 'This item number does not exist!'
else:
return template('Résultat : <br> {{itemr}}', itemr=retour)
def index():
# Connection to the database
conn = sqlite3.connect('base.db')
cursor = conn.cursor()
activite = []
installation = []
# Query in the database -> activite table
for row in cursor.execute("select nom from activite group by nom"):
if row not in activite:
if(row != ""):
activite.append(row)
# Query in the database -> installation table
for row in cursor.execute("select ville from installation group by nom"):
if row not in installation:
if(row != ""):
installation.append(row)
# Sort results
activite.sort()
installation.sort()
return template('tpl/index', activite=activite, installation=installation)
def search():
"""
Méthode qui fait les requêtes sql pour afficher les résultats en fonction de la recherche effectuée.
Elle récupère ce qui a été sélectionner sur le template tmpt_search.
"""
try:
#on se connecte à la bd.
conn = sqlite3.connect("../bddata/database.db")
c = conn.cursor()
#on récupère la ville et l'activité qui ont été sélectionné.
city = request.forms.get('city')
activity = request.forms.get('activity')
#Si l'utilisateur a sélectionné seulement une city on affiche toutes les activités de cette city
if (city != "all" and activity == "all"):
req = c.execute(
"SELECT a.activity, i.name, e.nomEq, a.city from installations i, equipements e, activities a where a.city=e.city and e.numInstal=i.num and a.city=\""
+ city + "\";")
#Si l'utilisateur a sélectionné une city et une activité, on affiche toutes les activités correspondant à celle sélectionnée dans la city.
if (city != "all" and activity != "all"):
req = c.execute(
"SELECT a.activity, i.name, e.nomEq, a.city from installations i, equipements e, activities a where a.city=e.city and e.numInstal=i.num and a.city=\""
+ city + "\" and a.activity=\"" + activity + "\";")
#Si l'utilisateur a sélectionné seulement une activité on affiche toutes les citys où l'on peut pratiquer cette activité
if (city == "all" and activity != "all"):
req = c.execute(
"SELECT \"" + activity +
"\", i.name, e.nomEq, a.city from installations i, equipements e, activities a where a.city=e.city and e.numInstal=i.num and a.activity=\""
+ activity + "\" limit 20;")
if (city == "all" and activity == "all"):
req = []
if req != []:
list = [a for a in req]
c.close()
#on envoie ces données au template tmpt_correct.
return template('tpl/tmpt_results', results=list)
else:
return ("Veuillez rentrer au moins une ville ou une activité")
except Exception as error:
print("Unexpected error: {0}".format(error))
return ("erreur")
def index():#on affiche la page principale avec le formulaire
ville="''"
activite="''"
maps = []
table = []
conn = sqlite3.connect('static/sports_pdl.db')# in order to have access to the database you must connect
cursor = conn.cursor()
cursor.execute("""SELECT DISTINCT code_postal, commune FROM installation WHERE code_postal != '' AND commune != '' ORDER BY code_postal ASC""")
#request that return the postal code and the name of the city
tab = []
for x in cursor.fetchall(): #separator for each row of the cursor
tab.append(x[0]+" - "+x[1])
cursor2 = conn.cursor()
cursor2.execute("""SELECT DISTINCT act_code, lib_act FROM activite ORDER BY act_code ASC""")
#request that return the postal code and the name of the city
act = []
for y in cursor2.fetchall(): #separator for each row of the cursor
act.append(str(y[0])+" - "+y[1])
return template('index', tab=json.dumps(tab), act=json.dumps(act),num_act=0,code_postal=0,table=json.dumps(table),maps=json.dumps(maps),ville=ville,activite=activite)
def home():
"""
Lancement de la page où l'on doit sélectionner une city et/ou une activité,
la page d'acceuil, en lui envoyant les données des villes et des activites.
"""
conn = sqlite3.connect("../bddata/database.db")
c = conn.cursor()
city = c.execute("SELECT a.city from activities a;")
l_city = [a for a in city]
cities = []
for a in l_city:
cities.append(a[0])
cities = sorted(set(cities))
activity = c.execute("SELECT a.activity from activities a;")
l_activity = [a for a in activity]
c.close()
activities = []
for a in l_activity:
activities.append(a[0])
activities = sorted(set(activities))
return template('tpl/tmpt_search', city=cities, activity=activities)
def index(postal,num_act):
conn = sqlite3.connect('static/sports_pdl.db')# in order to have access to the database you must connect
ville="''"
activite="''"
maps = []
table = []
#return template('<b>code postal = {{postal}} et <b>num act = {{act}}</b>!</b>!',postal=postal, num_act = num_act)
#REQUETES RECUP DONNEEE
if(postal != 0):#le client a choisi par code postal
cursor3 = conn.cursor()
cursor3.execute("""SELECT lib_act, numero_voie, nom_voie, lieu_dit, commune, nom_usuel_inst, longitude, latitude FROM installation i, equipement e, activite a WHERE i.numero_inst=e.numero_inst AND e.equipement_id=a.equipement_id AND code_postal ="""+postal+""" GROUP BY lib_act, numero_voie, nom_voie, lieu_dit, commune, nom_usuel_inst""")
i=0
for x in cursor3.fetchall(): #separator for each row of the cursor
temp = [i,x[5],x[0],str(x[1])+" "+x[2]+" "+x[3]+" "+str(postal)+" "+x[4]]#on récupère le nom de l'installation, l'activité et on concatène l'adresse
table.append(temp)
temp = [i,x[6],x[7]]
maps.append(temp)
i = i+1
cursor31 = conn.cursor()
cursor31.execute("""SELECT commune FROM installation WHERE code_postal = """+postal+""" LIMIT 1""")
for x in cursor31.fetchall(): #separator for each row of the cursor
ville = "'"+x[0]+"'"
if(num_act != 0):#le client a choisi par activité
cursor4 = conn.cursor()
cursor4.execute("""SELECT numero_voie, nom_voie, lieu_dit,code_postal, commune, nom_usuel_inst, longitude, latitude FROM installation i, equipement e, activite a WHERE i.numero_inst=e.numero_inst AND e.equipement_id=a.equipement_id AND act_code="""+num_act+""" GROUP BY numero_voie, nom_voie,lieu_dit, code_postal, commune, nom_usuel_inst""")
i = 0
for x in cursor4.fetchall(): #separator for each row of the cursor
temp = [i,x[5],x[4],str(x[0])+" "+x[1]+" "+x[2]+" "+str(x[3])+" "+x[4]]#on récupère le nom de l'installation, on concatène l'adresse et on récupère la commune à part
table.append(temp)
temp = [i,x[6],x[7]]
maps.append(temp)
i = i+1
cursor41 = conn.cursor()
cursor41.execute("""SELECT lib_act FROM activite WHERE act_code="""+num_act+""" LIMIT 1""")
for x in cursor41.fetchall(): #separator for each row of the cursor
activite = "'"+x[0]+"'"
#FIN REQUETES
#je récupère les données pour un nouvelle recherche si besoin
cursor = conn.cursor()
cursor.execute("""SELECT DISTINCT code_postal, commune FROM installation WHERE code_postal != '' AND commune != '' ORDER BY code_postal ASC""")
#request that return the postal code and the name of the city
tab = []
for x in cursor.fetchall(): #separator for each row of the cursor
tab.append(x[0]+" - "+x[1])
cursor2 = conn.cursor()
cursor2.execute("""SELECT DISTINCT act_code, lib_act FROM activite ORDER BY act_code ASC""")
#request that return the postal code and the name of the city
act = []
for y in cursor2.fetchall(): #separator for each row of the cursor
act.append(str(y[0])+" - "+y[1])
return template('index', tab=json.dumps(tab), act=json.dumps(act),num_act=num_act,code_postal=postal,table=json.dumps(table),maps=json.dumps(maps),ville=ville,activite=activite)
def index(name):
return template("<b>Hello {{name}}</b>!", name=name)
def catch_all(path):
# Return static files.
if path in STATIC_FILES:
mimetype = MIME_TYPES.get(Path(path).suffix, 'auto')
return static_file(path, STATIC_DIR, mimetype=mimetype)
# Parse path.
run_dir, test_dir = parse_path(path)
if run_dir and not test_dir:
# Show run page.
run = database.load_run(database.run_report_file(run_dir))
if not run:
return template('error',
message=f'Run "{run_dir}" does not exist.')
nav = [{
'title': 'Home',
'link': '/'
}, {
'title': 'Run: ' + run_dir,
'link': '/' + run_dir
}]
stats = run_stats(run)
return template('run',
nav=nav,
tag_titles=TAG_TITLES,
stats=stats,
run_dir=run_dir,
run=run,
run_tags=database.run_tags,
format_date=format_date,
format_duration=format_duration)
elif run_dir and test_dir:
# Show test page.
test = database.load_test(database.test_report_file(run_dir, test_dir))
if not test:
return template(
'error',
message=f'Test "{test_dir}" does not exist for run "{run_dir}".'
)
action = request.query.get('action', None)
if action == 'compare':
# Compare images.
image = Path(request.query['image']).as_posix()
result_image = Path('/result') / run_dir / test_dir / image
error_image = Path(str(result_image) + config.ERROR_IMAGE_SUFFIX)
ref_dir = Path(test['ref_dir']).relative_to(database.ref_dir)
ref_image = Path('/ref') / ref_dir / image
jeri_data = create_jeri_data(result_image, ref_image, error_image)
return template('compare',
image=str(image),
jeri_data=json.dumps(jeri_data))
else:
nav = [{
'title': 'Home',
'link': '/'
}, {
'title': 'Run: ' + run_dir,
'link': '/' + run_dir
}, {
'title': 'Test: ' + test_dir,
'link': '/' + run_dir + '/' + test_dir
}]
stats = test_stats(test)
ref_dir = str(
Path(test['ref_dir']).relative_to(database.ref_dir).as_posix())
return template('test',
nav=nav,
stats=stats,
run_dir=run_dir,
test_dir=test_dir,
ref_dir=ref_dir,
test=test,
format_duration=format_duration)
else:
return template('error', message='Invalid URL.')
return str([run_dir, test_dir])
def login_page():
page = login.Login('login', 1)
return template('index', page=page)
def map(lat, long):
return template('tpl/map', lat = lat, long = long)
def login():
return template('tpl/login')
def module_subpage(pagepath):
page=instantiate_page(pagepath)
if not page.special_return:
return template('index', page=page)
else:
return page.special_return
def display_forum():
id = request.query.id
page = request.query.page or '1'
return template('Forum ID: {{id}} (page {{page}})', id=id, page=page)
