IF (EXISTS(Select * from sys.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);
  declare @ServiceNoLodging integer;
  declare @ServiceTipDDS integer;
  declare @Nomer integer;
  declare @NextStartDate date;
  declare @DateArrive date;
  declare @DateLeave date;
  declare @MonthIsWith30Days integer;
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  select FloatToDate(DateReg),
    FloatToDate(DateReg+NumLodgings) into @DateArrive,@DateLeave
    from Book
    where Book.RegNum = @RegNo;
  set @IsFullMonth = 1;
  set @ArtPrice = GetRentPriceForDate(@RegNo,@DateReg);
  if(@ArtPrice is null) then
    set @ArtPrice = @ArticlePrice;
    set @ServiceTipDDS = @TipDDS;
    select V.TipDDS,if V.TipDDS = 3 then 0 else VR.Rate endif,
      VR.DDSType into @ServiceTipDDS,@VatRate,@DDSType from Book as B,PriceKinds as PK,Services as S join VATRates as VR on S.VatRateN = VR.ID,VidSdelki as V where B.RegNum = @RegNo and B.PriceKind = PK.PriceKind and S.ServiceNo = PK.ServiceNo and S.TipSdelka = V.N;
  else
    select V.TipDDS,if V.TipDDS = 3 then 0 else VR.Rate endif,
      VR.DDSType into @ServiceTipDDS,@VatRate,@DDSType from Book as B,PriceKinds as PK,Services as S join VATRates as VR on S.VatRateN = VR.ID,VidSdelki as V where B.RegNum = @RegNo and B.PriceKind = PK.PriceKind and S.ServiceNo = PK.ServiceNo and S.TipSdelka = V.N;
    if @ServiceTipDDS = 2 then
      set @ArticlePrice = @ArticlePrice*(@VatRate+1); --ako dds-to e vkliucheno v cenata
      set @ServiceTipDDS = 1
    end if;
    set @ArticlePrice = @ArticlePrice-@PackagePrice;
    if @ServiceTipDDS = 1 then
      set @ArticlePrice = @ArticlePrice/(@VatRate+1); --ako dds-to ne e vkliucheno v cenata
      set @ServiceTipDDS = 2
    end if end if;
  select R.RoomName into @RoomName from Book as B,Rooms as 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
  --Yoan +
  select Max(N) into @Nomer from PriceSeasonPeriods as PSP where((PSP.StartDate <= @DateReg) and(PSP.EndDate >= @DateReg));
  --select Max(StartDate) into @NextStartDate from PriceSeasonPeriods as PSP where PSP.N = @Nomer+1;
  select dateadd(day,1,EndDate) into @NextStartDate from PriceSeasonPeriods as PSP where PSP.N = @Nomer;
  if(select count() from PriceSeasonPeriods where StartDate = @NextStartDate) = 0 then set @NextStartDate = null
  end if;
  if(@NextStartDate < @DateEnd) then
    set @DateEnd = @NextStartDate;
    set @IsFullMonth = 0
  end if;
  --Yoan -
  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((Month(@DateStart) = Month(@DateArrive)) and(Year(@DateStart) = Year(@DateArrive))) or((Month(@DateStart) = Month(@DateLeave)) and(Year(@DateStart) = Year(@DateLeave))) then
    set @MonthIsWith30Days = (select MonthIsWith30Days from Hotels where Hotel_ID = (select IntStojnost from Registers where RegisterName = 'HotelID'))
  else
    set @MonthIsWith30Days = 0
  end if;
  if(@IsFullMonth = 0) then
    if(@MonthIsWith30Days) = 0 then
      set @DaysInMonth = day(dateadd(day,-day(@DateStart),dateadd(month,1,@DateStart)));
      if(day(@DateStart) = 31) or(month(@DateStart) = 1) then
        set @DaysInMonth = 31
      end if
    else set @DaysInMonth = 30
    end if;
    set @ArticleName = @ArticleName || ' ' || @RealLodgings || '/' || @DaysInMonth;
    set @ArtPrice = (@ArtPrice*@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,RentMonth,RentYear,VatRate,DDSType ) 
    values( @RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@ServiceTipDDS,MONTH(@DateStart),YEAR(@DateStart),@VatRate,@DDSType ) ;
  -- 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
-- [23.05.2009] - Dobrin - dobaveno e popylvaneto na poletata RentMonth i RentYear v #ArticleLodgings
-- [29.05.2009] - Dobrin - syobrazqva se s Hotels.IsMonthWith30Days
-- [20.07.2009] - Yoan - izchislqva pravilno pri PriceSeasonPeriods nachalna data razlichna ot 1vi
-- [24.09.2009] - Yoan - izchislqva pravilno pri nachalo 31vi