IF (not EXISTS(Select * from systable where LCase(table_name) = LCase('DepositForContracts'))) then

Create table DepositForContracts
(
  id integer not null DEFAULT AUTOINCREMENT  PRIMARY KEY,
  GlobalInvoice integer not null,
  SubInvoice    integer not null,
  ContractNo    integer not null
);

end if;


GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FinishBill'))) THEN
Drop function 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;
  declare @TrueInvoiceNo integer;
  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
    set @TrueInvoiceNo=GetCounter('TrueInvoiceNo')
  else
    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.00,@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 from #Articles do
    //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) values( 
      @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate,@DDSType) ;
    //Promenia noshtuvkite
    if @RegNo > 0 then
      update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@ArticleQty where
        RegNum = @RegNo
    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;
//////////////////////////////////////////////// ivaylo
  end if;
  //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
  if(@BillType = 7) and(@ContractNo <> 0) then
    for f5 as curs5 scroll cursor for
      select Contract from Guests where contract = @ContractNo do
      call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  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



GO


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


GO

create procedure SetInvoiceNoDeposit(in @InvoiceNo integer ,in @ContractNo integer)
begin
   declare @CurrSum    double;
   set     @CurrSum=0;

     select Sum(ResMoney.Sum*Currencies.ExchangeRate) into @CurrSum
     from ResMoney,Currencies
     where ResMoney.ContractNo=@ContractNo
     and ResMoney.Status=2 AND ResMoney.Currency=Currencies.KodCurrency;
   if  (@CurrSum>0) then
        update DepositForContracts
        set    GlobalInvoice=@InvoiceNo
        where  ContractNo=@ContractNo and GlobalInvoice=0
end if;
end;


GO


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


GO


CREATE PROCEDURE "DBA"."CheckContractDeposits"(in @ContractNo integer,in @KodAdmin integer,in @InvoiceNo integer,in @ZeroCountDeposit integer)
//Za daden dogovor nulira istinskite mu depositi kato vkarva vuv ResMoney
//zapisi s otricatelna stojnost i otricatelen N, za da ne izlizat v otchetite
begin
  declare @DepositNo integer;
  declare @ExchangeRate decimal(12,6);
  //
  call  SetInvoiceNoDeposit(@InvoiceNo,@ContractNo);
  for f as curs scroll cursor for
    select Sum(ResMoney.Sum) as @CurrSum,ResMoney.Currency as @KodCurrency
      from "DBA".ResMoney
      where ResMoney.ContractNo=@ContractNo
      and ResMoney.Status=2
      group by ResMoney.Currency do
    //
    select min(DepositNo)-1 into @DepositNo
      from "DBA".ResMoney;
    if @DepositNo>=0 then set @DepositNo=-1
    end if
    ;
    message 'Sumata na neobrabotenite depoziti do momenta -> '+cast(@CurrSum as varchar);
    if @CurrSum<>0 then
      insert into "DBA".ResMoney(ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,
        Status,GroupNo,ContractNo,InvoiceNo,RoomNo,AccountNumber) values(
        0,0,@KodAdmin,-@CurrSum,@KodCurrency,1,'',0,@DepositNo,2,0,@ContractNo,@InvoiceNo,0,0);
      //
      select Currencies.ExchangeRate into @ExchangeRate from "DBA".Currencies
        where Currencies.KodCurrency=@KodCurrency;
      insert into "DBA".ResMoney(ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,ContractNo,
        InvoiceNo,RoomNo,AccountNumber) values(
        0,0,@KodAdmin,@CurrSum*@ExchangeRate,1,1,'',0,@DepositNo-1,(if @ZeroCountDeposit<>0 then 2 else 1 endif),0,@ContractNo,@InvoiceNo,0,0)
    end if
  end for
//
end
//[v 00.05.28]


GO

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


GO

create function GetSmetkaWithDepozits(in @SmetkaNo integer)
returns varchar(50)
begin
  declare @Smetki    varchar(50);
  declare @G          integer;
  declare @C          integer;
  select MAX(GuestNo),MAX(ContractNo) into @G,@C from ResMoney where InvoiceNo=@SmetkaNo;
  set @Smetki='';
  if ((@G<>0)and (@C=0) ) then
     for F as Curs scroll cursor for
       select InvoiceNo as @Inv
         from ResMoney
         where GuestNo = @G
         and Status=1
         and DepositNo<0
         and GuestNo<>0
         and ContractNo=0 do
       if((locate(@Smetki,@Inv)=0) and(@Inv<>@SmetkaNo)) then
         set @Smetki=@Smetki||@Inv||','
       end if
     end for;
  end if;
  if ((@G=0) and (@C<>0) ) then
     for F2 as Curs2 scroll cursor for
       select GlobalInvoice  as @GlobalInvoice
       from  DepositForContracts
       where  SubInvoice=@SmetkaNo and ContractNo=@C
              and  GlobalInvoice<>0   do
       if((locate(@Smetki,@GlobalInvoice)=0) and(@GlobalInvoice<>@SmetkaNo)) then
         set @Smetki=@Smetki||@GlobalInvoice||','
       end if
     end for;
  end if;

  set @Smetki=substr(@Smetki,1,length(@Smetki)-1);
  return(@Smetki)
end