-- Скрипт 0589
-- Справка "ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки" (като хотелски рагистър), 
-- която да дава само реалните нощувки за месеца

 IF (EXISTS (select table_name from systable where LCase(table_name)=LCase('v_OfficialRegisterRealNights'))) THEN
 Drop view v_OfficialRegisterRealNights
END IF 

go

create view
  DBA.v_OfficialRegisterRealNights
  as

select G.GuestNum,
    G.OfficialNum,
    G.VaulcherNo,
    ArriveDate,Signature='',
    G.Name,
    BirthCountry=CO.Description,
    BirthData=String(BirthDateDt,', ',G.City),
    PassportData=String(G.PassportSeria,G.PassportNumber),
    /*PassportData=String(G.PassportSeria,G.PassportNumber,', ',G.PassportDateDt,' ',G.PassportWhere),*/
    Age=Years(BirthDateDt,current date),
    ImaTaksa=DBA.IsResortTaxed(BirthCountry,BirthDateDt,G.Sex),
    AddressData=G.Address,
    RoomNo=LastRoom,
    BNights=Days(if bvrData1 > BK.ArriveDate then bvrData1 else BK.ArriveDate endif,if bvrData2 > BK.LeaveDate then BK.LeaveDate else bvrData2 endif),
    TNights=ImaTaksa*BNights,
    BedNights=DBA.Restr(BNights),
    TaxNights=DBA.Restr(TNights),
    LeaveDate=BK.LeaveDate,--convert(date,Days(BK.ArriveDate,BedNights)),
    MPSData=String(G.CarNumber,' ',G.CarModel),
    G.Reservation,
    G.Balance,
    Pol=if G.Sex <> '' then G.Sex
    /* v[17.03.2005] Jorko dobavena kolona PaspValDate*/
    else '' endif,Realpol=if G.Sex = 'M' then 'М' else 'Ж' endif,
    G.PaspValDate,
    Sex=if Pol <> '' then Realpol
    else '' endif 
from 
  DBA.v_ArriveStayLeaveGuestBook as BK,
  DBA.Guests as G,
  DBA.Countries as CO,
  DBA.Contracts as Ctr 
where
  BK.GuestNum = G.GuestNum 
  and G.NativeCountry = CO.CountryKod 
  and Mod(RoomNo,bvrOfficDivider) < bvrOfficReminder 
  --and ArriveDate >= bvrData1 
  and ArriveDate <= bvrData2 
  and bvrData1<=BK.LeaveDate
  --and bvrData2 >=ArriveDate 
  and ArriveDate <> BK.LeaveDate 
  and G.OfficialNum >= 0 
  and G.Contract = Ctr.ContractNo 
  and  (Ctr.ContractNo = 0 or Ctr.ShortName like bvrOfficContrIncl) 
  and G.HasData = 1
  
go
  
Delete from Relations where lcase(RelationTable) = lcase('HOTEL REGISTRY - Real')

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','__Options__','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','__Options__','HOTEL REGISTRY - Real','__Options__',1,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'__Options__','','',0,0,'','','Printer.Orientation=1','call CalcOfficialNum()',1,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','bvrData1','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','От дата','HOTEL REGISTRY - Real','From Date',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','bvrData2','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','До дата','HOTEL REGISTRY - Real','To Date',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','OfficialNum','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','No','HOTEL REGISTRY - Real','N',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'OfficialNum','','',1,1,'','','','',40,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','ArriveDate','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Дата прист.','HOTEL REGISTRY - Real','Accom. Date',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'ArriveDate','','',0,0,'','','','',72,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','Name','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Име','HOTEL REGISTRY - Real','Name',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'Name','','',0,0,'','','select Name from guests','',180,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','VaulcherNo','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Ваучер No','HOTEL REGISTRY - Real','VaulcherNo',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'VaulcherNo','','',1,0,'','','','',70,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','BirthCountry','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Държава','HOTEL REGISTRY - Real','Country',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'BirthCountry','','',0,0,'','','SELECT Description FROM Countries WHERE CountryKod>=0','',88,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','BirthData','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Дата и място на раждане','HOTEL REGISTRY - Real','Date and Place of Birth',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'BirthData','','',0,0,'','','','',120,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','Sex','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Пол','HOTEL REGISTRY - Real','Sex',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'Sex','','',1,0,'','','','',30,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','Age','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Възр.','HOTEL REGISTRY - Real','Adults',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'Age','','',2,0,'','','','',40,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','PassportData','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Паспорт','HOTEL REGISTRY - Real','ID Card',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'PassportData','','',0,0,'','','','',96,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','PaspValDate','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Валидност','HOTEL REGISTRY - Real','PaspValDate',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'PaspValDate','','',1,0,'','','','',70,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','AddressData','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Местожителство','HOTEL REGISTRY - Real','Residence',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'AddressData','','',0,0,'','','','',188,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','RoomNo','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Стая','HOTEL REGISTRY - Real','Room',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'RoomNo','','',2,0,'','','','',40,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','BedNights','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Нощувки','HOTEL REGISTRY - Real','Sleeps',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'SUM(BedNights)','SUM(qrData.Нощувки)','',2,0,'','','','',45,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','TaxNights','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Нощувки за такс.','HOTEL REGISTRY - Real','Sleeps for Pay',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'SUM(TaxNights)','SUM(qrData.Нощувки за такс.)','',2,0,'','','','',45,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','LeaveDate','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Дата замин.','HOTEL REGISTRY - Real','Check Out Date',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'LeaveDate','','',0,0,'','','','',72,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','MPSData','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','МПС','HOTEL REGISTRY - Real','Vehicle',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'MPSData','','',0,0,'','','','',140,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','Reservation','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Резерв.','HOTEL REGISTRY - Real','Reserv.',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'Reservation','','',0,0,'','','','',48,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('v_OfficialRegisterRealNights','Balance','ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки','Дължима сума','HOTEL REGISTRY - Real','Dept Amount',2,GetFreeNInRelations('HOTEL REGISTRY - Real', Null, Null, 10),'Balance','','# ### ##0.00',1,0,0,0,'','',100,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_OfficialRegisterRealNights'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_OfficialRegisterRealNights'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;