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

CREATE PROCEDURE 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)
		,@pIp			VARCHAR(50)
		,@pId_Session	VARCHAR(32)
		,@pOrgien		VARCHAR(3)
		,@pVersion		VARCHAR(32)

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')),'')
SET @pClave						= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'Clave')), '')
SET @pIp						= ISNULL(CONVERT(VARCHAR(50), dbo.CF_JSON_VALUE(@pJson,'Ip')),'')
SET @pId_Session				= ISNULL(CONVERT(VARCHAR(32), dbo.CF_JSON_VALUE(@pJson,'Id_Session')), '')
SET @pOrgien					= ISNULL(CONVERT(VARCHAR(3), dbo.CF_JSON_VALUE(@pJson,'Orgien')), '')
SET @pVersion					= ISNULL(CONVERT(VARCHAR(32), dbo.CF_JSON_VALUE(@pJson,'Version')), '')

/*FIN Variables recibidas por parámetro a traves del JSON*/

DECLARE @Cant		INT	
		,@Id_Cliente	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',C.Fecha_Web_Ult_Ingreso
			INTO	#Resp
			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
	    SELECT @Id_Cliente = Id_Cliente FROM #Resp
		SELECT * FROM #Resp R
		DROP TABLE #Resp
        IF OBJECT_ID('Clientes_Logins') IS NOT NULL
		BEGIN
			INSERT INTO Clientes_Logins (Fecha_Login,Id_Cliente,[Ip],Id_Session,Orgien,[Version])
					VALUES (GETDATE(),@Id_Cliente,@pIp,@pId_Session,@pOrgien,@pVersion)
		END
		UPDATE Clientes SET Fecha_Web_Ult_Ingreso = GETDATE() WHERE Id_Cliente = @Id_Cliente
	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',C.Fecha_Web_Ult_Ingreso
				INTO #Resp2
				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

			SELECT @Id_Cliente = Id_Cliente FROM #Resp2
			SELECT * FROM #Resp2 
			DROP TABLE #Resp2
			IF OBJECT_ID('Clientes_Logins') IS NOT NULL
			BEGIN
				INSERT INTO Clientes_Logins (Fecha_Login,Id_Cliente,[Ip],Id_Session,Orgien,[Version])
						VALUES (GETDATE(),@Id_Cliente,@pIp,@pId_Session,@pOrgien,@pVersion)
			END
			UPDATE Clientes SET Fecha_Web_Ult_Ingreso = GETDATE() WHERE Id_Cliente = @Id_Cliente
		END			

	END
END
