--Se aņade el nuevo campo LiqFletePropio en la tabla Vehiculos
GO
BEGIN TRANSACTION;

-- Verificar si la columna ya existe antes de agregarla
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Vehiculos' AND COLUMN_NAME = 'LiqFletePropio'
)
BEGIN
    ALTER TABLE Vehiculos
    ADD LiqFletePropio BIT NOT NULL DEFAULT 0;
END

COMMIT TRANSACTION;
GO
--Se eliminan los prcedimientos que tienen la referencia de la tabla
GO
DROP PROC IF EXISTS [paWcfSeInsVehiculos]
GO
--Se elimina el tipo de tabla y se vuelve a crear

GO
DROP TYPE IF EXISTS [dataTypeVehiculos]
GO

/****** Object:  UserDefinedTableType [dbo].[dataTypeVehiculos]    Script Date: 11/03/2025 14:13:18 ******/
CREATE TYPE [dbo].[dataTypeVehiculos] AS TABLE(
	[IdVehiculo] [varchar](10) NULL,
	[NumVeh] [varchar](10) NULL,
	[ClaseVeh] [varchar](10) NULL,
	[IdTipoVeh] [varchar](4) NULL,
	[IdMarca] [varchar](4) NULL,
	[IdLinea] [varchar](10) NULL,
	[IdColor] [varchar](4) NULL,
	[IdTipoMot] [varchar](4) NULL,
	[IdCrceria] [varchar](4) NULL,
	[Modelo] [varchar](4) NULL,
	[FecRep] [smalldatetime] NULL,
	[Config] [varchar](5) NULL,
	[VehArtic] [bit] NULL,
	[NumLlan] [int] NULL,
	[NumLlans] [int] NULL,
	[IdCat] [varchar](4) NULL,
	[CdCatv] [varchar](4) NULL,
	[ClaseMat] [varchar](10) NULL,
	[Cilind] [decimal](14, 4) NULL,
	[CapTanq] [decimal](14, 4) NULL,
	[IdCom] [varchar](4) NULL,
	[IdLub] [varchar](4) NULL,
	[IdTlla] [varchar](4) NULL,
	[IdMarlla] [varchar](4) NULL,
	[PesoVacio] [decimal](14, 4) NULL,
	[PesoMax] [decimal](14, 4) NULL,
	[NumMotor] [varchar](30) NULL,
	[SerieChasis] [varchar](30) NULL,
	[NumSerie] [varchar](30) NULL,
	[CdRemque] [varchar](10) NULL,
	[Longitud] [decimal](14, 4) NULL,
	[CarrAlto] [decimal](14, 4) NULL,
	[CarrAncho] [decimal](14, 4) NULL,
	[CarrLargo] [decimal](14, 4) NULL,
	[CarrCapac] [decimal](14, 4) NULL,
	[UndCapc] [varchar](10) NULL,
	[Comptmtos] [int] NULL,
	[CapComp] [varchar](50) NULL,
	[PasjerosPie] [int] NULL,
	[PasjerosSen] [int] NULL,
	[NitEmpresa] [varchar](16) NULL,
	[IdPropietario] [varchar](16) NULL,
	[IdPoseedor] [varchar](16) NULL,
	[IdConductor] [varchar](16) NULL,
	[IdPpd] [varchar](4) NULL,
	[TipoAfil] [varchar](10) NULL,
	[Adquisc] [varchar](10) NULL,
	[NitProv] [varchar](16) NULL,
	[FecCompra] [smalldatetime] NULL,
	[VrComcial] [money] NULL,
	[VrAseg] [money] NULL,
	[VrAvaludo] [money] NULL,
	[VidaUtil] [int] NULL,
	[FecSalida] [smalldatetime] NULL,
	[NContrato] [int] NULL,
	[IdAdmon] [varchar](4) NULL,
	[IdNiv] [varchar](4) NULL,
	[IdGrupo] [varchar](4) NULL,
	[CdGrupR] [varchar](4) NULL,
	[CdTarifa] [varchar](4) NULL,
	[FecIngreso] [smalldatetime] NULL,
	[FecVigencia] [smalldatetime] NULL,
	[FecRetiro] [smalldatetime] NULL,
	[NumSoat] [varchar](30) NULL,
	[FecSoat] [smalldatetime] NULL,
	[VigSoat] [smalldatetime] NULL,
	[NitEmpSoat] [varchar](16) NULL,
	[TarjProp] [varchar](30) NULL,
	[FecTProp] [smalldatetime] NULL,
	[VigTProp] [smalldatetime] NULL,
	[CdLugTp] [varchar](8) NULL,
	[Ulttramite] [varchar](150) NULL,
	[RespCivil] [varchar](30) NULL,
	[FecRCivil] [smalldatetime] NULL,
	[VigRCivil] [smalldatetime] NULL,
	[RegNalCarga] [varchar](30) NULL,
	[FecRegNal] [smalldatetime] NULL,
	[VigRegNal] [smalldatetime] NULL,
	[RevTecMec] [varchar](30) NULL,
	[FecTecMec] [smalldatetime] NULL,
	[VigTecMec] [smalldatetime] NULL,
	[CertGases] [varchar](30) NULL,
	[FecCertGas] [smalldatetime] NULL,
	[VigCertGas] [smalldatetime] NULL,
	[TarjOper] [varchar](30) NULL,
	[FecTarjOper] [smalldatetime] NULL,
	[VigTarjOper] [smalldatetime] NULL,
	[KmInicial] [int] NULL,
	[KmActual] [int] NULL,
	[Km2Actual] [int] NULL,
	[Regtradora] [bit] NULL,
	[CentInicial] [int] NULL,
	[CentFinal] [int] NULL,
	[VrLmtCred] [money] NULL,
	[VrSaldoAct] [money] NULL,
	[Descripcion] [varchar](100) NULL,
	[Observacion] [varchar](250) NULL,
	[CdCenSer] [varchar](4) NULL,
	[CdLocal] [varchar](8) NULL,
	[Ubicacion] [varchar](100) NULL,
	[PathFoto] [varchar](30) NULL,
	[FecPriServ] [smalldatetime] NULL,
	[FecUltServ] [smalldatetime] NULL,
	[FecUltAcc] [smalldatetime] NULL,
	[TieneAcc] [bit] NULL,
	[FecPagImp] [smalldatetime] NULL,
	[IdEstado] [varchar](4) NULL,
	[Inactivo] [bit] NULL,
	[FechaAdd] [smalldatetime] NULL,
	[FechaUpdate] [smalldatetime] NULL,
	[IdUsuario] [varchar](11) NULL,
	[ValorCupo] [money] NULL,
	[ObligaTProd] [bit] NULL,
	[GarantiaAcc] [bit] NULL,
	[DocCompleta] [bit] NULL,
	[CertMovilizacion] [varchar](20) NULL,
	[FecCertMovil] [smalldatetime] NULL,
	[VigCertMovil] [smalldatetime] NULL,
	[CdRutaHab] [varchar](4) NULL,
	[DeclaracImp] [varchar](50) NULL,
	[TipoIngreso] [varchar](4) NULL,
	[IdOrgTra] [varchar](8) NULL,
	[GPSoperador] [varchar](250) NULL,
	[GPSUsuario] [varchar](50) NULL,
	[GPSClave] [varchar](50) NULL,
	[CantFiltros] [decimal](14, 4) NULL,
	[GPSIdOper] [varchar](16) NULL,
	[LiqFletePropio] [bit] NULL
)
GO


