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

GO

create procedure DBA.AddForfeitsForBook( in @RegNo integer,in @CurrentDate date ) 
begin atomic
  declare @Price numeric(12,2);
  declare @ArticlePrice numeric(12,2);
  declare @MDays integer;
  declare @NumDays integer;
  declare @DateStart date;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @StartDateNotPayed date;
  declare @CoveredByDepositsDate date;
  declare @Interest integer;
  declare @ForfeitServiceNo integer;
  declare @ForfeitNumMonths integer;
  declare @InterestMonths integer;
  declare @ForfeitMonth integer;
  declare @ForfeitYear integer;
  declare @OldCreditNo integer;
  declare @OldForfeitPrice decimal(12,2);
  declare @OldForfeitPayed integer;
  declare @OldRegNo integer;
  declare @PayChislo integer;
  declare @ForfeitName varchar(100);
  declare @DaysLate integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @TipDDS integer;
  declare @NowDate date;
  declare @WhoPays integer;
  declare @Guest integer;
  declare @AlternativeName varchar(100);
  declare @TempArticlePrice decimal(12,2);
  declare @ForfeitPrice decimal(12,2);
  declare @ForfeitTipDDS integer;
  declare @ForfeitCreditNo integer;
  declare @KodCurrency integer;
  declare @IteratorMonths integer;
  declare @ShouldAddForfeit integer;
  declare @YearService varchar(4);
  declare @LastDateStart date;
  declare @DoDelete integer;
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  select B.Room,B.Guest,R.RoomName,PK.NachinPlat,B.Price,isNull(PK.ServiceNo,-1),dateadd(day,B.PayedLodgings,B.DateRegDt),
    B.WhoPays,KodCurrency,B.NumLodgings-B.PayedLodgings into @RoomNo,@Guest,
    @RoomName,@NachinPlat,@Price,@ServiceNoLodging,@StartDateNotPayed,
    @WhoPays,@KodCurrency,
    @NumDays from Book as B,Rooms as R,PriceKinds as PK
    where B.RegNum = @RegNo and B.Room = R.RoomNum and B.PriceKind = PK.PriceKind;
  select S.ServiceNo,S.ServiceName,VS.TipDDS into @ForfeitServiceNo,@ForfeitName,@ForfeitTipDDS from Registers as R,Services as S,VidSdelki as VS
    where R.RegisterName = 'ForfeitService' and R.IntStojnost = S.ServiceNo and S.TipSdelka = VS.N;
  if @ForfeitServiceNo is null then
    set @ForfeitServiceNo = 0
  end if;
  select IntStojnost into @PayChislo from Registers where RegisterName = 'PayChislo';
  if @PayChislo is null then
    set @PayChislo = 0
  end if;
  if(@NachinPlat = 1) and(@ForfeitServiceNo <> 0) and(@PayChislo > 0) then
    set @NowDate = dateadd(day,-@PayChislo,@CurrentDate);
    set @CoveredByDepositsDate = GetDateCoveredByDeposits(@RegNo);
    if(@CoveredByDepositsDate > @StartDateNotPayed) then
      set @StartDateNotPayed = @CoveredByDepositsDate
    end if;
    set @ForfeitNumMonths = datediff(month,@StartDateNotPayed,@NowDate)+1;
    -- proveriava dali da se nachisliava neustojka
    if(@ForfeitNumMonths > 1) then
      set @ShouldAddForfeit = 1
    else
      if(@ForfeitNumMonths < 1) then
        set @ShouldAddForfeit = 0
      else -- kogato @ForfeitNumMonths = 0
        set @ShouldAddForfeit = 0;
        -- kogato e nastanen predi datata za plashtane na naema - nachisliava
        if(datepart(day,@StartDateNotPayed) < @PayChislo) then
          set @ShouldAddForfeit = 1
        else -- ili kogato e premestvan ot druga staia i v drugata staia e nastanen predi datata za plashtane - nachisliava
          if(exists(select RegNum from Book where Guest = @Guest and Staying = 2 and dateadd(day,PayedLodgings,DateRegDt) < @NowDate)) then
            set @ShouldAddForfeit = 1
          end if end if end if end if;
    if(@ShouldAddForfeit = 1) then
      select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
      select V.TipDDS,if @TipDDS = 3 then 0 else VR.Rate endif,VR.DDSType 
	  into @TipDDS,@VatRate,@DDSType from Services as S join VatRates as VR on S.VatRateN = VR.ID,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
      if @TipDDS = 1 then
        set @ArticlePrice = @ArticlePrice/(@VatRate+1); --ako dds-to ne e vkliucheno v cenata
        set @TipDDS = 2
      end if;
      set @DateStart = @StartDateNotPayed;
      set @IteratorMonths = @ForfeitNumMonths;
      while((@NumDays > 0) and(@IteratorMonths > 0)) loop
        set @IteratorMonths = @IteratorMonths-1;
        delete from #ArticleLodgings;
        set @InterestMonths = datediff(month,@DateStart,@CurrentDate)+1;
        select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,0,'наем',1,@TipDDS,'') into @MDays;
        select(if TipDDS = 2 then Price*(1+@VatRate) else Price endif)
          into @TempArticlePrice from #ArticleLodgings;
        if(@TempArticlePrice > 0) then
          -- nachisliava neustojkata ako e nujno
          -- izchisliava procenta neustojka
          set @Interest = 6*@InterestMonths;
          if @InterestMonths = 1 then
            set @DaysLate = datepart(day,@NowDate);
            if @DaysLate > 0 then
              if @DaysLate <= 6 then
                set @Interest = @DaysLate
              end if
            else set @Interest = 0
            end if end if; -- @InterestMonths=1
          if(@Interest > 0) then
            set @ForfeitMonth = month(@DateStart);
            set @ForfeitYear = year(@DateStart);
            -- proveriava dali veche e nachislena neustojka
            set @OldCreditNo = null;
            set @ForfeitPrice = Round(@Interest*@TempArticlePrice/100,2);
            select SC.CreditNo,SC.PayedServices,SC.SumCredit,convert(integer,SSCRegNo.Stoinost) into @OldCreditNo,
              @OldForfeitPayed,@OldForfeitPrice,
              @OldRegNo from ServiceCredits as SC,SvoServiceCreditsStn as SSCNachalo,SvoServiceCreditsStn as SSCKrai,SvoServiceCreditsStn as SSCRegNo
              where SC.Service = @ForfeitServiceNo and WhoPays = @WhoPays
              and SC.CreditNo = SSCNachalo.Glava and SSCNachalo.Svoistvo = 2 and SSCNachalo.Stoinost = @DateStart
              and SC.CreditNo = SSCKrai.Glava and SSCKrai.Svoistvo = 3 and SSCKrai.Stoinost = "date"(dateadd(day,@NumDays-@MDays-1,@DateStart))
              and SC.CreditNo = SSCRegNo.Glava and SSCRegNo.Svoistvo = 7 and SSCRegNo.Stoinost = @RegNo;
            if(@OldCreditNo is not null) then
              if(@OldForfeitPayed = 0) and(@OldForfeitPrice <> @ForfeitPrice) then
                delete from ServiceCredits where CreditNo = @OldCreditNo
              else
                set @Interest = 0
              end if end if;
            -- dobavia lihvata vuv servicecredits
            if(@Interest > 0) then
              set @YearService = '' || datepart(year,@DateStart);
              set @YearService = "right"(@YearService,2);
              set @AlternativeName = @ForfeitName || ' ' || ImeMesec(month(@DateStart)) || '''' || @YearService; //|| ' в стая ' || @RoomName;
              if(Day(@DateStart) = 1) and(Day(dateadd(day,@NumDays-@MDays,@DateStart)) = 1) then
              else
                set @AlternativeName = @AlternativeName || ' ' || @NumDays-@MDays || '/' || DOM(@ForfeitMonth,@ForfeitYear,0)
              end if;
              set @ForfeitPrice = Round(@Interest*@TempArticlePrice/100,2);
              set @ForfeitCreditNo = GetCounter('CreditCounter');
              insert into ServiceCredits( CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,
                NumberServices,PayedServices,WhoPays,GroupPays,ContractPays,RoomPays,RoomNum ) 
                values( @ForfeitCreditNo,DateToFloat(Now()),@WhoPays,@ForfeitServiceNo,@ForfeitPrice,1,bvrUserN,1,0,@WhoPays,0,0,0,@RoomNo ) ;
              -- dobavia dopulnitelnite svojstva
              -- alternativno ime
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,1,@AlternativeName ) ;
              -- mesec
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,4,@ForfeitMonth ) ;
              -- godina
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,5,@ForfeitYear ) ;
              -- period start
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,2,@DateStart ) ;
              -- period end
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,3,"date"(dateadd(day,@NumDays-@MDays-1,@DateStart)) ) ;
              -- procent lihva
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,6,@Interest ) ;
              -- RegNo
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost ) 
                values( @ForfeitCreditNo,7,@RegNo ) ;
              call FillSaleOrderLines_RegService(@WhoPays,@ForfeitCreditNo)
            -- @Interest > 0
            end if end if end if; -- (@TempArticlePrice > 0)
        --set @LastDateStart=@DateStart; --Yoan
        set @DateStart = dateadd(day,@NumDays-@MDays,@DateStart);
        --if (MONTH(@DateStart)=MONTH(@LastDateStart) and YEAR(@DateStart)=YEAR(@LastDateStart)) then
        --  set @DoDelete=0
        --else
        --  set @DoDelete=1;
        --end if;
        set @NumDays = @MDays
      end loop
    -- @ForfeitNumMonths > 1 or ...
    --(@NachinPlat = 1) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0)
    end if
  end if
end
--[09.02.2009 - Nixon] - Promeneno e lihvata da stava s natrupvane - t.e. ako zakusnee s poveche ot
-- edin mesec shte bude 12%, s 3 meseca  - 18% i t.n.
--[13.02.2009 - Nixon] - Promeneno e ako se zakusnee poveche ot edin mesec za tekushtiat mesec pak da vzima vpredvid PayChislo-to
--[20.02.2009] - Nixon - dobavena godinata kum imeto na smetkata
--[24.08.2009] - Yoan - Raboti s PriceSeasonPeriods