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

GO

create procedure
DBA.FillOwePaidRentMonth(@Month date,@DoDelete tinyint= 0)
--Spravka daljim i platen naem - mesec
as --Izpolzva se ot FillOweRentForMonth()
begin
  declare @StartDateMonth date,
  @EndDateMonth date,
  @TempDate date,
  @NextSeasonStartDate date
  set @StartDateMonth="date"('1.' || MONTH(@Month) || '.' || YEAR(@Month))
  set @EndDateMonth=dateadd(day,-1,dateadd(month, 1, @StartDateMonth))
	--message(@StartDateMonth)
	--message(@EndDateMonth)
  if(@DoDelete = 1)
    delete from #OwePaidRentMonth
  while(@StartDateMonth < @EndDateMonth)
    begin
      select @NextSeasonStartDate = Max(StartDate) from PriceSeasonPeriods as PSP where PSP.N = ((select Max(N) from PriceSeasonPeriods as PSP1 where((PSP1.StartDate <= @StartDateMonth) and(PSP1.EndDate >= @StartDateMonth)))+1)
      if(@NextSeasonStartDate is null)
        set @NextSeasonStartDate=@EndDateMonth
			--message(@NextSeasonStartDate)
      if(@NextSeasonStartDate < @EndDateMonth)
        begin
          insert into #OwePaidRentMonth( GuestNo,NameMonth,Year,Name,UniName,Kurs,Spec,FN,Address,City,
            Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,Razlika,
            RealDatePaid,Koef,RoomName,Country,DaysInMonth) 
            select B.Guest,
              DBA.ImeMesec(MONTH(@Month)),
              YEAR(@Month),
              G.Name,
              U.FullName,
              K.Name,
              S.Name,
              G.FN,
              G.Address,
              Settlements.SettleMentName,
              G.Sex,
              PK.Description,
              DBA.GetNoshtuvkaNameForPeriodInMonth(B.RegNum,@StartDateMonth,@NextSeasonStartDate-1),
              Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,@StartDateMonth),B.Price*C.ExchangeRate),
              Kol=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@NextSeasonStartDate-1),2),
              SumOwe=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@NextSeasonStartDate-1)*Cena,2),
              KolPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@NextSeasonStartDate-1),2),
              SumPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@NextSeasonStartDate-1)*Cena,2),
              Razlika=SumOwe-SumPaid,
              DBA.GetRealDatePayedRentForPeriod(B.RegNum,@StartDateMonth,@NextSeasonStartDate-1),
              Kol,
              R.RoomName,
              Ctr.Description,
              DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@NextSeasonStartDate-1)+1 from
              DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,
              DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R,DBA.Countries as Ctr,DBA.Settlements where
              B.PriceKind = PK.PriceKind and
              G.GuestNum = B.WhoPays and
              PK.NachinPlat = 1 and
              B.DateRegDt < dateadd(month,1,"date"('1.' || MONTH(@Month) || '.' || YEAR(@Month))) and
              dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || MONTH(@Month) || '.' || YEAR(@Month)) and
              G.Kurs = K.N and
              G.SpecNo = S.N and
              G.UniN = U.N and
              B.KodCurrency = C.KodCurrency and
              R.RoomNum = B.Room and
              Settlements.N = G.SettlementN and
              G.NativeCountry = Ctr.CountryKod and
              B.Staying <> 3 and
              Kol <> 0.0
          set @StartDateMonth=@NextSeasonStartDate
        end
      else
        begin
          insert into #OwePaidRentMonth( GuestNo,NameMonth,Year,Name,UniName,Kurs,Spec,FN,Address,City,
            Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,Razlika,
            RealDatePaid,Koef,RoomName,Country,DaysInMonth) 
            select B.Guest,
              DBA.ImeMesec(MONTH(@Month)),
              YEAR(@Month),
              G.Name,
              U.FullName,
              K.Name,
              S.Name,
              G.FN,
              G.Address,
              Settlements.SettleMentName,
              G.Sex,
              PK.Description,
              DBA.GetNoshtuvkaNameForPeriodInMonth(B.RegNum,@StartDateMonth,@EndDateMonth),
              Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,@StartDateMonth),B.Price*C.ExchangeRate),
              Kol=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@EndDateMonth),2),
              SumOwe=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@EndDateMonth)*Cena,2),
              KolPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@EndDateMonth),2),
              SumPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@EndDateMonth)*Cena,2),
              Razlika=SumOwe-SumPaid,
              DBA.GetRealDatePayedRentForPeriod(B.RegNum,@StartDateMonth,@EndDateMonth),
              Kol,
              R.RoomName,
              Ctr.Description,
              DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@EndDateMonth)+1 from
              DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,
              DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R,DBA.Countries as Ctr,DBA.Settlements where
              B.PriceKind = PK.PriceKind and
              G.GuestNum = B.WhoPays and
              PK.NachinPlat = 1 and
              B.DateRegDt < dateadd(month,1,"date"('1.' || MONTH(@Month) || '.' || YEAR(@Month))) and
              dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || MONTH(@Month) || '.' || YEAR(@Month)) and
              G.Kurs = K.N and
              G.SpecNo = S.N and
              G.UniN = U.N and
              B.KodCurrency = C.KodCurrency and
              R.RoomNum = B.Room and
              Settlements.N = G.SettlementN and
              G.NativeCountry = Ctr.CountryKod and
              B.Staying <> 3 and
              Kol <> 0.0
          set @StartDateMonth=@EndDateMonth
        end
    end
