//[17.August.2006] - Yanko
// skripta zamestva v Relations (za spravkata "Statistika po strani") 'v_StatisticsByCountry' s 'v_New_StatisticsByCountry2'
// bez da iztriva v_StatisticsByCountry ot bazata

IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_New_StatisticsByCountry2'))) THEN
  Drop view v_New_StatisticsByCountry2
END IF

Go

Create View v_New_StatisticsByCountry2 as

SELECT Distinct
Country = A.Description,
UnorgAdultCount = (Select Count() from v_New_StatisticsByCountry where AdultType=0 and NativeCountry=A.NativeCountry and ContractPays=0),
UnorgChildrenCount = (Select Count() from v_New_StatisticsByCountry where AdultType<>0 and NativeCountry=A.NativeCountry and ContractPays=0),
UnourgTotal = UnorgAdultCount + UnorgChildrenCount,
OrganizedAdultCount = (Select Count() from v_New_StatisticsByCountry where AdultType=0 and NativeCountry=A.NativeCountry and ContractPays<>0),
OrganizedChildrenCount = (Select Count() from v_New_StatisticsByCountry where AdultType<>0 and NativeCountry=A.NativeCountry and ContractPays<>0),
OrganizedTotal = OrganizedAdultCount + OrganizedChildrenCount,
TotalAdults = UnorgAdultCount + OrganizedAdultCount,
TotalChildren = UnorgChildrenCount + OrganizedChildrenCount,
Total = TotalAdults + TotalChildren,
NumLodgingsAdults = IsNull((Select Sum(NumLodgings) from v_New_StatisticsByCountry where AdultType=0 and NativeCountry=A.NativeCountry),0),
NumLodgingsChildren = IsNull((Select Sum(NumLodgings) from v_New_StatisticsByCountry where AdultType<>0 and NativeCountry=A.NativeCountry),0),
TotalLodgings = NumLodgingsAdults + NumLodgingsChildren,
NewArrivedRooms = (Select Sum(NewLodgings) from v_New_StatisticsByCountry1 where Description = Country),
OccupiedRooms = (Select Count() from v_New_StatisticsByCountry1 where Description = Country)
FROM v_New_StatisticsByCountry as A
//[17.August.2006] - Yanko

Go

Delete from Relations where lcase(RelationTable) = lcase('Country Statistics')

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_New_StatisticsByCountry2','__Options__','Статистика по страни','__Options__','Country Statistics','__Options__',1,GetFreeNInRelations('Country Statistics', Null, Null, 10),'__Options__','','',0,0,'','','Printer.Orientation=1','',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_New_StatisticsByCountry2','bvrData1','Статистика по страни','От дата','Country Statistics','From Date',0,GetFreeNInRelations('Country Statistics', Null, Null, 10),'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('v_New_StatisticsByCountry2','bvrData2','Статистика по страни','До дата','Country Statistics','To Date',0,GetFreeNInRelations('Country Statistics', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',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_New_StatisticsByCountry2','Country','Статистика по страни','Държава','Country Statistics','Country',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Country','','',0,1,'','','','',120,'',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_New_StatisticsByCountry2','UnorgAdultCount','Статистика по страни','Неорг. възр.','Country Statistics','UnorgAdultCount',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'UnorgAdultCount','Sum(qrData.Неорг. възр.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','UnorgChildrenCount','Статистика по страни','Неорг. деца','Country Statistics','UnorgChildrenCount',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(WalkInChi)','Sum(qrData.Неорг. деца)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','UnourgTotal','Статистика по страни','Неорг.','Country Statistics','UnourgTotal',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(WalkInTot)','Sum(qrData.Неорг.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','OrganizedAdultCount','Статистика по страни','Орг. възр.','Country Statistics','OrganizedAdultCount',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(FITEld)','Sum(qrData.Орг. възр.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','OrganizedChildrenCount','Статистика по страни','Орг. деца','Country Statistics','OrganizedChildrenCount',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(FITChi)','Sum(qrData.Орг. деца)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','OrganizedTotal','Статистика по страни','Орг.','Country Statistics','OrganizedTotal',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(FITTot)','Sum(qrData.Орг.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','TotalAdults','Статистика по страни','Тотал възр.','Country Statistics','TotalAdults',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(TotEld)','Sum(qrData.Нощ. възр.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','TotalChildren','Статистика по страни','Тотал деца','Country Statistics','TotalChildren',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(TotChi)','Sum(qrData.Тотал деца)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','Total','Статистика по страни','Тотал','Country Statistics','Total',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(TotTot)','Sum(qrData.Тотал)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','NumLodgingsAdults','Статистика по страни','Нощ. възр.','Country Statistics','NumLodgingsAdults',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(EldNights)','Sum(qrData.Нощ. възр.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','NumLodgingsChildren','Статистика по страни','Нощ. деца','Country Statistics','NumLodgingsChildren',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(ChiNights)','Sum(qrData.Нощ. деца)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','TotalLodgings','Статистика по страни','Нощ.','Country Statistics','TotalLodgings',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(TotNights)','Sum(qrData.Нощ.)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','NewArrivedRooms','Статистика по страни','Прист. стаи','Country Statistics','NewArrivedRooms',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(ArrivedRooms)','Sum(qrData.Прист. стаи)','',1,0,'','','','',48,'',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_New_StatisticsByCountry2','OccupiedRooms','Статистика по страни','Заети стаи','Country Statistics','OccupiedRooms',2,GetFreeNInRelations('Country Statistics', Null, Null, 10),'Sum(StayedRooms)','Sum(qrData.Заети стаи)','',1,0,'','','','',48,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
    select N from Relations where TableName='v_New_StatisticsByCountry2'and ColName='__OPTIONS__'))=0 then
  f1: for f1 as curs scroll cursor for Select grNo=N 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='v_New_StatisticsByCountry2'and ColName='__OPTIONS__'  ),1,1);
    end if;
  end for;
end if;