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

CREATE FUNCTION [dbo].[CF_BancoPagoTIC_API_AR_TX](@pId_Trans INT, @pImporte DECIMAL(12,2), @pId_Tar_Lote INT, @pPeriodo DATETIME) 
RETURNS VARCHAR(8000)
AS
BEGIN

/*
VERSIONES:
21/06/2022 Se setea due_date y last_due_date tal como nos indicaron de PagoTIC
*/

/*Variable de Retorno*/
DECLARE @Retorno				VARCHAR(8000)
/*FIN Variable de Retorno*/

/*Variables Locales*/
DECLARE @body_Json				VARCHAR(8000)
DECLARE @detalle_json			VARCHAR(8000)
DECLARE @Url_WebService			VARCHAR(255)
DECLARE @IPN_Pagos				VARCHAR(255)
DECLARE @Url_Envio				VARCHAR(255)
DECLARE @Tar_Nro				VARCHAR(50)
DECLARE @Id_Moneda				VARCHAR(10)
DECLARE @Fecha_Vence			DATETIME
DECLARE @Fecha_Ult_Pago			DATETIME
DECLARE @Fecha_Vence_VC			VARCHAR(25)
DECLARE @Fecha_Ult_Pago_VC		VARCHAR(25)
DECLARE @Id_Concepto			VARCHAR(10)
DECLARE @Concepto_Descrip		VARCHAR(100)
DECLARE @Lote_Trans				VARCHAR(16)
/*FIN Variables Locales*/

/*--------------------------------------------------------------------------------------------------------------------------------*/

SELECT 
@Url_WebService		= ISNULL(Url_WebService, ''),
@IPN_Pagos			= ISNULL(IPN_Pagos, '')
FROM AR_PAGOTIC_WS_Configuracion
WHERE
Id_Configuracion = 0

SELECT 
@Tar_Nro		= ISNULL(Nro, '')
FROM Val_Tarjetas_Lotes_Items VTLI
WHERE
Id_Tar_Lote = @pId_Tar_Lote
AND Id_Trans = @pId_Trans

--SET @Url_Envio = @Url_WebService + 'suscripciones/adhesion/' + @Tar_Nro + '/pago'

SET @Lote_Trans = RIGHT('00000' + CAST(@pId_Tar_Lote AS VARCHAR), 5) + '-' + RIGHT('0000000000' + CAST(@pId_Trans AS VARCHAR), 10)

SET @Id_Moneda = 'ARS'

--21/06/2022 Segun lo hablado con Martin Malva y Micaela Soria se pone el 8 de cada mes para due_date y 12 para last_due_date 

SET @Fecha_Vence		= DATEADD(DAY, 8, dbo.CF_FOMONTH(@pPeriodo))

SET @Fecha_Vence_VC =	CAST(YEAR(@Fecha_Vence) AS VARCHAR)
						+ '-' 
						+ RIGHT('00' + CAST(MONTH(@Fecha_Vence) AS VARCHAR), 2) 
						+ '-' 
						+ RIGHT('00' + CAST(DAY(@Fecha_Vence) AS VARCHAR), 2)
						+ 'T'
						+ '23:59:59-0300'

SET @Fecha_Ult_Pago		= DATEADD(DAY, 12, dbo.CF_FOMONTH(@pPeriodo))

SET @Fecha_Ult_Pago_VC = CAST(YEAR(@Fecha_Ult_Pago) AS VARCHAR)
						+ '-' 
						+ RIGHT('00' + CAST(MONTH(@Fecha_Ult_Pago) AS VARCHAR), 2) 
						+ '-' 
						+ RIGHT('00' + CAST(DAY(@Fecha_Ult_Pago) AS VARCHAR), 2)
						+ 'T'
						+ '23:59:59-0300'

SELECT 
TOP 1
@Id_Concepto			= LTRIM(RTRIM(CI.Id_Producto)),
@Concepto_Descrip		= LTRIM(RTRIM(PR.Descripcion_Resumida))
FROM Cbtes_Items CI, Productos PR
WHERE
CI.Id_Producto = PR.Id_Producto
AND Id_Trans = @pId_Trans

/*--------------------------------------------------------------------------------------------------------------------------------*/

SET @detalle_json = '{' +
					dbo.CF_Json_ParsearVariable('external_reference', @pId_Trans, 'VARCHAR(10)') + ',' +
					dbo.CF_Json_ParsearVariable('concept_id', @Id_Concepto, 'VARCHAR(10)') + ',' +
					dbo.CF_Json_ParsearVariable('concept_description', @Concepto_Descrip, 'VARCHAR(100)') + ',' +
					dbo.CF_Json_ParsearVariable('amount', @pImporte, 'VARCHAR(10)') +
					'}'


SET @body_Json = '{' +
					dbo.CF_Json_ParsearVariable('currency_id', @Id_Moneda, 'VARCHAR(15)') + ',' +
					dbo.CF_Json_ParsearVariable('external_transaction_id', @Lote_Trans, 'VARCHAR(16)') + ',' +
					dbo.CF_Json_ParsearVariable('due_date', @Fecha_Vence_VC, 'VARCHAR(25)') + ',' +
					dbo.CF_Json_ParsearVariable('last_due_date', @Fecha_Ult_Pago_VC, 'VARCHAR(25)') + ',' +
					dbo.CF_Json_ParsearVariable('notification_url', @IPN_Pagos, 'VARCHAR(255)') + ',' +
					'"details":[' + @detalle_json + ']' + 
					'}'

/*--------------------------------------------------------------------------------------------------------------------------------*/

/*
SET @Retorno =	@Url_Envio 
				+ ';'
				+ @body_Json
*/

SET @Retorno =  @body_Json

RETURN @Retorno
END

--SELECT dbo.CF_BancoPagoTIC_API_AR_TX(Id_Trans, Importe, Id_Tar_Lote, '20220501') FROM Val_Tarjetas_Lotes_Items WHERE Id_Tar_Lote = 3

/*
RTA EJ:
{
	"currency_id":"ARS",
	"external_transaction_id":"00003-0000330824",
	"due_date":"2022-05-31T23:59:59-0300",
	"last_due_date":"2022-05-31T23:59:59-0300",
	"notification_url":"https://clubtesting.miclub.info/utils/pagotic_ipn_pagos.php",
	"details":[
					{
						"external_reference":"330824",
						"concept_id":"CS",
						"concept_description":"CUOTA SOCIAL",
						"amount":"1100.00"
					}
				]
}
*/
