IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_NoshtuvkiVPeriod')))
THEN Drop view v_NoshtuvkiVPeriod
END IF

GO

create view
  DBA.v_NoshtuvkiVPeriod
  as select Sex,
    Years(BirthDateDt,current date) as Age,
    GuestNum,isnull(ExtresCount,0) as EC,isnull(ExtresType,-1) as ET,if ET = 3 then 1 else if ET = 2 then 2 else 0 endif endif as SeasonKind,
    convert(real,(convert(date,(DATEFORMAT((select startdate from DBA.PriceSeasons where SeasonNo = 1),'dd.mm.') || YEAR(DateRegDt))))) as SilenSezonBEGIN,
    convert(real,(convert(date,(DATEFORMAT((select startdate from DBA.PriceSeasons where SeasonNo = 2),'dd.mm.') || YEAR(DateRegDt))))) as SlabSezonBEGIN,
    convert(real,(convert(date,(DATEFORMAT((select enddate from DBA.PriceSeasons where SeasonNo = 1),'dd.mm.') || YEAR(dateadd(day,NumLodgings,DateRegDt)))))) as SilenSezonEND,
    convert(real,(convert(date,(DATEFORMAT((select enddate from DBA.PriceSeasons where SeasonNo = 2),'dd.mm.') || YEAR(dateadd(day,NumLodgings,DateRegDt)))))) as SlabSezonEND,
    if ContractPays <> 0 then
      if(select isGroupContract from DBA.Contracts where ContractNo = ContractPays) = 1 and(select GroupNo from DBA.Contracts where ContractNo = ContractPays) = 0 then
        1
      else
        0
      endif else
      0
    endif as IsTurOperator,
    convert(real,DateRegDt) as BD,NumLodgings+BD as ED,convert(real,bvrData1) as D1,convert(real,bvrData2)-1 as D2,
    if BD >= SilenSezonBEGIN then if ED <= SilenSezonEND then Broi else if ED > SilenSezonEND then SilenSezonEND-BD endif endif else if ED < SilenSezonBEGIN then 0 else ED-SilenSezonBEGIN endif endif as BrNoshtuvkiSilen,
    if BD >= SlabSezonBEGIN then if ED <= SlabSezonEND then Broi else if ED > SlabSezonEND then SlabSezonEND-BD endif endif else if ED < SlabSezonBEGIN then 0 else ED-SlabSezonBEGIN endif endif as BrNoshtuvkiSlab,
    if(IsTurOperator = 1 and ET = 0) or(SeasonKind = 2 and BD <= SilenSezonEND and BD >= SilenSezonBEGIN) or(SeasonKind = 1 and BD <= SlabSezonEND and BD >= SlabSezonBEGIN) then
      if BD+EC < ED then
        BD+EC
      else
        ED
      endif
    else
      if(SeasonKind = 2 and EC > BrNoshtuvkiSilen and BD < SilenSezonBEGIN and ED > SilenSezonBEGIN) then
        if BD+EC-BrNoshtuvkiSilen < ED-brNoshtuvkiSilen then
          BD+EC-BrNoshtuvkiSilen
        else
          ED
        endif
      else
        if(SeasonKind = 1 and EC > BrNoshtuvkiSlab and BD < SlabSezonBEGIN and ED > SlabSezonBEGIN) then
          if BD+EC-BrNoshtuvkiSlab < ED-BrNoshtuvkiSlab then
            BD+EC-BrNoshtuvkiSlab
          else
            ED
          endif
        else
          if Seasonkind <> 0 then
            if BD+EC < ED then
              BD+EC
            else
              ED
            endif
          else
            BD
          endif
        endif
      endif
    endif as BDE,
    if(IsTurOperator = 1 and ET = 1) or(SeasonKind = 2 and BD >= SilenSezonBEGIN and ED <= SilenSezonEND) then
      if ED-EC > BD then
        ED-EC
      else
        BD
      endif
    else
      if(SeasonKind = 2 and BD < SilenSezonBEGIN and ED >= SilenSezonBEGIN and EC > BrNoshtuvkiSilen) then
        if SilenSezonBEGIN-1 > BD then
          SilenSezonBEGIN-1
        else
          BD
        endif
      else
        if(Seasonkind = 1 and BD < SlabSezonBEGIN and ED >= SlabSezonBEGIn and EC > BrNoshtuvkiSlab) then
          if SlabSezonBEGIN-1 > BD then
            SlabSezonBEGIN-1
          else
            BD
          endif
        else
          ED
        endif
      endif
    endif as EDE,
    if(BDE > D2) or(EDE <= D1) or(D1 > D2) or(EDE < BDE) or(BDE < BD) or(EDE < BD) or(BDE = EDE) then 0
    else if(BDE <= D1) and(D2 < EDE) then D2-D1+1
      else if(BDE > D1) and(EDE <= D2) then EDE-BDE
        else if(BDE > D1) then D2-BDE+1
          else if(EDE <= D2) then EDE-D1 endif
          endif
        endif
      endif
    endif as BroiBezE,
    if(BD > D2) or(ED <= D1) or(D1 > D2) or(ED < BD) then 0
    else if(BD <= D1) and(D2 < ED) then D2-D1+1
      else if(BD > D1) and(ED <= D2) then NumLodgings
        else if(BD > D1) then D2-BD+1
          else if(ED <= D2) then ED-D1 endif
          endif
        endif
      endif
    endif as Broi,Broi-BroiBezE as BroiEkstrasi,
    if IsTurOperator = 1 then Broi else 0 endif as BroiTurOperator,
    if Age < (select MIN(FromAge) from DBA.PolAgeType) then Broi else 0 endif as BroiDeca,
    if Sex = 'F' and Age > (select ToAge from DBA.PolAgeType where Sex = 2) then Broi else 0 endif as Broijeni,
    if Sex = 'M' and Age > (select ToAge from DBA.PolAgeType where Sex = 1) then Broi else 0 endif as BroiMyje from
    DBA.Book as b join DBA.guests as g on g.GuestNum = b.Guest,DBA.Book as b join DBA.Contracts on b.ContractPays = Contracts.ContractNo where
    Hasdata = 1 order by
    GuestNum asc
    
