SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'DSS_FRONT_Explotacion') )
DROP Procedure DSS_FRONT_Explotacion
GO

CREATE PROCEDURE DSS_FRONT_Explotacion  
	@desde        DATETIME,
	@hasta        DATETIME,
	@orden        TINYINT = 1,
	@id_mone      INT,
	@tasa         NUMERIC(13, 8),
	@TipoInforme  VARCHAR(100),
	@Fil_id_agen  BIT = 0,
	@vFil_id_agen INT = 0,
	@Fil_id_merc  BIT = 0,
	@vFil_id_merc INT = 0,
	@Fil_id_cont  BIT = 0,
	@vFil_id_cont INT = 0,
	@Fil_id_thab  BIT = 0,
	@vFil_id_thab INT = 0,
	@Fil_id_uhab  BIT = 0,
	@vFil_id_uhab INT = 0,
	@Fil_tipo     BIT = 0,
	@vFil_tipo    BIT = 0
AS

/*   PROC QUE RETORNA EL CURSOR CON LA INFORMACION NECESARIA PARA EL LISTADO DE ESTADISTICA DE EXPLOTACION */

BEGIN

	DECLARE @Fil VARCHAR(1000) = '' 

	SET NOCOUNT ON
	
	-- SET @Tasa = (1 / CASE @Tasa WHEN 0 THEN 1 ELSE @Tasa END)
	
	-- General
	
	CREATE TABLE #EstaGeneral
				(capacidad     INT,
				 capacidad_for INT,
				 plazas        INT,
				 plazas_for    INT	)
	
	INSERT INTO #EstaGeneral
	EXEC   Estadisticas_Explotacion_Gene @desde, @hasta
	
	-- NOEX ?
	
	CREATE TABLE #EstaNoex
				(id_noex   INT,
				 desc_noex VARCHAR(40),
				 importe   NUMERIC(21, 6))
	
	INSERT INTO #EstaNoex
	EXEC   Estadisticas_Explotacion_Ext @desde, @hasta
	
	-- SELECT * FROM #EstaNoex
	
	-- Otros
	
	CREATE TABLE #EstaOtros
				(otros_front NUMERIC(21, 6),
				 otros_pv    NUMERIC(21, 6)	)
	
	INSERT INTO #EstaOtros
	EXEC   Estadisticas_Explotacion_Otros @desde, @hasta, @id_mone, @tasa
	  
	SELECT cont.contrato, -- Ingresos Extras (Alojamiento y Otros)
		   cont.desc_cont,
		   agen.agencia,
		   merc.desc_merc,
		   uhab.uso_hab,
		   uhab.desc_uso_hab,
		   thab.tipo_hab,
		   thab.desc_t_hab,
		   hospedaje  = SUM(COALESCE(ingxaloj    * @tasa,0)),
		   ayb        = SUM(COALESCE(ingxpension * @tasa,0)),
		   otros      = SUM(COALESCE(ingxotro    * @tasa,0)),
		   noches     = 0,
		   hab_noches = 0,
		   adultos    = 0,
		   ninos      = 0,
		   ad_noche   = 0,
		   ni_noche   = 0,
		   coma.id_cont,
		   coma.id_naci,
		   coma.id_ralo,
		   coma.id_uhab,
		   thab.id_thab,
		   agen.id_agen,
		   agen.directo,
		   merc.id_merc,
		   dias.fecha
	INTO #temp
	FROM       estaxing ing
		INNER JOIN Estadias dias  ON ing.id_dias  = dias.id_dias
		INNER JOIN Estacoma coma  ON ing.id_coma  = coma.id_coma
		INNER JOIN Hoteuhab uhab  ON coma.id_uhab = uhab.id_uhab
		INNER JOIN Hotethab thab  ON uhab.id_thab = thab.id_thab
		INNER JOIN Hotecont cont  ON coma.id_cont = cont.id_cont
		INNER JOIN Hoteagen agen  ON cont.id_agen = agen.id_agen
		INNER JOIN Siclamon mone  ON ing.id_mone  = mone.id_mone
		INNER JOIN Hotemerc merc  ON cont.id_merc = merc.id_merc
	WHERE DATEDIFF(DAY, @desde, dias.fecha) >= 0 
	  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0
	GROUP BY cont.contrato, cont.desc_cont, 
	         agen.agencia,  merc.desc_merc, 
			 uhab.uso_hab,  uhab.desc_uso_hab,
			 thab.tipo_hab, thab.desc_t_hab,
			 coma.id_cont, coma.id_naci,
		     coma.id_ralo, coma.id_uhab,
		     thab.id_thab, agen.id_agen, agen.directo,
		     merc.id_merc, dias.fecha
	UNION ALL
	SELECT cont.contrato, -- Ingresos Agencia (Aloj, AyB y Otros)
		   cont.desc_cont,
		   agen.agencia,
		   merc.desc_merc,
		   uhab.uso_hab,
		   uhab.desc_uso_hab,
		   thab.tipo_hab,
		   thab.desc_t_hab,
		   hospedaje  = SUM(CASE WHEN  ing.tipo = 1 THEN importe * @tasa ELSE 0 END),
		   ayb        = SUM(CASE WHEN  ing.tipo = 2 THEN importe * @tasa ELSE 0 END),
		   otros      = SUM(CASE WHEN  ing.tipo = 3 THEN importe * @tasa ELSE 0 END),
		   noches     = 0,
		   hab_noches = 0,
		   adultos    = 0,
		   ninos      = 0,
		   ad_noche   = 0,
		   ni_noche   = 0,
		   coma.id_cont,
		   coma.id_naci,
		   coma.id_ralo,
		   coma.id_uhab,
		   thab.id_thab,
		   agen.id_agen,
		   agen.directo,
		   merc.id_merc,
		   dias.fecha
	FROM   estaaing ing
			INNER JOIN Estadias dias  ON ing.id_dias  = dias.id_dias
			INNER JOIN Estacoma coma  ON ing.id_coma  = coma.id_coma
			INNER JOIN Hoteuhab uhab  ON coma.id_uhab = uhab.id_uhab
			INNER JOIN Hotethab thab  ON uhab.id_thab = thab.id_thab
			INNER JOIN Hotecont cont  ON coma.id_cont = cont.id_cont
			INNER JOIN Hoteagen agen  ON cont.id_agen = agen.id_agen
			INNER JOIN Hotemerc merc  ON cont.id_merc = merc.id_merc
			INNER JOIN Siclamon mone  ON ing.id_mone  = mone.id_mone
	WHERE DATEDIFF(DAY, @desde, dias.fecha) >= 0 
	  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0
	GROUP BY cont.contrato, cont.desc_cont,
	         agen.agencia,  merc.desc_merc, 
			 uhab.uso_hab,  uhab.desc_uso_hab,
			 thab.tipo_hab, thab.desc_t_hab,
			 coma.id_cont, coma.id_naci,
		     coma.id_ralo, coma.id_uhab,
		     thab.id_thab, agen.id_agen, agen.directo,
		     merc.id_merc, dias.fecha
	UNION ALL
	SELECT cont.contrato,  -- Noches y Entradas
		   cont.desc_cont,
		   agen.agencia,
		   merc.desc_merc,
		   uhab.uso_hab,
		   uhab.desc_uso_hab,
		   thab.tipo_hab,
		   thab.desc_t_hab,
		   hospedaje  = 0,
		   ayb        = 0,
		   otros      = 0,
		   noches     = SUM(COALESCE(habi.h_ocupada,0)),
		   hab_noches = SUM(COALESCE(habi.h_entrada,0)),
		   adultos    = 0,
		   ninos      = 0,
		   ad_noche   = 0,
		   ni_noche   = 0,
		   coma.id_cont,
		   coma.id_naci,
		   coma.id_ralo,
		   coma.id_uhab,
		   thab.id_thab,
		   agen.id_agen,
		   agen.directo,
		   merc.id_merc,
		   dias.fecha
	FROM ESTAALOH       habi
		INNER JOIN Estadias dias  ON habi.id_dias = dias.id_dias
		INNER JOIN Estacoma coma  ON habi.id_coma = coma.id_coma
		INNER JOIN Hoteuhab uhab  ON coma.id_uhab = uhab.id_uhab
		INNER JOIN Hotethab thab  ON uhab.id_thab = thab.id_thab
		INNER JOIN Hotecont cont  ON coma.id_cont = cont.id_cont
		INNER JOIN Hoteagen agen  ON cont.id_agen = agen.id_agen
		INNER JOIN Hotemerc merc  ON cont.id_merc = merc.id_merc
		CROSS Join Hotetabl tabl
		INNER JOIN Siclamon mone ON tabl.id_mone  = mone.id_mone
	WHERE DATEDIFF(DAY, @desde, dias.fecha) >= 0 
	  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0
	GROUP BY cont.contrato, cont.desc_cont,
	         agen.agencia,  merc.desc_merc, 
			 uhab.uso_hab,  uhab.desc_uso_hab,
			 thab.tipo_hab, thab.desc_t_hab,
			 coma.id_cont, coma.id_naci,
		     coma.id_ralo, coma.id_uhab,
		     thab.id_thab, agen.id_agen, agen.directo,
		     merc.id_merc, dias.fecha
	UNION ALL
	SELECT cont.contrato, -- Pax de Entrada y Pax por Noches
		   cont.desc_cont,
		   agen.agencia,
		   merc.desc_merc,
		   uhab.uso_hab,
		   uhab.desc_uso_hab,
		   thab.tipo_hab,
		   thab.desc_t_hab,
		   hospedaje  = 0,
		   ayb        = 0,
		   otros      = 0,
		   noches     = 0,
		   hab_noches = 0,
		   adultos    = SUM(COALESCE(paxs.a_entrada  ,0)),
		   ninos      = SUM(COALESCE(paxs.n_entrada  ,0)),
		   ad_noche   = SUM(COALESCE(paxs.a_estancia ,0)),
		   ni_noche   = SUM(COALESCE(paxs.n_estancia ,0)),
		   coma.id_cont,
		   coma.id_naci,
		   coma.id_ralo,
		   coma.id_uhab,
		   thab.id_thab,
		   agen.id_agen,
		   agen.directo,
		   merc.id_merc,
		   dias.fecha
	FROM   ESTAALOP paxs
		INNER JOIN Estadias dias  ON paxs.id_dias = dias.id_dias
		INNER JOIN Estacoma coma  ON paxs.id_coma = coma.id_coma
		INNER JOIN Hoteuhab uhab  ON coma.id_uhab = uhab.id_uhab
		INNER JOIN Hotethab thab  ON uhab.id_thab = thab.id_thab
		INNER JOIN Hotecont cont  ON coma.id_cont = cont.id_cont
		INNER JOIN Hoteagen agen  ON cont.id_agen = agen.id_agen
		INNER JOIN Hotemerc merc  ON cont.id_merc = merc.id_merc
		CROSS Join Hotetabl tabl
		INNER JOIN Siclamon mone ON tabl.id_mone  = mone.id_mone
	WHERE DATEDIFF(DAY, @desde, dias.fecha) >= 0 
	  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0
	GROUP BY cont.contrato, cont.desc_cont,    
	         agen.agencia,  merc.desc_merc, 
			 uhab.uso_hab,  uhab.desc_uso_hab,
			 thab.tipo_hab, thab.desc_t_hab,
			 coma.id_cont, coma.id_naci,
		     coma.id_ralo, coma.id_uhab,
		     thab.id_thab, agen.id_agen, agen.directo,
		     merc.id_merc, dias.fecha
		     
	IF @Fil_id_agen = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblGENAgencia: ' + ISNULL((SELECT LTRIM(RTRIM(agencia)) FROM HOTEAGEN WHERE id_agen = @vFil_id_agen), '')
	
	    DELETE #temp WHERE id_agen <> @vFil_id_agen  
	END
	IF @Fil_id_merc = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblFDKSMercado: ' + ISNULL((SELECT LTRIM(RTRIM(desc_merc)) FROM HOTEMERC WHERE id_merc = @vFil_id_merc), '')
	
	    DELETE #temp WHERE id_merc <> @vFil_id_merc  
	END
	IF @Fil_id_cont = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblGENTarifa: ' + ISNULL((SELECT LTRIM(RTRIM(contrato)) FROM HOTECONT WHERE id_cont = @vFil_id_cont), '')
	
	    DELETE #temp WHERE id_cont <> @vFil_id_cont  
	END
	IF @Fil_id_thab = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblGENRoomType: ' + ISNULL((SELECT LTRIM(RTRIM(tipo_hab)) FROM HOTETHAB WHERE id_thab = @vFil_id_thab), '')
	
	    DELETE #temp WHERE id_thab <> @vFil_id_thab  
	END
	IF @Fil_id_uhab = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblGENUsoHab: ' + ISNULL((SELECT LTRIM(RTRIM(uso_hab)) FROM HOTEUHAB WHERE id_uhab = @vFil_id_uhab), '')
	
	    DELETE #temp WHERE id_uhab <> @vFil_id_uhab  
	END
	IF @Fil_tipo = 1 
	BEGIN 
	
		IF LEN(LTRIM(RTRIM(@Fil))) > 0
		BEGIN
			SET @Fil = @Fil + ', '
		END
		
		SET @Fil = @Fil + 'LblGENType: ' + CASE @vFil_tipo WHEN 1 THEN 'LblFDKDirectos' ELSE 'LblGENEmpresas' END
	
	    DELETE #temp WHERE directo <> @vFil_tipo  
	END	     
	
			 
	DECLARE @Moneda CHAR(18) = (SELECT desc_mone 
	                               FROM IHSecurity..SICLAMON 
	                               WHERE id_mone = @id_mone)
	                               
	DECLARE @capacidad INT = (SELECT capacidad FROM #EstaGeneral) 
	DECLARE @capacidad_for INT = (SELECT capacidad_for FROM #EstaGeneral) 
	DECLARE @plazas INT = (SELECT plazas FROM #EstaGeneral) 
	DECLARE @plazas_for INT = (SELECT plazas_for FROM #EstaGeneral) 
	DECLARE @otros_front NUMERIC(21, 6) = (SELECT otros_front FROM #EstaOtros)   
	DECLARE @otros_pv NUMERIC(21, 6) = (SELECT otros_pv FROM #EstaOtros)  

	SELECT contrato,
		   desc_cont,
		   agencia,
		   desc_merc,
		   uso_hab,
		   desc_uso_hab,
		   tipo_hab,
		   desc_t_hab,
		   hospedaje  = ROUND(SUM(hospedaje), 2),
		   ayb        = ROUND(SUM(ayb), 2),
		   otros      = ROUND(SUM(otros), 2),
		   noches     = SUM(noches),
		   hab_noches = SUM(hab_noches),
		   adultos    = SUM(adultos),
		   ninos      = SUM(ninos),
		   ad_noche   = SUM(ad_noche),
		   ni_noche   = SUM(ni_noche),
		   fecha,
		   CASE @orden
				WHEN 1  THEN 'Agencia'
				WHEN 2  THEN 'Agencia'
				WHEN 3  THEN 'Agencia'
				WHEN 4  THEN 'Agencia'
				WHEN 5  THEN 'Mercado'
				WHEN 6  THEN 'Mercado'
				WHEN 7  THEN 'Mercado'
				WHEN 8  THEN 'Mercado'
				WHEN 9  THEN 'Mercado'
				WHEN 10 THEN 'Contrato'
				WHEN 11 THEN 'Contrato'
				WHEN 12 THEN 'Contrato'
				WHEN 13 THEN 'Tipo de Habitación'
				WHEN 14 THEN 'Tipo de Habitación'
				WHEN 15 THEN 'Tipo de Habitación'
				WHEN 16 THEN 'Tipo de Habitación'
				WHEN 17 THEN 'Tipo de Habitación'
				END  AS Agrupador,
		   CASE @orden
				WHEN 1  THEN agencia
				WHEN 2  THEN agencia
				WHEN 3  THEN agencia
				WHEN 4  THEN agencia
				WHEN 5  THEN desc_merc
				WHEN 6  THEN desc_merc
				WHEN 7  THEN desc_merc
				WHEN 8  THEN desc_merc
				WHEN 9  THEN desc_merc
				WHEN 10 THEN desc_cont
				WHEN 11 THEN desc_cont
				WHEN 12 THEN desc_cont
				WHEN 13 THEN desc_t_hab
				WHEN 14 THEN desc_t_hab
				WHEN 15 THEN desc_t_hab
				WHEN 16 THEN desc_t_hab
				WHEN 17 THEN desc_t_hab
				END  AS AgrupadorVal,
		  CASE @orden
				WHEN 1  THEN 'Contrato'
				WHEN 2  THEN 'Contrato'
				WHEN 3  THEN 'Tipo de Habitación'
				WHEN 4  THEN 'Uso de Habitación'
				WHEN 5  THEN 'Agencia'
				WHEN 6  THEN 'Agencia'
				WHEN 7  THEN 'Contrato'
				WHEN 8  THEN 'Tipo de Habitación'
				WHEN 9  THEN 'Uso de Habitación'
				WHEN 10 THEN 'Tipo de Habitación'
				WHEN 11 THEN 'Tipo de Habitación'
				WHEN 12 THEN 'Uso de Habitación'
				WHEN 13 THEN 'Agencia'
				WHEN 14 THEN 'Agencia'
				WHEN 15 THEN 'Contrato'
				WHEN 16 THEN 'Mercado'
				WHEN 17 THEN 'Uso de Habitación'
				END AS Detalle,
		   CASE @orden
				WHEN 1  THEN desc_cont
				WHEN 2  THEN desc_cont
				WHEN 3  THEN desc_t_hab
				WHEN 4  THEN desc_uso_hab
				WHEN 5  THEN agencia
				WHEN 6  THEN agencia
				WHEN 7  THEN desc_cont
				WHEN 8  THEN desc_t_hab
				WHEN 9  THEN desc_uso_hab
				WHEN 10 THEN desc_t_hab
				WHEN 11 THEN desc_t_hab
				WHEN 12 THEN desc_uso_hab
				WHEN 13 THEN agencia
				WHEN 14 THEN agencia
				WHEN 15 THEN desc_cont
				WHEN 16 THEN desc_merc
				WHEN 17 THEN desc_uso_hab
				END AS DetalleVal,
				@desde AS FecIni,
				@hasta AS FecFin,
				@capacidad AS capacidad,
				@capacidad_for AS capacidad_for,
				@plazas AS plazas,
				@plazas_for AS plazas_for,
				@otros_front AS otros_front,
				@otros_pv AS otros_pv,
		   @Moneda AS Moneda,
		   CASE SUM(noches)
			    WHEN 0 
			    THEN 0
			    ELSE ROUND(SUM(hospedaje + ayb + otros), 2) / SUM(noches)
			    END AS TarifaProm,
		   @TipoInforme AS TipoInforme,
		   CASE @orden
				WHEN 1  THEN 1
				WHEN 5  THEN 1
				WHEN 10 THEN 1
				WHEN 13 THEN 1
				ELSE 0
				END  AS OcultarDetalle
	INTO #temp2
	FROM #temp
	GROUP BY contrato, desc_cont, 
	         agencia,desc_merc, uso_hab, 
			 desc_uso_hab, tipo_hab, desc_t_hab, fecha
			 
	DECLARE @tot_hosp NUMERIC(18, 2) = (SELECT SUM(hospedaje) FROM #temp2)
	DECLARE @tot_ayb NUMERIC(18, 2) = (SELECT SUM(ayb) FROM #temp2)
	DECLARE @tot_otros NUMERIC(18, 2) = (SELECT SUM(otros) FROM #temp2)
	DECLARE @tot_noches NUMERIC(18, 2) = (SELECT SUM(noches) FROM #temp2)
	DECLARE @tot_ad_noches NUMERIC(18, 2) = (SELECT SUM(ad_noche + ni_noche) FROM #temp2)
	
	IF @tot_hosp = 0 SET @tot_hosp = 1
	IF @tot_ayb = 0 SET @tot_ayb = 1
	IF @tot_otros = 0 SET @tot_otros = 1
	IF @tot_noches = 0 SET @tot_noches = 1
	IF @tot_ad_noches = 0 SET @tot_ad_noches = 1
			 
	SELECT *,
	       hospedaje * (1 / @tot_hosp) * 100 hosp_porc,
	       ayb * (1 / @tot_ayb) * 100 ayb_porc,
	       otros * (1 / @tot_otros) * 100 otros_porc,
	       noches * (1 / @tot_noches) * 100 noches_porc,
	       (ad_noche + ni_noche) * (1 / @tot_ad_noches) * 100 ad_noches_porc
	FROM   #temp2
	ORDER BY  CASE  @orden
					WHEN 1  THEN agencia
					WHEN 2  THEN agencia
					WHEN 3  THEN agencia
					WHEN 4  THEN agencia
					WHEN 5  THEN desc_merc
					WHEN 6  THEN desc_merc
					WHEN 7  THEN desc_merc
					WHEN 8  THEN desc_merc
					WHEN 9  THEN desc_merc
					WHEN 10 THEN desc_cont
					WHEN 11 THEN desc_cont
					WHEN 12 THEN desc_cont
					WHEN 13 THEN desc_t_hab
					WHEN 14 THEN desc_t_hab
					WHEN 15 THEN desc_t_hab
					WHEN 16 THEN desc_t_hab
					WHEN 17 THEN desc_t_hab
					END,
			  CASE  @orden
					WHEN 1  THEN desc_cont
					WHEN 2  THEN desc_cont
					WHEN 3  THEN desc_t_hab
					WHEN 4  THEN desc_uso_hab
					WHEN 5  THEN agencia
					WHEN 6  THEN agencia
					WHEN 7  THEN desc_cont
					WHEN 8  THEN desc_t_hab
					WHEN 9  THEN desc_uso_hab
					WHEN 10 THEN desc_t_hab
					WHEN 11 THEN desc_t_hab
					WHEN 12 THEN desc_uso_hab
					WHEN 13 THEN agencia
					WHEN 14 THEN agencia
					WHEN 15 THEN desc_cont
					WHEN 16 THEN desc_merc
					WHEN 17 THEN desc_uso_hab
					END
					
	SELECT @Fil Filtro
	
	RETURN

END










