Me
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Me as PDF for free.
More details
- Words: 243
- Pages: 2
/*========== created by :- mohammed salman ======= created date :- 13/11/2006 ============== -- stored procedure <<:::fo_rpt_guest_list::::>> use for guest list report. -- report==>>guest list(based on room status, date range) -- syntax==>> fo_rpt_guest_list , , -- use==>> fo_rpt_guest_list '','01/01/2002','07/07/2007' */ create procedure dbo.fo_rpt_guest_list @room_status smallint, @date_from varchar(10), @date_to varchar(10) as set nocount on declare @qrystr varchar(8000), @condition varchar(300), @total_room varchar(3) select @total_room= count(number)
from fo_room_t
if @date_from<>' ' and @date_to<>' ' begin select @qrystr= 'select dbo.fo_registration_t.room_no as [str col header 1], dbo.fo_room_category_t.name as [str col header 2], dbo.fo_registration_t.title + '' '' + dbo.fo_registration_t.family_name + '' '' + dbo.fo_registration_t.first_name as [str col header 3], dbo.fo_registration_t.sex as [str col header 4] , dbo.fo_registration_t.room_rate as [dec col header 5], dbo.fo_registration_t.number_of_pax as [int col header 6], datediff(day,convert(datetime, left(dbo.fo_registration_t.arrival_date, 12), 103) , convert(datetime, left(dbo.fo_registration_t.departure_date, 12), 103)) as [int col header 7], convert(datetime, left(dbo.fo_registration_t.arrival_date, 12), 103) as [dat col header 8], convert(datetime, left(dbo.fo_registration_t.departure_date, 12), 103) as [dat col header 9], dbo.fo_company_t.name as [str col header 10], dbo.fo_nationality_t.name as [str col header 11], convert(int, "' +@total_room+'" )as [total room] from dbo.fo_registration_t inner join dbo.fo_nationality_t on dbo.fo_registration_t.nationality_code = dbo.fo_nationality_t.code inner join dbo.fo_company_t on dbo.fo_registration_t.company_code = dbo.fo_company_t.code inner join dbo.fo_room_category_t on dbo.fo_registration_t.room_type_code = dbo.fo_room_category_t.code', @condition=' where ( convert(datetime,left(dbo.fo_registration_t.arrival_date,12),103) between convert(datetime,"'+@date_from+'" ,103) and convert(datetime,"'+@date_to+'",103)) ' begin if @room_status=1 select @condition=@condition + ' and (dbo.fo_registration_t.room_status =1) '
else select @condition=@condition + ' (dbo.fo_registration_t.room_status =0) ' end end else raiserror ('invalid parameter found.',16,1) print @qrystr select @qrystr=@qrystr + @condition print @qrystr exec (@qrystr) go
and
from fo_room_t
if @date_from<>' ' and @date_to<>' ' begin select @qrystr= 'select dbo.fo_registration_t.room_no as [str col header 1], dbo.fo_room_category_t.name as [str col header 2], dbo.fo_registration_t.title + '' '' + dbo.fo_registration_t.family_name + '' '' + dbo.fo_registration_t.first_name as [str col header 3], dbo.fo_registration_t.sex as [str col header 4] , dbo.fo_registration_t.room_rate as [dec col header 5], dbo.fo_registration_t.number_of_pax as [int col header 6], datediff(day,convert(datetime, left(dbo.fo_registration_t.arrival_date, 12), 103) , convert(datetime, left(dbo.fo_registration_t.departure_date, 12), 103)) as [int col header 7], convert(datetime, left(dbo.fo_registration_t.arrival_date, 12), 103) as [dat col header 8], convert(datetime, left(dbo.fo_registration_t.departure_date, 12), 103) as [dat col header 9], dbo.fo_company_t.name as [str col header 10], dbo.fo_nationality_t.name as [str col header 11], convert(int, "' +@total_room+'" )as [total room] from dbo.fo_registration_t inner join dbo.fo_nationality_t on dbo.fo_registration_t.nationality_code = dbo.fo_nationality_t.code inner join dbo.fo_company_t on dbo.fo_registration_t.company_code = dbo.fo_company_t.code inner join dbo.fo_room_category_t on dbo.fo_registration_t.room_type_code = dbo.fo_room_category_t.code', @condition=' where ( convert(datetime,left(dbo.fo_registration_t.arrival_date,12),103) between convert(datetime,"'+@date_from+'" ,103) and convert(datetime,"'+@date_to+'",103)) ' begin if @room_status=1 select @condition=@condition + ' and (dbo.fo_registration_t.room_status =1) '
else select @condition=@condition + ' (dbo.fo_registration_t.room_status =0) ' end end else raiserror ('invalid parameter found.',16,1) print @qrystr select @qrystr=@qrystr + @condition print @qrystr exec (@qrystr) go
and
