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;
  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') || ''',';
      for f3 as curs3 scroll cursor for select PriceKind from PriceKinds where PriceKind in (2,4,5,6,7,8) order by PriceKind asc do
        set @tmpSum=@tmpSum+GetNumBookForPKDay(bvrData1,PriceKind);
        set @sqlTemp=@sqlTemp || GetNumBookForPKDay(bvrData1,PriceKind) || ',' end for;
      set @sqlTemp=@sqlTemp || @tmpSum || ',1.10,' || 1.1*@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) order by pricekind asc do
    set @rez=@rez || '"C' || PriceKind || '" integer,' end for;
  return(@rez)
end

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
  delete from Relations where lcase(TableName) = lcase('#RLegloDni');
  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(*)) ;
  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,10),'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,10),'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,10),'Den','Sum(qrData.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) order by pricekind asc do
    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,'Реализирани леглодни',Description,'Beddays used','C' || PriceKind,2,GetFreeNInRelations('Beddays used',null,null,10),'C' || PriceKind,'Sum(qrData.C' || PriceKind || ')','',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,10),'Total','Sum(qrData.Total)','',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,10),'Sum(qrData.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,10),'Sum(qrData.TotalNorm)','','',0,0,'','','','',150,'',0,0,1,today(*)) ;
  delete from InformPermission where RelationN = 
    (select N from Relations where TableName = '#RLegloDni' and ColName = '__OPTIONS__');
  for f2 as curs2 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;
  call FillRLegloDni()
end

go