IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Hotels') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Hotels"
ADD "LastChanged" timestamp not null default current timestamp
END IF

GO
IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigUpdateRowHotels')))
THEN Drop trigger TrigUpdateRowHotels
END IF

GO

create trigger
TrigUpdateRowHotels after update order 2 on
DBA.Hotels
referencing new as new_name
for each row 
begin
  update Hotels set
    LastChanged = Now(*) where
    Hotel_ID = new_name.Hotel_ID
end

go

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Services') and LCase(sc.column_name)= LCase('IsMonthlyBill') ) = 0 then
ALTER TABLE "Services"
ADD "IsMonthlyBill" integer not null default 0
END IF

GO

IF (select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st 
where LCase(st.table_name)= LCase('Guests') and LCase(sc.column_name)= LCase('ServiceCreditsSum') ) = 0 then
  alter table Guests
    add "ServiceCreditsSum" numeric(12,4)
else
  alter table Guests
    modify "ServiceCreditsSum" numeric(12,4)
END IF
GO

if (not exists (select N from PermissionTypes where N=10317)) then
  insert into PermissionTypes (N,Name,"Default")
    values(10317,'Настанява със задна дата',1)
end if

go

if (not exists (select GroupNo from Permissions where GroupNo=1 and PermissionNo=10317)) then
  insert into Permissions(GroupNo,PermissionNo,Stoinost)
    values(1,10317,0)
end if

go

for f as scroll cursor for
  select 
    AG.N as @GroupN from AdminGroups AG
  where (not exists (select GroupNo from Permissions where GroupNo=AG.N and PermissionNo=10317))
  do
  insert into Permissions(GroupNo,PermissionNo,Stoinost)
    values(@GroupN,10317,1)
end for 

go

if (not exists(select RegisterName from Registers where RegisterName='PayChislo')) then
  insert into Registers (RegisterName,IntStojnost,Komentar) 
    values ('PayChislo',0,'Дата, до която се плаща наема')
end if

go

