GO
/****** Object:  StoredProcedure [dbo].[paWcfSeLisTrn_TraEspReservasRelDet]    Script Date: 12/01/2018 10:18:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	John Jairo Roa
-- Create date(dd/MM/yyyy): 02/03/2017
-- Description:	Imprimir reservas para informes
-- =============================================
ALTER PROCEDURE [dbo].[paWcfSeLisTrn_TraEspReservasRelDet]
AS
--Encabezado
SELECT '' AS TipDoc, CAST('' AS INT) AS Reserva,'' IdCia,'' AS Compania, '' AS TipDocCnt, CAST('' AS INT) AS Contrato, '' AS IdCiaCnt, CAST('' AS Money) AS VrPresupuesto,
CAST('' AS Money) AS VrSaldoPresup, '' AS IdCliente,'' AS RazonSocial,CAST('' AS datetime) AS Fecha, CAST('' AS datetime) AS FechaInicio,CAST('' AS datetime) AS FechaFin,
'' AS EstadoRes, '' AS IdAgencia,'' AS Agencia, '' AS IdMneda, '' AS Mneda, '' AS CdCarga,'' AS Modalidad, '' AS TipoLiquid, '' AS TipFac, CAST('' AS INT) AS  Factura,
'' AS IdCiaFac,CAST('' AS datetime) AS FechaFac, '' AS TipOdp, CAST('' AS INT) AS  NumOdp, '' AS IdCiaOdp,CAST('' AS datetime) AS FechaOdp,'' AS TipFuec,
CAST('' AS INT) AS NumFuec, '' AS IdCiaFuec, CAST('' AS Money) AS  TotalCobro,CAST('' AS Money) AS  TotalPagos, '' AS OrigenAdd,CAST('' AS bit) AS Anulado,
CAST('' AS datetime) AS FecDev, '' AS IdEstado,'' AS Estado, CAST('' AS datetime) TimeSys, CAST('' AS datetime) AS FecUpdate, '' AS IdCiaCrea,
'' AS IdUsuario,'' AS Usuario,
--Anexos
'' AS NomCliente, '' AS TelCliente, '' AS emlCliente,'' AS DireccionCli,
'' AS Observacion,''  AS NombArchivo,'' AS DescripCarga, '' AS Clausulas, '' AS Responsable,
--Detalles
CAST('' AS INT) AS Item, CAST('' AS INT) AS ItemCnt, '' AS IdVehiculo,'' AS CdTipVeh, '' AS TipoVehiculo,'' AS TipoAfil, '' AS IdPropietario,'' AS NomPropietario,
'' AS IdPoseedor,'' AS NomPoseedor, '' AS IdConductor,'' AS NomConductor, '' AS IdRuta, '' AS Ruta, '' AS CdTipRuta, CAST('' AS INT) AS NumPasajeros, '' AS PesoNeto, 
CAST('' AS Money) AS VrCobro, CAST('' AS Money) AS VrPagos, '' AS Referencia1, '' AS Referencia2, '' AS Referencia3, '' AS TipOdp, CAST('' AS INT) AS  NumOdp, 
'' AS IdCiaOdp,CAST('' AS datetime) AS FechaOdp, CAST('' AS BIT) AS EsRutaCliente, '' AS CdCiuOrigen, '' as CiuOrigenD, '' AS DeptOrigen, '' AS DirOrigen, 
CAST('' AS datetime) AS FecOrigen, '' AS CdCiuDestino, '' AS  CiuDestinoD,'' AS DeptDestino, '' AS DirDestino,CAST('' AS datetime) AS FecDestino, CAST('' AS INT) AS  DiasDisp,
CAST('' AS Money) AS VrDia, '' AS Parada, '' AS CdTipServicio, '' AS EstadoItem, CAST('' AS MONEY) AS VrMonitor, CAST('' AS INT) AS CantMonitor, CAST('' AS MONEY) AS VrRecorrido,
CAST('' AS INT) AS NumDias, '' AS TipoTarifa, '' AS IdColegio, '' AS NomColegio, '' AS TFac, CAST('' AS INT) AS Fact, '' AS CiaFact, CAST('' AS datetime) AS FecFac,
'' AS TipCum, CAST('' AS INT) AS Cumplido, '' AS IdCiaCum, CAST('' AS datetime) AS FechaCum, '' AS Codigo,
--Datos del cliente
'' AS TipoId, '' AS Dv, '' AS Codigo, '' AS NomCial, '' AS Direccion, '' AS Telefono, '' AS e_mail, '' AS NitRepLeg, '' AS NomRepLeg, '' AS IdSzona,
'' AS Subzona, '' AS IdZona, '' AS Zona, '' AS IdGrupo, '' AS GrupoClie,
--Detalles programación
'' AS TipDocPgr, CAST('' AS INT) AS Programa, '' AS IdCiaPgr, CAST('' AS INT) AS ItemPgr, CAST('' AS datetime) AS FecInicPgr,  CAST('' AS datetime) AS FecFinPgr,
--Datos de Ruta
'' AS Distkmt,
--Fecha Día para reporte estimado horas
CAST('' AS datetime) FechaDia
GO

/****** Object:  StoredProcedure [dbo].[paWcfSeInsTrn_TraEspPrograma]    Script Date: 12/01/2018 10:27:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	Joe Alexander Núñez Yaguna
-- Create date(dd/MM/yyy): 21/10/2016
-- Description:	Insertar CRUD Programacion de vehiculos.
-- =============================================
ALTER PROCEDURE [dbo].[paWcfSeInsTrn_TraEspPrograma]
	@Trn_TraEspPrograma dataTypeTrn_TraEspPrograma READONLY,--Tipo de dato definido por el usuario que representa la tabla Trn_TraEspPrograma
	@Trn_TraEspProgramaDet dataTypeTrn_TraEspProgramaDet READONLY,--Tipo de dato definido por el usuario que representa la tabla Trn_TraEspProgramaDet
	@Trn_TraEspProgramaPers dataTypeTrn_TraEspProgramaPers READONLY,--Tipo de dato definido por el usuario que representa la tabla Trn_TraEspProgramaPers
	@operacion varchar(1)
AS
DECLARE @Documento INT --Esta variable guarda el número de consecutivo del Reserva/Cotización
DECLARE @TipDoc VARCHAR(3) --Esta variable guarda el tipo de documento del Reserva/Cotización
DECLARE @IdCia VARCHAR(4) --Esta variable guarda la compañia del Reserva/Cotización
declare @IdEstado varchar(4)
declare @item int
	BEGIN TRAN
		BEGIN TRY

			--Seleccionar Variables
			IF(@operacion = 'i')
			begin
				SELECT TOP 1 @TipDoc = TipDoc, @IdCia = IdCia FROM @Trn_TraEspPrograma
				SELECT @Documento = Numero + 1  FROM TiposDocCons where IdDoc = @TipDoc AND IdCia = @IdCia

				IF(@Documento <> '')
					UPDATE TiposDocCons SET Numero = @Documento WHERE IdDoc = @TipDoc and IdCia = @IdCia
				ELSE
				BEGIN
					SELECT @Documento = 1
					INSERT INTO TiposDocCons VALUES (@TipDoc,@IdCia,0,0,0,0,NULL,'',@Documento,0,0,'','',1,1,1,0,1,CURRENT_TIMESTAMP,null)
				END
			end

			--Seleccionar Variables
			--IF(@operacion = 'u')
			--begin
			--end

			--UPDATE SI-EXISTE RECORDS
			IF(@operacion = 'u')
			BEGIN

			   IF(EXISTS(SELECT * FROM @Trn_TraEspPrograma))
			   begin
			   UPDATE Trn_TraEspPrograma
			   SET 
			   TipDoc = t2.TipDoc,Programa = t2.Programa,IdCia = t2.IdCia,
			   Fecha = t2.Fecha,OrigenAdd = t2.OrigenAdd,Anulado = t2.Anulado,
			   FecDev = t2.FecDev,IdEstado = t2.IdEstado,TimeSys = t2.TimeSys,
			   FecUpdate = t2.FecUpdate,IdCiaCrea = t2.IdCiaCrea,IdUsuario = t2.IdUsuario
		       FROM Trn_TraEspPrograma t1
			   JOIN @Trn_TraEspPrograma t2 ON t1.TipDoc = t2.TipDoc and t1.Programa = t2.Programa and t1.IdCia = t2.IdCia
			   end

			   IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaDet))
			   begin
			   SELECT TOP 1 @TipDoc = TipDoc, @IdCia = IdCia, @Documento = Programa FROM @Trn_TraEspProgramaDet
			   select @item =  isnull(MAX(Item),0) from Trn_TraEspProgramaDet where TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia

			   --se actuallizan los que vienen en el tipo de dato y coincidan con los que estan el la tabla
			   UPDATE Trn_TraEspProgramaDet
			   SET 
			   TipDoc = t2.TipDoc,Programa = t2.Programa,IdCia = t2.IdCia,Item = t2.Item,
			   FechaInicio = t2.FechaInicio,FechaFin = t2.FechaFin,TipDocRes = t2.TipDocRes,
			   Reserva = t2.Reserva,IdCiaRes = t2.IdCiaRes,ItemRes = t2.ItemRes,IdOrigen = t2.IdOrigen,
			   IdDestino = t2.IdDestino,IdVehiculo = t2.IdVehiculo,
			   CdTipVeh = t2.CdTipVeh,IdConductor = t2.IdConductor,NumPasajeros = t2.NumPasajeros,TipDocFuec = t2.TipDocFuec,
			   NumFuec = t2.NumFuec,IdCiaFuec = t2.IdCiaFuec,TipDocOdp = t2.TipDocOdp,NumOdp = t2.NumOdp,
			   IdCiaOdp = t2.IdCiaOdp,FechaOdp=t2.FechaOdp,IdNovedad = t2.IdNovedad,Novedad = t2.Novedad,Cumplido = t2.Cumplido,FecIniCump = t2.FecIniCump, FecFinCump = t2.FecFinCump,
			   VrCobro = t2.VrCobro, VrPagos = t2.VrPagos,DiasDisp= t2.DiasDisp,Parada= t2.Parada,CdTipServicio= t2.CdTipServicio,
			   DirOrigen= t2.DirOrigen,DirDestino= t2.DirDestino,IdRuta= t2.IdRuta,CdTipRuta= t2.CdTipRuta,PesoNeto= t2.PesoNeto,EstadoItem=t2.EstadoItem,
			   VrMonitor=t2.VrMonitor,CantMonitor=t2.CantMonitor,VrRecorrido= t2.VrRecorrido, Observacion= t2.Observacion
		       FROM Trn_TraEspProgramaDet t1
			   JOIN @Trn_TraEspProgramaDet t2 ON t1.TipDoc = t2.TipDoc and t1.Programa = t2.Programa and t1.IdCia = t2.IdCia and t1.Item = t2.Item
			   WHERE t1.TipDoc = t2.TipDoc and t1.Programa = t2.Programa and t1.IdCia = t2.IdCia and t1.Item = t2.Item
			   --Inserta los que no existen
			   INSERT INTO Trn_TraEspProgramaDet(
			   TipDoc,Programa,IdCia,Item,FechaInicio,FechaFin,TipDocRes,Reserva,IdCiaRes,ItemRes,
			   IdOrigen,IdDestino,IdVehiculo,CdTipVeh,IdConductor,NumPasajeros,TipDocFuec,NumFuec,
			   IdCiaFuec,TipDocOdp,NumOdp,IdCiaOdp,FechaOdp,IdNovedad,Novedad,Cumplido,FecIniCump,FecFinCump,VrCobro,VrPagos,DiasDisp,Parada,
			   CdTipServicio,DirOrigen,DirDestino,IdRuta,CdTipRuta,PesoNeto,EstadoItem,VrMonitor,CantMonitor,VrRecorrido,Observacion
	           ) 
			   SELECT 
			   @TipDoc,@Documento,@IdCia,(@item + ROW_NUMBER() OVER(ORDER BY Item ASC)),FechaInicio,FechaFin,TipDocRes,Reserva,IdCiaRes,ItemRes,
			   IdOrigen,IdDestino,IdVehiculo,CdTipVeh,IdConductor,NumPasajeros,TipDocFuec,NumFuec,
			   IdCiaFuec,TipDocOdp,NumOdp,IdCiaOdp,FechaOdp,IdNovedad,Novedad,Cumplido,FecIniCump,FecFinCump,VrCobro,VrPagos,DiasDisp,Parada,
			   CdTipServicio,DirOrigen,DirDestino,IdRuta,CdTipRuta,PesoNeto,EstadoItem,VrMonitor,CantMonitor,VrRecorrido,Observacion
			   FROM @Trn_TraEspProgramaDet
			   WHERE (TipDoc + Convert(varchar,Programa)+IdCia+Convert(varchar,Item)) NOT IN(SELECT(TipDoc + Convert(varchar,Programa)+IdCia+Convert(varchar,Item)) FROM Trn_TraEspProgramaDet)
			    
			   --Actualizar estado de vehiculo CUANDO NO TIENE NOVEDAD
			   UPDATE Vehiculos
			   SET IdEstado = '0002'
			   FROM Vehiculos v
			   JOIN @Trn_TraEspProgramaDet pd ON v.IdVehiculo = pd.IdVehiculo and pd.IdNovedad = '0' and pd.Cumplido = 0
			   WHERE v.IdVehiculo = pd.IdVehiculo 


			   --Actualizar estado de vehiculo CUANDO TENGA NOVEDAD DE TIPO VEHICULO
			   ----DECLARE @IDNOV VARCHAR(4)
			   ----SELECT @IDNOV = IdNovedad FROM @Trn_TraEspProgramaDet
			   ----IF(UPPER((select Tipo from TiposNovPgr where IdNovedad = @IDNOV)) = 'VEHICULO')
			   ----BEGIN
				  ---- UPDATE Vehiculos
				  ---- SET IdEstado = (select IdEstado from TiposNovPgr where IdNovedad = pd.IdNovedad and Tipo = 'VEHICULO')
				  ---- FROM Vehiculos v
				  ---- JOIN @Trn_TraEspProgramaDet pd ON v.IdVehiculo = pd.IdVehiculo and pd.IdNovedad != '0' and pd.Cumplido = 0
				  ---- WHERE v.IdVehiculo = pd.IdVehiculo 
			   ----END

			    declare @acveh varchar(11)
			    declare @acidnov varchar(4)
				--declare @acItem int
				
				DECLARE acvehic CURSOR
				FOR SELECT IdVehiculo,IdNovedad FROM @Trn_TraEspProgramaDet where IdNovedad != '0' and Cumplido = 0
				OPEN acvehic
				FETCH NEXT FROM acvehic INTO @acveh,@acidnov
				WHILE @@FETCH_STATUS = 0
				BEGIN
				   UPDATE Vehiculos
				   SET IdEstado = tn.IdEstado
				   FROM Vehiculos v, TiposNovPgr tn
				   WHERE v.IdVehiculo = @acveh and tn.IdNovedad = @acidnov and tn.Tipo = 'VEHICULO'
				FETCH NEXT FROM acvehic INTO @acveh,@acidnov
				END
				CLOSE acvehic
				DEALLOCATE acvehic

			   --ACTUALIZAR RESERVAS 
			   ----UPDATE Trn_TraEspResDetalle
			   ----SET EstadoItem = 'PROGRAMADA'
			   ----FROM Trn_TraEspResDetalle p
			   ----JOIN @Trn_TraEspProgramaDet d ON p.TipDoc = d.TipDocRes  and p.Reserva = d.Reserva and p.IdCia = d.IdCiaRes and p.Item = d.ItemRes
			   ----WHERE d.TipDoc = @TipDoc and d.Programa = @Documento and d.IdCia = @IdCia and d.IdNovedad = '0' and d.Cumplido = 0

			   	declare @acReserva int
				declare @acCiaRes varchar(2)
				declare @acItem int
				
				DECLARE acres CURSOR
				FOR SELECT Reserva,IdCiaRes,ItemRes FROM @Trn_TraEspProgramaDet where TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia and IdNovedad = '0' and Cumplido = 0
				OPEN acres
				FETCH NEXT FROM acres INTO @acReserva,@acCiaRes,@acItem
				WHILE @@FETCH_STATUS = 0
				BEGIN
					UPDATE Trn_TraEspResDetalle
					SET EstadoItem = 'PROGRAMADA'
					FROM Trn_TraEspResDetalle p
					WHERE  p.TipDoc = 'RES' and p.Reserva = @acReserva and p.IdCia = @acCiaRes and p.Item = @acItem
					-- Fecha:20/11/2017 Se desactiva esta condición porque al entrar 2 usuarios a hacer la misma operación queda desactualizado 
					-- el número de consecutivo que se obtiene en el software y se actualizaría reservas del primera operación, en este caso, quedaría mal para la segunada transacción.
					--WHERE d.TipDoc = @TipDoc and d.Programa = @Documento and d.IdCia = @IdCia
				FETCH NEXT FROM acres INTO @acReserva,@acCiaRes,@acItem
				END
				CLOSE acres
				DEALLOCATE acres

			   --ACTUALIZAR Vehiculos en ProgramaPers 
			   UPDATE Trn_TraEspProgramaPers
			   SET IdVehiculo = d.IdVehiculo
			   FROM Trn_TraEspProgramaPers p
			   JOIN @Trn_TraEspProgramaDet d ON p.TipDoc = d.TipDoc  and p.Programa = d.Programa and p.IdCia = d.IdCia and p.Item = d.Item
			   WHERE p.TipDoc = d.TipDoc and p.Programa = d.Programa and p.IdCia = d.IdCia and p.Item= d.Item and d.IdNovedad = '0' and d.Cumplido = 0

			   end

			   IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaPers))
			   begin
			   SELECT TOP 1 @TipDoc = TipDoc, @IdCia = IdCia, @Documento = Programa, @item = Item FROM @Trn_TraEspProgramaPers
			
			  --Eliminar los registros
			   delete from Trn_TraEspProgramaPers where TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia and Item = @item 
			   --Inserta los que no existen
			   INSERT INTO Trn_TraEspProgramaPers(
			   TipDoc,Programa,IdCia,Item,IdVehiculo,Nit,IdNovedad,Novedad,Cumplido,IdRuta
	           ) 
			   SELECT 
			   @TipDoc,@Documento,@IdCia,@item,IdVehiculo,Nit,IdNovedad,Novedad,Cumplido,IdRuta
			   FROM @Trn_TraEspProgramaPers
			   			   
			   end

			END

		    --INSERT NO-EXISTE RECORDS
			IF(@operacion = 'i')
			 BEGIN

				IF(EXISTS(SELECT * FROM @Trn_TraEspPrograma))
				begin
				INSERT INTO Trn_TraEspPrograma(
				TipDoc,Programa,IdCia,Fecha,OrigenAdd,Anulado,FecDev,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
				)
				SELECT 
				@TipDoc,@Documento,@IdCia,Fecha,OrigenAdd,Anulado,FecDev,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
				FROM @Trn_TraEspPrograma
				end

				IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaDet))
				begin
				INSERT INTO Trn_TraEspProgramaDet(
			    TipDoc,Programa,IdCia,Item,FechaInicio,FechaFin,TipDocRes,Reserva,IdCiaRes,ItemRes,
			    IdOrigen,IdDestino,IdVehiculo,CdTipVeh,IdConductor,NumPasajeros,TipDocFuec,NumFuec,
			    IdCiaFuec,TipDocOdp,NumOdp,IdCiaOdp,FechaOdp,IdNovedad,Novedad,Cumplido,FecIniCump,FecFinCump,VrCobro,VrPagos,DiasDisp,Parada,
				CdTipServicio,DirOrigen,DirDestino,IdRuta,CdTipRuta,PesoNeto,EstadoItem,VrMonitor,CantMonitor,VrRecorrido,Observacion
				) 
			    SELECT 
			    @TipDoc,@Documento,@IdCia,Item,FechaInicio,FechaFin,TipDocRes,Reserva,IdCiaRes,ItemRes,
			    IdOrigen,IdDestino,IdVehiculo,CdTipVeh,IdConductor,NumPasajeros,TipDocFuec,NumFuec,
			    IdCiaFuec,TipDocOdp,NumOdp,IdCiaOdp,FechaOdp,IdNovedad,Novedad,Cumplido,FecIniCump,FecFinCump,VrCobro,VrPagos,DiasDisp,Parada,
				CdTipServicio,DirOrigen,DirDestino,IdRuta,CdTipRuta,PesoNeto,EstadoItem,VrMonitor,CantMonitor,VrRecorrido,Observacion
			    FROM @Trn_TraEspProgramaDet
				end

				IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaPers))
				begin
				INSERT INTO Trn_TraEspProgramaPers(
			    TipDoc,Programa,IdCia,Item,IdVehiculo,Nit,IdNovedad,Novedad,Cumplido,IdRuta
				) 
			    SELECT 
			    @TipDoc,@Documento,@IdCia,Item,IdVehiculo,Nit,IdNovedad,Novedad,Cumplido,IdRuta
			    FROM @Trn_TraEspProgramaPers
				end

				--ACTUALIZAR VEHICULOS 
				UPDATE Vehiculos
				SET IdEstado = '0002'
				FROM Vehiculos v
				JOIN @Trn_TraEspProgramaDet pd ON v.IdVehiculo = pd.IdVehiculo 
				WHERE v.IdVehiculo = pd.IdVehiculo --AND pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia

				--ACTUALIZAR RESERVAS 
				declare @actReserva int
				declare @actCiaRes varchar(2)
				declare @actItem int
				
				DECLARE actres CURSOR
				FOR SELECT Reserva,IdCiaRes,ItemRes FROM @Trn_TraEspProgramaDet
				OPEN actres
				FETCH NEXT FROM actres INTO @actReserva,@actCiaRes,@actItem
				WHILE @@FETCH_STATUS = 0
				BEGIN
					UPDATE Trn_TraEspResDetalle
					SET EstadoItem = 'PROGRAMADA'
					FROM Trn_TraEspResDetalle p
					WHERE  p.TipDoc = 'RES' and p.Reserva = @actReserva and p.IdCia = @actCiaRes and p.Item = @actItem
					-- Fecha:20/11/2017 Se desactiva esta condición porque al entrar 2 usuarios a hacer la misma operación queda desactualizado 
					-- el número de consecutivo que se obtiene en el software y se actualizaría reservas del primera operación, en este caso, quedaría mal para la segunada transacción.
					--WHERE d.TipDoc = @TipDoc and d.Programa = @Documento and d.IdCia = @IdCia
				FETCH NEXT FROM actres INTO @actReserva,@actCiaRes,@actItem
				END
				CLOSE actres
				DEALLOCATE actres

			 END

			--DELETE RECORDS
			IF(@operacion = 'd')
			BEGIN
			 declare @Programa int
			 select @TipDoc = TipDoc, @IdCia = IdCia,  @Programa = Programa from @Trn_TraEspPrograma
			 DELETE FROM Trn_TraEspProgramaPers WHERE TipDoc = @TipDoc and Programa = @Programa and IdCia = @IdCia
			 DELETE FROM Trn_TraEspProgramaDet WHERE TipDoc = @TipDoc and Programa = @Programa and IdCia = @IdCia
			 DELETE FROM Trn_TraEspPrograma WHERE TipDoc = @TipDoc and Programa = @Programa and IdCia = @IdCia
			END

			--ANULAR RECORDS
			IF(@operacion = 'a')
			BEGIN
			  --select @IdEstado = IdEstado from EstadoDoc where Estado = 'ANULADO'
			  select @TipDoc = TipDoc, @IdCia = IdCia,  @Documento = Programa from @Trn_TraEspPrograma
			  UPDATE Trn_TraEspPrograma SET IdEstado = '9999',Anulado = '1' WHERE TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia

			  --ACTUALIZAR VEHICULOS 
			  UPDATE Vehiculos
			  SET IdEstado = '0001'
			  FROM Vehiculos v
			  JOIN Trn_TraEspProgramaDet pd ON v.IdVehiculo = pd.IdVehiculo and pd.IdNovedad = '0'
			  WHERE v.IdVehiculo = pd.IdVehiculo AND pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia

			  -- --ACTUALIZAR Reservas 
			  --UPDATE Trn_TraEspResDetalle
			  --SET 
			  --FROM Trn_TraEspResDetalle rd
			  --JOIN Trn_TraEspProgramaDet pd ON pd.TipDocRes = rd.TipDoc and rd.Reserva = pd.Reserva and pd.IdCiaRes = rd.IdCia
			  ----WHERE v.IdVehiculo = pd.IdVehiculo AND pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia
			END

			--CERRAR PROGRAMACIÓN
			IF(@operacion = 'c')
			BEGIN
			  declare @date date
			  select @date = Convert(date, getdate())
			  select @TipDoc = TipDoc, @IdCia = IdCia,  @Documento = Programa from @Trn_TraEspPrograma

			  --cerrar las programaciones 
			  UPDATE Trn_TraEspPrograma SET IdEstado = '9998' WHERE TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia
			  UPDATE Trn_TraEspProgramaDet SET EstadoItem = '0' WHERE TipDoc = @TipDoc and Programa = @Documento and IdCia = @IdCia

			  --Actualizar las reservasdet al estado XLIQUIDAR, las que cumplan con la condición de que la fecha sea igual al dia actual
			  UPDATE Trn_TraEspResDetalle
			  SET EstadoItem = 'XLIQUIDAR'
		      FROM Trn_TraEspResDetalle t1
			  JOIN Trn_TraEspProgramaDet t2 ON t1.TipDoc = t2.TipDocRes and t1.Reserva = t2.Reserva and t1.IdCia = t2.IdCiaRes and t1.Item = t2.ItemRes
			  where (@date >= Convert(date,t1.FecDestino)) AND t2.TipDoc = @TipDoc and t2.Programa = @Documento and t2.IdCia = @IdCia

			  --actualizar vehiculos
			  declare @kmp varchar 
			  select @kmp = Valor from adm_opciones where IdOpc = 'kmp'
			  IF(@kmp = '2')
			  BEGIN
				UPDATE Vehiculos 
				SET 
				Km2Actual = (Km2Actual + (SELECT Distkmt FROM Rutas WHERE IdRuta = pd.IdRuta))
				FROM Vehiculos v
				JOIN Trn_TraEspProgramaDet pd ON pd.IdVehiculo = v.IdVehiculo and pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia
			  END

			  --Actualizar fechas de servicio
			  UPDATE Vehiculos 
			  SET 
			  FecPriServ = CASE WHEN FecPriServ <> NULL THEN v.FecPriServ ELSE pd.FecIniCump END, 
			  FecUltServ = pd.FecFinCump
			  FROM Vehiculos v
			  JOIN Trn_TraEspProgramaDet pd on v.IdVehiculo = pd.IdVehiculo and pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia

			   --Actualizar estado de vehiculo
			  UPDATE Vehiculos 
			  SET 
			  IdEstado = '0001'
			  FROM Vehiculos v
			  JOIN Trn_TraEspProgramaDet pd on v.IdVehiculo = pd.IdVehiculo and pd.IdNovedad = '0' and pd.TipDoc = @TipDoc and pd.Programa = @Documento and pd.IdCia = @IdCia
			  where v.IdEstado = '0002' 

			END

			--Cumplido de Detalles y Personas
			IF(@operacion = 'f')
			BEGIN
			   IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaDet))
			   BEGIN
			    declare @fTipDoc varchar(MAX)
				declare @fPrograma int
				declare @fCiaPgr varchar(2)
				declare @fItemPgr int
				declare @fCumplido bit
				declare @fFecIniCump datetime
				declare @fFecFinCump datetime

			    DECLARE cumpserv CURSOR
				FOR SELECT TipDoc,Programa,IdCia,Item,Cumplido,FecIniCump,FecFinCump FROM @Trn_TraEspProgramaDet
				OPEN cumpserv
				FETCH NEXT FROM cumpserv INTO @fTipDoc,@fPrograma,@fCiaPgr,@fItemPgr,@fCumplido,@fFecIniCump,@fFecFinCump
				WHILE @@FETCH_STATUS = 0
				BEGIN

				   UPDATE Trn_TraEspProgramaDet
				   SET Cumplido = @fCumplido,FecIniCump = @fFecIniCump, FecFinCump = @fFecFinCump			   
				   FROM Trn_TraEspProgramaDet 
				   WHERE TipDoc = @fTipDoc and Programa = @fPrograma and IdCia = @fCiaPgr  and Item = @fItemPgr

				   UPDATE Trn_TraEspProgramaPers
				   SET Cumplido = @fCumplido
				   from Trn_TraEspProgramaPers
				   WHERE TipDoc = @fTipDoc and Programa = @fPrograma and IdCia = @fCiaPgr  and Item = @fItemPgr
				
				FETCH NEXT FROM cumpserv INTO @fTipDoc,@fPrograma,@fCiaPgr,@fItemPgr,@fCumplido,@fFecIniCump,@fFecFinCump
				END
				CLOSE cumpserv
				DEALLOCATE cumpserv

			   END
			END

			--Cancelar servicio
			IF(@operacion = 's')
			BEGIN
			   IF(EXISTS(SELECT * FROM @Trn_TraEspProgramaDet))
			   BEGIN

			    declare @cveh varchar(11)
			    declare @cidnov varchar(4)
				declare @cnov varchar(MAX)
				declare @cObserv varchar(MAX)
				declare @cPrograma int
				declare @cCiaPgr varchar(2)
				declare @cItemPgr int
				declare @cEstadoItem int

				DECLARE cancelserv CURSOR
				FOR SELECT IdVehiculo,IdNovedad,Novedad,Programa,IdCia,Item,Observacion,EstadoItem FROM @Trn_TraEspProgramaDet where IdNovedad != '0'
				OPEN cancelserv
				FETCH NEXT FROM cancelserv INTO @cveh,@cidnov,@cnov,@cPrograma,@cCiaPgr,@cItemPgr,@cObserv,@cEstadoItem
				WHILE @@FETCH_STATUS = 0
				BEGIN
				   --Actualizar programa detalles las columnas de la novedad
				   UPDATE Trn_TraEspProgramaDet
				   SET IdNovedad = @cidnov, Novedad = @cnov, Observacion = @cObserv,EstadoItem = @cEstadoItem
				   FROM Trn_TraEspProgramaDet
				   WHERE TipDoc = 'PGR' and Programa = @cPrograma and IdCia = @cCiaPgr  and Item=  @cItemPgr

				   --Verificar vehiculos
				   declare @count int
				   select @count = count(*) from Trn_TraEspProgramaDet 
				   WHERE IdVehiculo = @cveh and  TipDoc = 'PGR' and Programa = @cPrograma and IdCia = @cCiaPgr

				   IF(@count = 1)
				   BEGIN
					   UPDATE Vehiculos
					   SET IdEstado = '0001'
					   FROM Vehiculos
					   WHERE IdVehiculo = @cveh 
				   END

				   --Eliminar personas de la programacion detalle cancelada
				   DELETE FROM Trn_TraEspProgramaPers WHERE TipDoc = 'PGR' and Programa = @cPrograma and IdCia = @cCiaPgr  and Item =  @cItemPgr

				FETCH NEXT FROM cancelserv INTO @cveh,@cidnov,@cnov,@cPrograma,@cCiaPgr,@cItemPgr,@cObserv,@cEstadoItem
				END
				CLOSE cancelserv
				DEALLOCATE cancelserv

			   END
			END

			SELECT @Documento

			COMMIT TRAN
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();
			RAISERROR(
			@ErrorMessage, -- Message text.
			@ErrorSeverity, -- Severity.
			@ErrorState -- State.
			);
		END CATCH
RETURN 0
GO