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

GO

create view
  DBA.v_RegServicesPeriod
  as select RegDate=if Services.ServiceNo = (select IntStojnost from Registers where RegisterName='ForfeitService') then DBA.GetDateLihva(ServiceCredits.CreditNo) else DateCreditDt endif,
    /*Maria +, 1728*/
    RealDateTime=ServiceCredits.RealDateTime,
    /*Maria -*/
    GuestName=Guests.Name,
    Account=Guests.GuestNum,
    --Maria
    --RoomNo=v_ArriveStayLeaveGuestBook.LastRoom,
    RoomNo=v_ArriveStayLeaveGuestBook.LastRoomName,
    --Maria
	AltName=(select Stoinost from SvoServiceCreditsStn where Glava=ServiceCredits.CreditNo and Svoistvo=1),
    Service=(if AltName is null then Services.ServiceName || ' в стая ' || B.Room || ' за месец ' || DBA.ImeMesec(MONTH(RegDate)) else AltName endif), --Dobrin 2195 - Promqna na imeto na uslugata
    ServiceNo=Services.ServiceNo,
    ServicesNum=ServiceCredits.NumberServices,
    /*ServiceSngPrice=if ServiceCredits.KodCurrency=2 then DayRates.ExchangeRate*ServiceCredits.SumCredit
    else ServiceCredits.SumCredit endif, */
    ServiceSngPrice=(if(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1) then(select SpaCurrencies.ExchangeRate from DBA.SpaCurrencies where ServiceCredits.KodCurrency = SpaCurrencies.KodCurrency) else Currencies.ExchangeRate endif)*ServiceCredits.SumCredit,
    AllSum=ServiceCredits.NumberServices*ServiceSngPrice,
    Plateni=ServiceCredits.PayedServices*ServiceSngPrice,
    Dogovor=Contracts.ShortName,
    Admin=Admin.ShortName,
    ShowService=DBA.ShouldShowService(ServiceCredits.CreditNo,Services.IsMonthlyBill,AllSum-Plateni,RegDate) from --Dobrin 2195 - ako e komunalna i e platena da ne se pokazva
    DBA.ServiceCredits left outer join DBA.Admin on ServiceCredits.KodAdmin = Admin.AdminNo,
    DBA.Guests, 
	DBA.Book as B,
    DBA.Services,
    DBA.Currencies,
    DBA.v_ArriveStayLeaveGuestBook,
    DBA.Contracts
where --Dobrin 2195
    ShowService = 1 and --Dobrin 2195
    ServiceCredits.Service = Services.ServiceNo and
	B.RegNum = (select max(B1.RegNum) from Book B1 where B1.Guest=Guests.GuestNum) and
    ServiceCredits.KodCurrency = Currencies.KodCurrency and
    ServiceCredits.NumberServices <> 0 and Guests.Contract = Contracts.ContractNo and
    Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and
    ServiceCredits.GuestNo = Guests.GuestNum and
    v_ArriveStayLeaveGuestBook.GuestNum = Guests.GuestNum and
    ((ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1))
	
go

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

GO

create function
DBA.GetDateLihva(in @CreditNo integer)
returns date
begin
  declare @rez date;
  declare @M varchar(5);
  declare @Y varchar(5);
  set @M=(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and svoistvo = 4);
  set @Y=(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and svoistvo = 5);
  if @M is null or @Y is null then set @rez=(select DateCreditDt from ServiceCredits where CreditNo=@CreditNo)
  else if Length(@M) = 1 then set @M='0' || @M
    end if;
    set @rez=cast('01.' || @M || '.' || @Y as date)
  end if;
  return(@rez)
end
--Dobrin 12.02.2009 created

go

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

GO

create function
DBA.ShouldShowService(in @CreditNo integer,in @IsMonthly tinyint,in @SumDiff real,in @RegDate date)
returns tinyint
begin
  declare @IsForPeriod tinyint;
  declare @D1 date;
  declare @D2 date;
  declare @DFStart date; //dateForfeit start
  declare @DFEnd date; //dateForfeit end
  declare @rez tinyint;
  set @IsForPeriod=1;
  set @D1=(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 2);
  set @D2=(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 3);
  if @D1 is null or @D2 is null then set @IsForPeriod=0
  end if;
  if @IsForPeriod = 0 then
    if @RegDate <= bvrData2 and @RegDate >= bvrData1 then
	  set @rez=1
    else 
      if @IsMonthly = 0 then
        set @rez=0
      else
        if (@SumDiff = 0) or (bvrData2 < @D1) then set @rez=0
        else set @rez=1
        end if
      end if
    end if
  else
    if DateIntersection(bvrData1,bvrData2,@D1,@D2) <> 0 then
      set @rez=1
    else
      if @IsMonthly = 0 then
        set @rez=0
      else
        if (@SumDiff = 0) or (bvrData2 < @D1) then set @rez=0
        else set @rez=1
        end if
      end if
    end if
  end if;
  if((select Service from ServiceCredits where CreditNo = @CreditNo) = (select intStojnost from Registers where RegisterName = 'ForfeitService')) and
    (exists(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 4)) and(exists(select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 5)) then
	
    set @DFStart=cast('01.' || (select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 4) || '.' || (select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 5) as date);
	set @DFEnd=dateadd(month,1,@DFStart);
    if DateIntersection(bvrData1,bvrData2,@DFStart,@DFEnd) <> 0 then
      set @rez=1
    else
      if (@SumDiff = 0) or (bvrData2 < @DFStart) then set @rez=0
      else set @rez=1
      end if
    end if
  end if;
  return(@rez)
end
--Dobrin 12.02.2009 Created za spravka Registrirani uslugi. Vryshta 1 ako trqbva da se pokaje uslugata i 0 ako ne trqbva

go