Return-path: <martin@ssf.com.mx>
Envelope-to: omar@ssf.com.mx
Delivery-date: Mon, 28 Sep 2015 16:33:23 -0500
Received: from [189.203.170.134] (port=54152 helo=[192.168.23.74])
	by vishnu.hosting-mexico.net with esmtpsa (TLSv1.2:DHE-RSA-AES128-SHA:128)
	(Exim 4.85)
	(envelope-from <martin@ssf.com.mx>)
	id 1Zgg30-000LuR-Te
	for omar@ssf.com.mx; Mon, 28 Sep 2015 16:33:23 -0500
Message-ID: <5609B224.10104@ssf.com.mx>
Date: Mon, 28 Sep 2015 16:33:24 -0500
From: martin <martin@ssf.com.mx>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.8.0
MIME-Version: 1.0
To: omar@ssf.com.mx
Subject: Query JasperServer
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

te envio el query de exporta movimientos que esta en jasperserver

SELECT
CASE
WHEN NULLIF(m.alarms, '')::int & 64 = 64
      THEN 'CPU fuera de linea '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.alarms, '')::int & 128 = 128
      THEN 'Servicio de calibracion '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.alarms, '')::int & 256 = 256
      THEN 'Servicio sin inicio '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.alarms, '')::int & 512 = 512
      THEN 'Servicion sin fin '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.alarms, '')::int & 1024 = 1024
      THEN 'Incongruencia de esquema '
      ELSE ''
END AS decalarms,
CASE
WHEN NULLIF(m.conditions, '')::int & 512 = 512
      THEN 'ProgramacionVolumen '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 1024 = 1024
      THEN 'ProgramacionVenta '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 2046 = 2046
      THEN 'AutorizacionGPS '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 4096 = 4096
      THEN 'OperadorIdentificado '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 8192 = 8192
      THEN 'UnidadRecepcionIdetificada '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 16384 = 16384
      THEN 'PrecioDefaultAplicado '
      ELSE ''
END ||
CASE
WHEN NULLIF(m.conditions, '')::int & 32768 = 32768
      THEN 'DescuentoAplicado '
      ELSE ''
END AS deccondi,
m.consecutive as FolioMovimiento,
s.sales_folio as FolioVenta,
s.total as Venta_Total,
m.volume as Volumen,
m.mass as masa,
coalesce(mu.name,'S/N') as Unidad,
m.price as Precio,
m.discount as Descuento,
m.scheme as Esquema,
m.scheme_source as FuenteEsquema,
m.tax_rate as Impuestos,
m.initial_date as FechaInicio,
m.end_date as FechaFin,
m.longitude as Longitud,
m.latitude as Latitud,
m.altitude as Altitud,
m.radio_error as RadioError,
cu.code as CodigoCliente,
cu.name as NombreCliente,
cu.national_fiscal_identity as RFC,
cu.email as Email,
ru.description as DescripcionUnidadRecepcion,
m.reception_unit_id as Tag,
m.alarms as Alamas,
m.conditions as Condiciones,
m.density as Densidad,
m.temperature as Temperatura,
m.odometer as Odometro,
p.name as Producto,
eq.equipment_number as NumeroGaspar,
eq.application_type as TipoAplicacion,
eq.tag as EtiquetaEquipo,
m.insertion_timestamp as FechaDescarga,
('http://maps.google.com/?q=' || m.latitude || ',' || m.longitude) as 
GoogleMaps
FROM movements m
LEFT JOIN reception_units ru ON m.reception_unit_id = ru.id
LEFT JOIN customers cu ON ru.customer_id = cu.id
LEFT JOIN sales s ON m.sale_id = s.id
LEFT JOIN measure_units mu ON mu.id = m.measurement_unit
LEFT JOIN products p ON p.id = m.product_id
LEFT JOIN equipments eq ON eq.id = m.equipment_id
WHERE m.initial_date between $P{INITIAL_TIMESTAMP} and $P{FINAL_TIMESTAMP}
ORDER BY m.consecutive, m.initial_date desc