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('RealLodgingsNum') ) = 0 then
alter table Articles
add "RealLodgingsNum" integer 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)
END IF
GO

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st 
where LCase(st.table_name)= LCase('PriceKinds') and LCase(sc.column_name)= LCase('ServiceNo') ) = 0 then
alter table PriceKinds
add "ServiceNo" integer
END IF
GO
IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st 
where LCase(st.table_name)= LCase('PriceKinds') and LCase(sc.column_name)= LCase('NachinPlat') ) = 0 then
alter table PriceKinds
add "NachinPlat" tinyint
END IF
go

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

GO

create function
DBA.CalcBalance(in @GuestNo integer)
returns decimal(12,4)
// Presmiata balansa za @GuestNo v mestna valuta (leva) po tekushtia kurs:
//   Balans = SUM(Neplateni usligi) - SUM(Depositi).
begin
  declare @D decimal(12,4);
  declare @S decimal(12,4);
  declare @B decimal(12,4);
  declare @SCS decimal(12,4);
  //
  message '-- CalcBalance for Guest=',@GuestNo type info to console;
  set @D=CalcDepositSum(@GuestNo);
  set @S=CalcServiceSum(@GuestNo);
  set @SCS=CalcServiceCreditsSum(@GuestNo);
  set @B=@S-@D;
  //message '---- CalcBalance again...',;
  update Guests set DepositSum = @D,ServiceSum = @S,Balance = @B, ServiceCreditsSum=@SCS where
    GuestNum = @GuestNo;
  //message '-- CalcBalance Done.',@GuestNo;
  return(@B)
end
//[v 00.06.23]
//[v 2008.12.10 - MON] - Nixon - da zapisva i kolonata ServiceCreditsSum
go

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

GO

create function DBA.CalcServiceSum(in @GuestNo integer)
returns decimal(12,4)
// Vryshta sumata na neplatenite uslugi za @GuestNo v mestna valuta (leva).
begin
  declare @R decimal(12,4);
  declare @SpaR decimal(12,4);
  declare @TempB decimal(12,4);
  declare @B decimal(12,4);
  declare @HumanKind integer;
  declare @DDS decimal(12,4);
  select sum(if VidSdelki.TipDDS <> 2 then
      (ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    else
      (1+bvrDDSPart)*(ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    endif) into @R
    from ServiceCredits,Currencies,Services,VidSdelki where
    ServiceCredits.WhoPays = @GuestNo and
    ServiceCredits.Service = Services.ServiceNo and
    Services.TipSdelka = VidSdelki.N and
    ServiceCredits.KodCurrency = Currencies.KodCurrency and
    ((ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1));
  if @R is null then set @R=0
  end if;
  set @B=0;
  for f as curs scroll cursor for
    select RegNum as @RegNo from Book where Book.WhoPays = @GuestNo do
	call FillArticleLodgings(@RegNo,0,1,'');
	select sum(if TipDDS=2 then Qty*Price*(1+bvrDDSPart) else Qty*Price endif) into @TempB from #ArticleLodgings;
	if @TempB is null then set @TempB=0
	end if;
	set @B = @B + @TempB;
  end for;
  return(@R+@B)
end
//[v 01.01.11]
//[v 01.07.02] - Niki
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
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('FinishBill')))
THEN Drop procedure FinishBill
END IF

GO

