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

GO

Create Procedure
  DBA.VaucherGuestExtres(
                        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,
                        in @VatType integer,
                        in @TmpGuestID integer
                       )
begin
  -- prerabotena ot VaucherExtresType - zapisva samo sumata na bezpl. noshtuvki s obraten znak
  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 double;
  declare @Currency integer;
  declare @Adult integer;
  declare @Child integer;
  declare @DiscName varchar(50);
  declare @GuestDiscount double;
-- --------------------------------------
  Message '+ VaucherGuestExtres START';
  set @SeassonPriority='';
  set @TimePriority='';
  set @PriceKindStr='';
  select Min(DiskName), cast(Min(FreeSum) as Integer) as OstDays, Min(PersentOrSum) as ExtType 
    into @DiscName, @OstDays, @ExtType
    from OtherDiskForVaucher where ContractNo = @ContractNo and DiskType = 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 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='';
    -- формиране на @FreeDaysStr - по 3 символа (число с водещи нули) за сезон
    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;
-- Yanko +, fix - ако безплатните дни са повече от заявените, не искаме да изпаднем в безкраен цикъл
      if substr(@SeasonStr,(@SeaIndex-1)*3+1,3) = '' then
        set @OstDays = 0
      end if;
-- Yanko -
      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 @VatType = 1 then
      set @RealPrice=(@RealPrice/1.07)
    else
      set @RealPrice=@RealPrice
    end if;
    set @RealPrice = IsNull(@RealPrice,0);
    if @AdultType = 0 then
      set @Adult=@NumPeople;
      set @Child=0
    else
      set @Adult=0;
      set @Child=@NumPeople
    end if;

    set @GuestDiscount = (select first FreeSum from OtherDiskForVaucher where ContractNo=@ContractNo and DiskType=0);
    --set @Disc = @Disc + @GuestDiscount;
    set @Disc = @GuestDiscount;
    set @DiscName = (select first DiskName from OtherDiskForVaucher where ContractNo=@ContractNo and DiskType=3)
      || ' (' || @Seasson || ')';

    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,TempGuestID) values(
          @ArticleNumber,0,'','','',
          '','','бр.',null,null,
          null,null,null,(-1)*CalcPriceWithDiscount(@Disc,(@NumPeople*@brFreeDays*@RealPrice)),
          @DiscName,'',0,@Disc,@ServiceNum,@Currency,
          @DiscName,@Seasson,@AdditionBed,'',@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          12,@VaucherNo,@TmpGuestID) ;
        set @ArticleNumber=@ArticleNumber+1
      end if;
    else
      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,TempGuestID) values(
          @ArticleNumber,0,'','','',
          '','','бр.',null,null,
          null,null,null,(-1)*CalcPriceWithDiscount(@Disc,(@NumPeople*@brFreeDays*@RealPrice)),
          @DiscName,'',0,@Disc,@ServiceNum,@Currency,
          @DiscName,@Seasson,@AdditionBed,'',@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          12,@VaucherNo,@TmpGuestID) ;
        set @ArticleNumber=@ArticleNumber+1
      end if
    end if
  end loop;
  Message '+ VaucherGuestExtres END';
end

//v.1 [17.Jul.2007] - Yanko, razdeliane po gosti
//v.2 [14.Sep.2007] - Yanko, fix na @DiskName