ALTER TABLE Articles
MODIFY Discount  double NULL

GO

ALTER TABLE Contracts
MODIFY Discount double NOT NULL DEFAULT 0

GO

ALTER TABLE ServicesForContract
MODIFY Discount double NULL DEFAULT 0

GO

ALTER TABLE ProArticles
MODIFY Discount double NULL

GO

ALTER TABLE ProVaucherArticles
MODIFY Discount double NULL

GO

ALTER TABLE SpaArticles
MODIFY Discount double NULL

GO

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

GO

create function
"DBA".CalcPriceWithDiscount(in @Discount double,in @ArtPrice double)
returns double
begin
  return(@ArtPrice-((@ArtPrice*@Discount)/100))
end

GO

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

GO

create function
DBA.FillLodgingsForBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50),in @RRDate date)
returns integer
// Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles
// na noshtuvka(pochivka) i uslugite ot paketa, za opredelen zapis ot Book
// Tia se vika ot PrepareGuestBill, PrepareGroupBill i PrepareReserveBill
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @ArticlePrWithDDS double; //nt
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @ServiceNoLodging integer;
  declare @I integer;
  declare @Disc double;
  select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Book.RegNum = @RegNo; //nt
  select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum;
  select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
  set @ArticlePrWithDDS=@ArticlePrice; //nt

  select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice
    from Packages,Currencies,Services,VidSdelki where
    Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and
    Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
  if @PackagePrice is null then
    set @PackagePrice=0;
    set @PackagePrWithDDS=0
  else

    set @PackagePrWithDDS=@PackagePrice
  end if;

  select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
  if @TipDDS = 2 then
    set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
    set @TipDDS=1
  end if;
  set @ArticlePrice=@ArticlePrice-@PackagePrice;

  if @TipDDS = 1 then
    set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
    set @TipDDS=2
  end if;
  if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm')
  else
    set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm')
  end if;

  set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt

  insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
    CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'Бр.',
    @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
    @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double),
    ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ;
  set @I=1;

  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services where
      RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;

    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Packages.RegNo = Book.RegNum and Book.RegNum = @RegNo; //nt
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;

    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=2
    end if;
    set @ArticlePrWithDDS=@ArticlePrice; //nt

    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'Бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ;
    set @I=@I+1;
    set @Disc=0 end for;
  return(@I)
end


GO

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

GO


create function 
DBA.FillLodgingsForContractBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50))
returns integer
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @ServiceNoLodging integer;
  declare @I integer;
  declare @Disc double; //nt
  declare @ArticlePrWithDDS double; //nt
  declare @IsForAlServices integer;
  declare @ContractNo integer;
  //Proverqva dali ima otstypka po dogovora i dali tazi otstypka e za vsi4ki services
  select C.IsDiscForAll,C.Discount,c.ContractNo into @IsForAlServices,
    @Disc,
    @ContractNo from Contracts as C,Book as B where
    B.ContractPays = C.ContractNo and
    B.RegNum = @Regno;
  if(@IsForAlServices = 0) then set @Disc=0
  end if;
  //@ServiceNoLodging e N-na na noshtuvka ili pochivka syotvetno za nashenec ili chujdenec
  select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum;
  //Izchislenie cenata na noshtuvkata v leva syobrazno otstypkata po dogovora ako ima otstypka
  select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
  set @ArticlePrWithDDS=@ArticlePrice;
  //Podvajda sumata na uslugite ot paketa ot
  select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice
    from Packages,Currencies,Services,VidSdelki where
    Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and
    Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
  if @PackagePrice is null then
    set @PackagePrice=0;
    set @PackagePrWithDDS=0 //nt
  else
    set @PackagePrWithDDS=@PackagePrice //nt
  end if;
  //Izchisliava se tipa DDS za noshtuvkata
  select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
  if @TipDDS = 2 then
    set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
    set @TipDDS=1
  end if;
  set @ArticlePrice=@ArticlePrice-@PackagePrice;
  if @TipDDS = 1 then
    set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
    set @TipDDS=2
  end if;
  if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm')
  else
    set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm')
  end if;
  if(@Disc = 0) then
    select srv.Discount into @Disc
      from ServicesForContract as srv,Book as b where
      b.ContractPays = srv.ContractNo and
      b.RegNum = @RegNo and
      srv.ServiceNo = @ServiceNoLodging
  end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice)
  set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt
  //Insertva noshtuvka ili pochivka
  insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
    CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'бр.',
    @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
    @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double),
    ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ;
  set @I=1;
  set @Disc=0;
  //  if(@IsForAlServices=0) then set @Disc=0
  //  end if
  //  ; //Insertva i uslugite ot paketa
  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services where
      RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;
    //Izchisliava se tipa DDS za uslugata
    //    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract=Contracts.ContractNo and Guests.GuestNum=Book.Guest and Packages.RegNo=Book.RegNum and Book.RegNum=@RegNo; //nt
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;
    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=2
    end if;
    //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice);
    if(@Disc = 0) then
      select Discount into @Disc from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @ServiceNo
    end if;
    //    set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice);
    //Insertva uslugata vuv #Articles
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ;
    set @I=@I+1;
    set @Disc=0 end for;
  return(@I)