create function DBA.FinishBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT decimal(12,2),in @VAT decimal(12,2),in @GrandTotal decimal(12,2),in @WithoutVAT decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer,in @DDSType integer)
returns integer
//Insertva dannite za fakturata vuv Invoices i Articles i insertva parite v ResMoney
//@No e syotvetno nomer na grupa, rezervaciq, gost, staia i anulirana smetka, ako smetkata e za grupa, rezervacia, gost, staia ili anulirashta
begin atomic
  declare @InvoiceNo integer;
  declare @DepositNo integer;
  --Maria +
  --declare @TrueInvoiceNo integer;
  declare @TrueInvoiceNo numeric(10);
  --Maria -
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  declare @CreditNo integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @NullDepositCount integer;
  declare @InvoiceMask integer; //ivaylo
  declare @DepContractNo integer; //// ivaylo
  if @BillType <> 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1005
  end if;
  if @BillType = 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1004
  end if;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #Articles where ArticleQty = 0;
  //Vmukva dannite za fakturata
  set @InvoiceNo=GetCounter('InvoiceCounter');
  if @IsInvoice = 1 then
    --Maria +
    --set @TrueInvoiceNo=GetCounter('TrueInvoiceNo')
    set @TrueInvoiceNo=GetFakturaCounter('TrueInvoiceNo')
  else --Maria -
    set @TrueInvoiceNo=0
  end if;
  if @BillType = -1 then
    set @CancelInvoiceNo=@No
  else
    set @CancelInvoiceNo=0
  end if;
  set @ResMoneyStatus=1;
  select IntStojnost into @InvoiceMask from Registers where RegisterName = 'MaskaFaktura'; //ivaylo
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  //ivaylo
  insert into Invoices( InvoiceNo,Status,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,CancelInvoiceNo,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,MaskaFaktura,DDSType) values( 
    @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',1,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),1.0,@VidSmetka,@ContractNo,@InvoiceMask,@DDSType) ;
  for f1 as curs1 scroll cursor for
    select ArticleNumber as @ArticleNumber,ArticleName as @ArticleName,ArticleQty as @ArticleQty,
      ArticlePrice as @ArticlePrice,RegNo as @RegNo,CreditNo as @CreditNum,ArticleSum as @ArticleSum,
      TipDDS as @TipDDS,Discount as @Discount,ServiceNo as @Service,ArticleSumInvCurrency as @SumInvCurrency,
      RegDate as @RDate,RealLodgNum as @RLN from #Articles do //Dobrin 2087 - dobaveno @RLN
    //Ako e swobodna smetka vmukva vuv uslugite na gosta uslugite ot svobodnata smetka
    if @BillType = 5 then
      select ServiceNo into @ServiceNo from Services where ServiceNo = @Service;
      if(@ServiceNo <> 1005) and(@ServiceNo <> 1004) and(@ServiceNo <> -8) then
        set @CreditNo=GetCounter('CreditCounter');
        insert into ServiceCredits( CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,PayedServices,WhoPays) values( 
          @CreditNo,@InvoiceDate,@No,@ServiceNo,@ArticleSum,1,@KodAdmin,@ArticleQty,0,@No) 
      else
        set @ResMoneyStatus=2;
        set @CreditNo=0
      end if
    else
      set @CreditNo=@CreditNum
    end if;
    //Insertva zapis vuv Articles
    insert into Articles( InvoiceNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo,
      ArticleName,TipDDS,Discount,ServiceNo,RegDate,DDSType,RealLodgingsNum) values( 
      @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate,@DDSType, @RLN) ;
    //Promenia noshtuvkite
    if @RegNo > 0 then
      if @RLN = 0 then
        update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@ArticleQty where
          RegNum = @RegNo
      else
        update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@RLN where
          RegNum = @RegNo
      end if
    end if;
    //Promenia uslugite
    if @CreditNo > 0 then
      update ServiceCredits set PayedServices = PayedServices+sign(@BillType)*@ArticleQty where
        CreditNo = @CreditNo
    end if end for;
  //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva
  //Promenia CancelInvoiceNo na anuliranata smetka na -1
  if @BillType = -1 then
    delete from DBA.ResMoney where InvoiceNo = @No and DateMoney = 0;
    update Invoices set CancelInvoiceNo = -1 where InvoiceNo = @No;
    select max(Status) into @ResMoneyStatus from ResMoney where InvoiceNo = @No;
    //////////////////////////////////////////////// ivaylo
    set @DepContractNo=0;
    select MAX(ContractNo) into @DepContractNo from ResMoney where InvoiceNo = @No;
    if(@DepContractNo <> 0) then
      update DepositForContracts set GlobalInvoice = 0 where
        GlobalInvoice = @No and ContractNo = @DepContractNo
    end if
  end if; //////////////////////////////////////////////// ivaylo
  //Nulira predishnite depoziti syotvetno na gost, grupa, staia i reservacia
  //Nulira depositi, ako e smetka za gost
  if @BillType = 1 then
    call CheckDeposits(@No,@KodAdmin,@InvoiceNo,@NullDepositCount)
  end if;
  //Nulira depositi, ako e smetka za grupa
  if @BillType = 2 then
    for f2 as curs2 scroll cursor for
      select GuestNum as @GuestNum from Guests where "Group" = @No do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira Depositi ako e smetka za staia
  if @BillType = 3 then
    for f3 as curs3 scroll cursor for
      select Guest as @GuestNum from Book where Room = @No and Staying = 1 do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira depositi, ako e smetka za grupa
  if @BillType = 4 then
    for f4 as curs4 scroll cursor for
      select GuestNum as @GuestNum from Guests where Reservation = @No do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira depositi, ako e smetka po dogovor - fixed
  if(@BillType = 7) and(@ContractNo <> 0) then
    call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount,null)
  end if;
  //Vmukva plashtaniata ot wremennata tablica za plashtania
  for f6 as curs6 scroll cursor for
    select PaymentType as @PaymentType,Sum as @Sum,KodCurrency as @KodCurrency,AccountNumber as @AccountNumber,
      GuestNo as @mGuestNo,GroupNo as @mGroupNo,RoomNo as @mRoomNo,ReserveNo as @mReserveNo,PTVid as @PTVid from
      //,ContractNo as @Contract
      #PaymentTypes do
    set @DepositNo=GetCounter('DepositCounter');
    ///////////////////////////////////// ivaylo
    if((@ContractNo <> 0) and(@BillType = 5) and(@mGuestNo = 0)) then
      insert into DepositForContracts( GlobalInvoice,SubInvoice,ContractNo) values( 
        0,@InvoiceNo,@ContractNo) 
    end if;
    ////////////////////////////////////  ivaylo
    insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,
      ContractNo,InvoiceNo,RoomNo,AccountNumber,PTVid) values( 
      @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) end for;
  return(@InvoiceNo)
