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

create function "DBA"."FillSingleArticleLodging"(in @RegNo integer, in @DateReg date, in @NumDays integer, in @ArticlePrice numeric(12,2), in @PackagePrice numeric(12,2), in @ServiceName varchar(40), in @FillAll integer, in @TipDDS integer, in @place varchar(50))
returns integer
begin
  declare @R integer;
  declare @ArticleName varchar(100);
  declare @DateStart date;
  declare @DateEnd date;
  declare @IsFullMonth integer;
  declare @RealLodgings integer;
  declare @DaysInMonth integer;
  declare @ArtPrice numeric(12,2);
  declare @Qty integer;
  declare @ZIndex integer;
  declare @RoomName varchar(6);
  declare @YearService varchar(4);
  
  set @IsFullMonth=1;
  set @ArtPrice = @ArticlePrice;
  
  select R.RoomName into @RoomName from Book B, Rooms R where B.RegNum=@RegNo and B.Room=R.RoomNum;
  
  // setva nachalnata data
  set @DateStart=@DateReg;
  if day(@DateStart) <> 1 then
    set @IsFullMonth=0;
  end if;
  
  // setva krajnata data
  set @DateEnd=dateadd(day,-day(@DateReg)+1,@DateReg); // setva DateEnd na purviat den ot meseca na nachalnata data
  set @DateEnd=dateadd(month,1,@DateEnd); // dobavia 1 mesec za da stane purviat den ot seldvashtiat mesec na nachalnata data
  if @DateEnd>dateadd(day,@NumDays,@DateReg) then
    set @DateEnd=dateadd(day,@NumDays,@DateReg);
    set @IsFullMonth=0;
  end if;

  //izchisliava broiat dni
  set @RealLodgings = datediff(day,@DateStart,@DateEnd);
  
  // setva imeto na uslugata
  set @YearService = ''||datepart(year,@DateStart);
  set @YearService = right(@YearService,2);
  set @ArticleName=@ServiceName||' '||ImeMesec(month(@DateStart))||''''||@YearService||@place||@RoomName;
  if (@IsFullMonth=0) then 
    set @DaysInMonth=day(dateadd(day,-day(@DateStart),dateadd(month,1,@DateStart)));
	set @ArticleName=@ArticleName||' '||@RealLodgings||'/'||@DaysInMonth;
	set @ArtPrice=(@ArticlePrice*@RealLodgings)/@DaysInMonth;
  end if;
  
  // izchisliava dali da e po podrazbirane izbrana uslugata za plashtane ili ne
  set @Qty=1;
  if (@FillAll=0) then
    if (month(@DateStart) > month(now()) and year(@DateStart) = year(now()) ) or (year(@DateStart)>year(now())) then
	  set @Qty=0;
	end if;
  end if;

  // vkarva ZIndex za da ne moje naprimer da se plati naem za oktomvri predi da e 
  // platen naema za septemvri - tova se pravi vuv Bills formata
  select Max(ZIndex)+1 into @ZIndex from #ArticleLodgings;
  if @ZIndex  is null then 
    set @ZIndex=1;
  end if;
  // vkarva uslugata
  insert into #ArticleLodgings (RegNum,"Name",RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS)
     values (@RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@TipDDS);
  
  // namalia @NumDays sus realnite noshtuvki i vrushta rezultata
  set @R=@NumDays-@RealLodgings;
  return (@R);
end;
// [20.02.2009] - Nixon - dobavena godinata kum imeto na smetkata
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);
  
  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;
		  // ili kogato e premestvan ot druga staia i v drugata staia e nastanen predi datata za plashtane - nachisliava
		else  
		  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 SSCYear,SvoServiceCreditsStn as SSCMonth,SvoServiceCreditsStn as SSCRegNo where
              SC.Service = @ForfeitServiceNo and WhoPays = @WhoPays and
              SC.CreditNo = SSCYear.Glava and SSCYear.Svoistvo = 5 and SSCYear.Stoinost = convert(varchar(100),@ForfeitYear) and
              SC.CreditNo = SSCMonth.Glava and SSCMonth.Svoistvo = 4 and SSCMonth.Stoinost = convert(varchar(100),@ForfeitMonth) and
              SC.CreditNo = SSCRegNo.Glava and SSCRegNo.Svoistvo = 7 and SSCRegNo.Stoinost = convert(varchar(100),@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;
              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,FloatToDate(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) ;
              // 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 @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
        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

go