from django.shortcuts import render from django.contrib.auth.decorators import login_required from ..services.foxQueryService import(getCampaignNumbers, get_queryfoxservice, get_projected_orders, get_estimated_orders, get_accumulated_units, get_n_and_n1_campaign_numbers, get_accumulated_invoicing, check_closed_campaign, get_queryfoxservice_hist, ) from datetime import datetime import csv from django.views.decorators.csrf import csrf_exempt from django.http import JsonResponse, HttpResponse from django.http import JsonResponse import json import openpyxl from openpyxl.utils import get_column_letter from io import BytesIO from django.db.models import Sum from collections import defaultdict import os from django.conf import settings import django @login_required def foxqueryexport(request): has_data = False file_url = None numeros_campana = getCampaignNumbers() filterCampaignId = 0 showButtonDownloadExcel = 0 #no show result = None projected_orders = None total_orders_n1 = None estimated_orders = None estimated_orders_n1 = None orders_of_day = None orders_of_day_n1 = None # commercial_orders = None accumulated_orders = None accumulated_orders_n1 = None desv_ped = None desv_unids = None desv_invoicing = None units_of_day = None units_of_day_n1 = None units_accumulated_n = None units_accumulated_n1 = None units_totals_n1 = None projected_units = None invoicing_day = None invoicing_day_n1 = None show_facturacion = False cumulative_invoicing = None projected_invoicing = None cumulative_invoicing_n1 = None total_invoicing_n1 = None previous_campaign_number = None campaign_number = None # grafico total_units_per_day = None total_units_per_product_type = None total_units_per_division = None #post if request.method == 'POST': has_data = True campaign_number = int(request.POST.get('campaign_number', 0)) filterCampaignId = campaign_number showButtonDownloadExcel = 1 # pedidos result = get_projected_orders(campaign_number) projected_orders = result .get("Proyeccion") if result else None orders_of_day = result.get("AcumuladoDia") if result else None orders_of_day_n1 = result.get("AcumuladoDiaNM1") if result else None accumulated_orders = result.get("AcumuladoActual") if result else None accumulated_orders_n1 = result.get("AcumuladoActualNM1") if result else None total_orders_n1 = result.get("TotalPedidosNM1") if result else None # pedidos estimados y comerciales result_est = get_estimated_orders(campaign_number) estimated_orders = result_est.get("EstimatedOrders") estimated_orders_n1 = result_est.get("EstimatedOrdersN1") # desvio pedidos desv_ped = round((accumulated_orders - accumulated_orders_n1) * 100 / accumulated_orders, 4) # acumulados unidades result_accum = get_accumulated_units(campaign_number) result_accum = result_accum[0] if result_accum else None units_accumulated_n = result_accum.get("UnitsAccumulatedN") if result_accum else None units_accumulated_n1 = result_accum.get("UnitsAccumulatedN1") if result_accum else None projected_units = result_accum.get("ProjectedUnits") if result_accum else None units_of_day = result_accum.get("UnitsOfDay") if result_accum else None units_of_day_n1 = result_accum.get("UnitsOfDayNM1") if result_accum else None units_totals_n1 = result_accum.get("TotalUnidadesNM1") if result_accum else None # facturación: usar queryfoxservice y tomar última fila #data_queryfox = get_queryfoxservice(campaign_number) ## nuevo, si es campaña cerrada busco en historico, sino es realtime if check_closed_campaign(campaign_number): ## campaign_number -> campaignid en este caso data_queryfox = get_queryfoxservice_hist(campaign_number) else: data_queryfox = get_queryfoxservice(campaign_number) desv_unids = round((units_accumulated_n - units_accumulated_n1) * 100 / units_accumulated_n, 4) if data_queryfox: data_queryfox_invoicing = get_accumulated_invoicing(campaign_number) ultima_fila = data_queryfox_invoicing[-1] invoicing_day = float(ultima_fila.get("FacturacionDia") or 0) invoicing_day_n1 = float(ultima_fila.get("FacturacionDiaN1") or 0) cumulative_invoicing = float(ultima_fila.get("FacturacionAcumuladaN") or 0) cumulative_invoicing_n1 = float(ultima_fila.get("FacturacionAcumuladaN1_Comparable") or 0) total_invoicing_n1 = float(ultima_fila.get("TotalFacturacionN1") or 0) projected_invoicing = float(ultima_fila.get("FacturacionProyectada") or 0) cumulative_invoicing_n1 = int(cumulative_invoicing_n1 or 0) total_invoicing_n1 = int(total_invoicing_n1 or 0) desv_invoicing = round((cumulative_invoicing - cumulative_invoicing_n1 ) * 100 / cumulative_invoicing, 4) campaign_info = get_n_and_n1_campaign_numbers(campaign_number) campaign_number = campaign_info.get("CampaignNumber") previous_campaign_number = campaign_info.get("PreviousCampaignNumber") ## gráfico total_units_per_day = defaultdict(float) for row in data_queryfox: # Acceder a las columnas con los nombres correctos, eliminando los espacios dia_calendario = row.get('DIA_CALENDARIO_FACT') uni_fact_x_dia = row.get('UNI FACT X DIA', 0) # Asegúrate de usar el nombre correcto de la columna con espacios # Asegurarnos de que uni_fact_x_dia sea un valor numérico (float o int) if uni_fact_x_dia is None: uni_fact_x_dia = 0 elif not isinstance(uni_fact_x_dia, (int, float)): # Si no es ni int ni float, lo transformamos en 0 uni_fact_x_dia = 0 if dia_calendario: # Verificamos que no sea None o vacío total_units_per_day[dia_calendario] += uni_fact_x_dia name_mapping = { " NO COSMETICA-COMPUTABLE COMUN": "NO COSMETICA", "CONTRATAPA (T.OFERTA 226)": "CONTRATAPA", "COSMETICA COMPUTABLE COMUN": "COSMETICA", "DEMOS COMPUTABLES (OF-12-264-266)": "DEMOS COMPUTABLES", "DIV-HOGAR CUOTAS COMPUTABLE": "DIV-HOGAR CUOTAS", "GANANCIA PLUS (T.OFERTA 29)": "GANANCIA PLUS", "NO COMPUTABLE": "NO COMPUTABLE", "OFERTA MAXIMA (T.OFERTA 150)": "OFERTA MAXIMA", "VENTA ANTICIPADA ELIMINABLE": "VENTA ANTICIPADA" } # Sumar las unidades por ProductType total_units_per_product_type = defaultdict(float) for row in data_queryfox: product_type = row.get('ProductType') # Asegúrate de usar el nombre correcto de la columna uni_fact_x_dia = row.get('UNI FACT X DIA', 0) # Asegúrate de usar el nombre correcto de la columna # Asegurarnos de que uni_fact_x_dia sea un valor numérico (float o int) if uni_fact_x_dia is None: uni_fact_x_dia = 0 elif not isinstance(uni_fact_x_dia, (int, float)): # Si no es ni int ni float, lo transformamos en 0 uni_fact_x_dia = 0 if product_type and product_type in name_mapping: product_type = name_mapping[product_type] if product_type: # Verificamos que no sea None o vacío total_units_per_product_type[product_type] += uni_fact_x_dia # Sumar las unidades por Division total_units_per_division = defaultdict(float) for row in data_queryfox: division = row.get('Division') # Asegúrate de usar el nombre correcto de la columna uni_fact_x_dia = row.get('UNI FACT X DIA', 0) # Asegúrate de usar el nombre correcto de la columna # Asegurarnos de que uni_fact_x_dia sea un valor numérico (float o int) if uni_fact_x_dia is None: uni_fact_x_dia = 0 elif not isinstance(uni_fact_x_dia, (int, float)): # Si no es ni int ni float, lo transformamos en 0 uni_fact_x_dia = 0 if division: # Verificamos que no sea None o vacío total_units_per_division[division] += uni_fact_x_dia # Convertir a listas ordenadas (si lo necesitas en un formato ordenado) total_units_per_day = sorted(total_units_per_day.items()) total_units_per_product_type = sorted(total_units_per_product_type.items()) total_units_per_division = sorted(total_units_per_division.items()) # Imprimir resultados para depuración total_units_per_day = json.dumps(total_units_per_day) # Convierte a JSON total_units_per_product_type = json.dumps(total_units_per_product_type) # Convierte a JSON total_units_per_division = json.dumps(total_units_per_division) data_queryfox_excel = list(data_queryfox) # Crear un workbook en memoria wb = openpyxl.Workbook() ws = wb.active ws.title = "QueryFox" # Encabezados headers = [ 'AA', 'CC', 'DIA', 'MES', 'AÑO', 'COD PROD', 'DESC PROD', 'ESTI_PED', 'ENTREG_PED', 'UNI_EST', 'UNI_PROY_INVOICING', '% DESCUENTO', 'UNI FACT X DIA', 'UNI ACU FACT HASTA DIA', 'UNI CORTADAS X DIA', 'UNI ACU CORTADAS HASTA DIA', 'ANT_DIA', 'ANT_ACUM', 'ANT_NODIA', 'ANT_NOACUM', 'DIA CALENDARIO FACT', 'TIPO PROD', 'DIVISION DE NEGOCIO', 'PRECIO VENTA SIN DESCUENTO' ] ws.append(headers) # Filas con datos for item in data_queryfox_excel: row = [ item['AA'], item['CC'], item['DIA'], item['MES'], item['ANO'], item['COD_PROD'], item['DESC_PROD'], item['ESTI_PED'], item['ENTREG_PED'], item['UNI_EST'], item['UNI_PROY_INVOICING'], 0, # % DESCUENTO // ("% DESCUENTO", oiqf => 0), item['UNI FACT X DIA'], item['UNI_ACU_FACT_HASTA_DIA'], item['UNI_CORTADAS_X_DIA'], item['UNI_ACU_CORTADAS_HASTA_DIA'], 0, # ANT_DIA // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_ACUM // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_NODIA // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_NOACUM // Dejar vacías (solicitado por Gigot), para macros de Excel: item['DIA_CALENDARIO_FACT'], item['ProductType'], item['Division'], item['PrecioVenta'], ] ws.append(row) # Ajustar ancho de columnas automáticamente for col_num, col_name in enumerate(headers, 1): max_length = max( (len(str(ws.cell(row=row_num, column=col_num).value)) for row_num in range(1, ws.max_row+1)), default=10 ) ws.column_dimensions[get_column_letter(col_num)].width = max_length + 2 # Guardar en memoria como xlsx output = BytesIO() wb.save(output) output.seek(0) # a partir de aca generación del file output_dir = os.path.join(settings.BASE_DIR, 'static', 'public', 'fox') # Crear directorio si no existe os.makedirs(output_dir, exist_ok=True) # Asegurar permisos (solo si estás en entorno Unix/Linux) try: os.chmod(output_dir, 0o777) except PermissionError: pass # Ignorar si el entorno no permite cambiar permisos # Generar nombre de archivo now = datetime.now() filename = now.strftime("%d-%m-%Y_%H_%M-fox-query.xlsx") # Ruta completa file_path = os.path.join(output_dir, filename) # Guardar archivo físico (asumiendo que "output" es un BytesIO o Workbook) with open(file_path, "wb") as f: f.write(output.getvalue()) # Opcional: si querés devolver la URL pública del archivo al frontend relative_url = f"/static/public/fox/{filename}" file_url = request.build_absolute_uri(relative_url) print("file_url", file_url) return render(request, 'foxqueryexport.html', {'numeros_campana': numeros_campana, 'has_data': has_data, 'filterCampaignId':filterCampaignId, 'showButtonDownloadExcel':showButtonDownloadExcel, "projected_orders": projected_orders, "estimated_orders": estimated_orders, "estimated_orders_n1": estimated_orders_n1, "orders_of_day":orders_of_day, "orders_of_day_n1":orders_of_day_n1, 'accumulated_orders': accumulated_orders, "units_of_day":units_of_day, "units_accumulated_n": units_accumulated_n, "units_accumulated_n1": units_accumulated_n1, "units_of_day_n1":units_of_day_n1, "units_totals_n1":units_totals_n1, "projected_units":projected_units, "total_orders_n1": total_orders_n1, "accumulated_orders_n1": accumulated_orders_n1, "show_facturacion": show_facturacion, "cumulative_invoicing": cumulative_invoicing, "projected_invoicing": projected_invoicing, "cumulative_invoicing_n1": cumulative_invoicing_n1, "invoicing_day": invoicing_day, "invoicing_day_n1": invoicing_day_n1, "total_invoicing_n1": total_invoicing_n1, "campaign_number":campaign_number, "previous_campaign_number": previous_campaign_number, "total_units_per_day": total_units_per_day, "total_units_per_product_type": total_units_per_product_type, "total_units_per_division": total_units_per_division, "file_url": file_url, "desv_ped": desv_ped, "desv_unids": desv_unids, "desv_invoicing": desv_invoicing, }) @login_required def giftparameters(request): numeros_campana = getCampaignNumbers() filterCampaignId = 0 showButtonDownloadExcel = 0 #no show #post if request.method == 'POST': campaign_number = int(request.POST.get('campaign_number', 0)) filterCampaignId = campaign_number showButtonDownloadExcel = 1 return render(request, 'giftparameters.html', {'numeros_campana': numeros_campana, 'filterCampaignId':filterCampaignId, 'showButtonDownloadExcel':showButtonDownloadExcel, }) @csrf_exempt def download_queryfox(request): if request.method == 'POST': try: data = json.loads(request.body) campaign_number = int(data.get('campaign_number')) data_queryfox = list(get_queryfoxservice(campaign_number)) # Crear un workbook en memoria wb = openpyxl.Workbook() ws = wb.active ws.title = "QueryFox" # Encabezados headers = [ 'AA', 'CC', 'DIA', 'MES', 'AÑO', 'COD PROD', 'DESC PROD', 'ESTI_PED', 'ENTREG_PED', 'UNI_EST', 'UNI_PROY_INVOICING', '% DESCUENTO', 'UNI FACT X DIA', 'UNI ACU FACT HASTA DIA', 'UNI CORTADAS X DIA', 'UNI ACU CORTADAS HASTA DIA', 'ANT_DIA', 'ANT_ACUM', 'ANT_NODIA', 'ANT_NOACUM', 'DIA CALENDARIO FACT', 'TIPO PROD', 'DIVISION DE NEGOCIO', 'PRECIO VENTA SIN DESCUENTO' ] ws.append(headers) # Filas con datos for item in data_queryfox: row = [ item['AA'], item['CC'], item['DIA'], item['MES'], item['ANO'], item['COD_PROD'], item['DESC_PROD'], item['ESTI_PED'], item['ENTREG_PED'], item['UNI_EST'], item['UNI_PROY_INVOICING'], 0, # % DESCUENTO // ("% DESCUENTO", oiqf => 0), item['UNI FACT X DIA'], item['UNI_ACU_FACT_HASTA_DIA'], item['UNI_CORTADAS_X_DIA'], item['UNI_ACU_CORTADAS_HASTA_DIA'], 0, # ANT_DIA // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_ACUM // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_NODIA // Dejar vacías (solicitado por Gigot), para macros de Excel: 0, # ANT_NOACUM // Dejar vacías (solicitado por Gigot), para macros de Excel: item['DIA_CALENDARIO_FACT'], item['ProductType'], item['Division'], item['PrecioVenta'], ] ws.append(row) # Ajustar ancho de columnas automáticamente for col_num, col_name in enumerate(headers, 1): max_length = max( (len(str(ws.cell(row=row_num, column=col_num).value)) for row_num in range(1, ws.max_row+1)), default=10 ) ws.column_dimensions[get_column_letter(col_num)].width = max_length + 2 # Guardar en memoria como xlsx output = BytesIO() wb.save(output) output.seek(0) # Preparar respuesta HTTP filename = "parametros_premios.xlsx" response = HttpResponse( output, content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) response['Content-Disposition'] = f'attachment; filename={filename}' return response except Exception as e: return JsonResponse({'error': f'Error al procesar la solicitud: {str(e)}'}, status=500) return JsonResponse({'error': 'Método no permitido'}, status=405)