SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE Res_to_Rooming1
                @rese int, 
                @nro_rooming int output
AS

/*  Procedimiento que convierte una reserva en rooming list */

begin
  SET NOCOUNT ON

  declare @key_room int,@key_rool int,@key_roon int,@id_dega int,
          @id_lres int,@precio money,@cant_habi int,@count int, 
          @id_habi int,@uhab tinyint,@ralo tinyint, @default_nac int,
          @rool int, @max_person int, @count_person int,
	      @max_adultos int, @max_ninos int
	     ,@id_cont int, @f_ent datetime, @f_sal datetime, 
	      @result int --numeric(13,4)

  if exists(select id_rese From hoterese  where id_rese = @rese and id_estado in (3,5) )
   begin
       
       Select @rool = 0
       select @default_nac = default_nac From Hotetabl
       
     /* Comienzo la Transaccion  */
        Begin Transaction 
        
     /* Inserto en Hoteroom  */
    
        Select @key_room = 1
         Insert Hoteroom (id_dega,id_grup,id_cont,id_origen,referencia,nro_rooming,
                          fecha_rooming,id_venta,id_oper)
                   Select @id_dega,null, rese.id_cont,rese.id_origen,rese.referencia, -2147283647,
                          bl.fecha_hotelera,rese.id_venta,rese.id_oper
                    From Hoterese rese,Hotetabl bl where rese.id_rese = @rese 
                    
         Select @key_room = @@identity 
         Select @nro_rooming = null
         Select @nro_rooming = nro_rooming From Hoteroom Where id_room = @key_room
         
         Select @id_cont = id_cont, @f_ent = fecha_ent, @f_sal = fecha_sal
            from HOTERESE where id_rese = @rese
 
     /* Inserto en HOTEROOM_Origen*/
         Insert Hoteroom_origen (id_room,id_dato,id_refer)
                select @key_room,id_dato,id_refer
                   From Hoterese_origen where id_rese = @rese
 
       /* Inserto los planes de pago  */
         Insert Hoteroga (id_room, fecha_plazo, importe)
                select distinct @key_room,fecha_plazo, importe
                   From Hoterega where id_rese = @rese
                   
       /* Inserto los degas  */
         Insert Hoterogl (id_roga, id_dega)
            select hoteroga.id_roga, hoteregl.id_dega
                   From Hoterega 
                      inner join hoteregl on hoterega.id_rega = hoteregl.id_rega
                      inner join hoteroga on hoteroga.fecha_plazo = hoterega.fecha_plazo 
                                 and hoteroga.importe = hoterega.importe
                   Where hoterega.id_rese = @rese and hoteroga.id_room = @key_room 
                         and hoteregl.id_dega not in (select id_dega from hoterogl)
                 
         /* Updateo el estado actual del dega */
         Update hotedega set asignada_a = 2, id_refer = @key_room
                where id_dega in 
                       (select id_dega 
                           from hoteregl 
                                inner join hoterega on hoteregl.id_rega = hoterega.id_rega 
                                where hoterega.id_rese = @rese)

         Select  @key_rool = 1
         Declare c_rool  CURSOR  LOCAL  For  
			Select id_lres,id_uhab,id_ralo,cant_habi,precio_hab, id_habi 
				From Hotelres  where id_rese = @rese  For Read Only
         Open c_rool
         Select @rool = @@CURSOR_ROWS
         fetch next from c_rool into @id_lres,@uhab, @ralo, @cant_habi, @precio, @id_habi
         
         While @@fetch_status = 0 
         Begin
             --execute Calc_Precio_Rool @rese, @uhab, @ralo, @precio output 
             Exec Calc_Prec_Rool @id_cont, @uhab, @ralo, @f_ent, @f_sal, @precio output
	         Select @max_adultos = COALESCE(max_adultos,0),
		            @max_ninos   = COALESCE(max_ninos,0)
                From Hoteuhab where id_uhab = @uhab

             Select @max_person = @max_adultos + @max_ninos
             Select @count = 1
             While  @count <= @cant_habi
             Begin
                  /* Inserto en Hoterool */
                  Insert Hoterool (id_uhab,id_room,id_estado,id_vuelsal,
                                   id_habi,id_ralo,fecha_ent,fecha_sal,
                                   observaciones,precio,referencia,id_vuelent)
		                  Select @uhab,@key_room,rese.id_estado,rese.id_vuelsal,
		                         case when @count = 1 
		                              then @id_habi 
		                              else null end,
		                         @ralo,rese.fecha_ent,
			                      rese.fecha_sal,rese.observaciones,
			                      @precio,rese.referencia,rese.id_vuelent
                            From Hoterese rese 
                              Where rese.id_rese = @rese
                
                Select @key_rool  = @@identity

                 /* Inserto en Hoteroon  tantas personas como max_adultos de la habitacion*/                
		Select @key_roon = 1          
        Select  @count_person = 1
        
        While  @count_person <= @max_adultos
        begin
   	       Insert Hoteroon (id_cardex,id_rool,nombre,adulto,id_naci)   
	              Select rese.id_cardex,@key_rool,rese.nombre,1,
             		     Case When rese.id_naci is not null  
             		          then rese.id_naci  
             		          else @default_nac end as id_naci
		              From Hoterese rese 
		              Where rese.id_rese = @rese  
                       
                      Select @count_person = @count_person + 1
                  end

                 /* Inserto en Hoteroon  tantas personas como max_ninos de la habitacion*/
                Select @count_person = 1
                
                While  @count_person <= @max_ninos
                begin
   	                  Insert Hoteroon (id_cardex,id_rool,nombre,adulto,id_naci)   
	                     Select rese.id_cardex,@key_rool,rese.nombre,0,
             		            Case When rese.id_naci is not null  
             		                 then rese.id_naci  
             		                 else @default_nac end as id_naci
		                     From Hoterese rese 
		                     Where rese.id_rese = @rese  
                      Select @count_person = @count_person + 1
                  end

                  /* Inserto los cargos automaticos */
                   Insert Hoterlau (id_rool,id_con0,precio,cantidad,
                                    fecha_desde,fecha_hasta)
                          select @key_rool,id_con0, precio, cantidad, 
                                 fecha_desde, fecha_hasta
                             From Hotereau 
                             where id_lres = @id_lres

                    Insert Hoterlag (id_rool,id_cfag,precio,cantidad,
                                     fecha_desde,fecha_hasta)
                          select @key_rool,id_cfag, precio, cantidad, 
                                 fecha_desde, fecha_hasta
                              From Hotereag 
                              where id_lres = @id_lres

                Select @count = @count + 1
             End          
             fetch next from c_rool into @id_lres,@uhab,@ralo,@cant_habi, @precio, @id_habi
         End      
         CLOSE    c_rool
         deallocate c_rool
         
         if  @rool <> 0 
             begin
                  Update Hoterese Set id_estado = 1 Where id_rese = @rese
                  Commit Transaction         
            end
        else
            Rollback Transaction 
        end
end

