""" Ce fichier contient les requetes utilisées dans les tableaux de bord des Formations """ import ast import dateutil import pymongo from dateutil.relativedelta import relativedelta from flask import send_file from pymongo import MongoClient import json from bson import ObjectId import re from datetime import datetime, timezone, date import prj_common as mycommon import secrets import inspect import sys, os import csv import pandas as pd from pymongo import ReturnDocument import GlobalVariable as MYSY_GV from math import isnan import GlobalVariable as MYSY_GV from datetime import timedelta from datetime import timedelta from operator import itemgetter """ Recupération du nombre de session par formation par mois """ def Get_Qery_Formation_By_Session_By_Periode(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'session_start_date', 'session_end_date', 'filter_value' ] incom_keys = diction.keys() for val in incom_keys: if val not in field_list and val.startswith('my_') is False: mycommon.myprint(str( inspect.stack()[0][3]) + " Le champ '" + val + "' n'existe pas") return False, " Les informations fournies sont incorrectes", """ Verification des champs obligatoires """ field_list_obligatoire = ['token', 'session_start_date', 'session_end_date' ] for val in field_list_obligatoire: if val not in diction: mycommon.myprint( str(inspect.stack()[0][3]) + " - La valeur '" + val + "' n'est pas presente dans la liste des arguments ") return False, " Les informations fournies sont incorrectes", """ Verification de l'identité et autorisation de l'entité qui appelle cette API """ token = "" if ("token" in diction.keys()): if diction['token']: token = diction['token'] local_status, my_partner = mycommon.Check_Connexion_And_Return_Partner_Data(diction) if (local_status is not True): return local_status, my_partner filt_session_start_date = "" if ("session_start_date" in diction.keys() and diction['session_start_date']): filt_session_start_date = str(diction['session_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa." filt_session_end_date = "" if ("session_end_date" in diction.keys() and diction['session_end_date']): filt_session_end_date = str(diction['session_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa." """ Si la valeur de 'filter_value' est m0 ou m1, on va aller recuperer les date du mois correspondant. On ecrase les valeur de filt_session_start_date et filt_session_end_date """ if( 'filter_value' in diction.keys() ): if( str(diction['filter_value']) == "m0"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Current_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date elif (str(diction['filter_value']) == "m1"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Previous_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date RetObject = [] val_tmp = 1 filt_session_start_date_ISODATE = datetime.strptime(str(filt_session_start_date), '%d/%m/%Y') filt_session_end_date_ISODATE = datetime.strptime(str(filt_session_end_date), '%d/%m/%Y') filt_session_start_date_ISODATE_work = filt_session_start_date_ISODATE filt_session_end_date_ISODATE_work = filt_session_end_date_ISODATE interval_date = [] while filt_session_start_date_ISODATE_work <= filt_session_end_date_ISODATE_work: interval_date.append(filt_session_start_date_ISODATE_work) filt_session_start_date_ISODATE_work += relativedelta(months=1) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {"formateur_id": {"$exists": 'true'}}, { 'mysy_date_debut_session': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_date_debut_session": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'myclass', 'let': {'formateur_id': '$formateur_id', 'partner_owner_recid': '$partner_owner_recid'}, 'localField': 'class_internal_url', 'foreignField': 'internal_url', 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_owner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'myclass' } }, {'$group': { '_id': { "class_internal_url": '$myclass.internal_url', "mois_annee_session": {'$substr': ["$date_debut", 3, 7]}, }, 'count': {'$count': {} } } }, { '$sort': {'_id': -1} }, ]) print(" ### Get_Qery_Formation_By_Session_By_Periode ici pipe_qry = ", pipe_qry) new_retval_titles = [] new_retval_data = [] for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): #print(" ### retval = ", retval ) user = retval existe_title = "0" tab_new_mois_data = str(retval['_id']['mois_annee_session']).split("/") new_mois_data = str(tab_new_mois_data[1])+"/"+str(tab_new_mois_data[0]) new_mois_count = retval['count'] new_point = {"x":str(new_mois_data), "y":mycommon.tryInt(str(retval['count']))} #print(" ### new_point = ", new_point) for val in new_retval_data: if( val["id"] == retval['_id']['class_internal_url'] ): val["data"].append(new_point) existe_title = "1" if(existe_title == "0" ): # Ce titre n'existe pas new_title = {} new_title['id'] = retval['_id']['class_internal_url'] new_title["data"] = [] new_title["data"].append(new_point) new_retval_data.append(new_title) val_tmp = val_tmp + 1 #print(" #### new_retval_data = ", new_retval_data) interval_date_str = [] for val in interval_date: tmp = str(val.strftime("%Y/%m")) interval_date_str.append(tmp) for formation_data in new_retval_data: #print(" ANALYSE DE LA LIGNE :", formation_data) for my_date in interval_date_str: #print(" ### on cherche la date = ", my_date) ok = 0 for val2 in formation_data['data']: if (val2['x'] == my_date): ok = 1 #print(" trouvé : " + str(my_date)) if (ok == 0): #print(" on PAS trouvé " + str(my_date)) new_node = {'x': str(my_date), 'y': 0} formation_data['data'].append(new_node) #print(" Apres ajout ",formation_data['data'] ) formation_data['data'].sort(key=itemgetter('x'), reverse=False) #print(" #### TRIEEE = ", formation_data) #print(" #### new_retval_data = ", new_retval_data) RetObject.append(mycommon.JSONEncoder().encode(new_retval_data)) return True, RetObject except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() mycommon.myprint(str(inspect.stack()[0][3]) + " -" + str(e) + " - Line : " + str(exc_tb.tb_lineno)) return False, " Impossible de récupérer les données " """" Export Excel/CSV du resultat de Get_Qery_Formation_By_Session_By_Periode """ def Get_Qery_Formation_By_Session_By_Periode_Export_CSV(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'user_dashbord_id' ] incom_keys = diction.keys() for val in incom_keys: if val not in field_list and val.startswith('my_') is False: mycommon.myprint(str( inspect.stack()[0][3]) + " Le champ '" + val + "' n'existe pas") return False, " Les informations fournies sont incorrectes", """ Verification des champs obligatoires """ field_list_obligatoire = ['token', 'user_dashbord_id' ] for val in field_list_obligatoire: if val not in diction: mycommon.myprint( str(inspect.stack()[0][3]) + " - La valeur '" + val + "' n'est pas presente dans la liste des arguments ") return False, " Les informations fournies sont incorrectes", """ Verification de l'identité et autorisation de l'entité qui appelle cette API """ token = "" if ("token" in diction.keys()): if diction['token']: token = diction['token'] local_status, my_partner = mycommon.Check_Connexion_And_Return_Partner_Data(diction) if (local_status is not True): return local_status, my_partner # Recuperation des données du user_dashbord my_user_dashbord = MYSY_GV.dbname['user_dashbord'].find_one( {'_id': ObjectId(str(diction['user_dashbord_id'])), 'valide': '1', 'locked': '0', 'partner_owner_recid': str(my_partner['recid'])}) if (my_user_dashbord is None): mycommon.myprint( str(inspect.stack()[0][3]) + " - L'identifiant du tableau de bord est invalide ") return False, " L'identifiant du tableau de bord est invalide", local_default_filter = ast.literal_eval(str(my_user_dashbord['default_filter'])) my_new_diction = {} my_new_diction['token'] = diction['token'] filt_session_start_date = "" filt_session_end_date = "" local_default_filter = ast.literal_eval(str(my_user_dashbord['default_filter'])) if ("session_start_date" in local_default_filter.keys() and "session_end_date" in local_default_filter.keys()): filt_session_start_date = local_default_filter['session_start_date'] filt_session_end_date = local_default_filter['session_end_date'] elif ("periode" in local_default_filter.keys()): my_new_diction['filter_value'] = str(local_default_filter['periode']) if (str(local_default_filter['periode']) == "m0"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Current_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date elif (str(local_default_filter['periode']) == "m1"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Previous_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date print(" ### DATE RECUP filt_session_start_date = ", filt_session_start_date, " --- filt_session_end_date = ",filt_session_end_date) RetObject = [] val_tmp = 1 filt_session_start_date_ISODATE = datetime.strptime(str(filt_session_start_date), '%d/%m/%Y') filt_session_end_date_ISODATE = datetime.strptime(str(filt_session_end_date), '%d/%m/%Y') filt_session_start_date_ISODATE_work = filt_session_start_date_ISODATE filt_session_end_date_ISODATE_work = filt_session_end_date_ISODATE interval_date = [] while filt_session_start_date_ISODATE_work <= filt_session_end_date_ISODATE_work: interval_date.append(filt_session_start_date_ISODATE_work) filt_session_start_date_ISODATE_work += relativedelta(months=1) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {"formateur_id": {"$exists": 'true'}}, { 'mysy_date_debut_session': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_date_debut_session": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'myclass', 'let': {'formateur_id': '$formateur_id', 'partner_owner_recid': '$partner_owner_recid'}, 'localField': 'class_internal_url', 'foreignField': 'internal_url', 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_owner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'myclass' } }, {'$group': { '_id': { "class_internal_url": '$myclass.internal_url', "mois_annee_session": {'$substr': ["$date_debut", 3, 7]}, }, 'count': {'$count': {} } } }, { '$sort': {'_id': -1} }, ]) #print(" ### Get_Qery_Formation_By_Session_By_Periode ici pipe_qry = ", pipe_qry) new_retval_titles = [] new_retval_data = [] for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): #print(" ### retval = ", retval ) user = retval existe_title = "0" tab_new_mois_data = str(retval['_id']['mois_annee_session']).split("/") new_mois_data = str(tab_new_mois_data[1])+"/"+str(tab_new_mois_data[0]) new_mois_count = retval['count'] new_point = {"x":str(new_mois_data), "y":mycommon.tryInt(str(retval['count']))} #print(" ### new_point = ", new_point) for val in new_retval_data: if( val["id"] == retval['_id']['class_internal_url'] ): val["data"].append(new_point) existe_title = "1" if(existe_title == "0" ): # Ce titre n'existe pas new_title = {} new_title['id'] = retval['_id']['class_internal_url'] new_title["data"] = [] new_title["data"].append(new_point) new_retval_data.append(new_title) val_tmp = val_tmp + 1 #print(" #### new_retval_data = ", new_retval_data) interval_date_str = [] for val in interval_date: tmp = str(val.strftime("%Y/%m")) interval_date_str.append(tmp) for formation_data in new_retval_data: #print(" ANALYSE DE LA LIGNE :", formation_data) for my_date in interval_date_str: #print(" ### on cherche la date = ", my_date) ok = 0 for val2 in formation_data['data']: if (val2['x'] == my_date): ok = 1 #print(" trouvé : " + str(my_date)) if (ok == 0): #print(" on PAS trouvé " + str(my_date)) new_node = {'x': str(my_date), 'y': 0} formation_data['data'].append(new_node) #print(" Apres ajout ",formation_data['data'] ) formation_data['data'].sort(key=itemgetter('x'), reverse=False) #print(" #### TRIEEE = ", formation_data) #print(" #### RetObject = ", RetObject) new_tab = [] for val in new_retval_data: for tmp in val['data']: local_node = {} local_node['class'] = str(val['id'][0]) local_node['date'] = tmp['x'] local_node['nb_session'] = tmp['y'] new_tab.append(local_node) #print(" ### final new_tab = ", new_tab) todays_date = str(date.today().strftime("%d/%m/%Y")) ts = datetime.now().timestamp() ts = str(ts).replace(".", "").replace(",", "")[-5:] orig_file_name = "Export_csv_" + str(my_partner['recid']) + "_" + str(ts) + ".csv" outputFilename = str(MYSY_GV.TEMPORARY_DIRECTORY) + "/" + str(orig_file_name) tab_exported_fields = ['date_extraction', 'filtre_date_debut', 'filtre_date_fin', 'class', 'date', 'nb_session'] session_start_date = str(filt_session_start_date_ISODATE) session_end_date = str(filt_session_end_date_ISODATE) with open(outputFilename, 'w', newline='') as outfile: fields = tab_exported_fields write = csv.DictWriter(outfile, fieldnames=fields) write.writeheader() for answers_record in new_tab: # Here we are using 'cursor' as an iterator flattened_record = {} for local_fiels in tab_exported_fields: answers_record_JSON = ast.literal_eval(str(answers_record)) if( str(local_fiels) in answers_record_JSON.keys() ): flattened_record[str(local_fiels)] = answers_record_JSON[str(local_fiels)] flattened_record['filtre_date_debut'] = str(session_start_date)[0:10] flattened_record['filtre_date_fin'] = str(session_end_date)[0:10] flattened_record['date_extraction'] = str(todays_date)[0:10] #print(" ### flattened_record = ", flattened_record) write.writerow(flattened_record) if os.path.exists(outputFilename): #print(" ### ok os.path.exists(outputFilename) "+str(outputFilename)) return True, send_file(outputFilename, as_attachment=True) return False, "Impossible de générer l'export csv (2) " except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() mycommon.myprint(str(inspect.stack()[0][3]) + " -" + str(e) + " - Line : " + str(exc_tb.tb_lineno)) return False, " Impossible de récupérer les données " """ V2 : Recuperation du nombre de session par periode """ def Get_Qery_Session_By_Periode_V2(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'session_start_date', 'session_end_date', 'filter_value', ] incom_keys = diction.keys() for val in incom_keys: if val not in field_list and val.startswith('my_') is False: mycommon.myprint(str( inspect.stack()[0][3]) + " Le champ '" + val + "' n'existe pas") return False, " Les informations fournies sont incorrectes", """ Verification des champs obligatoires """ field_list_obligatoire = ['token', 'session_start_date', 'session_end_date'] for val in field_list_obligatoire: if val not in diction: mycommon.myprint( str(inspect.stack()[0][3]) + " - La valeur '" + val + "' n'est pas presente dans la liste des arguments ") return False, " Les informations fournies sont incorrectes", """ Verification de l'identité et autorisation de l'entité qui appelle cette API """ token = "" if ("token" in diction.keys()): if diction['token']: token = diction['token'] local_status, my_partner = mycommon.Check_Connexion_And_Return_Partner_Data(diction) if (local_status is not True): return local_status, my_partner filt_session_start_date = "" if ("session_start_date" in diction.keys() and diction['session_start_date']): filt_session_start_date = str(diction['session_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa." filt_session_end_date = "" if ("session_end_date" in diction.keys() and diction['session_end_date']): filt_session_end_date = str(diction['session_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa." """ Si la valeur de 'filter_value' est m0 ou m1, on va aller recuperer les date du mois correspondant. On ecrase les valeur de filt_session_start_date et filt_session_end_date """ if ('filter_value' in diction.keys()): if (str(diction['filter_value']) == "m0"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Current_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date elif (str(diction['filter_value']) == "m1"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Previous_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date RetObject = [] val_tmp = 1 filt_session_start_date_ISODATE = datetime.strptime(str(filt_session_start_date), '%d/%m/%Y') filt_session_end_date_ISODATE = datetime.strptime(str(filt_session_end_date), '%d/%m/%Y') """ Creation de la range des mois entre filt_periode_start_date_ISODATE et filt_periode_end_date_ISODATE """ range_date_month = [] start = filt_session_start_date_ISODATE end = filt_session_end_date_ISODATE while start <= end: node = {} node['month_year'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['label'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['TotalAmount'] = 0 node['value'] = 0 node['count'] = 0 range_date_month.append(node) start += relativedelta(months=1) filt_session_start_date_ISODATE_work = filt_session_start_date_ISODATE filt_session_end_date_ISODATE_work = filt_session_end_date_ISODATE qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {'mysy_session_date_debut': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} # print(" ### qery_match = ", qery_match) pipe_qry = ([ {"$addFields": { "mysy_session_date_debut": { '$dateFromString': { 'dateString': { "$substr": [ "$date_debut", 0, 10 ] }, 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, { "$lookup": { 'from': 'myclass', 'localField': 'class_internal_url', 'foreignField': 'internal_url', "pipeline": [{'$project': {'title': 1, 'internal_url': 1, 'external_code': 1, 'published': 1}} ], "as": "myclass_collection" } }, {'$group': { '_id': { "mois_annee_inscription": { "$concat": [{'$substr': ["$date_debut", 3, 2]}, "_", {'$substr': ["$date_debut", 6, 4]}]}, "annee_inscription": {'$substr': ["$date_debut", 6, 4]}, "mois_inscription": {'$substr': ["$date_debut", 3, 2]}, }, "count": {"$sum": 1} } }, { '$sort': {'_id.mois_annee_inscription': 1} }, ]) print(" ### Get_Qery_Session_By_Formation_By_Periode_V2 ici pipe_qry = ", pipe_qry) axis_data = [] cpt = 0 tab_lines_inscription_data = [] for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): cpt = cpt + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) if( str(retval['_id']['mois_annee_inscription']) == str(tmp['month_year']) ): tmp['count'] = mycommon.tryFloat(str(retval['count'])) RetObject = [] json_retval = {} json_retval['data'] = range_date_month json_retval['axis_data'] = axis_data #print(" ### json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) #print(" ### RetObject = ", RetObject) return True, RetObject except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() mycommon.myprint(str(inspect.stack()[0][3]) + " -" + str(e) + " - Line : " + str(exc_tb.tb_lineno)) return False, " Impossible de récupérer les données " """ V2 : Recuperation du nombre de session par periode """ def Get_Qery_Session_By_Formation_V2(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'session_start_date', 'session_end_date', 'filter_value', ] incom_keys = diction.keys() for val in incom_keys: if val not in field_list and val.startswith('my_') is False: mycommon.myprint(str( inspect.stack()[0][3]) + " Le champ '" + val + "' n'existe pas") return False, " Les informations fournies sont incorrectes", """ Verification des champs obligatoires """ field_list_obligatoire = ['token', 'session_start_date', 'session_end_date'] for val in field_list_obligatoire: if val not in diction: mycommon.myprint( str(inspect.stack()[0][3]) + " - La valeur '" + val + "' n'est pas presente dans la liste des arguments ") return False, " Les informations fournies sont incorrectes", """ Verification de l'identité et autorisation de l'entité qui appelle cette API """ token = "" if ("token" in diction.keys()): if diction['token']: token = diction['token'] local_status, my_partner = mycommon.Check_Connexion_And_Return_Partner_Data(diction) if (local_status is not True): return local_status, my_partner filt_session_start_date = "" if ("session_start_date" in diction.keys() and diction['session_start_date']): filt_session_start_date = str(diction['session_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa." filt_session_end_date = "" if ("session_end_date" in diction.keys() and diction['session_end_date']): filt_session_end_date = str(diction['session_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa." """ Si la valeur de 'filter_value' est m0 ou m1, on va aller recuperer les date du mois correspondant. On ecrase les valeur de filt_session_start_date et filt_session_end_date """ if ('filter_value' in diction.keys()): if (str(diction['filter_value']) == "m0"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Current_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date elif (str(diction['filter_value']) == "m1"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Previous_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date RetObject = [] val_tmp = 1 filt_session_start_date_ISODATE = datetime.strptime(str(filt_session_start_date), '%d/%m/%Y') filt_session_end_date_ISODATE = datetime.strptime(str(filt_session_end_date), '%d/%m/%Y') """ Creation de la range des mois entre filt_periode_start_date_ISODATE et filt_periode_end_date_ISODATE """ range_date_month = [] start = filt_session_start_date_ISODATE end = filt_session_end_date_ISODATE while start <= end: node = {} node['month_year'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['label'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['TotalAmount'] = 0 node['value'] = 0 node['count'] = 0 range_date_month.append(node) start += relativedelta(months=1) filt_session_start_date_ISODATE_work = filt_session_start_date_ISODATE filt_session_end_date_ISODATE_work = filt_session_end_date_ISODATE qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {'mysy_session_date_debut': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} # print(" ### qery_match = ", qery_match) pipe_qry = ([ {"$addFields": { "mysy_session_date_debut": { '$dateFromString': { 'dateString': { "$substr": [ "$date_debut", 0, 10 ] }, 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, { "$lookup": { 'from': 'myclass', 'localField': 'class_internal_url', 'foreignField': 'internal_url', "pipeline": [{'$project': {'title': 1, 'internal_url': 1, 'external_code': 1, 'published': 1}} ], "as": "myclass_collection" } }, { "$group": { "_id": { "class_id": "$myclass_collection._id", "class_code": "$myclass_collection.external_code", "class_title": "$myclass_collection.title", }, "count": { "$sum": 1 } } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_Session_By_Formation_V2 ici pipe_qry = ", pipe_qry) axis_data = [] my_data = [] cpt = 0 for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): cpt = cpt + 1 axis_data.append(str(retval['_id']['class_code'][0])) node = {} node['class_code'] = str(retval['_id']['class_code'][0]) node['class_title'] = str(retval['_id']['class_title'][0]) node['label'] = str(retval['_id']['class_code'][0]) node['value'] = mycommon.tryFloat(str(retval['count'])) node['count'] = mycommon.tryFloat(str(retval['count'])) my_data.append(node) RetObject = [] json_retval = {} json_retval['data'] = my_data json_retval['axis_data'] = axis_data #print(" ### json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) #print(" ### RetObject = ", RetObject) return True, RetObject except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() mycommon.myprint(str(inspect.stack()[0][3]) + " -" + str(e) + " - Line : " + str(exc_tb.tb_lineno)) return False, " Impossible de récupérer les données " """ V2 : Recuperation du nombre de session par periode, CUMULE """ def Get_Qery_Session_By_Periode_Cumule_V2(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'session_start_date', 'session_end_date', 'filter_value', ] incom_keys = diction.keys() for val in incom_keys: if val not in field_list and val.startswith('my_') is False: mycommon.myprint(str( inspect.stack()[0][3]) + " Le champ '" + val + "' n'existe pas") return False, " Les informations fournies sont incorrectes", """ Verification des champs obligatoires """ field_list_obligatoire = ['token', 'session_start_date', 'session_end_date'] for val in field_list_obligatoire: if val not in diction: mycommon.myprint( str(inspect.stack()[0][3]) + " - La valeur '" + val + "' n'est pas presente dans la liste des arguments ") return False, " Les informations fournies sont incorrectes", """ Verification de l'identité et autorisation de l'entité qui appelle cette API """ token = "" if ("token" in diction.keys()): if diction['token']: token = diction['token'] local_status, my_partner = mycommon.Check_Connexion_And_Return_Partner_Data(diction) if (local_status is not True): return local_status, my_partner filt_session_start_date = "" if ("session_start_date" in diction.keys() and diction['session_start_date']): filt_session_start_date = str(diction['session_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut de session' n'est pas au format jj/mm/aaaa." filt_session_end_date = "" if ("session_end_date" in diction.keys() and diction['session_end_date']): filt_session_end_date = str(diction['session_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_session_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin de session' n'est pas au format jj/mm/aaaa." """ Si la valeur de 'filter_value' est m0 ou m1, on va aller recuperer les date du mois correspondant. On ecrase les valeur de filt_session_start_date et filt_session_end_date """ if ('filter_value' in diction.keys()): if (str(diction['filter_value']) == "m0"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Current_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date elif (str(diction['filter_value']) == "m1"): # On recupere les date du mois en cours local_status, start_current_month_date, end_current_month_date = mycommon.Get_Previous_Month_Start_End_Date() if (local_status is False): return local_status, start_current_month_date filt_session_start_date = start_current_month_date filt_session_end_date = end_current_month_date RetObject = [] val_tmp = 1 filt_session_start_date_ISODATE = datetime.strptime(str(filt_session_start_date), '%d/%m/%Y') filt_session_end_date_ISODATE = datetime.strptime(str(filt_session_end_date), '%d/%m/%Y') """ Creation de la range des mois entre filt_periode_start_date_ISODATE et filt_periode_end_date_ISODATE """ range_date_month = [] start = filt_session_start_date_ISODATE end = filt_session_end_date_ISODATE while start <= end: node = {} node['month_year'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['label'] = '{:02d}'.format(start.month) + "_" + str(start.year) node['TotalAmount'] = 0 node['value'] = 0 node['count'] = 0 range_date_month.append(node) start += relativedelta(months=1) filt_session_start_date_ISODATE_work = filt_session_start_date_ISODATE filt_session_end_date_ISODATE_work = filt_session_end_date_ISODATE qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {'mysy_session_date_debut': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} # print(" ### qery_match = ", qery_match) pipe_qry = ([ {"$addFields": { "mysy_session_date_debut": { '$dateFromString': { 'dateString': { "$substr": [ "$date_debut", 0, 10 ] }, 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, { "$lookup": { 'from': 'myclass', 'localField': 'class_internal_url', 'foreignField': 'internal_url', "pipeline": [{'$project': {'title': 1, 'internal_url': 1, 'external_code': 1, 'published': 1}} ], "as": "myclass_collection" } }, {'$group': { '_id': { "mois_annee_inscription": { "$concat": [{'$substr': ["$date_debut", 3, 2]}, "_", {'$substr': ["$date_debut", 6, 4]}]}, "annee_inscription": {'$substr': ["$date_debut", 6, 4]}, "mois_inscription": {'$substr': ["$date_debut", 3, 2]}, }, "count": {"$sum": 1} } }, { '$sort': {'_id.mois_annee_inscription': 1} }, ]) print(" ### Get_Qery_Session_By_Formation_By_Periode_V2 ici pipe_qry = ", pipe_qry) axis_data = [] cpt = 0 tab_lines_inscription_data = [] count_cumule = 0 for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): cpt = cpt + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) if( str(retval['_id']['mois_annee_inscription']) == str(tmp['month_year']) ): count_cumule = mycommon.tryFloat(str(retval['count'])) + count_cumule tmp['count'] = count_cumule RetObject = [] json_retval = {} json_retval['data'] = range_date_month json_retval['axis_data'] = axis_data #print(" ### json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) #print(" ### RetObject = ", RetObject) return True, RetObject except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() mycommon.myprint(str(inspect.stack()[0][3]) + " -" + str(e) + " - Line : " + str(exc_tb.tb_lineno)) return False, " Impossible de récupérer les données "