if (not exists(select N from SvoServiceCredits where N=4)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(4,'Месец неустойка','Служебно свойство - ако услугата е неустойка по наем - указва месеца, за който е неустойката','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

if (not exists(select N from SvoServiceCredits where N=5)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(5,'Година неустойка','Служебно свойство - ако услугата е неустойка по наем - указва годината, за която е неустойката','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

if (not exists(select N from SvoServiceCredits where N=6)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(6,'Процент неустойка','Служебно свойство - ако услугата е неустойка по наем - указва процента неустойка','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('AddForfeitsForBook'))) THEN
Drop procedure AddForfeitsForBook
END IF
GO

create procedure "DBA"."AddForfeitsForBook"(in @RegNo integer, in @CurrentDate date)
begin atomic
  declare @Price numeric(12,2);
  declare @ArticlePrice numeric(12,2);
  declare @MDays integer;
  declare @NumDays integer;
  declare @DateStart date;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @StartDateNotPayed date;
  declare @CoveredByDepositsDate date;
  declare @Interest integer;
  declare @ForfeitServiceNo integer;
  declare @ForfeitNumMonths integer;
  declare @ForfeitMonth integer;
  declare @ForfeitYear integer;
  declare @OldCreditNo integer;
  declare @OldForfeitInterest integer;
  declare @OldForfeitPayed integer;
  declare @PayChislo integer;
  declare @ForfeitName varchar(100);
  declare @DaysLate integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @TipDDS integer;
  declare @NowDate date;
  declare @Guest integer;
  declare @AlternativeName varchar(100);
  declare @TempArticlePrice decimal(12,2);
  declare @ForfeitPrice decimal(12,2);
  declare @ForfeitTipDDS integer;
  declare @ForfeitCreditNo integer;
  declare @KodCurrency integer;
  declare @IteratorMonths integer;

  select 
       B.Room, R.RoomName,PK.NachinPlat, B.Price, isNull(PK.ServiceNo,-1), dateadd(day,B.PayedLodgings,B.DateRegDt),
	   B.WhoPays, KodCurrency, B.NumLodgings-B.PayedLodgings
	   into @RoomNo,@RoomName,@NachinPlat, @Price, @ServiceNoLodging, @StartDateNotPayed,
	   @Guest, @KodCurrency, @NumDays
    from 
	   Book B, Rooms R, PriceKinds PK
    where 
	   B.RegNum=@RegNo and B.Room=R.RoomNum and B.PriceKind=PK.PriceKind;

  select S.ServiceNo, S.ServiceName, VS.TipDDS into @ForfeitServiceNo, @ForfeitName, @ForfeitTipDDS from Registers R,Services S, VidSdelki VS 
    where R.RegisterName='ForfeitService' and R.IntStojnost=S.ServiceNo and S.TipSdelka=VS.N;
  if @ForfeitServiceNo is null then
    set @ForfeitServiceNo=0;
  end if;
  select IntStojnost into @PayChislo from Registers where RegisterName='PayChislo';
  if @PayChislo is null then
    set @PayChislo=0;
  end if;
  if (@NachinPlat = 1) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0) then
    set @NowDate = dateadd(day,-@PayChislo,@CurrentDate);
	set @CoveredByDepositsDate=GetDateCoveredByDeposits(@RegNo);
	if (@CoveredByDepositsDate > @StartDateNotPayed) then 
	  set @StartDateNotPayed = @CoveredByDepositsDate;
	end if;
    set @ForfeitNumMonths=datediff(month,@StartDateNotPayed,@NowDate) + 1;
	if (@ForfeitNumMonths > 1) or ((@ForfeitNumMonths = 1) and datepart(day,@StartDateNotPayed) < @PayChislo) then 
	    select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
		select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
	    if @TipDDS = 1 then
	      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
	      set @TipDDS=2
	    end if;
	  
	    set @DateStart = @StartDateNotPayed;
		set @IteratorMonths = @ForfeitNumMonths;
	    while ((@NumDays > 0) and (@IteratorMonths > 0)) loop
		  set @IteratorMonths = @IteratorMonths-1;
		  delete from #ArticleLodgings;
	      select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,0,'наем',1,@TipDDS,'') into @MDays;
		  
		  select (if TipDDS=2 then Price*(1+bvrDDSPart) else Price endif) into @TempArticlePrice
		  from #ArticleLodgings;
		  
		  if (@TempArticlePrice > 0) then
			  // nachisliava neustojkata ako e nujno
			  // izchisliava procenta neustojka
		      set @Interest=6;
			  if @ForfeitNumMonths=1 then 
			    set @DaysLate = datepart(day,@NowDate);
			    if @DaysLate > 0 then
		          if @DaysLate > 6 then
				    set @Interest=6;
				  else
				    set @Interest=@DaysLate;
				  end if;
			    else
				  set @Interest = 0;
			    end if; 
			  end if; // @ForfeitNumMonths=1
			  
			  if (@Interest > 0) then 
				  set @ForfeitMonth=month(@DateStart);
				  set @ForfeitYear=year(@DateStart);
				  // proveriava dali veche e nachislena neustojka
				  set @OldCreditNo=null;
				  select 
				    SC.CreditNo, SC.PayedServices, convert(integer,SSCInterest.Stoinost) into @OldCreditNo, @OldForfeitPayed, @OldForfeitInterest
				  from ServiceCredits SC, SvoServiceCreditsStn SSCYear, SvoServiceCreditsStn SSCMonth, SvoServiceCreditsStn SSCInterest
				  where 
				    SC.Service = @ForfeitServiceNo and WhoPays=@Guest
				    and SC.CreditNo = SSCYear.Glava and SSCYear.Svoistvo=5 and SSCYear.Stoinost=convert(varchar(100),@ForfeitYear)
				    and SC.CreditNo = SSCMonth.Glava and SSCMonth.Svoistvo=4 and SSCMonth.Stoinost=convert(varchar(100),@ForfeitMonth)
				    and SC.CreditNo = SSCInterest.Glava and SSCInterest.Svoistvo=6;
					
				  if (@OldCreditNo is not null) then
				    if (@OldForfeitPayed = 0) and (@OldForfeitInterest <> @Interest) then 
				      delete from ServiceCredits where CreditNo=@OldCreditNo;
					else
					  set @Interest=0;
					end if;
				  end if;
				  
				  // dobavia lihvata vuv servicecredits
				  if (@Interest > 0) then

	 		        set @AlternativeName=@ForfeitName||' '||ImeMesec(month(@DateStart))||' в стая '||@RoomName;
					set @ForfeitPrice=Round(@Interest*@TempArticlePrice/100,2);
					
					set @ForfeitCreditNo = GetCounter('CreditCounter');
					
				    insert into ServiceCredits ("CreditNo","DateCredit","GuestNo","Service","SumCredit","KodCurrency","KodAdmin",
					    "NumberServices","PayedServices","WhoPays","GroupPays","ContractPays","RoomPays","RoomNum")
					values (@ForfeitCreditNo,FloatToDate(Now()),@Guest,@ForfeitServiceNo,@ForfeitPrice,1,bvrUserN,1,0,@Guest,0,0,0,@RoomNo);
					
					// dobavia dopulnitelnite svojstva
					// alternativno ime
					insert into SvoServiceCreditsStn(Glava,Svoistvo,Stoinost)
					  values(@ForfeitCreditNo, 1, @AlternativeName);
					// mesec
					insert into SvoServiceCreditsStn(Glava,Svoistvo,Stoinost)
					  values(@ForfeitCreditNo, 4, @ForfeitMonth);
					// godina
					insert into SvoServiceCreditsStn(Glava,Svoistvo,Stoinost)
					  values(@ForfeitCreditNo, 5, @ForfeitYear);
					// procent lihva
					insert into SvoServiceCreditsStn(Glava,Svoistvo,Stoinost)
					  values(@ForfeitCreditNo, 6, @Interest);
				  end if;
			  end if; // @Interest > 0
		  end if; // (@TempArticlePrice > 0)
		  
	      set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
	      set @NumDays=@MDays;
	    end loop;
    end if; // @ForfeitNumMonths > 1 or ...
  end if; //(@NachinPlat = 1) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0)
end

go

-- Admin

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Admin') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Admin"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedAdmin')))
THEN Drop trigger TrigLastChangedAdmin
END IF

GO

create trigger
TrigLastChangedAdmin before update order 2 on
DBA."Admin"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- Rooms
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Rooms') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Rooms"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedRooms')))
THEN Drop trigger TrigLastChangedRooms
END IF

