Delete from ServiceCredits where Service=(select IntStojnost from Registers where RegisterName = 'ForfeitService') and PayedServices=0;

Go

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;
	
  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 into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
      if @TipDDS = 1 then
        set @ArticlePrice=@ArticlePrice/(bvrDDSPart+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+bvrDDSPart) 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) 
            end if
          end if -- @Interest > 0
        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
    end if -- @ForfeitNumMonths > 1 or ...
  end if --(@NachinPlat = 1) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0)
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

Go

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

GO

create function
DBA.CalcBalance(in @GuestNo integer)
returns decimal(12,4)
-- Presmiata balansa za @GuestNo v mestna valuta (leva) po tekushtia kurs:
--   Balans = SUM(Neplateni usligi) - SUM(Depositi).
begin
  declare @D decimal(12,4);
  declare @S decimal(12,4);
  declare @B decimal(12,4);
  declare @SCS decimal(12,4);
  --
  --message STRING('-- CalcBalance for Guest=',@GuestNo) type info to console;
  set @D=CalcDepositSum(@GuestNo);
  set @S=CalcServiceSum(@GuestNo);
  set @SCS=CalcServiceCreditsSum(@GuestNo);
  set @B=@S-@D;
  --message '---- CalcBalance again...',;
  update Guests set DepositSum = @D,ServiceSum = @S,Balance = @B,ServiceCreditsSum = @SCS where
    GuestNum = @GuestNo;
  --message '-- CalcBalance Done.',@GuestNo;
  return(@B)
end
--[v 00.06.23]
--[v 2008.12.10 - MON] - Nixon - da zapisva i kolonata ServiceCreditsSum

Go

for A as @ACurs scroll cursor for
  select RegNum as @RegNum from Book do
	call AddForfeitsForBook(@RegNum,date(today()));
end for;
	
Go