end


GO

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


GO

create function                     
DBA.CalcUseSuma(in @Date1 date,in @Date2 date,in @ContrNo integer)
returns decimal(12,2)
begin
  declare sum1 decimal(12,4);
  declare sum2 decimal(12,4);
  declare sum3 decimal(12,4);
  declare vrsum decimal(12,4);
  declare mysum decimal(12,4);
  declare disk double;
  declare gst integer;
  declare @prval decimal(12,4);
  declare @ExchRate decimal(12,4);
  set sum1=0;
  set sum2=0;
  set sum3=0;
  set disk=0;
  set gst=0;
  //  if ()
  set vrsum=0;
  set mysum=0;
  for f1 as Curs1 scroll cursor for
    select b.KodCurrency as KodCurr,sum(b.Price*b.NumLodgings) as CSum,Max(Guest) as CGuest from
      dba.book as b,dba.Contracts as c where
      c.active <> 0 and
      c.ContractNo <> 0 and
      c.GroupNo = 1 and
      c.ContractNo = b.ContractPays and
      c.ContractNo = @ContrNo and
      b.DateRegDt between @Date1 and @Date2
      group by b.KodCurrency do
    set gst=CGuest;
    if(select HumanKind from Guests where GuestNum = gst) = 1 then
      select discount into disk from ServicesForContract where serviceNo = -1 and ContractNo = @ContrNo
    else
      select discount into disk from ServicesForContract where serviceNo = -2 and ContractNo = @ContrNo
    end if;
    select ExchangeRate into @ExchRate from Currencies where KodCurrency = KodCurr;
    if CSum is null then
      set CSum=0
    end if;
    set mysum=CSum;
    set vrsum=mysum*@ExchRate;
    if disk is not null then
      set vrsum=CalcPriceWithDiscount(disk,vrsum)
    end if;
    set sum1=sum1+vrsum end for;
  for f2 as Curs2 scroll cursor for
    select sc.SumCredit as @pr,sc.NumberServices as @quant,sc.Service as @servNo,cr.ExchangeRate as @ExRate from
      dba.Contracts as c,dba.ServiceCredits as sc,dba.Currencies as cr where
      c.active <> 0 and
      c.ContractNo <> 0 and
      cr.KodCurrency = sc.KodCurrency and
      c.GroupNo = 1 and
      sc.ContractPays = c.ContractNo and
      c.ContractNo = @ContrNo and
      sc.DateCreditDt between @Date1 and @Date2 do
    //message 'Service - >'||cast(@pr as varchar);
    set @prval=@pr*@ExRate;
    select discount into disk from ServicesForContract where serviceNo = @servNo and ContractNo = @ContrNo;
    if disk is not null then
      set sum3=CalcPriceWithDiscount(disk,@prval*@quant)
    else
      set sum3=@prval*@quant
    end if;
    set sum2=sum2+sum3 end for;
  if sum2 is null then
    set sum2=0
  end if;
  return(sum1+sum2)
end

GO

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

GO

create procedure 
DBA.CopyContractAtributes(in @ContractNo integer,in @ParentNode integer)
begin
  declare @Disc double;
  declare @IsForAll integer;
  select Discount,IsDiscForAll into @Disc,@IsForAll from Contracts where ContractNo = @ParentNode;
  update Contracts set Discount = @Disc,IsDiscForAll = @IsForAll where ContractNo = @ContractNo;
  delete from ServicesForContract where ContractNo = @ContractNo;
  insert into ServicesForContract( ContractNo,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt) 
    select @ContractNo,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt from
      ServicesForContract where ContractNo = @ParentNode
end

GO

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

GO

