1129 lines
44 KiB
Python
1129 lines
44 KiB
Python
"""
|
|
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 "
|
|
|