GO

create trigger
TrigLastChangedRooms before update order 2 on
DBA."Rooms"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- Guests
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Guests') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Guests"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedGuests')))
THEN Drop trigger TrigLastChangedGuests
END IF

GO

create trigger
TrigLastChangedGuests before update order 3 on
DBA."Guests"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- Book
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Book') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Book"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedBook')))
THEN Drop trigger TrigLastChangedBook
END IF

GO

create trigger
TrigLastChangedBook before update order 3 on
DBA."Book"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- Invoices
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Invoices') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Invoices"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedInvoices')))
THEN Drop trigger TrigLastChangedInvoices
END IF

GO

create trigger
TrigLastChangedInvoices before update order 3 on
DBA."Invoices"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- Articles
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Articles') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Articles"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedArticles')))
THEN Drop trigger TrigLastChangedArticles
END IF

GO

create trigger
TrigLastChangedArticles before update order 3 on
DBA."Articles"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- ResMoney
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('ResMoney') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "ResMoney"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedResMoney')))
THEN Drop trigger TrigLastChangedResMoney
END IF

GO

create trigger
TrigLastChangedResMoney before update order 3 on
DBA."ResMoney"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- ServiceCredits
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('ServiceCredits') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "ServiceCredits"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedServiceCredits')))
THEN Drop trigger TrigLastChangedServiceCredits
END IF

GO

create trigger
TrigLastChangedServiceCredits before update order 6 on
DBA."ServiceCredits"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- SvoServiceCreditsStn
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('SvoServiceCreditsStn') and LCase(sc.column_name)= LCase('LastChanged') ) > 0 then
ALTER TABLE "SvoServiceCreditsStn"
drop "LastChanged"
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedSSCStn')))
THEN Drop trigger TrigLastChangedSSCStn
END IF

GO

create trigger
TrigLastChangedSSCStn after update,insert order 3 on
DBA."SvoServiceCreditsStn"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  update ServiceCredits set LastChanged=Now(*) 
    where CreditNo=new_name.Glava
end

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedSSCStnDel')))
THEN Drop trigger TrigLastChangedSSCStnDel
END IF

GO

create trigger
TrigLastChangedSSCStnDel before delete order 4 on
DBA."SvoServiceCreditsStn"
referencing new as old_name
for each row /* WHEN( search_condition ) */
begin
  update ServiceCredits set LastChanged=Now(*) 
    where CreditNo=old_name.Glava
end

go

-- Reserve
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('Reserve') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "Reserve"
ADD "LastChanged" timestamp not null default current timestamp
END IF

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedReserve')))
THEN Drop trigger TrigLastChangedReserve
END IF

GO

create trigger
TrigLastChangedReserve before update order 3 on
DBA."Reserve"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  set new_name.LastChanged = Now(*);
end

go

-- ReservePlan
IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedReservePlan')))
THEN Drop trigger TrigLastChangedReservePlan
END IF

GO

create trigger
TrigLastChangedReservePlan after insert,update order 3 on
DBA."ReservePlan"
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  update Reserve set
    LastChanged = Now(*) where
    ReserveNo = new_name.ReserveNo
end

go

IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigLastChangedReservePlanDelete')))
THEN Drop trigger TrigLastChangedReservePlanDelete
END IF

GO

create trigger
TrigLastChangedReservePlanDelete before delete order 3 on
DBA."ReservePlan"
referencing old as old_name
for each row /* WHEN( search_condition ) */
begin
  update Reserve set
    LastChanged = Now(*) where
    ReserveNo = old_name.ReserveNo
end

go

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

GO

