Delete from Relations where RelationTable='Lodgings information'

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('#OwePaidRentLodg', '__OPTIONS__', 'Дължими и платени нощувки', '__OPTIONS__', 'Lodgings information', '__OPTIONS__', 1, GetFreeNInRelations('Lodgings information',null,null,5), '__OPTIONS__', '', '', 0, 0, '', '', '', 'call FillOweRentLodg()', 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('#OwePaidRentLodg', 'bvrSpravkaY1', 'Дължими и платени нощувки', 'Година От', 'Lodgings information', 'Year From', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'bvrSpravkaY1', '', '', 0, 0, '1', '1', '', 'select YEAR(today(*))', 20, '', 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('#OwePaidRentLodg', 'bvrSpravkaM1', 'Дължими и платени нощувки', 'Месец От', 'Lodgings information', 'Month From', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'bvrSpravkaM1', '', '', 0, 0, '1', '1', '', 'select MONTH(today(*))', 20, '', 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('#OwePaidRentLodg', 'bvrSpravkaY2', 'Дължими и платени нощувки', 'Година До', 'Lodgings information', 'Year To', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'bvrSpravkaY2', '', '', 0, 0, '1', '1', '', 'select YEAR(today(*))', 20, '', 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('#OwePaidRentLodg', 'bvrSpravkaM2', 'Дължими и платени нощувки', 'Месец До', 'Lodgings information', 'Month To', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'bvrSpravkaM2', '', '', 0, 0, '1', '1', '', 'select MONTH(today(*))', 20, '', 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('#OwePaidRentLodg', 'Month', 'Дължими и платени нощувки', 'Месец', 'Lodgings information', 'Month', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Month', '', '', 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('#OwePaidRentLodg', 'DaysInMonth', 'Дължими и платени нощувки', 'Дни', 'Lodgings information', 'DaysInMonth', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'sum(DaysInMonth)', 'sum(qrData.Дни)', '', 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('#OwePaidRentLodg', 'Year', 'Дължими и платени нощувки', 'Година', 'Lodgings information', 'Year', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Year', '', '', 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('#OwePaidRentLodg', 'GNo', 'Дължими и платени нощувки', 'Клиентски номер', 'Lodgings information', 'GNo', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'GNo', '', '', 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('#OwePaidRentLodg', 'Name', 'Дължими и платени нощувки', 'Студент', 'Lodgings information', 'Name', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Name', '', '', 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('#OwePaidRentLodg', 'Uni', 'Дължими и платени нощувки', 'Университет', 'Lodgings information', 'Uni', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Uni', '', '', 0, 0, '', '', 'select FullName from Universities', '', 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('#OwePaidRentLodg', 'Spec', 'Дължими и платени нощувки', 'Специалност', 'Lodgings information', 'Spec', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Spec', '', '', 0, 0, '', '', 'select Name from Specialities', '', 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('#OwePaidRentLodg', 'Kurs', 'Дължими и платени нощувки', 'Курс', 'Lodgings information', 'Kurs', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Kurs', '', '', 0, 0, '', '', 'select Name from Kursove', '', 100, '', 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('#OwePaidRentLodg', 'FN', 'Дължими и платени нощувки', 'ФН', 'Lodgings information', 'FN', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'FN', '', '', 0, 0, '', '', '', '', 50, '', 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('#OwePaidRentLodg', 'City', 'Дължими и платени нощувки', 'Град', 'Lodgings information', 'City', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'City', '', '', 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('#OwePaidRentLodg', 'Address', 'Дължими и платени нощувки', 'Адрес', 'Lodgings information', 'Address', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Address', '', '', 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('#OwePaidRentLodg', 'Sex', 'Дължими и платени нощувки', 'Пол', 'Lodgings information', 'Sex', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'Sex', '', '', 0, 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('#OwePaidRentLodg', 'PriceKind', 'Дължими и платени нощувки', 'Ценоразпис', 'Lodgings information', 'PriceKind', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'PriceKind', '', '', 0, 0, '', '', 'select Description from PriceKinds', '', 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('#OwePaidRentLodg', 'Usluga', 'Дължими и платени нощувки', 'Услуга', 'Lodgings information', 'Usluga', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Usluga', '', '', 0, 0, '', '', 'select ServiceName from Services', '', 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('#OwePaidRentLodg', 'Kol', 'Дължими и платени нощувки', 'Количество', 'Lodgings information', 'Kol', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Sum(Kol)', 'Sum(qrData.Количество)', '', 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('#OwePaidRentLodg', 'Cena', 'Дължими и платени нощувки', 'Цена', 'Lodgings information', 'Cena', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Cena', '', '', 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('#OwePaidRentLodg', 'SumOwe', 'Дължими и платени нощувки', 'Дължима Сума', 'Lodgings information', 'SumOwe', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Sum(SumOwe)', 'Sum(qrData.Дължима Сума)', '', 0, 0, '', '', '', '', 100, '', 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('#OwePaidRentLodg', 'SumPaid', 'Дължими и платени нощувки', 'Платена Сума', 'Lodgings information', 'SumPaid', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Sum(SumPaid)', 'Sum(qrData.Платена Сума)', '', 0, 0, '', '', '', '', 100, '', 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('#OwePaidRentLodg', 'Razlika', 'Дължими и платени нощувки', 'Разлика', 'Lodgings information', 'Razlika', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Sum(Razlika)', 'Sum(qrData.Разлика)', '', 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('#OwePaidRentLodg', 'RealDatePayed', 'Дължими и платени нощувки', 'Дата плащане', 'Lodgings information', 'RealDatePayed', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'RealDatePayed', '', '', 0, 0, '', '', '', '', 90, '', 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('#OwePaidRentLodg', 'PayTypeStr', 'Дължими и платени нощувки', 'Начин на плащане', 'Lodgings information', 'PayTypeStr', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'PayTypeStr', '', '', 0, 0, '', '', 'select PayTypeName from PayTypes', '', 100, '', 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('#OwePaidRentLodg', 'RName', 'Дължими и платени нощувки', 'Стая', 'Lodgings information', 'RName', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'RName', '', '', 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('#OwePaidRentLodg', 'BrStudenti', 'Дължими и платени нощувки', 'Брой Студенти', 'Lodgings information', 'BrStudenti', 2, GetFreeNInRelations('Lodgings information',null,null,5), 'Sum(BrStudenti)', 'Sum(qrData.Брой Студенти)', '', 0, 0, '', '', '', '', 90, '', 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('#OwePaidRentLodg', 'CountryName', 'Дължими и платени нощувки', 'Държава', 'Lodgings information', 'CountryName', 0, GetFreeNInRelations('Lodgings information',null,null,5), 'CountryName', '', '', 0, 0, '', '', '', '', 120, '', 0, 0, 1, today())
  
GO

Call InsertPravaSpravki('Lodgings information');

GO

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

GO

create view DBA.v_OwePaidLodgMonth
  as select PSP.SeassonNo,
    PSP.StartDate,
    PSP.EndDate,
    GNo=B.Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select Settlements.SettleMentName from DBA.Settlements where Settlements.N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum,1),
    Cena=DBA.GetRentPriceForDate(B.RegNum,null,PSP.SeassonNo),
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings,PSP.StartDate,PSP.EndDate),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings,PSP.StartDate,PSP.EndDate),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum,if MONTH(PSP.StartDate) = bvrSpravkaM then DAY(PSP.StartDate)-1 else 0
    endif+Kol),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
    DaysInMonth=Kol,
    PayType=DBA.GetPayTypePayedRentForMonth(B.RegNum),
    PayTypeStr=(select PayTypes.PayTypeName from DBA.PayTypes where PayTypes.PayTypeN = PayType)
    from DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R,DBA.Countries,DBA.PriceSeasonPeriods as PSP
    where B.PriceKind = PK.PriceKind
    and G.GuestNum = B.Guest
    and PK.NachinPlat = 0
    and G.Kurs = K.N
    and G.SpecNo = S.N
    and(MONTH(PSP.StartDate)+12*YEAR(PSP.StartDate)) <= (bvrSpravkaM+12*bvrSpravkaY)
    and(MONTH(PSP.EndDate)+12*YEAR(PSP.EndDate)) >= (bvrSpravkaM+12*bvrSpravkaY)
    and G.UniN = U.N
    and B.KodCurrency = C.KodCurrency
    and R.RoomNum = B.Room
    and G.NativeCountry = Countries.CountryKod
    and B.DateRegDt < dateadd(month,1,"date"('01.' || bvrSpravkaM || '.' || bvrSpravkaY)) --v.1
    and dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('01.' || bvrSpravkaM || '.' || bvrSpravkaY) and Kol <> 0 // v.1
// v.1 [07.02.2012] - Milko - Fixed DateFormat