E[8i,ddlmZddlmZddlmZddlmZmZddl m Z ddl m Z ddl m Z dd lmZdd lmZdd lZdd lZdd lZdd lmZd ZdefdZdefdZddZdefdZdefdZdefdZdefdZ dZ!dede"fdZ#dZ$dZ%dede"fdZ&defdZ'y ))login_required)render) csrf_exempt) JsonResponse HttpResponse) transaction)now)datetime)report_exporta_data) getConnexionN) queryfox_histc d}t} |j5}td|j||jDcgc]}|d }}|j Dcgc]}t t||}}|cdddScc}wcc}w#1swYyxYw#t$r }gcYd}~Sd}~wwxYw)NzI SELECT id,Number FROM Campaign ORDER BY id DESC z conectando..r) r cursorprintexecute descriptionfetchalldictzip Exception)query connectionrcolumncolumnsrowresultses 4/var/www/html/planif/app/services/foxQueryService.pygetCampaignNumbersr s EJ    F . ! NN5 !06/A/ABVvayBGB;A//:KL3tC-.LGL   CM    sXB(+B BB)BB B( BB%!B(%B(( B=1B82B=8B= campaign_idct} |j5}d}|j||f|j}|r!|dcddd|r|j SS ddd|r|j yy#1swYnxYwn5#t $r)}t d|Yd}~|r|j yyd}~wwxYw |r|j yy#|r|j wwxYw)zG Dado un campaign_id, obtiene el campo Number (ej: '14/2025'). z(SELECT Number FROM Campaign WHERE Id = ?rNz!Error obteniendo campaign_number:)r rrfetchonecloserr)r!rrsqlrrs rget_the_number_for_campaignidr&(s J     F 0 AND OI.PaymentCycle = 1 AND OI.IsReturn = 0 AND OI.IsHidden = 0 AND O.Zone NOT IN (999,998,997) AND OI.IsPreviousNoStock = 0 UNION ALL -- Diferencia sin promo SELECT O.Id AS OrderId, CAST(O.InvoicingDate AS DATE) AS Fecha, P.Code, P.Name, OI.Price AS PrecioVenta, (OI.Quantity - OI.PromotionPriceQuantity) AS Cantidad, CASE WHEN OI.OrderItemStatus = 3 THEN (OI.Quantity - OI.PromotionPriceQuantity) ELSE 0 END AS NoStockCantidad, C.CommercialOrders, CP.EstimatedSales, PT.Id AS ProductTypeId, PT.Description AS ProductType, PC.Name AS Division FROM OrderItem OI INNER JOIN Orders O ON O.Id = OI.OrderId INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId INNER JOIN Product P ON P.Id = CP.ProductId INNER JOIN ProductType PT ON CP.ProductTypeId = PT.Id INNER JOIN ProductCategory PC ON P.ProductCategoryId = PC.Id INNER JOIN Campaign C ON C.Id = @CCID WHERE O.CampaignId = @CCID AND O.OrderStatus IN (5,6,7,8,10) AND OI.PromotionPriceQuantity > 0 AND OI.Quantity > OI.PromotionPriceQuantity AND OI.PaymentCycle = 1 AND OI.IsReturn = 0 AND OI.IsHidden = 0 AND O.Zone NOT IN (999,998,997) AND OI.IsPreviousNoStock = 0 UNION ALL -- Sin promo SELECT O.Id AS OrderId, CAST(O.InvoicingDate AS DATE) AS Fecha, P.Code, P.Name, OI.Price AS PrecioVenta, OI.Quantity AS Cantidad, CASE WHEN OI.OrderItemStatus = 3 THEN OI.Quantity ELSE 0 END AS NoStockCantidad, C.CommercialOrders, CP.EstimatedSales, PT.Id AS ProductTypeId, PT.Description AS ProductType, PC.Name AS Division FROM OrderItem OI INNER JOIN Orders O ON O.Id = OI.OrderId INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId INNER JOIN Product P ON P.Id = CP.ProductId INNER JOIN ProductType PT ON CP.ProductTypeId = PT.Id INNER JOIN ProductCategory PC ON P.ProductCategoryId = PC.Id INNER JOIN Campaign C ON C.Id = @CCID WHERE O.CampaignId = @CCID AND O.OrderStatus IN (5,6,7,8,10) AND ISNULL(OI.PromotionPriceQuantity,0) = 0 AND OI.PaymentCycle = 1 AND OI.IsReturn = 0 AND OI.IsHidden = 0 AND O.Zone NOT IN (999,998,997) AND OI.IsPreviousNoStock = 0; ------------------------------------------------------------ -- Agregado por producto/día (N) ------------------------------------------------------------ SELECT F.Fecha, F.Code, F.Name, F.PrecioVenta, F.ProductType, F.Division, SUM(F.Cantidad) - SUM(F.NoStockCantidad) AS [UNI FACT X DIA_PRECIO], SUM(F.NoStockCantidad) AS [UNI CORTADAS X DIA_PRECIO], MAX(F.CommercialOrders) AS CommercialOrders, MAX(F.EstimatedSales) AS EstimatedSales INTO #Agregado FROM #ItemsNormalizados F GROUP BY F.Fecha, F.Code, F.Name, F.PrecioVenta, F.ProductType, F.Division; ------------------------------------------------------------ -- CampaignDaily / CampaignCum / PriceCum / Proy ------------------------------------------------------------ SELECT Fecha, SUM([UNI FACT X DIA_PRECIO]) AS UnitsByDay INTO #CampaignDaily FROM #Agregado GROUP BY Fecha; SELECT Fecha, SUM(UnitsByDay) OVER (ORDER BY Fecha ROWS UNBOUNDED PRECEDING) AS TotalNXDay INTO #CampaignCum FROM #CampaignDaily; SELECT Code, PrecioVenta, Fecha, SUM([UNI FACT X DIA_PRECIO]) OVER ( PARTITION BY Code, PrecioVenta ORDER BY Fecha ROWS UNBOUNDED PRECEDING ) AS TotalPrice INTO #PriceCum FROM #Agregado; SELECT PC.Code, PC.PrecioVenta, PC.Fecha, CC.TotalNXDay, @TotalNM1 AS Porcentaje, PC.TotalPrice AS InvoicingUnitsProjected INTO #Proy FROM #PriceCum PC INNER JOIN #CampaignCum CC ON CC.Fecha = PC.Fecha; ------------------------------------------------------------ -- CALENDARIO REAL DE FACTURACIÓN (solo días con ventas) ------------------------------------------------------------ SELECT Fecha, ROW_NUMBER() OVER (ORDER BY Fecha) AS DIA_CALENDARIO_REAL INTO #CalendarioDias FROM ( -- Podés basarte en #Agregado (por producto/día/precio) o en #CampaignDaily (por día) SELECT DISTINCT Fecha FROM #CampaignDaily ) D ORDER BY Fecha; ------------------------------------------------------------ -- Facturación acumulada y NoStock (en UNIDADES) ------------------------------------------------------------ SELECT Code, PrecioVenta, Fecha, SUM([UNI FACT X DIA_PRECIO]) OVER ( PARTITION BY Code, PrecioVenta ORDER BY Fecha ROWS UNBOUNDED PRECEDING ) AS UNI_ACU_FACT_HASTA_DIA INTO #FactAcum FROM #Agregado; SELECT Fecha, Code, SUM([UNI CORTADAS X DIA_PRECIO]) AS UNI_CORTADAS_X_DIA INTO #NoStockPorDia FROM #Agregado GROUP BY Fecha, Code; SELECT Code, Fecha, SUM(UNI_CORTADAS_X_DIA) OVER ( PARTITION BY Code ORDER BY Fecha ROWS UNBOUNDED PRECEDING ) AS UNI_ACU_CORTADAS_HASTA_DIA INTO #NoStockAcum FROM #NoStockPorDia; ------------------------------------------------------------ -- N-1: Totales por zona (constantes) ------------------------------------------------------------ SELECT o.Zone AS ZonaPedido, SUM(oi.Quantity) AS UnidsNM1_Total INTO #NM1Totales FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND o.Zone NOT IN (999,998,997) AND oi.IsPreviousNoStock = 0 AND pt.Id != 13 GROUP BY o.Zone; ------------------------------------------------------------ -- Fechas de corte N y zonas de N ------------------------------------------------------------ SELECT DISTINCT Fecha INTO #Cortes FROM #Agregado; SELECT c.Fecha, o.Zone AS ZonaPedido INTO #ZonasN_porDia FROM #Cortes c JOIN Orders o ON o.CampaignId = @CCID JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct CP ON CP.Id = oi.CampaignProductId JOIN ProductType PT ON CP.ProductTypeId = PT.Id WHERE o.OrderStatus IN (5,6,7,8,10) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND o.Zone NOT IN (999,998,997) AND oi.IsPreviousNoStock = 0 AND CAST(o.InvoicingDate AS date) <= c.Fecha AND PT.Id != 13 GROUP BY c.Fecha, o.Zone; ------------------------------------------------------------ -- Denominador N-1: suma de totales N-1 de las zonas que ya aparecieron en N ------------------------------------------------------------ SELECT zn.Fecha, SUM(t.UnidsNM1_Total) AS TotalNM1_Hoy INTO #TotalNM1_HoyPorDia FROM #ZonasN_porDia zn JOIN #NM1Totales t ON t.ZonaPedido = zn.ZonaPedido GROUP BY zn.Fecha; ------------------------------------------------------------ -- Facturación N por día y acumulada ------------------------------------------------------------ DECLARE @TotalFactNM1 DECIMAL(18,2) = ( SELECT SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * oi.Price ) FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId INNER JOIN #TotalesZonasN TZN ON TZN.LevelId = o.Zone WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND o.Zone NOT IN (997,998,999) AND oi.IsPreviousNoStock = 0 AND pt.Id != 13 ); SELECT Fecha, SUM(PrecioVenta * [UNI FACT X DIA_PRECIO]) AS FacturacionDia INTO #FactN_Dia FROM #Agregado GROUP BY Fecha; SELECT Fecha, FacturacionDia, SUM(FacturacionDia) OVER (ORDER BY Fecha ROWS UNBOUNDED PRECEDING) AS FacturacionAcumuladaN INTO #FactN FROM #FactN_Dia; ------------------------------------------------------------ -- SELECT FINAL ------------------------------------------------------------ SELECT RIGHT(SUBSTRING(@campana, CHARINDEX('/', @campana) + 1, 4), 2) AS AA, CAST(LEFT(@campana, CHARINDEX('/', @campana) - 1) AS INT) AS CC, DAY(A.Fecha) AS DIA, MONTH(A.Fecha) AS MES, YEAR(A.Fecha) AS ANO, A.Code AS COD_PROD, A.Name AS DESC_PROD, CAST(CASE WHEN A.CommercialOrders = 0 OR A.EstimatedSales = 0 THEN 0 ELSE ROUND(A.EstimatedSales * 1.0 / A.CommercialOrders, 4) END AS DECIMAL(10,4)) AS ESTI_PED, CAST(CASE WHEN A.CommercialOrders = 0 THEN 0 ELSE ROUND(P.InvoicingUnitsProjected * 1.0 / A.CommercialOrders, 4) END AS DECIMAL(10,4)) AS ENTREG_PED, A.EstimatedSales AS UNI_EST, CAST((1.0 * @TotalNM1 * FA.UNI_ACU_FACT_HASTA_DIA) / NULLIF(tnm1d.TotalNM1_Hoy,0) AS INT) AS UNI_PROY_INVOICING, A.[UNI FACT X DIA_PRECIO] AS [UNI FACT X DIA], FA.UNI_ACU_FACT_HASTA_DIA, NSD.UNI_CORTADAS_X_DIA, NSA.UNI_ACU_CORTADAS_HASTA_DIA, CD.DIA_CALENDARIO_REAL AS DIA_CALENDARIO_FACT, A.ProductType, A.Division, A.PrecioVenta FROM #Agregado A INNER JOIN #Proy P ON P.Code = A.Code AND P.PrecioVenta = A.PrecioVenta AND P.Fecha = A.Fecha INNER JOIN #FactAcum FA ON FA.Code = A.Code AND FA.PrecioVenta = A.PrecioVenta AND FA.Fecha = A.Fecha INNER JOIN #NoStockPorDia NSD ON NSD.Code = A.Code AND NSD.Fecha = A.Fecha INNER JOIN #NoStockAcum NSA ON NSA.Code = A.Code AND NSA.Fecha = A.Fecha INNER JOIN #TotalNM1_HoyPorDia tnm1d ON tnm1d.Fecha = A.Fecha INNER JOIN #CalendarioDias CD ON CD.Fecha = A.Fecha ORDER BY A.Fecha, DIA_CALENDARIO_FACT, A.PrecioVenta; ------------------------------------------------------------ -- Limpieza de tablas temporales ------------------------------------------------------------ DROP TABLE #ItemsNormalizados; DROP TABLE #Agregado; DROP TABLE #CampaignDaily; DROP TABLE #CampaignCum; DROP TABLE #PriceCum; DROP TABLE #Proy; DROP TABLE #CalendarioDias; DROP TABLE #FactAcum; DROP TABLE #NoStockPorDia; DROP TABLE #NoStockAcum; DROP TABLE #Cortes; DROP TABLE #ZonasN_porDia; DROP TABLE #TotalNM1_HoyPorDia; DROP TABLE #FactN_Dia; DROP TABLE #FactN; DROP TABLE #NM1Totales; DROP TABLE #TotalesZonasN; Nu$No se encontró ningún SELECT finalrz) Guardando resultados en queryfox_hist...z0 Datos guardados en queryfox_hist correctamente.zError:) r&rr rrrnextsetr$rrrinsert_queryfox_histr) r!is_saver(rrrr,rrrrs rget_queryfoxservicer4js 4K@O  8 FG e EN J     F NN5;"@ A$$,1A$$,1A!!)<=  2 *0););<#s1v>#     % ? @  #)"4"453A55oo "  #dC. !Fzz,+"6,#78|zz#$0$'/@(A$B ! zz#$0$'/@(A$B ! M%6  s$A EE E)EEEc$d}t}|j5}|j||f|j}dddsdddSjDcgc]}|d }}t t ||S#1swYAxYwcc}w)um Trae EstimatedOrders de la campaña actual (N) y EstimatedOrders de la campaña anterior (N-1). a DECLARE @CCID INT = ?; DECLARE @previousCampaignId INT = ( SELECT TOP 1 Id FROM Campaign WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID) ); SELECT C.EstimatedOrders, Cprev.EstimatedOrders AS EstimatedOrdersN1 FROM Campaign C LEFT JOIN Campaign Cprev ON Cprev.Id = @previousCampaignId WHERE C.Id = @CCID; N)EstimatedOrdersEstimatedOrdersN1r)r rrr#rrrr+s rget_estimated_ordersr?s  C"J     s[N+oo  #'dCC$00 1sCF 1D 1 D#    2s$B B B c Dt|}|std|gSd}t} |j5}|j |||f|j .|j r |j |j r|j &tdgcddd|jS|j Dcgc]}|d }}|j}|Dcgc]}tt||} }td| cddd|jScc}wcc}w#1swYnxYwn4#t$r(} td| gcYd} ~ |jSd} ~ wwxYw |jy#|jwxYw)u Devuelve las unidades del día, acumuladas y proyectadas para la campaña actual (N) y la anterior (N-1). Usa tablas temporales y es compatible con ejecución en conjunto con otras queries tipo 'query fox'. /⚠️ No se encontró Number para campaign_id=uQ ------------------------------------------------------------ -- Variables base ------------------------------------------------------------ DECLARE @CCID INT = ?; DECLARE @campana VARCHAR(10) = ?; DECLARE @previousCampaignId INT = ( SELECT TOP 1 Id FROM Campaign WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID) ); ------------------------------------------------------------ -- 1) Zonas que existen en campaña N (base para todo) ------------------------------------------------------------ SELECT DISTINCT o.Zone INTO #ZonasN FROM Orders o WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999); ------------------------------------------------------------ -- 2) Índice de días de N y N-1 (para alinear día N con día N-1) ------------------------------------------------------------ SELECT DISTINCT CAST(o.InvoicingDate AS DATE) AS Fecha, DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx INTO #DiasN FROM Orders o WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999); --- nuevo -- SELECT MAX(CAST(InvoicingDate AS DATE)) AS Fecha INTO #UltimaFechaN FROM Orders WHERE CampaignId = @CCID AND OrderStatus IN (5,6,7,8,10) AND Zone NOT IN (997,998,999); ------------------------------------------------------------ -- 4) UnitsOfDay (N) = Unidades del último día de N ------------------------------------------------------------ SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsOfDay INTO #UnitsDiaN FROM Orders o JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha -- <== ESTE FILTRO JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; --select * from #UnitsDiaN ------------------------------------------------------------- -- nuevo zonas que facturaron hoy. -------------------------------------------------------------- SELECT DISTINCT o.Zone INTO #ZonasHoyN FROM Orders o JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999); --SELECT * FROM #ZonasHoyN; ------------------------------------------------------------ -- 5) UnitsOfDayNM1 (N-1) = Unidades del día equivalente, -- solo en zonas que existen en N ------------------------------------------------------------ SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsOfDayNM1 INTO #UnitsDiaN1 FROM Orders o JOIN #ZonasHoyN zh ON zh.Zone = o.Zone -- << NO #ZonasN, SOLO ZONAS DE HOY JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; --select * from #UnitsDiaN1 ------------------------------------------------------------ -- 6) UnitsAccumulatedN = Unidades acumuladas de N (todas las fechas) ------------------------------------------------------------ SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsAccumulatedN INTO #UnitsN FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; ------------------------------------------------------------ -- 7) UnitsAccumulatedN1 = Unidades acumuladas de N-1, -- solo para zonas que existen en N ------------------------------------------------------------ SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsAccumulatedN1 INTO #UnitsN1 FROM Orders o JOIN #ZonasN zn ON zn.Zone = o.Zone JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; ------------------------------------------------------------ -- 8) TotalUnidadesNM1 = Total de la campaña N-1 (todas las zonas válidas) -- (igual que "TotalPedidosNM1" en pedidos) ------------------------------------------------------------ SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS TotalUnidadesNM1 INTO #TotalesCampanaUnidadesN1 FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; ------------------------------------------------------------ -- 9) Resultado + Proyección de unidades -- Proyección = TotalUnidadesNM1 * UnitsAccumulatedN / UnitsAccumulatedN1 ------------------------------------------------------------ SELECT @campana AS Campaña, ISNULL((SELECT UnitsOfDay FROM #UnitsDiaN), 0) AS UnitsOfDay, ISNULL((SELECT UnitsOfDayNM1 FROM #UnitsDiaN1), 0) AS UnitsOfDayNM1, ISNULL((SELECT UnitsAccumulatedN FROM #UnitsN), 0) AS UnitsAccumulatedN, ISNULL((SELECT UnitsAccumulatedN1 FROM #UnitsN1), 0) AS UnitsAccumulatedN1, ISNULL((SELECT TotalUnidadesNM1 FROM #TotalesCampanaUnidadesN1), 0) AS TotalUnidadesNM1, ISNULL( ( (SELECT TotalUnidadesNM1 FROM #TotalesCampanaUnidadesN1) * (SELECT UnitsAccumulatedN FROM #UnitsN) ) / NULLIF((SELECT UnitsAccumulatedN1 FROM #UnitsN1), 0) , 0) AS ProjectedUnits ------------------------------------------------------------ -- 10) Limpieza ------------------------------------------------------------ DROP TABLE #ZonasN; DROP TABLE #DiasN; DROP TABLE #UnitsDiaN; DROP TABLE #UnitsDiaN1; DROP TABLE #UnitsN; DROP TABLE #UnitsN1; DROP TABLE #TotalesCampanaUnidadesN1; drop table #UltimaFechaN; DROP TABLE #ZonasHoyN; Nu0 No se encontró ningún SELECT final con datos.rz$ Resultados obtenidos correctamente.z Error en get_accumulated_units:) r&rr rrrr1r$rrrr) r!r(rrrr,rrowsrrrs rget_accumulated_unitsrC s4K@O  ? }MN C EJJ     F NN5;"@ A$$,1A$$,1A!!)HI  * *0););<#s1v?3tC-.?G? 8 9  * =?   "  0!4  # *  s}EAD: D:" E<D: D0D:,D5D: E0 D::E?EF  E8 E3E8F 3E88F Fc 2t|}|std|gSd}t}|j5}|j |||f|j .|j r |j |j r|j tdgcdddS|j Dcgc]}|d }}|j}|Dcgc]}tt||c}cdddScc}wcc}w#1swYyxYw)u Calcula la facturación del día, acumulada y proyectada para la campaña actual (N) y la anterior (N-1), excluyendo productos NO COMPUTABLES (pt.Id != 13). rAu" ------------------------------------------------------------ -- Variables base ------------------------------------------------------------ DECLARE @CCID INT = ?; DECLARE @campana VARCHAR(10) = ?; DECLARE @previousCampaignId INT = ( SELECT TOP 1 Id FROM Campaign WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID) ); ------------------------------------------------------------ -- 1) Zonas activas en campaña N (para filtrar) ------------------------------------------------------------ SELECT DISTINCT o.Zone INTO #ZonasN FROM Orders o WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999); ------------------------------------------------------------ -- 2) Último día de N ------------------------------------------------------------ SELECT MAX(CAST(InvoicingDate AS DATE)) AS Fecha INTO #UltimaFechaN FROM Orders WHERE CampaignId = @CCID AND OrderStatus IN (5,6,7,8,10) AND Zone NOT IN (997,998,999); SELECT DISTINCT o.Zone INTO #ZonasHoyN FROM Orders o JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999); ------------------------------------------------------------ -- 3) Facturación del día actual (N) ------------------------------------------------------------ SELECT ISNULL(CAST(SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) ) AS DECIMAL(18,2)), 0) AS InvoicingOfDay INTO #FactDiaN FROM Orders o JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; --SELECT * FROM #FactDiaN ------------------------------------------------------------ -- 4) Facturación en N-1 de las zonas que facturaron HOY en N -- (toda la campaña N-1, sin filtro por fecha) ------------------------------------------------------------ SELECT ISNULL(CAST(SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) ) AS DECIMAL(18,2)), 0) AS InvoicingOfDayN1 INTO #FactDiaN1 FROM Orders o JOIN #ZonasHoyN zh ON zh.Zone = o.Zone JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; --SELECT * FROM #FactDiaN1 ------------------------------------------------------------ -- 5) Facturación acumulada N ------------------------------------------------------------ SELECT CAST(SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) ) AS DECIMAL(18,2)) AS InvoicingAccumulatedN INTO #FactN FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @CCID AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; ------------------------------------------------------------ -- 6) Facturación acumulada N-1 (solo zonas válidas de N) ------------------------------------------------------------ SELECT CAST(SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) ) AS DECIMAL(18,2)) AS InvoicingAccumulatedN1 INTO #FactN1 FROM Orders o JOIN #ZonasN zn ON zn.Zone = o.Zone JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; ------------------------------------------------------------ -- 7) Total Facturación N-1 (todas las zonas válidas) ------------------------------------------------------------ SELECT CAST(SUM( ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) ) AS DECIMAL(18,2)) AS TotalFacturacionN1 INTO #TotalesFacturacionN1 FROM Orders o JOIN OrderItem oi ON oi.OrderId = o.Id JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId JOIN ProductType pt ON pt.Id = cp.ProductTypeId WHERE o.CampaignId = @previousCampaignId AND o.OrderStatus IN (5,6,7,8,10) AND o.Zone NOT IN (997,998,999) AND oi.PaymentCycle = 1 AND oi.IsReturn = 0 AND oi.IsHidden = 0 AND oi.IsPreviousNoStock = 0 AND pt.Id != 13; -- testing -- -- Mostrar zonas + facturación del día de hoy --SELECT -- uf.Fecha AS FechaHoy, -- o.Zone, -- SUM( -- ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) -- + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) -- ) AS FacturacionZona -- FROM Orders o -- JOIN #UltimaFechaN uf -- ON CAST(o.InvoicingDate AS DATE) = uf.Fecha --JOIN OrderItem oi -- ON oi.OrderId = o.Id --JOIN CampaignProduct cp -- ON cp.Id = oi.CampaignProductId --JOIN ProductType pt -- ON pt.Id = cp.ProductTypeId --WHERE o.CampaignId = @CCID -- AND o.OrderStatus IN (5,6,7,8,10) -- AND o.Zone NOT IN (997,998,999) -- AND oi.PaymentCycle = 1 -- AND oi.IsReturn = 0 -- AND oi.IsHidden = 0 -- AND oi.IsPreviousNoStock = 0 -- AND pt.Id != 13 --GROUP BY -- uf.Fecha, -- o.Zone --ORDER BY -- FacturacionZona DESC; -- testing -- -- SELECT -- o.Zone, -- SUM( -- ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0) -- + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0) -- ) AS FacturacionZonaN1 --FROM Orders o --JOIN #ZonasHoyN zh ON zh.Zone = o.Zone -- << SOLO ZONAS DE HOY EN N --JOIN OrderItem oi ON oi.OrderId = o.Id --JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId --JOIN ProductType pt ON pt.Id = cp.ProductTypeId --WHERE o.CampaignId = @previousCampaignId -- Campaña N-1 -- AND o.OrderStatus IN (5,6,7,8,10) -- AND o.Zone NOT IN (997,998,999) -- AND oi.PaymentCycle = 1 -- AND oi.IsReturn = 0 -- AND oi.IsHidden = 0 -- AND oi.IsPreviousNoStock = 0 -- AND pt.Id != 13 -- GROUP BY o.Zone -- ORDER BY o.Zone; ------------------------------------------------------------ -- 8) Resultado y proyección ------------------------------------------------------------ SELECT @campana AS Campaña, ISNULL((SELECT InvoicingOfDay FROM #FactDiaN), 0) AS FacturacionDia, ISNULL((SELECT InvoicingOfDayN1 FROM #FactDiaN1), 0) AS FacturacionDiaN1, ISNULL((SELECT InvoicingAccumulatedN FROM #FactN), 0) AS FacturacionAcumuladaN, ISNULL((SELECT InvoicingAccumulatedN1 FROM #FactN1), 0) AS FacturacionAcumuladaN1_Comparable, ISNULL((SELECT TotalFacturacionN1 FROM #TotalesFacturacionN1), 0) AS TotalFacturacionN1, ISNULL( CAST( ( ISNULL((SELECT TotalFacturacionN1 FROM #TotalesFacturacionN1), 0) * ISNULL((SELECT InvoicingAccumulatedN FROM #FactN), 0) ) / NULLIF( ISNULL((SELECT InvoicingAccumulatedN1 FROM #FactN1), 0), 0) AS DECIMAL(18,2)), 0) AS FacturacionProyectada; ------------------------------------------------------------ -- Limpieza ------------------------------------------------------------ DROP TABLE #ZonasN; DROP TABLE #FactDiaN; DROP TABLE #FactDiaN1; DROP TABLE #FactN; DROP TABLE #FactN1; DROP TABLE #TotalesFacturacionN1; DROP TABLE #UltimaFechaN; DROP TABLE #ZonasHoyN; Nu&⚠️ Sin resultados de facturación.r) r&rr rrrr1rrr) r!r(rrrr,rrBrs rget_accumulated_invoicingrE s4K@O  ? }MN | E|J     9u{O<=  (V^^-=   (V^^-=    % : ; 9 9&,%7%78c3q688 378CS#&'8 9 998 9 9s6AD  D +D 9 DD D7D  D  Dc8t}tjd}d} |j5}|j ||j }|s%t d ddd|jy|d|d|d|d|d |d d }|cddd|jS#1swYnxYwn2#t$r&}t d |Yd}~|jyd}~wwxYw |jy#|jwxYw) uy Devuelve la campaña activa según la fecha actual. Busca en la tabla Campaign usando la conexión externa. z%Y-%m-%dz SELECT TOP 1 Id, Number, StartDatePick, EndDateCC, EndDatePick, Sequence FROM Campaign WHERE CAST(GETDATE() AS date) BETWEEN CAST(StartDatePick AS date) AND CAST(EndDatePick AS date) ORDER BY Sequence DESC u) No se encontró ninguna campaña activa.Nrr )IdNumber StartDatePick EndDateCC EndDatePickSequencez Error en get_current_campaign:) r r strftimerrr#rr$r)rtodayrrrcampaignrs rget_current_campaignrT(s  J ENN: &E E     F NN5 !//#CAB  , !fa&!$Q V"1vF H!  , -   $  /3 % ,  sRC0B6'CB6 C6B?;CD C2 C-D-C22DDreturncRd}d}|rd|d}nd}t} |j5}|j||f|j}|s(t d| ddd|j yt |d}|rt d |d nt d |d |cddd|j S#1swYnxYwn2#t$r&}t d |Yd}~|j yd}~wwxYw |j y#|j wxYw) u Valida en DB si HOY (según hora de Argentina) es el EndDateCC de la campaña con Id = campaign_id. Usa SYSDATETIMEOFFSET() con AT TIME ZONE para evitar diferencias horarias. z 2025-10-28FzT SELECT CASE WHEN CAST(EndDateCC AS date) = CAST('z' AS date) THEN 1 ELSE 0 END AS IsLastDay FROM Campaign WHERE Id = ? aH SELECT CASE WHEN CAST(EndDateCC AS date) = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AT TIME ZONE 'Argentina Standard Time' AS date) THEN 1 ELSE 0 END AS IsLastDay FROM Campaign WHERE Id = ? zNo existe Campaign.Id = Nrz3Hoy (hora Argentina) es EndDateCC para Campaign.Id=.z7 Hoy (hora Argentina) NO es EndDateCC para Campaign.Id=z% Error en is_last_day_of_campaign_id:)r rrr#rr$boolr) r! forced_todayuse_forced_dater%conncurris_lastrs ris_last_day_of_campaign_idr^Ps4 LO;;G.I    >D [[] c KKk^ ,,,.C0 >? $ 3q6lGKK=XYZ[OP[}\]^_  $ %     5q9  $  sRC5C C90C) CC CD C? C:%D:C??DD&c d}t||y)NT)r4)r!r3s rsave_foxquery_servicer`sG W- c  t|r.tjj|j |syt j 5|Dcgc]}td&id|d|d|jdd|jdd|jdd|jdd |jd d |jd d |jd d |jd d |jd d|jdd|jdd|jddd|jdd|jdd|jdd|jdd|jddd|jddd|jddd|jddd|jdd|jdd|jdd |jd!}}tjj|d"#d$d$d$y%cc}w#1swYy%xYw#t$r }Yd$}~yd$}~wwxYw)'zR Inserta los resultados de get_queryfoxservice en la tabla queryfox_hist.  CampaignIdFrdCampaignNumberAACCDIAMESANOCOD_PROD DESC_PRODESTI_PED ENTREG_PEDUNI_ESTUNI_PROY_INVOICINGPORC_DESCUENTOrUNI_FACT_X_DIAUNI FACT X DIAUNI_ACU_FACT_HASTA_DIAUNI_CORTADAS_X_DIAUNI_ACU_CORTADAS_HASTA_DIAANT_DIAANT_ACUM ANT_NODIA ANT_NOACUMDIA_CALENDARIO_FACT TIPO_PROD ProductTypeDIVISION_DE_NEGOCIODivisionPRECIO_VENTA_SIN_DESCUENTO PrecioVentai) batch_sizeNT) check_closed_campaignrobjectsfilterdeleteratomicr8 bulk_creater)rr!r(r bulk_objsrs rr2r2s4  -  ! ! ( (K ( @ G G I   !! I<#;:9*#2wwt}wwt}     !WWZ0"ggk2!WWZ0 #ww|4 GGI.(+ww/C'D$'77+;Q#?$'77+;#< ,/773K+L!"(+ww/C'D#$03ww7S/T%& GGIq1'(!WWZ3)*"ggk15+, #ww|Q7-.),0E(F/0"ggm412), (;3403ww}/E5I@  ! ! - -iC - HC! IHG! IH sG;I!I!IG I$#II!III!I!! I4/I4ctjj|j}|rt d|d|St d|d|S)u Verifica si una campaña ya fue procesada y almacenada en queryfox_hist. Retorna True si existen registros, False si no. rcu La campaña z* ya fue cerrada (existe en queryfox_hist).u La campaña u aún no fue cerrada.)rrrexistsr)r!rs rrrs[  " " ) )[ ) A H H JF  [M)STU M  k]*?@A Mrac tjj|j}|sgSg}|D]}id|dd|dd|dd|dd|dd|dd|dd |d t |d nd d |d t |d nd d |d d|dd|dd|dd|dd|dd|dd|d|d|dt |dnd d}|j ||S#t $r }gcYd }~Sd }~wwxYw)u Devuelve los registros de queryfox_hist para una campaña, adaptados con las mismas claves que espera el exportador Excel. rcrfrgrhrirjrkrlrmNrrnrorprsrrrtrurvr{r}r|r~r)rr)rrrvaluesfloatappendr)r! registros data_excelritemrs rget_queryfoxservice_histrs $!))00K0HOOQ I  $Aagagqxqx  qx  AjM Q{^AjM4ME!J-0ST,8SeAlO4YZ1Y<%a(<&=!!$4"5)!,D*E%a(<&=-a0L.M &q)>'?!"q~#$34IJKgIhItuQ'C%DEz{'D*   d #- $2  s#1C3B>C33 D<D=DD)F)(django.contrib.auth.decoratorsrdjango.shortcutsrdjango.views.decorators.csrfr django.httprr django.dbrdjango.utils.timezoner r services.foxReportServicer services.connexionsServicer pyodbcpandaspdcsvapp.Models.fox_products_modelsrr r9r&r.r4r;r?rCrErTrXr^r`r2rrrrarrs9#42!%;5  :0s0&s&TMd |c|@  c  HjsjZT93T9v&P5C5D5n 8x s t )#)ra