create function
DBA.FillLodgingsForBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50),in @RRDate date)
returns integer
// Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles
// na noshtuvka(pochivka) i uslugite ot paketa, za opredelen zapis ot Book
// Tia se vika ot PrepareGuestBill, PrepareGroupBill i PrepareReserveBill
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @ArticlePrWithDDS double; //nt
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @I integer;
  declare @Disc double;
  --Maria
  declare @RoomName varchar(5);
  --Maria
  --Dobrin
  declare @LodgNum integer;
  declare @RealArticleQty real;
  declare @IsForMonth tinyint;
  --Dobrin
  select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice
    from
    Packages,Currencies,Services,VidSdelki where
    Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and
    Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
  if @PackagePrice is null then
    set @PackagePrice=0;
    set @PackagePrWithDDS=0
  else
    set @PackagePrWithDDS=@PackagePrice
  end if;
  call FillArticleLodgings(@RegNo,@PackagePrice,0,@place);
  set @I=1;
  for f2 as curs2 scroll cursor for
    select Name as @LArticleName,RealLodgingsNum as @LRealLodgings,Price as @LArticlePrice,Qty as @LQty,
      MaxQty as @LMaxQty,MinQty as @LMinQty,ZIndex as @LZIndex,TipDDS as @LTipDDS, ServiceNoLodging as @ServiceNoLodging from
      #ArticleLodgings order by ZIndex do
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,RealLodgNum,ZIndex) values( 
      @ArticleNumber+@I,@LArticleName,'бр.',@LQty,@LMaxQty,@LMinQty,@LArticlePrice,0,@RegNo,0,@LArticlePrice*@LQty,
      @LTipDDS,@ServiceNoLodging,round(@LArticlePrice,2),
      round(@LArticlePrice*@LQty,2),@RRDate,@LRealLodgings,@LZIndex) ;
    set @I=@I+1 end for;
  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services where
      RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Packages.RegNo = Book.RegNum and Book.RegNum = @RegNo; //nt
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;
    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=2
    end if;
    set @ArticlePrWithDDS=@ArticlePrice; //nt
    --Maria +, dopulniteln uslugi
    set @ServiceName=@ServiceName || @place || cast(@Room as varchar(5));
    --Maria -
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ;
    set @I=@I+1;
    set @Disc=0 end for;
  return(@I)
end
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
//[v 2008.12.10 - MON.2] - Nixon - да подрежда

go

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

GO

create procedure DBA.ChangeBookPrice(in @RegNo integer,in @RoomPrice decimal(12,2),in @PriceCurrency integer,in @PriceKind integer,in @KodAdmin integer,in @HasPayed integer)
/* This procedure changes Price of one row in Book if there are not Payed Lodgings
or else inserts new record with New Price */
begin atomic
  declare @NewRegNO integer;
  //
  if @HasPayed = 0 then
    update Book set Price = @RoomPrice,KodCurrency = @PriceCurrency, PriceKind=@PriceKind where RegNum = @RegNo;
    call InsertPackage(@RegNo)
  else //Ako ima plateni noshtuvki se razdelia na dva zapisa
    for f as curs scroll cursor for
      select DateReg+PayedLodgings as @NewDateReg,NumLodgings-PayedLodgings as @NewNumLodgings,Room as @Room,
        Guest as @Guest,WhoPays as @WhoPays,GroupPays as @GroupPays,ContractPays as @ContractPays from Book where
        RegNum = @RegNo do
      //vmukva nov zapis v Book
      set @NewRegNo=GetCounter('BookCounter');
      message 'Ok' type info to console;
      insert into Book( RegNum,Datereg,NumLodgings,PayedLodgings,Payed,Staying,PriceKind,KodAdmin,Room,Price,
        Guest,WhoPays,KodCurrency,GroupPays,ContractPays,IsRest) values( 
        @NewRegNo,@NewDateReg,@NewNumLodgings,0,0,1,@PriceKind,@KodAdmin,@Room,@RoomPrice,@Guest,@WhoPays,
        @PriceCurrency,@GroupPays,@ContractPays,0) ;
      //Updejtvva staria zapis
      update Book set NumLodgings = PayedLodgings,Staying = 2 where RegNum = @RegNo end for;
    call InsertPackage(@NewRegNo)
  end if
end
//[v 2008.12.12 MON] Nixon - dobaveno e da smenia cenorazpisa ako niama plateni
go

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

GO

create function DBA.GetNumLodgingsInPeriod(in @RegNum integer, in @StartDate date,in @EndDate date)
returns integer
begin
  declare @DateReg date;
  declare @NumLodgings integer;
  declare @StartStaying date;
  declare @EndSTaying date;
  
  declare @TotalStaying integer;
  
  select B.DateRegDt,B.NumLodgings into @DateReg, @NumLodgings	from  Book B where RegNum=@RegNum;
  
  if @DateReg is null then 
    return 0;
  end if;
  
  set @StartStaying = @DateReg;
  set @EndStaying = dateadd(day,@NumLodgings-1,@DateReg);
  
  set @TotalStaying = 0;
  
  if (@StartStaying <= @EndDate) and (@EndStaying >= @StartDate) then 
	  if @StartStaying < @StartDate then
		set @StartStaying = @StartDate;
	  end if;
	  
	  if @EndStaying > @EndDate then
		set @EndStaying = @EndDate;
	  end if;
	  
	  set @TotalStaying=datediff(day,@StartStaying, @EndStaying) + 1;
  end if;
  
  return @TotalStaying;
end
go

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

GO

