select
movement.movementid, (endcall-startcall) AS duracion,
nombre||' '||apellido AS usuario, movement.description AS desc, destination.name as destination, client.name as client,  fromnumber, (case when movement.moment is null then movement.validfrom::timestamp else movement.moment end) as moment, amount, (case when startcall is null then validfrom else startcall end), (case when endcall is null then validto else endcall end), tonumber,
targetlocation.country as pais, targetlocation.city as ciudad, targetlocation.group as grupo
from movement
left join targetlocation on targetlocation.targetid=movement.targetid
inner join clientbag on clientbag.clientbagid=movement.clientbagid
inner join client on client.clientid=clientbag.clientid
inner join peerclientbag on peerclientbag.clientbagid=clientbag.clientbagid
inner join peer on peer.peerid=peerclientbag.peerid
inner join product on product.productid=peer.productid
left join cdr on cdr.cdrid=movement.cdrid
left join movementorigin on movementorigin.movementid=movement.movementid
left join recurso on recurso.recursoid=movementorigin.recursoid
left join destination on destination.destinationid=move	ment.destinationid


where

	case when '{%dteFechaDesdeFiltro%}' <> '1900-01-01' and '{%dteFechaHastaFiltro%}' = '1900-01-01' then
            (case when movement.moment is null then movement.validfrom::timestamp else movement.moment end)::date >= '{%dteFechaDesdeFiltro%}'
			and case when destination.name is null then '' else destination.name end ilike  			'{%strDestinationFiltro%}%'

			and (case when fromnumber is not null then fromnumber ilike '{%strNumeroFiltro%}%' else '' = '' end)
			and peer.number ilike '{%strNumeroFiltro%}%'
 			and client.name ilike  '{%strClienteFiltro%}%'
			and (case when '{%cmbUsuarioFiltro%}' = '' then '' = ''
					else recurso.recursoid ilike '{%cmbUsuarioFiltro%}' end)
			and (case when '{%cmbMedioFiltro%}' = '' then '' = ''
					else rechargemediaid ilike '{%cmbMedioFiltro%}' end)
			and (case when '{%cmbReseller%}' = '' then '' = ''
					else client.resellerid ilike '{%cmbReseller%}' end)
			and producttypeid=1
			and movement.description ilike '{%strDescripcionFiltro%}%'
			and client.resellerid in (select resellerid from recursoreseller where recursoid = [%USUARIO%])

	when '{%dteFechaDesdeFiltro%}' = '1900-01-01' and '{%dteFechaHastaFiltro%}' <> '1900-01-01' then
            (case when movement.moment is null then movement.validfrom::timestamp else movement.moment end)::date <= '{%dteFechaHastaFiltro%}'
			and case when destination.name is null then '' else destination.name end ilike  			'{%strDestinationFiltro%}%'

			and (case when fromnumber is not null then fromnumber ilike '{%strNumeroFiltro%}%' else '' = '' end)
			and peer.number ilike '{%strNumeroFiltro%}%'
 			and client.name ilike  '{%strClienteFiltro%}%'
			and (case when '{%cmbUsuarioFiltro%}' = '' then '' = ''
					else recurso.recursoid ilike '{%cmbUsuarioFiltro%}' end)
			and (case when '{%cmbMedioFiltro%}' = '' then '' = ''
					else rechargemediaid ilike '{%cmbMedioFiltro%}' end)
			and (case when '{%cmbReseller%}' = '' then '' = ''
					else client.resellerid ilike '{%cmbReseller%}' end)
			and producttypeid=1
			and movement.description ilike '{%strDescripcionFiltro%}%'
			and client.resellerid in (select resellerid from recursoreseller where recursoid = [%USUARIO%])

	when '{%dteFechaDesdeFiltro%}'<> '1900-01-01' and '{%dteFechaHastaFiltro%}' <> '1900-01-01' then
            (case when movement.moment is null then movement.validfrom::timestamp else movement.moment end)::date  BETWEEN '{%dteFechaDesdeFiltro%}' and '{%dteFechaHastaFiltro%}'
			and case when destination.name is null then '' else destination.name end ilike  			'{%strDestinationFiltro%}%'

			and (case when fromnumber is not null then fromnumber ilike '{%strNumeroFiltro%}%' else '' = '' end)
			and peer.number ilike '{%strNumeroFiltro%}%'
 			and client.name ilike  '{%strClienteFiltro%}%'
			and (case when '{%cmbUsuarioFiltro%}' = '' then '' = ''
					else recurso.recursoid ilike '{%cmbUsuarioFiltro%}' end)
			and (case when '{%cmbMedioFiltro%}' = '' then '' = ''
					else rechargemediaid ilike '{%cmbMedioFiltro%}' end)
			and (case when '{%cmbReseller%}' = '' then '' = ''
					else client.resellerid ilike '{%cmbReseller%}' end)
			and producttypeid=1
			and movement.description ilike '{%strDescripcionFiltro%}%'
			and client.resellerid in (select resellerid from recursoreseller where recursoid = [%USUARIO%])

	when '{%dteFechaDesdeFiltro%}'= '1900-01-01' and '{%dteFechaHastaFiltro%}' = '1900-01-01' then

			(case when fromnumber is not null then fromnumber ilike '{%strNumeroFiltro%}%' else '' = '' end)
			and case when destination.name is null then '' else destination.name end ilike  			'{%strDestinationFiltro%}%'
			and peer.number ilike '{%strNumeroFiltro%}%'
 			and client.name ilike  '{%strClienteFiltro%}%'
			and (case when '{%cmbUsuarioFiltro%}' = '' then '' = ''
					else recurso.recursoid ilike '{%cmbUsuarioFiltro%}' end)
			and (case when '{%cmbMedioFiltro%}' = '' then '' = ''
					else rechargemediaid ilike '{%cmbMedioFiltro%}' end)
			and (case when '{%cmbReseller%}' = '' then '' = ''
					else client.resellerid ilike '{%cmbReseller%}' end)
			and producttypeid=1
			and movement.description ilike '{%strDescripcionFiltro%}%'
			and client.resellerid in (select resellerid from recursoreseller where recursoid = [%USUARIO%])

END


order by (case when movement.moment is null then movement.validfrom::timestamp else movement.moment end)