velibLib.py 4.23 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
import urllib.request, json, os, pymysql
from datetime import datetime
from config import getMysqlConnection, getURLVelib
from adresseLib import getAdresse

def getAllStation():
    mysql = getMysqlConnection()

    #On récupère la liste des stations déjà en base
    requete = mysql.cursor()
11
    requete.execute('SELECT code, dateOuverture FROM stations')
12
    rep = requete.fetchall()
13
    aujourdhui = datetime.today().date()
14
    stations = []
15 16 17 18 19
    stationsFutur = []
    for row in rep:
        stations.append(row[0])
        if(row[1] is None or row[1] > aujourdhui):
            stationsFutur.append(row[0])
20 21 22 23

    nbTotalBike = 0
    nbTotalEBike = 0
    nbTotalFreeEDock = 0
24
    nbTotalEDock = 0
25 26 27 28 29 30 31

    #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
32
    nbStationsOuvertesDetectees = 0
33 34 35 36 37 38 39 40 41
    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'])
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
        if codeStation > 0:
            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) VALUES \
                ('+str(codeStation)+', "'+str(infoStation['name'])+'", '+str(longitude)+', '+str(latitude)+', "'+str(infoStation['type'])+'", '+strDateOuverture+', "'+getAdresse(latitude, longitude)+'")')
                stations.append(codeStation)
            
            #Et on prend les infos de l'état actuel de la station
            ok = True
            if infoStation['state'] == 'Work in progress' and codeStation == 10006:
                #Exception pour le moment sur cette station
                ok = False
60

61
            if ok:
62 63 64 65
                nbBike = int(etatStation['nbBike'])
                nbEbike = int(etatStation['nbEbike'])
                nbFreeEDock = int(etatStation['nbFreeDock'])+int(etatStation['nbFreeEDock'])
                nbEDock = int(etatStation['nbDock'])+int(etatStation['nbEDock'])
66
                requete = mysql.cursor()
67
                requete.execute('INSERT INTO status (code, idConso, state, nbBike, nbEBike, nbFreeEDock, nbEDock, nbBikeOverflow, nbEBikeOverflow, maxBikeOverflow) VALUES \
68
                ('+str(codeStation)+', '+strIdConso+', "'+str(infoStation['state'])+'", '+str(nbBike)+', '+str(nbEbike)+', '+str(nbFreeEDock)+', '+str(nbEDock)+', '+str(etatStation['nbBikeOverflow'])+', '+str(etatStation['nbEBikeOverflow'])+', '+str(etatStation['maxBikeOverflow'])+')')
69 70 71 72 73 74

                #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))
75

76
                #On ajoute a la conso
77 78 79 80
                nbTotalBike += nbBike
                nbTotalEBike += nbEbike
                nbTotalFreeEDock += nbFreeEDock
                nbTotalEDock += nbEDock
81 82
                if infoStation['state'] == "Operative":
                    nbStationsOuvertes += 1
83 84
                if nbEDock > 0 and nbBike + nbEbike + nbFreeEDock > 0:
                    nbStationsOuvertesDetectees += 1
85 86 87 88
    os.remove(tmpFileName)

    #On insert tout dans le statut
    requete = mysql.cursor()
89 90 91
    requete.execute('UPDATE statusConso SET \
    nbStation = '+str(nbStationsOuvertes)+', \
    nbStationDetecte = '+str(nbStationsOuvertesDetectees)+' ,\
92 93
    nbBike = '+str(nbTotalBike)+', \
    nbEbike = '+str(nbTotalEBike)+', \
94 95
    nbFreeEDock = '+str(nbTotalFreeEDock)+', \
    nbEDock = '+str(nbTotalEDock)+' \
96 97 98
    WHERE id = '+strIdConso)

    mysql.close()