
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Rooming]    Script Date: 08/17/2014 13:37:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO






ALTER     PROCEDURE [dbo].[Calc_Ing_Rooming]   @dia_inic   datetime, @dia_fin  datetime, @mone int, @tasa_ext numeric(13,8), 
			              @ing_Ro_Al  numeric(15,2) output, @ing_Ro_Pen numeric(15,2) output,
				      @ing_OAgen  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.
	
	--------------------------------------------------------------------------------------------------------------------------

*/
     /*  Proced que calcula los Ingresos de Agencias  por Rooming List por concepto de alojamiento, pension  y otros */     
/* COL.RAY Se le modifico el historico para que no tome las "NO SHOW" dentro el calculo de ingreso (Estado 3 y 5)  */


begin

    declare @id_rool int, @id_uhab int, @id_ralo tinyint, @id_cfag int, @tipo_cfag tinyint, @precio numeric(15,2), @id_cont int,
            @factor_div tinyint, @id_cfag_ralo int,  @factor_div_ralo tinyint, @precio_ralo numeric(15,2),
            @tasa_mb numeric(13,8),  @tasa numeric(13,8),  @valor numeric(15,2), @tipo char(1), @uhab int, @ralo int,
            @aloj_precio numeric(15,2),  @aloj_porc numeric(4,2), @pen_precio numeric(15,2) , @Impuestos numeric(15,2),
	    @pen_porc numeric(4,2), @otr_precio numeric(15,2), @otr_porc numeric(4,2), @cant_pax int, @incluido int,
 	    @aloj numeric(15,2), @pen numeric(15,2), @otr numeric(15,2), @fecha_hotel datetime, @ing_OAgenII 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)
							 
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)
							  
Declare @Tab_IngHAgenI TABLE (id_rool		int, 
							 id_uhab		int,
							 id_ralo		int,
							 precio 		numeric(15,2),
							 tasa			numeric(13,4),
							 impuesto		numeric(5,2),
							 incluido		int,
 							 id_cont		int)
 							 
Declare @Tab_IngHAgenII TABLE (id_rool		int, 
							 id_uhab		int,
							 id_ralo		int,
							 precio 		numeric(15,2),
							 tasa			numeric(13,4),
							 impuesto		numeric(5,2),
							 incluido		int,
 							 id_cont		int)								 	    

    Select @ing_Ro_Al = 0, @ing_Ro_Pen = 0, @ing_OAgen = 0

    Select @tasa_mb = tasa, @fecha_hotel = fecha_hotelera	    
         From  Hotetabl tabl Inner Join Siclamon sicla on tabl.id_mone = sicla.id_mone


/***********************************************  INGRESOS DE PENSION POR CARGOS AUTOMATICOS *************************************************************/

	/*

    Select @ing_Ro_Pen = Sum ( (rlau.precio*rlau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end  )   
       From Hoteroom room
          Inner Join Hoterool rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTERLAG rlau  on rool.id_rool   = rlau.id_rool
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  AND 
              (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				) and
              rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
				--(select rali.id_con0 from hoterali rali where rool.id_ralo = rali.id_ralo)

     SET @ing_Ro_Pen = Coalesce(@ing_Ro_Pen,0) + Coalesce((select Sum ( (rlau.precio*rlau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end  )   
       From Hotehrom room
          Inner Join Hotehrol rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTEHRLAG rlau  on rool.id_rool   = rlau.id_rool
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  
              and 
              --(rool.id_estado NOT IN(3, 5) 
				--OR 
				(rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				--) 
				and
              rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
				--(select rali.id_con0 from hoterali rali where rool.id_ralo = rali.id_ralo)
				), 0)
				
		*/
				
		SET @ing_Ro_Pen = 0
     

/************************************************* INGRESOS DE HABITACION POR CARGOS AUTOMATICOS ***********************************************************/

    Select @ing_Ro_Al  = Sum ( (rlau.precio*rlau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end  )   
       From Hoteroom room
          Inner Join Hoterool rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTERLAG rlau  on rool.id_rool   = rlau.id_rool
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  and 
              (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				) and
              rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)

     Select @ing_Ro_Al = Coalesce(@ing_Ro_Al,0) + Coalesce((select Sum ( (rlau.precio*rlau.cantidad) * Case when cont.id_mone ! = @mone
                                                                                     then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
                                                                                     else 1
                                                                                     end  )   
       From Hotehrom room
          Inner Join Hotehrol rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTEHRLAG rlau  on rool.id_rool   = rlau.id_rool
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  
              and 
              --(rool.id_estado NOT IN(3, 5) 
				--OR 
				(rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				--) 
				and
              rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)), 0)
     

