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
  for f1 as curs1 scroll cursor for select PriceKind,Description from PriceKinds where Valid = 1 and NachinPlat = 1 and PriceKindType in( 1,3,4,5)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','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','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
//[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

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 @IsolationSum 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
      for f3 as curs3 scroll cursor for select PriceKind from PriceKinds where Valid = 1 and NachinPlat = 1 and PriceKindType in( 1,3,4,5) order by PriceKind asc do
        set @tmpSum=@tmpSum+GetNumBookForPKDay(bvrData1,PriceKind);
        set @sqlTemp=@sqlTemp || GetNumBookForPKDay(bvrData1,PriceKind) || ',' end for;
      --dobaviame isolacionna staia
      set @IsolationSum=isNull((select Sum(NumBeds) from Rooms where RoomCat = 11),0);
      set @tmpSum=@tmpSum+@IsolationSum;
      set @sqlTemp=@sqlTemp || @IsolationSum || ',';
      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
//[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

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
    select PriceKind,Description from PriceKinds where Valid = 1 and NachinPlat = 1  and PriceKindType in( 1,3,4,5) order by pricekind asc do
    set @rez=@rez || '"C' || PriceKind || '" integer,' end for;
  set @rez=@rez || '"IsolationRoom" integer, ';
  return(@rez)
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