
/****** Object:  StoredProcedure [dbo].[Estadisticas_Explotacion]    Script Date: 11/23/2012 13:07:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--execute Estadisticas_Explotacion '12/01/2002','04/22/2004',1
ALTER     PROCEDURE [dbo].[Estadisticas_Explotacion]  
	@desde    datetime ,
	@hasta    datetime ,
	@tasa     numeric(13,8) ,
	@orden    tinyint = 1   ,
	@id_agen  int  =   NULL ,
	@id_merc  int  =   NULL ,
	@id_cont  int  =   NULL ,
	@id_thab  int  =   NULL ,
	@id_uhab  int  =   NULL ,
	@directo  bit  =   NULL     
AS

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

begin
SET NOCOUNT ON
  
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) ) ,
       hospedaje  = COALESCE(ingxaloj    * @tasa,0)  ,
       ayb        = COALESCE(ingxpension * @tasa,0)  ,
       otros      = COALESCE(ingxotro    * @tasa,0)  ,
       noches     = 0,
       hab_noches = 0,
       adultos    = 0,
       ninos      = 0,
       ad_noche   = 0,
       ni_noche   = 0,
       coma.id_cont,
       cont.id_agen,
       cont.id_merc,
       uhab.id_thab,
       coma.id_uhab,
       agen.directo
Into       #Temp1
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 	       
--and
--( @id_cont is null or coma.id_cont = @id_cont) and
--( @id_agen is null or cont.id_agen = @id_agen) and
--( @id_merc is null or cont.id_merc = @id_merc) and 
--( @id_thab is null or uhab.id_thab = @id_thab) and
--( @id_uhab is null or coma.id_uhab = @id_uhab) and
--( @directo is null or agen.directo = @directo) 
        
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 ) ,
       hospedaje  = CASE WHEN  ing.tipo = 1 THEN importe * @tasa ELSE 0 END  ,
       ayb        = CASE WHEN  ing.tipo = 2 THEN importe * @tasa ELSE 0 END  ,
       otros      = 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,
       cont.id_agen,
       cont.id_merc,
       uhab.id_thab,
       coma.id_uhab,
       agen.directo
Into       #Temp2
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 
--and
--( @id_cont is null or coma.id_cont = @id_cont) and
--( @id_agen is null or cont.id_agen = @id_agen) and
--( @id_merc is null or cont.id_merc = @id_merc) and 
--( @id_thab is null or uhab.id_thab = @id_thab) and
--( @id_uhab is null or coma.id_uhab = @id_uhab) and
--( @directo is null or agen.directo = @directo) 

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) ),
       noches     = COALESCE(habi.h_ocupada,0) ,
       hab_noches = COALESCE(habi.h_entrada,0) ,
       adultos    = 0,
       ninos      = 0,
       ad_noche   = 0,
       ni_noche   = 0,
       coma.id_cont,
       cont.id_agen,
       cont.id_merc,
       uhab.id_thab,
       coma.id_uhab,
       agen.directo
INTO       #Temp3
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 
--and
--( @id_cont is null or coma.id_cont = @id_cont) and
--( @id_agen is null or cont.id_agen = @id_agen) and
--( @id_merc is null or cont.id_merc = @id_merc) and 
--( @id_thab is null or uhab.id_thab = @id_thab) and
--( @id_uhab is null or coma.id_uhab = @id_uhab) and
--( @directo is null or agen.directo = @directo) 
        
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) ),
       adultos    = COALESCE(paxs.a_entrada  ,0) ,
       ninos      = COALESCE(paxs.n_entrada  ,0) ,
       ad_noche   = COALESCE(paxs.a_estancia ,0) ,
       ni_noche   = COALESCE(paxs.n_estancia ,0) ,
       coma.id_cont,
       cont.id_agen,
       cont.id_merc,
       uhab.id_thab,
       coma.id_uhab,
       agen.directo
INTO #Temp4
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 
--and
--( @id_cont is null or coma.id_cont = @id_cont) and
--( @id_agen is null or cont.id_agen = @id_agen) and
--( @id_merc is null or cont.id_merc = @id_merc) and 
--( @id_thab is null or uhab.id_thab = @id_thab) and
--( @id_uhab is null or coma.id_uhab = @id_uhab) and
--( @directo is null or agen.directo = @directo) 
        
IF @id_cont IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE id_cont <> @id_cont
	DELETE FROM #Temp2 WHERE id_cont <> @id_cont
	DELETE FROM #Temp3 WHERE id_cont <> @id_cont
	DELETE FROM #Temp4 WHERE id_cont <> @id_cont
END

IF @id_agen IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE id_agen <> @id_agen
	DELETE FROM #Temp2 WHERE id_agen <> @id_agen
	DELETE FROM #Temp3 WHERE id_agen <> @id_agen
	DELETE FROM #Temp4 WHERE id_agen <> @id_agen
END

IF @id_merc IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE id_merc <> @id_merc
	DELETE FROM #Temp2 WHERE id_merc <> @id_merc
	DELETE FROM #Temp3 WHERE id_merc <> @id_merc
	DELETE FROM #Temp4 WHERE id_merc <> @id_merc
END

IF @id_thab IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE id_thab <> @id_thab
	DELETE FROM #Temp2 WHERE id_thab <> @id_thab
	DELETE FROM #Temp3 WHERE id_thab <> @id_thab
	DELETE FROM #Temp4 WHERE id_thab <> @id_thab
END

IF @id_uhab IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE id_uhab <> @id_uhab
	DELETE FROM #Temp2 WHERE id_uhab <> @id_uhab
	DELETE FROM #Temp3 WHERE id_uhab <> @id_uhab
	DELETE FROM #Temp4 WHERE id_uhab <> @id_uhab
END

IF @directo IS NOT NULL
BEGIN
	DELETE FROM #Temp1 WHERE directo <> @directo
	DELETE FROM #Temp2 WHERE directo <> @directo
	DELETE FROM #Temp3 WHERE directo <> @directo
	DELETE FROM #Temp4 WHERE directo <> @directo
END
        
SELECT contrato,
       desc_cont,
       agencia,
       desc_merc,
       uso_hab,
       desc_uso_hab,
       tipo_hab,
       desc_t_hab,
       hospedaje  = Sum( hospedaje ) ,
       ayb        = Sum( ayb ) ,
       otros      = Sum( otros ) ,
       noches     = 0,
       hab_noches = 0,
       adultos    = 0,
       ninos      = 0,
       ad_noche   = 0,
       ni_noche   = 0 
INTO #temp
FROM #Temp1
Group By contrato, desc_cont, agencia, desc_merc, uso_hab, desc_uso_hab, tipo_hab, desc_t_hab
Union All
SELECT contrato,
       desc_cont,
       agencia,
       desc_merc,
       uso_hab,
       desc_uso_hab,
       tipo_hab,
       desc_t_hab,
       hospedaje  = Sum( COALESCE(hospedaje,0) ) ,
       ayb        = Sum( COALESCE(ayb,0) ) ,
       otros      = Sum( COALESCE(otros,0) ) ,
       noches     = 0,
       hab_noches = 0,
       adultos    = 0,
       ninos      = 0,
       ad_noche   = 0,
       ni_noche   = 0 
FROM #Temp2
Group By contrato, desc_cont, agencia, desc_merc, uso_hab, desc_uso_hab, tipo_hab, desc_t_hab
Union All
SELECT contrato,
       desc_cont,
       agencia,
       desc_merc,
       uso_hab,
       desc_uso_hab,
       tipo_hab,
       desc_t_hab,
       hospedaje  = 0,
       ayb        = 0,
       otros      = 0,
       noches     = Sum ( noches ),
       hab_noches = Sum ( hab_noches ),
       adultos    = 0,
       ninos      = 0,
       ad_noche   = 0,
       ni_noche   = 0 
FROM #Temp3
Group By contrato, desc_cont, agencia, desc_merc, uso_hab, desc_uso_hab, tipo_hab, desc_t_hab
Union All
SELECT contrato,
       desc_cont,
       agencia,
       desc_merc,
       uso_hab,
       desc_uso_hab,
       tipo_hab,
       desc_t_hab,
       hospedaje  = 0,
       ayb        = 0,
       otros      = 0,
       noches     = 0,
       hab_noches = 0,
       adultos    = Sum ( adultos ),
       ninos      = Sum ( ninos ),
       ad_noche   = Sum ( ad_noche ),
       ni_noche   = Sum ( ni_noche )
FROM #Temp4
Group By contrato, desc_cont, agencia, desc_merc, uso_hab, desc_uso_hab, tipo_hab, desc_t_hab


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 )
From   #temp
Group By contrato, desc_cont, agencia, desc_merc, uso_hab, desc_uso_hab, tipo_hab, desc_t_hab
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

end












