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

GO

create function
DBA.GetRentPriceForDate(in @RegNo integer,in @DateReg date, in @SN integer default null)
returns numeric(12,2)
begin
  declare @R numeric(12,2);
  declare @PriceKindNo integer;
  declare @SeasonNo integer;
  declare @ClassNo integer;
  declare @HumanKind integer;
  declare @AdultType integer;
  declare @FirstDateMonth integer;
  set @R=null;
  --set @FirstDateMonth=dateadd(day,-datepart(day,@DateReg)+1,@DateReg);
  set @FirstDateMonth=@DateReg;
  if @DateReg is not null then
    select min(SeassonNo) into @SeasonNo from PriceSeasonPeriods where StartDate <= @FirstDateMonth and EndDate >= @FirstDateMonth
  else
    set @SeasonNo = @SN
  end if;
  if(@SeasonNo is not null) then
    select B.PriceKind,R.RoomCat,G.HumanKind,G.AdultType into @PriceKindNo,@ClassNo,@HumanKind,@AdultType from Book as B,Guests as G,Rooms as R where B.RegNum = @RegNo and B.Guest = G.GuestNum and B.Room = R.RoomNum;
    select min(PL.PriceAll*C.ExchangeRate) into @R from PriceList as PL,Currencies as C where
      PL.ClassNo = @ClassNo and PL.PriceKindNo = @PriceKindNo and PL.HumanKind = @HumanKind and PL.AdultType = @AdultType and
      PL.SeasonNo = @SeasonNo and PL.KodCurrency = C.KodCurrency
  end if; // @SeasonNo is not null
  return(@R)
end
-- [20.07.2009] - Yoan - izchislqva pravilno pri PriceSeasonPeriods nachalna data razlichna ot 1vi
-- [04.09.2009] - Dobrin 2612 - dobaven posledniq parametyr

GO


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

GO

create function
DBA.GetRealDatePayedLodgForMonth(in @RegNo integer,in @DayNo integer default null)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffDays integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  select DateRegDt into @BookRegDate from Book where RegNum = @RegNo;
  if @DayNo is null then
    set @SpravkaDate="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)
  else
    set @SpravkaDate="date"(@DayNo || '.' || bvrSpravkaM || '.' || bvrSpravkaY)
  end if;
  set @DiffDays=datediff(day,@BookRegDate,@SpravkaDate);
  set @ResultDate=null;
  for f as curs scroll cursor for
    select Inv.InvoiceNo as @InvoiceNo,
      Inv.InvoiceDateDt as @InvoiceDateDt,
      A.Qty as @Qty from
      Invoices as Inv,Articles as A where
      A.InvoiceNo = Inv.InvoiceNo and
      A.RegNo = @RegNo and
      Inv.CancelInvoiceNo = 0 order by
      Inv.InvoiceNo asc do
    set @DiffDays=@DiffDays-@Qty;
    if(@DiffDays <= 0) then
      set @ResultDate=@InvoiceDateDt
    end if end for;
  return(@ResultDate)
end

GO

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

GO

create function
DBA.GetQtyLodgForMonth(in @RegNo integer,in @NumberLodgings integer,in @SeasonBegDt Date default null, in @SeasonEndDt Date default null)
returns integer
begin
  declare @DateEnd date;
  declare @DateBegin date;
  declare @res integer;
  select DateRegDt,dateadd(day,@NumberLodgings,DateRegDt) into @DateBegin,@DateEnd from Book where RegNum = @RegNo;
  if(@DateEnd > dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY))) then
    set @DateEnd=dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY))
  end if;
  if(@DateBegin < "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) then
    set @DateBegin="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)
  end if;
  if (@SeasonBegDt is not null) AND (@SeasonEndDt is not null) then
    if (@DateBegin < @SeasonBegDt) then set @DateBegin = @SeasonBegDt end if;
    if (@DateEnd > @SeasonEndDt) then set @DateEnd = dateadd(day,1,@SeasonEndDt) end if
  end if;
  set @res=datediff(day,@DateBegin,@DateEnd);
  if(@res < 0) then
    set @res=0
  end if;
  return(@res)
end

GO

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

GO

create view
  DBA.v_OwePaidLodgMonth
  as select
    SeassonNo,
    StartDate,
	EndDate,
    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,1),
    Cena=GetRentPriceForDate(B.RegNum,null,SeassonNo),
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings,StartDate,EndDate),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings,StartDate,EndDate),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum,if MONTH(StartDate)=bvrSpravkaM then DAY(StartDate)-1 else 0 endif + Kol),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
    DaysInMonth=Kol 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,PriceSeasonPeriods PSP where
    B.PriceKind = PK.PriceKind and
    G.GuestNum = B.Guest and
    PK.NachinPlat = 0 and
    G.Kurs = K.N and
    G.SpecNo = S.N and
	(MONTH(PSP.StartDate)+12*YEAR(PSP.StartDate))<=(bvrSpravkaM + 12*bvrSpravkaY) and
	(MONTH(PSP.EndDate)+12*YEAR(PSP.EndDate))>=(bvrSpravkaM + 12*bvrSpravkaY) and
    G.UniN = U.N and
    B.KodCurrency = C.KodCurrency and
    R.RoomNum = B.Room and
    G.NativeCountry = Countries.CountryKod and
    B.DateRegDt < dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) and
     dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY) and Kol<>0
	 
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetInvoiceNumbersDepozitSmetki'))) THEN
Drop function GetInvoiceNumbersDepozitSmetki
END IF

GO

create function GetInvoiceNumbersDepozitSmetki(in @Svoistvo integer, @GNo integer)
returns varchar(100)
begin
	declare rez varchar(100);
	set rez='';
	for f as curs scroll cursor for 
	select distinct ExternalNo as @InvoiceNo from ServiceCredits SC join Articles A on A.CreditNo=SC.CreditNo, Invoices I
	where GuestNo=@GNo and Service=(select Glava from SvoServicesStn where Svoistvo=@Svoistvo) and
	I.InvoiceNo = A.InvoiceNo do
		set rez=rez||@InvoiceNo||', ';
	end for;
--maham poslednata zapetaika
	set rez=substr(rez,0,length(rez)-2);
	return rez;
end

--Dobrin created 06.11.2009 - za Adresnata karta

GO

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

GO

