IF EXISTS ( SELECT * FROM   sysobjects WHERE  id = object_id(N'dbo.CPJ_Cliente_Login') and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CPJ_Cliente_Login 
END
GO


CREATE PROCEDURE [dbo].[CPJ_Cliente_Login] @pJson VARCHAR(8000)
AS 
SET NOCOUNT ON 

/*Variables recibidas por parámetro a traves del JSON*/
DECLARE @pFlagUsarTrans			TINYINT
DECLARE @pModoDebug				TINYINT
DECLARE @pEmail					VARCHAR(1000)
DECLARE @pClave					VARCHAR(1000)

SET @pFlagUsarTrans				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'FlagUsarTrans')), 0)
SET @pModoDebug					= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'ModoDebug')), 0)
SET @pEmail						= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'Email')), 0)
SET @pClave						= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'Clave')), 0)
/*FIN Variables recibidas por parámetro a traves del JSON*/

DECLARE @Cant INT	

IF @pModoDebug = 1
BEGIN
	PRINT @pEmail	
	PRINT @pClave
END


IF UPPER(SUBSTRING(@pEmail,1,3)) = 'ID:' AND @pClave = LTRIM(RTRIM(dbo.CF_xParam('Kiosk_Backdoor')))
BEGIN
	IF @pModoDebug = 1
	BEGIN
		PRINT 'Ingresa por ID'	
	END
	IF ISNUMERIC( SUBSTRING(@pEmail,4, LEN(@pEmail) - 3 ) ) = 1
		BEGIN 
		SELECT		C.Id_Cliente, C.Id_Cliente_Ref, C.Apellido, C.Nombre, C.Razon_Social, C.Email_Web, C.Ult_Cuota_Paga, C.Id_Cliente_Externo, C.Id_Tipo_Cli
					,T.Descripcion AS 'Descripcion_Tipo_Cli'
			FROM	Clientes C LEFT OUTER JOIN Clientes_Tipos T ON T.Id_Tipo_Cli = C.Id_Tipo_Cli
			WHERE	C.id_Cliente = CAST( SUBSTRING(@pEmail,4, LEN(@pEmail) - 3 ) AS INT)
					AND C.Activo=1
		END
END
ELSE
BEGIN
	IF @pModoDebug = 1
	BEGIN
		PRINT 'Ingresa por email'	
	END	
	IF  LEN(RTRIM(LTRIM(@pEmail))) <> 0
	BEGIN 

		SET @Cant = (	SELECT	COUNT(*)
							FROM	Clientes C WITH (NOLOCK)
									LEFT OUTER JOIN Clientes_Tipos T  WITH (NOLOCK) ON T.Id_Tipo_Cli = C.Id_Tipo_Cli
							WHERE	C.Email_Web = LTRIM(RTRIM(@pEmail))
								AND C.Clave_Web = LTRIM(RTRIM(@pClave))
								AND C.Activo=1)
		IF @pModoDebug = 1
		BEGIN
			PRINT 'Se repite el user y clave, cantidad:'+ CAST(@Cant AS VARCHAR(10))	
		END	
		IF 	@Cant = 1
		BEGIN					
			SELECT		C.Id_Cliente, C.Id_Cliente_Ref, C.Apellido, C.Nombre, C.Razon_Social, C.Email_Web
						, C.Ult_Cuota_Paga, C.Id_Cliente_Externo, C.Id_Tipo_Cli, T.Descripcion AS 'Descripcion_Tipo_Cli'
				FROM	Clientes C LEFT OUTER JOIN Clientes_Tipos T ON T.Id_Tipo_Cli = C.Id_Tipo_Cli
				WHERE	C.Email_Web = LTRIM(RTRIM(@pEmail))
					AND C.Clave_Web = LTRIM(RTRIM(@pClave))
					AND C.Activo=1
		END			

	END
END
