Delete from Relations where lcase(TableName) = lcase('#RLegloDni');
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('#RLegloDni','__OPTIONS__','Реализирани леглодни','__OPTIONS__','Beddays used','__OPTIONS__',1,GetFreeNInRelations('Beddays used', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',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( '#RLegloDni','bvrData1','Реализирани леглодни','От дата','Beddays used','From Date',
    2,GetFreeNInRelations('Beddays used',null,null,100),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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( '#RLegloDni','bvrData2','Реализирани леглодни','До дата','Beddays used','From Date',
    2,GetFreeNInRelations('Beddays used',null,null,100),'bvrData2','','',0,0,1,1,'','select bvrData1',72,'',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( '#RLegloDni','Total','Реализирани леглодни','Общо','Beddays used','Total',
    2,GetFreeNInRelations('Beddays used',null,null,1),'SUM(Total)','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( '#RLegloDni','Normativ','Реализирани леглодни','Норматив','Beddays used','Normativ',
    2,GetFreeNInRelations('Beddays used',null,null,1),'Normativ','','',0,0,'','','','',120,'',0,0,1,today(*))
go

  for f1 as curs scroll cursor for select N as grNo 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='#RLegloDni'and ColName='__OPTIONS__'  ),GrNo,1);
    end if;
  end for;
  
go


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

GO

create procedure
DBA.PrepareSpravkaRLegloDni()
begin
  declare DescNoDash varchar(30);
  delete from Relations where lcase(TableName) = lcase('#RLegloDni') and lcase(ColName) <> lcase('__OPTIONS__');
  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( '#RLegloDni','bvrData1','Реализирани леглодни','От дата','Beddays used','From Date',
    2,GetFreeNInRelations('Beddays used',null,null,1),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',0,0,1,today(*)) ;
  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( '#RLegloDni','bvrData2','Реализирани леглодни','До дата','Beddays used','From Date',
    2,GetFreeNInRelations('Beddays used',null,null,1),'bvrData2','','',0,0,1,1,'','select bvrData1',72,'',0,0,1,today(*)) ;
  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( '#RLegloDni','Den','Реализирани леглодни','Ден','Beddays used','Den',
    2,GetFreeNInRelations('Beddays used',null,null,1),'Den','','',0,0,'','','','',100,'',0,0,1,today(*)) ;
  for f1 as curs1 scroll cursor for select PriceKind,Description from PriceKinds where PriceKind in( 2,4,5,6,7,8,18) order by pricekind asc do
    set DescNoDash=replace(SubString(Description,0,30),'-','_');
    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( '#RLegloDni','C' || 
      PriceKind,'Реализирани леглодни',DescNoDash,'Beddays used','C' || PriceKind,2,GetFreeNInRelations('Beddays used',null,null,1),'SUM(C' || PriceKind || ')','Sum(qrData.'|| DescNoDash ||')','',0,0,'','','','',150,'',0,0,1,today(*)) end for;
  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( '#RLegloDni','Total','Реализирани леглодни','Общо','Beddays used','Total',
    2,GetFreeNInRelations('Beddays used',null,null,1),'SUM(Total)','Sum(qrData.Общо)','',0,0,'','','','',80,'',0,0,1,today(*)) ;
  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( '#RLegloDni','Normativ','Реализирани леглодни','Норматив','Beddays used','Normativ',
    2,GetFreeNInRelations('Beddays used',null,null,1),'Normativ','','',0,0,'','','','',120,'',0,0,1,today(*)) ;
  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( '#RLegloDni','TotalNorm','Реализирани леглодни','Общо норматив','Beddays used','TotalNorm',
    2,GetFreeNInRelations('Beddays used',null,null,1),'Sum(TotalNorm)','Sum(qrData.Общо норматив)','',0,0,'','','','',150,'',0,0,1,today(*)) ;
  call FillRLegloDni()
end

Go

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

GO

create procedure
DBA.FillRLegloDni()
begin
  declare @sqlTemp text;
  declare @tmpSum integer;
  declare @tmpD1 date;
  declare @tmpD2 date;
  declare @Normativ numeric(12,2);
  execute immediate 'delete from #RLegloDni';
  set @tmpD1=bvrData1;
  set @tmpD2=bvrData2;
  if bvrData1 <= bvrData2 then
    while(bvrData1 <= bvrData2) loop
      set @tmpSum=0;
      set @sqlTemp='insert into #RLegloDni Values(''' || DateFormat(bvrData1,'yyyy.mm.dd') || ''',';
      select isNull(Normativ,0) into @Normativ from SubsidyNormativ where PeriodStart <= bvrData1 and PeriodEnd >= bvrData1;
      for f3 as curs3 scroll cursor for select PriceKind from PriceKinds where PriceKind in(2,4,5,6,7,8,18) order by PriceKind asc do
        set @tmpSum=@tmpSum+GetNumBookForPKDay(bvrData1,PriceKind);
        set @sqlTemp=@sqlTemp || GetNumBookForPKDay(bvrData1,PriceKind) || ',' end for;
      set @sqlTemp=@sqlTemp || @tmpSum || ',' || @Normativ || ',' || @Normativ*@tmpSum || ')';
      execute immediate @sqlTemp;
      set bvrData1=DateAdd(Day,1,bvrData1)
    end loop
  end if;
  set bvrData1=@tmpD1;
  set bvrData2=@tmpD2
end
go

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

GO

create function
DBA.GetAllPKComma()
returns text
begin
  declare @rez text;
  set @rez='';
  for f1 as curs scroll cursor for
    select PriceKind,Description from PriceKinds where PriceKind in( 2,4,5,6,7,8,18) order by pricekind asc do
    set @rez=@rez || '"C' || PriceKind || '" integer,' end for;
  return(@rez)
end
go