create function DBA.GetRoomFactorRazpredelenie(in @RoomNum integer, in @StartDate date,in @EndDate date)
returns numeric(12,2)
begin
  declare @TotalDays integer;
  declare @TotalStaying integer;
  declare @Factor numeric(12,2);
  
  set @TotalDays = datediff(day,@StartDate, @EndDate) + 1;
  if @TotalDays <= 0 then 
    return 0;
  end if;
  set @Factor = 0;
  
  for f as curs scroll cursor for
    select 
	  B.RegNum as @RegNum,
	  B.DateRegDt as @DateReg,
	  B.NumLodgings as @NumLodgings
	from  
	  Book B
	where  
	  B.Staying in (1,2)
	  and B.Room=@RoomNum 
	do
	  set @TotalStaying = GetNumLodgingsInPeriod(@RegNum,@StartDate,@EndDate);
	  if (@TotalStaying >0) then
		  set @Factor=@Factor + convert(numeric(12,2),@TotalStaying)/@TotalDays;
	  end if;
  end for;
  
  return @Factor;
end
//[2008_12_22 MON] Nixon - created
go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillArticleLodgings'))) THEN
Drop procedure FillArticleLodgings
END IF
GO

create procedure "DBA"."FillArticleLodgings"(in @RegNo integer, in @PackagePrice numeric(12,2), in @FillAll integer, in @place varchar(50))
begin
  declare @Price numeric(12,2);
  declare @ArticlePrice numeric(12,2);
  declare @KodCurrency integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @NumDays integer;
  declare @DateReg date;
  declare @TipDDS integer;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @IsRest integer;
  declare @ArticleName varchar(100);
  declare @ServiceName varchar(40);
  declare @DateStart date;
  declare @MDays integer;
  
  delete from #ArticleLodgings;
  select 
     B.Price, B.KodCurrency, B.Room, PK.NachinPlat, isNull(PK.ServiceNo,-1), B.NumLodgings-B.PayedLodgings, dateadd(day,B.PayedLodgings,B.DateRegDt), B.IsRest into 
	 @Price, @KodCurrency, @RoomNo, @NachinPlat, @ServiceNoLodging, @NumDays, @DateReg, @IsRest
  from Book B, PriceKinds PK
  where B.RegNum=@RegNo and B.PriceKind=PK.PriceKind;
  if (@NumDays > 0) then 
      if @ServiceNoLodging=0 then 
	    set @ServiceNoLodging=-1;
	  end if;
	  select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
	  select ServiceName into @ServiceName from Services where ServiceNo=@ServiceNoLodging;
		
	  select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
	  if @TipDDS = 2 then
	    set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
	    set @TipDDS=1
	  end if;
	  set @ArticlePrice=@ArticlePrice-@PackagePrice;
	  if @TipDDS = 1 then
	    set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
	    set @TipDDS=2
	  end if;
	  set @RoomName=(select RoomName from Rooms where RoomNum=@RoomNo);
	  if @NachinPlat=0 then 
	    if @IsRest=1 then 
	      set @ArticleName='Почивка ' || @place || @RoomName +' за '+dateformat(@DateReg,'Dd.Mm')
	    else
	      set @ArticleName=@ServiceName || ' ' || @place || @RoomName +' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(dateadd(day,@NumDays,@DateReg),'Dd.Mm')
	    end if;
		insert into #ArticleLodgings (RegNum,"Name",RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS)
		  values (@RegNo,@ArticleName,0,@ArticlePrice,@NumDays,@NumDays,0,0,@TipDDS);
	  else // @NachinPlat =1 
	    // vurti cikul mesec po mesec i populva tablicata za vseki mesec
		set @DateStart = @DateReg;
	    while (@NumDays > 0) loop
		  select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,@PackagePrice,@ServiceName,@FillAll,@TipDDS,@place) into @MDays;
		  set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
		  set @NumDays=@MDays;
		end loop;
	  end if; // @NachinPlat=1
	  
	  update #ArticleLodgings set ServiceNoLodging=@ServiceNoLodging where ServiceNoLodging is null;
	end if // @NumDays > 0
end

go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillSingleArticleLodging'))) THEN
Drop procedure FillSingleArticleLodging
END IF
GO

