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

GO

create procedure DBA.PrepareFacByProform( in @ProformNo integer,in @place varchar(50) ) 
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus noshtuvkite,pochivkite i uslugite koito triabva da plashtat gostite
//se pravi faktura po predvaritelno izbrana proforma
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc double;
  declare @PrWithDisc double;
  declare @IsForAlServices integer;
  declare @HumanKind integer;
  declare @MaxQty integer;
  declare @MinQty integer;
  declare @ArticlePrWithDDS double;
  //Izprazva vremennata tablica za uslugi i noshtuvki
  delete from #Articles;
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber = 1
  end if;
  for f as curs scroll cursor for
    select ArticleName as @ArticleName,Qty as @Qty,Price as @ArticlePrice,Discount as @Discount,RegNo as @RegNo,
      CreditNo as @CreditNo,TipDDS as @TipDDS,ServiceNo as @ServiceNo,VatRate as @VatRate,DDSType as @DDSType
      from ProArticles where ProformNo = @ProformNo and ArticleNo > 0 do
    if(@Qty < 0) then
      set @MaxQty = 0;
      set @MinQty = @Qty
    else
      set @MaxQty = @Qty;
      set @MinQty = 0
    end if;
    set @ArticlePrWithDDS = @ArticlePrice;
    if @TipDDS = 1 then
      set @ArticlePrice = @ArticlePrice/(@VatRate+1);
      set @TipDDS = 2
    end if;
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,
      RegNo,CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,VatRate,DDSType ) 
      values( @ArticleNumber,@ArticleName,'бр.',@Qty,@MaxQty,@MinQty,@ArticlePrice,@Discount,@RegNo,
      @CreditNo,((@ArticlePrice*@Qty)-((@ArticlePrice*@Qty)*@Discount)/100),@TipDDS,@ServiceNo,@ArticlePrWithDDS,
      ((@ArticlePrWithDDS*@Qty)-((@ArticlePrWithDDS*@Qty)*@Discount)/100),null,@VatRate,@DDSType ) ;
    set @ArticleNumber = @ArticleNumber+1
  //Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor
  /*
select C.IsDiscForAll,C.Discount into @IsForAlServices,@Disc from Contracts as C where C.ContractNo=@ContractNo;
if(@IsForAlServices=0) then set @Disc=0
end if
;
for f as curs scroll cursor for
select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,
B.Room as @Room,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,IsRest as @IsRest
from Book as B
where B.ContractPays=@ContractNo and(B.NumLodgings-B.PayedLodgings)<>0 do
//select(if HumanKind=1 then-1 else-2 endif) into @HumanKind from Guests where GuestNum=@Guest;
//    if(@Disc=0) then
//      select Discount into @Disc from ServicesForContract where ServiceNo=@HumanKind and ContractNo=@ContractNo;
//      set @Price=CalcPriceWithDiscount(@Disc,@Price)
//    end if
//    ;
//    message 'Cenata s otstupkata - >'+cast(@Price as varchar);
//set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for;
set @ArticleNumber=@ArticleNumber+FillLodgingsForContractBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for;
//    if(@IsForAlServices=0) then set @Disc=0
//    end if
//Naliva uslugi po dogovor
if(@IsForAlServices=0) then set @Disc=0
end if
;
for f1 as curs scroll cursor for
select SC.CreditNo as @CreditNo,
S.ServiceName as @ArticleName,SC.RoomNum as @Room,
(SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service,
(NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS
from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C
where SC.ContractPays=@ContractNo and(NumberServices-PayedServices)<>0 and SC.Service=S.ServiceNo and S.TipSdelka=VS.N and SC.KodCurrency=C.KodCurrency do
if(@Room is not null) then
set @ArticleName=@ArticleName||@place||cast(@Room as varchar)
else
set @ArticleName=@ArticleName
end if
;
if(@Disc=0) then
select Discount into @Disc from ServicesForContract where ServiceNo=@Service and ContractNo=@ContractNo;
message 'Otstypka - >'+cast(@Disc as varchar);
//set @Price=CalcPriceWithDiscount(@Disc,@Price);
message 'Cenata s otstupkata - >'+cast(@Price as varchar)
end if
;
call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc);
set @Disc=0;
set @ArticleNumber=@ArticleNumber+1 end for;
set @Deposit=0;
set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo)/(1+bvrDDSPart);
if @Deposit<>0 then
insert into #Articles(ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
CreditNo,ArticleSum,TipDDS,ServiceNo) values(@ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0,
0,0,-@Deposit,2,1004)
end if;
*/
  end for
end
//[v 18.02.2003] Jorko