IF not (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('PriceKindTypes')))

THEN

create table PriceKindTypes(
  "N" integer not null default autoincrement,
  "Name" varchar(200),
  "LastChanged" date default today(*),
  primary key("N")
  )
end if


GO

if (select count(*) from PriceKindTypes where N=0)=0 then
insert into PriceKindTypes VALUES(0,'Не е зададен',default)
end if

GO

IF (not exists (select "role" from sys.sysforeignkey where "role"='FK_PriceKind_Types')) then

ALTER TABLE PriceKinds
ADD FOREIGN KEY "FK_PriceKind_Types" ("PriceKindType")
REFERENCES "PriceKindTypes" ("N")

END IF

GO

update PriceKinds set PriceKindType = 0 where PriceKindType is null

go

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_NewArriveStayLeaveGuestBook')))
THEN Drop view v_NewArriveStayLeaveGuestBook
END IF

GO

create view
  DBA.v_NewArriveStayLeaveGuestBook
  as select GuestNum=Guest,GuestName=min(G.Name),
    ArriveDate=Min(Book.DateRegDt),
    LastRoom=MAX(if Book.Staying = 2 then 0 else Book.Room endif),
    --Maria
    LastRoomName=MAX(if Book.Staying = 2 then '' else R.RoomName
    endif), --Maria
    NumNights=Sum(Book.NumLodgings),
    LeaveDate=Days(ArriveDate,NumNights),
    -- Yanko +
    --    RegPrice=max(Book.Price||' '||C.Symbol),
    RegPrice=Book.Price || ' ' || C.Symbol,
    -- Yanko -
    PayNightPrice=sum(Book.PayedLodgings*Book.Price*DR.ExchangeRate),
    Admin=Max(Admin.FullName),
    PriceKind=PRK.Description,
    PriceKindType=PKT.Name from
    DBA.Book,DBA.Admin,dba.Guests as G,dba.DayRates as DR,dba.Rooms as R, /*Maria*/ /*Maria*/
    dba.Currencies as C,DBA.PriceKinds as PRK,DBA.PriceKindTypes as PKT where
    --Maria
    Book.Room = R.RoomNum and
    --Maria
    Book.KodAdmin = Admin.AdminNo and
    Book.IsRest <> 1 and
    Book.Guest = G.GuestNum and
    DR.KodCurrency = Book.KodCurrency and
    DR.DateDt = Book.DateRegDt and
    C.KodCurrency = Book.KodCurrency and
    Book.PriceKind = PRK.PriceKind and
    PKT.N = PRK.PriceKindType
    group by Guest,RegPrice,PriceKind,PriceKindType
//[v. 22.06.2006]
//[v. 29.05.2008] - Maria, dobavena kolona PriceKind
//[v 27.10.2009] Milko dobavena kolona PriceKindType

go



