IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CFJ_SCA_GenerarQR') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CFJ_SCA_GenerarQR
END
GO


CREATE FUNCTION [dbo].[CFJ_SCA_GenerarQR](@pJson VARCHAR(8000)) 
RETURNS VARCHAR(50)
AS
BEGIN
/*Variable Retorno*/
DECLARE @QR						VARCHAR(50)
/*FIN Variable Retorno*/

/*Variables recibidas por parámetro a traves del JSON*/
DECLARE @pId_Trans				INT
DECLARE @pItem					SMALLINT
DECLARE @pFormato				VARCHAR(10)

SET @pId_Trans					= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Trans')), 0)
SET @pItem						= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Item')), 0)
SET @pFormato					= ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@pJson,'Formato')), '')
/*FIN Variables recibidas por parámetro a traves del JSON*/

/*Variables Locales*/
DECLARE @Evento_QR_Formato		VARCHAR(40)
DECLARE @Id_Lista_Precio		SMALLINT	
DECLARE @Id_Acceso				SMALLINT
DECLARE @Prox_Nro_Secuencial	INT
DECLARE @Cbtes_Id_Cliente		INT
DECLARE @Cbtes_Items_Id_Cliente	INT
DECLARE @Id_Producto			VARCHAR(14)
DECLARE @Doc_Nro				BIGINT
DECLARE @Nro_Ubic				VARCHAR(10)
DECLARE @Lote					VARCHAR(20)
/*FIN Variables Locales*/

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF(@pId_Trans = 0 OR @pItem = 0)
BEGIN
	SET @QR = ''
	RETURN @QR
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF(@pFormato <> '')
BEGIN
	SET @Evento_QR_Formato = @pFormato
END
ELSE
BEGIN
	SET @Evento_QR_Formato = dbo.CF_xParam('Evento_QR_Formato')
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT 
@Id_Lista_Precio			= ISNULL(Id_Lista, 0),
@Cbtes_Id_Cliente			= ISNULL(Id_Cliente, 0)
FROM Cbtes
WHERE
Id_Trans = @pId_Trans

SELECT
@Cbtes_Items_Id_Cliente		= ISNULL(Id_Cliente, 0),
@Id_Producto				= ISNULL(Id_Producto, ''),
@Lote						= ISNULL(Lote, '')
FROM Cbtes_Items
WHERE
Id_Trans = @pId_Trans
AND Item = @pItem

SELECT
@Doc_Nro					= ISNULL(Doc_Nro, 0)
FROM Clientes
WHERE
Id_Cliente = @Cbtes_Items_Id_Cliente

SELECT
@Nro_Ubic					= ISNULL(Nro_Ubic, '')
FROM Productos
WHERE
Id_Producto	= @Id_Producto

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF(@Evento_QR_Formato = 'DNI')
BEGIN
	SET @QR = CONVERT(VARCHAR(20), @Doc_Nro)

	RETURN @QR
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF(@Evento_QR_Formato = '14')
BEGIN
	SET @QR =	RIGHT('0000' + CAST(@Id_Lista_Precio AS VARCHAR), 4) + 
				RIGHT('000' + CAST(CAST(@Nro_Ubic AS INT) AS VARCHAR), 3) +
				RIGHT('0000000' + CAST(CAST(@Lote AS VARCHAR) AS VARCHAR), 7)

	RETURN @QR
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF(@Evento_QR_Formato = '43')
BEGIN
	SET @QR =	RIGHT('0000000000' + CAST(CAST(@Lote AS VARCHAR) AS VARCHAR), 10) +
				RIGHT('00000' + CAST(@Id_Lista_Precio AS VARCHAR), 5) + 
				RIGHT('000' + CAST(CAST(@Nro_Ubic AS INT) AS VARCHAR), 3) +
				RIGHT('0000000000' + CAST(@Cbtes_Items_Id_Cliente AS VARCHAR), 10) + 
				RIGHT('0000000000' + CAST(@pId_Trans AS VARCHAR), 10) + 
				RIGHT('00000' + CAST(@pItem AS VARCHAR), 5)

	RETURN @QR
END

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

RETURN ISNULL(@QR, '')
END

--SELECT DBO.CFJ_SCA_GenerarQR ('{"Id_trans":"5251414","Item":"1","Formato":"DNI"}')