IF (EXISTS (select proc_name from sysprocedure where proc_name='GetLeaveArriveDate')) THEN
  Drop function GetLeaveArriveDate;
END IF;
GO

CREATE FUNCTION "DBA"."GetLeaveArriveDate"(in @Flag integer,in @GuestNo integer)
returns date
begin
declare @CDate date;
declare @MinRegNum integer;
declare @MinDate date;
declare @Days integer;
select min(RegNum) into @MinRegNum
from book as bb
where bb.Guest =@GuestNo;
select DateRegDt into @MinDate
from book
where RegNum=@MinRegNum;
select sum(NumLodgings) into @Days
from book
where book.Guest =@GuestNo;
if @Flag=1
then
// za data na pristigane
set @CDate=@MinDate;
else
// za data na zaminavane
set @CDate=days(@MinDate,@Days);
end if;
return(@CDate)
end
//[24.08.2005] Jorko
GO

if (Exists (Select Table_name from SysTable where Table_name='v_Kamerierki')) then
  Drop View v_Kamerierki
end if

GO

CREATE VIEW "DBA"."v_Kamerierki"  as
select RoomType=Max(Classes.ShortName),
RoomNum=Rooms.RoomNum,
GuestNo=Min(Book.Guest),
RoomStatus=Max(RoomStatus.Description),
Elderly=Sum(if(Book.DateRegDt<=bvrData1)
and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1)
and(Guests.AdultType=0)
and(Guests.Status=1) then 1 else 0 endif),
Children=Sum(if(Book.DateRegDt<=bvrData1)
and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1)
and(Guests.AdultType=1)
and(Guests.Status=1) then 1 else 0 endif),
RegDate=GetLeaveArriveDate(1,GuestNo),
DaysClean=max(Registers.IntStojnost),
Clean=(if Mod("date"(bvrData1)-RegDate,DaysClean)<>0 then ' ' else 'П,' endif),
ChangeSheets=(if Mod("date"(bvrData1)-RegDate,(select Max(r.IntStojnost)
from "dba".Registers as r
where r.RegisterName='CleanPerDays'))<>0
then ''
else 'СБ' endif),
ToDo=Clean+LeaveStat,
LeaveDate=GetLeaveArriveDate(2,GuestNo),
GuestName=Max(if(Book.DateRegDt<=bvrData1)
and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1)
then Guests."Name"
else null endif),
VIP=Max(if(Book.DateRegDt<=bvrData1)
and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1) then
VIP.VipName else null endif),
LeaveStat=if LeaveDate=bvrData1 then 'З' else ' ' endif,
IsReserve=Max(if RP.ReserveNo<>0 then 'Резервирана' else null
endif)
from "dba".Book left outer join "dba".Guests on Book.Guest=Guests.GuestNum
,"dba".Rooms left outer join "dba".ReservePlan as RP on RP.FromDateDt=bvrData1 and Rooms.RoomNum=RP.RoomNo
,Rooms left outer join Book on Rooms.RoomNum=Book.Room
and Book.DateRegDt<bvrData1
and Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1
and Book.NumLodgings>0
,Guests left outer join VIP on Guests.TipVip=VIP.N
,"dba".Guests,"dba".Classes,"dba".RoomStatus,"dba".VIP,"dba".Registers
where Rooms.RoomCat=Classes.Class
and Rooms.Status=RoomStatus.RoomStatusNo
and Registers.RegisterName='Kamerierki'
group by RoomNum
//[24.08.2005] Jorko, Fix v LeaveDate. RegDate
Go

IF (EXISTS (select proc_name from sysprocedure where proc_name='GetLastRoom')) THEN
  Drop function GetLastRoom;
END IF;
GO

CREATE FUNCTION "DBA"."GetLastRoom" (in @Guest integer)
returns integer
begin
declare @Room integer;
select Room into @Room from Book where RegNum=(select max(RegNum)
                                               from Book BB where BB.Guest=@Guest);
return(@Room);
end
//[24.08.2005] Jorko
GO

if (Exists (Select Table_name from SysTable where Table_name='v_ArriveStayLeaveGuestBook1')) then
  Drop View v_ArriveStayLeaveGuestBook1
end if
GO

CREATE VIEW "DBA"."v_ArriveStayLeaveGuestBook1"  
 as select GuestNum=Guest,
    ArriveDate=Min(Book.DateRegDt),
    LastRoom=GetLastRoom(GuestNum),//MAX(if Book.Staying=2 then 0 else Book.Room endif),
    NumNights=Sum(Book.NumLodgings),
    LeaveDate=Days(ArriveDate,NumNights),
    RegPrice=SUM(Book.NumLodgings*Book.Price),
    Admin=Max(Admin.FullName)
    from "DBA".Book,"DBA".Admin
    where Book.KodAdmin=Admin.AdminNo and Book.IsRest<>1
    group by Guest
    having ArriveDate<>LeaveDate
//[24.08.2005] Jorko
GO

