INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('ECL','02','Estadisticas de Clientes-Movimientos','CrEclMov.rpt',1,1,1,0,1,'paQrytm_EstClientesMov','','Estadísticas de Clientes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LVT','01','Predeterminado','CrValCre.rpt',1,1,1,0,1,'paQryVales_Cr','','Vales crédito')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('PUN','01','Predeterminado','CrPunAcu.rpt',1,1,1,0,1,'paQryPuntos_Cr','','Puntos Acumulados')
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMVCC','MAIPRO','CLI',6,'Modificación de vales','FRMVCC','NNSNNNNNNNNNNNS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLPUN','MAIINF','GEN',8,'Puntos Acumulados','FRMLPUN','SSSSSSSSSSSSS',0,'')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPuntos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryPuntos_Cr]
GO

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryPuntos_Cr  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdProgrma INT=Null
 AS 
 SELECT  M.IdProgrma AS NumPrograma,M.NumTarjeta  AS NTarjeta,M.IdCliente AS NitCliente,CL.RazonSocial AS NomCliente,M.IdAgencia AS CdAgencia,Agencia
,TipDoc,TipoDoc,Documento,M.IdCia AS CdCia,Compania,Item,M.Fecha AS FechaMov,VrBase,PuntFactura,PuntExtra,M.PuntRedim AS Punt_Redim,Detalles,Referncia
--datos de tarjeta
,T.IdClie AS TarjNit,CLT.RazonSocial AS TarjNomCliente,T.Fecha AS TarjFecha,TotalPuntos,T.PuntRedim AS TotalRedim,FecUltCompra,T.Inactivo AS TarjInactivo
--datos de programa
,FecInicial,FecFinal,MesesInact,MesesVcmto,P.Observacion AS Observ,P.Anulado AS ProgAnulado,P.TimeSys AS FecCreacion,P.IdUsuario AS CdUsuari,Usuario
--datos del cliente
,CL.TipoId AS CliTipoId,CL.Dv AS CliDV,CL.NomCial AS CliNomCial,CL.SiglaRaz CliSigalRaz,CL.Direccion AS CliDireccion,CL.Telefono AS CliTelefono
,CL.IdLocal AS CliCodLocal,Localidad,L.IdDep AS CdDep,Departamento,CL.Inactivo AS TercInactivo,CLI.IdEstado AS CliCdEstado,CLI.Inactivo AS CliInactivo
 FROM Trn_Puntos AS M INNER JOIN  Terceros AS CL ON M.IdCliente=CL.IdTercero  INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia
 INNER JOIN Localidades AS L ON CL.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN Agencias AS  A ON M.IdAgencia=A.IdAgencia LEFT JOIN TercCliente AS CLI ON M.IdCliente=CLI.IdClie
 LEFT JOIN PuntTarjetas AS T ON M.NumTarjeta=T.NumTarjeta LEFT JOIN Terceros AS CLT ON T.IdClie=CLT.IdTercero
 LEFT JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
 LEFT JOIN  PuntProg AS P ON M.IdProgrma=P.IdProgrma LEFT JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 WHERE M.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 AND ( M.IdProgrma>=ISNULL(@pmIdProgrma,0)  AND  M.IdProgrma<=ISNULL(@pmIdProgrma,2147483647))
 ORDER BY M.IdCliente,M.Fecha
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVales @pmTipDoc VARCHAR(3),@pmnRadica INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,nRadica,IdCia,Item,TipoRango,IdRango,NumSerie,NumForma,Fecha,HoraServ,IdCliente
,IdAgencia,IdCuenta,NumMovil,CentCosto,Unidades,HoraRec,HoraEspera,FactUnid,FactRutas,FactHoras,VrPago,VrCobro,PlacaVeh
,CdConductor,NomConductor,CantPasaj,Usuarios,TipFac,NumFactura,IdCiaFac,FechaFac,Anulado,NumDev,FecDev,Observacion,FechaUp,CdUsuario
 FROM Trn_Vales
 WHERE TipDoc=@pmTipDoc AND nRadica=@pmnRadica AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVales_Cr  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmTipoRango VARCHAR(3)=Null,@pmNumFormaIni INT=Null,@pmNumFormaFin INT=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT TipoRango,IdRango,NumSerie,NumForma,V.Fecha AS FechaServ,HoraServ,V.nRadica AS NumRadica,V.IdCia AS CdCia,Compania,Item
