IF  EXISTS (SELECT * FROM sys.objects 
                     WHERE object_id = OBJECT_ID('Calculo_Estad_Explotacion') 
                     AND type in ('P'))
                     
DROP Procedure Calculo_Estad_Explotacion
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

Create PROCEDURE Calculo_Estad_Explotacion
                 @Desde datetime,@Hasta datetime,
                 @Agencia nvarchar(25),
                 @Contrato nvarchar(12),
                 @TipoHab nvarchar(12),
                 @UsoHab nvarchar(12),
                 @directo  bit  =   NULL,
                 @Opcion tinyint,
                 @Resultado numeric(12,2) output
				 --@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
/*  Hecho por: Luis Abarca
	Fecha : 22 Agosto 2012
	Detalle : Obtiene las Estadistica de Explotacion, por contrato
	          [Para el SevenExcel]
	Opcion:
		1-	Ingreso Hospedaje
		2-	Ingreso AyB 
		3-	Ingreso Otros
		4-	Habitacion Estancias Hab_Noches 
		5-	Clientes Estancias   Ad_Noches     
		6-  Tarifa Promedio    

*/

Begin
SET NOCOUNT ON
Declare @Tasa  numeric(13,8)=1,@orden tinyint = 1   ,
	    @id_agen  int  =   NULL ,
	    @id_merc  int  =   NULL ,
	    @id_cont  int  =   NULL ,
		@id_thab  int  =   NULL ,
		@id_uhab  int  =   NULL

Select @id_agen = id_agen From HOTEAGEN Where agencia = @Agencia
Select @id_cont = id_cont From HOTECONT Where contrato = @Contrato
Select @id_thab = id_thab From HOTETHAB Where tipo_hab = @TipoHab
Select @id_uhab = id_uhab From HOTEUHAB Where uso_hab =  @UsoHab


  -- Ingresos Extras (Alojamiento y Otros)
Select cont.contrato,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
 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 	       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) 
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
Union All
-- Ingresos Agencia (Aloj, AyB y Otros)
Select cont.contrato, 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
  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) 
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
Union All
-- Noches y Entradas
Select cont.contrato, 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
  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) 
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
Union All
/* Pax de Entrada y Pax por Noches */
/* =============================== */
Select cont.contrato, 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) )
  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) 
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


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 )
  Into  #TempTotales
   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

--select * from  #TempTotales

If @Opcion = 1 Select @Resultado = SUM(Hospedaje)  From #TempTotales
If @Opcion = 2 Select @Resultado = SUM(AyB)        From #TempTotales
If @Opcion = 3 Select @Resultado = SUM(Otros)      From #TempTotales
If @Opcion = 4 Select @Resultado = SUM(Hab_Noches) From #TempTotales
If @Opcion = 5 Select @Resultado = SUM(Ad_Noches)  From #TempTotales
If @Opcion = 6 Select @Resultado = round((SUM(Hospedaje)+ SUM(AyB)) / SUM(Hab_Noches),2) From #TempTotales


Set @Resultado = ISNULL(@Resultado,0)

Return @Resultado


End












