def get(self,
url,
timeout=datetime.now().timestamp() + timeout,
toDf=True):
'''
Halting the process
'''
msg = None
while datetime.now().timestamp() < timeout:
'''
?
'''
msg = None
session = None
try:
'''
Opening a session
'''
session = requests.Session()
'''
Sending a request
'''
request = session.get(self.url())
'''
Sending the credential
'''
request = session.post(self.url('login'), data=self.credential)
'''
Retrieving data
'''
request = session.get(url)
'''
Returning content
'''
if toDf:
return pandas.read_json(request.content)
else:
return request.json()
except Exception as e:
msg = "Requête rejetée par Oze : tentative de connexion à l'url \"" + url + "\""
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the session
'''
if session is not None:
session.close()
if not (msg is None):
_Error.raiseError(msg)
return None
def write(self, data):
try:
self.serverPipe.write(
struct.pack('I', len(data)) +
str.encode(data)) # Write str length and str
self.serverPipe.seek(0) # EDIT: This is also necessary
except Exception as e:
_Error.raiseError(
"Erreur lors de l'écriture dans le pipe nommé : \n" + str(e))
def loadFile(self):
''' return the settings file loaded into memory '''
try:
return xml.etree.ElementTree.parse(self.PATH_TO_FILE).getroot()
except PermissionError as e:
value = "Impossible d'accèder au fichier :" + self.PATH_TO_FILE
value += "\n" + str(e)
_Error.raiseError(value)
except Exception as e:
value = "Problème avec le fichier :" + self.PATH_TO_FILE
value += "\n" + str(type(e))
value += "\n" + str(e.args)
value += "\n" + str(e)
_Error.raiseError(value)
return None
def update(self):
'''
Computing the halting time
'''
halt = datetime.now().timestamp() + self.timeout
'''
Retrieving the users
'''
students = self.database.selectAllStudent(halt)
students['nom'] = students['nom'].apply(
lambda x: Tools.normalizeString(x, 'ascii'))
students['prenom'] = students['prenom'].apply(
lambda x: Tools.normalizeString(x, 'ascii'))
self.students = students
if students is None:
_Error.raiseError(
"Erreur : base de données locale vide\nAucun élève à sélectionner"
)
#external_data =self.externalBD.getAllUsers(halt)
#external_data = self.externalBD.processUsers(students)
#external_data.to_csv('students_oze_info.csv')
external_data = pandas.read_csv('students_oze_info.csv')
if external_data is None:
_Error.raiseError(
"Erreur : aucun élève accessible depuis la base de données externe"
)
#merge data
users, unmatch = self.matcheStudent(halt, students, external_data)
if unmatch is not None:
print("\n---------------------------------")
print(
"Les étudiants suivant n'ont pas été trouvés sur oZe (leurs informations ne seront donc pas mises à jour)"
)
print("\n")
print(unmatch[['nom', 'prenom', 'id_BD_local']])
print("\n----------------------------------\n")
'''
process data
'''
self.updateStudentBan(unmatch, halt)
self.updateStudentExit(users, halt)
return None
def __init__(self, pipeName):
self.pipeName = pipeName
file = None
msg = None
num_try = 0
while num_try < 30:
try:
file = open(r'\\.\pipe\{0}'.format(self.pipeName), 'r+b', 0)
break
except Exception as e:
msg = "Erreur lors de l'ouverture du pipe nommé (vérifier que le serveur a été lancé avant le script et que le script est l'unique instance lancée) : \n" + str(
e)
time.sleep(1)
num_try += 1
if msg != None:
_Error.raiseError(msg)
self.serverPipe = file
def selectAllStudent(self, timeout):
''' get all the student information saved into the database'''
msg = None
while datetime.now().timestamp() < timeout:
msg = None
connection = None
try:
'''
Opening a connection
'''
connection = pyodbc.connect(**self.credential)
'''
Building the query
'''
sql = 'SELECT Id_eleve AS id_BD_local, Nom AS nom, Prenom AS prenom, DemiPension as half_board_days, Id_regime as id_exit_regime, '
sql += '(SELECT R.Label FROM ' + Database.table[
'regime'] + ' R WHERE R.Id_regime=E.Id_regime) as label_exit_regime '
sql += 'FROM ' + Database.table['student'] + ' E '
'''
Returning the selected rows
'''
return pandas.read_sql(sql, connection)
except Exception as e:
msg = "Erreur lors de la selection des élèves dans la base de données locale"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the connection
'''
if connection is not None:
connection.close()
if not (msg is None):
_Error.raiseError(msg)
return None
def updateExitBans(self, timeout):
'''save the corresponding array as csv file, delete the corresponding table, insert all the new data'''
filename = Database.FILE_NAME_BAN
#we save all the csv file into the subdirectory "DIRECTORY_FILES"
filepath = os.path.join(self.path_temp_database_folder, filename)
try:
self.ban_array.to_csv(filepath,
sep=';',
encoding='utf-8-sig',
index=False)
except PermissionError as e:
msg = "Erreur : impossible de sauvegarder les données traitées"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
_Error.raiseError(msg)
self.truncateTable(self.table["ban"], timeout)
self.bulkInsert(self.table["ban"], filepath, timeout)
self.ban_array = self.ban_array[0:0] #remove all row from dataframe
def getComputerTableId(timeout, credential, table, id_computer):
''' get all the student information saved into the database'''
msg = None
while datetime.now().timestamp() < timeout:
msg = None
connection = None
try:
'''
Opening a connection
'''
connection = pyodbc.connect(**credential)
'''
Building the query
'''
sql = "SELECT Id_agent FROM " + table + " WHERE Identifiant='" + id_computer + "'"
'''
Returning the selected rows
'''
res = pandas.read_sql(sql, connection)
if len(res) != 1:
msg = "Impossible de trouver l'index de l'agent '" + Database.COMPUTER_AUTHENTIFICATION_ID + "' dans la base de donnée pour l'utiliser comme responsable lors de l'enregistrement des informations dans la base de données"
break
return res.Id_agent[0]
except Exception as e:
msg = "Erreur lors de la selection des élèves dans la base de données locale"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the connection
'''
if connection is not None:
connection.close()
if not (msg is None):
_Error.raiseError(msg)
def truncateTable(self, table, timeout):
'''
Halting the process
'''
msg = None
while datetime.now().timestamp() < timeout:
msg = None
connection = None
try:
'''
Opening a connection
'''
connection = pyodbc.connect(**self.credential)
'''
Building the query
'''
sql = 'TRUNCATE TABLE ' + table
'''
Returning the number of deleted rows
'''
return connection.execute(sql).rowcount
except Exception as e:
msg = "Erreur lors de la purge d'une table de la base de données locale"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the connection
'''
if connection is not None:
connection.close()
if not (msg is None):
_Error.raiseError(msg)
return None
def bulkInsert(self, table, filepath, timeout):
'''
Halting the process
'''
msg = None
while datetime.now().timestamp() < timeout:
msg = None
connection = None
try:
'''
Opening a connection
'''
connection = pyodbc.connect(**self.credential)
'''
Building the query
'''
sql = 'BULK INSERT ' + table + ' FROM \'' + filepath + '\' WITH (FIRSTROW = 2, FIELDTERMINATOR = \';\', ROWTERMINATOR = \'\n\')'
'''
Returning the number of inserted rows
'''
return connection.execute(sql).rowcount
except Exception as e:
msg = "Erreur lors de l'insertion des heures de sorties dans la base de données locale"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the connection
'''
if connection is not None:
connection.close()
if not (msg is None):
_Error.raiseError(msg)
return None
def matcheStudent(self,
halt,
internal_data,
external_dataframe,
process_unmacth=True):
"""
Into Oze each student have a specific id which is independant from the one use into the local
database. Thus, we need to get all the student information (last name, first name and used id)
Then matches the table id (from the local database) and the used id (from oze)
"""
local = internal_data
extern = external_dataframe
if extern is None:
_Error.raiseError("Erreur : aucun élève accessible depuis Oze")
extern['nom'] = extern['nom'].apply(
lambda x: Tools.normalizeString(x, 'ascii'))
extern['prenom'] = extern['prenom'].apply(
lambda x: Tools.normalizeString(x, 'ascii'))
#matches table id and used id
columns = ['nom', 'prenom']
users = pandas.merge(local, extern,
on=columns).drop_duplicates(subset=columns)
unmatch = None
if process_unmacth:
unmatch = local[(~local['nom'].isin(users['nom']))
& (~local['prenom'].isin(users['prenom']))]
if len(unmatch) == 0:
unmatch = None
users = deque(users.to_dict('records'))
return users, unmatch
def getExitRegimes(self, timeout):
msg = None
list_regime = []
while datetime.now().timestamp() < timeout:
msg = None
connection = None
try:
'''
get all exit regime label and id from the database
'''
connection = pyodbc.connect(**self.credential)
sql = "SELECT Label, SortieFinJournee, Id_regime From " + self.table[
'RegimeLabel']
res = pandas.read_sql(sql, connection)
#create list of regime
for index, row in res.iterrows():
regime = Tools.ExitRegime(label=row[0],
exitEndOfDay=row[1],
tableId=row[2])
list_regime.append(regime)
'''
get all authorizations associated to the regimes
'''
index = 0
for regime in list_regime:
#check data format
if regime is None:
list_regime.pop(index)
continue
table_id = regime.Id
if table_id == -1:
list_regime.pop(index)
continue
#get authorizations
sql = "SELECT P.Label, P.Periode, P.Debut, P.Fin, P.Id_permission FROM " + self.table[
'RegimeRelation'] + " R, " + self.table[
'RegimeAuthorization'] + " P WHERE P.Id_permission = R.Id_permission AND R.Id_regime={0}".format(
table_id)
res = pandas.read_sql(sql, connection)
for index, row in res.iterrows():
label = row[0]
period = Tools.Timer.timeToSecond(
Tools.Timer.stringToTime(row[1]))
start = Tools.Timer.timeToSecond(
Tools.Timer.stringToTime(row[2]))
end = Tools.Timer.timeToSecond(
Tools.Timer.stringToTime(row[3]))
table_id = row[4]
authorization = Tools.ExitRegimeAuthorization(
label, period, start, end, table_id)
regime.addAuthorization(authorization)
index = index + 1
return list_regime
except Exception as e:
msg = "Impossible d'accèder aux régimes de sortie dans la base de données"
msg += "\n" + str(type(e))
msg += "\n" + str(e.args)
msg += "\n" + str(e)
finally:
'''
Closing the connection
'''
if connection is not None:
connection.close()
if not (msg is None):
_Error.raiseError(msg)