/***************************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS AGENCIA ***********************************************************/
    
    --Select @ing_OAgenII  = Sum ( (rlag.precio*rlag.cantidad) * Case when cont.id_mone ! = @mone
    --                                                                                 then @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
    --                                                                                 else 1
    --                                                                                 end  ) 
  insert @Tab_IngOAgenI    
  select  room.id_room, rlag.id_cfag, rlag.precio ,rlag.cantidad,cont.id_mone, cont.tasa_fija, sicla.tasa, Impuesto=coalesce(aimp.porc_impu,0), incluido                                                                                
       From Hoteroom room
          Inner Join Hoterool rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTERLAG rlag  on rool.id_rool   = rlag.id_rool
                   Inner Join HOTEIMAG imag on rlag.id_cfag = imag.id_cfag
                   Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and imag.id_impu= imco.id_impu 
                   Inner Join SICLAIMP aimp on imag.id_impu = aimp.id_impu                              
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlag.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlag.fecha_hasta) > 0)  or
              (DateDiff(day, rlag.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlag.fecha_hasta) > 0))  and 
              (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				) and
              --rlag.id_cfag not in (select thab.id_con0 from hotethab thab ) and 
              --rlag.id_cfag not in (select rali.id_con0 from hoterali rali ) and
              rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0) AND 
              rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0) AND
              rlag.id_cfag not in (select cfag.id_cfag from HOTECFAG cfag where cfag.id_ralo >= 1)
              
  --union -- provoca errores el union con tablas de variable o.O
  insert @Tab_IngOAgenI 
  select  room.id_room, rlag.id_cfag, rlag.precio ,rlag.cantidad,cont.id_mone, cont.tasa_fija, sicla.tasa, Impuesto=coalesce(aimp.porc_impu,0), incluido                                                                                
       From Hotehrom room
          Inner Join Hotehrol rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTEHRLAG rlag  on rool.id_rool   = rlag.id_rool
                   Inner Join HOTEIMAG imag on rlag.id_cfag = imag.id_cfag
                   Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and imag.id_impu= imco.id_impu 
                   Inner Join SICLAIMP aimp on imag.id_impu = aimp.id_impu                              
          Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlag.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlag.fecha_hasta) > 0)  or
              (DateDiff(day, rlag.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlag.fecha_hasta) > 0))  
              and 
              --(rool.id_estado NOT IN(3, 5) 
				--OR 
				(rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				--) 
				and
              --rlag.id_cfag not in (select thab.id_con0 from hotethab thab ) and 
              --rlag.id_cfag not in (select rali.id_con0 from hoterali rali ) and
              rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0) AND 
              rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0) AND
              rlag.id_cfag not in (select cfag.id_cfag from HOTECFAG cfag where cfag.id_ralo >= 1)
              
  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_OAgenII  = Sum ( (precio / ((Impuesto/100)+1)* cantidad) * 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"
	from @Tab_IngOAgenI 
	group by id_room, precio, cantidad, id_mone, tasa_fija, tasa, incluido) as Total           
              

    Select @ing_OAgenII = Coalesce(@ing_OAgenII,0)
     