create function "DBA"."FillSingleArticleLodging"(in @RegNo integer, in @DateReg date, in @NumDays integer, in @ArticlePrice numeric(12,2), in @PackagePrice numeric(12,2), in @ServiceName varchar(40), in @FillAll integer, in @TipDDS integer, in @place varchar(50))
returns integer
begin
  declare @R integer;
  declare @ArticleName varchar(100);
  declare @DateStart date;
  declare @DateEnd date;
  declare @IsFullMonth integer;
  declare @RealLodgings integer;
  declare @DaysInMonth integer;
  declare @ArtPrice numeric(12,2);
  declare @Qty integer;
  declare @ZIndex integer;
  declare @RoomName varchar(6);
  
  set @IsFullMonth=1;
  set @ArtPrice = @ArticlePrice;
  
  select R.RoomName into @RoomName from Book B, Rooms R where B.RegNum=@RegNo and B.Room=R.RoomNum;
  
  // setva nachalnata data
  set @DateStart=@DateReg;
  if day(@DateStart) <> 1 then
    set @IsFullMonth=0;
  end if;
  
  // setva krajnata data
  set @DateEnd=dateadd(day,-day(@DateReg)+1,@DateReg); // setva DateEnd na purviat den ot meseca na nachalnata data
  set @DateEnd=dateadd(month,1,@DateEnd); // dobavia 1 mesec za da stane purviat den ot seldvashtiat mesec na nachalnata data
  if @DateEnd>dateadd(day,@NumDays,@DateReg) then
    set @DateEnd=dateadd(day,@NumDays,@DateReg);
    set @IsFullMonth=0;
  end if;

  //izchisliava broiat dni
  set @RealLodgings = datediff(day,@DateStart,@DateEnd);
  
  // setva imeto na uslugata
  set @ArticleName=@ServiceName||' '||ImeMesec(month(@DateStart))||@place||@RoomName;
  if (@IsFullMonth=0) then 
    set @DaysInMonth=day(dateadd(day,-day(@DateStart),dateadd(month,1,@DateStart)));
	set @ArticleName=@ArticleName||' '||@RealLodgings||'/'||@DaysInMonth;
	set @ArtPrice=(@ArticlePrice*@RealLodgings)/@DaysInMonth;
  end if;
  
  // izchisliava dali da e po podrazbirane izbrana uslugata za plashtane ili ne
  set @Qty=1;
  if (@FillAll=0) then
    if (month(@DateStart) > month(now()) and year(@DateStart) = year(now()) ) or (year(@DateStart)>year(now())) then
	  set @Qty=0;
	end if;
  end if;

  // vkarva ZIndex za da ne moje naprimer da se plati naem za oktomvri predi da e 
  // platen naema za septemvri - tova se pravi vuv Bills formata
  select Max(ZIndex)+1 into @ZIndex from #ArticleLodgings;
  if @ZIndex  is null then 
    set @ZIndex=1;
  end if;
  // vkarva uslugata
  insert into #ArticleLodgings (RegNum,"Name",RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS)
     values (@RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@TipDDS);
  
  // namalia @NumDays sus realnite noshtuvki i vrushta rezultata
  set @R=@NumDays-@RealLodgings;
  return (@R);
end;

go

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

GO

create procedure
DBA.FillServicesForBill(in @ArticleNumber integer,in @ArticleName varchar(40),in @Qty integer,in @ArticlePrice double,in @CreditNo integer,in @TipDDS integer,in @ServiceNo integer,in @Discount double,in @RegDate date)
// Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles
// na opredelen red ot ServiceCredits
begin
  declare @MaxQty integer;
  declare @MinQty integer;
  declare @ArticlePrWithDDS double; //nt
  declare @NameToInsert varchar(40);
  declare @RoomName varchar(40);
  
  message 'tuk sme pri FillServicesForBill @RegDate=' || @RegDate type info to console;
  if @Qty < 0 then
    set @MaxQty=0;
    set @MinQty=@Qty
  else
    set @MaxQty=@Qty;
    set @MinQty=0
  end if;
  set @ArticlePrWithDDS=@ArticlePrice; //nt
  message 'Cenata s dds - >'+cast(@ArticlePrWithDDS as varchar) type info to console;
  if @TipDDS = 1 then
    set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1);
    set @TipDDS=2
  end if;
  
  // vzima alternativno ime ako e zadadeno
  set @NameToInsert=isNull((select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and Svoistvo=1),'');
  if @NameToInsert = '' then 
    set @NameToInsert = @ArticleName;
  end if;
  
  insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
    CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@NameToInsert,'бр.',@Qty,@MaxQty,@MinQty,@ArticlePrice,@Discount,0, //nt
    //   CreditNo,ArticleSum,TipDDS,ServiceNo) values(@ArticleNumber,@NameToInsert,'бр.',@Qty,@MaxQty,@MinQty,@ArticlePrice,0,0,
    @CreditNo,((@ArticlePrice*@Qty)-((@ArticlePrice*@Qty)*@Discount)/100),@TipDDS,@ServiceNo,@ArticlePrWithDDS,
    ((@ArticlePrWithDDS*@Qty)-((@ArticlePrWithDDS*@Qty)*@Discount)/100),@RegDate) 
end
//[v 18.02.2003]
//[v 19.12.2008] Nixon - MON - dobaveno da pokazva alternativno ime ako e zadadeno

go

if (not exists(select RegisterName from Registers where RegisterName='ForfeitService')) then
  insert into Registers (RegisterName,IntStojnost,Komentar) 
    values ('ForfeitService',0,'Номер на услуга от Services, която се ползва като неустойка при плащане на наем')
end if

go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetDateCoveredByDeposits'))) THEN
Drop procedure GetDateCoveredByDeposits
END IF
GO

