IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillSvoRedoveStn'))) THEN
Drop procedure FillSvoRedoveStn
END IF
GO
create procedure FillSvoRedoveStn()
begin
  declare LodgingsSoFar integer;
  declare CurrRegNo integer;
  set CurrRegNo = -1;
  set LodgingsSoFar = -1;
  for f1 as curs1 scroll cursor for
    select RegNo RNo, ArticleNo AN, InvoiceNo INo, dateadd(day,-1,(select DateRegDt from book where RegNum=RNo)) RegDate, RealLodgingsNum RLN from Articles A, Book B
    where RegNo<>0 and (select count(*) from SvoRedoveStn where ArticleNo = AN and InvoiceNo=INo)=0 and B.RegNum = A.RegNo
    order by RegNo desc,InvoiceNo, ArticleNo do
	  if (CurrRegNo = -1) OR (CurrRegNo <> RNo) then
	    set CurrRegNo = RNo;
		set LodgingsSoFar = 0;
	  end if;
	  insert into SvoRedoveStn
	  VALUES(AN,INo,1,MONTH(dateadd(day,RLN+LodgingsSoFar,RegDate)));
	  insert into SvoRedoveStn
	  VALUES(AN,INo,2,YEAR(dateadd(day,RLN+LodgingsSoFar,RegDate)));
	  set LodgingsSoFar = LodgingsSoFar + RLN;
  end for;
end
//[24.05.2009] - Dobrin - created
go

Delete from Relations where lcase(TableName) = lcase('//v_GeneralReportServices')

Go

Delete from Relations where lcase(TableName) = lcase('v_GeneralReportServices')

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','__OPTIONS__','Обобщен отчет услуги','__OPTIONS__','Summurized Report by Services','__OPTIONS__',1,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,today(*))

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','bvrData2','Обобщен отчет услуги','Дата','Summurized Report by Services','Date',2,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select current date',72,'',0,0,1,today(*))

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','ServiceName','Обобщен отчет услуги','Име услуга','Summurized Report by Services','Service Name',2,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'ServiceName','','',0,0,'','','select ServiceName from Services','',120,'',0,0,1,today(*))

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','DayIncomes','Обобщен отчет услуги','Днев.приходи','Summurized Report by Services','Day Income',2,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'Sum(DayIncomes)','Sum(qrData.Днев.приходи)','# ##0.00',1,0,'','','','',100,'',0,0,1,today(*))

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','MonthIncomes','Обобщен отчет услуги','Мес.приходи','Summurized Report by Services','Monthly Income',2,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'Sum(MonthIncomes)','Sum(rqData.Мес.приходи)','# ##0.00',1,0,'','','','',100,'',0,0,1,today(*))

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_GeneralReportServices','PercentDayMonth','Обобщен отчет услуги','Процент(%)','Summurized Report by Services','Percent (%)',2,GetFreeNInRelations('Summurized Report by Services', Null, Null, 10),'PercentDayMonth','','# ##0.0',1,0,'','','','',100,'',0,0,1,today(*))

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='//v_GeneralReportServices'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='//v_GeneralReportServices'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for
go

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

GO

