DROP PROCEDURE IF EXISTS [dbo].[SP_UPSERTEMPLEADO]
	GO

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author:		EChevez
	-- Create date: 2021-02-19
	-- Update date: 2025-08-11
	-- Description:	Inserta/Actualiza empleados.
	-- =============================================
	CREATE PROC [dbo].[SP_UPSERTEMPLEADO] 
		@T_Empleado as dbo.TYPE_Empleado READONLY,
		@T_EmpleadoBeneficio as dbo.TYPE_EmpleadoBeneficio READONLY
	AS
	BEGIN
	
		SET LANGUAGE SPANISH;
		SET DATEFORMAT YMD;
		DECLARE @CADUCIDAD DATETIME;
		SET @CADUCIDAD = CAST('2050-12-31' as DATETIME);


	SELECT @CADUCIDAD
		

		MERGE INTO [ICGFRONTREST].[dbo].[TARJETAS] AS T 
		USING (SELECT  *, ROW_NUMBER() OVER(ORDER BY IDEMPLEADO) AS INC FROM @T_Empleado) AS TM 
		ON T.IDTARJETA = TM.IDEMPLEADO
		WHEN MATCHED THEN
			UPDATE SET 
			T.VALIDA = CASE WHEN TM.ACTIVO = 1 THEN 'T' ELSE 'F' END,
			T.ALIAS = TM.BANDA 
		WHEN NOT MATCHED THEN
			INSERT (
				IDTARJETA,
				CODCLIENTE,
				POSICION,
				IDTIPOTARJETA,
				DESCRIPCION,
				CADUCIDAD,
				VALIDA,
				SALDOTARJETA,
				ENTREGADA,
				OBSERVACIONES,
				ALIAS) 
			VALUES (
				--(SELECT (ISNULL(MAX(IDTARJETA), 0) + TM.INC) AS IDTARJETA FROM ICGFRONTREST.DBO.TARJETAS),
				TM.IDEMPLEADO,
				0,
				1,
				1,
				TM.IDEMPLEADO, 
				@CADUCIDAD,
				CASE WHEN TM.ACTIVO = 1 THEN 'T' ELSE 'F' END,
				0,
				'T',
				UPPER(CONCAT(TM.APELLIDOS, ', ', TM.NOMBRES)),
				TM.BANDA
			);
		


		MERGE INTO EMPLEADOS as E
		USING (SELECT E.*, ISNULL(T.IDTARJETA, 0) AS NUEVOIDTARJETA FROM @T_Empleado as E 
		LEFT JOIN [ICGFRONTREST].[DBO].[TARJETAS] AS T 
		ON E.IDEMPLEADO = T.IDTARJETA) AS TE
		ON TE.IdEmpleado = E.IdEmpleado
		WHEN MATCHED THEN 
		UPDATE SET
			LimiteCredito = TE.LimiteCredito,
			MontoCredito = TE.MontoCredito,
			LimiteCupon	 =  TE.LimiteCupon,
			MontoCupon = TE.MontoCupon,
			Activo = TE.Activo,
			Banda = TE.Banda
		WHEN NOT MATCHED THEN
		INSERT (
			IdEmpleado,
			Pin,
			IdTarjeta,
			Banda,
			Nombres,
			Apellidos,
			Dui,
			Nit,
			Domicilio,
			Telefono,
			Celular,
			Email,
			Imagen,
			LimiteCredito,
			MontoCredito,
			LimiteCupon,
			MontoCupon,
			Activo
		)VALUES (
			TE.IdEmpleado,
			TE.Pin,
			TE.NUEVOIDTARJETA,
			TE.Banda,
			TE.Nombres,
			TE.Apellidos,
			TE.Dui,
			TE.Nit,
			TE.Domicilio,
			TE.Telefono,
			TE.Celular,
			TE.Email,
			TE.Imagen,
			TE.LimiteCredito,
			TE.MontoCredito,
			TE.LimiteCupon,
			TE.MontoCupon,
			TE.Activo);

			--ELIMINAR Y REINGRESAR DATOS

			DELETE FROM EmpleadosBeneficios WHERE 
			IdEmpleado IN (SELECT IdEmpleado FROM @T_Empleado);


			INSERT INTO EmpleadosBeneficios (IdEmpleado, IdMarca, LimiteCupon)
			SELECT IdEmpleado, IdMarca, LimiteCupon FROM @T_EmpleadoBeneficio
	END
	GO
-- END SP_UPSERTEMPLEADO