IF (EXISTS (select proc_name from sysprocedure where proc_name='SpaFinishBill')) THEN
  Drop function SpaFinishBill;
END IF;


GO

CREATE FUNCTION SpaFinishBill(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)
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
  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
  ;
  message cast(@NullDepositCount as varchar);
  //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=SpaGetCounter('InvoiceCounter');
  if @IsInvoice=1 then
    set @TrueInvoiceNo=SpaGetCounter('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 SpaInvoices(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) values(
    @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,@InvoiceMask);
  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,SpaResID as @SpaReservationID  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) then
        set @CreditNo=GetCounter('CreditCounter');
        insert into ServiceCredits(CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,PayedServices,WhoPays,SPaReservationFk,SpaGuestTypeFlag) values(
          @CreditNo,@InvoiceDate,@No,@ServiceNo,@ArticleSum,1,@KodAdmin,@ArticleQty,0,@No,@SpaReservationID,1)
      else
        set @ResMoneyStatus=2;
        set @CreditNo=0
      end if
    else
      set @CreditNo=@CreditNum
    end if
    ; //Insertva zapis vuv Articles
    insert into SpaArticles(InvoiceNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo,
      ArticleName,TipDDS,Discount,ServiceNo,RegDate) values(
      @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate);
    //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".SpaResMoney where InvoiceNo=@No and DateMoney=0;
    update SpaInvoices set CancelInvoiceNo=-1 where InvoiceNo=@No;
    select max(Status) into @ResMoneyStatus from SpaResMoney where InvoiceNo=@No
  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
      message 'Minava pres checkContractDeposits...';
      message 'Nomer na Dogovora -> '+cast(@ContractNo as varchar);
      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
      //,ContractNo as @Contract
      from #PaymentTypes do
    set @DepositNo=SpaGetCounter('DepositCounter');
    message 'Dogovor nomer -> '+cast(@ContractNo as varchar);
    insert into SpaResMoney(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