CREATE PROCEDURE Rack_Habitaciones @all int,
@filter varchar(5000),
@orden int,
@grup int,
@lprecio bit
AS
begin
SET NOCOUNT ON
declare @id_face int,@precio money, @precio_mb money,@fecha datetime
Select face.id_face,
habi.habitacion,
habi.limpia,
nombre = LTRim( COALESCE(facn.nombre,'') ),
facn.id_naci,
nacionalidad = COALESCE(naci.nacionalidad,''),
face.id_cont,
cont.contrato,
agen.directo,
uhab.uso_hab,
face.id_ralo,
ralo.cod_ralo,
adultos = COALESCE(adult.adultos,0),
ninos = COALESCE(nin.ninos,0),
face.fecha_ent,
face.fecha_sal,
face.vip,
face.sin_equip,
precio = Convert(money, 0),
Mon_Aloj = sicla.cod_mone,
face.id_grup,
desc_grup = COALESCE(grup.desc_grup,''),
piso = COALESCE(habi.grupo,''),
face.id_vuel,
vuelo = COALESCE(vuel.vuelo, ''),
hora = COALESCE(vuel.hora, ''),
referencia = COALESCE(face.referencia, ''),
face.credito,
face.id_rese,
face.id_room,
facn.fecha_nac,
face.tar_cred
Into #temp2
From Hoteface face
Inner Join hotehabi habi on face.id_habi = habi.id_habi
Inner Join Hotecont cont on face.id_cont = cont.id_cont
Inner Join Hoteagen agen on cont.id_agen = agen.id_agen
Inner Join Hoteuhab uhab on face.id_uhab = uhab.id_uhab
Inner Join Hoteralo ralo on face.id_ralo = ralo.id_ralo
Inner Join Siclamon sicla on face.id_mone = sicla.id_mone
Left Join Hotegrup grup on face.id_grup = grup.id_grup
Left Join Hotevuel vuel on face.id_vuel = vuel.id_vuel
Left Join ( Select id_face,Count(*) adultos From hotefacn
Where adulto <> 0 Group By id_face) adult
on face.id_face = adult.id_face
Left Join ( Select id_face,Count(*) ninos From hotefacn
Where adulto = 0 Group By id_face ) nin
on face.id_face = nin.id_face
Left Join ( Select distinct id_face, id_facn = Min(id_facn) From Hotefacn Group By id_face ) facn1
on face.id_face = facn1.id_face
Left Join Hotefacn facn on facn1.id_facn = facn.id_facn
Left Join Hotenaci naci on facn.id_naci = naci.id_naci
if @lprecio = 1
begin
DECLARE c_rack CURSOR FOR Select id_face From #temp2 FOR READ ONLY
open c_rack
fetch next from c_rack into @id_face
while @@fetch_status = 0
begin
Select @precio = 0
Execute Calc_Precio_Aloj_Habit @id_face, @precio output, @precio_mb output
Update #temp2 Set precio = @precio Where id_face = @id_face
fetch next from c_rack into @id_face
end
deallocate c_rack
end
if @all = 1
begin
Select @fecha = fecha_hotelera From Hotetabl
Insert Into #temp2
Select
0,
habi.habitacion,
habi.limpia,
nombre = Case When ford.id_habi is not null then 'Bloqueada' else '' end,
id_naci = NULL,
nacionalidad = '',
id_cont = 0,
contrato ='',
0,
thab.tipo_hab,
0,
'',
adultos = 0,
ninos = 0,
GETDATE(),
GETDATE(),
0,
0,
0,
'',
'',
'',
piso = COALESCE(habi.grupo,''),
'',
'',
'',
'',
0,
0,
0,
NULL
From Hotehabi habi
Left Join ( Select id_habi From Hoteford where DATEDIFF(day, fecha_desde, @fecha ) >= 0 and
DATEDIFF(day, @fecha, fecha_hasta ) > 0
) ford on habi.id_habi = ford.id_habi
Inner Join Hotethab thab on habi.id_thab = thab.id_thab
Where habi.eliminado = 0 and habi.id_habi not in ( select id_habi from hoteface )
end
if @orden = 2
if @grup = 1
Execute ('Select * from #temp2 ' + @filter + ' order by piso,fecha_ent')
else
Execute ('Select * from #temp2 ' + @filter + ' order by fecha_ent')
else
if @orden = 3
if @grup = 1
EXECUTE ('Select * From #temp2 ' + @filter + ' order by piso,fecha_sal' )
else
EXECUTE('Select * From #temp2 ' + @filter + ' order by fecha_sal ' )
else
if @grup = 1
EXECUTE ('Select * From #temp2 ' + @filter + ' order by piso,Rtrim(Ltrim(Convert(char(10),Len(habitacion)))),habitacion')
else
EXECUTE('Select * From #temp2 ' + @filter + ' Order by Rtrim(Ltrim(Convert(char(10),Len(habitacion)))),habitacion')
end
GO