create procedure
DBA.PrepareAddrCard(in @Gno integer)
begin
  declare @MonthsGreek varchar(50);
  declare i integer;
  declare j integer;
  declare sqlsttm varchar(200);
  declare _egn varchar(18);
  declare _IsEgnEnch integer;
  call GetUchGodinaPayments(@Gno);
  select egn, isegnench into _egn, _IsEgnEnch from Guests where GuestNum = @GNo;
  set i=1;
  set @MonthsGreek='   I  II III  IV   V  VI VIIVIII  IX   X  XI XII';
  delete from #AddrCardPayments;
  while i <= 48 loop
    insert into #AddrCardPayments( Mes,MesReal) values( Trim(SUBSTR(@MonthsGreek,i,4)),i/4+1) ;
    set i=i+4
  end loop;
  set j=1;
  for f1 as curs1 scroll cursor for
    select if TipDDS = 2 then SUM(A.Price*(1+bvrDDSPart)) else SUM(A.Price) endif as Price,cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr,GetInvoiceNumbers(@GNo,Mnth,Yr) as INo from
      Book as B,Articles as A,Invoices as Inv,Currencies as C,SvoRedoveStn as SRS,SvoRedoveStn as SRS1 where
      B.WhoPays in (select GuestNum from Guests where egn = _egn and isegnench = _IsEgnEnch) and
	  B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and
      Inv.CancelInvoiceNo = 0 and SRS.ArticleNo = A.ArticleNo and SRS1.ArticleNo = A.ArticleNo and SRS.InvoiceNo = A.InvoiceNo and
      SRS1.InvoiceNo = A.InvoiceNo and SRS.Svoistvo = 1 and SRS1.Svoistvo = 2
      group by TipDDS,INo,Mnth,Yr order by
      Yr asc,Mnth asc do
    set i=Mnth;
	set j=0; 
	if Yr=bvrPayYear1 then
      set j=1 
	else
	  if Yr=bvrPayYear2 then
        set j=2 
	  else
		if Yr=bvrPayYear3 then 
		  set j=3 
		else
		  if Yr=bvrPayYear4 then 
		    set j=4 
		  else
			if Yr=bvrPayYear5 then 
			  set j=5 
			end if 
		  end if 
		end if 
	  end if 
	end if;
	if j<>0 then  
      set sqlsttm='update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=''' || INo || ''' where MesReal=' || i
	end if;
    execute immediate sqlsttm;
  end for
end
-- Yoan Vadi externalNo pri 2 smetki za mesec

GO

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

GO

create procedure GetUchGodinaPayments(in @GNo integer)
begin
  declare _egn varchar(18);
  declare _isegnench smallint;
  declare BeginYear integer;
  declare BrGodiniSum integer;
  declare i integer;
  declare LocalI integer;
  declare sqlstmnt varchar(30);//set bvrPayYear(1..5) = xxxx
  set i=5;//izpolzvam i za sqlstmnt - da si pazi vyv for cikyla do koi bvrPayYear sym stignal
  set LocalI=0;//izpolzvam LocalI za lokalniq while cikyl vyv for-a
  set BrGodiniSum = 0;
  select G.egn, G.isegnench, YEAR(dateadd(day,B.Numlodgings,B.DateRegDt))+1 into _egn, _isegnench, BeginYear 
    from Guests G join Book B on G.GuestNum = B.Guest where G.GuestNum=@GNo;
  --obrazuvame vsichki periodi, v koito studenta s tova egn e bil v obshtejitieto  i broim kolko obshto godini e imal plashtaniq
  for f1 as curs scroll cursor for
    select YEAR(DateRegDt) as Ybegin, 
	YEAR(dateadd(day,if Staying = 1 then PayedLodgings else Numlodgings endif,DateRegDt)) as Yend,
	Yend - Ybegin + 1 as BrGodini from Book 
	where Guest in (select GuestNum from Guests where egn = _egn and isegnench = _isegnench) 
	order by RegNum desc do
	
	set LocalI=BrGodini;
	
	if BeginYear=Yend then
	  set BrGodiniSum = BrGodiniSum-1;
	  set LocalI = LocalI - 1;
	end if;	
	
	set BrGodiniSum = BrGodiniSum + BrGodini;
	
	while (LocalI>0) AND (i>0) loop
	  set sqlstmnt='set bvrPayYear'||cast(i as varchar(1))||' = ';
	  set sqlstmnt=sqlstmnt||Yend-BrGodini+LocalI;
	  
	  execute (sqlstmnt);
	
	  set LocalI=LocalI-1;
	  set i=i-1
	end loop;
	
	set BeginYear = Ybegin;
  end for;	

  if BrGodiniSum<5 then
--Myrdam godinite po mestata im, poneje ako ima po-malko ot 5 godini prestoi nqma da sa podredeni kakto trqbva	
	while (BrGodiniSum > 0) loop
	  set sqlstmnt = 'set bvrPayYear'||cast(5-BrGodiniSum+1-i as varchar(1))||'='||'bvrPayYear'||cast(5-BrGodiniSum+1 as varchar(1));
	  execute (sqlstmnt);
	  set BrGodiniSum=BrGodiniSum-1;
	end loop;
	
--popylvam poslednite godini	
	while (i>0) loop
	  set sqlstmnt = 'set bvrPayYear'||cast(6-i as varchar(1))||'='||'bvrPayYear'||cast(6-i-1 as varchar(1))||'+1';
	  execute (sqlstmnt);
	  set i=i-1;	  
	end loop
  end if;
end

GO

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

GO

create function
DBA.GetInvoiceNumbers(in @GNo integer,in @Mnth integer,in @Yr integer)
returns varchar(50)
begin
  declare rez varchar(50);
  declare _egn varchar(18);
  declare _isEgnEnch smallint;
  set rez='';
  select egn, isEgnEnch into _egn,_isEgnEnch from Guests where GuestNum = @GNo;
  for f as curs scroll cursor for
    select distinct A.InvoiceNo as InvNo from Articles as A,SvoRedoveStn as SVS1,SvoRedoveStn as SVS2 where
      A.RegNo = any(select RegNum from Book where WhoPays in (select GuestNum from Guests where egn=_egn and isEgnEnch=_isEgnEnch)) and
      SVS1.Svoistvo = 1 and SVS2.Svoistvo = 2 and SVS1.Stoinost = @Mnth and SVS2.Stoinost = @Yr and
      A.ArticleNo = SVS1.ArticleNo and A.InvoiceNo = SVS1.InvoiceNo and
      A.ArticleNo = SVS2.ArticleNo and A.InvoiceNo = SVS2.InvoiceNo do
    set rez=rez || (select Inv1.ExternalNo from Invoices as Inv1 where Inv1.InvoiceNo = InvNo) || ', ' end for;
  set rez=substr(rez,0,length(rez)-2);
  return rez
end
--[v 11.09.2009] - Yoan - vrashta ExternalNo

GO

if (select count(*) from SvoServices where N=9)=0 then
  insert into SvoServices 
    VALUES(9,'Депозит 1с','Избира се услугата, с която се маркира 
семестриалния депозит за първи семестър на студента
и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

if (select count(*) from SvoServices where N=10)=0 then
  insert into SvoServices 
    VALUES(10,'Депозит 2с','Избира се услугата, с която се маркира 
семестриалния депозит за втори семестър
на студента и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

if (select count(*) from SvoServices where N=11)=0 then
  insert into SvoServices 
    VALUES(11,'Депозит 1-2с','Избира се услугата, с която се маркира 
семестриалния депозит за двата семестъра
на студента и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

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

GO

create view DBA.v_BillsToGuests as
  select Invoices.InvoiceNo,
    Invoices.ExternalNo,
    Invoices.TrueInvoiceNo,
    Invoices.InvoiceDateDt,
    Invoices.GrandTotal,
    Invoices.Whom,
    Admin.FullName,
    GuestNum=ServiceCredits.GuestNo from
    dba.Invoices,dba.Articles,dba.ServiceCredits,dba.Admin where
    RegNo = 0 and Invoices.InvoiceNo = Articles.InvoiceNo and Invoices.KodAdmin = Admin.AdminNo and
    Articles.CreditNo = ServiceCredits.CreditNo union
  select Invoices.InvoiceNo,
    Invoices.ExternalNo,
    Invoices.TrueInvoiceNo,
    Invoices.InvoiceDateDt,
    Invoices.GrandTotal,
    Invoices.Whom,
    Admin.FullName,
    GuestNum=Book.Guest from
    dba.Invoices,dba.Articles,dba.Book,dba.Admin where
    CreditNo = 0 and Invoices.InvoiceNo = Articles.InvoiceNo and Invoices.KodAdmin = Admin.AdminNo and
    Articles.RegNo = Book.RegNum
//[v 00.12.12]

GO

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

GO

create function DBA.NextIsTheMoved(in @RNo integer)
returns tinyint
begin
  declare @Room integer;
  declare @Guest integer;
  declare @RoomNxt integer;
  declare @GuestNxt integer;
  declare @StayingNxt integer;
  declare @Rez tinyint;
  declare @NextRNo integer;
  set @StayingNxt=0;
  set @Rez=2;
  select Room,Guest into @Room,@Guest from Book where RegNum = @RNo;
  set @NextRNo = (select MIN(RegNum) from Book where RegNum>@RNo and Guest = @Guest);
  if(select count(*) from Book where RegNum in( @RNo,@NextRNo) ) <> 2 then set @Rez=0
  end if;
  select Room,Guest,Staying into @RoomNxt,@GuestNxt,@StayingNxt from Book where RegNum = @NextRNo;
  if @Rez = 2 then
    if @StayingNxt = 1 then
      if(@Room = @RoomNxt) and(@Guest = @GuestNxt) then
        set @Rez=1
      end if
    else
      if NextIsTheMoved(@NextRNo) = 1 then
        set @Rez=1
      else
        set @Rez=0
      end if
    end if
  end if;
  return(@Rez)
end

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;
  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 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) 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
-- [24.09.2009] - Yoan - izchislqva pravilno pri nachalo 31vi

GO

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

GO

create function
DBA.FinishBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT decimal(12,2),in @VAT decimal(12,2),in @GrandTotal decimal(12,2),in @WithoutVAT decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer,in @DDSType integer)
returns integer
//Insertva dannite za fakturata vuv Invoices i Articles i insertva parite v ResMoney
//@No e syotvetno nomer na grupa, rezervaciq, gost, staia i anulirana smetka, ako smetkata e za grupa, rezervacia, gost, staia ili anulirashta
begin atomic
  declare @InvoiceNo integer;
  declare @DepositNo integer;
  --Maria +
  --declare @TrueInvoiceNo integer;
  declare @TrueInvoiceNo numeric(10);
  --Maria -
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  declare @CreditNo integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @NullDepositCount integer;
  declare @InvoiceMask integer; //ivaylo
  declare @DepContractNo integer; //// ivaylo
  if @BillType <> 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1005
  end if;
  if @BillType = 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1004
  end if;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #Articles where ArticleQty = 0;
  //Vmukva dannite za fakturata
  set @InvoiceNo=GetCounter('InvoiceCounter');
  if @IsInvoice = 1 then
    --Maria +
    --set @TrueInvoiceNo=GetCounter('TrueInvoiceNo')
    set @TrueInvoiceNo=GetFakturaCounter('TrueInvoiceNo')
  else --Maria -
    set @TrueInvoiceNo=0
  end if;
  if @BillType = -1 then
    set @CancelInvoiceNo=@No
  else
    set @CancelInvoiceNo=0
  end if;
  set @ResMoneyStatus=1;
  select IntStojnost into @InvoiceMask from Registers where RegisterName = 'MaskaFaktura'; //ivaylo
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  //ivaylo
  insert into Invoices( InvoiceNo,Status,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,CancelInvoiceNo,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,MaskaFaktura,DDSType) values( 
    @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',1,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),1.0,@VidSmetka,@ContractNo,@InvoiceMask,@DDSType) ;
  for f1 as curs1 scroll cursor for
    select ArticleNumber as @ArticleNumber,ArticleName as @ArticleName,ArticleQty as @ArticleQty,
      ArticlePrice as @ArticlePrice,RegNo as @RegNo,CreditNo as @CreditNum,ArticleSum as @ArticleSum,
      TipDDS as @TipDDS,Discount as @Discount,ServiceNo as @Service,ArticleSumInvCurrency as @SumInvCurrency,
      RegDate as @RDate,RealLodgNum as @RLN,RentMonth as @RentMonth,RentYear as @RentYear from #Articles do //Dobrin 2087 - dobaveno @RLN
    //Ako e swobodna smetka vmukva vuv uslugite na gosta uslugite ot svobodnata smetka
    if @BillType = 5 then
      select ServiceNo into @ServiceNo from Services where ServiceNo = @Service;
      if(@ServiceNo <> 1005) and(@ServiceNo <> 1004) and(@ServiceNo <> -8) then
        set @CreditNo=GetCounter('CreditCounter');
        insert into ServiceCredits( CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,PayedServices,WhoPays) values( 
          @CreditNo,@InvoiceDate,@No,@ServiceNo,@ArticleSum,1,@KodAdmin,@ArticleQty,0,@No) 
      else
        set @ResMoneyStatus=2;
        set @CreditNo=0
      end if
    else
      set @CreditNo=@CreditNum
    end if;
    //Insertva zapis vuv Articles
    insert into Articles( InvoiceNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo,
      ArticleName,TipDDS,Discount,ServiceNo,RegDate,DDSType,RealLodgingsNum) values( 
      @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency,
      @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate,@DDSType,@RLN) ;
    //Popylva SvoRedoveStn
    if @RentMonth <> -1 then
      insert into SvoRedoveStn values( @ArticleNumber,@InvoiceNo,1,@RentMonth) ;
      insert into SvoRedoveStn values( @ArticleNumber,@InvoiceNo,2,@RentYear) 
    end if;
    //Promenia noshtuvkite
    if @RegNo > 0 then
      if (@RLN = 0) or ((select NachinPlat from PriceKinds PK join Book B on PK.PriceKind = B.PriceKind where RegNum = @RegNo)=0) then
        update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@ArticleQty where
          RegNum = @RegNo
      else
        update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@RLN where
          RegNum = @RegNo
      end if
    end if;
    //Promenia uslugite
    if @CreditNo > 0 then
      update ServiceCredits set PayedServices = PayedServices+sign(@BillType)*@ArticleQty where
        CreditNo = @CreditNo
    end if end for;
  //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva
  //Promenia CancelInvoiceNo na anuliranata smetka na -1
  if @BillType = -1 then
    delete from DBA.ResMoney where InvoiceNo = @No and DateMoney = 0;
    update Invoices set CancelInvoiceNo = -1 where InvoiceNo = @No;
    select max(Status) into @ResMoneyStatus from ResMoney where InvoiceNo = @No;
    //////////////////////////////////////////////// ivaylo
    set @DepContractNo=0;
    select MAX(ContractNo) into @DepContractNo from ResMoney where InvoiceNo = @No;
    if(@DepContractNo <> 0) then
      update DepositForContracts set GlobalInvoice = 0 where
        GlobalInvoice = @No and ContractNo = @DepContractNo
    end if
  end if; //////////////////////////////////////////////// ivaylo
  //Nulira predishnite depoziti syotvetno na gost, grupa, staia i reservacia
  //Nulira depositi, ako e smetka za gost
  if @BillType = 1 then
    call CheckDeposits(@No,@KodAdmin,@InvoiceNo,@NullDepositCount)
  end if;
  //Nulira depositi, ako e smetka za grupa
  if @BillType = 2 then
    for f2 as curs2 scroll cursor for
      select GuestNum as @GuestNum from Guests where "Group" = @No do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira Depositi ako e smetka za staia
  if @BillType = 3 then
    for f3 as curs3 scroll cursor for
      select Guest as @GuestNum from Book where Room = @No and Staying = 1 do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira depositi, ako e smetka za grupa
  if @BillType = 4 then
    for f4 as curs4 scroll cursor for
      select GuestNum as @GuestNum from Guests where Reservation = @No do
      call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira depositi za gosti pri obshta smetka
  if @BillType = 6 then
    for fcb as curscb scroll cursor for
      select GuestNo as @GNum from #GuestsForBill do
      call CheckDeposits(@GNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for
  end if;
  //Nulira depositi, ako e smetka po dogovor - fixed
  if(@BillType = 7) and(@ContractNo <> 0) then
    call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount,null)
  end if;
  //Vmukva plashtaniata ot wremennata tablica za plashtania
  for f6 as curs6 scroll cursor for
    select PaymentType as @PaymentType,Sum as @Sum,KodCurrency as @KodCurrency,AccountNumber as @AccountNumber,
      GuestNo as @mGuestNo,GroupNo as @mGroupNo,RoomNo as @mRoomNo,ReserveNo as @mReserveNo,PTVid as @PTVid from
      //,ContractNo as @Contract
      #PaymentTypes do
    set @DepositNo=GetCounter('DepositCounter');
    ///////////////////////////////////// ivaylo
    if((@ContractNo <> 0) and(@BillType = 5) and(@mGuestNo = 0)) then
      insert into DepositForContracts( GlobalInvoice,SubInvoice,ContractNo) values( 
        0,@InvoiceNo,@ContractNo) 
    end if;
    ////////////////////////////////////  ivaylo
    insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,
      ContractNo,InvoiceNo,RoomNo,AccountNumber,PTVid) values( 
      @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) end for;
  return(@InvoiceNo)
end
//[v 00.11.18]
//[v 01.07.09] - Niki - za vidovete smetki
//[v 18.09.02] - Dobaven e parametyr za N na Dogovora vyv vryska s Smetki po dogovori
//[v 18.02.2003] Jorko
//[  02.11.2007] Kozludjov - fix depoziti po dogovor
//[v 18.01.2008] - Niki - depositi za dogovor
//[v 14.03.2008] Maria - tip na TrueInvoiceNo e promenen i se vika funkcia GetFakturaCounter, koiato dava nomera na fakturata
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
//[v 2009.05.23 - MON] - Dobrin - da popylva SvoRedoveStn

GO

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

GO

create procedure
DBA.PrepareAddrCard(in @Gno integer)
begin
  declare @MonthsGreek varchar(50);
  declare i integer;
  declare j integer;
  declare sqlsttm varchar(200);
  declare _egn varchar(18);
  declare _IsEgnEnch integer;
  call GetUchGodinaPayments(@Gno);
  select egn, isegnench into _egn, _IsEgnEnch from Guests where GuestNum = @GNo;
  set i=1;
  set @MonthsGreek='   I  II III  IV   V  VI VIIVIII  IX   X  XI XII';
  delete from #AddrCardPayments;
  while i <= 48 loop
    insert into #AddrCardPayments( Mes,MesReal) values( Trim(SUBSTR(@MonthsGreek,i,4)),i/4+1) ;
    set i=i+4
  end loop;
  set j=1;
  for f1 as curs1 scroll cursor for
    select if TipDDS = 2 then SUM(A.Price*(1+bvrDDSPart)) else SUM(A.Price) endif as Price,cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr,GetInvoiceNumbers(@GNo,Mnth,Yr) as INo from
      Book as B,Articles as A,Invoices as Inv,Currencies as C,SvoRedoveStn as SRS,SvoRedoveStn as SRS1 where
      B.WhoPays in (select GuestNum from Guests where egn = _egn and isegnench = _IsEgnEnch) and
	  B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and
      Inv.CancelInvoiceNo = 0 and SRS.ArticleNo = A.ArticleNo and SRS1.ArticleNo = A.ArticleNo and SRS.InvoiceNo = A.InvoiceNo and
      SRS1.InvoiceNo = A.InvoiceNo and SRS.Svoistvo = 1 and SRS1.Svoistvo = 2
      group by TipDDS,INo,Mnth,Yr order by
      Yr asc,Mnth asc do
    set i=Mnth;
	set j=0; 
	if Yr=bvrPayYear1 then
      set j=1 
	else
	  if Yr=bvrPayYear2 then
        set j=2 
	  else
		if Yr=bvrPayYear3 then 
		  set j=3 
		else
		  if Yr=bvrPayYear4 then 
		    set j=4 
		  else
			if Yr=bvrPayYear5 then 
			  set j=5 
			end if 
		  end if 
		end if 
	  end if 
	end if;
	if j<>0 then  
      set sqlsttm='update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=''' || INo || ''' where MesReal=' || i
	end if;
    execute immediate sqlsttm;
  end for
end
-- Yoan Vadi externalNo pri 2 smetki za mesec

GO


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

GO

create procedure
DBA.GetDataForDateSeasonPeriods(in @Dt date,out @SeasonN integer,out @Days integer,out @NextDate date,in @ND integer)
begin
  //Tazi funkciq vryshta danni ot PriceSeasonPeriods - SeasonN, dnite, ostavashti do nachaloto na sledvashtiq period(@Days)
  // i datata, nachalo na sledvashtiq period(@NextDate)
  declare @N integer;
  select SeassonNo,days(@Dt,EndDate)+1,N into @SeasonN,@Days,@N from PriceSeasonPeriods where StartDate <= @Dt and EndDate >= @Dt;
  --set @NextDate=(select StartDate from PriceSeasonPeriods where N = @N+1);
  set @NextDate=(select dateadd(day,1,EndDate) from PriceSeasonPeriods where N = @N);
  if (select count(*) from PriceSeasonPeriods where StartDate=@NextDate) = 0 then set @NextDate=null end if;
  --Nqma sledvasht period i trqbva vsichki ostanali noshtuvki da sa v tozi cenovi sezon. Malko si igraem s datata i s @Days
  if @NextDate is null then
    set @NextDate=dateadd(day,@ND+1,@Dt); //Datata da e tazi, koqto trqbva da se vizualizira + 1 den
    set @Days=@ND+1 //izkarvame @NumDays+1, za da moje kato obrazuvame datata posle vyv FillArticleLodgings da si pasne vsichko
  end if
end

GO

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

GO

create procedure
DBA.FillArticleLodgings(in @RegNo integer,in @PackagePrice numeric(12,2),in @FillAll integer,in @place varchar(50))
begin
  declare @Price numeric(12,2);
  declare @ArticlePrice numeric(12,2);
  declare @KodCurrency integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @NumDays integer;
  declare @DateReg date;
  declare @TipDDS integer;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @IsRest integer;
  declare @ArticleName varchar(100);
  declare @ServiceName varchar(40);
  declare @DateStart date;
  declare @MDays integer;
  declare @SeasonN integer;
  declare @ZInd integer;
  set @ZInd = 1;
  delete from #ArticleLodgings;
  select B.Price,B.KodCurrency,B.Room,PK.NachinPlat,isNull(PK.ServiceNo,-1),B.NumLodgings-B.PayedLodgings,dateadd(day,B.PayedLodgings,B.DateRegDt),B.IsRest into @Price,
    @KodCurrency,@RoomNo,@NachinPlat,@ServiceNoLodging,@NumDays,@DateReg,
    @IsRest from Book as B,PriceKinds as PK where
    B.RegNum = @RegNo and B.PriceKind = PK.PriceKind;
  if(@NumDays > 0) then
    if @ServiceNoLodging = 0 then
      set @ServiceNoLodging=-1
  end if;
    select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
    select ServiceName into @ServiceName from Services where ServiceNo = @ServiceNoLodging;
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
    if @TipDDS = 2 then
      set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=1
    end if;
    set @ArticlePrice=@ArticlePrice-@PackagePrice;
    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
      set @TipDDS=2
    end if;
    set @RoomName=(select RoomName from Rooms where RoomNum = @RoomNo);
    if @NachinPlat = 0 then
      if @IsRest = 1 then
        set @ArticleName='Почивка ' || @place || @RoomName+' за '+dateformat(@DateReg,'dd.mm')
      end if; //else  Dobrin 2612 - po-dolu shte setna @ArticleName, ako ne e pochivka
      //set @ArticleName=@ServiceName || ' ' || @place || @RoomName+' от '+dateformat(@DateReg,'dd.mm')+' до '+dateformat(dateadd(day,@NumDays,@DateReg),'Dd.Mm')
      --Dobrin 2612 +	  Za da se syobrazqva sys zapisite ot PriceSeasonPeriods - shte e syshtoto kato po-dolu, no malko po-prostichko
      if @IsRest <> 1 then
        set @DateStart=@DateReg;
        while(@NumDays > 0) loop
          set @ArticleName=@ServiceName || ' ' || @place || @RoomName || ' от ' || dateformat(@DateStart,'dd.mm.yyyy') || ' до ';
          call GetDataForDateSeasonPeriods(@DateStart,@SeasonN,@MDays,@DateStart,@NumDays);
          if @MDays < @NumDays then
            set @NumDays=@NumDays-@MDays;
            set @ArticleName=@ArticleName || dateformat(dateadd(day,-1,@DateStart),'dd.mm.yyyy')
          else
            set @ArticleName=@ArticleName || dateformat(dateadd(day,-1,dateadd(day,@NumDays-@MDays,@DateStart)),'dd.mm.yyyy');
            set @MDays=@NumDays;
            set @NumDays=0
          end if;
 set @ArticlePrice=GetRentPriceForDate(@RegNo,null,@SeasonN);
          insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS) values( 
            @RegNo,@ArticleName,@MDays,@ArticlePrice,@MDays,@MDays,0,@ZInd,@TipDDS);
		  set @ZInd = @ZInd + 1;
        end loop
      else
        insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS) values( 
          @RegNo,@ArticleName,0,@ArticlePrice,@NumDays,@NumDays,0,0,@TipDDS) 
      end if
    else --Dobrin 2612 -
      // @NachinPlat =1
      // vurti cikul mesec po mesec i populva tablicata za vseki mesec
      set @DateStart=@DateReg;
      while(@NumDays > 0) loop
        select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,@PackagePrice,@ServiceName,@FillAll,@TipDDS,@place) into @MDays;
        set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
        set @NumDays=@MDays
      end loop
    end if; // @NachinPlat=1
    update #ArticleLodgings set ServiceNoLodging = @ServiceNoLodging where ServiceNoLodging is null
  end if // @NumDays > 0
end
--[04.09.2009] Dobrin 2612 - cenovite sezoni da okazvat vliqnie i kogato cenorazpisa ne e za mesec
--[30.09.2009] Dobrin 2672 - da sloji ZIndex kogato se razdelq na cenovi sezoni za noshtuvka, za da moje da se podrejdat posle artikulite kato svqt
		  
GO

IF not (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('PriceKindTypes')))

THEN

create table PriceKindTypes(
  "N" integer not null default autoincrement,
  "Name" varchar(200),
  "LastChanged" date default today(*),
  primary key("N")
  )
end if


GO

if (select count(*) from PriceKindTypes where N=0)=0 then
insert into PriceKindTypes VALUES(0,'Не е зададен',default)
end if

GO

IF (not exists (select "role" from sys.sysforeignkey where "role"='FK_PriceKind_Types')) then

ALTER TABLE PriceKinds
ADD FOREIGN KEY "FK_PriceKind_Types" ("PriceKindType")
REFERENCES "PriceKindTypes" ("N")

END IF

GO

update PriceKinds set PriceKindType = 0 where PriceKindType is null

GO

if (select count(*) from SvoServices where N=9)=0 then
  insert into SvoServices 
    VALUES(9,'Депозит 1с','Избира се услугата, с която се маркира 
семестриалния депозит за първи семестър на студента
и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

if (select count(*) from SvoServices where N=10)=0 then
  insert into SvoServices 
    VALUES(10,'Депозит 2с','Избира се услугата, с която се маркира 
семестриалния депозит за втори семестър
на студента и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

if (select count(*) from SvoServices where N=11)=0 then
  insert into SvoServices 
    VALUES(11,'Депозит 1-2с','Избира се услугата, с която се маркира 
семестриалния депозит за двата семестъра
на студента и се попълва автоматично в личния картон','T','',10,'')
end if;

GO

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

GO

create function
DBA.GetInvoiceNumbers(in @GNo integer,in @Mnth integer,in @Yr integer)
returns varchar(50)
begin
  declare rez varchar(50);
  declare _egn varchar(18);
  declare _isEgnEnch smallint;
  set rez='';
  select egn, isEgnEnch into _egn,_isEgnEnch from Guests where GuestNum = @GNo;
  for f as curs scroll cursor for
    select distinct A.InvoiceNo as InvNo from Articles as A,SvoRedoveStn as SVS1,SvoRedoveStn as SVS2 where
      A.RegNo = any(select RegNum from Book where WhoPays in (select GuestNum from Guests where egn=_egn and isEgnEnch=_isEgnEnch)) and
      SVS1.Svoistvo = 1 and SVS2.Svoistvo = 2 and SVS1.Stoinost = @Mnth and SVS2.Stoinost = @Yr and
      A.ArticleNo = SVS1.ArticleNo and A.InvoiceNo = SVS1.InvoiceNo and
      A.ArticleNo = SVS2.ArticleNo and A.InvoiceNo = SVS2.InvoiceNo do
    set rez=rez || (select Inv1.ExternalNo from Invoices as Inv1 where Inv1.InvoiceNo = InvNo) || ', ' end for;
  set rez=substr(rez,0,length(rez)-2);
  return rez
end
--[v 11.09.2009] - Yoan - vrashta ExternalNo

GO

IF ( select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st 
where LCase(st.table_name)= LCase('Hotels') and LCase(sc.column_name)= LCase('MonthIsWith30Days') ) = 0 then
  alter table Hotels add MonthIsWith30Days smallint default 0
END IF

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.systable where LCase(table_name) = LCase('v_BillsToGuests')))
THEN Drop view v_BillsToGuests
END IF

GO

create view DBA.v_BillsToGuests as
  select Invoices.InvoiceNo,
    Invoices.ExternalNo,
    Invoices.TrueInvoiceNo,
    Invoices.InvoiceDateDt,
    Invoices.GrandTotal,
    Invoices.Whom,
    Admin.FullName,
    GuestNum=ServiceCredits.GuestNo from
    dba.Invoices,dba.Articles,dba.ServiceCredits,dba.Admin where
    RegNo = 0 and Invoices.InvoiceNo = Articles.InvoiceNo and Invoices.KodAdmin = Admin.AdminNo and
    Articles.CreditNo = ServiceCredits.CreditNo union
  select Invoices.InvoiceNo,
    Invoices.ExternalNo,
    Invoices.TrueInvoiceNo,
    Invoices.InvoiceDateDt,
    Invoices.GrandTotal,
    Invoices.Whom,
    Admin.FullName,
    GuestNum=Book.Guest from
    dba.Invoices,dba.Articles,dba.Book,dba.Admin where
    CreditNo = 0 and Invoices.InvoiceNo = Articles.InvoiceNo and Invoices.KodAdmin = Admin.AdminNo and
    Articles.RegNo = Book.RegNum
//[v 00.12.12]

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

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.sysprocedure where LCase(proc_name) = LCase('DOM')))
THEN Drop procedure DOM
END IF

GO

create function
DBA.DOM(in @mnth integer,in @yr integer,in @Return30 smallint default 0)
returns integer
begin
  if @Return30=0 then
    if @mnth in( 1,3,5,7,8,10,12) then return(31)
    else
      if @mnth in( 4,6,9,11) then return(30)
      else
        if(MOD(@yr,4) = 0 or MOD(@yr,100) <> 0) and(MOD(@yr,400) = 0) then return(29)
        else
          return(28)
        end if
      end if
    end if
  else
    return(30)
  end if;
end

GO
//[v 2008.12.09 - MON] - Dobrin - created
//[b 2009.05.30 - MON] - Dobrin - dobaven treti parametyr

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetMonthKol'))) THEN
Drop function GetMonthKol
END IF

GO

create function GetMonthKol(in @RegNo integer,in @SeasonNo smallint default 0)
returns real
begin
  declare DateA date;
  declare DateL date;
  declare rez real;
  declare MonthsDiff integer;
  set rez = 0.0;
  set DateA = (select FloatToDate(DateReg+PayedLodgings) from Book where RegNum = @RegNo);
  set DateL = (select FloatToDate(DateReg+NumLodgings) from Book where RegNum = @RegNo);
  if @SeasonNo<>0 then
    for f1 as curs scroll cursor for
      select StartDate, EndDate from PriceSeasonPeriods where N between  
      (select N from PriceSeasonPeriods where StartDate<=DateA and EndDate>=DateA)
      AND (select N from PriceSeasonPeriods where StartDate<=DateL and EndDate>=DateL)
      AND SeassonNo=@SeasonNo do
	    set DateA = (select FloatToDate(DateReg+PayedLodgings) from Book where RegNum = @RegNo);
		if StartDate>DateA then set DateA=StartDate end if;
        set DateL = (select FloatToDate(DateReg+NumLodgings) from Book where RegNum = @RegNo);
		if EndDate<DateL then set DateL=EndDate end if;
        set Monthsdiff = YEAR(DateL)*12 + MONTH(DateL) - YEAR(DateA)*12 - MONTH(DateA)-1;
        if (MONTH(DateA) = MONTH(DateL)) AND (YEAR(DateA)=YEAR(DateL)) then 
          set rez = cast(DAY(DateL)-DAY(DateA) as real)/cast (DOM(MONTH(DateL),YEAR(DateL),1) as real)
        else
          set rez = cast((DOM(MONTH(DateA),YEAR(DateA),1) - DAY(DateA)+1) as real)/cast(DOM(MONTH(DateA),YEAR(DateA),1) as real) + cast(DAY(DateL)-1 as real)/cast(DOM(MONTH(DateL),YEAR(DateL),1) as real);
        end if;
        if Monthsdiff>0 then set rez = rez+Monthsdiff end if;
    end for
  else
    set Monthsdiff = YEAR(DateL)*12 + MONTH(DateL) - YEAR(DateA)*12 - MONTH(DateA)-1;
    if (MONTH(DateA) = MONTH(DateL)) AND (YEAR(DateA)=YEAR(DateL)) then 
      set rez = cast(DAY(DateL)-DAY(DateA) as real)/cast (DOM(MONTH(DateL),YEAR(DateL),1) as real)
    else
      set rez = cast((DOM(MONTH(DateA),YEAR(DateA),1) - DAY(DateA)+1) as real)/cast(DOM(MONTH(DateA),YEAR(DateA),1) as real) + cast(DAY(DateL)-1 as real)/cast(DOM(MONTH(DateL),YEAR(DateL),1) as real);
    end if;
    if Monthsdiff>0 then set rez = rez+Monthsdiff end if;
  end if;
  if rez<0 then set rez=0.0 end if;
  return rez;
end

GO

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

GO

create view
  DBA.v_WaitingBills as
  select G.GuestNum,
    Season = 0,
    GuestName=G.Name,
    Qty=(SC.NumberServices-SC.PayedServices),
	Prc=0.0,
    Price=string(SC.SumCredit,' ',CrS.Symbol),
    PriceLv=SC.SumCredit*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo = ASL.LastRoom,
    RoomNo=ASL.LastRoomName,
    --Maria
    S.ServiceName,
    G.Note,
    DateService=SC.DateCreditDt from
    dba.Guests as G,dba.ServiceCredits as SC,dba.Currencies as CrS,
    dba.v_ArriveStayLeaveGuestBook as ASL,dba.Services as S where
    SC.WhoPays = G.GuestNum and
    SC.WhoPays = ASL.GuestNum and
    SC.KodCurrency = CrS.KodCurrency and
    SC.NumberServices <> SC.PayedServices and
    SC.Service = S.ServiceNo and
    G.ServiceSum <> 0 and
    G.Status = 2 and
    Balance <> 0 and(
    (SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) union all
  /* Yanko +*/
  select GuestNum=B.Guest,
    Season=Ps.SeasonNo,
    GuestName=G.Name,
    Qty=Round(GetMonthKol(B.RegNum,Season),3),
	Prc=GetPriceForBook(B.RegNum,B.PriceKind,Season),
    Price=string(Prc,' ',Crs.Symbol),
    PriceLv=Prc*Crs.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo=B.Room,
    RoomNo=R.RoomName,ServiceName='НАЕМ ('||Lower(Ps.Description)||' сезон)',
    --Maria
    G.Note,
    DateService=B.DateRegDt from PriceSeasons as Ps,
    dba.Book as B,dba.Guests as G,dba.Currencies as CrS,DBA.Rooms as R where /*Maria*/ /*Maria*/
    --Maria
    B.Room = R.RoomNum and
    --Maria
    B.WhoPays = G.GuestNum and
    B.KodCurrency = CrS.KodCurrency and
    B.PayedLodgings <> B.NumLodgings and
    B.Price <> 0 and
    G.Status = 2 and
    B.WhoPays <> 0 and
	Qty<>0.0 union all
  /* Yanko -*/
  select GuestNum=RM.GuestNo,
    Season = 0,
    GuestName=G.Name,
    Qty=(-1),
	Prc=0.0,
    Price=string(RM.Sum,' ',C.Symbol),
    PriceLv=RM.Sum*C.ExchangeRate,
    Total=Qty*PriceLv,RoomNo='0',ServiceName='Aaiiceo',
    G.Note,
    DateService=RM.DateMoneyDt from
    dba.ResMoney as RM,dba.Guests as G,dba.Currencies as C where
    RM.Status = 2 and
    RM.InvoiceNo = any(select InvoiceNo from dba.Invoices where CancelInvoiceNo = 0) and
    RM.GuestNo <> 0 and
    RM.DepositNo > 0 and
    G.GuestNum = RM.GuestNo and
    G.Balance <> 0 and
    G.Status = 2 and
    C.KodCurrency = RM.Currency

GO

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

GO

create view
  DBA.v_ElaborateReportServices as
  select Articles.InvoiceNo,
    Service=left(Articles.ArticleName,if charindex('в стая',Articles.ArticleName)=0 then if charindex('за стая',Articles.ArticleName)=0 then LENGTH(Articles.ArticleName) else charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif),
    Suma=round(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif,4),
    Invoices.InvoiceDateDt,
    PayTypes.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	OtchetDate=Otcheti.OtchetDateDt,
	OtchetNo=Otcheti.OtchetNo,
    --Maria +, 1982
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    --Maria -
    DBA.Articles,
	DBA.Otcheti,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.Book,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
	Invoices.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    abs(Articles.RegNo) = Book.RegNum and
    Articles.RegNo <> 0 and
    Book.Guest = Guests.GuestNum and
    Guests.Contract *= Contracts.ContractNo and
    VR.DDSType = Articles.DDSType union all
  select Articles.InvoiceNo,
    Service=left(Articles.ArticleName,if charindex('в стая',Articles.ArticleName)=0 then if charindex('за стая',Articles.ArticleName)=0 then LENGTH(Articles.ArticleName) else charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif),
	Suma=round(if Articles.TipDDS = 3 then DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty) else DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty)*(1+VR.Rate) endif,4),
    Invoices.InvoiceDateDt,
    PayTypes.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	OtchetDate=Otcheti.OtchetDateDt,
	OtchetNo=Otcheti.OtchetNo,
    --Maria +, 1982
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    --Maria -
    DBA.Articles,
	DBA.Otcheti,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.ServiceCredits,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
	Invoices.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    Articles.CreditNo = ServiceCredits.CreditNo and
    Articles.CreditNo <> 0 and
    ServiceCredits.GuestNo = Guests.GuestNum and
    Guests.Contract *= Contracts.ContractNo and
    VR.DDSType = Articles.DDSType union all
  select InvoiceNo=max(Articles.InvoiceNo),
    Service=max(left(Articles.ArticleName,if charindex('в стая',Articles.ArticleName)=0 then if charindex('за стая',Articles.ArticleName)=0 then LENGTH(Articles.ArticleName) else charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif)),
    Suma=round(max(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif),4),
    max(Invoices.InvoiceDateDt),
    max(PayTypes.PayTypeName),
    max(Admin.FullName),
    ContractName=max(Contracts.FullName),
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	OtchetDate=Otcheti.OtchetDateDt,
	OtchetNo=Otcheti.OtchetNo,
    --Maria +, 1982
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    --Maria -
    DBA.Articles,
	DBA.Otcheti,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.ResMoney,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
	Invoices.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    Articles.RegNo = 0 and
    Articles.CreditNo = 0 and
    ResMoney.InvoiceNo = Invoices.InvoiceNo and
    Guests.GuestNum = ResMoney.GuestNo and
    Contracts.ContractNo = Guests.Contract and
    Articles.ServiceNo in( 1004,1005) and
    VR.DDSType = Articles.DDSType
    group by Articles.InvoiceNo,
    Articles.ArticleNo,
    Articles.Qty,
    Invoices.CancelInvoiceNo,Invoices.Fiscal,OtchetDate,OtchetNo union all
  select VA.InvoiceNo,
    Service=S.ServiceName,
    Suma=Round((VA.GrandTotal*Cr.ExchangeRate)*(if(VI.VAT = 0 and VI.WithoutVAT <> 0) then 1
    --Maria +, 1982
    --Maria -
    -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
    else(1+VR.Rate)
    endif),4),VI.InvoiceDateDt,
    PT.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights)
    endif),
	OtchetDate='01.01.01',
	OtchetNo=-1,
    Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.VaucherArticles as VA,
    DBA.VaucherInvoices as VI,
    DBA.PayTypes as PT,
    DBA.VATRates as VR,
    DBA.Services as S,
    DBA.Admin,
    DBA.Contracts,
    DBA.Currencies as Cr where
    VA.InvoiceNo = VI.VaucherInvoiceNo and
    VI.PayHow = PT.PayTypeN and
    VI.KodAdmin = Admin.AdminNo and
    Contracts.ContractNo = VI.ContractNo and
    VI.InvoiceDateDt >= bvrData1 and
    VI.InvoiceDateDt <= bvrData2 and
    VR.ID = 4 and
    VA.ServiceNo = S.ServiceNo and
    Cr.KodCurrency = VI.KodCurrency and(
    (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)
	
GO

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

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_ElaborateReportServices','__OPTIONS__','Подробен отчет услуги','__OPTIONS__','Подробен отчет услуги','__OPTIONS__',1,GetFreeNInRelations('Подробен отчет услуги', 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_ElaborateReportServices','bvrData1','Подробен отчет услуги','От дата','Подробен отчет услуги','От дата',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',64,'',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_ElaborateReportServices','bvrData2','Подробен отчет услуги','До дата','Подробен отчет услуги','До дата',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',64,'',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_ElaborateReportServices','InvoiceDateDt','Подробен отчет услуги','Дата','Подробен отчет услуги','Дата',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'InvoiceDateDt','','',0,0,'','','','',64,'',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_ElaborateReportServices','InvoiceNo','Подробен отчет услуги','Сметка','Подробен отчет услуги','Сметка',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'InvoiceNo','','',0,0,'','','','',64,'',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_ElaborateReportServices','Fiscal','Подробен отчет услуги','Фискализирана','Подробен отчет услуги','Фискализирана',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'Fiscal','','',0,0,'','',' select ''Не'' union all select ''Да'' ','',64,'',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_ElaborateReportServices','Service','Подробен отчет услуги','Услуга','Подробен отчет услуги','Услуга',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'Service','','',0,101,'','','select distinct Service from v_ElaborateReportServices','',200,'',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_ElaborateReportServices','Qty','Подробен отчет услуги','Бр.услуги','Подробен отчет услуги','Qty',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'Sum(Qty)','SUM(qrData.Бр. услуги)','',1,0,'','','','',60,'',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_ElaborateReportServices','Suma','Подробен отчет услуги','Сума','Подробен отчет услуги','Сума',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'Sum(Suma)','SUM(qrData.Сума)','# ### ##0.00',1,0,'','','','',64,'',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_ElaborateReportServices','PayTypeName','Подробен отчет услуги','Начин на плащане','Подробен отчет услуги','PayTypeName',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'PayTypeName','','',0,0,'','','select PayTypeName from PayTypes','',80,'',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_ElaborateReportServices','FullName','Подробен отчет услуги','Администратор','Подробен отчет услуги','Администратор',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'FullName','','',1,0,'','','','',200,'',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_ElaborateReportServices','OtchetDate','Подробен отчет услуги','Дата отчет','Подробен отчет услуги','Дата отчет',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'OtchetDate','','',1,101,'','','select OtchetDateDt from Otcheti','',90,'',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_ElaborateReportServices','OtchetNo','Подробен отчет услуги','Номер отчет','Подробен отчет услуги','Номер отчет',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'OtchetNo','','',1,101,'','','select OtchetNo from Otcheti','',90,'',0,0,1,today(*))

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_ElaborateReportServices'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_ElaborateReportServices'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetInvoiceNumbersDepozitSmetki'))) THEN
Drop function GetInvoiceNumbersDepozitSmetki
END IF

GO

create function GetInvoiceNumbersDepozitSmetki(in @Svoistvo integer, @GNo integer)
returns varchar(100)
begin
	declare rez varchar(100);
	set rez='';
	for f as curs scroll cursor for 
	select distinct ExternalNo as @InvoiceNo from ServiceCredits SC join Articles A on A.CreditNo=SC.CreditNo, Invoices I
	where GuestNo=@GNo and Service=(select Glava from SvoServicesStn where Svoistvo=@Svoistvo) and
	I.InvoiceNo = A.InvoiceNo do
		set rez=rez||@InvoiceNo||', ';
	end for;
--maham poslednata zapetaika
	set rez=substr(rez,0,length(rez)-2);
	return rez;
end

--Dobrin created 06.11.2009 - za Adresnata karta

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetPriceForBook'))) THEN
Drop function GetPriceForBook
END IF

GO

create function GetPriceForBook(in @RegNo integer,in @NewPK integer, in @Season integer default -1)
returns double
begin
  declare rez double;
  declare @Sezon integer;
  if @Season = -1 then
    set @Sezon = (select IntStojnost from Registers where RegisterName='ActivePriceSeason');
  else
    set @Sezon = @Season;
  end if;
  set rez = (select PriceAll from PriceList PL ,Guests G, Book B, Rooms R
  where B.RegNum=@RegNo and G.GuestNum=B.Guest and B.Room=R.RoomNum and G.HumanKind=PL.HumanKind
  and G.AdultType=PL.AdultType and PL.PriceKindNo=@NewPK and R.Roomcat=PL.ClassNo and
  PL.SeasonNo=@Sezon);
  if rez=null then set rez=-1 end if;
  return rez;
end

GO

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

GO

create function DBA.NextIsTheMoved(in @RNo integer)
returns tinyint
begin
  declare @Room integer;
  declare @Guest integer;
  declare @RoomNxt integer;
  declare @GuestNxt integer;
  declare @StayingNxt integer;
  declare @Rez tinyint;
  declare @NextRNo integer;
  set @StayingNxt=0;
  set @Rez=2;
  select Room,Guest into @Room,@Guest from Book where RegNum = @RNo;
  set @NextRNo = (select MIN(RegNum) from Book where RegNum>@RNo and Guest = @Guest);
  if(select count(*) from Book where RegNum in( @RNo,@NextRNo) ) <> 2 then set @Rez=0
  end if;
  select Room,Guest,Staying into @RoomNxt,@GuestNxt,@StayingNxt from Book where RegNum = @NextRNo;
  if @Rez = 2 then
    if @StayingNxt = 1 then
      if(@Room = @RoomNxt) and(@Guest = @GuestNxt) then
        set @Rez=1
      end if
    else
      if NextIsTheMoved(@NextRNo) = 1 then
        set @Rez=1
      else
        set @Rez=0
      end if
    end if
  end if;
  return(@Rez)
end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillCompletePriceList'))) THEN
Drop procedure FillCompletePriceList
END IF
GO
create procedure FillCompletePriceList()
begin
declare TariffNoNew integer;
for f as curs scroll cursor for
  select TariffNo T, ClassNo C, PriceKindNo P, AdultType A, SeasonNo S, PriceAll PA, KodCurrency K, ExtraBed E, BoardPlan B
  from PriceList group by C,P,A,S,T,PA,K,E,B do
    set TariffNoNew =(select MAX(TariffNo)+1 from PriceList);
    if (select count(*) from PriceList where ClassNo=C and PriceKindNo=P and AdultType=A and SeasonNo=S and HumanKind=1)=0
	then insert into PriceList Values(TariffNoNew,C,P,1,A,S,PA,K,E,B) end if;
	set TariffNoNew =(select MAX(TariffNo)+1 from PriceList);
    if (select count(*) from PriceList where ClassNo=C and PriceKindNo=P and AdultType=A and SeasonNo=S and HumanKind=2)=0
	then insert into PriceList Values(TariffNoNew,C,P,2,A,S,PA,K,E,B) end if;
end for
end
--Dobrin 16.05.2009 - created

GO

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

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

GO

create procedure GetUchGodinaPayments(in @GNo integer)
begin
  declare _egn varchar(18);
  declare _isegnench smallint;
  declare BeginYear integer;
  declare BrGodiniSum integer;
  declare i integer;
  declare LocalI integer;
  declare sqlstmnt varchar(30);//set bvrPayYear(1..5) = xxxx
  set i=5;//izpolzvam i za sqlstmnt - da si pazi vyv for cikyla do koi bvrPayYear sym stignal
  set LocalI=0;//izpolzvam LocalI za lokalniq while cikyl vyv for-a
  set BrGodiniSum = 0;
  select G.egn, G.isegnench, YEAR(dateadd(day,B.Numlodgings,B.DateRegDt))+1 into _egn, _isegnench, BeginYear 
    from Guests G join Book B on G.GuestNum = B.Guest where G.GuestNum=@GNo;
  --obrazuvame vsichki periodi, v koito studenta s tova egn e bil v obshtejitieto  i broim kolko obshto godini e imal plashtaniq
  for f1 as curs scroll cursor for
    select YEAR(DateRegDt) as Ybegin, 
	YEAR(dateadd(day,if Staying = 1 then PayedLodgings else Numlodgings endif,DateRegDt)) as Yend,
	Yend - Ybegin + 1 as BrGodini from Book 
	where Guest in (select GuestNum from Guests where egn = _egn and isegnench = _isegnench) 
	order by RegNum desc do
	
	set LocalI=BrGodini;
	
	if BeginYear=Yend then
	  set BrGodiniSum = BrGodiniSum-1;
	  set LocalI = LocalI - 1;
	end if;	
	
	set BrGodiniSum = BrGodiniSum + BrGodini;
	
	while (LocalI>0) AND (i>0) loop
	  set sqlstmnt='set bvrPayYear'||cast(i as varchar(1))||' = ';
	  set sqlstmnt=sqlstmnt||Yend-BrGodini+LocalI;
	  
	  execute (sqlstmnt);
	
	  set LocalI=LocalI-1;
	  set i=i-1
	end loop;
	
	set BeginYear = Ybegin;
  end for;	

  if BrGodiniSum<5 then
--Myrdam godinite po mestata im, poneje ako ima po-malko ot 5 godini prestoi nqma da sa podredeni kakto trqbva	
	while (BrGodiniSum > 0) loop
	  set sqlstmnt = 'set bvrPayYear'||cast(5-BrGodiniSum+1-i as varchar(1))||'='||'bvrPayYear'||cast(5-BrGodiniSum+1 as varchar(1));
	  execute (sqlstmnt);
	  set BrGodiniSum=BrGodiniSum-1;
	end loop;
	
--popylvam poslednite godini	
	while (i>0) loop
	  set sqlstmnt = 'set bvrPayYear'||cast(6-i as varchar(1))||'='||'bvrPayYear'||cast(6-i-1 as varchar(1))||'+1';
	  execute (sqlstmnt);
	  set i=i-1;	  
	end loop
  end if;
end

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('SvoRedove'))) THEN
Drop Table SvoRedove
END IF
GO
create table SvoRedove
(
	N integer default autoincrement,
	Svoistvo varchar(50),
	Primary Key (N)
)
GO
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('SvoRedoveStn'))) THEN
Drop Table SvoRedoveStn
END IF
GO
create table SvoRedoveStn
(
	ArticleNo integer,
	InvoiceNo integer,
	Svoistvo integer,
	Stoinost varchar(100),
	Primary Key (Svoistvo,ArticleNo,InvoiceNo)
)
GO
ALTER TABLE SvoRedoveStn
ADD NOT NULL FOREIGN KEY "SvoRedoveStn_Articles_N" ("ArticleNo","InvoiceNo")
REFERENCES "Articles" ("ArticleNo","InvoiceNo")
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE SvoRedoveStn
ADD NOT NULL FOREIGN KEY "SvoRedoveStn_SvoRedove_N" ("Svoistvo")
REFERENCES "SvoRedove" ("N")
ON UPDATE CASCADE
ON DELETE CASCADE
GO

insert into SvoRedove Values(1,'Месец Наем')
GO

insert into SvoRedove Values(2,'Година Наем')

GO

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

GO

create function
DBA.GetRealDatePayedLodgForMonth(in @RegNo integer,in @DayNo integer default null)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffDays integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  select DateRegDt into @BookRegDate from Book where RegNum = @RegNo;
  if @DayNo is null then
    set @SpravkaDate="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)
  else
    set @SpravkaDate="date"(@DayNo || '.' || bvrSpravkaM || '.' || bvrSpravkaY)
  end if;
  set @DiffDays=datediff(day,@BookRegDate,@SpravkaDate);
  set @ResultDate=null;
  for f as curs scroll cursor for
    select Inv.InvoiceNo as @InvoiceNo,
      Inv.InvoiceDateDt as @InvoiceDateDt,
      A.Qty as @Qty from
      Invoices as Inv,Articles as A where
      A.InvoiceNo = Inv.InvoiceNo and
      A.RegNo = @RegNo and
      Inv.CancelInvoiceNo = 0 order by
      Inv.InvoiceNo asc do
    set @DiffDays=@DiffDays-@Qty;
    if(@DiffDays <= 0) then
      set @ResultDate=@InvoiceDateDt
    end if end for;
  return(@ResultDate)
end

GO

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

GO

create function
DBA.GetQtyLodgForMonth(in @RegNo integer,in @NumberLodgings integer,in @SeasonBegDt Date default null, in @SeasonEndDt Date default null)
returns integer
begin
  declare @DateEnd date;
  declare @DateBegin date;
  declare @res integer;
  select DateRegDt,dateadd(day,@NumberLodgings,DateRegDt) into @DateBegin,@DateEnd from Book where RegNum = @RegNo;
  if(@DateEnd > dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY))) then
    set @DateEnd=dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY))
  end if;
  if(@DateBegin < "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) then
    set @DateBegin="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)
  end if;
  if (@SeasonBegDt is not null) AND (@SeasonEndDt is not null) then
    if (@DateBegin < @SeasonBegDt) then set @DateBegin = @SeasonBegDt end if;
    if (@DateEnd > @SeasonEndDt) then set @DateEnd = dateadd(day,1,@SeasonEndDt) end if
  end if;
  set @res=datediff(day,@DateBegin,@DateEnd);
  if(@res < 0) then
    set @res=0
  end if;
  return(@res)
end

GO

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

GO

create view
  DBA.v_OwePaidLodgMonth
  as select
    SeassonNo,
    StartDate,
	EndDate,
    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,1),
    Cena=GetRentPriceForDate(B.RegNum,null,SeassonNo),
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings,StartDate,EndDate),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings,StartDate,EndDate),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum,if MONTH(StartDate)=bvrSpravkaM then DAY(StartDate)-1 else 0 endif + Kol),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
    DaysInMonth=Kol 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,PriceSeasonPeriods PSP where
    B.PriceKind = PK.PriceKind and
    G.GuestNum = B.Guest and
    PK.NachinPlat = 0 and
    G.Kurs = K.N and
    G.SpecNo = S.N and
	(MONTH(PSP.StartDate)+12*YEAR(PSP.StartDate))<=(bvrSpravkaM + 12*bvrSpravkaY) and
	(MONTH(PSP.EndDate)+12*YEAR(PSP.EndDate))>=(bvrSpravkaM + 12*bvrSpravkaY) and
    G.UniN = U.N and
    B.KodCurrency = C.KodCurrency and
    R.RoomNum = B.Room and
    G.NativeCountry = Countries.CountryKod and
    B.DateRegDt < dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) and
     dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY) and Kol<>0