
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Reserva_Otr]    Script Date: 08/17/2014 11:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










ALTER     PROCEDURE [dbo].[Calc_Ing_Reserva_Otr]   @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
    SET NOCOUNT ON
   
    Declare @tasa_mb numeric(13,8) , @fecha_hotel datetime, @ing_reservaII numeric(15,2) 
    Declare @Tab_IngOAgenI TABLE (id_room		int, 
								 id_cfag		int,
								 precio 		numeric(15,2),
								 cantidad		int,
								 id_mone		int,
								 tasa_fija		numeric(13,4),
								 tasa			numeric(13,4),
								 impuesto		numeric(8,2),
								 incluido		int,
								 cant_habi		int)
							 
	Declare @Tab_IngOAgenII TABLE(id_room		int, 
								 id_cfag		int,
								 precio 		numeric(15,2),
								 cantidad		int,
								 id_mone		int,
								 tasa_fija		numeric(13,4),
								 tasa			numeric(13,4),
								 impuesto		numeric(8,2),
								 incluido		int,
								 cant_habi		int) 
								 
    Select @tasa_mb = tasa,   @fecha_hotel = fecha_hotelera
        From Hotetabl  tabl
           Inner Join Siclamon sicla on tabl.id_mone = sicla.id_mone 
    
 Select  @ing_reservaII  = sum(Precio / ((impuesto/100)+1) * cantidad * cant_habi) from(
  select reau.precio ,reau.cantidad, SUM(coalesce(D.porc_impu,0))  as "Impuesto" , lres.cant_habi
       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 HOTEPRE0 B ON B.id_con0 = REAU.id_con0
				  inner join SICLAMON T on t.id_mone = B.id_mone and t.tasa=1
				  left join HOTEIMPU C on C.id_pre0 =B.id_pre0 or coalesce(C.id_pre0,0)=0
				  left join SICLAIMP D on D.id_impu = C.id_impu 
          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 not in (select rali.id_con0 from hoterali rali )and
              reau.id_con0 not in (select thab.id_con0 from hotethab thab )
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
         group by reau.precio,reau.cantidad,lres.id_lres,lres.cant_habi) as Total              

     --Select @ing_reservaII = COALESCE (@ing_reservaII, 0)
     
  -- Vamos a incluir el histórico para que no hayan diferencias con DSS
  Select  @ing_reservaII  = COALESCE (@ing_reservaII, 0) + COALESCE ((select sum(Precio / ((impuesto/100)+1) * cantidad * cant_habi) from(
  select reau.precio ,reau.cantidad, SUM(coalesce(D.porc_impu,0))  as "Impuesto" , lres.cant_habi
       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 HOTEPRE0 B ON B.id_con0 = REAU.id_con0
				  inner join SICLAMON T on t.id_mone = B.id_mone and t.tasa=1
				  left join HOTEIMPU C on C.id_pre0 =B.id_pre0 or coalesce(C.id_pre0,0)=0
				  left join SICLAIMP D on D.id_impu = C.id_impu 
          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 not in (select rali.id_con0 from hoterali rali )and
              reau.id_con0 not in (select thab.id_con0 from hotethab thab )
              AND rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
         group by reau.precio,reau.cantidad,lres.id_lres,lres.cant_habi) as Total), 0)              

     Select @ing_reservaII = COALESCE (@ing_reservaII, 0)
     

 insert @Tab_IngOAgenI    
  select  rese.id_rese, reau.id_cfag, reau.precio ,reau.cantidad,cont.id_mone, cont.tasa_fija, sicla.tasa, 
		  Impuesto=coalesce(D.porc_impu,0), incluido, lres.cant_habi  
       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 HOTEREAG reau on lres.id_lres = reau.id_lres
				  inner join HOTECFAG B ON B.id_cfag = REAU.id_cfag
				  inner join SICLAMON T on t.id_mone = cont.id_mone and t.tasa=1
				  left join HOTEIMAG C on C.id_cfag = B.id_cfag 
				  Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and C.id_impu= imco.id_impu 
				  left join SICLAIMP D on D.id_impu = C.id_impu 
          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 
              rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface) AND 
              ISNULL(B.id_uhab, 0) = 0 AND 
              ISNULL(B.id_ralo, 0) = 0
              
 insert @Tab_IngOAgenI    
  select  rese.id_rese, reau.id_cfag, reau.precio ,reau.cantidad,cont.id_mone, cont.tasa_fija, sicla.tasa, 
		  Impuesto=coalesce(D.porc_impu,0), incluido, lres.cant_habi  
       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 HOTEHREAG reau on lres.id_lres = reau.id_lres
				  inner join HOTECFAG B ON B.id_cfag = REAU.id_cfag
				  inner join SICLAMON T on t.id_mone = cont.id_mone and t.tasa=1
				  left join HOTEIMAG C on C.id_cfag = B.id_cfag 
				  Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and C.id_impu= imco.id_impu 
				  left join SICLAIMP D on D.id_impu = C.id_impu 
          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 
              rese.id_rese  NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface) AND 
              ISNULL(B.id_uhab, 0) = 0 AND 
              ISNULL(B.id_ralo, 0) = 0
             
    insert @Tab_IngOAgenII           
    select * from @Tab_IngOAgenI where incluido=1
    
	update @Tab_IngOAgenI set incluido=1
	from @Tab_IngOAgenI T1
	 inner join @Tab_IngOAgenII T2 on T1.id_room = T2.id_room 
	 where t1.incluido=0
     
    update @Tab_IngOAgenI set impuesto=0 where incluido=0

	Select @ing_reserva  = Sum ( (precio / ((Impuesto/100)+1) * cantidad * cant_habi) * Case when id_mone ! = 3
																							 then 1 / COALESCE(tasa_fija, tasa)
																							 else 1
																							 end) from(
	select id_room, precio, cantidad, id_mone, tasa_fija, tasa, incluido, SUM(coalesce(Impuesto,0)) as "Impuesto", cant_habi
	from @Tab_IngOAgenI 
	group by id_room, precio, cantidad, id_mone, tasa_fija, tasa, incluido, cant_habi) as Total  
	               

     Select @ing_reserva = COALESCE (@ing_reservaII, 0) +COALESCE (@ing_reserva, 0)


end







     