WITH DevVeiTmp AS (SELECT Dev.DEV_ID, COALESCE(VeiCga.VEI_ID, COALESCE(VeiDev.VEI_ID, -999999)) VEI_ID, CAST(Dev.DEV_ID AS TEXT) || ' - ' || Dev.DEV_FingerPrint DEV_Descricao, CASE WHEN VeiCga.VEI_ID IS NULL THEN CASE WHEN VeiDev.VEI_ID IS NULL THEN 'Dispositivo Sem Veículo Cadastrado' ELSE CAST(VeiDev.VEI_ID AS TEXT) || ' - ' || VeiDev.VEI_Modelo || '/' || VeiDev.VEI_Marca || ' - ' || VeiDev.VEI_Placa END ELSE CAST(VeiCga.VEI_ID AS TEXT) || ' - ' || VeiCga.VEI_Modelo || '/' || VeiCga.VEI_Marca || ' - ' || VeiCga.VEI_Placa END VEI_Descricao, date_trunc('day', CgaDia.DataHora) AnoMesDia, VeiDev.VEI_Placa, CgaDia.CGA_ID FROM PNVW_PN_Devices Dev LEFT JOIN PNVW_PN_DevApps DevApps ON Dev.DEV_ID = DevApps.DEV_ID AND DevApps.COD_Sistema <> -999999 AND Dev.DEV_Tipo = 1 LEFT JOIN PNVW_PN_Aplicativos Apps ON Apps.APP_Nome = 'Prisma Fleet.apk' AND Apps.APP_ID = DevApps.APP_ID LEFT JOIN PNVW_CargasDia CgaDia ON Dev.DEV_ID = CgaDia.DEV_ID AND date_trunc('day', CgaDia.DataHora) >= to_timestamp('2015-01-29 00:00:00', 'yyyy-MM-dd') AND date_trunc('day', CgaDia.DataHora) <= to_timestamp('2015-01-29 23:59:59', 'yyyy-MM-dd') LEFT JOIN PNVW_Veiculos VeiCga ON CgaDia.VEI_ID = VeiCga.VEI_ID LEFT JOIN PNVW_Veiculos VeiDev ON DevApps.COD_Sistema = VeiDev.VEI_ID GROUP BY Dev.DEV_ID, COALESCE(VeiCga.VEI_ID, COALESCE(VeiDev.VEI_ID, -999999)), CAST(Dev.DEV_ID AS TEXT) || ' - ' || Dev.DEV_FingerPrint, CASE WHEN VeiCga.VEI_ID IS NULL THEN CASE WHEN VeiDev.VEI_ID IS NULL THEN 'Dispositivo Sem Veículo Cadastrado' ELSE CAST(VeiDev.VEI_ID AS TEXT) || ' - ' || VeiDev.VEI_Modelo || '/' || VeiDev.VEI_Marca || ' - ' || VeiDev.VEI_Placa END ELSE CAST(VeiCga.VEI_ID AS TEXT) || ' - ' || VeiCga.VEI_Modelo || '/' || VeiCga.VEI_Marca || ' - ' || VeiCga.VEI_Placa END, date_trunc('day', CgaDia.DataHora), VeiDev.VEI_Placa, CgaDia.CGA_ID), DevVei AS (SELECT DEV_ID, VEI_ID, DEV_Descricao, VEI_Descricao || COALESCE(' - ' || string_agg(to_char(AnoMesDia, 'dd/MM/yyyy'), ', ' ORDER BY AnoMesDia), '') VEI_Descricao, VEI_Placa, DevVeiTmp.CGA_ID FROM DevVeiTmp GROUP BY DEV_ID, VEI_ID, DEV_Descricao, VEI_Descricao, VEI_Placa, DevVeiTmp.CGA_ID ORDER BY DEV_ID, VEI_ID), TrackerData AS (SELECT TrkPts.DEV_ID, MAX(TrkPts.GPS_ID) GPS_ID FROM PNVW_TrackerPontos TrkPts WHERE (TrkPts.GPS_DH >= to_timestamp('2015-01-29 00:00:00', 'yyyy-MM-dd HH24:mi:ss') AND TrkPts.GPS_DH <= to_timestamp('2015-01-29 23:59:59', 'yyyy-MM-dd HH24:mi:ss')) GROUP BY TrkPts.DEV_ID), Mtas AS ( SELECT Cgas.CGA_ID, Mta.MTA_Nome FROM PNVW_Cargas Cgas LEFT JOIN PNVW_Motoristas Mta ON Cgas.MTA_ID = Mta.MTA_ID WHERE Cgas.VEI_ID <> -1000 AND date_trunc('day', Cgas.CGA_Entregaini) >= to_timestamp('2015-01-29 00:00:00', 'yyyy-MM-dd') AND date_trunc('day', Cgas.CGA_Entregafim) <= to_timestamp('2015-01-29 23:59:59', 'yyyy-MM-dd') GROUP BY Cgas.CGA_ID, Mta.MTA_Nome ORDER BY Mta.MTA_Nome )
SELECT CAST(DevVei.VEI_ID AS TEXT) || ' - ' || DevVei.VEI_Descricao || ' / ' || CAST(DevVei.DEV_ID AS TEXT) || ' - ' || DevVei.DEV_Descricao Descricao, TrkPts.GPS_ID, TrkPts.GPS_DH, TrkPts.GPS_LAT,TrkPts.GPS_LON, DevVei.VEI_Placa, Mtas.MTA_Nome FROM DevVei JOIN Mtas ON DevVei.CGA_ID = Mtas.CGA_ID JOIN TrackerData ON DevVei.DEV_ID = TrackerData.DEV_ID JOIN PNVW_TrackerPontos TrkPts ON TrkPts.DEV_ID = TrackerData.DEV_ID AND TrkPts.GPS_ID = TrackerData.GPS_ID WHERE ((DevVei.VEI_Descricao ILIKE '%%') OR (DevVei.DEV_Descricao ILIKE '%%')) ORDER BY DevVei.VEI_ID, DevVei.DEV_ID