if (select count(*) from sys.systable where table_name='RealRegisters')=0
  then
    CREATE TABLE "DBA"."RealRegisters"
      (
	"RegisterName"  		varchar(20) NOT NULL,
	"RealStojnost"   		Numeric(12,2) NOT NULL,
	"Komentar"      		text NOT NULL,
	PRIMARY KEY ("RegisterName")
      )
end if;

GO

Delete from RealRegisters where RegisterName='Water'

GO

Delete from RealRegisters where RegisterName='HotWater'

GO

insert into RealRegisters(RegisterName,RealStojnost,Komentar)
                   values('Water',0.78,'');

GO

insert into RealRegisters(RegisterName,RealStojnost,Komentar)
                   values('HotWater',1.57,'');


GO

if (select count(*) from sys.systable where table_name='Water')=0
  then
     CREATE TABLE "DBA"."Water"
      (
	"Date"        		date NOT NULL,
	"AbonatNo"   		varchar(10) NOT NULL,
	"OldValue"       	integer NOT NULL default 0,
	"NewValue"       	integer NOT NULL default 0,
	"TransToServCredits"    integer default 0,
	"NumberInsertion"       integer default 0,
	PRIMARY KEY ("Date", "AbonatNo")
      )
end if;

GO

if (select count(*) from sys.systable where table_name='HotWater')=0
  then
     CREATE TABLE "DBA"."HotWater"
      (
	"Date"        		date NOT NULL,
	"AbonatNo"   		varchar(10) NOT NULL,
	"OldValue"      	integer NOT NULL default 0,
	"NewValue"       	integer NOT NULL default 0,
	"TransToServCredits"    integer default 0,
	"NumberInsertion"       integer default 0,
	PRIMARY KEY ("Date", "AbonatNo")
      )
end if;


GO

if (select count(*) from Services where ServiceNo=-1000)=0
  then
    insert into Services(ServiceNo,ServiceName,ServicePrice,KodCurrency,"Active",TipSdelka,isstandard,ServiceType,TimeSteps)
                  values(-1000,'Студена вода',(select RealStojnost from RealRegisters where RegisterName='Water'),
                   1,1,1,1,0,0);
end if;

GO

if (select count(*) from Services where ServiceNo=-2000)=0
  then
    insert into Services(ServiceNo,ServiceName,ServicePrice,KodCurrency,"Active",TipSdelka,isstandard,ServiceType,TimeSteps)
                  values(-2000,'Топла вода',(select RealStojnost from RealRegisters where RegisterName='HotWater'),
                  1,1,1,1,0,0);
end if;

GO

if (select count(*) from sys.systable where table_name='WaterAbonats')=0
  then
    CREATE TABLE "DBA"."WaterAbonats"
      (
	"WaterGauge"  		varchar(10) NOT NULL,
	"ApartName"   		varchar(20) NULL default '',
	"UserName"      	varchar(30) NULL default '',
	"Contract"      	integer default 0,
	PRIMARY KEY ("WaterGauge")
      )
end if;

GO

if (Exists (Select Table_name from SysTable where Table_name='v_Water')) then
  Drop View v_Water
end if

GO

create view "DBA".v_Water
  as select W."Date" Data,Month(W."Date") Mon,WA.ApartName,WA.UserName,W.AbonatNo,W.OldValue,W.NewValue,
       W.NewValue-W.OldValue Kol,(select RealStojnost from RealRegisters where RegisterName='Water') SinglePrice,
	   Kol*SinglePrice Suma
from Water W,WaterAbonats WA
where WA.WaterGauge=W.AbonatNo
and Data between bvrdata1 and bvrdata2
//[v 08.03.2006] Jorko created

GO

if (Exists (Select Table_name from SysTable where Table_name='v_HotWater')) then
  Drop View v_HotWater
end if

GO

create view "DBA".v_HotWater
  as select W."Date" Data,Month(W."Date") Mon,WA.ApartName,WA.UserName,W.AbonatNo,W.OldValue,W.NewValue,
       W.NewValue-W.OldValue Kol,(select RealStojnost from RealRegisters where RegisterName='HotWater') SinglePrice,
	   Kol*SinglePrice Suma
from HotWater W,WaterAbonats WA
where WA.WaterGauge=W.AbonatNo
and Data between bvrdata1 and bvrdata2
//[v 08.03.2006] Jorko created


