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,in @SaleOrderID integer default 0 ) 
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
  declare @docUNP varchar(100);
  declare @SaleLineID integer;
  declare @InitSaleId integer;
  declare @SaleLineGuid varchar(50);
  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
  if @BillType in( 1,5 ) then
    set @docUNP = (select first UNP from SaleOrders where GuestNum = @No and DocType = 0 order by ID asc)
  else if @BillType = -1 then
      set @docUNP = (select UNP from Invoices where InvoiceNo = @No)
    end if end if;
  set @InitSaleId = (select first ID from SaleOrders where UNP = @DocUNP and DocType = 0 order by ID asc);
  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,UNP ) 
    values( @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',1,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),1.0,@VidSmetka,@ContractNo,@InvoiceMask,@DDSType,@docUNP ) ;
  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,RentMonth as @RentMonth,RentYear as @RentYear from #Articles //Dobrin 2087 - dobaveno @RLN
  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,RealLodgingsNum ) 
      values( @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate,@DDSType,@RLN ) ;
    //Popylva SvoRedoveStn
    if @RentMonth <> -1 then
      insert into SvoRedoveStn values( @ArticleNumber,@InvoiceNo,1,@RentMonth ) ;
      insert into SvoRedoveStn values( @ArticleNumber,@InvoiceNo,2,@RentYear ) 
    end if;
    //Promenia noshtuvkite
    if @RegNo > 0 then
      if(@RLN = 0) or((select NachinPlat from PriceKinds as PK join Book as B on PK.PriceKind = B.PriceKind where RegNum = @RegNo) = 0) then
        update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@ArticleQty
          where RegNum = @RegNo;
        if @BillType <> -1 then
          update SaleOrderLines as SL set PayedQty = PayedQty+sign(@BillType)*@ArticleQty from
            SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
            join SaleOrders as SO on So.Id = SL.DocId
            where SOL.Book_RegNum = @RegNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0;
          insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
            select SL.ID,@InvoiceNo,@ArticleNumber
              from SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
                join SaleOrders as SO on So.Id = SL.DocId
              where SOL.Book_RegNum = @RegNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0
        end if
      else update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@RLN
          where RegNum = @RegNo;
        if @BillType <> -1 then
          update SaleOrderLines as SL set PayedQty = RegQty from
            SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
            join SaleOrders as SO on So.Id = SL.DocId
            where SOL.Book_RegNum = @RegNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0
            and '01.' || @RentMonth || '.' || @RentYear >= SOL.StartDate and '01.' || @RentMonth || '.' || @RentYear <= SOL.EndDate;
          insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
            select SL.ID,@InvoiceNo,@ArticleNumber
              from SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
                join SaleOrders as SO on So.Id = SL.DocId
              where SOL.Book_RegNum = @RegNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0
              and '01.' || @RentMonth || '.' || @RentYear >= SOL.StartDate and '01.' || @RentMonth || '.' || @RentYear <= SOL.EndDate
        end if end if;
      if @BillType = -1 then
        set @SaleLineID = GetCounter('SaleOrderLineID');
        set @SaleLineGuid = (select SaleLineGuid from SaleOrderLines where ID = (select first SaleLineId from SaleOrderLines_Articles where InvoiceNo = @No and ArticleNo = @ArticleNumber order by N desc));
        insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty,PayedQty ) 
          values( @SaleLineID,@SaleOrderID,@Service,@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
          (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),abs(@ArticleQty*@ArticlePrice*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif))),
          @ArticleName,@SaleLineGuid,@ArticleQty,@ArticleQty ) ;
        insert into SaleOrderLines_Links( SaleLineID,Book_RegNum ) 
          values( @SaleLineID,@RegNo ) ;
		insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
          values( @SaleLineID,@InvoiceNo,@ArticleNumber );
        set @SaleLineID = GetCounter('SaleOrderLineID');
        insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty ) 
          values( @SaleLineID,@InitSaleID,@Service,@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
          (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),@ArticleQty*abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),
          @ArticleName,convert(varchar(50),NEWID()),@ArticleQty ) ;
        insert into SaleOrderLines_Links( SaleLineID,Book_RegNum ) 
          values( @SaleLineID,@RegNo ) ;
 
      end if end if;
    //Promenia uslugite
    if @CreditNo > 0 then
      update ServiceCredits set PayedServices = PayedServices+sign(@BillType)*@ArticleQty
        where CreditNo = @CreditNo;
      if @BillType <> -1 then
        update SaleOrderLines as SL set PayedQty = PayedQty+sign(@BillType)*@ArticleQty from
          SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
          join SaleOrders as SO on So.Id = SL.DocId
          where SOL.ServiceCreditNo = @CreditNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0 and SL.PayedQty <> SL.RegQty;
        insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
          select SL.ID,@InvoiceNo,@ArticleNumber
            from SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
              join SaleOrders as SO on So.Id = SL.DocId
            where SOL.ServiceCreditNo = @CreditNo and SL.ItemID = @Service and SL.Status = 1 and So.DocType = 0
      end if;
      if @BillType = -1 then
        set @SaleLineID = GetCounter('SaleOrderLineID');
        set @SaleLineGuid = (select SaleLineGuid from SaleOrderLines where ID = (select first SaleLineId from SaleOrderLines_Articles where InvoiceNo = @No and ArticleNo = @ArticleNumber order by N desc));
        insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty,PayedQty ) 
          values( @SaleLineID,@SaleOrderID,@Service,@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
          (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),@ArticleQty*Abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),
          @ArticleName,@SaleLineGuid,@ArticleQty,@ArticleQty ) ;
        insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,ServiceCreditNo ) 
          values( @SaleLineID,0,@CreditNo ) ;
		insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
          values( @SaleLineID,@InvoiceNo,@ArticleNumber ); 
        set @SaleLineID = GetCounter('SaleOrderLineID');
        insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty ) 
          values( @SaleLineID,@InitSaleID,@Service,@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
          (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),@ArticleQty*abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),
          @ArticleName,convert(varchar(50),NEWID()),@ArticleQty ) ;
        insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,ServiceCreditNo ) 
          values( @SaleLineID,0,@CreditNo ) ;
      end if end if;
    if(@CreditNo = 0) and(@BillType = 5) and(@ResMoneyStatus = 2) then
      set @SaleLineID = GetCounter('SaleOrderLineID');
      insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty,PayedQty ) 
        values( @SaleLineID,@SaleOrderID,@Service,@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
        (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),@ArticleQty*Abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),
        @ArticleName,convert(varchar(50),NEWID()),@ArticleQty,@ArticleQty ) ;
      insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,ServiceCreditNo ) 
        values( @SaleLineID,0,@CreditNo ) ;
      set @SaleLineID = GetCounter('SaleOrderLineID');
      insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty ) 
        values( @SaleLineID,@InitSaleID,@Service,-@ArticleQty,abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),0,
        (if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif),-@ArticleQty*abs(@ArticlePrice)*(1+(if @TipDDS = 2 then abs(bvrDDSPart) else 0 endif)),
        @ArticleName,convert(varchar(50),NEWID()),-@ArticleQty ) ;
      insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,ServiceCreditNo ) 
        values( @SaleLineID,0,@CreditNo ) ;
      insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
        values( @SaleLineID,@InvoiceNo,@ArticleNumber ) 
    end if;
    if(@Service in( 1004,1005 ) ) and(@CreditNo = 0) and(@BillType = 1) then
      update SaleOrderLines as SL set PayedQty = PayedQty+sign(@BillType)*@ArticleQty from
        SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
        join SaleOrders as SO on So.Id = SL.DocId
        where SO.ID = @InitSaleId and SOL.Book_RegNum = 0
        and SL.ItemID = @Service and SL.Qty < 0 and SL.Status = 1 and So.DocType = 0;
      insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo ) 
        select SL.ID,@InvoiceNo,@ArticleNumber
          from SaleOrderLines as SL join SaleOrderLines_Links as SOL on SL.ID = SOL.SaleLineId
            join SaleOrders as SO on So.Id = SL.DocId
          where SO.ID = @InitSaleId and SL.ItemID = @Service and SL.Qty < 0 and SL.Status = 1 and So.DocType = 0
    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 za gosti pri obshta smetka
  if @BillType = 6 then
    for fcb as curscb scroll cursor for
      select GuestNo as @GNum from #GuestsForBill do
      call CheckDeposits(@GNum,@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
      //,ContractNo as @Contract
      from #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
//[v 2009.05.23 - MON] - Dobrin - da popylva SvoRedoveStn