end
//[v 00.11.18]
//[v 01.07.09] - Niki - za vidovete smetki
//[v 18.09.02] - Dobaven e parametyr za N na Dogovora vyv vryska s Smetki po dogovori
//[v 18.02.2003] Jorko
//[  02.11.2007] Kozludjov - fix depoziti po dogovor
//[v 18.01.2008] - Niki - depositi za dogovor
//[v 14.03.2008] Maria - tip na TrueInvoiceNo e promenen i se vika funkcia GetFakturaCounter, koiato dava nomera na fakturata
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
go

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

GO

create procedure
DBA.MoveToRoom(in @OldRegNo integer,in @RoomNo integer,in @NowDate real,in @NumDays integer,in @KodAdmin integer,in @Price decimal(12,2),in @KodCurrency integer,in @PriceService decimal(12,2), in @PriceKind integer)
//Premestva daden gost ot edna staia v druga
//Ako ima predplateni noshtuvki i sumata e razlichna ot novata suma dobavia edna usluga - "Noshtuvka" s razlikata v cenite
begin atomic
  declare @OldLeftLodgings integer;
  declare @OldPayed integer;
  declare @GuestNo integer;
  declare @HumanKind integer;
  declare @OldWhoPays integer;
  declare @OldGroupPays integer;
  declare @OldContractPays integer;
  declare @OldPrice double;
  declare @OldKodCurrency integer;
  declare @OldRoomNo integer;
  declare @OldDateReg real;
  declare @NewNo integer;
  declare @NewPayed integer;
  declare @ServiceNoLodging integer;
  select PayedLodgings,Guest,WhoPays,GroupPays,ContractPays,Price,KodCurrency,Room,DateReg,Guests.HumanKind into 
    @OldPayed,@GuestNo,@OldWhoPays,@OldGroupPays,@OldContractPays,@OldPrice,
    @OldKodCurrency,@OldRoomNo,@OldDateReg,@HumanKind from Book,Guests where RegNum = @OldRegNo and Book.Guest = Guests.GuestNum;
  set @OldLeftLodgings=ceiling(@NowDate-@OldDateReg);
  //
  //Ako ima predplateni dni wmukwa edna usluga s razlikata v cenite
  if @OldLeftLodgings < @OldPayed then
    if @PriceService <> 0 then
      set @NewNo=GetCounter('creditcounter');
      if @HumanKind = 1 then
        set @ServiceNoLodging=-1
      else
        set @ServiceNoLodging=-2
      end if;
      insert into ServiceCredits( DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,CreditNo,PayedServices,WhoPays,
        GroupPays,ContractPays,RoomPays) values( 
        @NowDate,@GuestNo,@ServiceNoLodging,@PriceService,@KodCurrency,@KodAdmin,1,@NewNo,0,@OldWhoPays,
        @OldGroupPays,@OldContractPays,0) 
    end if
  end if;
  //Izchistva gosta ot starata staia
  if @OldLeftLodgings < @OldPayed then
    set @NewPayed=@OldLeftLodgings
  else
    set @NewPayed=@OldPayed
  end if;
  update Book set NumLodgings = @OldLeftLodgings,PayedLodgings = @NewPayed,Staying = 2 where
    RegNum = @OldRegNo;
  //
  update Rooms set Buzy = (if NumPeople = 1 then 0 else 2 endif),NumPeople = NumPeople-1 where RoomNum = @OldRoomNo;
  //Nastaniava gosta v novata staia
  set @NewNo=GetCounter('bookcounter');
  set @NewPayed=(@OldPayed-@OldLeftLodgings);
  if @NewPayed < 0 then
    set @NewPayed=0
  end if;
  insert into Book( RegNum,DateReg,NumLodgings,PayedLodgings,Payed,Staying,PriceKind,KodAdmin,Room,
    Price,Guest,WhoPays,KodCurrency,GroupPays,ContractPays,IsRest) values( 
    @NewNo,@NowDate,@NumDays,@NewPayed,
    0,1,@PriceKind,@KodAdmin,@RoomNo,@Price,@GuestNo,@OldWhoPays,@KodCurrency,@OldGroupPays,@OldContractPays,0) ;
  //vmukva uslugite ot paketa
  call InsertPackage(@NewNo);
  //updejtva infoto za staita
  update Rooms set NumPeople = NumPeople+1,Buzy = 2 where RoomNum = @RoomNo
