Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.ideaspymes.arthyweb.ventas.service.impl; import com.ideaspymes.arthyweb.ventas.model.Vendedor; import java.math.BigDecimal; import javax.ejb.Stateless; import javax.ejb.TransactionAttribute; import javax.ejb.TransactionAttributeType; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.joda.time.LocalDate; /** * * @author christian.romero */ @Stateless @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW) public class GestionComercialService { @PersistenceContext(unitName = "GCVentasPalermoASUPU") private EntityManager em; public Double getMetaCantidadAcum(Integer sucursalId, Integer vendedorId) { Double cantidadAcum = 0d; String bd = ""; switch (sucursalId) { case 1: bd = "[Arthy]"; //bd = "[GestionDeVentasASU]"; break; case 2: bd = "[Gestion de Ventas CDE]"; break; case 4: bd = "[Gestion de Ventas PJC]"; break; case 111: bd = "[Gestion de Ventas ENC]"; break; } try { BigDecimal cant = (BigDecimal) em.createNativeQuery(getConsultaCantidadCajasAcum(vendedorId, bd)) .getSingleResult(); if (cant != null) { cantidadAcum = cant.doubleValue(); } } catch (Exception e) { System.out.println("No ventas acum: " + e.getMessage()); } return cantidadAcum; } public static String getConsultaCantidadCajasAcum(Integer vendedorId, String bd) { LocalDate localDate = new LocalDate(); int ultimoDia = localDate.dayOfMonth().getMaximumValue(); int mes = localDate.getMonthOfYear(); int anio = localDate.getYear(); return "select sum(x.cant_caja) from \n" + "(SELECT \n" + " FD.[ID Sucursales] as sucursal_key\n" + " ,FD.[ID Vendedores] as cod_vendedor --\n" + " ,FD.[ID Comprobantes Ventas] as comprobante\n" + " ,FD.[Numero] as numero_fac\n" + " ,FD.[ID Linea] as linea_fac \n" + " ,DATEADD(dd, 0, DATEDIFF(dd, 0, F.[Fecha])) as fecha_key\n" + " ,F.[ID Clientes] as cod_cliente --\n" + " ,(SELECT \n" + " --OBTENEMOS LA DESCRIPCION DE LA ZONA EN LA QUE SE EJECUTO LA VENTA\n" + " CASE WHEN [Clasificacion 8] = '1' OR [Clasificacion 8] IS NULL THEN 'NO DEFINIDO' ELSE [Clasificacion 8] END\n" + " " + bd + ".FROM [VTA_Clientes] C WHERE C.[ID Clientes]=F.[ID Clientes] AND C.[ID Sucursales] = FD.[ID Sucursales]) as zona_desc,\n" + " --OBTENEMOS LA DESCRIPCION EL TERRITORIO EN LA QUE SE EJECUTO LA VENTA\n" + " (SELECT \n" + " CASE WHEN [Clasificacion 2] = '1' OR [Clasificacion 2] IS NULL THEN 'NO DEFINIDO' ELSE [Clasificacion 2] END\n" + " " + bd + ".FROM [VTA_Clientes] C WHERE C.[ID Clientes]=F.[ID Clientes] AND C.[ID Sucursales] = FD.[ID Sucursales]) as territorio_desc\n" + " ,FD.[ID Productos] as cod_producto --\n" + " ,FD.[ID Unidades Medidas] as unidad_key\n" + " ,FD.[Cantidad Envase] as cantidad_envase\n" + " ,FD.[Total Linea ML] as importe \n" + " ,ISNULL (CAST (FD.[Promocion] AS INT),0) as bonicicacion\n" + " ,ISNULL (FD.[PromoPLM3] ,0) as cantidad_promo\n" + " ,(CASE WHEN FD.[PromoId]=0 THEN NULL ELSE FD.[PromoId] END) as promo_key\n" + " --CALCULOS PARA OBTENER VOLUMENES DE VENTA POR CAJAS,GRUESAS Y CAJETILLAS\n" + " ,CAST (case when U.[ID Unidades Medidas] = 15 then cast (FD.[Cantidad Envase] as numeric(14,3)) / 50 else (case when U.[ID Unidades Medidas] = 25 then cast (FD.[Cantidad Envase] as numeric(14,3)) / calc.cantidad_caje_por_gruesa / 50 else (case when U.[ID Unidades Medidas] <> 16 then 0 else FD.[Cantidad Envase] end) end ) end AS numeric (14,3) ) as cant_caja\n" + "\n" + " ,CAST ( case when U.[ID Unidades Medidas] = 16 then cast (FD.[Cantidad Envase] as numeric(14,3)) * 50 else (case when U.[ID Unidades Medidas] = 25 then cast (FD.[Cantidad Envase] as numeric(14,3)) / calc.cantidad_caje_por_gruesa else (case when U.[ID Unidades Medidas] <> 15 then 0 else FD.[Cantidad Envase] end) end ) end AS numeric (14,3) )as cant_gruesa\n" + "\n" + " ,CAST (case when U.[ID Unidades Medidas] <> 29 then cast (FD.[Cantidad Envase] as numeric(14,3)) * U.[Cantidad] else 0 end AS numeric (14,3) )as cant_cajetilla\n" + " \n" + "FROM " + bd + ".[VTA_D Comprobantes de Ventas] FD WITH (NOLOCK)\n" + "INNER JOIN " + bd + ".[VTA_C Comprobantes de Ventas] F WITH (NOLOCK) \n" + " ON FD.Origen = F.Origen \n" + " AND FD.[ID Sucursales] = F.[ID Sucursales] \n" + " AND FD.[ID Vendedores] = F.[ID Vendedores] \n" + " AND FD.[ID Comprobantes Ventas] = F.[ID Comprobantes Ventas] \n" + " AND FD.numero = F.numero\n" + "--\n" + "LEFT JOIN " + bd + ".[STK_UM de Productos] U WITH (NOLOCK)\n" + " ON FD.[ID Unidades Medidas] = U.[ID Unidades Medidas]\n" + " AND FD.[ID Productos] = U.[ID Productos]\n" + "-- TABLA VIRTUAL PARA OBTENER CANTIDA DE PABILOS POR CAJETILLA\n" + "LEFT JOIN " + bd + ".calc_pabilo calc ON FD.[ID Productos] = calc.[ID Productos]\n" + "WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, F.[Fecha])) between " + convertFechaSQL(1, mes, anio) + " and " + convertFechaSQL(ultimoDia, mes, anio) + " \n" + " AND F.[Status] <> '11' \n" + " AND FD.[ID Vendedores] = " + vendedorId + ") x"; } private static String convertFechaSQL(int dia, int mes, int anio) { String sdia; if (dia < 10) { sdia = "0" + dia; } else { sdia = "" + dia; } String smes; if (dia < 10) { smes = "0" + mes; } else { smes = "" + mes; } String sanio = "" + anio; return "'" + sdia + "/" + smes + "/" + sanio + "'"; } }