/***************************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS DIRECTOS *******************************************************/

    Select @ing_OAgen  = Sum ( (precio/ ((Impuesto/100)+1) * cantidad) * Case when id_mone ! = 3
                                                                                     then 1 / COALESCE(tasa_fija, tasa)
                                                                                     else 1
                                                                                     end) from(
      select rlau.precio ,rlau.cantidad, t.id_mone, T.tasa, cont.tasa_fija, SUM(coalesce(D.porc_impu,0))  as "Impuesto"  
       From Hoteroom room
          Inner Join Hoterool rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTERLAU rlau  on rool.id_rool   = rlau.id_rool
				  inner join HOTEPRE0 B ON B.id_con0 = rlau.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                             
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  and 
              (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				) and
              rlau.id_con0 not in (select thab.id_con0 from hotethab thab ) and 
              rlau.id_con0 not in (select rali.id_con0 from hoterali rali ) 
        group by room.id_room, rlau.precio, rlau.cantidad,t.id_mone, T.tasa, cont.tasa_fija) as total    
        
     Select @ing_OAgen = Coalesce(@ing_OAgen,0) + Coalesce((select Sum ( (precio/ ((Impuesto/100)+1) * cantidad) * Case when id_mone ! = 3
                                                                                     then 1 / COALESCE(tasa_fija, tasa)
                                                                                     else 1
                                                                                     end) from(
      select rlau.precio ,rlau.cantidad, t.id_mone, T.tasa, cont.tasa_fija, SUM(coalesce(D.porc_impu,0))  as "Impuesto"  
       From Hotehrom room
          Inner Join Hotehrol rool  on  room.id_room  = rool.id_room
          Inner Join Hotecont cont  on  room.id_cont  = cont.id_cont
          Inner join HOTEHRLAU rlau  on rool.id_rool   = rlau.id_rool
				  inner join HOTEPRE0 B ON B.id_con0 = rlau.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                             
        Where --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and 
              ((DateDiff(day, rlau.fecha_desde, @dia_inic) >= 0 and
              DateDiff(day, @dia_inic, rlau.fecha_hasta) > 0)  or
              (DateDiff(day, rlau.fecha_desde, @dia_fin) >= 0 and
              DateDiff(day, @dia_fin, rlau.fecha_hasta) > 0))  
              and 
              --(rool.id_estado NOT IN(3, 5) 
				--OR 
				(rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				--) 
				and
              rlau.id_con0 not in (select thab.id_con0 from hotethab thab ) and 
              rlau.id_con0 not in (select rali.id_con0 from hoterali rali )
        group by room.id_room, rlau.precio, rlau.cantidad,t.id_mone, T.tasa, cont.tasa_fija) as total),0)                        

     Select @ing_OAgen = Coalesce(@ing_OAgen,0)+ Coalesce(@ing_OAgenII,0)
  
/******************************************************** TABLAS PARA UNIFICAR "INCLUIDO" **********************************************************************/

   insert @Tab_IngHAgenI
          Select id_rool, 
				 rool.id_uhab, 
				 rool.id_ralo, 
				 precio = Convert(Numeric(15,2),precio), 
				 tasa = Case when cont.id_mone ! = @mone
			                 then @tasa_ext / Coalesce(cont.tasa_fija,sicla.tasa)
                             else 1
                        end,
				porc_impu=Coalesce(porc_impu,0),
				--incluido=Coalesce(incluido,2), 
				1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.                      
				room.id_cont
                From Hoterool rool 
                   Inner Join Hoteroom room on rool.id_room   = room.id_room
                   Inner Join Hotecont cont  on room.id_cont  = cont.id_cont
                   Inner Join HOTECFAG cfag on  rool.id_uhab = cfag.id_uhab and cfag.eliminado=0
                   Inner Join HOTEIMAG imag on cfag.id_cfag = imag.id_cfag
                   Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and imag.id_impu= imco.id_impu
                   Inner Join SICLAIMP aimp on imag.id_impu = aimp.id_impu 
				   Inner  Join Siclamon sicla  on cont.id_mone = sicla.id_mone
                 Where   --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and
                       ((DateDiff(day, rool.fecha_ent, @dia_inic) >= 0 and
                         DateDiff(day, @dia_inic, rool.fecha_sal) > 0)  or
                        (DateDiff(day, rool.fecha_ent, @dia_fin) >= 0 and
                         DateDiff(day, @dia_fin, rool.fecha_sal) > 0))  and 
						 (rool.id_estado IN(3, 5) 
							OR (rool.id_estado = 2 
									AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
								)
							) 
			--union
			insert @Tab_IngHAgenI
			Select id_rool, 
				 rool.id_uhab, 
				 rool.id_ralo, 
				 precio = Convert(Numeric(15,2),precio), 
				 tasa = Case when cont.id_mone ! = @mone
			                 then @tasa_ext / Coalesce(cont.tasa_fija,sicla.tasa)
                             else 1
                        end,
				porc_impu=Coalesce(porc_impu,0),
				--incluido=Coalesce(incluido,2), 
				1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.                    
				room.id_cont
                From Hotehrol rool 
                   Inner Join Hotehrom room on rool.id_room   = room.id_room
                   Inner Join Hotecont cont  on room.id_cont  = cont.id_cont
                   Inner Join HOTECFAG cfag on  rool.id_uhab = cfag.id_uhab and cfag.eliminado=0
                   Inner Join HOTEIMAG imag on cfag.id_cfag = imag.id_cfag
                   Inner Join HOTEIMCO imco on cont.id_cont = imco.id_cont and imag.id_impu= imco.id_impu
                   Inner Join SICLAIMP aimp on imag.id_impu = aimp.id_impu 
				   Inner  Join Siclamon sicla  on cont.id_mone = sicla.id_mone
                 Where   --DATEDIFF(day, @fecha_hotel, fecha_ent)  >= 0 and
                       ((DateDiff(day, rool.fecha_ent, @dia_inic) >= 0 and
                         DateDiff(day, @dia_inic, rool.fecha_sal) > 0)  or
                        (DateDiff(day, rool.fecha_ent, @dia_fin) >= 0 and
                         DateDiff(day, @dia_fin, rool.fecha_sal) > 0))  
                         and 
						 --(rool.id_estado NOT IN(3, 5) 
							--OR 
							(rool.id_estado = 2 
									AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
								)
							--) 

	insert @Tab_IngHAgenII
		   select * from @Tab_IngHAgenI where incluido=1

	update @Tab_IngHAgenI set incluido=1
		   from @Tab_IngHAgenI T1
				inner join @Tab_IngHAgenII T2 on T1.id_rool = T2.id_rool 
		   where t1.incluido=0
  
	update @Tab_IngHAgenI set impuesto=0 where incluido=0
  
/*************************************************************** CURSOR DE INGRESOS **********************************************************************/

     DECLARE c_rool SCROLL CURSOR FOR 
		 select * from(
		 select id_rool, 
				id_uhab, 
				id_ralo, 
				precio,
				tasa, 
				SUM(coalesce(impuesto,0))  as "Impuestos", 
				incluido, 
				id_cont  
				from @Tab_IngHAgenI
				group by id_rool, id_uhab, id_ralo, precio, tasa, incluido, id_cont ) as Total
		 FOR READ ONLY   

    open c_rool
    fetch first from c_rool into @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido,  @id_cont
    --@porc_impu, @incluido,
    
/******************************************************** BUSQUEDA DE INGRESOS DEL CURSOR **********************************************************************/

    while @@fetch_status = 0
    begin
         select @Impuestos = Case when @incluido = 2
                             then 1
                             else (@Impuestos/100)+1
                         end
       /* Busca si esta incluido en el contrato */
        Select @id_cfag = null
      /*  Busco el id_cfag  */
        Select  @id_cfag     =  id_cfag, 
	        @tipo_cfag   =  tipo_cfag, 
                @uhab        =  cfag.id_uhab,
	        @ralo        =  cfag.id_ralo
            From Hotecfag cfag 
            Where id_uhab is not null and id_ralo is not null and id_uhab = @id_uhab and id_ralo = @id_ralo and eliminado = 0

        Select @factor_div = factor_div From Hoteccon where id_cfag = @id_cfag and id_cont = @id_cont             
        if @id_cfag is null
          begin
           Select @id_cfag = Max(hotecfag.id_cfag) , @tipo_cfag = Min(tipo_cfag), @uhab = @id_uhab, 
                  @cant_pax = Max(max_adultos)
              From Hotecfag inner join hoteccon on hotecfag.id_cfag = hoteccon.id_cfag
                            inner join hoteuhab on hoteuhab.id_uhab = hotecfag.id_uhab
              Where hotecfag.id_uhab = @id_uhab 
                and hoteccon.id_cont = @id_cont 
                and hotecfag.eliminado = 0
 
          Select @factor_div = factor_div From Hoteccon where id_cfag = @id_cfag and id_cont = @id_cont             

           Select @id_cfag_ralo = Max(hotecfag.id_cfag) , @ralo = @id_ralo,
                  @precio_ralo = Convert(numeric(15,2),Max(precio))
              From Hotecfag inner join hoteccon on hotecfag.id_cfag = hoteccon.id_cfag
                            inner join hotecpre on hoteccon.id_ccon = hotecpre.id_ccon
                            inner join hoteperi on hoteperi.id_peri = hotecpre.id_peri
              Where id_ralo = @id_ralo 
                and hoteccon.id_cont = @id_cont 
                and hotecfag.eliminado = 0
                and DateDiff(day, hoteperi.fecha_desde, @dia_inic) >= 0 
                and DateDiff(day, @dia_inic, hoteperi.fecha_hasta) > 0

           Select @factor_div_ralo = factor_div From Hoteccon where id_cfag = @id_cfag_ralo and id_cont = @id_cont 
           Select @precio_ralo = Coalesce(@precio_ralo * @cant_pax / @factor_div_ralo * @tasa,0)
          end
           
			             
          Select @precio =  @precio * @cant_pax / @factor_div * @tasa  /* Convierto el precio a moneda final */ 

          if @tipo_cfag in (1,2,3)    /* No compuesto  */
              begin
                if @uhab is not null and @ralo is not null
                   begin
                      Select  @ing_Ro_Al  = COALESCE(@ing_Ro_Al,0)  + (Coalesce(@precio,0)/@Impuestos)
                      Select  @ing_Ro_Pen = COALESCE(@ing_Ro_Pen,0) + Coalesce(@precio_ralo,0)    /* Ingreso de pension */   
                   end
                 else
                  if  @uhab is not null and @ralo is null
                    begin
						Select  @ing_Ro_Al   = COALESCE(@ing_Ro_Al,0) + (Coalesce(@precio,0)/@Impuestos)     /* Ingreso de Alojamiento */
					end	
                  else
                      Select  @ing_OAgen  =  COALESCE(@ing_OAgen,0) + Coalesce(@precio,0)   /* Ingreso por otros   */
              end  
          else
             begin
               Select  @tipo   = csim.tipo_csim      /* tipo del concepto compuesto  */
      	           From Hoteccom ccom Inner Join  Hotecsim  csim on ccom.id_csim = csim.id_csim
		   Where ccom.id_cfag = @id_cfag     
                  
               Exec Calc_Ing_Desg  @id_cfag , @aloj_precio  output, 
				                 @aloj_porc    output,
				                 @pen_precio   output, 
			         	         @pen_porc     output, 
				                 @otr_precio   output, 
				                 @otr_porc     output

               Select  @aloj =  ( @aloj_precio * @factor_div ) +  ( @precio * @aloj_porc /100 ),
  		       @pen  =  ( @pen_precio  * @factor_div ) +  ( @precio * @pen_porc  /100 ),
                       @otr  =  ( @otr_precio  * @factor_div ) +  ( @precio * @otr_porc  /100 )
             
                 if  @precio >  0 and  @aloj <= @precio 
                      begin	
		        Select @ing_Ro_Al = @ing_Ro_Al + @aloj, @precio =  @precio - @aloj
	              end  
                   else
                     begin
	                 Select @ing_Ro_Al = @ing_Ro_Al + @precio, @precio = 0                       
	             end

                 if  @precio >  0 and  @pen <= @precio 
                    begin	
		       Select @ing_Ro_Pen = @ing_Ro_Pen + @pen, @precio =  @precio -  @pen
	            end  
                 else
                    begin
	               Select @ing_Ro_Pen = @ing_Ro_Pen + @precio, @precio = 0                      
	            end

                 if  @precio >  0 and  @otr <= @precio 
                      begin	
		        Select @ing_OAgen   =  @ing_OAgen   +  @otr, @precio =  @precio - @otr
	              end  
                   else
                    begin
	              Select @ing_OAgen  =  @ing_OAgen  + @precio, @precio = 0                       
	            end

                  if @precio > 0   /* Asigno el resto del desglose del concepto */
                    begin
                        if  @tipo = 1 
		            Select @ing_Ro_Al    = @ing_Ro_Al + @precio
                        else
		           if   @tipo = 2 
                             Select @ing_Ro_Pen = @ing_Ro_Pen + @precio
                            else 
		              if  @tipo = 3 
			           Select @ing_OAgen   = @ing_OAgen + @precio		
	            end 
             end    
          fetch next  from c_rool into @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido, @id_cont
    end
   
   deallocate c_rool
end