end
--[29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods

GO

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

GO

create function
DBA.GetQtyRentForPeriodInMonth(in @RegNo integer,in @Tip tinyint,in @PeriodStart date,in @PeriodEnd date)
returns real --@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo
--Izchislqva koficienta na no6tuvki v zadaden period (plateni ili vsi4ki) ot celiq mesec (broi noshtuvki v perioda v meseca)/(broi dni v meseca)
begin
  declare @Res real;
  declare @DateArrive date;
  declare @DateLeave date;
  declare @DatePaidTo date;
  declare @MonthIsWith30Days integer;
  declare @CalcStart date;
  declare @CalcEnd date;
  if(@PeriodEnd <= @PeriodStart) then
    return(0)
  end if;
  if((MONTH(@PeriodStart) <> MONTH(@PeriodEnd)) or((YEAR(@PeriodStart) <> YEAR(@PeriodEnd)))) then
    return(0)
  end if;
  select FloatToDate(DateReg),
    FloatToDate(DateReg+NumLodgings),
    FloatToDate(DateReg+PayedLodgings) 
	into 
	  @DateArrive,
    @DateLeave,
    @DatePaidTo 
	from
    Book 
	where
    Book.RegNum = @RegNo;
  if(@DateArrive >= @DateLeave) then
    return(0)
  end if;
  if ((Month(@PeriodStart)=Month(@DateArrive)) and (Year(@PeriodStart)=Year(@DateArrive))) or ((Month(@PeriodStart)=Month(@DateLeave)) and (Year(@PeriodStart)=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(@Tip = 1) then
    set @DateLeave=@DatePaidTo
  end if;
  if(@DateArrive >= @PeriodEnd) then
    return(0)
  end if;
  if(@DateLeave <= @PeriodStart) then
    return(0)
  end if;
  set @CalcStart=@DateArrive;
  set @CalcEnd=@DateLeave-1;
  if(@CalcStart <= @PeriodStart) then
    set @CalcStart=@PeriodStart
  end if;
  if(@CalcEnd >= @PeriodEnd) then
    set @CalcEnd=@PeriodEnd
  end if;
  set @Res=(cast(DAY(@CalcEnd)-DAY(@CalcStart)+1 as real)/cast(DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days) as real));
  if(MONTH(@PeriodStart) = 2) and(@MonthIsWith30Days = 1) then
    if(cast(DAY(@CalcEnd)-DAY(@CalcStart)+1 as real) = 28) then
      set @Res=1
    end if
  end if;
  if(@Res > 1) then
    return(1)
  else
    return(@Res)
  end if
end
--[v. 29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods


GO

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;
	
	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
  else
    select V.TipDDS into @ServiceTipDDS from Book as B,PriceKinds as PK,Services as S,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*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @ServiceTipDDS=1
    end if;
    set @ArticlePrice=@ArticlePrice-@PackagePrice;
    if @ServiceTipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+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;
  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)))
    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) values( 
    @RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@ServiceTipDDS,MONTH(@DateStart),YEAR(@DateStart)) ;
  // 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

GO

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

GO

create function
DBA.GetNoshtuvkaNameForPeriodInMonth(in @RegNo integer,in @PeriodStart date,in @PeriodEnd date,in @IsLodg integer default 0)
returns varchar(60) --@IsLodg 0-uslugata e za mesec,1-uslugata e za noshtuvka
--Resultata e palen string s ime na noshtvkata vkliuchvasht mesec, staq i broi dni
begin
  declare @Res varchar(60);
  declare @ServiceName varchar(40);
  declare @RoomName varchar(6);
  declare @DaysInMonth varchar(6);
  declare @KoefDaysInMonth real;
  declare @MonthIsWith30Days integer;
	declare @DateArrive date;
  declare @DateLeave date;
	
	select FloatToDate(DateReg),
    FloatToDate(DateReg+NumLodgings)
	into 
	  @DateArrive,
    @DateLeave
	from
    Book 
	where
    Book.RegNum = @RegNo;	
	
  if ((Month(@PeriodStart)=Month(@DateArrive)) and (Year(@PeriodStart)=Year(@DateArrive))) or ((Month(@PeriodStart)=Month(@DateLeave)) and (Year(@PeriodStart)=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;
  set @KoefDaysInMonth=GetQtyRentForPeriodInMonth(@RegNo,0,@PeriodStart,@PeriodEnd);
  if @KoefDaysInMonth <> 1 then
    set @DaysInMonth=' ' || Round(@KoefDaysInMonth*DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days),0) || '/' || DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days)
  end if;
  select S.ServiceName,R.RoomName into @ServiceName,@RoomName from Services as S,PriceKinds as PK,Book as B,Rooms as R where
    B.RegNum = @RegNo and B.PriceKind = PK.PriceKind and PK.ServiceNo = S.ServiceNo and R.RoomNum = B.Room;
  if @IsLodg = 1 then
    if(select IsRest from Book where RegNum = @RegNo) = 1 then
      set @ServiceName='Почивка '
    else
      set @ServiceName='Нощувка '
    end if
  end if;
  if @IsLodg = 0 then
    set @Res=@ServiceName || ' ' || ImeMesec(MONTH(@PeriodStart)) || ' в стая ' || @RoomName || @DaysInMonth
  else
    set @Res=@ServiceName || ' в стая ' || @RoomName
  end if;
  return(@Res)
end
--[29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods


GO