create function DBA.GetQtyRentForMonth(in @RegNo integer,in @Tip tinyint) //@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo
returns real
begin
  declare @rez real;
  declare @DateA date;
  declare @DateL date;
  declare @DateP date;
  select FloatToDate(DateReg),FloatToDate(DateReg+NumLodgings),FloatToDate(DateReg+PayedLodgings) into @DateA,@DateL,@DateP from Book where RegNum = @RegNo;
  if @DateA>=@DateL then return(0) end if;
  if MONTH(@DateA) = bvrSpravkaM and YEAR(@DateA) = bvrSpravkaY then
    set @rez=cast(DOM(bvrSpravkaM,bvrSpravkaY)-DAY(@DateA)+1 as real)/cast(DOM(bvrSpravkaM,bvrSpravkaY) as real)
  else
    if MONTH(@DateL) = bvrSpravkaM and YEAR(@DateL) = bvrSpravkaY then
      set @rez=cast(DAY(@DateL)-1 as real)/DOM(bvrSpravkaM,bvrSpravkaY)
    else
      set @rez=CheckBookBalanceForMonth(@RegNo)
    end if
  end if;
  if DAY(@DateL) < DOM(bvrSpravkaM,bvrSpravkaY) and MONTH(@DateL) = bvrSpravkaM and YEAR(@DateL) = bvrSpravkaY and MONTH(@DateL) = MONTH(@DateA) and YEAR(@DateL) = YEAR(@DateA) then
    set @rez=cast(DAY(@DateL)-DAY(@DateA) as real)/DOM(bvrSpravkaM,bvrSpravkaY)
  end if;
  if @Tip = 0 then return(@rez)
  else if(@DateA = @DateP) then
      set @rez=0
    else
      if(@DateL <> @DateP) then
        if(year(@DateP) < bvrSpravkaY) then
          set @rez=0
        else
          if(year(@DateP) = bvrSpravkaY) then
            if(month(@DateP) < bvrSpravkaM) then
              set @rez=0
            else
              if(month(@DateP) = bvrSpravkaM)  then
                set @rez=cast (DAY(@DateP)-1 as real)/cast(DOM(bvrSpravkaM,bvrSpravkaY) as real)
              end if
            end if
          end if
        end if
      end if
    end if
  end if;
  return(@rez)
end

GO

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OwePaidRentMonth')))
THEN Drop view v_OwePaidRentMonth
END IF

GO

create view
  DBA.v_OwePaidRentMonth
  as select GNo=Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select SettleMentName from DBA.Settlements where N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum),
    Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,'01.'||bvrSpravkaM||'.'||bvrSpravkaY),B.Price*C.ExchangeRate),
    Kol=Round(DBA.GetQtyRentForMonth(B.RegNum,0),2),
    SumOwe=Round(DBA.GetQtyRentForMonth(B.RegNum,0)*Cena,2),
    KolPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1),2),
    SumPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1)*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedRentForMonth(B.RegNum),
    Koef=Kol,
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Ctr.Description,BeginDateMonth=
    "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY),
    EndDateMonth=dateadd(day,-1,dateadd(month,1,BeginDateMonth)),
    DaysInMonth=DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),BeginDateMonth,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 where
    B.PriceKind = PK.PriceKind and G.GuestNum = B.WhoPays and PK.NachinPlat = 1 and
    B.DateRegDt < dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) and
    dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY) 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
    G.NativeCountry = Ctr.CountryKod and Staying<>3 and Kol<>0.0
//[07.04.2009] - Nixon - dobavena kolona DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
//[03.06.2009] - Dobrin - Da ne vadi napusnalite gosti
go

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_FreeBeds')))
THEN Drop view v_FreeBeds
END IF

GO

create view
  DBA.v_FreeBeds
  as select HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where lcase(R.RegisterName) = lcase('hotelid') and Hotel_ID = R.IntStojnost),
    Room=Rooms.RoomName,
    RoomClass=Max(CL.ShortName),
    TotalBeds=max(Rooms.NumBeds),
    NumPeople=count(Book.RegNum),
    Status=(if(max(IsFull) = 1) or(TotalBeds-NumPeople <= 0) then 'Запълнена' else 'Незапълнена' endif),
    UnUsedBeds=(if(max(IsFull) = 1) then TotalBeds-NumPeople else 0 endif),
    FreeBeds=(if((TotalBeds-NumPeople < 0) or(max(IsFull) = 1)) then 0 else TotalBeds-NumPeople endif) from
    DBA.Rooms left outer join dba.Book on Book.NumLodgings <> 0 and
    Rooms.RoomNum = Book.Room and
	Book.Staying=1 and
    Days(Book.DateRegDt,Book.NumLodgings) >= bvrData2 and
    Book.DateRegDt <= bvrData2,
    DBA.Classes as CL where
    Rooms.RoomCat = CL.Class and
    Rooms.Status <> 1 and
    Rooms.Status <> 3 and
    Rooms.Status <> 10
    group by Rooms.RoomName order by
    Room asc
go