SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
==============================================================
-- Autor:       Francis Antonio Bolaños Arias
-- Fecha:       26/09/2012 09:24:30
-- Descripción: Procedimiento que obtiene la información
--              para generar el informe de Reservas / Rooming
--              Realizados, para DSS
-- ===========================================================
*/

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


CREATE PROCEDURE DSS_FRONT_ReseRoomRealizadas
                 (@vAno INT)
AS
BEGIN 

	CREATE TABLE #TotReseRoom
				(IdOperador  INT,
				Operador    VARCHAR(30),
				Descripcion VARCHAR(50),
				Enero       INT,
				Febrero     INT,
				Marzo       INT,
				Abril       INT,
				Mayo        INT,
				Junio       INT,
				Julio       INT,
				Agosto      INT,
				Septiembre  INT,
				Octubre     INT,
				Noviembre   INT,
				Diciembre   INT	)
	
	SELECT op.id_oper,
	       op.nombre
	INTO   #Operadores
	FROM   IHSecurity..Operador op
	WHERE  op.activo = 1 
	
	SET IDENTITY_INSERT #Operadores ON
	
	INSERT INTO #Operadores
	       (id_oper,
		    nombre)
	  VALUES
	      (0,'Seven Online')
	
	SET IDENTITY_INSERT #Operadores OFF
	
	INSERT INTO #TotReseRoom
	SELECT op.id_oper AS IdOperador,
	       op.nombre  AS Operador,
	       'Reservas' AS Descripcion,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 1
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 1
				AND YEAR (fecha_reserv)  = @vAno) AS Enero,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 2
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 2
				AND YEAR (fecha_reserv)  = @vAno) AS Febrero,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 3
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 3
				AND YEAR (fecha_reserv)  = @vAno) AS Marzo,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 4
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 4
				AND YEAR (fecha_reserv)  = @vAno) AS Abril,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 5
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 5
				AND YEAR (fecha_reserv)  = @vAno) AS Mayo,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 6
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 6
				AND YEAR (fecha_reserv)  = @vAno) AS Junio,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 7
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 7
				AND YEAR (fecha_reserv)  = @vAno) AS Julio,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 8
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 8
				AND YEAR (fecha_reserv)  = @vAno) AS Agosto,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 9
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 9
				AND YEAR (fecha_reserv)  = @vAno) AS Septiembre,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 10
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 10
				AND YEAR (fecha_reserv)  = @vAno) AS Octubre,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 11
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 11
				AND YEAR (fecha_reserv)  = @vAno) AS Noviembre,
	       (SELECT  COUNT(*) 
	        FROM    HOTERESE
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 12
				AND YEAR (fecha_reserv)  = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHRES
	        WHERE   id_oper    = op.id_oper
				AND id_estado NOT IN (1, 4)
				AND MONTH(fecha_reserv) = 12
				AND YEAR (fecha_reserv)  = @vAno) AS Diciembre
	FROM  #Operadores op
	
	INSERT INTO #TotReseRoom
	SELECT op.id_oper AS IdOperador,
	       op.nombre  AS Operador,
	       'Rooming' AS Descripcion,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 1
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 1
				AND YEAR (fecha_rooming) = @vAno) AS Enero,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 2
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 2
				AND YEAR (fecha_rooming) = @vAno) AS Febrero,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 3
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 3
				AND YEAR (fecha_rooming) = @vAno) AS Marzo,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 4
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 4
				AND YEAR (fecha_rooming) = @vAno) AS Abril,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 5
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 5
				AND YEAR (fecha_rooming) = @vAno) AS Mayo,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 6
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 6
				AND YEAR (fecha_rooming) = @vAno) AS Junio,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 7
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 7
				AND YEAR (fecha_rooming) = @vAno) AS Julio,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 8
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 8
				AND YEAR (fecha_rooming) = @vAno) AS Agosto,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 9
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 9
				AND YEAR (fecha_rooming) = @vAno) AS Septiembre,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 10
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 10
				AND YEAR (fecha_rooming) = @vAno) AS Octubre,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 11
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 11
				AND YEAR (fecha_rooming) = @vAno) AS Noviembre,
	       (SELECT  COUNT(*) 
	        FROM    HOTEROOM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 12
				AND YEAR (fecha_rooming) = @vAno)
		   + 
		   (SELECT  COUNT(*) 
	        FROM    HOTEHROM
	        WHERE   id_oper    = op.id_oper
				AND MONTH(fecha_rooming) = 12
				AND YEAR (fecha_rooming) = @vAno) AS Diciembre
	FROM  #Operadores op
	
	DELETE FROM #TotReseRoom 
	WHERE Enero   + Febrero   + Marzo + 
	      Abril   + Mayo      + Junio + 
	      Julio   + Agosto    + Septiembre + 
	      Octubre + Noviembre + Diciembre = 0
	
	SELECT *,
	       @vAno AS Ano
	FROM #TotReseRoom
	ORDER BY Operador, Descripcion ASC
	
	RETURN

END