end
//[v 01.02.26]
//[v 22.10.2003] Jorko v insert na Book @OldKodCurrency --> @KodCurrency
//[v 10.12.2008 MON] Nixon - Dobaven cenorazpis

go

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

GO

create procedure
DBA.PreparePrintAgain(in @SmetkaNo integer,in @OkCancel integer)
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus articulite ot opredelena smetka pri owtoren pechat na smetka ili Anulirane na smetka
// kato ako e PrintAgain @OkCancel e 1, a pri Cancel -1
begin
  //Iztriva wremennata tablica za artikulite
  delete from #Articles;
  //Iztriva wremennata tablica za nacinite na plashtane
  delete from #PaymentTypes;
  //Popylva wremennata tablica za artikulite
  //@ArticlePrWithDDS,@ArticlePrWithDDS*@Qty
  for f0 as curs0 scroll cursor for
    select ArticleNo as @ArticleNumber,ArticleName as @ArticleName,Qty as @ArticleQty,Price as @ArticlePrice,
      RegNo as @RegNo,CreditNo as @CreditNo,TotalSum as @ArticleSum,Discount as @Discount,
      TipDDS as @TipDDS,ServiceNo as @Service,Price as @ArticlePrWithDDS,TotalSum as @TotalSumWithDDS,
      RegDate as @RDate, RealLodgingsNum as @RealLodgingsNum from
      Articles where InvoiceNo = @SmetkaNo do
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,RegNo,CreditNo,
      ArticleSum,Discount,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,RealLodgNum) values( 
      @ArticleNumber,@ArticleName,'бр.',@ArticleQty,@ArticleQty,@ArticleQty,
      @OkCancel*@ArticlePrice,@RegNo,@CreditNo,@OkCancel*@ArticleSum,@Discount,@TipDDS,@Service,@ArticlePrWithDDS,@TotalSumWithDDS,@RDate,@RealLodgingsNum) end for;
  //Popylva wremennata tablica za nachinite na plashtane
  for f1 as curs1 scroll cursor for
    select RM.Sum as @Sum,RM.Currency as @KodCurrency,RM.Payment as @PaymentType,Cr.Symbol as @Symbol,
      RM.AccountNumber as @AccountNumber,RM.GuestNo as @GuestNo,RM.GroupNo as @GroupNo,RM.RoomNo as @RoomNo,RM.ReserveNo as @ReserveNo,
      RM.PTVid as @PTVid from
      DBA.ResMoney as RM,DBA.Currencies as Cr where
      RM.InvoiceNo = @SmetkaNo and
      RM.DateMoney <> 0 and
      RM.Currency = Cr.KodCurrency do
    insert into #PaymentTypes( PaymentType,Sum,KodCurrency,AccountNumber,Symbol,GuestNo,GroupNo,RoomNo,ReserveNo,PTVid) values( 
      @PaymentType,@OkCancel*@Sum,@KodCurrency,@AccountNumber,@Symbol,@GuestNo,@GroupNo,@RoomNo,@ReserveNo,@PTVid) end for
end
//[v.1 00.11.16]
//[v.2 18.02.2003] Jorko
//[v.3 19.Nov.2007] - A.Kozludjov, fix pri anulirane na smetka
//[v.4 10.Dec.2008 - MON] - Nixon - dobaveno RealLodgingsNum

go

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st 
where LCase(st.table_name)= LCase('Countries') and LCase(sc.column_name)= LCase('AdminSoftId') ) = 0 then
alter table Countries
add "AdminSoftId" integer
END IF
GO

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Oblasti'))) THEN
  create table Oblasti (
    N integer not null,
	OblastName varchar(50),
	primary key("N")
  )
end if

go

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Municipalities'))) THEN
  create table Municipalities (
    N integer not null,
	OblastN integer,
	MunicipalityName varchar(50),
	primary key("N")
  )
end if

go

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Settlements'))) THEN
  create table Settlements (
    N integer not null,
	MunicipalityN integer,
	SettlementName varchar(50),
	primary key("N")
  )