--Creamos Nuevamente el procedimiento 

GO
CREATE PROCEDURE [dbo].[paWcfSeInsVehiculos]
	@Vehiculos [dbo].[dataTypeVehiculos] READONLY,
	@VehAnexo [dbo].[dataTypeVehAnexo] READONLY,
	@operacion [varchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN TRAN
	BEGIN TRY		 

		--UPDATE SI-EXISTE RECORDS
		 IF(@operacion = 'u')
		 BEGIN
			 UPDATE Vehiculos
			 SET 
			 IdVehiculo = t2.IdVehiculo, NumVeh = t2.NumVeh, ClaseVeh = t2.ClaseVeh, IdTipoVeh = t2.IdTipoVeh, IdMarca = t2.IdMarca, IdLinea = t2.IdLinea,
			 IdColor = t2.IdColor, IdTipoMot = t2.IdTipoMot, IdCrceria = t2.IdCrceria,  Modelo = t2.Modelo, FecRep = t2.FecRep, Config = t2.Config,
			 VehArtic = t2.VehArtic, NumLlan = t2.NumLlan, NumLlans = t2.NumLlans, IdCat = t2.IdCat, CdCatv = t2.CdCatv, ClaseMat = t2.ClaseMat,
			 Cilind = t2.Cilind, CapTanq = t2.CapTanq, IdCom = t2.IdCom, IdLub = t2.IdLub, IdTlla = t2.IdTlla, IdMarlla = t2.IdMarlla,
			 PesoVacio = t2.PesoVacio, PesoMax = t2.PesoMax, NumMotor = t2.NumMotor, SerieChasis = t2.SerieChasis, NumSerie = t2.NumSerie, CdRemque = t2.CdRemque,
			 Longitud = t2.Longitud, CarrAlto = t2.CarrAlto, CarrAncho = t2.CarrAncho,  CarrLargo = t2.CarrLargo, CarrCapac = t2.CarrCapac, UndCapc = t2.UndCapc,
			 Comptmtos = t2.Comptmtos, CapComp = t2.CapComp, PasjerosPie = t2.PasjerosPie, PasjerosSen = t2.PasjerosSen, NitEmpresa = t2.NitEmpresa, IdPropietario = t2.IdPropietario,
			 IdPoseedor = t2.IdPoseedor, IdConductor = t2.IdConductor, IdPpd = t2.IdPpd, TipoAfil = t2.TipoAfil, Adquisc = t2.Adquisc, NitProv = t2.NitProv, 
			 FecCompra = t2.FecCompra, VrComcial = t2.VrComcial, VrAseg = t2.VrAseg, VrAvaludo = t2.VrAvaludo, VidaUtil = t2.VidaUtil, FecSalida = t2.FecSalida, 
			 NContrato = t2.NContrato, IdAdmon = t2.IdAdmon, IdNiv = t2.IdNiv, IdGrupo = t2.IdGrupo, CdGrupR = t2.CdGrupR, CdTarifa = t2.CdTarifa, 
			 FecIngreso = t2.FecIngreso, FecVigencia = t2.FecVigencia, FecRetiro = t2.FecRetiro, NumSoat = t2.NumSoat, FecSoat = t2.FecSoat, VigSoat = t2.VigSoat, 
			 NitEmpSoat = t2.NitEmpSoat, TarjProp = t2.TarjProp, FecTProp = t2.FecTProp, VigTProp = t2.VigTProp, CdLugTp = t2.CdLugTp, Ulttramite = t2.Ulttramite, 
			 RespCivil = t2.RespCivil, FecRCivil = t2.FecRCivil, VigRCivil = t2.VigRCivil, RegNalCarga = t2.RegNalCarga, FecRegNal = t2.FecRegNal, VigRegNal = t2.VigRegNal, 
			 RevTecMec = t2.RevTecMec, FecTecMec = t2.FecTecMec, VigTecMec = t2.VigTecMec, CertGases = t2.CertGases, FecCertGas = t2.FecCertGas, VigCertGas = t2.VigCertGas, 
			 TarjOper = t2.TarjOper, FecTarjOper = t2.FecTarjOper, VigTarjOper = t2.VigTarjOper, KmInicial = t2.KmInicial, KmActual = t2.KmActual, Km2Actual = t2.Km2Actual, 
			 Regtradora = t2.Regtradora, CentInicial = t2.CentInicial, CentFinal = t2.CentFinal, VrLmtCred = t2.VrLmtCred, VrSaldoAct = t2.VrSaldoAct, Descripcion = t2.Descripcion, 
			 Observacion = t2.Observacion, CdCenSer = t2.CdCenSer, CdLocal = t2.CdLocal, Ubicacion = t2.Ubicacion, PathFoto = t2.PathFoto, FecPriServ = t2.FecPriServ, 
			 FecUltServ = t2.FecUltServ,FecUltAcc = t2.FecUltAcc,TieneAcc = t2.TieneAcc, FecPagImp = t2.FecPagImp, IdEstado = t2.IdEstado, Inactivo = t2.Inactivo, 
			 FechaAdd = t2.FechaAdd, FechaUpdate = t2.FechaUpdate, IdUsuario = t2.IdUsuario, ValorCupo = t2.ValorCupo, ObligaTProd = t2.ObligaTProd, GarantiaAcc = t2.GarantiaAcc, 
			 DocCompleta = t2.DocCompleta, CertMovilizacion = t2.CertMovilizacion, FecCertMovil = t2.FecCertMovil, VigCertMovil = t2.VigCertMovil, CdRutaHab = t2.CdRutaHab, 
			 DeclaracImp = t2.DeclaracImp, TipoIngreso = t2.TipoIngreso, IdOrgTra = t2.IdOrgTra, GPSoperador = t2.GPSoperador,  GPSUsuario = t2.GPSUsuario, GPSClave = t2.GPSClave,
			 CantFiltros = t2.CantFiltros,GPSIdOper=t2.GPSIdOper,LiqFletePropio=t2.LiqFletepropio
			 FROM Vehiculos t1
			 JOIN @Vehiculos t2 ON t1.Idvehiculo = t2.IdVehiculo
			 where t1.IdVehiculo = t2.IdVehiculo

			 UPDATE VehAnexo
			 SET 
			 IdVehiculo = t2.IdVehiculo,
			 PolizaRCE = t2.PolizaRCE,
			 FecEmisionRCE = t2.FecEmisionRCE,
			 FecVigenciaRCE = t2.FecVigenciaRCE,
			 appPyP = t2.appPyP
			 FROM VehAnexo t1
			 JOIN @VehAnexo t2 ON t1.Idvehiculo = t2.IdVehiculo
			 where t1.IdVehiculo = t2.IdVehiculo
			 --Inserta los que no existen
			 INSERT INTO VehAnexo(
			 IdVehiculo,PolizaRCE,FecEmisionRCE,FecVigenciaRCE,appPyP
	         ) 
			 SELECT 
			 IdVehiculo,PolizaRCE,FecEmisionRCE,FecVigenciaRCE,appPyP
			 FROM @VehAnexo
			 WHERE IdVehiculo NOT IN(SELECT IdVehiculo FROM VehAnexo)
		 END
 
		 --INSERT NO-EXISTE RECORDS
		 IF(@operacion = 'i')
		 BEGIN
			 INSERT INTO Vehiculos(
			 IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,
			 NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq, IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,
			 NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,
			 PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,TipoAfil,Adquisc,NitProv,FecCompra,VrComcial,
			 VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,
			 FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,
			 VigRCivil,RegNalCarga,FecRegNal,VigRegNal,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,
			 VigTarjOper,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,
			 CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo,FechaAdd,FechaUpdate,
			 IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,
			 GPSoperador,GPSUsuario,GPSClave, CantFiltros,GPSIdOper,LiqFletePropio
			 )
			 SELECT 
			 IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,
			 NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq, IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,
			 NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,
			 PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,TipoAfil,Adquisc,NitProv,FecCompra,VrComcial,
			 VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,
			 FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,
			 VigRCivil,RegNalCarga,FecRegNal,VigRegNal,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,
			 VigTarjOper,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,
			 CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo,FechaAdd,FechaUpdate,
			 IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,
			 GPSoperador,GPSUsuario,GPSClave, CantFiltros,GPSIdOper,LiqFletePropio
			 FROM @Vehiculos
			 WHERE IdVehiculo NOT IN(SELECT IdVehiculo FROM Vehiculos)

			 INSERT INTO VehAnexo(
			 IdVehiculo,PolizaRCE,FecEmisionRCE,FecVigenciaRCE,appPyP
			 )
			 SELECT 
			 IdVehiculo,PolizaRCE,FecEmisionRCE,FecVigenciaRCE,appPyP
			 FROM @VehAnexo
			 WHERE IdVehiculo NOT IN(SELECT IdVehiculo FROM VehAnexo)
		 END

		 --DELETE RECORDS
		 IF(@operacion = 'd')
		 BEGIN
			 DECLARE @id VARCHAR (10)
			 SELECT @id = IdVehiculo FROM @Vehiculos
			 DELETE FROM VehAnexo WHERE IdVehiculo = @id
			 DELETE FROM Vehiculos WHERE IdVehiculo = @id
		 END

	SELECT '1'

	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