alter table Relations
  modify BGColName varchar(100) not null

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(40);
  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,'','','','',80,'',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
  for f1 as curs1 scroll cursor for select PriceKind,Description from PriceKinds where Valid = 1 and PriceKindType in( 1,3,4,5) order by pricekind asc do
    set DescNoDash=replace(SubString(Description,0,40),'-','_');
    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,'','','','',80,'',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','IsolationRoom','Реализирани леглодни','Изолационна','Beddays used','IsolationRoom',
    2,GetFreeNInRelations('Beddays used',null,null,1),'SUM(IsolationRoom)','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','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','UnSubs','Реализирани леглодни','Несубс. наем','Beddays used','Unsubs',
    2,GetFreeNInRelations('Beddays used',null,null,1),'SUM(UnSubs)','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,'','','','',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','TotalNorm','Реализирани леглодни','Общо норматив','Beddays used','TotalNorm',
    2,GetFreeNInRelations('Beddays used',null,null,1),'Sum(TotalNorm)','Sum(qrData.Общо норматив)','',0,0,'','','','',80,'',0,0,1,today(*)) ;
  call FillRLegloDni()
end
//[07.04.2009] Nixon - dobavena kolona za Isolacionna staia
//[02.02.2010] - Milko - promeneni da vadi samo za aktivni cenorazpisi + tezi za mesec + grupi samo subdirani naemi
//[14.04.2011] - Milko - Promeneno da vzima i nesubs Naemi i za den