create procedure DBA.VaucherExtresType(inout @ArticleNumber integer,in @VaucherNo integer,in @VaucherNum varchar(30),in @BoardPlanNo integer,in @NumAdult integer,in @Numchild integer,in @ServiceCount integer,in @ServiceNum integer,in @NumPeople integer,in @ArticleName varchar(50),in @GuestName varchar(50),in @Disc double,in @HumanKind integer,in @AdultType integer,in @HasExtraBed integer,in @RoomType integer,in @ContractNo integer,in date1 date,in date2 date,in @NumLodgings integer)
begin
  declare @SeassonPriority varchar(200);
  declare @TimePriority varchar(200);
  //  declare date1 date;
  //  declare date2 date;
  declare cdate date;
  declare @Season integer;
  declare br integer;
  declare @SeasonStr varchar(50);
  declare @FreeDays integer;
  declare @OstDays integer;
  declare @SeaIndex integer;
  declare @SeaDays integer;
  declare @FreeDaysStr varchar(50);
  declare @ExtType integer;
  declare @BrSeas integer;
  declare @MaxIndex integer;
  declare @MinIndex integer;
  declare @MaxSeason integer;
  declare @MinSeason integer;
  declare @cIndex integer;
  declare ii integer;
  declare @Sea1 integer;
  declare @brNights integer;
  declare @brFreeDays integer;
  declare @PriceKindStr varchar(50);
  declare @PriceK varchar(50);
  declare @PriceKindInt integer;
  declare @Seasson varchar(50);
  declare @BoardPlan varchar(20);
  declare @Room varchar(20);
  declare @AdditionBed varchar(20);
  declare @RealPrice decimal(12,4);
  declare @Currency integer;
  declare @Adult integer;
  declare @Child integer;
  set @SeassonPriority='';
  set @TimePriority='';
  //  set @OstDays=2;
  set @PriceKindStr='';
  select ExtresCount,ExtresType into @OstDays,@ExtType from Contracts as c where c.ContractNo = @ContractNo;
  //  set @ExtType=3;
  for f5 as curs5 scroll cursor for
    select distinct SeassonNo as @Sea from
      ContractPrices where
      Contract = @ContractNo do
    set @SeassonPriority=@SeassonPriority || @Sea end for;
  //  set date1='20040610';
  //  set date2='20040825';
  set br=0;
  set @TimePriority='';
  set @SeasonStr='';
  set cdate=days(date1,-1);
  set @Season=(select GetSeassonByDate(@ContractNo,date1));
  set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,date1));
  set @PriceK=(cast(@PriceKindInt as char(3)));
  set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK;
  set @TimePriority=@Season;
  while(cdate < date2) loop
    set br=br+1;
    set cdate=days(cdate,1);
    set @Season=(select GetSeassonByDate(@ContractNo,cdate));
    if(@Season <> substr(@TimePriority,length(@TimePriority),1)) then
      if @SeasonStr = '' then
        set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br-1)) as char(3)) || br-1
      else
        set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br
      end if;
      set @TimePriority=@TimePriority || @Season;
      set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,cdate));
      set @PriceK=(cast(@PriceKindInt as char(3)));
      set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK;
      //          message 'br='||br;
      set br=0
    end if
  end loop;
  if length(@TimePriority) = 1 then
    set br=br-1
  end if;
  set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br;
  set @BrSeas=CEILING(length(@SeasonStr)/3);
  if @ExtType = 0 then
    set @SeaIndex=1;
    set @SeaDays=substr(@SeasonStr,1,3);
    set @FreeDaysStr='';
    while @OstDays > @SeaDays loop
      set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays;
      set @OstDays=@OstDays-@SeaDays;
      set @SeaIndex=@SeaIndex+1;
      set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3)
    end loop;
    if @OstDays > 0 then
      set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
    end if;
    while @SeaIndex < @BrSeas loop
      set @FreeDaysStr=@FreeDaysStr || '000';
      set @SeaIndex=@SeaIndex+1
    end loop
  end if;
  if @ExtType = 1 then
    set @SeaIndex=@BrSeas;
    //      message '@SeaIndex='||@SeaIndex;
    set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3);
    //      message '@SeaDays='||@SeaDays;
    set @FreeDaysStr='';
    //      set @SeaIndex=@SeaIndex-1;
    while @OstDays > @SeaDays loop
      set @FreeDaysStr=cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays || @FreeDaysStr;
      set @OstDays=@OstDays-@SeaDays;
      set @SeaIndex=@SeaIndex-1;
      set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3)
    end loop; //        message '@SeaDays in loop='||@SeaDays;
    //        message '@OstDays in loop='||@OstDays;
    if @OstDays > 0 then
      set @SeaIndex=@SeaIndex-1;
      set @FreeDaysStr=cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays || @FreeDaysStr
    end if;
    //      message '@SeaIndex step last='||@SeaIndex;
    while @SeaIndex > 0 loop
      set @FreeDaysStr='000' || @FreeDaysStr;
      set @SeaIndex=@SeaIndex-1
    end loop
  end if;
  if @ExtType = 2 then
    set @FreeDaysStr='';
    set @MaxIndex=0;
    set @cIndex=1;
    set ii=0;
    while ii < length(@TimePriority) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      set @cIndex=locate(@SeassonPriority,cast(@Season as char(1)));
      if @MaxIndex < @cIndex then
        set @MaxIndex=@cIndex
      end if
    end loop;
    set @MaxSeason=substr(@SeassonPriority,@MaxIndex,1);
    set ii=0;
    while(ii < length(@TimePriority)) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      if @Season <> @MaxSeason then
        set @FreeDaysStr=@FreeDaysStr || '000'
      else
        set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3);
        if @OstDays > @SeaDays then
          set @OstDays=@SeaDays
        end if;
        set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
      end if
    end loop
  end if;
  if @ExtType = 3 then
    set @FreeDaysStr='';
    set @MinIndex=20;
    set @cIndex=1;
    set ii=0;
    while ii < length(@TimePriority) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      set @cIndex=locate(@SeassonPriority,cast(@Season as char(1)));
      if @MinIndex > @cIndex then
        set @MinIndex=@cIndex
      end if
    end loop;
    set @MinSeason=substr(@SeassonPriority,@MinIndex,1);
    set ii=0;
    while(ii < length(@TimePriority)) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      if @Season <> @MinSeason then
        set @FreeDaysStr=@FreeDaysStr || '000'
      else
        set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3);
        if @OstDays > @SeaDays then
          set @OstDays=@SeaDays
        end if;
        set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
      end if
    end loop
  end if;
  if @FreeDaysStr is null then
    set @FreeDaysStr=substr('000000000000000000000000',1,length(@TimePriority)*3)
  end if;
  set br=length(@TimePriority);
  set ii=0;
  while ii < br loop
    set ii=ii+1;
    set @Sea1=substr(@TimePriority,ii,1);
    set @brNights=substr(@SeasonStr,(ii-1)*3+1,3);
    set @brFreeDays=substr(@FreeDaysStr,(ii-1)*3+1,3);
    set @brNights=@brNights-@brFreeDays;
    set @PriceKindInt=substr(@PriceKindStr,(ii-1)*3+1,3);
    select Description into @Seasson from PriceSeasons where SeasonNo = @Sea1;
    select BoardPlanName into @BoardPlan from BoardPlan where N = @BoardPlanNo;
    select ShortName into @Room from Classes where Class = @RoomType;
    select Deskription into @AdditionBed from ExtraBed where N = @HasExtraBed;
    select PriceAll,KodCurrency into @RealPrice,@Currency from ContractsPriceList where
      ContractNo = @ContractNo and
      ClassNo = @RoomType and
      PriceKindNo = @PriceKindInt and
      HumanKind = @HumanKind and
      AdultType = @AdultType and
      SeasonNo = @Sea1 and
      BoardPlan = @BoardPlanNo and
      ExtraBed = @HasExtraBed;
    if @AdultType = 0 then
      set @Adult=@NumPeople;
      set @Child=0
    else
      set @Adult=0;
      set @Child=@NumPeople
    end if;
    if @ExtType = 0 then
      if @brFreeDays > 0 then
        // Tuk sa Nostuvki koito sa bezplatni
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
          VaucherRoomType,VaucherBedType,Measure,Adult,Child,
          Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
          GuestName,CreditNo,Discount,ServiceNo,Currency,
          TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
          HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
          RowType,ForVaucherN) values( 
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'бр.',@Adult,@Child,
          @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if;
      if @brNights > 0 then
        // Tuk sa Nostuvki koito sa plateni
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
          VaucherRoomType,VaucherBedType,Measure,Adult,Child,
          Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
          GuestName,CreditNo,Discount,ServiceNo,Currency,
          TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
          HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
          RowType,ForVaucherN) values( 
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'бр.',@Adult,@Child,
          @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if
    else
      if @brNights > 0 then
        // Tuk sa Nostuvki koito sa plateni
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
          VaucherRoomType,VaucherBedType,Measure,Adult,Child,
          Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
          GuestName,CreditNo,Discount,ServiceNo,Currency,
          TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
          HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
          RowType,ForVaucherN) values( 
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'бр.',@Adult,@Child,
          @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if;
      if @brFreeDays > 0 then
        // Tuk sa Nostuvki koito sa bezplatni
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
          VaucherRoomType,VaucherBedType,Measure,Adult,Child,
          Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
          GuestName,CreditNo,Discount,ServiceNo,Currency,
          TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
          HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
          RowType,ForVaucherN) values( 
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'бр.',@Adult,@Child,
          @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if
    end if
  end loop
end

GO

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

GO

CREATE PROCEDURE
DBA.CopyAllContrPropsUpdate(in @FromContractNo integer,in @ToContractNo integer)
begin
  declare @ContractNod integer;
  declare @DiskCNo integer;
  declare @DiskNomer integer;
  declare @Glava integer;
  declare @FullName varchar(40);
  declare @ShortName varchar(15);
  declare @Phone varchar(15);
  declare @Fax varchar(15);
  declare @Email varchar(30);
  declare @WWWAddress varchar(50);
  declare @Discount double;
  declare @Note varchar(50);
  declare @FromDate real;
  declare @ToDate real;
  declare @Active integer;
  declare @FromDateDt date;
  declare @ToDateDt date;
  declare @CardType integer;
  declare @Balance decimal(12,4);
  declare @CardNum varchar(30);
  declare @LimitKind integer;
  declare @Limit integer;
  declare @ContractMol varchar(50);
  declare @ContractBulStad varchar(13);
  declare @ContrDDSBank varchar(50);
  declare @ContrDDSAccount varchar(16);
  declare @IsDiscForAll integer;
  declare @ContrDanNum varchar(15);
  declare @ContractNode integer;
  declare @GroupNo integer;
  declare @IsGroupContract integer;
  declare @ExtresCount integer;
  declare @ContractMolAddress varchar(200);
  declare @ExtresType integer;
  declare @TarNomer integer;
  declare @Br integer;
  if @FromContractNo = 0 then return
  end if;
  delete from ContractPrices where Contract = @ToContractNo;
  for f1 as curs1 scroll cursor for
    select Contract as @CPContract,StartDate as @CPStartDate,
      EndDate as @CPEndDate,PriceKind as @CPPriceKind,
      SeassonNo as @CPSeassonNo from
      ContractPrices where
      Contract = @FromContractNo do
    insert into ContractPrices( Contract,StartDate,EndDate,PriceKind,SeassonNo) values( 
      @ToContractNo,@CPStartDate,@CPEndDate,@CPPriceKind,@CPSeassonNo) end for;
  delete from ContractsPriceList where ContractNo = @ToContractNo;
  for f2 as curs2 scroll cursor for
    select TariffNo as @CTariffNo,ClassNo as @CClassNo,PriceKindNo as @CPriceKindNo,
      HumanKind as @CHumanKind,AdultType as @CAdultType,SeasonNo as @CSeasonNo,
      PriceAll as @CPriceAll,KodCurrency as @CKodCurrency,ExtraBed as @CExtraBed,
      BoardPlan as @CBoardPlan,ContractNo as @CContractNo from
      ContractsPriceList where
      ContractNo = @FromContractNo do
    select max(TariffNo)+1 into @TarNomer from ContractsPriceList;
    insert into ContractsPriceList( TariffNo,ClassNo,PriceKindNo,HumanKind,AdultType,SeasonNo,PriceAll,KodCurrency,ExtraBed,BoardPlan,ContractNo) values( 
      @TarNomer,@CClassNo,@CPriceKindNo,@CHumanKind,@CAdultType,@CSeasonNo,
      @CPriceAll,@CKodCurrency,@CExtraBed,@CBoardPlan,@ToContractNo) end for;
  delete from ServicesForContract where ContractNo = @ToContractNo;
  insert into ServicesForContract( ContractNo,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt)
    select @ToContractNo,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt from
      ServicesForContract where ContractNo = @FromContractNo;
  //  ***************************************************************************************
  delete from OtherDiskForContract where ContractNo = @ToContractNo;
  for f3 as curs3 scroll cursor for
    select DiskForContractNo as @OldDiskForContractNo,DiskName as @DiskName,
      Sign as @Sign,FreeSum as @FreeSum,ServiceCount as @ServiceCount,
      DiskPersent as @DiskPersent from
      OtherDiskForContract where
      ContractNo = @FromContractNo do
    set @DiskCNo=GetCounter('OtherDiskForContract');
    set @DiskNomer=0;
    insert into OtherDiskForContract( DiskForContractNo,DiskName,Sign,FreeSum,ServiceCount,ContractNo,DiskPersent) values(
      @DiskCNo,@DiskName,@Sign,@FreeSum,@ServiceCount,@ToContractNo,@DiskPersent) ;
    for f4 as curs4 scroll cursor for
      select Svoistvo as @Svoistvo,Stoinost as @Stoinost,SvoKind as @SvoKind from
        SvoVaucherStn where
        Glava = @OldDiskForContractNo do
      select count(*) into @Br
        from SvoVaucherStn where
        Glava = @DiskCNo and
        Svoistvo = @Svoistvo and
        Stoinost = @Stoinost and
        SvoKind = @SvoKind;
      if((@Br = 0) or(@Br is null)) then
        insert into SvoVaucherStn values( 
          @DiskCNo,@Svoistvo,@Stoinost,@SvoKind)
      end if;
      end for end for
//   end if
end
//[v 17.07.2003] Jorko
//[v 28.07.2003] Jorko
//[v 21.05.2004] Jorko
//[v 08.11.2004] Jorko
//[v 17.02.2005] Jorko
//[v 08.03.2005] Jorko Koregirana izcialo

GO

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

GO

create procedure 
DBA.CopyAllContractProperties(in @ContractNo integer,in @InSubContract integer,in @ToContractNo integer)
begin
  // InSubContract=1 kopira se v poddogovor
  // InSubContract=0 Kopira se na sustoto nivo
  declare @ContractNod integer;
  declare @DiskCNo integer;
  declare @DiskNomer integer;
  declare @Glava integer;
  //  ******************************************
  //  ******************************************
  declare @NewContract integer;
  declare @FullName varchar(40);
  declare @ShortName varchar(15);
  declare @Phone varchar(15);
  declare @Fax varchar(15);
  declare @Email varchar(30);
  declare @WWWAddress varchar(50);
  declare @Discount double;
  declare @Note varchar(50);
  declare @FromDate real;
  declare @ToDate real;
  declare @Active integer;
  declare @FromDateDt date;
  declare @ToDateDt date;
  declare @CardType integer;
  declare @Balance decimal(12,4);
  declare @CardNum varchar(30);
  declare @LimitKind integer;
  declare @Limit integer;
  declare @ContractMol varchar(50);
  declare @ContractBulStad varchar(13);
  declare @ContrDDSBank varchar(50);
  declare @ContrDDSAccount varchar(16);
  declare @IsDiscForAll integer;
  declare @ContrDanNum varchar(15);
  declare @ContractNode integer;
  declare @GroupNo integer;
  declare @IsGroupContract integer;
  declare @ExtresCount integer;
  declare @ContractMolAddress varchar(200);
  declare @ExtresType integer;
  declare @TarNomer integer;
  declare @Br integer;
  declare @HaveChildren integer;
  if @ContractNo = 0 then return
  end if;
  select FullName,ShortName,Phone,Fax,Email,WWWAddress,Discount,Note,FromDate,
    ToDate,Active,FromDateDt,ToDateDt,CardType,Balance,CardNum,LimitKind,
    Limit,ContractMol,ContractBulStad,ContrDDSBank,ContrDDSAccount,
    IsDiscForAll,ContrDanNum,ContractNode,GroupNo,IsGroupContract,ExtresCount,
    ContractMolAddress,ExtresType into @FullName,
    @ShortName,@Phone,@Fax,@Email,@WWWAddress,@Discount,@Note,
    @FromDate,@ToDate,@Active,@FromDateDt,@ToDateDt,@CardType,@Balance,
    @CardNum,@LimitKind,@Limit,@ContractMol,@ContractBulStad,@ContrDDSBank,
    @ContrDDSAccount,@IsDiscForAll,@ContrDanNum,@ContractNode,@GroupNo,
    @IsGroupContract,@ExtresCount,@ContractMolAddress,
    @ExtresType from Contracts where
    ContractNo = @ContractNo;
  set @NewContract=GetCounter('ContractNo');
  if @ToContractNo = 0 then
    // za nov dogovor stava duma
    //          set @NewContract=GetCounter('ContractNo');
    if @InSubContract = 0 then
      // Kopira na sustoto nivo
      if @ContractNo = @ContractNode then
        insert into Contracts( ContractNo,FullName,ShortName,Phone,Fax,
          Email,WWWAddress,Discount,Note,FromDate,
          ToDate,Active,FromDateDt,ToDateDt,CardType,
          Balance,CardNum,LimitKind,Limit,ContractMol,
          ContractBulstad,ContrDDSBank,ContrDDSAccount,IsDiscForAll,ContrDanNum,
          ContractNode,GroupNo,IsGroupContract,ExtresCount,ContractMolAddress,
          ExtresType) values( 
          @NewContract,@FullName || @NewContract,@ShortName || @NewContract,'','','','',
          0,'',0,
          0,1,cast(null as date),cast(null as date),0,
          0,'',0,0,'','','','',
          0,'',
          @NewContract,@GroupNo,@IsGroupContract,'','',
          0) 
      else
        insert into Contracts( ContractNo,FullName,ShortName,Phone,Fax,
          Email,WWWAddress,Discount,Note,FromDate,
          ToDate,Active,FromDateDt,ToDateDt,CardType,
          Balance,CardNum,LimitKind,Limit,ContractMol,
          ContractBulstad,ContrDDSBank,ContrDDSAccount,IsDiscForAll,ContrDanNum,
          ContractNode,GroupNo,IsGroupContract,ExtresCount,ContractMolAddress,
          ExtresType) values( 
          @NewContract,@FullName || @NewContract,@ShortName || @NewContract,'','','','',
          0,'',0,
          0,1,cast(null as date),cast(null as date),0,
          0,'',0,0,'','','','',
          0,'',
          @ContractNode,@GroupNo,@IsGroupContract,0,'',
          0) 
      end if
    else
      // kopira v poddogocor
      insert into Contracts( ContractNo,FullName,ShortName,Phone,Fax,
        Email,WWWAddress,Discount,Note,FromDate,
        ToDate,Active,FromDateDt,ToDateDt,CardType,
        Balance,CardNum,LimitKind,Limit,ContractMol,
        ContractBulstad,ContrDDSBank,ContrDDSAccount,IsDiscForAll,ContrDanNum,
        ContractNode,GroupNo,IsGroupContract,ExtresCount,ContractMolAddress,
        ExtresType) values( 
        @NewContract,@FullName || @NewContract,@ShortName || @NewContract,'','','','',
        0,'',0,
        0,1,cast(null as date),cast(null as date),0,
        0,'',0,0,'','','','',
        0,'',
        @ContractNo,@GroupNo,0,'','',
        0) 
    end if
  else
    // znachi tuk e pri update na veche sustestvuvast dogovor
    set @NewContract=@ToContractNo;
  end if;
  //  select max(ContractNo)+1 into @NewContract from Contracts;
  for f1 as curs1 scroll cursor for
    select Contract as @CPContract,StartDate as @CPStartDate,
      EndDate as @CPEndDate,PriceKind as @CPPriceKind,
      SeassonNo as @CPSeassonNo from
      ContractPrices where
      Contract = @ContractNo do
    select count(*) into @Br from ContractPrices where
      Contract = @NewContract and
      StartDate = @CPStartDate and
      EndDate = @CPEndDate and
      PriceKind = @CPPriceKind and
      SeassonNo = @CPSeassonNo;
    if((@Br = 0) or(@Br is null)) then
      insert into ContractPrices( Contract,StartDate,EndDate,PriceKind,SeassonNo) values( @NewContract,@CPStartDate,@CPEndDate,@CPPriceKind,@CPSeassonNo) 
    end if end for;
  delete from ContractsPriceList where
    ContractNo = @NewContract;
  for f2 as curs2 scroll cursor for
    select TariffNo as @CTariffNo,ClassNo as @CClassNo,PriceKindNo as @CPriceKindNo,
      HumanKind as @CHumanKind,AdultType as @CAdultType,SeasonNo as @CSeasonNo,
      PriceAll as @CPriceAll,KodCurrency as @CKodCurrency,ExtraBed as @CExtraBed,
      BoardPlan as @CBoardPlan,ContractNo as @CContractNo from
      ContractsPriceList where
      ContractNo = @ContractNo do
    select max(TariffNo)+1 into @TarNomer from ContractsPriceList;
    insert into ContractsPriceList( TariffNo,ClassNo,PriceKindNo,HumanKind,AdultType,SeasonNo,PriceAll,KodCurrency,ExtraBed,BoardPlan,ContractNo) values( @TarNomer,
      @CClassNo,@CPriceKindNo,@CHumanKind,@CAdultType,@CSeasonNo,
      @CPriceAll,@CKodCurrency,@CExtraBed,@CBoardPlan,@NewContract) end for;
  delete from ServicesForContract where ContractNo = @NewContract;
  insert into ServicesForContract( ContractNo,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt)
    select @NewContract,ServiceNo,Discount,CurrLimitCnt,MaxLimitCnt from
      ServicesForContract where ContractNo = @ContractNo;
  //  ***************************************************************************************
  select ContractNode into @ContractNod
    from Contracts where ContractNo = @NewContract;
  if @ContractNod <> @NewContract then
    for f3 as curs3 scroll cursor for
      select DiskForContractNo as @OldDiskForContractNo,DiskName as @DiskName,
        Sign as @Sign,FreeSum as @FreeSum,ServiceCount as @ServiceCount,
        DiskPersent as @DiskPersent from
        OtherDiskForContract where
        ContractNo = @ContractNod do
      //      select max(DiskForContractNo)+1 into @DiskCNo from OtherDiskForContract;
      set @DiskCNo=GetCounter('OtherDiskForContract');
      set @DiskNomer=0;
      select count(*) into @Br
        from OtherDiskForContract where
        DiskName = @DiskName and
        Sign = @Sign and
        FreeSum = @FreeSum and
        ServiceCount = @ServiceCount and
        ContractNo = @NewContract and
        DiskPersent = @DiskPersent;
      if((@Br = 0) or(@Br is null)) then
        insert into OtherDiskForContract( DiskForContractNo,DiskName,Sign,FreeSum,ServiceCount,ContractNo,DiskPersent) values( 
          @DiskCNo,@DiskName,@Sign,@FreeSum,@ServiceCount,@NewContract,@DiskPersent) 
      end if;
      for f4 as curs4 scroll cursor for
        select Svoistvo as @Svoistvo,Stoinost as @Stoinost,SvoKind as @SvoKind from
          SvoVaucherStn where
          Glava = @OldDiskForContractNo do
        select count(*) into @Br
          from SvoVaucherStn where
          Glava = @DiskCNo and
          Svoistvo = @Svoistvo and
          Stoinost = @Stoinost and
          SvoKind = @SvoKind;
        if((@Br = 0) or(@Br is null)) then
          insert into SvoVaucherStn( Glava,Svoistvo,Stoinost,SvoKind) values( 
            @DiskCNo,@Svoistvo,@Stoinost,@SvoKind) 
        end if;
         end for end for
  else
    for f5 as curs5 scroll cursor for
      select DiskForContractNo as @OldDiskForContractNo,DiskName as @DiskName,
        Sign as @Sign,FreeSum as @FreeSum,ServiceCount as @ServiceCount,
        DiskPersent as @DiskPersent from
        OtherDiskForContract where
        ContractNo = @ContractNo do
      set @DiskCNo=GetCounter('OtherDiskForContract');
      //      select max(DiskForContractNo)+1 into @DiskCNo from OtherDiskForContract;
      set @DiskNomer=0;
      select count(*) into @Br
        from OtherDiskForContract where
        DiskName = @DiskName and
        Sign = @Sign and
        FreeSum = @FreeSum and
        ServiceCount = @ServiceCount and
        ContractNo = @NewContract and
        DiskPersent = @DiskPersent;
      if((@Br = 0) or(@Br is null)) then
        insert into OtherDiskForContract( DiskForContractNo,DiskName,Sign,FreeSum,ServiceCount,ContractNo,DiskPersent) values( 
          @DiskCNo,@DiskName,@Sign,@FreeSum,@ServiceCount,@NewContract,@DiskPersent) ;
        for f6 as curs6 scroll cursor for
          select Svoistvo as @Svoistvo,Stoinost as @Stoinost,SvoKind as @SvoKind from
            SvoVaucherStn where
            Glava = @OldDiskForContractNo do
          select count(*) into @Br
            from SvoVaucherStn where
            Glava = @DiskCNo and
            Svoistvo = @Svoistvo and
            Stoinost = @Stoinost and
            SvoKind = @SvoKind;
          if((@Br = 0) or(@Br is null)) then
            insert into SvoVaucherStn( Glava,Svoistvo,Stoinost,SvoKind) values( 
              @DiskCNo,@Svoistvo,@Stoinost,@SvoKind) 
          end if end for
      end if end for
  end if
end
//[v 17.07.2003] Jorko
//[v 28.07.2003] Jorko
//[v 21.05.2004] Jorko
//[v 08.11.2004] Jorko
//[v 08.03.2005] Jorko Koregirana izcialo
//[v 29.03.2005] Jorko dobaven Parameter @ToContractNo i prisposobena i za Update

GO

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

GO

create procedure DBA.PrepareGuestBill(in @place varchar(50))
result(@Disc double)
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc double;
  //  declare @DateCr date;
  delete from #Articles;
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber=1
  end if;
  for g as curs1 scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    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,B.DateRegDt as @RDate from
        Book as B where
        B.WhoPays = @GuestNo and(B.NumLodgings-B.PayedLodgings) <> 0 do
      set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
        @ArticleNumber,@place,@RDate) end for end for;
  for gg as curs11 scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @GuestNo;
    for ff as cursf 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,
        SC.DateCreditDt as @DateCr,
        SC.AutomaticService as @AutomaticService from
        ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C where
        SC.WhoPays = @GuestNo and
        (NumberServices-PayedServices) <> 0 and
        SC.Service = S.ServiceNo and
        S.TipSdelka = VS.N and
        SC.KodCurrency = C.KodCurrency and
        ((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) do
      if(@Room is not null) then
        if @AutomaticService = 1 then
          set @ArticleName='* ' || @ArticleName || @place || cast(@Room as varchar)
        else
          set @ArticleName=@ArticleName || @place || cast(@Room as varchar)
        end if
      else
        set @ArticleName=@ArticleName
      end if;
      //      if(@Disc<>0) then
      //        set @Price=CalcPriceWithDiscount(@Disc,@Price)
      //      end if
      //
      call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr);
      set @ArticleNumber=@ArticleNumber+1 end for end for;
  set @Deposit=0;
  for gk as cursgk scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    set @Deposit=@Deposit+CalcDepositSum(@GuestNo)/(1+bvrDDSPart) end for;
  if @Deposit <> 0 then
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0,
      0,0,-@Deposit,2,1005,null) 
  end if;
end
//[v 18.02.2003] Jorko