""" Ce fichier permet de gerer les tableaux de bord liée aux factures, commandes et devis """ import ast import dateutil import pymongo import xlsxwriter 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 from dateutil.relativedelta import relativedelta """ Recuperation du chiffre d'affaire (factures) par mois, client, """ def Get_Qery_List_Factures_Data_By_Periode(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_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_periode_start_date_ISODATE end = filt_periode_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) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'mysy_invoice_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_invoice_date": { '$dateFromString': { 'dateString': '$invoice_date', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "mois_annee_facture": { "$concat": [{'$substr': ["$invoice_date", 3, 2]},"_", {'$substr': ["$invoice_date", 6, 4]}]}, "annee_facture": {'$substr': ["$invoice_date", 6, 4]}, "mois_facture": {'$substr': ["$invoice_date", 3, 2]}, }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": { "$sum": 1} } }, { '$sort': {'_id.mois_annee_facture': 1} }, ]) #print(" ### Get_Qery_List_Factures_Data ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] count_cumule = 0 """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_invoice_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) if( str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) ): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) count_cumule = mycommon.tryFloat(str(retval['TotalAmount'])) + count_cumule tmp['TotalAmount_cumule'] = count_cumule series_TotalAmount_data.append( str(retval['TotalAmount'])) else: series_TotalAmount_data.append("0") json_retval = {} json_retval['data'] = range_date_month json_retval['axis_data'] = axis_data json_retval['series_TotalAmount_data'] = series_TotalAmount_data print(" ### Get_Qery_List_Factures_Data_By_Periode : json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) 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 du chiffre d'affaire par formation important : seulement les formation ayant un CA > 0 """ def Get_Qery_List_Factures_Data_By_Class(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_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_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_end_date), '%d/%m/%Y') qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'}, { 'mysy_invoice_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_invoice_date": { '$dateFromString': { 'dateString': '$invoice_date', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_invoice_line', "let": {"invoice_header_id": {'$toString': "$_id"}, 'partner_invoice_line_partner_owner_recid': '$partner_owner_recid', 'partner_invoice_line_invoice_header_ref_interne': '$invoice_header_ref_interne'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$valide", "1"]}, {'$eq': ["$invoice_header_ref_interne",'$$partner_invoice_line_invoice_header_ref_interne']}, {'$eq': ["$partner_owner_recid", '$$partner_invoice_line_partner_owner_recid']}, ] } } }, ], 'as': 'collection_partner_invoice_line' } }, { '$unwind': '$collection_partner_invoice_line' }, {'$group': { '_id': { "class_internal_url": "$collection_partner_invoice_line.order_line_formation", }, "TotalAmount_HT": { "$sum": {'$toDouble': '$collection_partner_invoice_line.order_line_montant_hors_taxes'}}, "count": {"$sum": 1} } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_List_Factures_Data_By_Class ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] tab_data = [] tab_axis_data_class_code = [] tab_axis_data_class_title = [] """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_invoice_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 node = {} node['class_internal_url'] = retval['_id']['class_internal_url'] node['TotalAmount_HT'] = retval['TotalAmount_HT'] node['label'] = retval['TotalAmount_HT'] node['nb_line'] = retval['count'] # Recuperation des données de la formation class_data = MYSY_GV.dbname['myclass'].find_one({'internal_url':str(retval['_id']['class_internal_url']), 'partner_owner_recid':str(my_partner['recid']), 'valide':'1'}) if( class_data and "title" in class_data.keys() ): node['class_title'] = class_data['title'] tab_axis_data_class_title.append(class_data['title']) else: node['class_title'] = "" tab_axis_data_class_title.append("") if (class_data and "external_code" in class_data.keys()): node['class_external_code'] = class_data['external_code'] tab_axis_data_class_code.append(class_data['external_code']) else: node['class_external_code'] = "" tab_axis_data_class_code.append("") tab_data.append(node) json_retval = {} json_retval['data'] = tab_data json_retval['axis_class_code'] = tab_axis_data_class_code json_retval['axis_class_title'] = tab_axis_data_class_title print(" ### Get_Qery_List_Factures_Data_By_Class json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) 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 " """ Affichage du chiffre d'affaire par client sur une periode """ def Get_Qery_List_Factures_Data_By_Client_Periode(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_end_date), '%d/%m/%Y') qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'mysy_invoice_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_invoice_date": { '$dateFromString': { 'dateString': '$invoice_date', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "Client_id": "$order_header_client_id", "Client_nom": "$partner_client_collection.nom", "Client_raison_sociale": "$partner_client_collection.raison_sociale", }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": { "$sum": 1} } }, { '$sort': {'count': -1} }, ]) #print(" ### Get_Qery_List_Factures_Data ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_invoice_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 user = {} user['label'] = retval['_id']['Client_nom'][0] user['value'] = retval['TotalAmount'] user['count'] = retval['count'] user['Client_id'] = retval['_id']['Client_id'] user['Client_nom'] = retval['_id']['Client_nom'][0] user['Client_raison_sociale'] = retval['_id']['Client_raison_sociale'][0] RetObject.append(mycommon.JSONEncoder().encode(user)) print(" ### Get_Qery_List_Factures_Data_By_Client_Periode 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 Affichage du chiffre d'affaire par client """ def Get_Qery_List_Factures_Data_By_Client_V2(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_end_date), '%d/%m/%Y') qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'mysy_invoice_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_invoice_date": { '$dateFromString': { 'dateString': '$invoice_date', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "Client_id": "$order_header_client_id", "Client_nom": "$partner_client_collection.nom", "Client_raison_sociale": "$partner_client_collection.raison_sociale", }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": { "$sum": 1} } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_List_Factures_Data_By_Client_V2 ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] """ On recupere les données, on les format dans le 'range_date_month' et on retourne """ tab_data = [] for retval in MYSY_GV.dbname['partner_invoice_header'].aggregate(pipe_qry): #print(" ### retval ici pipe_qry = ", retval) val_tmp = val_tmp + 1 user = {} if( retval['_id']['Client_nom'] and retval['_id']['Client_nom'][0] ): user['label'] = retval['_id']['Client_nom'][0] else: user['label'] = "?" user['value'] = retval['TotalAmount'] user['count'] = retval['count'] if (retval['_id']['Client_id']): user['Client_id'] = retval['_id']['Client_id'] else: user['Client_id'] = "?" if (retval['_id']['Client_nom'] and retval['_id']['Client_nom'][0]): user['Client_nom'] = retval['_id']['Client_nom'][0] else: user['Client_nom'] = "?" if (retval['_id']['Client_raison_sociale'] and retval['_id']['Client_raison_sociale'][0]): user['Client_raison_sociale'] = retval['_id']['Client_raison_sociale'][0] else: user['Client_raison_sociale'] = "?" tab_data.append(user) retval = {} retval['data'] = tab_data RetObject.append(mycommon.JSONEncoder().encode(retval)) #print(" ### Get_Qery_List_Factures_Data_By_Client_Periode 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 fonctionne donne le CA des devis (gagné ou perdu ) sur une periode """ def Get_Qery_List_Quotation_Data_By_Periode(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_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_periode_start_date_ISODATE end = filt_periode_end_date_ISODATE while start <= end: node = {} node['month_year'] = '{:02d}'.format(start.month) +"_"+str(start.year) node['mois_annee_facture'] = '{: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 node['nb_devis_gagne'] = 0 node['nb_devis_perdu'] = 0 range_date_month.append(node) start += relativedelta(months=1) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'is_validated': {'$exists': True}}, { "order_header_type": "devis" }, { 'mysy_quotation_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE} }, ]} pipe_qry = ([ {"$addFields": { "mysy_quotation_date": { '$dateFromString': { 'dateString': '$order_header_date_cmd', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "order_header_status": "$order_header_status", "mois_annee_facture": { "$concat": [{'$substr': ["$order_header_date_cmd", 3, 2]},"_", {'$substr': ["$order_header_date_cmd", 6, 4]}]}, "annee_facture": {'$substr': ["$order_header_date_cmd", 6, 4]}, "mois_facture": {'$substr': ["$order_header_date_cmd", 3, 2]}, }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": { "$sum": 1} } }, { '$sort': {'_id.mois_annee_facture': 1} }, ]) print(" ### Get_Qery_List_Factures_Data ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] nb_cumule_gagne = 0 nb_cumule_perdu = 0 """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_order_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) if( str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) and str(retval['_id']['order_header_status']) == "3" ): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) tmp['nb_devis_gagne'] = mycommon.tryFloat(str(retval['count'])) nb_cumule_gagne = mycommon.tryFloat(str(retval['count'])) + nb_cumule_gagne tmp['nb_cumule_perdu'] = mycommon.tryFloat(str(nb_cumule_perdu)) tmp['nb_cumule_gagne'] = mycommon.tryFloat(str(nb_cumule_gagne)) series_TotalAmount_data.append( str(retval['TotalAmount'])) elif (str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) and str(retval['_id']['order_header_status']) == "4"): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) tmp['nb_devis_perdu'] = mycommon.tryFloat(str(retval['count'])) nb_cumule_perdu = mycommon.tryFloat(str(retval['count'])) + nb_cumule_perdu tmp['nb_cumule_perdu'] = mycommon.tryFloat(str(nb_cumule_perdu)) tmp['nb_cumule_gagne'] = mycommon.tryFloat(str(nb_cumule_gagne)) series_TotalAmount_data.append(str(retval['TotalAmount'])) json_retval = {} json_retval['data'] = range_date_month print(" ### Get_Qery_List_Quotation_Data_By_Periode json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) 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 fonctionne donne le CA des devis (gagné ou perdu ) par client """ def Get_Qery_List_Quotation_Data_By_Client(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_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_periode_start_date_ISODATE end = filt_periode_end_date_ISODATE while start <= end: node = {} node['month_year'] = '{:02d}'.format(start.month) +"_"+str(start.year) node['mois_annee_facture'] = '{: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 node['nb_devis_gagne'] = 0 node['nb_devis_perdu'] = 0 range_date_month.append(node) start += relativedelta(months=1) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'is_validated': {'$exists': True}}, { "order_header_type": "devis" }, { 'mysy_quotation_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE} }, ]} pipe_qry = ([ {"$addFields": { "mysy_quotation_date": { '$dateFromString': { 'dateString': '$order_header_date_cmd', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "Client_id": "$order_header_client_id", "Client_nom": "$partner_client_collection.nom", "Client_raison_sociale": "$partner_client_collection.raison_sociale", }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": {"$sum": 1} } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_List_Factures_Data ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] nb_cumule_gagne = 0 nb_cumule_perdu = 0 """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_order_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) tmp['label'] = retval['_id']['Client_nom'][0] tmp['Client_id'] = retval['_id']['Client_id'] tmp['Client_nom'] = retval['_id']['Client_nom'][0] tmp['Client_raison_sociale'] = retval['_id']['Client_raison_sociale'][0] if( str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) and str(retval['_id']['order_header_status']) == "3" ): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) tmp['nb_devis_gagne'] = mycommon.tryFloat(str(retval['count'])) series_TotalAmount_data.append( str(retval['TotalAmount'])) elif (str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) and str(retval['_id']['order_header_status']) == "4"): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) tmp['nb_devis_perdu'] = mycommon.tryFloat(str(retval['count'])) series_TotalAmount_data.append(str(retval['TotalAmount'])) json_retval = {} json_retval['data'] = range_date_month print(" ### Get_Qery_List_Quotation_Data_By_Periode json_retval = ", json_retval) RetObject.append(mycommon.JSONEncoder().encode(json_retval)) 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'exporter au format excel les données des TBD des factures Get_Qery_List_Factures_Data_By_Periode (facture_01) """ def TBD_FACTURE_01_Export_Dashbord_To_Excel(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'user_dashbord_id', 'date_from', 'date_to', 'client_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", print(" ### my_user_dashbord = ", my_user_dashbord) 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['periode_start_date'] = session_start_date my_new_diction['periode_end_date'] = session_end_date if (my_user_dashbord['dashbord_internal_code'] == "tbd_facture_01"): # 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") 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) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(outputFilename) worksheet = workbook.add_worksheet() row = 0 column = 0 # Ecrire l'entete for header_item in tab_exported_fields: worksheet.write(row, column, header_item) column += 1 """ Si dans la requete l'utilisateur a fornir des dates debut, fin et code client, alors on ecrase ce qui a été fait et on replace par les valeurs du diction """ if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): my_new_diction['periode_start_date'] = diction['date_from'] my_new_diction['periode_end_date'] = diction['date_to'] if ("client_id" in diction.keys() and diction['client_id']): my_new_diction['filter_client_id'] = diction['client_id'] #print(" ### my_new_diction === ", my_new_diction) local_status, local_retval = Get_Qery_List_Factures_Data_By_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval answers_record_JSON_Data = ast.literal_eval(str(new_retval_data)) flattened_record = {} for val_tmp in answers_record_JSON_Data: val_tmp_JSON = ast.literal_eval(str(val_tmp)) for val_tmp2 in val_tmp_JSON['data'] : column = 0 row = row + 1 worksheet.write(row, column, str(todays_date)) column += 1 if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): worksheet.write(row, column, str(diction['date_from'])) column += 1 worksheet.write(row, column, str(diction['date_to'])) column += 1 else : worksheet.write(row, column, str(session_start_date)) column += 1 worksheet.write(row, column, str(session_end_date)) column += 1 for local_fiels in tab_exported_fields: answers_record_JSON = ast.literal_eval(str(val_tmp2)) if (str(local_fiels) in answers_record_JSON.keys()): local_status, local_retval = mycommon.IsFloat( str(answers_record_JSON[str(local_fiels)]).strip()) no_html = "" if (local_status is True): no_html = answers_record_JSON[str(local_fiels)] else: no_html = mycommon.cleanhtml(answers_record_JSON[str(local_fiels)]) worksheet.write(row, column, no_html) column += 1 elif (my_user_dashbord['dashbord_internal_code'] == "tbd_facture_02"): # 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") 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) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(outputFilename) worksheet = workbook.add_worksheet() row = 0 column = 0 # Ecrire l'entete for header_item in tab_exported_fields: worksheet.write(row, column, header_item) column += 1 """ Si dans la requete l'utilisateur a fornir des dates debut, fin et code client, alors on ecrase ce qui a été fait et on replace par les valeurs du diction """ if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): my_new_diction['periode_start_date'] = diction['date_from'] my_new_diction['periode_end_date'] = diction['date_to'] if ("client_id" in diction.keys() and diction['client_id']): my_new_diction['filter_client_id'] = diction['client_id'] local_status, local_retval = Get_Qery_List_Factures_Data_By_Client_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval # Ecrire le reste des lignes for answers_record in new_retval_data: # Here we are using 'cursor' as an iterator column = 0 row = row + 1 worksheet.write(row, column, str(todays_date)) column += 1 if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): worksheet.write(row, column, str(diction['date_from'])) column += 1 worksheet.write(row, column, str(diction['date_to'])) column += 1 else: worksheet.write(row, column, str(session_start_date)) column += 1 worksheet.write(row, column, str(session_end_date)) column += 1 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()): local_status, local_retval = mycommon.IsFloat( str(answers_record_JSON[str(local_fiels)]).strip()) no_html = "" if (local_status is True): no_html = answers_record_JSON[str(local_fiels)] else: no_html = mycommon.cleanhtml(answers_record_JSON[str(local_fiels)]) worksheet.write(row, column, no_html) column += 1 elif (my_user_dashbord['dashbord_internal_code'] == "tbd_facture_03"): # 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") 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) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(outputFilename) worksheet = workbook.add_worksheet() row = 0 column = 0 # Ecrire l'entete for header_item in tab_exported_fields: worksheet.write(row, column, header_item) column += 1 """ Si dans la requete l'utilisateur a fornir des dates debut, fin et code client, alors on ecrase ce qui a été fait et on replace par les valeurs du diction """ if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction[ 'date_to']): my_new_diction['periode_start_date'] = diction['date_from'] my_new_diction['periode_end_date'] = diction['date_to'] if ("client_id" in diction.keys() and diction['client_id']): my_new_diction['filter_client_id'] = diction['client_id'] #print(" ### my_new_diction === ", my_new_diction) local_status, local_retval = Get_Qery_List_Quotation_Data_By_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval answers_record_JSON_Data = ast.literal_eval(str(new_retval_data)) flattened_record = {} for val_tmp in answers_record_JSON_Data: val_tmp_JSON = ast.literal_eval(str(val_tmp)) for val_tmp2 in val_tmp_JSON['data']: column = 0 row = row + 1 worksheet.write(row, column, str(todays_date)) column += 1 if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): worksheet.write(row, column, str(diction['date_from'])) column += 1 worksheet.write(row, column, str(diction['date_to'])) column += 1 else: worksheet.write(row, column, str(session_start_date)) column += 1 worksheet.write(row, column, str(session_end_date)) column += 1 for local_fiels in tab_exported_fields: answers_record_JSON = ast.literal_eval(str(val_tmp2)) if (str(local_fiels) in answers_record_JSON.keys()): local_status, local_retval = mycommon.IsFloat( str(answers_record_JSON[str(local_fiels)]).strip()) no_html = "" if (local_status is True): no_html = answers_record_JSON[str(local_fiels)] else: no_html = mycommon.cleanhtml(answers_record_JSON[str(local_fiels)]) worksheet.write(row, column, no_html) column += 1 elif (my_user_dashbord['dashbord_internal_code'] == "tbd_facture_04"): # 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") 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) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(outputFilename) worksheet = workbook.add_worksheet() row = 0 column = 0 # Ecrire l'entete for header_item in tab_exported_fields: worksheet.write(row, column, header_item) column += 1 """ Si dans la requete l'utilisateur a fornir des dates debut, fin et code client, alors on ecrase ce qui a été fait et on replace par les valeurs du diction """ if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction[ 'date_to']): my_new_diction['periode_start_date'] = diction['date_from'] my_new_diction['periode_end_date'] = diction['date_to'] if ("client_id" in diction.keys() and diction['client_id']): my_new_diction['filter_client_id'] = diction['client_id'] # print(" ### my_new_diction === ", my_new_diction) local_status, local_retval = Get_Qery_List_Facture_Previsionnelle_Data_By_Periode(my_new_diction) if (local_status is False): return local_status, local_retval new_retval_data = local_retval print( " ### new_retval_data = ", new_retval_data ) answers_record_JSON_Data = ast.literal_eval(str(new_retval_data)) flattened_record = {} for val_tmp in answers_record_JSON_Data: val_tmp_JSON = ast.literal_eval(str(val_tmp)) for val_tmp2 in val_tmp_JSON['data']: column = 0 row = row + 1 worksheet.write(row, column, str(todays_date)) column += 1 if ("date_from" in diction.keys() and diction['date_from'] and "date_to" in diction.keys() and diction['date_to']): worksheet.write(row, column, str(diction['date_from'])) column += 1 worksheet.write(row, column, str(diction['date_to'])) column += 1 else: worksheet.write(row, column, str(session_start_date)) column += 1 worksheet.write(row, column, str(session_end_date)) column += 1 for local_fiels in tab_exported_fields: answers_record_JSON = ast.literal_eval(str(val_tmp2)) if (str(local_fiels) in answers_record_JSON.keys()): local_status, local_retval = mycommon.IsFloat( str(answers_record_JSON[str(local_fiels)]).strip()) no_html = "" if (local_status is True): no_html = answers_record_JSON[str(local_fiels)] else: no_html = mycommon.cleanhtml(answers_record_JSON[str(local_fiels)]) worksheet.write(row, column, no_html) column += 1 else: mycommon.myprint( str(inspect.stack()[0][3]) + " Requête inconnue ") return False, " Requête inconnue ", workbook.close() 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 " """ Recuperation du chiffre d'affaire previsionnelle sur une période donnée """ def Get_Qery_List_Facture_Previsionnelle_Data_By_Periode_save(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_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_periode_start_date_ISODATE end = filt_periode_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) print(" ### range_date_month = ", range_date_month) qery_match = {'$and': [{"partner_owner_recid": str(my_partner['recid'])}, {"valide": '1'},filt_client_id, { 'mysy_invoice_date': {'$gte': filt_periode_start_date_ISODATE, '$lte': filt_periode_end_date_ISODATE}}, ]} pipe_qry = ([ {"$addFields": { "mysy_invoice_date": { '$dateFromString': { 'dateString': '$invoice_date', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'partner_client', "let": {'order_header_client_id': "$order_header_client_id", 'partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ["$_id", {'$convert': { 'input': "$$order_header_client_id", 'to': "objectId", 'onError': {'error': 'true'}, 'onNull': {'isnull': 'true'} }}]}, {'$eq': ["$valide", "1"]}, {'$eq': ["$partner_recid", '$$partner_owner_recid']} ] } } }, ], 'as': 'partner_client_collection' } }, {'$group': { '_id': { "mois_annee_facture": { "$concat": [{'$substr': ["$invoice_date", 3, 2]},"_", {'$substr': ["$invoice_date", 6, 4]}]}, "annee_facture": {'$substr': ["$invoice_date", 6, 4]}, "mois_facture": {'$substr': ["$invoice_date", 3, 2]}, }, "TotalAmount": {"$sum": {'$toDouble': '$total_header_toutes_taxes'}}, "count": { "$sum": 1} } }, { '$sort': {'count': -1} }, ]) print(" ### Get_Qery_List_Facture_Previsionnelle_Data_By_Periode ici pipe_qry = ", pipe_qry) RetObject = [] val_tmp = 0 axis_data = [] series_TotalAmount_data = [] """ On recupere les données, on les format dans le 'range_date_month' et on retourne""" for retval in MYSY_GV.dbname['partner_invoice_header'].aggregate(pipe_qry): val_tmp = val_tmp + 1 for tmp in range_date_month: axis_data.append(str(tmp['month_year'])) if( str(retval['_id']['mois_annee_facture']) == str(tmp['month_year']) ): tmp['TotalAmount'] = mycommon.tryFloat(str(retval['TotalAmount'])) tmp['count'] = mycommon.tryFloat(str(retval['count'])) series_TotalAmount_data.append( str(retval['TotalAmount'])) else: series_TotalAmount_data.append("0") json_retval = {} json_retval['data'] = range_date_month json_retval['axis_data'] = axis_data json_retval['series_TotalAmount_data'] = series_TotalAmount_data RetObject.append(mycommon.JSONEncoder().encode(json_retval)) 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 " def Get_Qery_List_Facture_Previsionnelle_Data_By_Periode(diction): try: diction = mycommon.strip_dictionary(diction) """ Verification des input acceptés """ field_list = ['token', 'periode_start_date', 'periode_end_date', 'filter_value', 'filter_client_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', ] 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_client_id = {} if ("filter_client_id" in diction.keys() and diction['filter_client_id']): filt_client_id = {'order_header_client_id': str(diction['filter_client_id'])} filt_periode_start_date = "" if ("periode_start_date" in diction.keys() and diction['periode_start_date']): filt_periode_start_date = str(diction['periode_start_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_start_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de debut' n'est pas au format jj/mm/aaaa." filt_periode_end_date = "" if ("periode_end_date" in diction.keys() and diction['periode_end_date']): filt_periode_end_date = str(diction['periode_end_date'])[0:10] local_status = mycommon.CheckisDate(filt_periode_end_date) if (local_status is False): mycommon.myprint(str( inspect.stack()[0][3]) + " Le filtre : 'date de fin' n'est pas au format jj/mm/aaaa.") return False, " Le filtre : 'date de fin' 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_periode_start_date = start_current_month_date filt_periode_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_periode_start_date = start_current_month_date filt_periode_end_date = end_current_month_date filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') filt_periode_end_date_ISODATE = datetime.strptime(str(filt_periode_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_periode_start_date_ISODATE end = filt_periode_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['first_day'] = "01/"+'{:02d}'.format(start.month) + "/" + str(start.year) node['list_session'] = [] node['TotalAmount'] = 0 node['value'] = 0 node['count'] = 0 range_date_month.append(node) start += relativedelta(months=1) #print(" ### range_date_month = ", range_date_month) for data in range_date_month: #print(" data = ", data) ca_month_previsionnel = 0 local_first_day_ISODATE = datetime.strptime(str(data['first_day']), '%d/%m/%Y') work_first_day = str(local_first_day_ISODATE.strftime("%d/%m/%Y")) #filt_periode_start_date_ISODATE = datetime.strptime(str(filt_periode_start_date), '%d/%m/%Y') #print(" local_first_day_ISODATE = ", local_first_day_ISODATE) qery_match = {'$and': [{"valide": '1', 'partner_owner_recid': str(my_partner['recid'])}, { 'mysy_session_start_date': {'$lte': local_first_day_ISODATE}, }, { 'mysy_session_end_date': {'$gte': local_first_day_ISODATE, }, }, ]} pipe_qry = [ {"$addFields": { "mysy_session_start_date": { '$dateFromString': { 'dateString': '$date_debut', 'format': "%d/%m/%Y" } } } }, {"$addFields": { "mysy_session_end_date": { '$dateFromString': { 'dateString': '$date_fin', 'format': "%d/%m/%Y" } } } }, {'$match': qery_match}, {'$lookup': { 'from': 'inscription', 'let': {'session_id': {'$toString': '$_id'}, 'session_partner_owner_recid': '$partner_owner_recid'}, 'pipeline': [ {'$match': {'$expr': {'$and': [ {'$eq': ['$session_id', '$$session_id']}, {'$eq': ['$partner_owner_recid', '$$session_partner_owner_recid']} ] } } }, ], 'as': 'inscription_collection' } }, ] #print(" ### pipe_qry previsionnel = ", pipe_qry) #print(" ### datetime.strptime(str(data['first_day']), '%d/%m/%Y') = ", datetime.strptime(str(data['first_day']), '%d/%m/%Y')) for val in MYSY_GV.dbname['session_formation'].aggregate(pipe_qry): """ 30/05/2024 - update : /!\ : Ici on parle du prix TOTAL de la session Pour recalculer le prix mensuelle, il faut prendre on fait un ratio : - calculer le nombre de mois - diviser le prix par le nombre de mois """ local_diction = {} local_diction['date_from'] = str(val['date_debut']) local_diction['date_to'] = str(val['date_fin']) local_diction['total_price'] = str(val['prix_session']) local_price_status, local_price_retval = mycommon.Compute_Monthly_Price_From_Dates(local_diction) if (local_price_status is False): return local_price_status, local_price_retval new_price_data = local_price_retval['monthly_price'] #print(" ### pour la session : "+str(val['code_session'])+" le prix mensuel est de new_price_data = ", local_price_retval) for tmp in range_date_month: CA_previsionnel = 0 if( tmp['first_day'] == work_first_day): ca_prev = 0 local_node = {} local_node['session_id'] = str(val['_id']) local_node['code_session'] = val['code_session'] local_node['prix_session'] = val['prix_session'] local_node['price_by'] = val['price_by'] if( "inscription_collection" in val.keys() ): local_node['nb_participant'] = len(val['inscription_collection']) else: local_node['nb_participant'] = '0' if( val['price_by'] == "perstagiaire"): ca_prev = (mycommon.tryFloat( new_price_data) * mycommon.tryFloat( local_node['nb_participant'])) + ca_prev elif( val['price_by'] == "persession"): ca_prev = (mycommon.tryFloat( new_price_data) ) + ca_prev local_node['ca_previsionnel'] = str(ca_prev) #tmp['list_session'].append(local_node) ca_month_previsionnel = ca_month_previsionnel + ca_prev data['ca_month_previsionnel'] = mycommon.tryFloat(str(ca_month_previsionnel)) final_data = {} final_data['data'] = range_date_month final_data['axis_data'] = [] final_data['series_TotalAmount_data'] = [] #print(" ### final_data = ", final_data) RetObject = [] RetObject.append(mycommon.JSONEncoder().encode(final_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 " """ Voici la requete pour le chiffre d'affaire groupé par client et par mois et année On obtient un resultat comme ca : { _id: { mois_facture: '03', annee_facture: '2024' }, TotalAmount: 103, count: 9 }, { _id: { mois_facture: '04', annee_facture: '2024' }, TotalAmount: 255544, count: 1 } --- -- db.partner_invoice_header.aggregate( [ { "$addFields":{ "mysy_invoice_date":{ "$dateFromString":{ "dateString":"$invoice_date", "format":"%d/%m/%Y" } } } }, { "$match":{ "partner_owner_recid":"43598820dd270936c3d2fd822717d0f18f194b1a1b894aaf89" } }, { "$lookup":{ "from":"partner_client", "let":{ "order_header_client_id":"$order_header_client_id", "partner_owner_recid":"$partner_owner_recid" }, "pipeline":[ { "$match":{ "$expr":{ "$and":[ { "$eq":[ "$_id", { "$convert":{ "input":"$$order_header_client_id", "to":"objectId", "onError":{ "error":"true" }, "onNull":{ "isnull":"true" } } } ] }, { "$eq":[ "$valide", "1" ] }, { "$eq":[ "$partner_recid", "$$partner_owner_recid" ] } ] } } } ], "as":"partner_client_collection" } }, { "$group":{ "_id":{ "Client_id":"$order_header_client_id", "Client_nom":"$partner_client_collection.nom", "Client_raison_sociale":"$partner_client_collection.raison_sociale", "mois_facture":{ "$substr":[ "$invoice_date", 3, 2 ] }, "annee_facture":{ "$substr":[ "$invoice_date", 6, 4 ] } }, "totalAmount": { "$sum": {'$toDouble': '$total_header_toutes_taxes'} }, "count": { $sum: 1 } } }, { "$sort":{ "count":-1 } } ]) """