Delete from Relations where lcase(RelationTable) = lcase('Dayly list for period')

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_DaylyListForPeriod','__Options__','Дневен лист за период','__Options__','Dayly list for period','__Options__',1,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'__Options__','','',0,0,0,0,'Printer.Orientation=1','',80,'',0,0,0,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_DaylyListForPeriod','bvrData1','Дневен лист за период','От дата','Dayly list for period','From Date',2,GetFreeNInRelations('Dayly list for period', 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_DaylyListForPeriod','bvrData2','Дневен лист за период','До дата','Dayly list for period','From Date',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'bvrData2','','',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_DaylyListForPeriod','GuestNum','Дневен лист за период','Студент №','Dayly list for period','GuestNum',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'GuestNum','','',1,0,'','','','',60,'',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_DaylyListForPeriod','Name','Дневен лист за период','Име','Dayly list for period','Name',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Name','','',0,0,'','','','',200,'',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_DaylyListForPeriod','ArriveDate','Дневен лист за период','Дата прист.','Dayly list for period','ArriveDate',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'ArriveDate','','',0,1,'','','','',80,'',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_DaylyListForPeriod','LeaveDate','Дневен лист за период','Дата замин.','Dayly list for period','LeaveDate',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'LeaveDate','','',0,0,'','','','',80,'',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_DaylyListForPeriod','LastRoomName','Дневен лист за период','Стая','Dayly list for period','LastRoomName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'LastRoomName','','',1,0,'','','','',60,'',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_DaylyListForPeriod','NumNights','Дневен лист за период','Нощувки','Dayly list for period','NumNights',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'NumNights','','',1,0,'','','','',60,'',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_DaylyListForPeriod','Price','Дневен лист за период','Цена за месец','Dayly list for period','Price',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Price','','# ### ##0.00',1,0,'','','','',80,'',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_DaylyListForPeriod','PriceKind','Дневен лист за период','Ценоразпис','Dayly list for period','PriceKind',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'PriceKind','','# ### ##0.00',0,0,'','','select Description from PriceKinds
where PriceKind > 1 and Valid = 1','',150,'',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_DaylyListForPeriod','Suma','Дневен лист за период','Платено','Dayly list for period','Suma',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(Suma)','Sum(qrData.Платено)','# ### ##0.00',1,0,'','','','',60,'',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_DaylyListForPeriod','Balance','Дневен лист за период','Остатък','Dayly list for period','Balance',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(Balance)','Sum(qrData.Остатък)','# ### ##0.00',1,0,'','','','',60,'',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_DaylyListForPeriod','Total','Дневен лист за период','Общо','Dayly list for period','Total',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(Total)','Sum(qrData.Общо)','# ### ##0.00',1,0,'','','','',60,'',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_DaylyListForPeriod','Passport','Дневен лист за период','Паспорт','Dayly list for period','Passport',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Passport','','',0,0,'','','','',150,'',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_DaylyListForPeriod','EGN','Дневен лист за период','ЕГН','Dayly list for period','EGN',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'EGN','','',0,0,'','','','',80,'',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_DaylyListForPeriod','Country','Дневен лист за период','Държава','Dayly list for period','Country',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Country','','',0,0,'','','select Description from Countries where Active=1','',80,'',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_DaylyListForPeriod','AddressData','Дневен лист за период','Местожителство','Dayly list for period','AddressData',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'AddressData','','',0,0,'','','','',150,'',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_DaylyListForPeriod','ContractName','Дневен лист за период','Договор','Dayly list for period','ContractName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'ContractName','','',0,0,'','','select FullName from Contracts where Active=1','',150,'',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_DaylyListForPeriod','Admin','Дневен лист за период','Потребител','Dayly list for period','Admin',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Admin','','',0,0,'','','','',150,'',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_DaylyListForPeriod','UName','Дневен лист за период','Университет','Dayly list for period','UName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'UName','','',0,0,'','','','',230,'',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_DaylyListForPeriod','SName','Дневен лист за период','Специалност','Dayly list for period','SName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'SName','','',0,0,'','','','',230,'',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_DaylyListForPeriod','Kurs','Дневен лист за период','Курс','Dayly list for period','Kurs',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Kurs','','',0,0,'','','','',60,'',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_DaylyListForPeriod','FN','Дневен лист за период','Факултетен номер','Dayly list for period','FN',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'FN','','',0,0,'','','','',110,'',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_DaylyListForPeriod','NumStud','Дневен лист за период','Брой студенти','Dayly list for period','NumStud',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(NumStud)','Sum(qrData.Брой Студенти)','',0,0,'','','','',110,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_DaylyListForPeriod'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_DaylyListForPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

go

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_DaylyListForPeriod')))
THEN Drop view v_DaylyListForPeriod
END IF

GO

create view
  DBA.v_DaylyListForPeriod
  as select BK.GuestNum,
    Name=BK.GuestName,
    BK.ArriveDate,
    BK.LeaveDate,
    --Maria
    --BK.LastRoom,
    BK.LastRoomName,
    --Maria
    BK.NumNights,
    Price=BK.RegPrice,
    PriceKind=BK.PriceKind,
    G.Balance,
    -- Yanko + fix_18_August_2006
    /*
    xxxxx=isnull((select sum((SC.PayedServices*sc.SumCredit)*dr.ExchangeRate)
    from "dba".ServiceCredits as sc,"dba".DayRates as dr,"dba".Resmoney as rs
    where sc.payedservices>0
    and sc.kodcurrency=dr.kodcurrency
    and sc.datecreditdt=dr.datedt and sc.GuestNo=BK.GuestNum
    and BK.GuestNum=rs.Guestno
    and rs.datemoneydt between bvrdata1 and bvrdata2),0),
    */
    xxxxx=isnull((select SUM(R.SUM*C.ExchangeRate) from
      DBA.ResMoney as R,DBA.Currencies as C,DBA.Admin as A where
      R.GuestNo = BK.GuestNum and
      R.Currency = C.KodCurrency and
      R.KodAdmin = A.AdminNo and
      R.DateMoney <> 0),
    --  AND R.DateMoneyDt between bvrdata1 and bvrdata2
    0),
    -- Suma=(BK.PayNightPrice+xxxxx+GetPayedDepositsByGuest(BK.GuestNum)),
    Suma=xxxxx,
    Total=G.Balance+Suma, -- Milko za MON p2278
    -- Yanko -
    Passport=String(G.PassportSeria,G.PassportNumber,' ',G.PassportDateDt), --Krum
    G.EGN,
    Country=C.Description,
    AddressData=G.Address,
    --Dobrin 2195 +
    G.FN,
    UName=U.FullName,
    SName=Spec.Name,
    Kurs=K.Name,
    --Dobrin -
    ContractName=Ctr.FullName,
	BK.Admin,
	NumStud = 1 
	from
    dba.v_NewArriveStayLeaveGuestBook as BK,
    dba.Guests as G,
    dba.Countries as C,
    dba.Contracts as Ctr,
    --Dobrin 2195 +
    dba.Universities as U,
    dba.Kursove as K,
    dba.Specialities as Spec where
    --Dobrin -
    /* /Krum/  BK.ArriveDate <> BK.LeaveDate and */
    --Dobrin 2195+
    U.N = G.UniN and K.N = G.Kurs and Spec.N = G.SpecNo and
    --Dobrin -
    G.GuestNum = BK.GuestNum and
    C.CountryKod = G.NativeCountry and
    G.Contract = Ctr.ContractNo and
    /* /Krum/  (DBA.DateIntersection(bvrData1,bvrData2,BK.ArriveDate,BK.LeaveDate) > 0) */
    ((BK.ArriveDate >= bvrData1 and BK.ArriveDate <= bvrData2) or(BK.LeaveDate >= bvrData1 and BK.LeaveDate <= bvrData2) or
    (BK.ArriveDate < bvrData1 and BK.LeaveDate > bvrData2))
//[v 18.04.2005] Jorko
//[v 21.06.2005] Jorko
//[v 27.06.2005] Safa
//[v 22.08.2006] Yanko
//[v 29.05.2008] Maria - dobavena kolona PriceKind (Cenorazpis)
//[v 11.02.2009] Milko - Dobavena kolona Total za MON
//[v 08.10.2009] Milko - Dobavena kolona NumStud za MON