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 proc_name from sysprocedure where LCase(proc_name)=LCase('PrepareAddrCard'))) THEN
Drop procedure PrepareAddrCard
END IF
GO
create procedure PrepareAddrCard(in @Gno integer)
begin
  declare @MonthsGreek varchar(50);
  declare i integer;
  declare @BroiPayments integer;
  declare j integer;
  declare sqlsttm varchar(200);
  set @BroiPayments=(SELECT count(*) FROM Book B,Articles A,Invoices Inv,Currencies C WHERE B.WhoPays = @Gno AND B.KodCurrency = C.KodCurrency AND B.RegNum=A.RegNo AND A.InvoiceNo=Inv.InvoiceNo AND Inv.CancelInvoiceNo=0);
  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 i=(select MONTH(DateRegDt) from Book where Guest=@Gno and Staying=1);
  set j=1;
  for f1 as curs1 scroll cursor for
   select if TipDDS=2 then A.Price*(1+bvrDDSPart) else A.Price endif as Price,Inv.InvoiceNo
   FROM Book B,Articles A,Invoices Inv,Currencies C
   WHERE B.WhoPays = @Gno AND B.KodCurrency = C.KodCurrency AND B.RegNum=A.RegNo AND A.InvoiceNo=Inv.InvoiceNo AND Inv.CancelInvoiceNo=0
   ORDER BY A.InvoiceNo,A.ArticleNo do
   set sqlsttm='update #AddrCardPayments set lvUG'||j||'='||Round(Price,2)||',kvUG'||j||'='||InvoiceNo||' where MesReal='||i;
   execute(sqlsttm);
   set i=i+1;
   if i>12 then
     set i=1;
	 set j=j+1;
   end if
  end for;
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 * 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 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 ( 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
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;
  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
  if @DateEnd > dateadd(day,@NumDays,@DateReg) then
    set @DateEnd=dateadd(day,@NumDays,@DateReg);
    set @IsFullMonth=0
  end if;
  //izchisliava broiat dni
  set @RealLodgings=datediff(day,@DateStart,@DateEnd);
  // setva imeto na uslugata
  set @YearService='' || datepart(year,@DateStart);
  set @YearService="right"(@YearService,2);
  set @ArticleName=@ServiceName || ' ' || ImeMesec(month(@DateStart)) || '''' || @YearService || @place || @RoomName;
  if(@IsFullMonth = 0) then
    if (select MonthIsWith30Days from Hotels where Hotel_ID = (select IntStojnost from Registers where RegisterName='HotelID'))=0 then
      set @DaysInMonth=day(dateadd(day,-day(@DateStart),dateadd(month,1,@DateStart)))
	else
	  set @DaysInMonth=30
	end if;
    set @ArticleName=@ArticleName || ' ' || @RealLodgings || '/' || @DaysInMonth;
    set @ArtPrice=(@ArtPrice*@RealLodgings)/@DaysInMonth
  end if;
  // izchisliava dali da e po podrazbirane izbrana uslugata za plashtane ili ne
  set @Qty=1;
  if(@FillAll = 0) then
    if(month(@DateStart) > month(now(*)) and year(@DateStart) = year(now(*))) or(year(@DateStart) > year(now(*))) then
      set @Qty=0
    end if
  end if;
  // vkarva ZIndex za da ne moje naprimer da se plati naem za oktomvri predi da e
  // platen naema za septemvri - tova se pravi vuv Bills formata
  select Max(ZIndex)+1 into @ZIndex from #ArticleLodgings;
  if @ZIndex is null then
    set @ZIndex=1
  end if;
  // vkarva uslugata
  insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS,RentMonth,RentYear) values( 
    @RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@ServiceTipDDS,MONTH(@DateStart),YEAR(@DateStart)) ;
  // namalia @NumDays sus realnite noshtuvki i vrushta rezultata
  set @R=@NumDays-@RealLodgings;
  return(@R)
end
// [20.02.2009] - Nixon - dobavena godinata kum imeto na smetkata
// [23.05.2009] - Dobrin - dobaveno e popylvaneto na poletata RentMonth i RentYear v #ArticleLodgings
// [29.05.2009] - Dobrin - syobrazqva se s Hotels.IsMonthWith30Days
go

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

GO

create function
DBA.FillLodgingsForBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50),in @RRDate date)
returns integer
// Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles
// na noshtuvka(pochivka) i uslugite ot paketa, za opredelen zapis ot Book
// Tia se vika ot PrepareGuestBill, PrepareGroupBill i PrepareReserveBill
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @ArticlePrWithDDS double; //nt
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @I integer;
  declare @Disc double;
  --Maria
  declare @RoomName varchar(5);
  --Maria
  --Dobrin
  declare @LodgNum integer;
  declare @RealArticleQty real;
  declare @IsForMonth tinyint;
  --Dobrin
  select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice
    from
    Packages,Currencies,Services,VidSdelki where
    Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and
    Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
  if @PackagePrice is null then
    set @PackagePrice=0;
    set @PackagePrWithDDS=0
  else
    set @PackagePrWithDDS=@PackagePrice
  end if;
  call FillArticleLodgings(@RegNo,@PackagePrice,0,@place);
  set @I=1;
  for f2 as curs2 scroll cursor for
    select Name as @LArticleName,RealLodgingsNum as @LRealLodgings,Price as @LArticlePrice,Qty as @LQty,
      MaxQty as @LMaxQty,MinQty as @LMinQty,ZIndex as @LZIndex,TipDDS as @LTipDDS,ServiceNoLodging as @ServiceNoLodging,
	  RentMonth as @RentMonth, RentYear as @RentYear from
      #ArticleLodgings order by ZIndex asc do
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,RealLodgNum,ZIndex,RentMonth,RentYear) 
	  values(@ArticleNumber+@I,@LArticleName,'бр.',@LQty,@LMaxQty,@LMinQty,@LArticlePrice,0,@RegNo,0,@LArticlePrice*@LQty,
      @LTipDDS,@ServiceNoLodging,round(@LArticlePrice,2),
      round(@LArticlePrice*@LQty,2),@RRDate,@LRealLodgings,@LZIndex,@RentMonth,@RentYear) ;
    set @I=@I+1 end for;
  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services where
      RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Packages.RegNo = Book.RegNum and Book.RegNum = @RegNo; //nt
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;
    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=2
    end if;
    set @ArticlePrWithDDS=@ArticlePrice; //nt
    --Maria +, dopulniteln uslugi
    set @ServiceName=@ServiceName || @place || cast(@Room as varchar(5));
    --Maria -
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ;
    set @I=@I+1;
    set @Disc=0 end for;
  return(@I)
end
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
//[v 2008.12.10 - MON.2] - Nixon - да подрежда
//[v 2009.05.23 - MON.3] - Dobrin - да попълва RentMonth, RentYear в #Articles

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 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, 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 @BroiPayments integer;
  declare j integer;
  declare sqlsttm varchar(200);
  set @BroiPayments=(select count(*) from Book as B,Articles as A,Invoices as Inv,Currencies as C where B.WhoPays = @Gno and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and Inv.CancelInvoiceNo = 0);
  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,Inv.InvoiceNo,cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr from
      Book as B,Articles as A,Invoices as Inv,Currencies as C, SvoRedoveStn as SRS, SvoRedoveStn as SRS1 where
      B.WhoPays = @Gno 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,Inv.InvoiceNo,Mnth,Yr 
	  order by Yr, Mnth asc do
    set i=Mnth;
	set sqlsttm='update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=' || InvoiceNo || ' where MesReal=' || i;
    execute immediate sqlsttm;
    if i = 12 then
      set j=j+1;
    end if;
	end for
end
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,DateRegDt) 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

call FillSvoRedoveStn()
go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetExtraSum'))) THEN
Drop function GetExtraSum
END IF
GO
create function GetExtraSum(in @RoomNo integer)
returns double
begin
  //tazi funkciq vryshta sumata, koqto poprincip studenta shte plati, no za momenta oshte ne e nalojitelno
  //naprimer ako sme mesec mart, naema za mesec april shte trqbva da go plati, no ne i mart
  declare rez double;
  declare @NumMonths double;
  declare @PackagePrice double;
  declare @Price double;
  set rez=0.0;
  for f1 as curs scroll cursor for
    select FloatToDate(Datereg+NumLodgings) @DLDt, RegNum @RegNo, Price @Prc from Book where Room = @RoomNo and Staying=1 do
	
	select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice
    from Packages,Currencies,Services,VidSdelki where
    Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and
    Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
	if @PackagePrice is null then set @PackagePrice=0.0 end if;
	call FillArticleLodgings(@RegNo,@PackagePrice,0,'');
	set rez = rez + (select SUM(Price) from #ArticleLodgings where Qty=0)

  end for;
  if rez=null then set rez=0.0 end if;
  
  return (rez);
end;
//Dobrin - created 22.05.09
go