end if

go

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('FK_Municipality_Oblast'))) then
	alter table Municipalities
	add foreign key "FK_Municipality_Oblast" ("OblastN")
	references "Oblasti" ("N")
	on update cascade
	on delete restrict
end if
go

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('FK_Settlements_Municipality'))) then
	alter table Settlements
	add foreign key "FK_Settlements_Municipality" ("MunicipalityN")
	references "Municipalities" ("N")
	on update cascade
	on delete restrict
end if
go

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

GO

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

GO

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

GO

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

GO

create trigger TrigUpdateRowOblasti after update order 2 on
DBA."Oblasti"
referencing new as new_name
for each row 
begin
  update Oblasti set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

create trigger TrigUpdateRowMunicipalities after update order 2 on
DBA."Municipalities"
referencing new as new_name
for each row 
begin
  update Municipalities set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

create trigger TrigUpdateRowSettlements after update order 2 on
DBA."Settlements"
referencing new as new_name
for each row 
begin
  update Settlements set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

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('SettlementN') ) = 0 then
ALTER TABLE "Guests"
ADD "SettlementN" integer not null default 0
END IF

GO

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

GO

create function DBA.CalcServiceCreditsSum(in @GuestNo integer)
returns decimal(12,4)
// Vryshta sumata na neplatenite dopulnitelni uslugi za @GuestNo v mestna valuta (leva).
begin
  declare @R decimal(12,4);
  select sum(if VidSdelki.TipDDS <> 2 then
      (ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    else
      (1+bvrDDSPart)*(ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    endif) into @R
    from ServiceCredits,Currencies,Services,VidSdelki where
    ServiceCredits.WhoPays = @GuestNo and
    ServiceCredits.Service = Services.ServiceNo and
    Services.TipSdelka = VidSdelki.N and
    ServiceCredits.KodCurrency = Currencies.KodCurrency and
    ((ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1));
  if @R is null then set @R=0
  end if;
  return(@R)
end
//[v 2008.12.09 - MON] - Nixon - created

go

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

GO

create function DBA.DOM(in @Month integer,in @Year integer)
returns integer
begin
  if @Month in( 1,3,5,7,8,10,12) then return(31)
  else
    if @Month in( 4,6,9,11) then return(30)
    else
      if(MOD(@Year,4) = 0 or MOD(@Year,100) <> 0) and(MOD(@Year,400) = 0) then return(29)
      else
        return(28)
      end if
    end if
  end if
end
//[v 2008.12.09 - MON] - Dobrin - created

go

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

GO

create function DBA.GetInfoMonths(in @RegNo integer,in @LodgType tinyint)
returns real //@LodgType - 1-vryshta br plateni meseci,2-vryshta br na vsichki meseci
begin
  declare @RegDate date;
  declare @DateLeave date;
  declare @DatePaidTo date;
  declare @Months real;
  declare @PMonths real;
  declare @FMonth real;
  select DateRegDt,DateAdd(day,NumLodgings,DateRegDt),DateAdd(day,PayedLodgings-1,DateRegDt) into @RegDate,
    @DateLeave,@DatePaidTo from Book as B where B.RegNum = @RegNo;
  if DOM(MONTH(@RegDate),YEAR(@RegDate))-DAY(@RegDate)+1 > 15 then set @FMonth=1
  else set @FMonth=.5
  end if;
  if @LodgType = 2 then
    set @Months=@FMonth+(YEAR(@DateLeave)-YEAR(@RegDate))*12+MONTH(@DateLeave)-MONTH(@RegDate);
    return(@Months)
  else
    set @PMonths=@FMonth+(YEAR(@DatePaidTo)-YEAR(@RegDate))*12+MONTH(@DatePaidTo)-MONTH(@RegDate);
    return(@PMonths)
  end if
end
//[v 2008.12.09 - MON] - Dobrin - created
go

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

CREATE FUNCTION "DBA"."ImeMesec"(in MonthNo integer)
returns varchar(20)
begin
  case MonthNo
  when 1 then return('Януари')
  when 2 then return('Февруари')
  when 3 then return('Март')
  when 4 then return('Април')
  when 5 then return('Май')
  when 6 then return('Юни')
  when 7 then return('Юли')
  when 8 then return('Август')
  when 9 then return('Септември')
  when 10 then return('Октомври')
  when 11 then return('Ноември')
  when 12 then return('Декември')
  end case
end
//[v 2008.12.09 - MON] - Dobrin - 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);
  declare @ForfeitServiceNo integer;
  declare @ForfeitNumMonths integer;
  declare @NowDate date;
  declare @Cnt integer;
  declare @Interest numeric(12,4);
  declare @DaysLate integer;
  declare @PayChislo integer;
  declare @ForfeitName varchar(100);
  declare @ForfeitPrice numeric(12,2);
  
  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);
  
  // nachisliava neustojki za prosrochen naem ako e setnat registura na sushtestvuvashta usluga i studenta e zakusnial
  select S.ServiceNo, S.ServiceName into @ForfeitServiceNo, @ForfeitName from Registers R,Services S where R.RegisterName='ForfeitService' and R.IntStojnost=S.ServiceNo;
  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 (@Qty > 0) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0) then
    set @NowDate = dateadd(day,-@PayChislo,Now(*));
    set @ForfeitNumMonths=datediff(month,@DateStart,@NowDate) + 1;
	set @Cnt=1;
	while @Cnt <= @ForfeitNumMonths loop
	  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; // @Cnt=1
	  
	  if @Interest>0 then
	    set @ArticleName=@ForfeitName||' '||ImeMesec(month(@DateStart))||@place||@RoomName;
		set @ForfeitPrice=Round(@Interest*@ArtPrice/100,2);
		// update-va naema za da ne moje da se plati neustojka bez da se plati naema
		update #ArticleLodgings set MinQty=1 where ZIndex=@ZIndex and RegNum=@RegNo;
        insert into #ArticleLodgings (RegNum,"Name",RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS,ServiceNoLodging)
          values (0,@ArticleName,0,@ForfeitPrice,1,1,1,@ZIndex,@TipDDS,@ForfeitServiceNo);
	  end if;
	  set @Cnt = @Cnt + 1;
	end loop;
  end if;
  
  // namalia @NumDays sus realnite noshtuvki i vrushta rezultata
  set @R=@NumDays-@RealLodgings;
  return (@R);
