""" Ce fichier contient les requetes utilisées dans les tableaux de bord des sessions """ 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 import Dashbord_queries.formation_tbd_qries as formation_tbd_qries """ Recuperation des sessions avec le taux de remplissage """ def Get_Qery_List_Session_Data(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', ] 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() ): #print(" filter_value = ", diction['filter_value']) 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 #print(" ### filt_session_start_date = ", filt_session_start_date, " ### filt_session_end_date = ", filt_session_end_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 qery = [{'partner_owner_recid':str(my_partner['recid'])}, {'code_session':1, 'session_etape':1, 'class_internal_url':1, 'distantiel':1, 'presentiel':1, 'nb_participant':1, 'formateur_id':1, 'date_debut':1, 'date_fin':1}] #print(" #### qery = ", qery) for retval in MYSY_GV.dbname['session_formation'].find({'partner_owner_recid':str(my_partner['recid'])}, {'code_session':1, 'session_etape':1, 'class_internal_url':1, 'distantiel':1, 'presentiel':1, 'nb_participant':1, 'formateur_id':1, 'date_debut':1, 'date_fin':1}): user = retval user['id'] = str(val_tmp) val_tmp = val_tmp + 1 nb_participant = "1" if ("nb_participant" in retval.keys()): nb_participant = retval['nb_participant'] user['nb_participant'] = nb_participant ## Recuperation du nombre d'inscrits Count_Inscrit = MYSY_GV.dbname['inscription'].count_documents( {'session_id': str(retval['_id']), 'class_internal_url': str(retval['class_internal_url']), 'status': "1"}) user['nb_inscrit'] = str(Count_Inscrit) ## Recuperation du nombre de preinscrits Count_Preinscrit = MYSY_GV.dbname['inscription'].count_documents( {'session_id': str(retval['_id']), 'class_internal_url': str(retval['class_internal_url']), 'status': "0"}) user['nb_preinscrit'] = str(Count_Preinscrit) taux_disponibilite = "100" taux_remplissage = "0" if (str(nb_participant) != "0"): taux_remplissage = (round(int(mycommon.tryInt(Count_Inscrit)) / int(mycommon.tryInt(nb_participant)), 2))*100 taux_disponibilite = 100 - taux_remplissage user['taux_remplissage'] = str(taux_remplissage) user['taux_remplissageColor'] = "green" user['taux_disponibilite'] = str(taux_disponibilite) user['taux_disponibiliteColor'] = "yellow" if (filt_session_start_date and filt_session_end_date): # Si on a un filtre sur la date debut et de fin de session if ((datetime.strptime(str(retval['date_debut'][0:10]).strip(), '%d/%m/%Y') >= datetime.strptime( str(filt_session_start_date).strip(), '%d/%m/%Y')) and (datetime.strptime(str(retval['date_fin'][0:10]).strip(), '%d/%m/%Y') <= datetime.strptime(str(filt_session_end_date).strip(), '%d/%m/%Y')) ): RetObject.append(mycommon.JSONEncoder().encode(user)) elif (filt_session_start_date): # Si on a un filtre uniquement sur la date de debut de session if ((datetime.strptime(str(retval['date_debut'][0:10]).strip(), '%d/%m/%Y') >= datetime.strptime( str(filt_session_start_date).strip(), '%d/%m/%Y'))): RetObject.append(mycommon.JSONEncoder().encode(user)) elif (filt_session_end_date): # Si on a un filtre uniquement sur la date de fin session if ((datetime.strptime(str(retval['date_fin'][0:10]).strip(), '%d/%m/%Y') <= datetime.strptime( str(filt_session_end_date).strip(), '%d/%m/%Y'))): RetObject.append(mycommon.JSONEncoder().encode(user)) else: RetObject.append(mycommon.JSONEncoder().encode(user)) #RetObject.append(mycommon.JSONEncoder().encode(user)) print(" #### Get_Qery_List_Session_Data 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 des sessions avec le taux de remplissage """ def Get_Qery_List_Taux_Remplissage_Session_Data_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_date_debut_session': {'$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE}}, ]} """ qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, {"apprenant_id": {"$exists": True}}, ]} """ # print(" ### qery_match = ", qery_match) pipe_qry = ([ {"$addFields": { "mysy_date_debut_session": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, ]) print(" ### Get_Qery_List_Taux_Remplissage_Session_Data_V2 ici pipe_qry = ", pipe_qry) axis_data = [] my_data = [] cpt = 0 tab_lines_inscription_data = [] tab_serie_taux_replissage = [] tab_serie_taux_non_replissage = [] tab_serie_label = [] for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): cpt = cpt + 1 axis_data.append(str(retval['code_session'])) node = {} node['session_code'] = str(retval['code_session']) node['session_title'] = str(retval['titre']) node['label'] = str(retval['code_session']) ### gestion des taux nb_participant = "1" if ("nb_participant" in retval.keys()): nb_participant = retval['nb_participant'] node['nb_participant'] = nb_participant ## Recuperation du nombre d'inscrits Count_Inscrit = MYSY_GV.dbname['inscription'].count_documents( {'session_id': str(retval['_id']), 'class_internal_url': str(retval['class_internal_url']), 'status': "1", 'partner_owner_recid': str(my_partner['recid'])}) qry_nb_inscrit = {'session_id': str(retval['_id']), 'class_internal_url': str(retval['class_internal_url']), 'status': "1", 'partner_owner_recid': str(my_partner['recid'])} node['nb_inscrit'] = str(Count_Inscrit) taux_remplissage = "0" if (str(nb_participant) != "0" and str(nb_participant).strip() != ""): taux_remplissage = round(int(mycommon.tryInt(Count_Inscrit)) / int(mycommon.tryInt(nb_participant)), 2) node['taux_remplissage'] = str(taux_remplissage) taux_non_remplissage = round(1 - taux_remplissage, 2) tab_serie_taux_replissage.append(taux_remplissage) tab_serie_taux_non_replissage.append(taux_non_remplissage) tab_serie_label.append( str(retval['code_session'])) my_data.append(node) RetObject = [] json_retval = {} json_retval['axis_data'] = axis_data node_taux_replissage = {} node_taux_replissage['data'] = tab_serie_taux_replissage node_taux_replissage['label'] = "Taux Remplissage" json_retval['taux_replissage'] = node_taux_replissage node_taux_non_replissage = {} node_taux_non_replissage['data'] = tab_serie_taux_non_replissage node_taux_non_replissage['label'] = "Taux non Remplissage" json_retval['taux_non_replissage'] = node_taux_non_replissage print(" ### Get_Qery_List_Taux_Remplissage_Session_Data_V2 json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) #print(" ### Get_Qery_List_Taux_Remplissage_Session_Data_V2 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 " """ Recuperation des sessions par formateur sur une periode """ def Get_Qery_Session_By_Trainer_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') 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': 'ressource_humaine', "let": {'formateur_id': "$formateur_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$formateur_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'ressource_humaine' } }, {'$group': { '_id': { "Formateur_id": "$formateur_id", "Formateur_nom": "$ressource_humaine.nom", "Formateur_prenom": "$ressource_humaine.prenom", "mois_session": {'$substr': ["$date_debut", 3, 2]}, "annee_session": {'$substr': ["$date_debut", 6, 4]}, }, 'count': {'$count': {} } } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_Session_By_Trainer_By_Periode ici pipe_qry = ", pipe_qry) for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): user = {} user["Formateur_id"] = retval['_id']['Formateur_id'] if( len(retval['_id']['Formateur_nom']) > 0 ): user["Formateur_nom"] = retval['_id']['Formateur_nom'][0] else: user["Formateur_nom"] = "" if (len(retval['_id']['Formateur_nom']) > 0): user["Formateur_prenom"] = retval['_id']['Formateur_prenom'][0] else: user["Formateur_prenom"] = "" user["mois_session"] = retval['_id']['mois_session'] user["annee_session"] = retval['_id']['annee_session'] user["nb"] = retval['count'] if( str(user["Formateur_nom"]).strip() == "" and str(user["Formateur_prenom"]).strip() == ""): user["Formateur_nom_prenom_mois_annee"] = str(user["annee_session"]) + "/" + str(user["mois_session"]) + " - Inconnu " else: user["Formateur_nom_prenom_mois_annee"] = str(user["annee_session"]) + "/" + str( user["mois_session"]) + " - " + str(user["Formateur_nom"]) + " " + str(user["Formateur_prenom"]) val_tmp = val_tmp + 1 RetObject.append(mycommon.JSONEncoder().encode(user)) #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 " """ Repartition du pourcentage de session par formateur """ def Get_Qery_Session_Repartition_Session_By_Trainer_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') #print(" ### filt_session_start_date_ISODATE = ", filt_session_start_date_ISODATE) #print(" ### filt_session_end_date_ISODATE = ", filt_session_end_date_ISODATE) qery_match = {'$and': [{"partner_owner_recid":str(my_partner['recid']) }, {"valide":'1'}, { 'mysy_date_debut_session': { '$gte': filt_session_start_date_ISODATE, '$lte': filt_session_end_date_ISODATE } } , ]} #print(" ### qery_match = ", qery_match) # Recuperation du nombre total de session sur la periode pipe_qry_nb_record = ([ {"$addFields": { "mysy_date_debut_session": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$group': {'_id': 'null', 'nb_record': {'$sum': 1}}}, {'$project': {'_id': 0}} ]) print(" ### pipe_qry_nb_record = ", pipe_qry_nb_record) nb_total_session = 0 Nb_Record_RetObject = [] for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry_nb_record): if( "nb_record" in retval.keys() and retval['nb_record']): nb_total_session = mycommon.tryInt(retval['nb_record']) #print(" #### nb_total_session = ",str(nb_total_session)) if(nb_total_session == 0 ): mycommon.myprint(str( inspect.stack()[0][3]) + " Aucune session de formation valide ") return True, RetObject pipe_qry = ([ {"$addFields": { "mysy_date_debut_session": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'ressource_humaine', "let": {'formateur_id': "$formateur_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$formateur_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'ressource_humaine' } }, {'$group': { '_id': { "Formateur_id": "$formateur_id", "Formateur_nom": "$ressource_humaine.nom", "Formateur_prenom": "$ressource_humaine.prenom", }, 'count': {'$count': {} } } }, { '$sort': {'count': -1} }, ]) #print(" ### Get_Qery_Session_Repartition_Session_By_Trainer_By_Periode ici pipe_qry = ", pipe_qry) for retval in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): #print(" ### retval = ", retval) if( "Formateur_id" in retval['_id'].keys() ): user = {} user["Formateur_id"] = retval['_id']['Formateur_id'] if( len(retval['_id']['Formateur_nom']) > 0 ): user["Formateur_nom"] = retval['_id']['Formateur_nom'][0] else: user["Formateur_nom"] = "" if (len(retval['_id']['Formateur_nom']) > 0): user["Formateur_prenom"] = retval['_id']['Formateur_prenom'][0] else: user["Formateur_prenom"] = "" user["nb"] = retval['count'] user["pourcentage"] = str(round(mycommon.tryInt( str(user["nb"] )) / nb_total_session, 2)) if( str(user["Formateur_nom"]).strip() == "" and str(user["Formateur_prenom"]).strip() == ""): user["Formateur_nom_prenom"] = "Inconnu " user["id"] = "Inconnu " else: user["Formateur_nom_prenom"] = str(user["Formateur_nom"]) + " " + str(user["Formateur_prenom"]) user["id"] = str(user["Formateur_nom"]) + " " + str(user["Formateur_prenom"]) user["label"] = user["id"] user["value"] = retval['count'] val_tmp = val_tmp + 1 RetObject.append(mycommon.JSONEncoder().encode(user)) #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 " """ Cette fonction permet d'experter un dashbord en csv """ def Export_Dashbord_To_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", new_retval_data = {} my_new_diction = {} my_new_diction['token'] = diction['token'] session_start_date = "" 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()): session_start_date = local_default_filter['session_start_date'] 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 session_start_date = start_current_month_date 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 session_start_date = start_current_month_date session_end_date = end_current_month_date my_new_diction['session_start_date'] = session_start_date my_new_diction['session_end_date'] = session_end_date if( my_user_dashbord['dashbord_internal_code'] == "tbd_code_session_01"): local_status, local_retval = Get_Qery_List_Session_Data(my_new_diction) if( local_status is False): return local_status, local_retval new_retval_data = local_retval elif ( my_user_dashbord['dashbord_internal_code'] == "tbd_code_session_02"): local_status, local_retval = Get_Qery_Session_By_Trainer_By_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval elif (my_user_dashbord['dashbord_internal_code'] == "tbd_code_session_03"): local_status, local_retval = Get_Qery_Session_Repartition_Session_By_Trainer_By_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval else: mycommon.myprint( str(inspect.stack()[0][3]) + " Requête inconnue ") return False, " Requête inconnue ", #print(" ### la new_retval_data = ", new_retval_data) # Recuperation des colonne à exporter base_config_dashbord_data = MYSY_GV.dbname['base_config_dashbord'].find_one({'dashbord_internal_code':str(my_user_dashbord['dashbord_internal_code'])}) tab_exported_fields = [] if( "exported_fields" in base_config_dashbord_data): tab_exported_fields = base_config_dashbord_data['exported_fields'] # Remettres les dates de filtres en debut de liste if( "filtre_date_fin" in tab_exported_fields): tab_exported_fields.remove("filtre_date_fin") tab_exported_fields.insert(0, "filtre_date_fin") if ("filtre_date_debut" in tab_exported_fields): tab_exported_fields.remove("filtre_date_debut") tab_exported_fields.insert(0, "filtre_date_debut") tab_exported_fields.insert(0, "date_extraction") #print(" ### Liste des colonnes à exporter sont : ", tab_exported_fields) 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) with open(outputFilename, 'w', newline='') as outfile: fields = tab_exported_fields write = csv.DictWriter(outfile, fieldnames=fields) write.writeheader() for answers_record in new_retval_data: # 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) flattened_record['filtre_date_fin'] = str(session_end_date) flattened_record['date_extraction'] = str(todays_date) #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 d'exporter les données "