-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--  Author		: Jesus Baizabal
--  email		: baizabal.jesus@hbues.net, breton.miguel@hbues.net, javier.garcia@hbues.net
--  Create date		: July 16, 2026
--  Description		: Build Logic views for Solicitud de viaje
--  TODO		: clean
--  @Last_patch		: --
--  @license		: MIT License (http://www.opensource.org/licenses/mit-license.php)
--  Database owner	: hbues.net
--  @status		: Stable
--  @version		: 1.0.0
-- Copyright © 2026, HB Universal Engineering Systems - hbues.net, All Rights Reserved
-------------------------------------------------------------------------------
-- Description:        Verbose description of what the query does goes here. Be specific and don't be
--                     afraid to say too much. More is better, than less, every single time. Think about
--                     "what, when, where, how and why" when authoring a description.
-- Call by:            [schema.usp_ProcThatCallsThis]
--                     [Application Name]
--                     [Job]
--                     [PLC/Interface]
-- Affected table(s):  [schema.TableModifiedByProc1]
--                     [schema.TableModifiedByProc2]
-- Used By:            Functional Area this is use in, for example, Payroll, Accounting, Finance
-- Parameter(s):       @param1 - description and usage
--                     @param2 - description and usage
-- Usage:              EXEC dbo.usp_DoSomeStuff
--                         @param1 = 1,
--                         @param2 = 3,
--                         @param3 = 2
--                     Additional notes or caveats about this object, like where is can and cannot be run, or
--                     gotchas to watch for when using it.
-------------------------------------------------------------------------------

-- ---------------------------------------------------------------
-- NOTE TEST ZONE
-- ---------------------------------------------------------------
/*
with crt as (
	select 
	-- ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC)
	row_number() over(partition by ztg.no_viaje,ztg.id_area order by ztg.no_viaje desc) as 'idx',
	ztg.num_guia , ztg.status_guia, ztg.prestamo,
	ztg.id_cliente, zcl.nombre, 
	--max(ztg.id_tipo_operacion) as 'id_tipo_operacion', 
	ztg.id_area, ztg.no_viaje 
	  from  gstdb.dbo.trafico_guia as ztg
	  inner join gstdb.dbo.trafico_cliente as zcl
	       on ztg.id_cliente = zcl.id_cliente
	where ztg.status_guia not in ('B') 
		 and  
		 ztg.no_viaje in (33208) 
       	-- and id_area = 8 --and ztg.id_asignacion = 139323
--	order by no_viaje desc
) 
select * from crt where idx = 1
	
	group by
		-- ztg.num_guia, 
		ztg.id_cliente, zcl.nombre, 
		--ztg.id_tipo_operacion,
	       	ztg.id_area, ztg.no_viaje 

      select dspa.id_asignacion, count(dspa.consecutivo) as 'consecutives', dspa.id_area_pedido
       from gstdb.dbo.desp_asignaciondet as dspa
	where dspa.id_asignacion = 139323
	group by 
	dspa.id_asignacion
--	,dspa.consecutivo
	,dspa.id_area_pedido
	 
*/

/*
Label      count      
---------- -----------
Finalizado       50500
Programado          49
Activo              65


class       act        
----------- -----------
Historicos        50500
Activos              65
Programados          34

*/

/*
with summ (class,act)
as (
	select 'Historicos' as 'class', count(id_asignacion) from sistemas.dbo.twrview_Historialviajes 
	union all
	select 'Activos' as 'class', count(id_asignacion) from sistemas.dbo.twrview_Viajesactivos
	union all
	select 'Programados' as 'class', count(id_asignacion) from sistemas.dbo.twrview_Programados
	union all
	select 'PorLiberarse' as 'class', count(id_asignacion) from sistemas.dbo.twrview_PorLiberarse
) 
select * from summ
union all
with summary  (Label,count)
as (
	select Label, count(id_asignacion) from sistemas.dbo.gst_tms_view_tower_trips where Label = 'Finalizados' and HAYR > 2025 group by Label
	union all
	select Label,count(id_asignacion) from sistemas.dbo.gst_tms_view_tower_trips where Label = 'PorLiberarse' and HAYR > 2025 group by Label
	union all
	select Label,count(id_asignacion) from sistemas.dbo.gst_tms_view_tower_trips where Label = 'Activos' and HAYR > 2025 group by Label
	union all
	select Label,count(id_asignacion) from sistemas.dbo.gst_tms_view_tower_trips where Label = 'Programados' and PYR > 2025 group by Label
) 
select * from summary
GO
*/

-- select id_area,no_guia,num_guia,no_viaje from gstdb.dbo.trafico_guia where no_viaje = 9632
-- ---------------------------------------------------------------
-- View for get onlyone record from trafico_guia
-- ---------------------------------------------------------------
/*
IF OBJECT_ID ('gst_tms_view_tower_guias', 'U') IS NOT NULL
    DROP table gst_tms_view_tower_guias
GO
IF OBJECT_ID ('gst_tms_view_tower_guias', 'V') IS NOT NULL
    DROP view gst_tms_view_tower_guias
GO


-- NOTE version 1.0.0
create or alter view gst_tms_view_tower_guias
with encryption
as
with crt as (
    select -- 168526 58166 + 1225 
	row_number() over(partition by ztg.no_viaje,ztg.id_area order by ztg.no_viaje) as 'idx',
	ztg.num_guia,
	--ztg.status_guia, 
	--ztg.prestamo,
	ztg.id_cliente, 
	zcl.nombre, 
	ztg.id_tipo_operacion, 
	ztg.id_area, 
	ztg.no_viaje 
    from  gstdb.dbo.trafico_guia as ztg
    inner join gstdb.dbo.trafico_cliente as zcl
       on ztg.id_cliente = zcl.id_cliente
    where ztg.status_guia not in ('B')  
 ) 
 select idx from crt --where idx = 2
 group by idx

GO
*/
-- select count(*)  from  gstdb.dbo.trafico_guia 

-- ---------------------------------------------------------------
-- View for get current plazas and municipalitys
-- ---------------------------------------------------------------

IF OBJECT_ID ('gst_tms_view_tower_places', 'U') IS NOT NULL
    DROP table gst_tms_view_tower_places
GO

-- NOTE version 1.0.0
create or alter view gst_tms_view_tower_places
with encryption
as
  select 
      tfr.id_ruta , 
      tfp.id_plaza, 
      tfs.id_municipio,  
      tfp.desc_plaza, 
      tfs.descripcion_municipio
  from gstdb.dbo.trafico_ruta as tfr 
  left join  gstdb.dbo.trafico_plaza as tfp 
    on tfr.id_ruta = tfp.id_plaza 
  left join gstdb.dbo.sat_municipio as tfs 
    on tfp.id_municipio_sat = tfs.id_municipio
  where tfp.id_plaza is not null
GO

-- ---------------------------------------------------------------
-- View for get current trips from zam db
-- select * from sistemas.dbo.gst_tms_view_tower_trips where Label in ('Activos') order by id_asignacion desc 
-- select * from sistemas.dbo.gst_tms_view_tower_trips where Label in ('Programados') -- and PYR > 2025  --and id_asignacion = 136992 order by id_asignacion desc 
-- select * from sistemas.dbo.twrview_Programados
-- select * from sistemas.dbo.gst_tms_view_tower_trips where Label in ('PorLiberarse') order by id_asignacion desc 
/*
select
 a.id_asignacion,a.id_area
,b.id_asignacion,b.id_area,a.Carta_Porte,b.num_guia,b.guia,b.Label
from sistemas.dbo.twrview_Programados as a right join sistemas.dbo.gst_tms_view_tower_trips as b
on
 a.id_asignacion = b.id_asignacion
where b.Label = 'Programados'


select
 a.id_asignacion,a.id_area,a.despacho,b.tipo_viaje
,b.id_asignacion,b.id_area,b.num_guia,b.guia,b.despacho,b.tipo_viaje,b.Label,PYR,HAYR
from sistemas.dbo.twrview_Viajesactivos as a right join sistemas.dbo.gst_tms_view_tower_trips as b
on
 a.id_asignacion = b.id_asignacion
where b.Label = 'Activos'



select id_unidad,id_asignacion,id_posicion,* from sistemas.dbo.gst_tms_view_tower_trips where PorLiberarse = 1 --Activos = 1 
select  * from sistemas.dbo.gst_tms_view_tower_trips where Activos = 1
select  * from sistemas.dbo.gst_tms_view_tower_trips where Programados = 1 
select  * from sistemas.dbo.gst_tms_view_tower_trips where (PorLiberarse = 1 or Activos = 1) and Programados is null and Finalizados is null
select  * from sistemas.dbo.gst_tms_view_tower_trips where (PorLiberarse = 1 ) and Programados is null and Finalizados is null
-- in ('Activos')
-- in ('Programados') and PYR > 2025
 in ('PorLiberarse')

select * from sistemas.dbo.gst_tms_view_tower_trips where id_asignacion in (139659,139948,140392,140423,140426)


select * from sistemas.dbo.twrview_Viajesactivos where id_asignacion in (139659,139948,140392,140423,140426)
select * from sistemas.dbo.twrview_PorLiberarse

exec sp_desc gst_tms_view_tower_trips


with cte as (
select 
count(id_asignacion) as 'counts',
--viajeactual,
--case when fecha_real_fin_viaje is not null then 'not-null' else 'isnull' end as 'fecha_real_fin_viaje',
--case when num_guia is not null then 'not-null' else 'isnull' end as 'num_guia',

count(Programados) as 'Programados',
count(Activos) as 'Activos',
count(PorLiberarse) as 'PorLiberarse',
count(Finalizados) as 'Finalizados' 
from sistemas.dbo.gst_tms_view_tower_trips
--group by Programados,Activos,PorLiberarse,Finalizados
)
select 
	counts,
	--viajeactual,
	--fecha_real_fin_viaje,
	--num_guia,
	Programados,
	Activos,
	PorLiberarse,
	Finalizados 
from cte
group by 
--	viajeactual,
--	fecha_real_fin_viaje,
--	num_guia,
	Programados,
	Activos,
	PorLiberarse,
	Finalizados 


select * from sistemas.dbo.gst_tms_view_tower_trips
where Programados in (1)


select --34 
	t.id_asignacion as ast,
	p.id_asignacion as asp
from 
	sistemas.dbo.gst_tms_view_tower_trips as t
	left join 
	sistemas.dbo.twrview_Programados as p 
        on
	t.id_asignacion = p.id_asignacion 
	where 
	t.Programados = 1


select -- 65
	t.id_asignacion as ast,
	p.id_asignacion as asp
from 
	sistemas.dbo.gst_tms_view_tower_trips as t
	left join 
	sistemas.dbo.twrview_Viajesactivos as p 
        on
	t.id_asignacion = p.id_asignacion 
	where 
	t.Activos = 1

select -- 6
	t.id_asignacion as ast,
	p.id_asignacion as asp
from 
	sistemas.dbo.gst_tms_view_tower_trips as t
	left join 
	sistemas.dbo.twrview_PorLiberarse as p 
        on
	t.id_asignacion = p.id_asignacion 
	where 
	t.PorLiberarse = 1

exec sp_desc gst_tms_view_tower_trips
drop view gst_tms_view_tower_trips

SELECT LEFT(CONVERT(varchar, GetDate(),112),6)

*/

-- ---------------------------------------------------------------
-- Last Geo Position of units 
-- ---------------------------------------------------------------

IF OBJECT_ID ('gst_tms_view_tower_last_geo', 'U') IS NOT NULL
    DROP table gst_tms_view_tower_last_geo
GO

-- NOTE version 1.0.0
create or  alter view gst_tms_view_tower_last_geo
    with encryption
	as
select 
	mto.id_unidad,
	mto.tipo_unidad,
	mto.id_tipo_motor,
	mto.id_area,
	mto.estatus,
	mto.id_flota,
	mtu.descripcion,
	mtu.configuracion_autotransporte,
	mrk.nombre,
	position.id_posicion
from 
	gstdb.dbo.mtto_unidades mto
inner join 
	gstdb.dbo.mtto_tipos_unidades mtu 
   on 
	mto.tipo_unidad = mtu.id_tipo_unidad
inner join 
	gstdb.dbo.mtto_marcas_unidades mrk
   on
	mrk.id_marca_unidad = mto.id_marca_unidad
inner join (
       select 
		max(pmu.id_posicion) as id_posicion,pmu.id_unidad
	from
		gstdb.dbo.desp_posicion_unidad as pmu
	group by 
		pmu.id_unidad
 ) as position on
 mto.id_unidad = position.id_unidad
where 
	mtu.id_tipo_unidad = 1
  and 
	mto.estatus in ('A')
GO

-- select max(id_posicion) as 'id_posicion',id_unidad from gstdb.dbo.desp_posicion_unidad group by id_unidad

-- SELECT s.name as schema_name, t.name as table_name, c.* FROM sys.columns AS c
-- INNER JOIN sys.tables AS t ON t.object_id = c.object_id
-- INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
-- WHERE t.name = 'desp_posicion_unidad' AND s.name = 'dbo';
/*
select f_ini_status,
	case 
		when DATEDIFF(hour, f_ini_status, getdate()) <= 1199
		    then 'Programados'
		when DATEDIFF(hour, f_ini_status, getdate()) >= 1200 and DATEDIFF(hour, f_ini_status, getdate()) <= 7000
		    then 'Activos'
		when DATEDIFF(hour, f_ini_status, getdate()) >= 7001
		    then 'PorLiberarse'
		else 'Unknown'
	end  as 'Label',	
	DATEDIFF ( hour , f_ini_status , getdate()) as 'hh'
from gstdb.dbo.trafico_viaje where no_viaje = 21536 




DECLARE @f FLOAT = 13.5;
SELECT CONVERT(CHAR(5), DATEADD(MINUTE, 60*@f, 0), 108);
SELECT CONVERT(CHAR(5), DATEADD(MINUTE, 60*@f, 0), 108);
*/
-- ---------------------------------------------------------------

IF OBJECT_ID ('gst_tms_trips_tower_order', 'U') IS NOT NULL
    DROP table gst_tms_trips_tower_order
GO


IF OBJECT_ID ('gst_tms_view_tower_trips', 'U') IS NOT NULL
    DROP table gst_tms_view_tower_trips
GO

IF OBJECT_ID ('gst_tms_view_tower_trips', '') IS NOT NULL
    DROP view gst_tms_view_tower_trips
GO

-- NOTE version 1.0.0
create or  alter view gst_tms_view_tower_trips
    with encryption
	as
  select  
	row_number() over(order by asig.id_asignacion) as 'id', 
	asig.id_asignacion, -- implies viaje and asignacion

	case --viaje.viajeactual = 'S' and viaje.no_liquidacion is null
		when DATEDIFF(hour, viaje.f_ini_status, getdate()) <= 12
			/* then "558b2f" */
			then "text-success"
		when DATEDIFF(hour, viaje.f_ini_status, getdate()) > 12 and DATEDIFF(hour, viaje.f_ini_status, getdate()) <= 24
			/* then "ff9800" */
			then "text-warning"

		when DATEDIFF(hour, viaje.f_ini_status, getdate()) > 24
			/* then "a00123" */
			then "text-danger"
			/* then '{ style: "background-color: #A00123" }' */
		else null
	end  as 'Label',	

	case --viaje.viajeactual = 'S' and viaje.no_liquidacion is null
		when DATEDIFF(hour, viaje.f_ini_status, getdate()) < 12
			then 1 else null
	end  as 'Green',	
	case --viaje.viajeactual = 'S' and viaje.no_liquidacion is null
		when DATEDIFF(hour, viaje.f_ini_status, getdate()) > 11 and DATEDIFF(hour, viaje.f_ini_status, getdate()) < 24
			then 1 else null
	end  as 'Yellow',	
	case --viaje.viajeactual = 'S' and viaje.no_liquidacion is null
		when DATEDIFF(hour, viaje.f_ini_status, getdate()) > 23
			then 1 else null
	end  as 'Red',	

	case
		when
			asig.status_asignacion = 1 and asig.f_despachado is null and guia.num_guia is not null
		then 1 else null
	end as 'Programado',

	case
		when
			viaje.viajeactual = 'S' and viaje.no_liquidacion is null
		then 1 else null
	end as 'Activo',

	case
		when
			viaje.id_statusviaje in  (9,10,11) and viaje.fecha_fin_viaje is null
		then 1 else null
	end as 'Siguiente',

	case
		when
			viaje.viajeactual = 'N' and viaje.fecha_real_fin_viaje is not null and guia.num_guia is null
		then 1 else null
	end as 'Finalizado',

	-- Status Tracto : Column 2
	mtt.id_status as 'id_truck_status',
	case
	   when mtt.id_status in (1,2,3,16,17) then 'En Viaje'
	   when mtt.id_status in (4,18) then 'En Taller'
	   when mtt.id_status in (7,10,11,12,13,14,15) then 'No Operable'
	   when mtt.id_status in (6) then 'Sin Viaje'
	   when mtt.id_status in (8) then 'Siniestrado'
	   else 'Sin Estado'
	end collate Modern_Spanish_CI_AS as 'truck_status', -- [2:activos] Estado

	stv.nombre_status_viaje as 'trip_status', --estatus_viaje

	CAST(
	   DATEDIFF(MINUTE, viaje.f_ini_status, GETDATE()) / 60 AS VARCHAR) 
	   + ':' + 
	   RIGHT('00' + CAST(DATEDIFF(MINUTE, viaje.f_ini_status, GETDATE()) % 60 AS VARCHAR), 2)
	 collate Modern_Spanish_CI_AS 'tiempo_transcurrido', -- Cambio de orden


	asig.status_asignacion, -- 1:programado
	asig.fecha_ingreso,
	asig.f_despachado, -- [8:activos] Despacho :: NULL:programado 
	viaje.f_despachado as 'despacho', -- [8:activos] Despacho :: NULL:programado 
	viaje.no_viaje, -- [3:activos] No Viaje
	viaje.id_unidad, -- [4:activos] [2:programados] Tracto
	viaje.id_area,
	viaje.id_statusviaje, -- in [9,10,11]:PorLiberarse
	viaje.fecha_fin_viaje, -- is null : PorLiberarse
	viaje.fecha_real_fin_viaje, -- is-not-null:historico
	viaje.fecha_real_viaje, -- is-not-null : historico
	viaje.no_liquidacion, -- null:actual
	viaje.viajeactual,  -- S:actual;N:historico
	viaje.kms_viaje as 'kms', -- [14:activos] Kms 

	suc.Company as 'area', -- [1:activos] [1:programados]Udn
	opr.tipo_operacion as 'operacion', -- [6:activos] [5:programados] Operacion
	opr.id_tipo_operacion as 'id_tipo_operacion',

	-- move this for join with a better scheme
	(
		select
			max(ppl.nombre)
		from
		gstdb.dbo.personal_personal as ppl
		where
			viaje.id_personal = ppl.id_personal
	) as 'Operator', -- [5:activos] [4:programados] Operador
--
	guia.id_cliente,
	guia.nombre as 'client', -- [7:activos] [5:programados] Cliente
	guia.num_guia as 'num_guia',

	case 
          when dsa.consecutives is NULL 
       	    then 'Vacio'
       	    else 'Cargado' 
     	  end collate Modern_Spanish_CI_AS as 'tipo_viaje', -- [9:activos] Tipo de Viaje [Cargado/Vacio]

	guia.f_arribo_prog, 
	guia.f_carga_inicio as 'cita_carga',
	
	origin.desc_plaza as 'Origen', -- [10:activos] Origen
	origin.descripcion_municipio as 'MunicipioOrigen', -- [11:activos] MunicipioOrigen
	destiny.desc_plaza 'Destino', -- [12:activos] Destino
	destiny.descripcion_municipio as 'MunicipioDestino', -- [13:activos] MunicipioDestino
	null as 'inicio_status', --[15:activos] Inicio Status
	--stv.nombre_status_viaje as 'status_viaje',  -- [16:activos] Status

	geo.id_posicion,
	position.posicion,
	position.poslat,
	position.poslon,
	position.timezone,

	null as 'fatiga' , -- [17:activos] Fatiga
	null as 'incidencias', -- [18:activos] Uncidencias
	null as 'ver',  -- [19:activos] ver
	 
	convert(int,year(asig.fecha_ingreso)) as 'PYR', 
	convert(int,year(viaje.fecha_real_viaje)) as 'HAYR'
  
  from
	gstdb.dbo.desp_asignacion as asig -- ZAM
  left join 
	gstdb.dbo.trafico_viaje as viaje -- ZAM
	on
		asig.no_viaje = viaje.no_viaje and asig.id_area = viaje.id_area
  left join 
	gstdb.dbo.mtto_unidades as mtt  -- ZAM
	on
		viaje.id_unidad = mtt.id_unidad
  left join 
	gstdb.dbo.desp_statusviaje as stv   -- ZAM
	on
		viaje.id_statusviaje = stv.id_statusviaje
  left join 
	gstdb.dbo.trafico_ruta as ruta
	on 
          viaje.id_ruta = ruta.id_ruta 


	OUTER APPLY (
	-- Guias que estan relacionadas con pedido y despacho-asignacion [solicitud & despacho ??]
        -- las guia que no tienen un pedido asociado son remolques o vacios?
        -- o los que no tienen una asignacion son vacios ; mas bien los que no tienen guia son vacios
        -- asignacion --> viaje --> guia 
	    SELECT TOP (1)
		   g.num_guia,
		   ped.f_arribo_prog,
		   g.id_cliente,
		   clt.nombre,
		   g.id_tipo_operacion,
		   ped.f_carga_inicio
	    FROM gstdb.dbo.desp_asignaciondet dad WITH (NOLOCK)
	    INNER JOIN gstdb.dbo.desp_pedido ped WITH (NOLOCK)
		ON dad.id_area_pedido = ped.id_area
	       AND dad.id_pedidopk = ped.id_pedidopk
	    INNER JOIN gstdb.dbo.trafico_guia g WITH (NOLOCK)
		ON g.id_area = ped.id_area
	       AND g.no_guia = ped.no_guia
	       AND g.status_guia <> 'B'

	    -- Add client name 
	    inner join gstdb.dbo.trafico_cliente as clt
	       on g.id_cliente = clt.id_cliente

	    WHERE dad.id_area_pedido = asig.id_area
	      AND dad.id_asignacion = asig.id_asignacion
	    ORDER BY g.no_guia DESC
	) as guia

  left join 
	sistemas.dbo.gst_tms_view_tower_places	as  origin
	on 
	  ruta.origen_ruta = origin.id_plaza
  left join 
	sistemas.dbo.gst_tms_view_tower_places	as  destiny
	on 
	  ruta.destino_ruta = destiny.id_plaza
-- This block join can be remove 
	
  left join (
	select 
	 dspa.id_asignacion 
	,count(dspa.consecutivo) as 'consecutives' 
	,dspa.id_area_pedido
 	 --pdd.f_arribo_prog,
	,max(pdd.f_carga_inicio) as 'f_carga_inicio'
	from gstdb.dbo.desp_asignaciondet as dspa

	inner join 
		gstdb.dbo.desp_pedido as pdd
	    on
		dspa.id_area_pedido = pdd.id_area and dspa.id_pedidopk = pdd.id_pedidopk
	group by 
	 dspa.id_asignacion
	,dspa.id_area_pedido
	) as dsa on asig.id_asignacion = dsa.id_asignacion and viaje.id_area = dsa.id_area_pedido

  left join 
       	sistemas.dbo.gst_tms_zam_operations as opr  -- GST
	on
		viaje.id_tipo_operacion = opr.id_tipo_operacion
  left join
       	sistemas.dbo.gst_tms_zam_areas as suc  -- GST
	on
		asig.id_area = suc.id_area
 left join
	gst_tms_view_tower_last_geo as geo
  on 
	viaje.id_unidad = geo.id_unidad
left join
	gstdb.dbo.desp_posicion_unidad as position 
  on
	position.id_posicion = geo.id_posicion

  where 
	asig.status_asignacion = 1 and asig.f_despachado is null and guia.num_guia is not null
	or
	viaje.viajeactual = 'S' and viaje.no_liquidacion is null
	--or
	--viaje.id_statusviaje in  (9,10,11) and viaje.fecha_fin_viaje is null
	--		viaje.viajeactual = 'N' and viaje.fecha_real_fin_viaje is not null and guia.num_guia is null
GO



IF OBJECT_ID ('gst_tms_summary_tower_trips', 'U') IS NOT NULL
    DROP table gst_tms_summary_tower_trips
GO

IF OBJECT_ID ('gst_tms_summary_tower_trips', '') IS NOT NULL
    DROP view gst_tms_summary_tower_trips
GO

--exec sp_desc gst_tms_summary_tower_trips

-- NOTE version 1.0.0
create or  alter view gst_tms_summary_tower_trips
    with encryption
	as
with cte as (
select 
	case Activo when 1 then sum(Green) else 0  end as 'Green',
	case Activo when 1 then sum(Yellow) else 0 end as 'Yellow',
	case Activo when 1 then sum(Red) else 0 end as 'Red',
	sum(Programado) as 'Programados',
	sum(Activo) as 'Activos',
	sum(Siguiente) as 'PorLiberarse'

from sistemas.dbo.gst_tms_view_tower_trips group by Activo
)
select 
1 as 'id', 
sum(Programados) as 'Programados',sum(Activos) as 'Activos',sum(PorLiberarse) as 'PorLiberarse',sum(Green) as 'Green',sum(Yellow) as 'Yellow',sum(Red) as 'Red'
from 
	cte
GO



IF OBJECT_ID ('tms_view_tower_shipment', 'U') IS NOT NULL
    DROP table tms_view_tower_shipment
GO

IF OBJECT_ID ('tms_view_tower_shipment', '') IS NOT NULL
    DROP view tms_view_tower_shipment
GO

--exec sp_desc tms_view_tower_shipment

-- NOTE version 1.0.0
create or  alter view tms_view_tower_shipment
    with encryption
	as
    select  
	-- viajes zam 
	zam.id,                     
        zam.id_asignacion,          
        zam.Label,                  
        zam.Green,                  
        zam.Yellow,                 
        zam.Red,                    
        zam.Programado,             
        zam.Activo,                 
        zam.Siguiente,              
        zam.Finalizado,             
        zam.id_truck_status,        
        zam.truck_status,           
        zam.trip_status,            
        zam.tiempo_transcurrido,    
        zam.status_asignacion,      
        zam.fecha_ingreso,          
        zam.f_despachado,           
        zam.despacho,               
        zam.no_viaje,               
        zam.id_unidad,              
        zam.id_area,                
        zam.id_statusviaje,         
        zam.fecha_fin_viaje,        
        zam.fecha_real_fin_viaje,   
        zam.fecha_real_viaje,       
        zam.no_liquidacion,         
        zam.viajeactual,            
        zam.kms,                    
        zam.area,                   
        zam.operacion,              
        zam.id_tipo_operacion,      
        zam.Operator,               
        zam.id_cliente,             
        zam.client,                 
        zam.num_guia,               
        zam.tipo_viaje,             
        zam.f_arribo_prog,          
        zam.cita_carga,             
        zam.Origen,                 
        zam.MunicipioOrigen,        
        zam.Destino,                
        zam.MunicipioDestino,       
        zam.inicio_status,          
        /* zam.status_viaje, */                 
        zam.id_posicion,            
        zam.posicion,               
        zam.poslat,                 
        zam.poslon,                 
        zam.timezone,               
        zam.fatiga,                 
        zam.incidencias,            
        zam.ver,                    
        zam.PYR,                    
        zam.HAYR,
	--shipment
	ttl.id as 'fms_id',               
	ttl.token,            
	ttl.tower_catalog_id, 
	ttl.fms_order,        
	ttl.fms_order_uuid,   
	ttl.fms_order_id,     
	ttl.codename,         
	ttl.number,           
	ttl.serial,           
	ttl.code,             
	ttl.order_code,       
	ttl.level,            
	/* 5 as 'tms_origin', */
	ttl.description,      
	ttl.created,          
	ttl.modified,         
	ttl.status           
    from
       	gst_tms_view_tower_trips as zam
    left join 
	tms_tower_layers as ttl on ttl.fms_order_id = zam.id_asignacion
	and 
       	ttl.status = 1