end;

go

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Kursove'))) THEN
  create table Kursove (
    N integer not null,
	"Name" varchar(50),
	ShortName varchar(50),
	OrderId integer,
	LastChanged timestamp not null default current timestamp,
	primary key("N")
  )
end if

go

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

GO

create trigger TrigUpdateRowKursove after update order 2 on
DBA."Kursove"
referencing new as new_name
for each row 
begin
  update Kursove set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

if not (Exists (Select Table_name from SysTable where Table_name='NachinPlashtane')) then
create table NachinPlashtane(
  "N" integer not null default autoincrement,
  "opisanie" varchar(100),
  primary key("N")
  )
end if

go

if not (exists (select N from NachinPlashtane where N=0)) then
  insert into NachinPlashtane (N,opisanie)
    values (0,'на ден')
end if
go

if not (exists (select N from NachinPlashtane where N=1)) then
  insert into NachinPlashtane (N,opisanie)
    values (1,'на месец')
end if
go

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('SpecialityTypes'))) THEN
  create table SpecialityTypes (
    N integer not null,
	"SpecialityTypeName" varchar(50),
	primary key("N")
  )
end if

go

IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Specialities'))) THEN
	create table Specialities(
	  "N" integer not null default autoincrement,
	  "Name" varchar(100),
	  primary key("N")
	  )
END IF
GO

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('FK_Speciality_Type'))) then
	alter table Specialities
	add foreign key "FK_Speciality_Type" ("SpecialityType")
	references "SpecialityTypes" ("N")
	on update cascade
	on delete restrict
end if
go

IF (NOT EXISTS (select column_id from SysTable, SysColumn where SysColumn.table_id=SysTable.table_id and 
   LCase(SysTable.Table_name)=LCase('Specialities') and LCase(SysColumn.Column_Name)=LCase('SpecialityType'))) THEN
alter table Specialities 
  add SpecialityType integer
end if

go

IF (NOT EXISTS (select column_id from SysTable, SysColumn where SysColumn.table_id=SysTable.table_id and 
   LCase(SysTable.Table_name)=LCase('Specialities') and LCase(SysColumn.Column_Name)=LCase('AdminSoftId'))) THEN
alter table Specialities 
  add AdminSoftId bigint
end if

go

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

GO

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st
where LCase(st.table_name)= LCase('SpecialityTypes') and LCase(sc.column_name)= LCase('LastChanged') ) = 0 then
ALTER TABLE "SpecialityTypes"
ADD "LastChanged" timestamp not null default current timestamp
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('NumZN') ) = 0 then
  alter table Guests add "NumZN" varchar(30)
else
  alter table Guests modify "NumZN" varchar(30)
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('DateZN') ) = 0 then
alter table Guests add "DateZN" date
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('FN') ) = 0 then
alter table Guests add "FN" varchar(20)
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('Kurs') ) = 0 then
alter table Guests add "Kurs" integer
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('SpecNo') ) = 0 then
ALTER TABLE Guests
ADD FOREIGN KEY "Speciality" ("SpecNo")
REFERENCES "Specialities" ("N")
ON UPDATE CASCADE
ON DELETE CASCADE
END IF
GO

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

