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,
    PriceKindType=BK.PriceKindType,
    City=(select SettlementName from DBA.Settlements where N = G.SettlementN),
	NomerZapoved = G.NumZN,
	Note = G.Note2
    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
    --Dobrin -
    /* /Krum/  BK.ArriveDate <> BK.LeaveDate and */
    --Dobrin 2195+
    where U.N = G.UniN and K.N = G.Kurs and Spec.N = G.SpecNo
    --Dobrin -
    and 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
//[v 27.10.2009] Milko - Dobavena nova kolona PriceKindType
//[v 04.11.2009] Milko - Dobavena kolona City

GO

if not exists(select * from Relations where RelationTable='Dayly list for period' and ColName = 'NomerZapoved') then
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', 'NomerZapoved', 'Дневен лист за период', 'Номер заповед', 'Dayly list for period', 'NomerZapoved', 2, (select MAX(N)+5 from Relations), 'NomerZapoved', '', '', 0, 0, '', '', '', '', 90, '', 0, 0, 1, '18.11.2010');
end if;

GO

if not exists(select * from Relations where RelationTable='Dayly list for period' and ColName = 'Note') then
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', 'Note', 'Дневен лист за период', 'Доп.инфо', 'Dayly list for period', 'Note', 2, (select MAX(N)+5 from Relations), 'Note', '', '', 0, 0, '', '', '', '', 120, '', 0, 0, 1, '18.11.2010');
end if;
GO