if (Exists (Select Table_name from SysTable where Table_name='v_RoomingListRestaurant')) then
  Drop View v_RoomingListRestaurant
end if
GO

CREATE VIEW "DBA"."v_RoomingListRestaurant"  as select RestDate=GuestBoardPlans.PlanDate,
RoomNum=LastRoom,
GuestName=Guests."Name",
Elderly=if Guests.AdultType=0 then 1 else 0 endif,
Children=if Guests.AdultType>0 then 1 else 0 endif,
ArriveDate=ArriveDate,
LeaveDate=LeaveDate,
BB=(if GuestBoardPlans.BoardPlan in(4,5,6,7,8) then 1 else 0 endif),
L=(if GuestBoardPlans.BoardPlan in(2,3,7,8) then 1 else 0 endif),
D=(if GuestBoardPlans.BoardPlan in(1,3,5,6,7) then 1 else 0 endif),
HB=(if GuestBoardPlans.BoardPlan in(5,6) then BoardPlan.BoardPlanName else null endif),
Board=BoardPlan.BoardPlanName,
ReserveName=Reserve."Name",
GStatus=if (select Staying from Book where RegNum=(select max(RegNum) from Book where guest=Guests.GuestNum))=3
then 'Напус.' else 'Наст.' endif,
TourOperator=(select FullName from "dba".Contracts where ContractNo=Guests.Contract)
from "DBA".v_ArriveStayLeaveGuestBook1,"DBA".Guests,"DBA".GuestBoardPlans,"DBA".BoardPlan
,"dba".Reserve
where Guests.GuestNum=v_ArriveStayLeaveGuestBook1.GuestNum and Guests.Reservation*=ReserveNo
and Guests.GuestNum=GuestBoardPlans.Guest
and GuestBoardPlans.BoardPlan=BoardPlan.N
//[v 00.12.27]
//[v 04.06.2003] Jorko    AdultType>0
//[v 24.08.2005] Jorko  v_ArriveStayLeaveGuestBook1 vmesto v_ArriveStayLeaveGuestBook
GO

if (Exists (Select Table_name from SysTable where Table_name='v_UsedServicesByContract')) then
  Drop View v_UsedServicesByContract
end if
GO

CREATE VIEW "DBA"."v_UsedServicesByContract"  as select distinct g.GuestNum as GNum,
g."Name" as Guest,
b.DateRegDt as DateCredit,
(if b.IsRest=0 then 'Нощувка в стая '+cast(b.Room as varchar) else 'Почивка в стая '+cast(b.Room as varchar) endif) as Service,
b.NumLodgings as Quantity,
b.Price as PrWithoutDisc,
cur.Symbol as curKod,
Quantity*PrWithoutDisc as SumWithoutDisc,
scr.Discount as Disc,
scr.Discount as Discount,
CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
Quantity*PrWithDisc as SumWithDisc,
SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
c.FullName as Dogovor,
b.Room as RoomNum
from "dba".Book as b,"dba".Guests as g,"dba".Contracts as c,"dba".Currencies as cur,"dba".ServicesForContract as scr
where b.ContractPays=c.ContractNo
and b.DateRegDt between bvrData1 and bvrData2
and b.Guest=g.GuestNum
and b.ContractPays=scr.ContractNo
and scr.ServiceNo in(-1,-2)
and b.KodCurrency=cur.KodCurrency
and c.ContractNo<>0 union all
select distinct g.GuestNum as GNum,
g."Name" as Guest,
sc.DateCreditDt as DateCredit,
s.ServiceName,
(select Sum(NumberServices) from "dba".ServiceCredits
where Service=s.ServiceNo and GuestNo=g.GuestNum
and KodCurrency=cur.KodCurrency
and CreditNo=sc.CreditNo) as Quantity,
sc.SumCredit as PrWithoutDisc,
cur.Symbol as curKod,
(Quantity*PrWithoutDisc) as SumWithoutDisc,
(select Discount from "dba".ServicesForContract where ServiceNo=s.ServiceNo and ContractNo=c.ContractNo) as Disc,
Discount=if Disc is null then 0 else Disc endif,
CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
Quantity*PrWithDisc as SumWithDisc,
SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
c.FullName as Dogovor,
sc.RoomNum
from "dba".ServiceCredits as sc,"dba".Contracts as c,"dba".Guests as g,"dba".Services as s,"dba".Currencies as cur
where sc.ContractPays=c.ContractNo
and sc.DateCreditDt between bvrData1 and bvrData2
and sc.GuestNo=g.GuestNum
and sc.ContractPays<>0
and s.ServiceNo=sc.Service
and sc.KodCurrency=cur.KodCurrency
// v[5.09.2003 ] Jorko opravia dublirane na zapisi.
// v[12.08.2005] Jorko mahnah distinct ot vtoria select v rezultat moze da se poiaviat ednakvi redove za uslugite
// v[24.08.2005] Jorko opravia Discount da ne e Null a 0.