Delete from Relations where lcase(RelationTable) = lcase('Report Cold Water')

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('v_Water','__Options__','Справка студена вода','__Options__','Report Cold Water',
 '__Options__',1,
 GetFreeNInRelations('Report Cold Water', Null, Null, 100),
 '__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('v_Water','bvrData1','Справка студена вода','От дата','Report Cold Water','bvrData1',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10)
 ,'bvrData1','','',1,0,'1','1','','select bvrData1',56,'',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('v_Water','bvrData2','Справка студена вода','До дата',
 'Report Cold Water','bvrData2',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10)
 ,'bvrData2','','',1,0,'1','1','','select bvrData2',56,'',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('v_Water','Data','Справка студена вода','Дата',
 'Report Cold Water','Date',2,
  GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'Data','','',0,0,'','','','',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('v_Water','Mon','Справка студена вода','Месец',
 'Report Cold Water','Month',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'Mon','','',1,1,'','','','',40,'',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('v_Water','ApartName','Справка студена вода','Помещение',
 'Report Cold Water','Apartment Name',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'ApartName','','',0,0,'','','select distinct ApartName from WaterAbonats','',70,'',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('v_Water','UserName','Справка студена вода','Потребител',
 'Report Cold Water','Username',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'UserName','','',0,0,'','','select distinct Username from WaterAbonats','',70,'',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('v_Water','AbonatNo','Справка студена вода','Номер водомер',
 'Report Cold Water','Abonat Number',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'AbonatNo','','',0,0,'','','','',56,'',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('v_Water','OldValue','Справка студена вода','Старо Показание м3',
 'Report Cold Water','Old Value',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'OldValue','','',1,0,'','','','',85,'',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('v_Water','NewValue','Справка студена вода','Ново Показание м3',
 'Report Cold Water','New Value',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'NewValue','','',1,0,'','','','',85,'',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('v_Water','Kol','Справка студена вода','Кол.изразх. вода м3',
 'Report Cold Water','Drain',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'Sum(Kol)','SUM(qrData.Кол.изразх. вода м3)','',1,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('v_Water','SinglePrice','Справка студена вода','Единична цена лв.',
 'Report Cold Water','SinglePrice',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'Max(SinglePrice)','','# ### ##0.00',1,0,'','','','',65,'',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('v_Water','Suma','Справка студена вода','Сума за плащане лв.',
 'Report Cold Water','Sum',2,
 GetFreeNInRelations('Report Cold Water', Null, Null, 10),
 'Sum(Suma)','SUM(qrData.Сума за плащане лв.)','# ### ##0.00',1,0,'','','','',56,'',0,0,1,today())

GO

Call InsertPravaSpravki('Report Cold Water');

GO

Delete from Relations where lcase(RelationTable) = lcase('Report Hot Water')

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('v_HotWater','__Options__','Справка топла вода','__Options__','Report Hot Water',
 '__Options__',1,
 GetFreeNInRelations('Report Hot Water', Null, Null, 100),
 '__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('v_HotWater','bvrData1','Справка топла вода','От дата','Report Hot Water','bvrData1',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10)
 ,'bvrData1','','',1,0,'1','1','','select bvrData1',56,'',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('v_HotWater','bvrData2','Справка топла вода','До дата',
 'Report Hot Water','bvrData2',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10)
 ,'bvrData2','','',1,0,'1','1','','select bvrData2',56,'',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('v_HotWater','Data','Справка топла вода','Дата',
 'Report Hot Water','Date',2,
  GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'Data','','',0,0,'','','','',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('v_HotWater','Mon','Справка топла вода','Месец',
 'Report Hot Water','Month',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'Mon','','',1,1,'','','','',40,'',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('v_HotWater','ApartName','Справка топла вода','Помещение',
 'Report Hot Water','Apartment Name',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'ApartName','','',0,0,'','','select distinct ApartName from WaterAbonats','',70,'',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('v_HotWater','UserName','Справка топла вода','Потребител',
 'Report Hot Water','Username',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'UserName','','',0,0,'','','select distinct Username from WaterAbonats','',70,'',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('v_HotWater','AbonatNo','Справка топла вода','Номер водомер',
 'Report Hot Water','Abonat Number',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'AbonatNo','','',0,0,'','','','',56,'',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('v_HotWater','OldValue','Справка топла вода','Старо Показание м3',
 'Report Hot Water','Old Value',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'OldValue','','',1,0,'','','','',85,'',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('v_HotWater','NewValue','Справка топла вода','Ново Показание м3',
 'Report Hot Water','New Value',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'NewValue','','',1,0,'','','','',85,'',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('v_HotWater','Kol','Справка топла вода','Кол.изразх. вода м3',
 'Report Hot Water','Drain',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'Sum(Kol)','SUM(qrData.Кол.изразх. вода м3)','',1,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('v_HotWater','SinglePrice','Справка топла вода','Единична цена лв.',
 'Report Hot Water','SinglePrice',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'Max(SinglePrice)','','# ### ##0.00',1,0,'','','','',65,'',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('v_HotWater','Suma','Справка топла вода','Сума за плащане лв.',
 'Report Hot Water','Sum',2,
 GetFreeNInRelations('Report Hot Water', Null, Null, 10),
 'Sum(Suma)','SUM(qrData.Сума за плащане лв.)','# ### ##0.00',1,0,'','','','',56,'',0,0,1,today())

GO

Call InsertPravaSpravki('Report Hot Water');