delete from Relations where RelationTable='Free and occupied beds';

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_FreeBeds', 'OverduePeople', 'Свободни и заети легла', 'Настанени с проср.срок', 'Free and occupied beds', 'OverduePeople', 2, 50040, 'sum(OverduePeople)', 'Sum(qrData.Настанени с проср.срок)', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '25.03.2015');

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_FreeBeds', '__Options__', 'Свободни и заети легла', '__Options__', 'Free and occupied beds', '__Options__', 1, 50000, '__Options__', '', '', 0, 0, '', '', '', '', 1, '', 0, 0, 1, '18.11.2013');

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_FreeBeds', 'bvrData1', 'Свободни и заети легла', 'Дата', 'Free and occupied beds', 'Date', 2, 50005, 'bvrData2', '', '', 0, 0, '1', '1', '', 'select today(*)', 100, '', 0, 0, 1, '24.03.2015');

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_FreeBeds', 'HotelName', 'Свободни и заети легла', 'Общежитие', 'Free and occupied beds', 'HotelName', 0, 50010, 'HotelName', '', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'Room', 'Свободни и заети легла', 'Стая', 'Free and occupied beds', 'Room', 2, 50015, 'Room', 'select RoomName from Rooms', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'RoomClass', 'Свободни и заети легла', 'Категория', 'Free and occupied beds', 'RoomClass', 0, 50020, 'RoomClass', 'select ShortName from Classes where Valid=1', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'Status', 'Свободни и заети легла', 'Статус', 'Free and occupied beds', 'Status', 0, 50025, 'Status', '', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'TotalBeds', 'Свободни и заети легла', 'Легла', 'Free and occupied beds', 'TotalBeds', 2, 50030, 'sum(TotalBeds)', 'Sum(qrData.Легла)', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'NumPeople', 'Свободни и заети легла', 'Настанени', 'Free and occupied beds', 'NumPeople', 2, 50035, 'sum(NumPeople)', 'Sum(qrData.Настанени)', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '25.03.2015');

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_FreeBeds', 'FreeBeds', 'Свободни и заети легла', 'Свободни легла', 'Free and occupied beds', 'FreeBeds', 2, 50045, 'sum(FreeBeds)', 'Sum(qrData.Свободни легла)', '', 0, 0, '', '', '', '', 100, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'UnUsedBeds', 'Свободни и заети легла', 'Неизползваеми', 'Free and occupied beds', 'UnUsedBeds', 2, 50050, 'SUM(UnUsedBeds)', 'Sum(qrData.Неизползваеми)', '', 0, 0, '', '', '', '', 80, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'OverBooked', 'Свободни и заети легла', 'Легла над лимита', 'Free and occupied beds', 'OverBooked', 2, 50055, 'SUM(OverBooked)', 'Sum(qrData.Легла над лимита)', '', 0, 0, '', '', '', '', 80, '', 0, 0, 1, '01.04.2010');

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_FreeBeds', 'PercentUsed', 'Свободни и заети легла', '% заетост', 'Free and occupied beds', 'PercentUsed', 2, 50060, '(Avg(NumPeople)+Avg(UnUsedBeds))/Avg(TotalBeds)*100', 'Sum(qrData.% заетост)', '0.00', 0, 0, '', '', '', '', 80, '', 0, 0, 1, '01.04.2010');

GO

ALTER VIEW "DBA"."v_FreeBeds"
as select HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where lcase(R.RegisterName) = lcase('hotelid') and Hotel_ID = R.IntStojnost),
Room=R.RoomName,
RoomClass=max(CL.ShortName),
TotalBeds=max(R.NumBeds),
--//NumPeople=count(distinct(Guest)),
TotPeople=convert(decimal,count(distinct B.Guest)),
NumPeople=convert(decimal,count(distinct(if(dateadd(dd,B.NumLodgings,B.DateRegDt) > bvrData2) and(B.Staying <> 3) then B.Guest else null
endif))),OverduePeople=TotPeople-NumPeople,
Status=(if(max(IsFull) = 1) or(TotalBeds-NumPeople <= 0) then 'Запълнена' else 'Незапълнена' endif),
UnUsedBeds=if(Max(IsFull) = 1 and TotalBeds-NumPeople < 0) then 0 else if(Max(IsFull) = 1 and TotalBeds-NumPeople >= 0) then TotalBeds-NumPeople else 0 endif endif, --v.2
--//FreeBeds=(if((TotalBeds-NumPeople <= 0) or(max(IsFull) = 1)) then 0 else TotalBeds-NumPeople-UnUsedBeds endif),
FreeBeds=(if((TotalBeds-TotPeople <= 0) or(max(R.IsFull) = 1)) then convert(decimal,0) else convert(decimal,(TotalBeds-TotPeople-UnUsedBeds))
    endif),
PercentUsed=if TotalBeds = 0 then 0.0 else Round((convert(numeric(12,4),(NumPeople+UnusedBeds))/TotalBeds)*100,2) endif,
Overbooked=if(TotalBeds-NumPeople) < 0 then abs(TotalBeds-NumPeople) else 0 endif from --v.2
DBA.Rooms as R left outer join(dba.Book as B join dba.Guests as G on B.Guest = G.GuestNum) on
B.NumLodgings <> 0 and R.RoomNum = B.Room and(
((B.Staying = 1)) or(
(B.Staying = 2) and(dateadd(dd,B.NumLodgings,B.DateRegDt) <> bvrData2)) or(
(B.Staying = 3) and(dateadd(dd,B.NumLodgings,B.DateRegDt) <> bvrData2)) or(
(B.Staying = 3) and(dateadd(dd,B.NumLodgings,B.DateRegDt) = bvrData2) and(B.RegNum = (select Max(B2.RegNum) from dba.Book as B2 where B2.Guest = G.GuestNum)))) and
(B.DateRegDt <= bvrData2) and(((days(B.DateRegDt,B.NumLodgings) > bvrData2) and (B.Staying<>3)) or(B.Staying = 1)),
DBA.Classes as CL where
R.RoomCat = CL.Class and
R.Status <> 3 and
R.Status <> 10
group by R.RoomName order by
Room asc
-- v1[29.09.2009] - Milko - Dobavena kolona procentna zaetost
-- v2[31.03.2010] - Milko - Dobavena kolona Overbooked (nastaneni studenti nad limita)
-- v3[25.03.2015] - Nataliya - Dobavena kolona OverduePeople (nastaneni s prosrochen srok) -> promqna v iz`islenieto na nastanenite i na svobodnite legla

GO

if not exists (select * from InformPermission where RelationN=50000 and AdminGroupNo=-1) then
  insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost) 
  values((select MAX(N)+1 from InformPermission),50000,-1,1)
end if;

GO

if not exists (select * from InformPermission where RelationN=50000 and AdminGroupNo=1) then
  insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost) 
  values((select MAX(N)+1 from InformPermission),50000,1,1)
end if;

GO

if not exists (select * from InformPermission where RelationN=50000 and AdminGroupNo=2) then
  insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost) 
  values((select MAX(N)+1 from InformPermission),50000,2,1)
end if;