
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Reserva]    Script Date: 08/17/2014 11:12:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO






ALTER     PROCEDURE [dbo].[Calc_Ing_Reserva]   @dia_inic datetime, @dia_fin datetime, @mone int, @tasa_ext numeric(13,8), @ing_reserva numeric(15,2) output
AS

/*

	-------------------------------------------------- SEVEN DSS -------------------------------------------------------------

	ADVERTENCIA: Este proceso nativo de Front se incluye en los scripts principales de Seven DSS.
	Se incluye porque tiene actualizaciones que lo hacen más preciso y permite que sus resultados sean los mismos que del DSS.
	Por favor indicar si este script fue actualizado para incluirlo en los scripts de Seven DSS y evitar
	des-actualizarlo por accidente al instalar o actualizar Seven DSS.
	
	--------------------------------------------------------------------------------------------------------------------------

*/

/*   Calcula el pronostico de ingresos de extras por reservas en la moneda base del Front , para el dia pasado como parametro */
begin
   
    Declare @tasa_mb numeric(13,8) , @fecha_hotel datetime
    Select @tasa_mb = tasa,   @fecha_hotel = fecha_hotelera
        From Hotetabl  tabl
           Inner Join Siclamon sicla on tabl.id_mone = sicla.id_mone 
           
    -- Recordar que Front siempre guarda en HOTELRES el precio con impuesto incluido. 
    DECLARE @ImpuHospedaje NUMERIC(18, 2) = (ISNULL((SELECT TOP 1 SUM(im.porc_impu) impu_hosp
													 FROM       HOTETHAB th
													 INNER JOIN HOTECON0 c0 ON th.id_con0 = c0.id_con0
													 INNER JOIN HOTEPRE0 p0 ON c0.id_con0 = p0.id_con0
													 INNER JOIN HOTEIMPU ip ON p0.id_pre0 = ip.id_pre0
													 INNER JOIN SICLAIMP im ON ip.id_impu = im.id_impu
													 INNER JOIN SICLAMON mo ON p0.id_mone = mo.id_mone
													 WHERE mo.tasa = 1-- AND ip.incluido = 1
													 GROUP BY th.id_thab), 0) / 100) + 1

    Select  @ing_reserva  = Sum ( Convert(numeric(15,2),(precio_hab/@ImpuHospedaje))*cant_habi * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / sicla.tasa 
                                                                                     else 1
                                                                                     end )   
       From Hoterese rese
          Inner Join Hotelres lres     on  rese.id_rese    = lres.id_rese
          Inner Join Hotecont cont  on  rese.id_cont   = cont.id_cont
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rese.fecha_ent, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rese.fecha_sal) > 0)  or
              (DateDiff(day, rese.fecha_ent, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rese.fecha_sal) > 0))  and 
              rese.id_estado in(2,3,5)
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
              
    -- Vamos a incluir el histórico para que no hayan diferencias con DSS
    Select  @ing_reserva = Coalesce(@ing_reserva,0) + Coalesce(Sum ( Convert(numeric(15,2),(precio_hab/@ImpuHospedaje))*cant_habi * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / sicla.tasa 
                                                                                     else 1
                                                                                     end ),0.00)  
       From Hotehres rese
          Inner Join Hotehlre lres     on  rese.id_rese    = lres.id_rese
          Inner Join Hotecont cont  on  rese.id_cont   = cont.id_cont
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rese.fecha_ent, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rese.fecha_sal) > 0)  or
              (DateDiff(day, rese.fecha_ent, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rese.fecha_sal) > 0))  and 
              rese.id_estado in(2,3,5)
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)

   Select  @ing_reserva  = Coalesce(@ing_reserva,0) + Coalesce(Sum ( (reau.precio*reau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end ) ,0.00)
       From Hoterese rese
          Inner Join Hotelres lres     on  rese.id_rese    = lres.id_rese
          Inner Join Hotecont cont  on  rese.id_cont   = cont.id_cont
          Inner join Hotereau reau on lres.id_lres = reau.id_lres
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, reau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, reau.fecha_hasta) > 0)  or
              (DateDiff(day, reau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, reau.fecha_hasta) > 0))  and 
              rese.id_estado in(2,3,5) and
              reau.id_con0 in (select thab.id_con0 from hotethab thab )
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
              
    -- Vamos a incluir el histórico para que no hayan diferencias con DSS
    Select  @ing_reserva  = Coalesce(@ing_reserva,0) + Coalesce(Sum ( (reau.precio*reau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end ) ,0.00)
       From Hotehres rese
          Inner Join Hotehlre lres     on  rese.id_rese    = lres.id_rese
          Inner Join Hotecont cont  on  rese.id_cont   = cont.id_cont
          Inner join Hotehreau reau on lres.id_lres = reau.id_lres
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, reau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, reau.fecha_hasta) > 0)  or
              (DateDiff(day, reau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, reau.fecha_hasta) > 0))  and 
              rese.id_estado in(2,3,5) and
              reau.id_con0 in (select thab.id_con0 from hotethab thab )
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
              
     Select @ing_reserva = COALESCE (@ing_reserva, 0)
end