,IdCliente,CL.RazonSocial AS NomCliente,V.IdAgencia AS ValAgencia,Agencia,NumMovil,CentCosto,Unidades,HoraRec,HoraEspera,FactUnid,FactRutas,FactHoras
,VrPago,VrCobro,IdCuenta,CantPasaj,Usuarios,V.Observacion AS ValObserv,TipFac,NumFactura,IdCiaFac,FechaFac,V.Anulado AS ValAnulado,V.NumDev AS ValNumAnul,V.FecDev AS ValFechaDev
,FechaUp,CdUsuario,UV.Usuario AS ValUsuario
--datos de radicación
,R.Fecha AS FechaRad,FechaVence,R.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,R.Referencia AS DocReferncia,CxPagar,TipCom,Comprobante,IdCiaCom
,R.Anulado AS DocAnulado,R.NumDev AS DocNumDev,R.FecDev  AS DocFecDev,R.Observacion AS Observ,R.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
--datos del cliente
,CL.TipoId AS CliTipoId,CL.Dv AS CliDv,CL.Codigo AS CliCodigo,CL.NomCial AS CliNomCial,CL.Direccion AS CliDireccion,CL.IdLocal AS CliCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,CL.Telefono AS CliTelefono,CL.Fax AS CliFax,CL.e_mail AS CliEmail,CL.IdSector AS CdSector,SectorEco,CL.IdRegimen AS CdRegimen,Regimen,CL.TipEnte AS CliTipoEnte
 FROM Trn_Vales AS V INNER JOIN Trn_RadicaVales AS R ON V.TipDoc=R.TipDoc AND V.nRadica=R.nRadica AND V.IdCia=R.IdCia
 INNER JOIN  Terceros AS CL ON V.IdCliente=CL.IdTercero
 INNER JOIN Localidades AS L ON CL.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON CL.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON CL.IdSector=SE.IdSector
 INNER JOIN Companias AS CN ON V.IdCia=CN.IdCia
 INNER JOIN  Terceros AS T ON R.IdTercero=T.IdTercero   INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 LEFT JOIN adm_Usuarios AS UV ON V.CdUsuario=UV.IdUsuario  LEFT JOIN Agencias AS A ON V.IdAgencia=A.IdAgencia 
 WHERE  (V.Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND V.Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 AND V.IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipoRango LIKE ISNULL(@pmTipoRango,'%')
 AND NumForma BETWEEN ISNULL(@pmNumFormaIni,0) AND ISNULL(@pmNumFormaFin,2147483647)
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 ORDER BY TipoRango,IdRango,NumForma



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_EstClientesMov @pmtmEst CHAR(2)
 AS 
 SELECT TC.tmIdCliente AS NitCliente,T.RazonSocial AS NomCliente,TC.tmIdAgencia AS CdAgencia,A.Agencia AS NomAgencia,TC.tmIdCia AS CdCia,CI.Compania AS NomCia
 ,tmFecUltFcr,tmPPlazoFcr,tmMayorFcr,tmMenorFcr,tmFecUltFco,tmPPlazoFco,tmMayorFco,tmMenorFco,tmVentaBruto
,tmVrDevolucion,tmVrDescuentos,tmCostoBruto,tmCostoDev,tmVrIva,tmFecUltPago,tmPromPago,tmSaldoMora,tmFecIniMora,tmDiasEnMora
,tmChequesDev,tmSaldoActual,tmSaldoAnterior,tmVrFacturas,tmVrDevFacturas,tmVrRecibos,tmVrDevRecibos,tmVrNotasDeb,tmVrNotasCre,tmVrCheques
--Datos del cliente
,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigla,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,VrCupo,VrSaldo,CLI.IdForma AS CdForma,ExcIva,TrfIntMora,CLI.DiasGracia AS CliDiasGracia,LiqFletes,Autoret
,CLI.Inactivo AS ClieInactivo,CLI.CdBandera AS CliCdbandera,TipoBandera,CLI.IdPlazo AS CdPlazo,Plazo,DiasPago,CLI.NContrato AS CliNumContrato
,CLI.IdVend AS CdVend,VN.RazonSocial AS Vendedor
--datos de movimientos
,M.tmIdAgencia AS MovAgencia,AM.Agencia AS MovNomAgencia,M.tmIdCia AS MovCodCia,CM.Compania AS MovNomCia,tmTipDoc,tmNumero,M.tmItem AS MovItem,tmFecha,tmCargos,tmAbonos,tmDetalle
,tmReferencia,tmNumForma,tmModalidad,tmTipRef,tmDocRef,tmIdCiaRef,tmCdConcepto
,tmTotalAbono,tmVrAfavor,tmTimeSys,tmFecVence,tmCdForma,tmEnEfectivo,tmCdBanco,tmBeneficiario,tmTipCom,tmComprobante,tmIdCiaCom
,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario
 FROM tm_EstClientes AS TC INNER JOIN Terceros AS T ON TC.tmIdCliente=T.IdTercero  INNER JOIN TercCliente AS CLI ON TC.tmIdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON CLI.IdVend=VN.IdTercero   INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona   INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  Companias AS CI ON TC.tmIdCia=CI.IdCia   LEFT JOIN Agencias AS A ON TC.tmIdAgencia=A.IdAgencia
 LEFT JOIN TiposBan AS TB ON CLI.CdBandera=TB.IdBandera LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN tm_MovClientes AS M ON TC.tmEst=M.tmEst AND TC.tmIdCliente=M.tmIdCliente
 AND TC.tmIdAgencia=M.tmIdAgencia AND TC.tmIdCia=M.tmIdCiaCom
 LEFT JOIN Agencias AS AM ON M.tmIdAgencia=AM.IdAgencia   LEFT JOIN  Companias AS CM ON M.tmIdCia=CM.IdCia
 WHERE TC.tmEst=@pmtmEst
 ORDER BY T.RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO