USE GLPLUGINS
GO
SET DATEFORMAT YMD;

-- VER. 1.0.0.23
-- OBTENER EL TIQUETCAB SERIE 'F' O 'E', Y DESCARTAR LOS 'T' O 'X'

-- SP_PROCESARCERTIFICADOREGALOMINUTA
	DROP PROCEDURE IF EXISTS [dbo].[SP_PROCESARCERTIFICADOREGALOMINUTA]
	GO

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author:		EChevez
	-- Create date: 2022-08-08
	-- Update date: 2022-08-08
	-- Description:	Procesar certificados de regalo en ventas
	-- =============================================
	CREATE PROCEDURE [dbo].[SP_PROCESARCERTIFICADOREGALOMINUTA] 
		@IDMINUTA BIGINT, 
		@IDCERTIFICADOREGALO INT, 
		@IDTIPOTRANSACCION INT 
	AS
	BEGIN
		SET NOCOUNT ON;

		BEGIN TRY
			SET NOCOUNT ON;
			SET DATEFORMAT YMD;


			DECLARE @FO SMALLINT;
			DECLARE @SERIE VARCHAR(4);
			DECLARE @NUMERO INT;
			DECLARE @N VARCHAR(1);

			DECLARE @NUEVOIDMINUTA BIGINT;
			DECLARE @FECHAINI DATETIME;

			SET @FECHAINI = (SELECT FECHA FROM CERTIFICADOSREGALOMINUTAS WHERE IDMINUTA = @IDMINUTA AND IDCERTIFICADOREGALO = @IDCERTIFICADOREGALO AND IDTIPOTRANSACCION = @IDTIPOTRANSACCION);

			SELECT TOP 1 @FO = ISNULL(FO,0), 
			@SERIE = ISNULL(SERIE,''), 
			@NUMERO = ISNULL(NUMERO, 0), 
			@N = ISNULL(N, ''), 
			@NUEVOIDMINUTA = ISNULL(IDMINUTA, 0) 
			FROM ICGFRONTREST.DBO.TIQUETSCAB 
			WHERE (IDMINUTA = @IDMINUTA
			OR FECHAINI = @FECHAINI) 
			AND (SERIE LIKE 'F%'
			OR SERIE LIKE 'E%') 
			ORDER BY FECHAFIN DESC;

			UPDATE CERTIFICADOSREGALOMINUTAS 
			SET FO = @FO, 
			SERIE = @SERIE,
			NUMERO = @NUMERO, 
			N = @N, 
			PROCESADO = 1, 
			FECHAPROCESADO = GETDATE() 
			WHERE IDMINUTA = @IDMINUTA 
			AND IDCERTIFICADOREGALO = @IDCERTIFICADOREGALO 
			AND IDTIPOTRANSACCION = @IDTIPOTRANSACCION
			AND PROCESADO = 0;

			SELECT 1 AS RESULTADO;
		END TRY
		BEGIN CATCH
			--ROLLBACK TRAN
			SELECT 0 AS RESULTADO;
		END CATCH
	END
	GO
-- END SP_PROCESARCERTIFICADOREGALOMINUTA

-- SP_PROCESARMEMBRESIAMINUTA
	DROP PROCEDURE IF EXISTS [dbo].[SP_PROCESARMEMBRESIAMINUTA]
	GO

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author:		EChevez
	-- Create date: 2025-11-23
	-- Update date: 2025-11-23
	-- Description:	Procesar membresias en ventas
	-- =============================================
	CREATE PROCEDURE [dbo].[SP_PROCESARMEMBRESIAMINUTA] 
		@IDMINUTA BIGINT, 
		@IDCLIENTE INT,
		@MEMBRESIA VARCHAR(20)
	AS
	BEGIN
		SET NOCOUNT ON;

		BEGIN TRY
			SET NOCOUNT ON;
			SET DATEFORMAT YMD;


			DECLARE @FO SMALLINT;
			DECLARE @SERIE VARCHAR(4);
			DECLARE @NUMERO INT;
			DECLARE @N VARCHAR(1);

			DECLARE @IDSUCURSAL INT;
			DECLARE @IDSUCURSALCENTURIA INT;
			DECLARE @CODVENDEDOR INT;
			DECLARE @VENDEDOR VARCHAR(255);
			DECLARE @CAJAFRONT INT;
			DECLARE @CAJAMANAGER INT;

			DECLARE @NUEVOIDMINUTA BIGINT;
			DECLARE @FECHAINI DATETIME;

			SET @FECHAINI = (SELECT FECHA FROM MEMBRESIASMINUTAS WHERE IDMINUTA = @IDMINUTA AND IDCLIENTE = @IDCLIENTE);

			SELECT TOP 1 @FO = ISNULL(FO,0), 
			@SERIE = ISNULL(SERIE,''), 
			@NUMERO = ISNULL(NUMERO, 0), 
			@N = ISNULL(N, ''),
			@CODVENDEDOR = ISNULL(CODVENDEDOR, 0), 
			@CAJAFRONT = ISNULL(CAJA, 0),
			@NUEVOIDMINUTA = ISNULL(IDMINUTA, 0) 
			FROM ICGFRONTREST.DBO.TIQUETSCAB 
			WHERE (IDMINUTA = @IDMINUTA
			OR FECHAINI = @FECHAINI) 
			AND (SERIE LIKE 'F%'
			OR SERIE LIKE 'E%') 
			ORDER BY FECHAFIN DESC;

			-- OBTENER CAJAMGR
			SELECT @CAJAMANAGER = ISNULL(CAJAMANAGER, 0) 
			FROM [ICGFRONTREST].[DBO].[REM_CAJASMGR] 
			WHERE CAJAFRONT = @CAJAFRONT;

			-- OBTENER ID DE SUCURSAL PARA CLIENTE ESTRELLA (CENTURIA).
			SELECT @IDSUCURSAL = ISNULL(IDSUCURSAL, 0),
			@IDSUCURSALCENTURIA = ISNULL(IDCENTURIA, 0) 
			FROM SUCURSALES 
			WHERE FO = @FO;

			-- OBTENER NOMBRE DE VENDEDOR.
			SELECT @VENDEDOR = ISNULL(NOMBREVENDEDOR, '')
			FROM [ICGFRONTREST].[DBO].[VENDEDORES] 
			WHERE CODVENDEDOR = @CODVENDEDOR;

			DECLARE @CODFORMAPAGOLEALTAD INT;

			-- OBTERNER CODIGO DE FORMA DE PAGO LEALTAD
			SELECT @CODFORMAPAGOLEALTAD = CAST(VALOR AS INT) 
			FROM PARAMETROS 
			WHERE CLAVE = 'FDP_LEALTAD';

			DECLARE @MONTODINERO NUMERIC(12,2);
			DECLARE @MONTOPUNTOS NUMERIC(12,2);

			SELECT @MONTODINERO = ISNULL(SUM(IMPORTE + PROPINA), 0) 
				FROM ICGFRONTREST.DBO.TIQUETSPAG 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO = @CODFORMAPAGOLEALTAD;

			SELECT @MONTOPUNTOS = ISNULL(SUM(IMPORTE + PROPINA), 0) 
				FROM ICGFRONTREST.DBO.TIQUETSPAG 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO IN (SELECT CODFORMAPAGO FROM FORMASPAGOLEALTAD);

			IF @MONTOPUNTOS > 0
			BEGIN 
				INSERT INTO MEMBRESIASTRANSACCIONES 
				(
					IDCLIENTE, 
					MEMBRESIA, 
					IDTIPOTRANSACCION,
					IDSUCURSAL, 
					FO,
					SERIE, 
					NUMERO, 
					N, 
					TIPODOCUMENTO, 
					FECHACAJA,
					MONTO, 
					TOTAL, 
					FECHA,
					CODVENDEDOR, 
					VENDEDOR, 
					CAJA
				)
				SELECT 
				@IDCLIENTE AS IDCLIENTE,
				@MEMBRESIA AS MEMBRESIA, 
				1 AS IDTIPOTRANSACCION, 
				@IDSUCURSALCENTURIA AS IDSUCURSAL, 
				FO, 
				SERIE,
				NUMERO, 
				N, 
				SERIE AS TIPODOCUMENTO, 
				FECHA AS FECHACAJA, 
				@MONTOPUNTOS AS MONTO, 
				(TOTALNETO + PROPINA) AS TOTAL, 
				GETDATE() AS FECHA, 
				@CODVENDEDOR AS CODVENDEDOR,
				@VENDEDOR AS VENDEDOR, 
				@CAJAMANAGER AS CAJA 
				FROM [ICGFRONTREST].[DBO].[TIQUETSCAB] 
				WHERE FO = @FO AND SERIE = @SERIE AND NUMERO = @NUMERO AND N = @N;
			END

			-- SI HAY SALDO CANJEADO.
			IF @MONTODINERO > 0
			BEGIN

				INSERT INTO MEMBRESIASTRANSACCIONES 
				(
					IDCLIENTE, 
					MEMBRESIA, 
					IDTIPOTRANSACCION,
					IDSUCURSAL, 
					FO,
					SERIE, 
					NUMERO, 
					N, 
					TIPODOCUMENTO, 
					FECHACAJA, 
					MONTO,
					TOTAL, 
					FECHA,
					CODVENDEDOR, 
					VENDEDOR, 
					CAJA
				)
				SELECT 
				@IDCLIENTE AS IDCLIENTE,
				@MEMBRESIA AS MEMBRESIA, 
				2 AS IDTIPOTRANSACCION, 
				@IDSUCURSALCENTURIA AS IDSUCURSAL, 
				FO, 
				SERIE,
				NUMERO, 
				N, 
				SERIE AS TIPODOCUMENTO, 
				FECHA AS FECHACAJA, 
				@MONTODINERO AS MONTO, 
				(TOTALNETO + PROPINA) AS TOTAL, 
				GETDATE() AS FECHA, 
				@CODVENDEDOR AS CODVENDEDOR,
				@VENDEDOR AS VENDEDOR, 
				@CAJAMANAGER AS CAJA 
				FROM [ICGFRONTREST].[DBO].[TIQUETSCAB] 
				WHERE FO = @FO AND SERIE = @SERIE AND NUMERO = @NUMERO AND N = @N;
			END

			UPDATE MEMBRESIASMINUTAS 
			SET FO = @FO, 
			SERIE = @SERIE,
			NUMERO = @NUMERO, 
			N = @N, 
			MONTOPUNTOS = @MONTOPUNTOS, 
			MONTODINERO = @MONTODINERO,
			PROCESADO = 1, 
			FECHAPROCESADO = GETDATE() 
			WHERE IDMINUTA = @IDMINUTA 
			AND IDCLIENTE = @IDCLIENTE
			AND PROCESADO = 0;

			SELECT 1 AS RESULTADO;
		END TRY
		BEGIN CATCH
			--ROLLBACK TRAN
			PRINT(ERROR_MESSAGE());
			SELECT 0 AS RESULTADO;
		END CATCH
	END
	GO
-- END SP_PROCESARMEMBRESIAMINUTA

-- SP_PROCESAREMPLEADOMINUTA
	DROP PROCEDURE IF EXISTS [dbo].[SP_PROCESAREMPLEADOMINUTA]
	GO

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author:		EChevez
	-- Create date: 2022-08-08
	-- Update date: 2022-08-08
	-- Description:	Procesar certificados de regalo en ventas
	-- =============================================
	-- [dbo].[SP_PROCESAREMPLEADOMINUTA]  103545, 2374, 'R4Z9'
	CREATE PROCEDURE [dbo].[SP_PROCESAREMPLEADOMINUTA] 
		@IDMINUTA BIGINT, 
		@IDEMPLEADO INT, 
		@PIN VARCHAR(10)
	AS
	BEGIN
		SET NOCOUNT ON;

		BEGIN TRY
			SET NOCOUNT ON;
			SET DATEFORMAT YMD;


			DECLARE @FO SMALLINT;
			DECLARE @SERIE VARCHAR(4);
			DECLARE @NUMERO INT;
			DECLARE @N VARCHAR(1);
			DECLARE @FECHA DATETIME;

			DECLARE @IDSUCURSAL INT;
			DECLARE @CAJAFRONT INT;
			DECLARE @CAJAMANAGER INT;
			DECLARE @IDMOTIVODTO INT;

			DECLARE @NUEVOIDMINUTA BIGINT;
			DECLARE @FECHAINI DATETIME;

			SET @FECHAINI = (SELECT FECHA FROM EMPLEADOSMINUTAS WHERE IDMINUTA = @IDMINUTA AND IDEMPLEADO = @IDEMPLEADO);

			SELECT TOP 1 @FO = ISNULL(FO,0), 
			@SERIE = ISNULL(SERIE,''), 
			@NUMERO = ISNULL(NUMERO, 0), 
			@N = ISNULL(N, ''),
			@FECHA = FECHA, 
			@CAJAFRONT = CAJA, 
			@IDMOTIVODTO = IDMOTIVODTO 
			FROM ICGFRONTREST.DBO.TIQUETSCAB 
			WHERE (IDMINUTA = @IDMINUTA
			OR FECHAINI = @FECHAINI) 
			AND (SERIE LIKE 'F%'
			OR SERIE LIKE 'E%') 
			ORDER BY FECHAFIN DESC;

			-- OBTENER CAJAMGR
			SELECT @CAJAMANAGER = ISNULL(CAJAMANAGER, 0) 
			FROM [ICGFRONTREST].[DBO].[REM_CAJASMGR] 
			WHERE CAJAFRONT = @CAJAFRONT;

			-- OBTENER ID DE SUCURSAL
			SELECT @IDSUCURSAL = ISNULL(IDSUCURSAL, 0)
			FROM SUCURSALES 
			WHERE FO = @FO;

			DECLARE @CODFORMAPAGOCREDITOEMPLEADO INT;
			DECLARE @CODFORMAPAGOCUPONEMPLEADO INT;

			-- OBTERNER CODIGO DE FORMA DE PAGO CREDITO DE EMPLEADO
			SELECT @CODFORMAPAGOCREDITOEMPLEADO = CAST(VALOR AS INT) 
			FROM PARAMETROS 
			WHERE CLAVE = 'FDP_CREDITO_EMPLEADO';

			-- OBTERNER CODIGO DE FORMA DE PAGO CUPON DE EMPLEADO
			SELECT @CODFORMAPAGOCUPONEMPLEADO = CAST(VALOR AS INT) 
			FROM PARAMETROS 
			WHERE CLAVE = 'FDP_CUPON_EMPLEADO';

			DECLARE @SALDOCREDITO NUMERIC(12,2);
			DECLARE @SALDOCUPON NUMERIC(12,2);

			SELECT @SALDOCREDITO = ISNULL(SALDOCREDITO, 0), 
			@SALDOCUPON = ISNULL(SALDOCUPON, 0)
			FROM EMPLEADOSMINUTAS 
			WHERE IDMINUTA = @IDMINUTA 
			AND IDEMPLEADO = @IDEMPLEADO;

			DECLARE @MONTOCREDITO NUMERIC(12,2) = 0;
			DECLARE @MONTOCUPON NUMERIC(12,2) = 0;

			DECLARE @IMPORTECREDITO NUMERIC(12,2);
			DECLARE @PROPINACREDITO NUMERIC(12,2);

			SELECT @IMPORTECREDITO = ISNULL(SUM(IMPORTE), 0) 
				FROM ICGFRONTREST.DBO.TIQUETSPAG 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO = @CODFORMAPAGOCREDITOEMPLEADO;

			SELECT @PROPINACREDITO = ISNULL(SUM(PROPINA), 0) 
				FROM ICGFRONTREST.DBO.TIQUETSPAG 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO = @CODFORMAPAGOCREDITOEMPLEADO;

			SET @MONTOCREDITO = @IMPORTECREDITO + @PROPINACREDITO;

			IF @SALDOCUPON > 0
			BEGIN
				DECLARE @CUPONREQASIST BIT;

				SELECT @CUPONREQASIST = CAST(VALOR AS BIT) 
				FROM PARAMETROS 
				WHERE CLAVE = 'CPN_REQ_AST';

				DECLARE @MARCACION BIT;

				-- VERIFICAR SI EL EMPLEADO HA MARCADO ASISTENCIA
				EXEC @MARCACION = SP_VERIFICAREMPLEADOMARCACION @FECHA, @IDEMPLEADO;

				IF @CUPONREQASIST = 0 OR @MARCACION = 1
				BEGIN
					--SI SALDO CUPON ES MAYOR AL MONTO DE CREDITO
					IF @SALDOCUPON >= @MONTOCREDITO
					BEGIN
						-- PASO TODO EL MONTO DE CREDITO A CUPON
						SET @MONTOCUPON = @MONTOCREDITO;
					END

					-- SI SALDO CUPON ES MENOR AL MONTO DE CREDITO
					IF @SALDOCUPON < @MONTOCREDITO
					BEGIN
						-- IGUALO EL MONTO AL SALDO
						SET @MONTOCUPON = @SALDOCUPON;
					END
				END

				SET @MONTOCREDITO = ISNULL(@MONTOCREDITO - @MONTOCUPON, 0);
			END

			IF @MONTOCREDITO > 0 OR @MONTOCUPON > 0
			BEGIN
				INSERT INTO EMPLEADOSTRANSACCIONES
				(
					IDEMPLEADO,
					IDTIPOTRANSACCION,
					IDSUCURSAL,
					FO,
					SERIE,
					NUMERO,
					N,
					TIPODOCUMENTO,
					FECHACAJA,
					MONTOCREDITO,
					MONTOCUPON,
					TOTAL,
					PROCESADO,
					FECHA, 
					CAJA
				)
				SELECT 
				@IDEMPLEADO AS IDEMPLEADO,
				2 AS IDTIPOTRANSACCION,
				@IDSUCURSAL AS IDSUCURSAL,
				FO,
				SERIE,
				NUMERO,
				N,
				SERIE AS TIPODOCUMENTO,
				FECHA AS FECHACAJA,
				@MONTOCREDITO AS MONTOCREDITO,
				@MONTOCUPON AS MONTOCUPON,
				(TOTALNETO + PROPINA) AS TOTAL,
				0 AS  PROCESADO,
				GETDATE() AS FECHA, 
				@CAJAMANAGER CAJA 
				FROM [ICGFRONTREST].[DBO].[TIQUETSCAB] 
				WHERE FO = @FO AND SERIE = @SERIE AND NUMERO = @NUMERO AND N = @N;
			END

			-- VERIFICAR SI DEBO HACER CAMBIOS EN FORMAS DE PAGO
			-- SI TODO EL CREDITO PASA A SER CUPON, CAMBIO LA FORMA DE PAGO
			IF @MONTOCUPON > 0 AND @MONTOCREDITO = 0
			BEGIN
				UPDATE ICGFRONTREST.DBO.TIQUETSPAG 
				SET CODFORMAPAGO = @CODFORMAPAGOCUPONEMPLEADO 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO = @CODFORMAPAGOCREDITOEMPLEADO;
			END

			-- SI PARTE DEL CREDITO PASA A CUPON
			IF @MONTOCUPON > 0 AND @MONTOCREDITO > 0
			BEGIN
				UPDATE ICGFRONTREST.DBO.TIQUETSPAG 
				SET IMPORTE = @IMPORTECREDITO, 
				IMPORTE2 = @IMPORTECREDITO,
				PROPINA = ISNULL(@PROPINACREDITO, 0), 
				ENTREGADO = ISNULL(@MONTOCREDITO, 0)
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N 
				AND CODFORMAPAGO = @CODFORMAPAGOCREDITOEMPLEADO;

				DECLARE @NUMLINEA INT;

				SELECT @NUMLINEA = COUNT(FO) 
				FROM ICGFRONTREST.DBO.TIQUETSPAG 
				WHERE FO = @FO 
				AND SERIE = @SERIE 
				AND NUMERO = @NUMERO 
				AND N = @N;

				INSERT INTO ICGFRONTREST.DBO.TIQUETSPAG 
				(
					FO
					,SERIE
					,NUMERO
					,N
					,NUMLINEA
					,CODFORMAPAGO
					,CODMONEDA
					,IMPORTE
					,IMPORTE2
					,ENTREGADO
					,CAMBIO
					,CAMBIO2
					,PROPINA
					,BENEFICIO
					,PENDIENTE
					,FECHAVENCIM
					,AUTCODE
					,ESTADO
					,TIPOTARJETA
					,TITULARTARJETA
					,REDONDEO
					,NUMTRANSACCION
					,ACUENTAMNG
					,NUMTXNTEF
					,NUMRTSTEF
					,BINTARJETA
					,NUMENTREGACUENTA
					,TRANSACTIONDATA
				)
				VALUES
				(
					@FO
					,@SERIE
					,@NUMERO
					,@N
					,@NUMLINEA
					,@CODFORMAPAGOCUPONEMPLEADO
					,1
					,@MONTOCUPON
					,@MONTOCUPON
					,@MONTOCUPON
					,0
					,0
					,0
					,0
					,0
					,CAST(GETDATE() AS DATE)
					,''
					,0
					,''
					,''
					,0
					,0
					,''
					,''
					,''
					,''
					,-1
					,''
				);
			END

			-- OBTENER CODIGO DE DESCUENTO DE CUPON CUMPLEANERO
			DECLARE @CODMOTIVODTOCUPONCUMPLEANERO INT;

			SELECT @CODMOTIVODTOCUPONCUMPLEANERO = CAST(VALOR AS INT) 
			FROM PARAMETROS 
			WHERE CLAVE = 'MD_CUPON_CUMP';

			DECLARE @APLICATOKENEMPLEADO VARCHAR(5);

			SELECT @APLICATOKENEMPLEADO = ISNULL(VALOR, 'False') 
			FROM PARAMETROS 
			WHERE CLAVE = 'APL_TOK_EMPL';

			IF (@APLICATOKENEMPLEADO = 'True' AND @CODMOTIVODTOCUPONCUMPLEANERO = @IDMOTIVODTO) 
			BEGIN
				EXEC SP_INSERTARCUPONCUMPLEANERO @IDEMPLEADO, @FO, @SERIE, @NUMERO, @N;
			END

			UPDATE EMPLEADOSMINUTAS 
			SET FO = @FO, 
			SERIE = @SERIE,
			NUMERO = @NUMERO, 
			N = @N, 
			MONTOCREDITO = ISNULL(@MONTOCREDITO, 0),
			MONTOCUPON = ISNULL(@MONTOCUPON, 0),
			PROCESADO = 1, 
			FECHAPROCESADO = GETDATE()
			WHERE IDMINUTA = @IDMINUTA 
			AND IDEMPLEADO = @IDEMPLEADO
			AND PROCESADO = 0;

			SELECT 1 AS RESULTADO;
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			--DECLARE @ERRORMESSAGE NVARCHAR(4000);  
			--DECLARE @ERRORSEVERITY INT;  
			--DECLARE @ERRORSTATE INT;  
  
			--SELECT @ERRORMESSAGE = ERROR_MESSAGE(),  
			--	@ERRORSEVERITY = ERROR_SEVERITY(),  
			--	@ERRORSTATE = ERROR_STATE();  
  
			---- Use RAISERROR inside the CATCH block to return error  
			---- information about the original error that caused  
			---- execution to jump to the CATCH block.  
			--RAISERROR (@ERRORMESSAGE, -- Message text.  
			--		   @ERRORSEVERITY, -- Severity.  
			--		   @ERRORSTATE); -- State.  
			SELECT 0 AS RESULTADO;
		END CATCH
	END
	GO
-- END SP_PROCESAREMPLEADOMINUTA