if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelFcSemanas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelFcSemanas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFcAbonos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFcAbonos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFcSemanas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFcSemanas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Semanas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_Semanas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Semanas_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_Semanas_Sel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFcSemanas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFcSemanas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFcSemanasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFcSemanasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFcSemanas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFcSemanas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFcSemanasAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFcSemanasAbo]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT
 AS 
 DELETE FROM Trn_FcSemanas
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFcAbonos @pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME
,@pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmVrAbono MONEY,@pmDescripcion VARCHAR(150)
 AS 
 INSERT INTO Trn_FcAbonos (TipRec,Recibo,IdCia,Item,Fecha,IdVehiculo,nSemana,VrAbono,Descripcion)
 VALUES (@pmTipRec,@pmRecibo,@pmIdCia,@pmItem,@pmFecha,@pmIdVehiculo,@pmnSemana,@pmVrAbono,@pmDescripcion)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmNumCargue INT,@pmTipoSem INT,@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrCargo MONEY
,@pmVrOtros MONEY,@pmVrPagado MONEY,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmFechaPago SMALLDATETIME,@pmObservacion VARCHAR(250),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FcSemanas (IdVehiculo,nSemana,NumCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado,TipRec,Recibo,IdCiaRec,FechaPago,Observacion,TimeSys,IdUsuario)
 VALUES (@pmIdVehiculo,@pmnSemana,@pmNumCargue,@pmTipoSem,@pmFecha,@pmFecVence,@pmVrCargo,@pmVrOtros,@pmVrPagado,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmFechaPago,@pmObservacion,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Semanas @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmVehiculo VARCHAR(10)
,@pmtmSemana INT,@pmtmVrTotal MONEY,@pmtmVrPagado MONEY,@pmtmDescripcion VARCHAR(150)
 AS 
 INSERT INTO tm_Semanas (tmNumero,tmItem,tmVehiculo,tmSemana,tmVrTotal,tmVrPagado,tmDescripcion)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmVehiculo,@pmtmSemana,@pmtmVrTotal,@pmtmVrPagado,@pmtmDescripcion)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Semanas_Sel  @pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Semanas (tmNumero,tmItem,tmVehiculo,tmSemana,tmVrTotal,tmVrPagado,tmDescripcion)
 SELECT  @pmtmNumero,Item, IdVehiculo, nSemana,0,VrAbono,Descripcion
 FROM   Trn_FcAbonos
 WHERE  TipRec=@pmTipRec  AND Recibo=@pmRecibo  AND IdCia=@pmIdCia
  

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT
 AS 
 SELECT IdVehiculo,nSemana,NumCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado,TipRec,Recibo,IdCiaRec,FechaPago,Observacion,TimeSys,IdUsuario
 FROM Trn_FcSemanas
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFcSemanasLta @pmIdVehiculo VARCHAR(10)=Null,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT F.IdVehiculo AS PlacaVeh,NumVeh,nSemana,NumCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado
,TipRec,Recibo,IdCiaRec,FechaPago,F.Observacion AS Observ,TimeSys,F.IdUsuario AS CdUsuario,Usuario
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,IdPropietario,T.RazonSocial AS Propietario
,IdPoseedor,P.RazonSocial AS Poseedor,IdConductor,CD.RazonSocial AS Conductor,TipoAfil,FecIngreso,V.FechaAdd AS FechaCrea,V.Inactivo AS VehInactivo
 FROM Trn_FcSemanas AS F INNER JOIN Vehiculos AS V ON F.IdVehiculo=V.IdVehiculo
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 INNER JOIN Terceros AS T ON V.IdPropietario=T.IdTercero INNER JOIN Terceros AS P ON V.IdPoseedor=P.IdTercero
 INNER JOIN Terceros AS CD ON V.IdConductor=CD.IdTercero
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 WHERE F.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY F.IdVehiculo,nSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmNumCargue INT,@pmTipoSem INT,@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrCargo MONEY,@pmVrOtros MONEY,@pmVrPagado MONEY
,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmFechaPago SMALLDATETIME,@pmObservacion VARCHAR(250)
 AS 
 UPDATE Trn_FcSemanas SET NumCargue=@pmNumCargue,TipoSem=@pmTipoSem,Fecha=@pmFecha,FecVence=@pmFecVence,VrCargo=@pmVrCargo,VrOtros=@pmVrOtros,VrPagado=@pmVrPagado
,TipRec=@pmTipRec,Recibo=@pmRecibo,IdCiaRec=@pmIdCiaRec,FechaPago=@pmFechaPago,Observacion=@pmObservacion
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpFcSemanasAbo  @pmIdVehiculo VARCHAR(10)=Null
 AS 
  UPDATE Trn_FcSemanas SET VrPagado=0  WHERE  IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') 
  UPDATE Trn_FcSemanas SET  Trn_FcSemanas.VrPagado=(SELECT ISNULL(SUM(Trn_FcAbonos.VrAbono),0)
   FROM Trn_FcAbonos  WHERE Trn_FcSemanas.IdVehiculo=Trn_FcAbonos.IdVehiculo
   AND Trn_FcSemanas.nSemana=Trn_FcAbonos.nSemana)
  WHERE IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO