if (select count(*) from syscolumn where column_name='DDSType' and
    table_id=(select table_id from systable where table_name='Invoices')) =0 then

Alter table  Invoices
Add  DDSType integer  not null default 1

end if;

GO

COMMENT ON
COLUMN Invoices.DDSType
is '1-DDS 20% 2- DDS 7%'


GO


if (select count(*) from syscolumn where column_name='DDSType' and
    table_id=(select table_id from systable where table_name='Articles')) =0 then

Alter table  Articles
Add  DDSType integer  not null default 1

end if;

GO

COMMENT ON
COLUMN Articles.DDSType
is '1-DDS 20% 2- DDS 7%'

GO

if (select count(*) from syscolumn where column_name='DDSType' and
    table_id=(select table_id from systable where table_name='ProArticles')) =0 then

Alter table  ProArticles
Add  DDSType integer  not null default 1

end if;

GO

COMMENT ON
COLUMN ProArticles.DDSType
is '1-DDS 20% 2- DDS 7%'

GO


if (select count(*) from syscolumn where column_name='DDSType' and
    table_id=(select table_id from systable where table_name='Proform')) =0 then

Alter table  Proform
Add  DDSType integer  not null default 1

end if;

GO

COMMENT ON
COLUMN Proform.DDSType
is '1-DDS 20% 2- DDS 7%'

GO


IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FinishBill'))) THEN
Drop function FinishBill
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
  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,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@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
  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');
    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('FinishProformBill'))) THEN
Drop function FinishProformBill
END IF


GO


create function DBA.FinishProformBill(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 fakturata vuv Proform i ProArticles i sled tova po tazi Proforma moje da se izdava faktura
begin atomic
  declare @ProformNo 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;
  //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 @ProformNo=GetCounter('TrueProfNo');
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into Proform( ProformNo,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,DDSType) values(
    @ProformNo,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,@DDSType) ;
  for f as curs 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 from #Articles do
    //Ako e swobodna smetka vmukva vuv uslugite na gosta uslugite ot svobodnata smetka
    //Insertva zapis vuv Articles
    insert into ProArticles( ProformNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo,
      ArticleName,TipDDS,Discount,ServiceNo,DDSType) values( @ProformNo,@RegNo,@ArticlePrice,@CreditNum,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@DDSType) end for;
  //Promenia noshtuvkite
  //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva
  //Promenia CancelInvoiceNo na anuliranata smetka na -1
  return(@ProformNo)
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


GO

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


GO


create function
DBA.FinishVaucherProformBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT double,in @VAT double,in @GrandTotal double,in @WithoutVAT double,in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate double,in @ContractNo integer)
returns integer
//Insertva dannite fakturata vuv Proform i ProVaucherArticles i sled tova po tazi Proforma moje da se izdava faktura za smetka dogovor - vaucher
begin atomic
  declare @ProformNo integer;
  declare @DepositNo integer;
  declare @TrueInvoiceNo integer;
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  //declare @CreditNo integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @ArtNo integer;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #VaucherArticles where TotalNights = 0;
  //Vmukva dannite za fakturata
  set @ProformNo=GetCounter('TrueProfNo');
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into Proform( ProformNo,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,DDSType) values(
    @ProformNo,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,2) ;
  set @ArtNo=1;
  //  return;
  for f1 as curs1 scroll cursor for
    select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,
      SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan,RoomType as @RoomType from
      #VaucherArticles where
      RowType = 1 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
  for f2 as curs2 scroll cursor for
    select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,
      SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan,RoomType as @RoomType from
      #VaucherArticles where
      RowType = 2 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
//------------------------------------------------------------------- ivaylo Depozit
 for f3 as curs3 scroll cursor for
    select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,
      SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan,RoomType as @RoomType from
      #VaucherArticles where
      RowType = 7 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
//------------------------------------------------------------------- ivaylo Depozit
  return(@ProformNo)
end