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

GO

create view
  DBA.v_WaitingBills as
  select G.GuestNum,
    Season=0,
    GuestName=G.Name,
    Qty=(SC.NumberServices-SC.PayedServices),
    Prc=0.0,
    Price=string(SC.SumCredit,' ',CrS.Symbol),
    PriceLv=SC.SumCredit*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo = ASL.LastRoom,
    RoomNo=ASL.LastRoomName,
    --Maria
    if exists(select * from SvoServiceCreditsStn where glava = SC.CreditNo and Svoistvo = 1) 
    then (select Stoinost from SvoServiceCreditsStn where glava = SC.CreditNo and Svoistvo = 1)
    else S.ServiceName endif as ServiceName,
	G.Note,
    DateService=SC.DateCreditDt from
    dba.Guests as G,dba.ServiceCredits as SC,dba.Currencies as CrS,
    dba.v_ArriveStayLeaveGuestBook as ASL,dba.Services as S where
    SC.WhoPays = G.GuestNum and
    SC.WhoPays = ASL.GuestNum and
    SC.KodCurrency = CrS.KodCurrency and
    SC.NumberServices <> SC.PayedServices and
    SC.Service = S.ServiceNo and
    G.ServiceSum <> 0 and
    G.Status = 2 and
    Balance <> 0 and(
    (SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) union all
  /* Yanko +*/
  select GuestNum=B.Guest,
    Season=Ps.SeasonNo,
    GuestName=G.Name,
    Qty=Round(DBA.GetMonthKol(B.RegNum,Season),3),
    Prc=DBA.GetPriceForBook(B.RegNum,B.PriceKind,Season),
    Price=string(Prc,' ',Crs.Symbol),
    PriceLv=Prc*Crs.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo=B.Room,
    RoomNo=R.RoomName,ServiceName='Наем (' || Lower(Ps.Description) || ' сезон)',
    --Maria
    G.Note,
    DateService=B.DateRegDt from DBA.PriceSeasons as Ps,
    dba.Book as B,dba.Guests as G,dba.Currencies as CrS,DBA.Rooms as R where /*Maria*/ /*Maria*/
    --Maria
    B.Room = R.RoomNum and
    --Maria
    B.WhoPays = G.GuestNum and
    B.KodCurrency = CrS.KodCurrency and
    B.PayedLodgings <> B.NumLodgings and
    B.Price <> 0 and
    G.Status = 2 and
    B.WhoPays <> 0 and
    Qty <> 0.0 union all
  /* Yanko -*/
  select GuestNum=RM.GuestNo,
    Season=0,
    GuestName=G.Name,
    Qty=(-1),
    Prc=0.0,
    Price=string(RM.Sum,' ',C.Symbol),
    PriceLv=RM.Sum*C.ExchangeRate,
    Total=Qty*PriceLv,RoomNo='0',ServiceName='Депозит',
    G.Note,
    DateService=RM.DateMoneyDt from
    dba.ResMoney as RM,dba.Guests as G,dba.Currencies as C where
    RM.Status = 2 and
    RM.InvoiceNo = any(select InvoiceNo from dba.Invoices where CancelInvoiceNo = 0) and
    RM.GuestNo <> 0 and
    RM.DepositNo > 0 and
    G.GuestNum = RM.GuestNo and
    G.Balance <> 0 and
    G.Status = 2 and
    C.KodCurrency = RM.Currency
	
Go

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

GO

create view
  /*SQL-1003-0002689930*/
  DBA.v_Services
  as select Account=g.GuestNum,
    ServiceDate=DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY'),
    /*Maria +, 1728*/
    ServiceDateTime=sc.RealDateTime,
    /*Maria -*/
    if exists(select * from SvoServiceCreditsStn where glava = sc.CreditNo and Svoistvo = 1) 
    then (select Stoinost from SvoServiceCreditsStn where glava = sc.CreditNo and Svoistvo = 1)
    else S.ServiceName endif as Service,
    ServiceGroupBy=s.ServiceName,
    GuestName=g.Name,
    Country=Countries.Description,
    Agent=Contracts.ShortName,
    PayedSum=sc.PayedServices*sc.SumCredit,
    Balance=(sc.PayedServices-sc.NumberServices)*sc.SumCredit,
    AdminName=Admin.ShortName,
    --Maria
    --Room=ASL.LastRoom,
    Room=ASL.LastRoomName,
    --Maria
    Currency=(if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then(select Spacur.Symbol from DBA.SpaCurrencies as Spacur where sc.KodCurrency = Spacur.KodCurrency) else cur.Symbol endif),
    ExplanationColumn=if(sc.PBXLogN is not null or sc.KodAdmin = 1002) then 'PBX'
    else
      if(select count(A.AdminNo) from
        dba.Admin as A where
        A.AdminGroup = 0 and A.AdminNo = sc.KodAdmin) > 0 then 'POS'
      else
        if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then 'СПА'
        else
          null
        endif
      endif
    endif from
    dba.ServiceCredits as sc,
    DBA.Services as s,
    DBA.Contracts left outer join DBA.Guests as g on Contracts.ContractNo = g.Contract,
    DBA.Countries,
    DBA.Admin,
    DBA.Currencies as cur,
    DBA.v_ArriveStayLeaveGuestBook as ASL where
    sc.Service = s.ServiceNo and
    sc.GuestNo = g.GuestNum and
    Contracts.GroupNo = any(select distinct(showtype) from dba.GrContracts where GrNo = -1) and
    g.CountryArrive = Countries.CountryKod and
    sc.KodAdmin = Admin.AdminNo and
    ASL.GuestNum = g.GuestNum and
    sc.KodCurrency = cur.KodCurrency and(
    (sc.SPaReservationFk = -1) or(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1))
/*Opravia filtriraneto na data na spravkata*/
//[17.03.2005] Safa, optimizatsia
//[21.12.2007] Maria, dobavena kolonata ServiceDateTime, realnata data i chas

GO

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

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_Services','__OPTIONS__','Услуги','__OPTIONS__','Services','__OPTIONS__',1,GetFreeNInRelations('Services', 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_Services','ServiceDate','Услуги','Дата','Services','Date',2,GetFreeNInRelations('Services', Null, Null, 10),'ServiceDate','','',0,1,'','','','',80,'',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_Services','ServiceDateTime','Услуги','Реални дата и час','Services','ServiceDateTime',2,GetFreeNInRelations('Services', Null, Null, 10),'ServiceDateTime','','',0,1,'','','','',80,'',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_Services','Room','Услуги','Стая No','Services','Room #',2,GetFreeNInRelations('Services', Null, Null, 10),'Room','','',0,1,'','','','',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_Services','Account','Услуги','Кл.номер','Services','Client Number',2,GetFreeNInRelations('Services', Null, Null, 10),'Account','','',0,0,'','','','',80,'',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_Services','GuestName','Услуги','Име гост','Services','Guest Name',2,GetFreeNInRelations('Services', Null, Null, 10),'GuestName','','',0,0,'','','"SELECT ""Name"" FROM Guests GROUP BY ""Name"""','',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_Services','Country','Услуги','Държава','Services','Country',2,GetFreeNInRelations('Services', Null, Null, 10),'Country','','',0,0,'','','select Description from Countries','',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_Services','Agent','Услуги','Агент','Services','Agent',2,GetFreeNInRelations('Services', Null, Null, 10),'Agent','','',0,0,'','','select ShortName from Contracts order by ContractNo','',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_Services','Service','Услуги','Услуга - пълно име','Services','Service',2,GetFreeNInRelations('Services', Null, Null, 10),'Service','','',0,0,'','','select ServiceName from Services','',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_Services','ExplanationColumn','Услуги','Услуги от','Services','ExplanationColumn',2,GetFreeNInRelations('Services', Null, Null, 10),'ExplanationColumn','','',1,0,'','','select ''POS'' union select ''PBX'' union select ''''','',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_Services','PayedSum','Услуги','Платени','Services','Paid',2,GetFreeNInRelations('Services', Null, Null, 10),'Sum(PayedSum)','Sum(qrData.Платени)','# ##0.00',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_Services','Balance','Услуги','Баланс','Services','Balance',2,GetFreeNInRelations('Services', Null, Null, 10),'Balance','Sum(qrData.Баланс)','# ##0.00',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_Services','Currency','Услуги','Код валута','Services','Currency',2,GetFreeNInRelations('Services', Null, Null, 10),'Currency','','',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_Services','ServiceGroupBy','Услуги','Услуга','Services','ServiceGroupBy',2,GetFreeNInRelations('Services', Null, Null, 10),'ServiceGroupBy','','',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_Services','AdminName','Услуги','Админ','Services','Admin',2,GetFreeNInRelations('Services', Null, Null, 10),'AdminName','','',1,0,'','','','',100,'',0,0,1,today(*))

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_Services'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_Services'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;


go

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('NextIsTheMoved')))
THEN Drop procedure NextIsTheMoved
END IF

GO

create function
DBA.NextIsTheMoved(in @RNo integer)
returns tinyint
begin
  declare @Room integer;
  declare @Guest integer;
  declare @RoomNxt integer;
  declare @GuestNxt integer;
  declare @StayingNxt integer;
  declare @Rez tinyint;
  declare @NextRNo integer;
  set @StayingNxt=0;
  set @Rez=2;
  select Room,Guest into @Room,@Guest from Book where RegNum = @RNo;
  set @NextRNo=(select MIN(RegNum) from Book where RegNum > @RNo and Guest = @Guest and Room=@Room);
  if(select count(*) from Book where RegNum in( @RNo,@NextRNo) ) <> 2 then set @Rez=0
  end if;
  select Room,Guest,Staying into @RoomNxt,@GuestNxt,@StayingNxt from Book where RegNum = @NextRNo;
  if @Rez = 2 then
    if @StayingNxt = 1 then
      if(@Room = @RoomNxt) and(@Guest = @GuestNxt) then
        set @Rez=1
      end if
    else
      if NextIsTheMoved(@NextRNo) = 1 then
        set @Rez=1
      else
        set @Rez=0
      end if
    end if
  end if;
  return(@Rez)
end