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

GO

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

  while ((bvrSpravkaY < bvrSpravkaY2) or ((bvrSpravkaY=bvrSpravkaY2) and (bvrSpravkaM<=bvrSpravkaM2))) loop
    insert into #OwePaidRentForMonth (select 
      GNo, "Month","Year","Name",Uni,Kurs,Spec,FN,Address,City,Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,
      Razlika,RealDatePayed,Koef,BrStudenti,RName,CountryName
    from
      v_OwePaidRentMonth);
	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('#OwePaidRentForMonth')

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('#OwePaidRentForMonth','__OPTIONS__','Дължим и платен наем','__OPTIONS__','Rent Information','__OPTIONS__',1,GetFreeNInRelations('Rent Information', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillOweRentForMonth()',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('#OwePaidRentForMonth','bvrSpravkaY1','Дължим и платен наем','Година От','Rent Information','Year From',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','bvrSpravkaM1','Дължим и платен наем','Месец От','Rent Information','Month From',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','bvrSpravkaY2','Дължим и платен наем','Година До','Rent Information','Year To',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','bvrSpravkaM2','Дължим и платен наем','Месец До','Rent Information','Month To',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Month','Дължим и платен наем','Месец','Rent Information','Month',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Year','Дължим и платен наем','Година','Rent Information','Year',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Name','Дължим и платен наем','Студент','Rent Information','Name',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Uni','Дължим и платен наем','Университет','Rent Information','Uni',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Spec','Дължим и платен наем','Специалност','Rent Information','Spec',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Kurs','Дължим и платен наем','Курс','Rent Information','Kurs',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','FN','Дължим и платен наем','ФН','Rent Information','FN',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','City','Дължим и платен наем','Град','Rent Information','City',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Address','Дължим и платен наем','Адрес','Rent Information','Address',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','CountryName','Дължим и платен наем','Държава','Rent Information','Country',2,GetFreeNInRelations('Rent Information', Null, Null, 10),'CountryName','','',0,0,'','','select Description from Countries','',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('#OwePaidRentForMonth','Sex','Дължим и платен наем','Пол','Rent Information','Sex',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','PriceKind','Дължим и платен наем','Ценоразпис','Rent Information','PriceKind',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Usluga','Дължим и платен наем','Услуга','Rent Information','Usluga',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Kol','Дължим и платен наем','Количество','Rent Information','Kol',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Cena','Дължим и платен наем','Цена','Rent Information','Cena',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','SumOwe','Дължим и платен наем','Дължима Сума','Rent Information','SumOwe',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','SumPaid','Дължим и платен наем','Платена Сума','Rent Information','SumPaid',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','Razlika','Дължим и платен наем','Разлика','Rent Information','Razlika',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','RealDatePayed','Дължим и платен наем','Дата плащане','Rent Information','RealDatePayed',2,GetFreeNInRelations('Rent Information', Null, Null, 10),'RealDatePayed','','',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('#OwePaidRentForMonth','RName','Дължим и платен наем','Стая','Rent Information','RName',2,GetFreeNInRelations('Rent 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('#OwePaidRentForMonth','BrStudenti','Дължим и платен наем','Брой Студенти','Rent Information','BrStudenti',2,GetFreeNInRelations('Rent Information', Null, Null, 10),'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('#OwePaidRentForMonth','Koef','Дължим и платен наем','Коефициент','Rent Information','Koef',2,GetFreeNInRelations('Rent Information', Null, Null, 10),'Sum(Koef)','Sum(qrData.Кофициент)','',0,0,'','','','',90,'',0,0,1,today(*))

Go

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

go