create function "DBA"."GetDateCoveredByDeposits"(in @RegNo integer)
returns date
begin
  declare @DateCovered date;
  declare @WhoPays integer;
  declare @Guest integer;
  declare @DepositSum decimal(12,2);
  declare @ArticlePrice decimal(12,2); 
  declare @AddedDays integer;
  
  select 
    dateadd(day,B.PayedLodgings,B.DateRegDt), B.WhoPays, B.Guest into @DateCovered, @WhoPays, @Guest
  from 
    Book B
  where B.RegNum=@RegNo;
  
  if (@Guest = @WhoPays) then
    set @DepositSum = CalcDepositSum(@Guest);
	if (@DepositSum > 0) then 
		for f as curs scroll cursor for
		  select 
		    B.RegNum as @RegNum,
			PK.NachinPlat as @NachinPlat,
			B.DateRegDt as @DateReg,
			B.PayedLodgings as @PayedLodgings
		  from 
		    Book B,PriceKinds PK 
		  where 
		    B.Guest=@Guest and B.WhoPays=@Guest and B.NumLodgings>B.PayedLodgings and B.PriceKind=PK.PriceKind
		  order by B.DateRegDt asc
		do
		 if (@DepositSum > 0) then 
		  set @DateCovered = dateadd(day,@PayedLodgings,@DateReg);
		  call FillArticleLodgings(@RegNum,0,1,'');
		  
		  for f1 as curs1 scroll cursor for
		    select 
			   RealLodgingsNum as @RealLodgingsNum, Qty as @Qty, ZIndex as @ZIndex, 
			   Price as @Price, TipDDS as @TipDDS
			from #ArticleLodgings 
			order by ZIndex asc
		  do 
		    if @TipDDS = 2 then 
			  set @ArticlePrice=@Price*(1+bvrDDSPart);
			else
			  set @ArticlePrice=@Price;
			end if;
			
			if (@ArticlePrice <> 0) then
				if (@NachinPlat = 0) then // Ako se plashta na den
				  set @AddedDays = convert(integer,(@DepositSum/@ArticlePrice)+0.03);
				  if @AddedDays > @Qty then
				    set @AddedDays = @Qty;
				  end if;
				  set @DepositSum=@DepositSum - @AddedDays*@ArticlePrice;
				  if @DepositSum < @ArticlePrice then 
				    set @DepositSum = 0;
				  end if;
				else // ako se plashta na mesec
				  set @DepositSum = @DepositSum - @ArticlePrice;
				  if (@DepositSum >0 ) then
				    set @AddedDays = @RealLodgingsNum;
				  else
				    set @AddedDays = 0;
				  end if;
				end if;
		      set @DateCovered = dateadd(day,@AddedDays,@DateCovered);
			end if;
		  end for;
	     end if; // @DepositSum > 0
		end for;
	end if; // @DepositSum > 0
  end if;
  
  return (@DateCovered);
end;

go

if (Select Count(*) from "DBA".PermissionTypes where Name='Дава отстъпки') = 0 then
  Create variable Tag_ID integer;
  Set Tag_ID = 5700;
  Delete from "DBA".PermissionTypes Where N=Tag_ID;
  Insert Into "DBA".PermissionTypes Values(Tag_ID,'Дава отстъпки',1);
  Delete from "DBA".Permissions Where PermissionNo=Tag_ID;
  for f1 as curs scroll cursor for Select N from AdminGroups do
    -- Admin ima prava
    if N=1 then Insert Into "DBA".Permissions Values(N,Tag_ID,0)
      -- ostanalite grupi niamat prava po podrazbirane
      else Insert Into "DBA".Permissions Values(N,Tag_ID,1)
    end if;
  end for;
  Drop variable Tag_ID;
end if;

GO

update Relations set TableName = '//v_VtdVauchers' where TableName= 'v_VtdVauchers'

go

update Relations set TableName = '//v_ExtraDiskForContract' where TableName= 'v_ExtraDiskForContract'

go

update Relations set TableName = '//v_StatisticsByBaseContract' where TableName= 'v_StatisticsByBaseContract'

go

update Relations set TableName = '//v_IzdadeniInvoiceOsnovniDogovori' where TableName= 'v_IzdadeniInvoiceOsnovniDogovori'

go

update Relations set TableName = '//v_Kamerierki' where TableName= 'v_Kamerierki'

go

update Relations set TableName = '//v_HotelChargeKontingentNEWRealLast' where TableName= 'v_HotelChargeKontingentNEWRealLast'

go

update Relations set TableName = '//v_HotelChargeKontingent' where TableName= 'v_HotelChargeKontingent'

go

update Relations set TableName = '//v_HotelChargeKontingentReserv' where TableName= 'v_HotelChargeKontingentReserv'

go

update Relations set TableName = '//v_HotelChargeKontingentReserv' where TableName= 'v_HotelChargeKontingentReserv'

go