GO

create trigger TrigUpdateRowSpecialities after update order 2 on
DBA."Specialities"
referencing new as new_name
for each row 
begin
  update Specialities set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

create trigger TrigUpdateRowSpecialityTypes after update order 2 on
DBA."SpecialityTypes"
referencing new as new_name
for each row 
begin
  update SpecialityTypes set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO
IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Universities'))) THEN
  create table Universities (
    N integer not null,
	FullName varchar(200),
	ShortName varchar(30),
	Bulstat varchar(20),
	DN varchar(10),
	AdminSoftId integer,
	LastChanged timestamp not null default current timestamp,
	primary key("N")
  )
end if

go

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

GO

create trigger TrigUpdateRowUniversities after update order 2 on
DBA."Universities"
referencing new as new_name
for each row 
begin
  update Universities set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

if ((select count (N) from Universities where N=0) = 0) then 
  insert into Universities (N, FullName, ShortName)
    values(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('UniN') ) = 0 then
ALTER TABLE "Guests"
ADD "UniN" integer	not null default 0
END IF

GO

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('FK_Guests_Uni'))) then
	alter table Guests
	add foreign key "FK_Guests_Uni" ("UniN")
	references "Universities" ("N")
	on update cascade
	on delete restrict
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('IsInAdminSoftDB') ) = 0 then
ALTER TABLE "Guests"
ADD "IsInAdminSoftDB" integer not null default 0
END IF

GO

alter table Articles
modify ArticleName varchar(100)

go

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('IsFull') ) = 0 then
ALTER TABLE "Rooms"
ADD "IsFull" integer not null default 0
END IF

GO

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

GO

create trigger TrigRoomsStatus after update order 1 on
DBA.Rooms
referencing old as RMOLD new as RMNEW
for each row
// 1. Pri osvobojdawane na staia (Buzy 2 -> 0) ustanoviava statusa na staiata 
//   v nepochistena (2).
// 2. Pri zaemane na staia (Buzy 0 -> 2) ustanoviava statusa na staiata 
//   v normalna (0).
// 3. Pri promiana nomera na staia promenia svyrzanite tablici.
// 4. Pri promiana tipa na staia promenia svyrzanite tablici.
// 5. Ako staiata se osvobodi, togava setva IsFull na 0
begin
  if RMOLD.Buzy <> 0 and RMNEW.Buzy = 0 then
    update Rooms set Status = 2 where RoomNum = RMNEW.RoomNum
  else if RMOLD.Buzy = 0 and RMNEW.Buzy <> 0 and RMNEW.Status = 2 then
      update Rooms set Status = 0 where RoomNum = RMNEW.RoomNum
    end if
  end if;
  // 3. Pri promiana nomera na staia promenia svyrzanite tablici:
  if RMOLD.RoomNum <> RMNEW.RoomNum then
    update Book set Room = RMNEW.RoomNum where Room = RMOLD.RoomNum;
    update ReservePlan set RoomNo = RMNEW.RoomNum where RoomNo = RMOLD.RoomNum;
    update ResMoney set RoomNo = RMNEW.RoomNum where RoomNo = RMOLD.RoomNum
  end if;
  // 4. Pri promiana tipa na staia promenia svyrzanite tablici:
  if RMOLD.RoomCat <> RMNEW.RoomCat then
    update ReservePlan set RoomClass = RMNEW.RoomCat where RoomNo = RMNEW.RoomNum
  end if;
  // 5. Setva IsFull, ako napusnat gostite
  if RMNEW.Buzy = 0 then 
    update Rooms set IsFull=0 where RoomNum = RMNEW.RoomNum;
  end if;
end
//[v 00.12.31]
//[v 2008.12.10 - MON] - Nixon - Dobavena e funkcionalnost za IsFull

go

if (not exists (select Table_name from SysTable where LCase(Table_name)=LCase('SvoServiceCredits'))) THEN
create table SvoServiceCredits(
  "N" integer not null,
  "Ime" varchar(40) not null,
  "Opisanie" varchar(500),
  "DanTip" char(1) not null,
  "Nachalo" long varchar,
  "Syzdatel" integer,
  "PickList" long varchar,
  primary key("N")
  )
end if

GO

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('SVSC_SvoTipove_Tip'))) then
  ALTER TABLE SvoServiceCredits
    ADD NOT NULL FOREIGN KEY "SVSC_SvoTipove_Tip" ("DanTip")
    REFERENCES "SvoTipove" ("DanTip")
