IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FillOweRentLodg')))
THEN Drop procedure FillOweRentLodg
END IF

GO

create procedure
DBA.FillOweRentLodg()
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentLodg;

  while ((bvrSpravkaY < bvrSpravkaY2) or ((bvrSpravkaY=bvrSpravkaY2) and (bvrSpravkaM<=bvrSpravkaM2))) loop
    insert into #OwePaidRentLodg (select 
      GNo, "Month","Year","Name",Uni,Kurs,Spec,FN,Address,City,Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,
      Razlika,RealDatePayed,BrStudenti,RName
    from
      v_OwePaidLodgMonth);
	if (bvrSpravkaM >= 12) then 
	  set bvrSpravkaM = 1;
	  set bvrSpravkaY = bvrSpravkaY + 1;
	else 
	  set bvrSpravkaM = bvrSpravkaM + 1;
	end if;
  end loop;
end
go


Delete from Relations where lcase(TableName) = lcase('#OwePaidRentLodg')

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, 10),'__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, 10),'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, 10),'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, 10),'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, 10),'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, 10),'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','Year','Дължими и платени нощувки','Година','Lodgings information','Year',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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, 10),'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, 10),'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, 10),'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, 10),'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, 10),'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, 10),'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, 10),'','','',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','RName','Дължими и платени нощувки','Стая','Lodgings information','RName',2,GetFreeNInRelations('Lodgings information', Null, Null, 10),'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, 10),'Sum(BrStudenti)','Sum(qrData.Брой Студенти)','',0,0,'','','','',90,'',0,0,1,today(*))

go

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

go