update Relations set TableName = '//v_NoshtuvkiVPeriod' where TableName= 'v_NoshtuvkiVPeriod'

go

update Relations set TableName = '//v_UsedServicesByContract' where TableName= 'v_UsedServicesByContract'

go

update Relations set TableName = '//v_ExtraDiskForContract' where TableName= 'v_ExtraDiskForContract'

go

update Relations set TableName = '//v_NonPayedServicesByContract' where TableName= 'v_NonPayedServicesByContract'

go

update Relations set TableName = '//v_TelTalksNotRegistered' where TableName= 'v_TelTalksNotRegistered'

go

update Relations set TableName = '//v_CrossBaseServicesContracts' where TableName= 'v_CrossBaseServicesContracts'

go

update Relations set TableName = '//v_CrossServicesContracts' where TableName= 'v_CrossServicesContracts'

go

update Relations set TableName = '//v_HappyBirthDay' where TableName= 'v_HappyBirthDay'

go

update Relations set TableName = '//v_Union_RLR_RLRArriving' where TableName= 'v_Union_RLR_RLRArriving'

go

update Relations set TableName = '//v_FreeRooms' where TableName= 'v_FreeRooms'

go

update Relations set TableName = '//v_GuestListBySafe' where TableName= 'v_GuestListBySafe'

go

update Relations set TableName = '//v_ServicesSaif' where TableName= 'v_ServicesSaif'

go

update Relations set TableName = '//v_SpaServicesByDays' where TableName= 'v_SpaServicesByDays'

go

update Relations set TableName = '//v_SpaPayTypes' where TableName= 'v_SpaPayTypes'

go

update Relations set TableName = '//v_SpaProdajbiPoUslugi' where TableName= 'v_SpaProdajbiPoUslugi'

go

update Relations set TableName = '//v_SpaProdajbiPoUslugi' where TableName= 'v_SpaProdajbiPoUslugi'

go

update Relations set TableName = '//v_SpaServices' where TableName= 'v_SpaServices'

go

update Relations set TableName = '//v_SpaPocedures' where TableName= 'v_SpaPocedures'

go

update Relations set TableName = '//v_SpaReservations' where TableName= 'v_SpaReservations'

go

update Relations set TableName = '//v_SpaServiceList' where TableName= 'v_SpaServiceList'

go

update Relations set TableName = '//v_MinibarReport' where TableName= 'v_MinibarReport'

go

update Relations set TableName = '//v_HotWater' where TableName= 'v_HotWater'

go

update Relations set TableName = '//v_AverageNightsPerGuest' where TableName= 'v_AverageNightsPerGuest'

go

update Relations set TableName = '//v_StatisticsByContract' where TableName= 'v_StatisticsByContract'

go

update Relations set TableName = '//v_TelTalks' where TableName= 'v_TelTalks'

go

update Relations set TableName = '//v_TelTalksOfficial' where TableName= 'v_TelTalksOfficial'

go

update Relations set TableName = '//v_TelTalksGuests' where TableName= 'v_TelTalksGuests'

go

update Relations set TableName = '//v_TouristsWithTur' where TableName= 'v_TouristsWithTur'

go

update Relations set TableName = '//v_OfficialRegisterDanFak' where TableName= 'v_OfficialRegisterDanFak'

go

update Relations set TableName = '//v_ExtraDiskForContract' where TableName= 'v_ContractPrices'

go

update Relations set TableName = '//v_ExtraDiskForContract' where TableName= 'v_MemberOfVipClub'
go

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

GO

create function DBA.RoomBusessForPeriod(in @Room integer,in @FromDate date,in @ToDate date)
returns integer
begin
  declare i integer;
  declare flag integer;
  declare @Stat integer;
  declare @FlagPast integer;
  set flag=0;
  for F as curs dynamic scroll cursor for
    select FromDateDt as @FFDate,FromDateDt+NumDays as @TTDate,ReserveNo as @ResNo from
      ReservePlan where
      RoomNo = @Room do
    select Status into @Stat from Reserve where ReserveNo = @ResNo;
    select count(*) into @FlagPast from Reserve where
      ReserveNo = @ResNo and
      DateReserveDt >= today(*);
    select CheckPeriodInPeriod(@FFDate,@TTDate,@FromDate,@ToDate) into i;
    message i type info to console;
    message @FFDate || '   ' || @TTDate || '   ' || @FromDate || '   ' || @ToDate type info to console;
    if((i > 0) and(@Stat = 1) and(@FlagPast > 0)) then // @Stat=1 Reservaciata ne e otmenena
      set flag=1
    end if end for;
  return(flag)
end
//[v 24.11.2003] Jorko
//[v 31.10.2003] Jorko
//[v 15.03.2005] Jorko seldia dali reservaciata ne e otmenena i dali ne e minala
//[v 23.07.2008] gnikolov - добавено проверка и на заети стаи не по резервация
//[v 11.12.2008] Nixon - Za MON : premahnata e proverkata za stai ne po rezervacia

go