end if
go

--  Syzdava tablicata sus stojnostite na dopulnitelnite svojstva

if (not exists (select Table_name from SysTable where LCase(Table_name)=LCase('SvoServiceCreditsStn'))) THEN
create table SvoServiceCreditsStn(
  "Glava" integer not null,
  "Svoistvo" integer not null,
  "Stoinost" varchar(100),
  primary key("Glava", "Svoistvo")
  )
end if

GO

if (not exists (select Role from SysForeignKey where LCase(role)=LCase('SVSCStn_Svo_N'))) then
  ALTER TABLE SvoServiceCreditsStn
    ADD NOT NULL FOREIGN KEY "SVSCStn_Svo_N" ("Svoistvo")
    REFERENCES "SvoServiceCredits" ("N")
end if
go
--  Dobavia dopulnitelnite svojstva za ServiceCredits

if (not exists(select N from SvoServiceCredits where N=1)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(1,'Алтернативно име','Име, което да излиза на сметката вместо името на услугата','T','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go
  
if (not exists(select N from SvoServiceCredits where N=2)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(2,'Период Начало','Ако начисляването на дадена услуга е за някакъв период - указва началото на периода','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

if (not exists(select N from SvoServiceCredits where N=3)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(3,'Период Край','Ако начисляването на дадена услуга е за някакъв период - указва края на периода','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

--Project MON Common DB Upgrade
--10.Nov.2008 - Kostadin - Created.

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

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

GO

create trigger
TrigUpdateRowClasses after update order 2 on
DBA.Classes
referencing new as new_name
for each row /* WHEN( search_condition ) */
begin
  update Classes set
    LastChanged = Now(*) where
    Class = new_name.Class
end

go

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

GO

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

GO

create trigger TrigUpdateRowRoomTypes after update order 2 on
DBA."RoomTypes"
referencing new as new_name
for each row 
begin
  update RoomTypes set
    LastChanged = Now(*) where
    "RoomTypeNo" = new_name."RoomTypeNo"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowContracts after update order 2 on
DBA."Contracts"
referencing new as new_name
for each row 
begin
  update Contracts set
    LastChanged = Now(*) where
    "ContractNo" = new_name."ContractNo"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowCountries after update order 1 on
DBA."Countries"
referencing new as new_name
for each row 
begin
  update Countries set
    LastChanged = Now(*) where
    "CountryKod" = new_name."CountryKod"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowCurrencies after update order 2 on
DBA."Currencies"
referencing new as new_name
for each row 
begin
  update Currencies set
    LastChanged = Now(*) where
    "KodCurrency" = new_name."KodCurrency"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowDocTypes after update order 2 on
DBA."DocTypes"
referencing new as new_name
for each row 
begin
  update DocTypes set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowPayTypes after update order 2 on
DBA."PayTypes"
referencing new as new_name
for each row 
begin
  update PayTypes set
    LastChanged = Now(*) where
    "PayTypeN" = new_name."PayTypeN"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowPayTypesVids after update order 2 on
DBA."PayTypesVids"
referencing new as new_name
for each row 
begin
  update PayTypesVids set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowPriceKinds after update order 2 on
DBA."PriceKinds"
referencing new as new_name
for each row 
begin
  update PriceKinds set
    LastChanged = Now(*) where
    "PriceKind" = new_name."PriceKind"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowRoomStatus after update order 2 on
DBA."RoomStatus"
referencing new as new_name
for each row 
begin
  update RoomStatus set
    LastChanged = Now(*) where
    "RoomStatusNo" = new_name."RoomStatusNo"
end

GO

--Services
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('LastChanged') ) = 0 then
ALTER TABLE "Services"
ADD "LastChanged" timestamp not null default current timestamp
END IF

GO

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

GO

create trigger TrigUpdateRowServices after update order 2 on
DBA."Services"
referencing new as new_name
for each row 
begin
  update Services set
    LastChanged = Now(*) where
    "ServiceNo" = new_name."ServiceNo"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowVATRates after update order 2 on
DBA."VATRates"
referencing new as new_name
for each row 
begin
  update VATRates set
    LastChanged = Now(*) where
    "ID" = new_name."ID"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowVidSdelki after update order 2 on
DBA."VidSdelki"
referencing new as new_name
for each row 
begin
  update VidSdelki set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO

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

GO

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

GO

create trigger TrigUpdateRowVidSmetki after update order 2 on
DBA."VidSmetki"
referencing new as new_name
for each row
begin
  update VidSmetki set
    LastChanged = Now(*) where
    "N" = new_name."N"
end

GO