GO

Delete from Relations where lcase(TableName) = lcase('v_NoshtuvkiVPeriod')

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_NoshtuvkiVPeriod','Sum(Broi)','Курортни Такси','Нощувки(общо)','Book','Sum(Broi)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(Broi)','Sum(qrData.Broi)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','bvrData1','Курортни такси','От дата','Book','D1',0,GetFreeNInRelations('Book', 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_NoshtuvkiVPeriod','__OPTIONS__','Курортни такси','__OPTIONS__','Book','__OPTIONS__',1,GetFreeNInRelations('Book', Null, Null, 10),'__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_NoshtuvkiVPeriod','bvrData2','Курортни такси','До дата','Book','D2',0,GetFreeNInRelations('Book', Null, Null, 10),'bvrData2','','',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_NoshtuvkiVPeriod','Sum(BroiTurOperator)','Курортни Такси','Чрез туроператор','Book','Sum(BroiTuroperator)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(BroiTurOperator)','Sum(qrData.BroiTuroperator)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','Sum(BroiBezE)','Курортни Такси','Без екстраси','Book','Sum(BroiBezE)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(BroiBezE)','Sum(qrData.BroiBezE)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','Sum(BroiEkstrasi)','Курортни Такси','Екстраси','Book','Sum(BroiEkstrasi)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(BroiEkstrasi)','sum(qrdata.BroiEkstrasi)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','Sum(BroiDeca)','Курортни Такси','Деца','Book','Sum(BroiDeca)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(BroiDeca)','Sum(qrData.BroiDeca)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','Sum(BroiMyje)','Курортни Такси','Мъже','Book','Sum(BroiMyje)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(BroiMyje)','Sum(qrData.BroiMyje)','',0,0,'','','','',150,'',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_NoshtuvkiVPeriod','Sum(Broijeni)','Курортни Такси','Жени','Book','Sum(Broijeni)',2,GetFreeNInRelations('Book', Null, Null, 10),'Sum(Broijeni)','Sum(qrData.Broijeni)','',0,0,'','','','',150,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_NoshtuvkiVPeriod'and ColName='__OPTIONS__')

Go

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_NoshtuvkiVPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;