def getInsee(codeStation):
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute('SELECT insee FROM tranche WHERE debut <= ' +
str(codeStation) + ' AND fin >= ' + str(codeStation))
tranche = requete.fetchone()
return tranche[0]
def recalculNbStationsResumeOfResume(periodeOrigine, periodeFinale, dateCourante):
dateConso = dateCourante.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateCourante + datetime.timedelta(minutes=periodeFinale)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
#On récupère les conso
proprietes = ['nbStation', 'nbStationDetecte']
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT "+', '.join(proprietes)+" FROM `resumeConso` WHERE duree = "+str(periodeOrigine)+" and date >= '"+dateConso+"' and date < '"+dateConsoFin+"' order by date")
consos = requete.fetchall()
infosCourantes = {}
for row in consos:
for i, cle in enumerate(proprietes):
if not row[i] is None:
valeurProp = int(row[i])
if cle in infosCourantes:
infosCourantes[cle] = max(infosCourantes[cle], valeurProp)
else:
infosCourantes[cle] = valeurProp
#Et on met à jour
if infosCourantes != {} and 'nbStation' in infosCourantes and 'nbStationDetecte' in infosCourantes:
nbStations = infosCourantes['nbStation']
nbStationsDetecte = infosCourantes['nbStationDetecte']
requete = mysql.cursor()
requete.execute('UPDATE `resumeConso` SET nbStation = '+str(nbStations) + ', nbStationDetecte = '+str(nbStationsDetecte) + ' WHERE \
date = "'+dateConso+'" and duree = '+str(periodeFinale))
def calculerResumeOfResume(dateConsoDT, dureeConsoOrigine, dureeConsoFinale):
dateConso = dateConsoDT.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateConsoDT + datetime.timedelta(minutes=dureeConsoFinale)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
#On récupère les conso
proprietes = [
'nbBikeMin', 'nbBikeMax', 'nbBikeMoyenne', 'nbBikePris', 'nbBikeRendu',
'nbEBikeMin', 'nbEBikeMax', 'nbEBikeMoyenne', 'nbEBikePris',
'nbEBikeRendu', 'nbFreeEDockMin', 'nbFreeEDockMax',
'nbFreeEDockMoyenne', 'nbEDock', 'nbBikeOverflowMin',
'nbBikeOverflowMax', 'nbBikeOverflowMoyenne', 'nbEBikeOverflowMin',
'nbEBikeOverflowMax', 'nbEBikeOverflowMoyenne', 'maxBikeOverflow'
]
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT code, " + ', '.join(proprietes) +
" FROM `resumeStatus` WHERE duree = " +
str(dureeConsoOrigine) + " and date >= '" + dateConso +
"' and date < '" + dateConsoFin + "' order by code")
consos = requete.fetchall()
precedentCode = None
infosCourantes = {}
for row in consos:
codeStation = int(row[0])
if precedentCode != None and precedentCode != codeStation: #On change de station
#On enregistre la conso
enregistrerConso(mysql, proprietes, infosCourantes, precedentCode,
dateConso, dureeConsoFinale)
infosCourantes = {}
for i, cle in enumerate(proprietes):
if cle[-7:] == 'Moyenne':
valeurProp = float(row[i + 1])
else:
valeurProp = int(row[i + 1])
if cle in infosCourantes:
if cle[-7:] == 'Moyenne':
infosCourantes[cle].append(valeurProp)
elif cle[-3:] == 'Min':
infosCourantes[cle] = min(infosCourantes[cle], valeurProp)
elif cle[
-3:] == 'Max' or cle == 'maxBikeOverflow' or cle == 'nbEDock':
infosCourantes[cle] = max(infosCourantes[cle], valeurProp)
else:
infosCourantes[cle] += valeurProp
else:
if cle[-7:] == 'Moyenne':
infosCourantes[cle] = [valeurProp]
else:
infosCourantes[cle] = valeurProp
precedentCode = codeStation
#Et on oublie pas le dernier !
enregistrerConso(mysql, proprietes, infosCourantes, precedentCode,
dateConso, dureeConsoFinale)
def calculerResumeOfResumeConso(dateConsoDT, dureeConsoOrigine,
dureeConsoFinale):
dateConso = dateConsoDT.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateConsoDT + datetime.timedelta(minutes=dureeConsoFinale)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
#On récupère les conso
proprietes = [
'nbStation', 'nbStationDetecte', 'nbBikeMin', 'nbBikeMax',
'nbBikeMoyenne', 'nbEBikeMin', 'nbEBikeMax', 'nbEBikeMoyenne',
'nbFreeEDockMin', 'nbFreeEDockMax', 'nbFreeEDockMoyenne', 'nbEDock'
]
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT " + ', '.join(proprietes) +
" FROM `resumeConso` WHERE duree = " +
str(dureeConsoOrigine) + " and date >= '" + dateConso +
"' and date < '" + dateConsoFin + "' order by date")
consos = requete.fetchall()
precedentCode = None
infosCourantes = {}
for row in consos:
for i, cle in enumerate(proprietes):
if cle[-7:] == 'Moyenne':
valeurProp = float(row[i])
else:
valeurProp = int(row[i])
if cle in infosCourantes:
if cle[-7:] == 'Moyenne':
infosCourantes[cle].append(valeurProp)
elif cle[-3:] == 'Min':
infosCourantes[cle] = min(infosCourantes[cle], valeurProp)
else:
infosCourantes[cle] = max(infosCourantes[cle], valeurProp)
else:
if cle[-7:] == 'Moyenne':
infosCourantes[cle] = [valeurProp]
else:
infosCourantes[cle] = valeurProp
#Et on enregistre
if infosCourantes != {}:
strValeurs = ""
for i, cle in enumerate(proprietes):
if cle[-7:] == 'Moyenne':
data = infosCourantes[cle]
moyenne = sum(data) / max(len(data), 1)
infosCourantes[cle] = moyenne
if i != 0:
strValeurs += ', '
strValeurs += str(infosCourantes[cle])
requete = mysql.cursor()
requete.execute('INSERT INTO `resumeConso` (`id`, `date`, `duree`, ' +
', '.join(proprietes) + ') VALUES \
(NULL, "' + dateConso + '", ' + str(dureeConsoFinale) + ', ' +
strValeurs + ')')
def nettoyerInstantanne(date):
dateStr = date.strftime("%Y-%m-%d %H:%M:%S")
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute('DELETE FROM status WHERE idConso IN ( \
SELECT id FROM statusConso \
WHERE date < "' + dateStr + '")')
requete = mysql.cursor()
requete.execute('DELETE FROM statusConso WHERE date < "' + dateStr + '"')
def nettoyerConso(date, filtre):
dateStr = date.strftime("%Y-%m-%d %H:%M:%S")
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute('DELETE FROM resumeStatus WHERE date < "' + dateStr +
'" AND (' + filtre + ')')
requete = mysql.cursor()
requete.execute('DELETE FROM resumeConso WHERE date < "' + dateStr +
'" AND (' + filtre + ')')
def recalculNbStationsResume(periode, dateCourante):
dateConso = dateCourante.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateCourante + datetime.timedelta(minutes=periode)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
proprietes = ['state', 'nbEDock', 'nbBike', 'nbEBike', 'nbFreeEDock']
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT s."+', s.'.join(proprietes)+", c.date, c.id \
FROM status s \
INNER JOIN `statusConso` c ON c.id = s.idConso \
WHERE (c.`date` >= '"+dateConso+"' AND c.`date` < '"+dateConsoFin+"') \
ORDER BY c.id ASC")
statusConso = requete.fetchall()
nbStations = 0
nbStationsDetecte = 0
nbStationsMax = 0
nbStationsDetecteMax = 0
precedenteDate = None
precedenteConso = None
for row in statusConso:
if precedenteDate != None and precedenteDate != row[5]:
#On met à jour la conso
requete = mysql.cursor()
requete.execute('UPDATE statusConso SET nbStation = '+str(nbStations) + ', nbStationDetecte = '+str(nbStationsDetecte) + ' WHERE id = '+str(precedenteConso))
#On calcule le resume
nbStationsMax = max(nbStationsMax, nbStations)
nbStationsDetecteMax = max(nbStationsDetecteMax, nbStationsDetecte)
#Et on prépare la suivante
nbStations = 0
nbStationsDetecte = 0
if row[0] is None or row[0] == 'Operative':
nbStations += 1
if row[1] > 0 and (row[2] + row[3] + row[4]) > 0:
nbStationsDetecte += 1
precedenteDate = row[5]
precedenteConso = row[6]
if precedenteDate != None:
#on oublie pas la dernière
requete = mysql.cursor()
requete.execute('UPDATE statusConso SET nbStation = '+str(nbStations) + ', nbStationDetecte = '+str(nbStationsDetecte) + ' WHERE id = '+str(precedenteConso))
#On calcule le resume
nbStationsMax = max(nbStationsMax, nbStations)
nbStationsDetecteMax = max(nbStationsDetecteMax, nbStationsDetecte)
requete = mysql.cursor()
requete.execute('UPDATE resumeConso SET nbStation = '+str(nbStations) + ', nbStationDetecte = '+str(nbStationsDetecte) + ' WHERE date = "'+str(dateConso)+'" AND duree = '+str(periode))
if cle in infosCourantes:
infosCourantes[cle] = max(infosCourantes[cle], valeurProp)
else:
infosCourantes[cle] = valeurProp
#Et on met à jour
if infosCourantes != {} and 'nbStation' in infosCourantes and 'nbStationDetecte' in infosCourantes:
nbStations = infosCourantes['nbStation']
nbStationsDetecte = infosCourantes['nbStationDetecte']
requete = mysql.cursor()
requete.execute('UPDATE `resumeConso` SET nbStation = '+str(nbStations) + ', nbStationDetecte = '+str(nbStationsDetecte) + ' WHERE \
date = "'+dateConso+'" and duree = '+str(periodeFinale))
#On commence par avoir la date min et max
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT MIN(date), MAX(date) FROM `statusConso`")
bornesDate = requete.fetchone()
dateMin = bornesDate[0].replace(microsecond = 0, second = 0)
dateMax = bornesDate[1].replace(microsecond = 0, second = 0)
print("******* Bornes *******")
print("Début = "+str(dateMin))
print("Fin = "+str(dateMax))
print("******* Bornes *******")
#Périodes à calculer
periodes = [5, 15, 60, 360]
periodePrecedente = 0
for periode in periodes:
universal_newlines=True,
shell=True)
return ast.literal_eval(result.stdout)
def filetime_to_dt(s):
return datetime.datetime.fromtimestamp(
(s - 116444736000000000) // 10000000)
def version_dict_to_str(dict):
return str(dict["m_build"]) + "." + str(dict["m_major"]) + "." + str(
dict["m_minor"]) + "." + str(dict["m_revision"])
mydb = getMysqlConnection()
os.chdir("./Replays/")
for file in glob.glob("*.StormReplay"):
try:
header = out("python3 -m heroprotocol --header \"" + str(file) + "\"")
details = out("python3 -m heroprotocol --details \"" + str(file) +
"\"")
version = version_dict_to_str(header["m_version"])
#lets check if the map name exist yet
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM maps WHERE map_name =\"" +
details["m_title"].decode("utf-8") + "\"")
myresult = mycursor.fetchall()
if (mycursor.rowcount == 0):
def creerDumpData(dateDebut):
dateDebut = dateDebut.replace(microsecond=0, second=0, minute=0, hour=0)
dateDebutStr = dateDebut.strftime("%Y-%m-%d %H:%M:%S")
dateDebutNomFichier = dateDebut.strftime("%Y-%m-%d")
dateFin = dateDebut + datetime.timedelta(days=1)
dateFinStr = dateFin.strftime("%Y-%m-%d %H:%M:%S")
nomFichier = '../dump/' + dateDebutNomFichier + '-data.db'
conn = sqlite3.connect(nomFichier)
mysql = getMysqlConnection()
#Creation des tables
c = conn.cursor()
creerStationTable(c)
c.execute('''
CREATE TABLE `status` (
`id` int(11) NOT NULL,
`code` int(11) NOT NULL,
`idConso` int(11) NOT NULL,
`state` varchar(32) NOT NULL,
`nbBike` int(3) NOT NULL,
`nbEBike` int(3) NOT NULL,
`nbFreeEDock` int(3) NOT NULL,
`nbEDock` int(3) NOT NULL,
`nbBikeOverflow` int(3) NOT NULL,
`nbEBikeOverflow` int(3) NOT NULL,
`maxBikeOverflow` int(3) NOT NULL
);
''')
c.execute('''
CREATE TABLE `statusConso` (
`id` int(11) NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`nbStation` int(11) DEFAULT NULL,
`nbStationDetecte` int(11) DEFAULT NULL,
`nbBike` int(11) DEFAULT NULL,
`nbEbike` int(11) DEFAULT NULL,
`nbFreeEDock` int(11) DEFAULT NULL,
`nbEDock` int(11) DEFAULT NULL
);
''')
conn.commit()
#On va récupérer chaque donnée de chaque table
creerStationData(conn, mysql)
c = conn.cursor()
requete = mysql.cursor()
requete.execute(
'SELECT id, date, nbStation, nbStationDetecte, nbBike, nbEbike, nbFreeEDock, nbEDock \
FROM statusConso \
WHERE date >= "' + dateDebutStr + '" AND date < "' + dateFinStr + '"')
stations = requete.fetchall()
for station in stations:
values = []
for cell in station:
values.append(val(cell))
c.execute(
'INSERT INTO statusConso (id, date, nbStation, nbStationDetecte, nbBike, nbEbike, nbFreeEDock, nbEDock) VALUES \
(' + values[0] + ', ' + values[1] + ', ' + values[2] + ', ' +
values[3] + ', ' + values[4] + ', ' + values[5] + ', ' +
values[6] + ', ' + values[7] + ')')
conn.commit()
c = conn.cursor()
requete = mysql.cursor()
requete.execute(
'SELECT s.id, s.code, s.idConso, s.state, s.nbBike, s.nbEBike, s.nbFreeEDock, s.nbEDock, s.nbBikeOverflow, s.nbEBikeOverflow, s.maxBikeOverflow \
FROM status s \
INNER JOIN `statusConso` c ON c.id = s.idConso \
WHERE (c.`date` >= "' + dateDebutStr + '" AND c.`date` < "' + dateFinStr +
'")')
statuts = requete.fetchall()
for statut in statuts:
values = []
for cell in statut:
values.append(val(cell))
c.execute(
'INSERT INTO status (id, code, idConso, state, nbBike, nbEBike, nbFreeEDock, nbEDock, nbBikeOverflow, nbEBikeOverflow, maxBikeOverflow) VALUES \
(' + ', '.join(values) + ')')
conn.commit()
#Et on ferme le fichier
c.close()
def creerDumpConso(dateDebut):
dateDebut = dateDebut.replace(microsecond=0, second=0, minute=0, hour=0)
dateDebutStr = dateDebut.strftime("%Y-%m-%d %H:%M:%S")
dateDebutNomFichier = dateDebut.strftime("%Y-%m-%d")
dateFin = dateDebut + datetime.timedelta(days=1)
dateFinStr = dateFin.strftime("%Y-%m-%d %H:%M:%S")
nomFichier = '../dump/' + dateDebutNomFichier + '-conso.db'
conn = sqlite3.connect(nomFichier)
mysql = getMysqlConnection()
#Creation des tables
c = conn.cursor()
creerStationTable(c)
c.execute('''
CREATE TABLE `resumeConso` (
`id` int(11) NOT NULL,
`date` datetime NOT NULL,
`duree` int(4) NOT NULL,
`nbStation` int(11) NOT NULL,
`nbStationDetecte` int(11) DEFAULT NULL,
`nbBikeMin` int(11) NOT NULL,
`nbBikeMax` int(11) NOT NULL,
`nbBikeMoyenne` int(11) NOT NULL,
`nbEBikeMin` int(11) NOT NULL,
`nbEBikeMax` int(11) NOT NULL,
`nbEBikeMoyenne` int(11) NOT NULL,
`nbFreeEDockMin` int(11) NOT NULL,
`nbFreeEDockMax` int(11) NOT NULL,
`nbFreeEDockMoyenne` int(11) NOT NULL,
`nbEDock` int(11) NOT NULL
);
''')
c.execute('''
CREATE TABLE `resumeStatus` (
`id` int(11) NOT NULL,
`code` int(11) NOT NULL,
`date` datetime NOT NULL,
`duree` int(4) NOT NULL,
`nbBikeMin` int(3) NOT NULL,
`nbBikeMax` int(3) NOT NULL,
`nbBikeMoyenne` decimal(5,2) NOT NULL,
`nbBikePris` int(3) NOT NULL,
`nbBikeRendu` int(3) NOT NULL,
`nbEBikeMin` int(3) NOT NULL,
`nbEBikeMax` int(3) NOT NULL,
`nbEBikeMoyenne` decimal(5,2) NOT NULL,
`nbEBikePris` int(3) NOT NULL,
`nbEBikeRendu` int(3) NOT NULL,
`nbFreeEDockMin` int(3) NOT NULL,
`nbFreeEDockMax` int(3) NOT NULL,
`nbFreeEDockMoyenne` decimal(5,2) NOT NULL,
`nbEDock` int(3) NOT NULL,
`nbBikeOverflowMin` int(3) NOT NULL,
`nbBikeOverflowMax` int(3) NOT NULL,
`nbBikeOverflowMoyenne` decimal(5,2) NOT NULL,
`nbEBikeOverflowMin` int(3) NOT NULL,
`nbEBikeOverflowMax` int(3) NOT NULL,
`nbEBikeOverflowMoyenne` decimal(5,2) NOT NULL,
`maxBikeOverflow` int(3) NOT NULL
);
''')
conn.commit()
#On va récupérer chaque donnée de chaque table
creerStationData(conn, mysql)
c = conn.cursor()
requete = mysql.cursor()
requete.execute(
'SELECT id, date, duree, nbStation, nbStationDetecte, nbBikeMin, nbBikeMax, nbBikeMoyenne, nbEBikeMin, nbEBikeMax, nbEBikeMoyenne, nbFreeEDockMin, nbFreeEDockMax, nbFreeEDockMoyenne, nbEDock \
FROM resumeConso \
WHERE date >= "' + dateDebutStr + '" AND date < "' + dateFinStr + '"')
stations = requete.fetchall()
for station in stations:
values = []
for cell in station:
values.append(val(cell))
c.execute(
'INSERT INTO resumeConso (id, date, duree, nbStation, nbStationDetecte, nbBikeMin, nbBikeMax, nbBikeMoyenne, nbEBikeMin, nbEBikeMax, nbEBikeMoyenne, nbFreeEDockMin, nbFreeEDockMax, nbFreeEDockMoyenne, nbEDock) VALUES \
(' + ', '.join(values) + ')')
conn.commit()
c = conn.cursor()
requete = mysql.cursor()
requete.execute(
'SELECT id, code, date, duree, nbBikeMin, nbBikeMax, nbBikeMoyenne, nbBikePris, nbBikeRendu, nbEBikeMin, nbEBikeMax, nbEBikeMoyenne, nbEBikePris, nbEBikeRendu, nbFreeEDockMin, nbFreeEDockMax, nbFreeEDockMoyenne, nbEDock, nbBikeOverflowMin, nbBikeOverflowMax, nbBikeOverflowMoyenne, nbEBikeOverflowMin, nbEBikeOverflowMax, nbEBikeOverflowMoyenne, maxBikeOverflow \
FROM resumeStatus \
WHERE date >= "' + dateDebutStr + '" AND date < "' + dateFinStr + '"')
stations = requete.fetchall()
for station in stations:
values = []
for cell in station:
values.append(val(cell))
c.execute(
'INSERT INTO resumeStatus (id, code, date, duree, nbBikeMin, nbBikeMax, nbBikeMoyenne, nbBikePris, nbBikeRendu, nbEBikeMin, nbEBikeMax, nbEBikeMoyenne, nbEBikePris, nbEBikeRendu, nbFreeEDockMin, nbFreeEDockMax, nbFreeEDockMoyenne, nbEDock, nbBikeOverflowMin, nbBikeOverflowMax, nbBikeOverflowMoyenne, nbEBikeOverflowMin, nbEBikeOverflowMax, nbEBikeOverflowMoyenne, maxBikeOverflow) VALUES \
(' + ', '.join(values) + ')')
conn.commit()
#Et on ferme le fichier
c.close()
def calculerResume(dateConsoDT, dureeConso):
dateConso = dateConsoDT.strftime("%Y-%m-%d %H:%M:%S")
minuteAvant = dateConsoDT - datetime.timedelta(minutes=1)
dateConsoAvant = minuteAvant.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateConsoDT + datetime.timedelta(minutes=dureeConso)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
proprietes = [
'nbBike', 'nbEBike', 'nbFreeEDock', 'nbEDock', 'nbBikeOverflow',
'nbEBikeOverflow', 'maxBikeOverflow'
]
mysql = getMysqlConnection()
#On récupère la minute d'avant pour les bases de conso
requete = mysql.cursor()
requete.execute("SELECT s.code, s." + ', s.'.join(proprietes) + " \
FROM status s \
INNER JOIN `statusConso` c ON c.id = s.idConso \
WHERE (c.`date` >= '" + dateConsoAvant + "' AND c.`date` < '" + dateConso +
"') \
ORDER BY c.id DESC")
statusConsoPrecedent = requete.fetchall()
precedents = {}
for row in statusConsoPrecedent:
codeStation = int(row[0])
for i, cle in enumerate(proprietes):
if cle in ['nbBike', 'nbEBike']:
valeurProp = int(row[i + 1])
if not codeStation in precedents:
precedents[codeStation] = {}
precedents[codeStation][cle] = valeurProp
requete = mysql.cursor()
requete.execute("SELECT s.code, s." + ', s.'.join(proprietes) + " \
FROM status s \
INNER JOIN `statusConso` c ON c.id = s.idConso \
WHERE (c.`date` >= '" + dateConso + "' AND c.`date` < '" + dateConsoFin +
"') \
ORDER BY c.id ASC")
statusConso = requete.fetchall()
bikeList = {}
for row in statusConso:
codeStation = int(row[0])
for i, cle in enumerate(proprietes):
valeurProp = int(row[i + 1])
if cle in ['nbEDock', 'maxBikeOverflow']:
bikeList[codeStation][cle] = valeurProp
else:
if codeStation not in bikeList:
bikeList[codeStation] = {
cle: {
'data': [],
'min': valeurProp,
'max': valeurProp
}
}
if cle in ['nbBike', 'nbEBike']:
bikeList[codeStation][cle]['pris'] = 0
bikeList[codeStation][cle]['remis'] = 0
elif cle not in bikeList[codeStation]:
bikeList[codeStation][cle] = {
'data': [],
'min': valeurProp,
'max': valeurProp
}
if cle in ['nbBike', 'nbEBike']:
bikeList[codeStation][cle]['pris'] = 0
bikeList[codeStation][cle]['remis'] = 0
else:
bikeList[codeStation][cle]['max'] = max(
bikeList[codeStation][cle]['max'], valeurProp)
bikeList[codeStation][cle]['min'] = min(
bikeList[codeStation][cle]['min'], valeurProp)
if cle in ['nbBike', 'nbEBike']:
if codeStation not in precedents:
precedents[codeStation] = {}
if cle in precedents[codeStation]:
delta = valeurProp - precedents[codeStation][cle]
if delta > 0:
bikeList[codeStation][cle]['remis'] += delta
if delta < 0:
bikeList[codeStation][cle]['pris'] -= delta
precedents[codeStation][cle] = valeurProp
bikeList[codeStation][cle]['data'].append(valeurProp)
for codeStation in bikeList:
for cle in bikeList[codeStation]:
info = bikeList[codeStation][cle]
if type(info) is dict:
data = info['data']
bikeList[codeStation][cle]['moyenne'] = sum(data) / max(
len(data), 1)
for codeStation in bikeList:
valeurs = bikeList[codeStation]
requete = mysql.cursor()
requete.execute(
'INSERT INTO `resumeStatus` (`id`, `code`, `date`, `duree`, `nbBikeMin`, `nbBikeMax`, `nbBikeMoyenne`, `nbBikePris`, `nbBikeRendu`, `nbEBikeMin`, `nbEBikeMax`, `nbEBikeMoyenne`, `nbEBikePris`, `nbEBikeRendu`, `nbFreeEDockMin`, `nbFreeEDockMax`, `nbFreeEDockMoyenne`, `nbEDock`, `nbBikeOverflowMin`, `nbBikeOverflowMax`, `nbBikeOverflowMoyenne`, `nbEBikeOverflowMin`, `nbEBikeOverflowMax`, `nbEBikeOverflowMoyenne`, `maxBikeOverflow`) VALUES \
(NULL, ' + str(codeStation) + ', "' + dateConso + '", ' +
str(dureeConso) + ', ' + str(valeurs['nbBike']['min']) + ', ' +
str(valeurs['nbBike']['max']) + ', ' +
str(valeurs['nbBike']['moyenne']) + ', ' +
str(valeurs['nbBike']['pris']) + ', ' +
str(valeurs['nbBike']['remis']) + ', ' +
str(valeurs['nbEBike']['min']) + ', ' +
str(valeurs['nbEBike']['max']) + ', ' +
str(valeurs['nbEBike']['moyenne']) + ', ' +
str(valeurs['nbEBike']['pris']) + ', ' +
str(valeurs['nbEBike']['remis']) + ', ' +
str(valeurs['nbFreeEDock']['min']) + ', ' +
str(valeurs['nbFreeEDock']['max']) + ', ' +
str(valeurs['nbFreeEDock']['moyenne']) + ', ' +
str(valeurs['nbEDock']) + ', ' +
str(valeurs['nbBikeOverflow']['min']) + ', ' +
str(valeurs['nbBikeOverflow']['max']) + ', ' +
str(valeurs['nbBikeOverflow']['moyenne']) + ', ' +
str(valeurs['nbEBikeOverflow']['min']) + ', ' +
str(valeurs['nbEBikeOverflow']['max']) + ', ' +
str(valeurs['nbEBikeOverflow']['moyenne']) + ', ' +
str(valeurs['maxBikeOverflow']) + ')')
def calculerResumeConso(dateConsoDT, dureeConso):
dateConso = dateConsoDT.strftime("%Y-%m-%d %H:%M:%S")
minuteAvant = dateConsoDT - datetime.timedelta(minutes=1)
dateConsoAvant = minuteAvant.strftime("%Y-%m-%d %H:%M:%S")
finConso = dateConsoDT + datetime.timedelta(minutes=dureeConso)
dateConsoFin = finConso.strftime("%Y-%m-%d %H:%M:%S")
proprietes = [
'nbStation', 'nbStationDetecte', 'nbBike', 'nbEBike', 'nbFreeEDock',
'nbEDock'
]
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute("SELECT " + ', '.join(proprietes) + " \
FROM statusConso \
WHERE (`date` >= '" + dateConso + "' AND `date` < '" + dateConsoFin + "') \
ORDER BY id ASC")
statusConso = requete.fetchall()
bikeList = {}
for row in statusConso:
for i, cle in enumerate(proprietes):
if not row[i] is None:
valeurProp = int(row[i])
if cle in ['nbStation', 'nbStationDetecte', 'nbEDock']:
if not cle in bikeList:
bikeList[cle] = valeurProp
else:
bikeList[cle] = max(bikeList[cle], valeurProp)
else:
if not cle in bikeList:
bikeList[cle] = {
'data': [],
'min': valeurProp,
'max': valeurProp
}
else:
bikeList[cle]['max'] = max(bikeList[cle]['max'],
valeurProp)
bikeList[cle]['min'] = min(bikeList[cle]['min'],
valeurProp)
bikeList[cle]['data'].append(valeurProp)
for cle in bikeList:
info = bikeList[cle]
if type(info) is dict:
data = info['data']
bikeList[cle]['moyenne'] = sum(data) / max(len(data), 1)
if len(bikeList) > 0:
valeurs = bikeList
requete = mysql.cursor()
#nbEDock peut être null
nbEDock = 0
if nbEDock in valeurs:
nbEDock = valeurs['nbEDock']
requete.execute(
'INSERT INTO `resumeConso` (`id`, `date`, `duree`, `nbStation`, nbStationDetecte, `nbBikeMin`, `nbBikeMax`, `nbBikeMoyenne`, `nbEBikeMin`, `nbEBikeMax`, `nbEBikeMoyenne`, `nbFreeEDockMin`, `nbFreeEDockMax`, `nbFreeEDockMoyenne`, `nbEDock`) VALUES \
(NULL, "' + dateConso + '", ' + str(dureeConso) + ', ' +
str(valeurs['nbStation']) + ', ' +
str(valeurs['nbStationDetecte']) + ', ' +
str(valeurs['nbBike']['min']) + ', ' +
str(valeurs['nbBike']['max']) + ', ' +
str(valeurs['nbBike']['moyenne']) + ', ' +
str(valeurs['nbEBike']['min']) + ', ' +
str(valeurs['nbEBike']['max']) + ', ' +
str(valeurs['nbEBike']['moyenne']) + ', ' +
str(valeurs['nbFreeEDock']['min']) + ', ' +
str(valeurs['nbFreeEDock']['max']) + ', ' +
str(valeurs['nbFreeEDock']['moyenne']) + ', ' + str(nbEDock) + ')')
def optimiserBDD():
mysql = getMysqlConnection()
requete = mysql.cursor()
requete.execute(
'OPTIMIZE TABLE resumeConso, resumeStatus, status, statusConso')
def getAllStation():
mysql = getMysqlConnection()
#On récupère la liste des stations déjà en base
requete = mysql.cursor()
requete.execute('SELECT code, dateOuverture FROM stations')
rep = requete.fetchall()
aujourdhui = datetime.today().date()
stations = []
stationsFutur = []
for row in rep:
stations.append(row[0])
if (row[1] is None or row[1] > aujourdhui):
stationsFutur.append(row[0])
nbTotalBike = 0
nbTotalEBike = 0
nbTotalFreeEDock = 0
nbTotalEDock = 0
#On créer une conso pour avoir son id
requete = mysql.cursor()
requete.execute('INSERT INTO statusConso (date) VALUES (NOW())')
idConso = requete.lastrowid
strIdConso = str(idConso)
nbStationsOuvertes = 0
nbStationsOuvertesDetectees = 0
now = datetime.now()
urlVelib = getURLVelib()
tmpFileName = 'detailsStations.json'
urllib.request.urlretrieve(urlVelib, tmpFileName)
data = json.load(open(tmpFileName))
for etatStation in data:
infoStation = etatStation['station']
codeStation = int(infoStation['code'])
if codeStation > 100:
if codeStation not in stations:
longitude = infoStation['gps']['longitude']
latitude = infoStation['gps']['latitude']
if infoStation['state'] != 'Operative':
strDateOuverture = 'NULL'
else:
strDateOuverture = 'CURDATE()'
requete = mysql.cursor()
requete.execute(
'INSERT INTO stations (code, name, longitude, latitude, type, dateOuverture, adresse, insee) VALUES \
(' + str(codeStation) + ', "' + str(infoStation['name']) +
'", ' + str(longitude) + ', ' + str(latitude) + ', "' +
str(infoStation['type']) + '", ' + strDateOuverture +
', "' + getAdresse(latitude, longitude) + '", ' +
str(getInsee(codeStation)) + ')')
stations.append(codeStation)
nbBike = int(etatStation['nbBike'])
nbEbike = int(etatStation['nbEbike'])
nbFreeEDock = int(etatStation['nbFreeDock']) + int(
etatStation['nbFreeEDock'])
nbEDock = int(etatStation['nbDock']) + int(etatStation['nbEDock'])
requete = mysql.cursor()
requete.execute(
'INSERT INTO status (code, idConso, state, nbBike, nbEBike, nbFreeEDock, nbEDock, nbBikeOverflow, nbEBikeOverflow, maxBikeOverflow) VALUES \
(' + str(codeStation) + ', ' + strIdConso + ', "' +
str(infoStation['state']) + '", ' + str(nbBike) + ', ' +
str(nbEbike) + ', ' + str(nbFreeEDock) + ', ' + str(nbEDock) +
', ' + str(etatStation['nbBikeOverflow']) + ', ' +
str(etatStation['nbEBikeOverflow']) + ', ' +
str(etatStation['maxBikeOverflow']) + ')')
#On met à jour la station au besoin
if codeStation in stationsFutur and nbEDock > 0:
requete = mysql.cursor()
requete.execute('UPDATE stations \
SET dateOuverture = CURDATE() WHERE code = ' +
str(codeStation))
#On ajoute a la conso
nbTotalBike += nbBike
nbTotalEBike += nbEbike
nbTotalFreeEDock += nbFreeEDock
nbTotalEDock += nbEDock
if infoStation['state'] == "Operative":
nbStationsOuvertes += 1
if nbEDock > 0 and nbBike + nbEbike + nbFreeEDock > 0:
nbStationsOuvertesDetectees += 1
os.remove(tmpFileName)
#On insert tout dans le statut
requete = mysql.cursor()
requete.execute('UPDATE statusConso SET \
nbStation = ' + str(nbStationsOuvertes) + ', \
nbStationDetecte = ' + str(nbStationsOuvertesDetectees) + ' ,\
nbBike = ' + str(nbTotalBike) + ', \
nbEbike = ' + str(nbTotalEBike) + ', \
nbFreeEDock = ' + str(nbTotalFreeEDock) + ', \
nbEDock = ' + str(nbTotalEDock) + ' \
WHERE id = ' + strIdConso)
mysql.close()
