if (not exists(select N from SvoServiceCredits where N=7)) then
  insert into SvoServiceCredits(N,Ime,Opisanie,DanTip,Nachalo,Syzdatel,PickList)
    values(7,'RegNo неустойка','Служебно свойство - ако услугата е неустойка по наем - указва записа от Book, за който се отнася','D','',(select Min(AdminNo) from Admin where AdminGroup=1),'')
end if  
go

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

GO

create procedure
DBA.AddForfeitsForBook(in @RegNo integer,in @CurrentDate date)
begin atomic
  declare @Price numeric(12,2);
  declare @ArticlePrice numeric(12,2);
  declare @MDays integer;
  declare @NumDays integer;
  declare @DateStart date;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @StartDateNotPayed date;
  declare @CoveredByDepositsDate date;
  declare @Interest integer;
  declare @ForfeitServiceNo integer;
  declare @ForfeitNumMonths integer;
  declare @InterestMonths integer;
  declare @ForfeitMonth integer;
  declare @ForfeitYear integer;
  declare @OldCreditNo integer;
  declare @OldForfeitPrice decimal(12,2);
  declare @OldForfeitPayed integer;
  declare @OldRegNo integer;
  declare @PayChislo integer;
  declare @ForfeitName varchar(100);
  declare @DaysLate integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @TipDDS integer;
  declare @NowDate date;
  declare @WhoPays integer;
  declare @Guest integer;
  declare @AlternativeName varchar(100);
  declare @TempArticlePrice decimal(12,2);
  declare @ForfeitPrice decimal(12,2);
  declare @ForfeitTipDDS integer;
  declare @ForfeitCreditNo integer;
  declare @KodCurrency integer;
  declare @IteratorMonths integer;
  declare @ShouldAddForfeit integer;
  select B.Room,B.Guest,R.RoomName,PK.NachinPlat,B.Price,isNull(PK.ServiceNo,-1),dateadd(day,B.PayedLodgings,B.DateRegDt),
    B.WhoPays,KodCurrency,B.NumLodgings-B.PayedLodgings into @RoomNo,@Guest,
    @RoomName,@NachinPlat,@Price,@ServiceNoLodging,@StartDateNotPayed,
    @WhoPays,@KodCurrency,
    @NumDays from
    Book as B,Rooms as R,PriceKinds as PK where
    B.RegNum = @RegNo and B.Room = R.RoomNum and B.PriceKind = PK.PriceKind;
  select S.ServiceNo,S.ServiceName,VS.TipDDS into @ForfeitServiceNo,@ForfeitName,@ForfeitTipDDS from Registers as R,Services as S,VidSdelki as VS where
    R.RegisterName = 'ForfeitService' and R.IntStojnost = S.ServiceNo and S.TipSdelka = VS.N;
  if @ForfeitServiceNo is null then
    set @ForfeitServiceNo=0
  end if;
  select IntStojnost into @PayChislo from Registers where RegisterName = 'PayChislo';
  if @PayChislo is null then
    set @PayChislo=0
  end if;
  if(@NachinPlat = 1) and(@ForfeitServiceNo <> 0) and(@PayChislo > 0) then
    set @NowDate=dateadd(day,-@PayChislo,@CurrentDate);
    set @CoveredByDepositsDate=GetDateCoveredByDeposits(@RegNo);
    if(@CoveredByDepositsDate > @StartDateNotPayed) then
      set @StartDateNotPayed=@CoveredByDepositsDate
    end if;
    set @ForfeitNumMonths=datediff(month,@StartDateNotPayed,@NowDate)+1;
	// proveriava dali da se nachisliava neustojka
	if (@ForfeitNumMonths > 1) then
	  set @ShouldAddForfeit = 1;
	else 
	  if (@ForfeitNumMonths < 1) then
	    set @ShouldAddForfeit = 0;
	  else // kogato @ForfeitNumMonths = 0
	    set @ShouldAddForfeit = 0;
		// kogato e nastanen predi datata za plashtane na naema - nachisliava
	    if (datepart(day,@StartDateNotPayed) < @PayChislo) then
		  set @ShouldAddForfeit = 1;
		  // ili kogato e premestvan ot druga staia i v drugata staia e nastanen predi datata za plashtane - nachisliava
		else  
		  if (exists(select RegNum from Book where Guest=@Guest and Staying=2 and dateadd(day,PayedLodgings,DateRegDt) < @NowDate)) then
		    set @ShouldAddForfeit = 1;
		  end if;
		end if;
	   end if;
	end if;
    if (@ShouldAddForfeit =1) then
      select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
      select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
      if @TipDDS = 1 then
        set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
        set @TipDDS=2
      end if;
      set @DateStart=@StartDateNotPayed;
      set @IteratorMonths=@ForfeitNumMonths;
      while((@NumDays > 0) and(@IteratorMonths > 0)) loop
        set @IteratorMonths=@IteratorMonths-1;
        delete from #ArticleLodgings;
		set @InterestMonths=datediff(month,@DateStart,@CurrentDate)+1;
        select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,0,'наем',1,@TipDDS,'') into @MDays;
        select(if TipDDS = 2 then Price*(1+bvrDDSPart) else Price endif) into @TempArticlePrice
          from #ArticleLodgings;
        if(@TempArticlePrice > 0) then
          // nachisliava neustojkata ako e nujno
          // izchisliava procenta neustojka
          set @Interest=6*@InterestMonths;
          if @InterestMonths = 1 then
            set @DaysLate=datepart(day,@NowDate);
            if @DaysLate > 0 then
              if @DaysLate <= 6 then
                set @Interest=@DaysLate
              end if
            else
              set @Interest=0
            end if
          end if; // @InterestMonths=1
          if(@Interest > 0) then
            set @ForfeitMonth=month(@DateStart);
            set @ForfeitYear=year(@DateStart);
            // proveriava dali veche e nachislena neustojka
            set @OldCreditNo=null;
            set @ForfeitPrice=Round(@Interest*@TempArticlePrice/100,2);
            select SC.CreditNo,SC.PayedServices,SC.SumCredit, convert(integer,SSCRegNo.Stoinost) into @OldCreditNo,@OldForfeitPayed,
              @OldForfeitPrice, @OldRegNo from ServiceCredits as SC,SvoServiceCreditsStn as SSCYear,SvoServiceCreditsStn as SSCMonth,SvoServiceCreditsStn as SSCRegNo where
              SC.Service = @ForfeitServiceNo and WhoPays = @WhoPays and
              SC.CreditNo = SSCYear.Glava and SSCYear.Svoistvo = 5 and SSCYear.Stoinost = convert(varchar(100),@ForfeitYear) and
              SC.CreditNo = SSCMonth.Glava and SSCMonth.Svoistvo = 4 and SSCMonth.Stoinost = convert(varchar(100),@ForfeitMonth) and
              SC.CreditNo = SSCRegNo.Glava and SSCRegNo.Svoistvo = 7 and SSCRegNo.Stoinost = convert(varchar(100),@RegNo);
            if(@OldCreditNo is not null) then
              if(@OldForfeitPayed = 0) and(@OldForfeitPrice <> @ForfeitPrice) then
                delete from ServiceCredits where CreditNo = @OldCreditNo
              else
                set @Interest=0
              end if
            end if;
            // dobavia lihvata vuv servicecredits
            if(@Interest > 0) then
              set @AlternativeName=@ForfeitName || ' ' || ImeMesec(month(@DateStart)) || ' в стая ' || @RoomName;
              set @ForfeitPrice=Round(@Interest*@TempArticlePrice/100,2);
              set @ForfeitCreditNo=GetCounter('CreditCounter');
              insert into ServiceCredits( CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,
                NumberServices,PayedServices,WhoPays,GroupPays,ContractPays,RoomPays,RoomNum) values( 
                @ForfeitCreditNo,FloatToDate(Now(*)),@WhoPays,@ForfeitServiceNo,@ForfeitPrice,1,bvrUserN,1,0,@WhoPays,0,0,0,@RoomNo) ;
              // dobavia dopulnitelnite svojstva
              // alternativno ime
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost) values( 
                @ForfeitCreditNo,1,@AlternativeName) ;
              // mesec
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost) values( 
                @ForfeitCreditNo,4,@ForfeitMonth) ;
              // godina
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost) values( 
                @ForfeitCreditNo,5,@ForfeitYear) ;
              // procent lihva
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost) values( 
                @ForfeitCreditNo,6,@Interest);
              // RegNo
              insert into SvoServiceCreditsStn( Glava,Svoistvo,Stoinost) values( 
                @ForfeitCreditNo,7,@RegNo);
            end if
          end if // @Interest > 0
        end if; // (@TempArticlePrice > 0)
        set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
        set @NumDays=@MDays
      end loop
    end if // @ForfeitNumMonths > 1 or ...
  end if //(@NachinPlat = 1) and (@ForfeitServiceNo <> 0) and (@PayChislo > 0)
end
//[09.02.2009 - Nixon] - Promeneno e lihvata da stava s natrupvane - t.e. ako zakusnee s poveche ot 
// edin mesec shte bude 12%, s 3 meseca  - 18% i t.n.
//[13.02.2009 - Nixon] - Promeneno e ako se zakusnee poveche ot edin mesec za tekushtiat mesec pak da vzima vpredvid PayChislo-to

go

delete from ServiceCredits 
  where 
    Service=(select IntStojnost from Registers where RegisterName='ForfeitService') 
	and (exists(select Glava from SvoServiceCreditsStn  where Glava=CreditNo and Svoistvo=6))
	and (not exists(select Glava from SvoServiceCreditsStn  where Glava=CreditNo and Svoistvo=7))
	
go

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

GO

create view
  DBA.v_DaylyListForPeriod
  as select BK.GuestNum,
    Name=BK.GuestName,
    BK.ArriveDate,
    BK.LeaveDate,
    --Maria
    --BK.LastRoom,
    BK.LastRoomName,
    --Maria
    BK.NumNights,
    Price=BK.RegPrice,
    PriceKind=BK.PriceKind,
    G.Balance,
    -- Yanko + fix_18_August_2006
    /*
    xxxxx=isnull((select sum((SC.PayedServices*sc.SumCredit)*dr.ExchangeRate)
    from "dba".ServiceCredits as sc,"dba".DayRates as dr,"dba".Resmoney as rs
    where sc.payedservices>0
    and sc.kodcurrency=dr.kodcurrency
    and sc.datecreditdt=dr.datedt and sc.GuestNo=BK.GuestNum
    and BK.GuestNum=rs.Guestno
    and rs.datemoneydt between bvrdata1 and bvrdata2),0),
    */
    xxxxx=isnull((select SUM(R.SUM*C.ExchangeRate) from
      DBA.ResMoney as R,DBA.Currencies as C,DBA.Admin as A where
      R.GuestNo = BK.GuestNum and
      R.Currency = C.KodCurrency and
      R.KodAdmin = A.AdminNo and
      R.DateMoney <> 0),
    --  AND R.DateMoneyDt between bvrdata1 and bvrdata2
    0),
    -- Suma=(BK.PayNightPrice+xxxxx+GetPayedDepositsByGuest(BK.GuestNum)),
    Suma=xxxxx,
	Total = G.Balance + Suma, -- Milko za MON p2278
    -- Yanko -
    Passport=String(G.PassportSeria,G.PassportNumber,' ',G.PassportDateDt), --Krum
    G.EGN,
    Country=C.Description,
    AddressData=G.Address,
    --Dobrin 2195 +
    G.FN,
    UName=U.FullName,
    SName=Spec.Name,
    Kurs=K.Name,
    --Dobrin -
    ContractName=Ctr.FullName,BK.Admin from
    dba.v_NewArriveStayLeaveGuestBook as BK,
    dba.Guests as G,
    dba.Countries as C,
    dba.Contracts as Ctr,
    --Dobrin 2195 +
    dba.Universities as U,
    dba.Kursove as K,
    dba.Specialities as Spec where
    --Dobrin -
    /* /Krum/  BK.ArriveDate <> BK.LeaveDate and */
    --Dobrin 2195+
    U.N = G.UniN and K.N = G.Kurs and Spec.N = G.SpecNo and
    --Dobrin -
    G.GuestNum = BK.GuestNum and
    C.CountryKod = G.NativeCountry and
    G.Contract = Ctr.ContractNo and
    /* /Krum/  (DBA.DateIntersection(bvrData1,bvrData2,BK.ArriveDate,BK.LeaveDate) > 0) */
    ((BK.ArriveDate >= bvrData1 and BK.ArriveDate <= bvrData2) or(BK.LeaveDate >= bvrData1 and BK.LeaveDate <= bvrData2) or
    (BK.ArriveDate < bvrData1 and BK.LeaveDate > bvrData2))
//[v 18.04.2005] Jorko
//[v 21.06.2005] Jorko
//[v 27.06.2005] Safa
//[v 22.08.2006] Yanko
//[v 29.05.2008] Maria - dobavena kolona PriceKind (Cenorazpis)
//[v 11.02.2009] Milko - Dobavena kolona Total za MON


go

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

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_DaylyListForPeriod','__Options__','Дневен лист за период','__Options__','Dayly list for period','__Options__','1','30351','__Options__','','','0','0','0','0','Printer.Orientation=1','','80','','0','0','0','06.02.2009')

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_DaylyListForPeriod','bvrData1','Дневен лист за период','От дата','Dayly list for period','From Date','2','30361','bvrData1','','','0','0','1','1','','select bvrData1','72','','0','0','1','06.02.2009')

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_DaylyListForPeriod','bvrData2','Дневен лист за период','До дата','Dayly list for period','From Date','2','30371','bvrData2','','','0','0','1','1','','select bvrData1','72','','0','0','1','06.02.2009')

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_DaylyListForPeriod','GuestNum','Дневен лист за период','Гост','Dayly list for period','GuestNum','2','30381','GuestNum','','','1','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Name','Дневен лист за период','Име','Dayly list for period','Name','2','30391','Name','','','0','0','','','','','200','','0','0','1','06.02.2009')

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_DaylyListForPeriod','ArriveDate','Дневен лист за период','Дата прист.','Dayly list for period','ArriveDate','2','30401','ArriveDate','','','0','1','','','','','80','','0','0','1','06.02.2009')

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_DaylyListForPeriod','LeaveDate','Дневен лист за период','Дата замин.','Dayly list for period','LeaveDate','2','30411','LeaveDate','','','0','0','','','','','80','','0','0','1','06.02.2009')

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_DaylyListForPeriod','LastRoomName','Дневен лист за период','Стая','Dayly list for period','LastRoom','2','30421','LastRoom','','','1','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','NumNights','Дневен лист за период','Нощувки','Dayly list for period','NumNights','2','30431','NumNights','','','1','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Price','Дневен лист за период','Цена за месец','Dayly list for period','Price','2','30441','Price','','# ### ##0.00','1','0','','','','','80','','0','0','1','11.02.2009')

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_DaylyListForPeriod','Suma','Дневен лист за период','Платено','Dayly list for period','Suma','2','30451','Sum(Suma)','Sum(qrData.Платено)','# ### ##0.00','1','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Balance','Дневен лист за период','Остатък','Dayly list for period','Balance','2','30461','Sum(Balance)','Sum(qrData.Остатък)','# ### ##0.00','1','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Total','Дневен лист за период','Общо','Dayly list for period','Total','2','30462','Sum(Total)','Sum(qrData.Общо)','# ### ##0.00','1','0','','','','','60','','0','0','1','11.02.2009')

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_DaylyListForPeriod','Passport','Дневен лист за период','Паспорт','Dayly list for period','Passport','2','30471','Passport','','','0','0','','','','','150','','0','0','1','06.02.2009')

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_DaylyListForPeriod','EGN','Дневен лист за период','ЕГН','Dayly list for period','EGN','2','30481','EGN','','','0','0','','','','','80','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Country','Дневен лист за период','Държава','Dayly list for period','Country','2','30491','Country','','','0','0','','','select Description from Countries where Active=1','','80','','0','0','1','06.02.2009')

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_DaylyListForPeriod','AddressData','Дневен лист за период','Местожителство','Dayly list for period','AddressData','2','30501','AddressData','','','0','0','','','','','150','','0','0','1','06.02.2009')

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_DaylyListForPeriod','ContractName','Дневен лист за период','Договор','Dayly list for period','ContractName','2','30511','ContractName','','','0','0','','','select FullName from Contracts where Active=1','','150','','0','0','1','06.02.2009')

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_DaylyListForPeriod','Admin','Дневен лист за период','Потребител','Dayly list for period','Admin','2','30521','Admin','','','0','0','','','','','150','','0','0','1','06.02.2009')

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_DaylyListForPeriod','UName','Дневен лист за период','Университет','Dayly list for period','UName','2','30531','UName','','','0','0','','','','','230','','0','0','1','11.02.2009')

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_DaylyListForPeriod','SName','Дневен лист за период','Специалност','Dayly list for period','SName','2','30541','SName','','','0','0','','','','','230','','0','0','1','11.02.2009')

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_DaylyListForPeriod','Kurs','Дневен лист за период','Курс','Dayly list for period','Kurs','2','30551','Kurs','','','0','0','','','','','60','','0','0','1','06.02.2009')

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_DaylyListForPeriod','FN','Дневен лист за период','Факултетен номер','Dayly list for period','FN','2','30561','FN','','','0','0','','','','','110','','0','0','1','06.02.2009')


go

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

GO

create view
  DBA.v_ProdajbiPoUslugi as
select  OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo endif,
    DokData=INV.InvoiceDateDt,
    --Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Service=Art.ArticleName, --Dobrin 2195 - vmesto ot Services da vzima imeto na Cenorazpisa
    /*Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,*/
    /*Kol=if (ART.totalsum < 0 ) then ((-1)*ART.Qty) else ART.Qty endif,*/
    Kol=if(ART.totalsum > 0) then
      ART.Qty
    else
      if(ART.Qty < 0) then
        ART.Qty
      -- Yanko +
      -- dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0
      else -- ((-1)*ART.Qty)
        if(ART.Totalsum = 0) then
          ART.QTY
        -- Yanko -
        --Maria
        --RoomNo=convert(varchar,BK.Room),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        --Maria
        --Maria
        --Maria
        --Maria
        --Maria +, 1975
        --Maria -
        --Maria
        --RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        -- RoomNo=(if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif)
        --and ART.ServiceNo not in(1004,1005)
        --Maria +, 1975
        --Maria -
        --Maria
        --RoomNo=max(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        --RoomNo=Max((if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif))
        --Maria +, 1975
        --Maria -
        -- [v.1 --]
        -- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates
        -- [v.3 26.May.2008] - gnikolov, добавена колона ДДС, p1902
        --Maria
        --RoomNo=(if(GuestNum > 0) then convert(varchar,(select distinct vv.LastRoom from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum)) else null endif),
        --Maria
        --Maria +,1982
        --Maria -
        else -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
          ((-1)*ART.Qty)
        endif
      endif
    endif,
//    EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS in (2,3) then 1 else 0 endif)),4), --Milko TipDDS in(2, 3)
	    EdCena=round(abs(ART.Price),4), --Milko TipDDS in(2, 3)
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    DanNo=INV.WhomDanNum,
    GuestNum=G.GuestNum,
    GuestName=G.Name,
    Komentar=String(DateRegDt,' - ',"Date"(Days(DateRegDt,NumLodgings))),
    Operator=ADMIN.ShortName,
    ContractName=(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Countries.Description,
    Nationality=HK.Description,
    RoomNo=R.RoomName,
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.PriceKinds as PK, --Dobrin 2195
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.Book as BK,
    DBA.Rooms as R,
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,
    DBA.Countries,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    BK.PriceKind = PK.PriceKind and --Dobrin 2195
    BK.Room = R.RoomNum and
    ART.InvoiceNo = INV.InvoiceNo and
    Abs(RegNo) = BK.RegNum and
    BK.RegNum <> 0 and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    BK.Guest = G.GuestNum and
    INV.KodAdmin = ADMIN.AdminNo and
    ART.ServiceNo = Services.ServiceNo and
    G.Contract = Contracts.ContractNo and
    G.HumanKind = HK.HumanKindNo and
    G.NativeCountry = Countries.CountryKod and
    VR.ID = Art.TipDDS union all
select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo endif,
    DokData=INV.InvoiceDateDt,
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    Service=Art.ArticleName,
    Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif,
	EdCena=round(abs(ART.Price)*((if Art.TipDDS in (2) then 1.2 else 1 endif)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    DanNo=INV.WhomDanNum,
    GuestNum=G.GuestNum,
    GuestName=G.Name,Komentar='',
    Operator=ADMIN.ShortName,
    ContractName=(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Countries.Description,
    Nationality=HK.Description,
    RoomNo=(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.ServiceCredits as SC,
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,dba.Countries,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    ART.InvoiceNo = INV.InvoiceNo and
    ART.CreditNo = SC.CreditNo and
    ART.CreditNo <> 0 and
    SC.GuestNo = G.GuestNum and
    INV.KodAdmin = ADMIN.AdminNo and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    ART.ServiceNo = Services.ServiceNo and
    G.Contract = Contracts.ContractNo and
    G.NativeCountry = Countries.CountryKod and
    G.HumanKind = HK.HumanKindNo and
    VR.DDSType = INV.DDSType union all
  select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((max(INV.TrueInvoiceNo) <> 0) and(max(INV.TrueInvoiceNo) < 1000000000)) then max(INV.MaskaFaktura) || substr('0000000000',1,9-length(max(INV.TrueInvoiceNo))) || max(INV.TrueInvoiceNo) endif,
    DokData=Max(INV.InvoiceDateDt),
    DokTime=DateFormat(Max(INV.TrueInvoiceHour),'hh:mm'),
    Service=Max(Services.ServiceName),
    Kol=if INV.CancelInvoiceNo > 0 then(-1)*max(ART.Qty) else max(ART.Qty) endif,
    EdCena=round(Max(abs(ART.Price)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*Max(abs(ART.Price)),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=Max(INV.Whom),
    DanNo=Max(INV.WhomDanNum),
    GuestNum=Max(RM.GuestNo),
    GuestName=Max(G.Name),Komentar='',
    Operator=Max(ADMIN.ShortName),
    ContractName=Max(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Max(Countries.Description),
    Nationality=Max(HK.Description),
    RoomNo=max(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,
    DBA.Countries,
    DBA.ResMoney as RM,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    ART.InvoiceNo = INV.InvoiceNo and
    ART.CreditNo = 0 and
    ART.RegNo = 0 and
    RM.InvoiceNo = INV.InvoiceNo and
    G.GuestNum = RM.GuestNo and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    G.Contract = Contracts.ContractNo and
    G.NativeCountry = Countries.CountryKod and
    INV.KodAdmin = ADMIN.AdminNo and
    ART.ServiceNo = Services.ServiceNo and
    G.HumanKind = HK.HumanKindNo and
    ART.ServiceNo in( 1004,1005) and
	VR.ID = Art.TipDDS
    group by ART.InvoiceNo,
    ART.ArticleNo,
    INV.CancelInvoiceNo,
    Discount,
    ART.DDSType,INV.Fiscal union all
  select OtchetNumber=DBA.GetOtchetNumber(VA.InvoiceNo),
    SmetkaNo=VA.InvoiceNo,
    FakturaNo=if((VI.TrueInvoiceNo <> 0) and(VI.TrueInvoiceNo < 1000000000)) then VI.MaskaFaktura || substr('0000000000',1,9-length(VI.TrueInvoiceNo)) || VI.TrueInvoiceNo endif,
    DokData=VI.InvoiceDateDt,
    DokTime=DateFormat(VI.TrueInvoiceHour,'hh:mm'),
    Service=S.ServiceName,
    Kol=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights) endif),
    EdCena=Round(Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif)*
    (if VI.VAT = 0 then 1
    else(1+VR.Rate)
    endif),4),Stoinost=Round(Kol*EdCena,4),
    StoinostBDDS=Round(Kol*Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif),
    4),DDS=Stoinost-StoinostBDDS,
    Discount=(if VA.SinglePrice <> 0 then VA.Discount else 0 endif),
    EdCenaWithDisc=Round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=Round(Kol*EdCenaWithDisc,4),
    Klient=VI.Whom,
    DanNo=VI.WhomDanNum,
    GuestNum=(if VA.CreditNo > 0 then(select SC.GuestNo from DBA.ServiceCredits as SC where SC.CreditNo = VA.CreditNo)
    else-1
    endif),GuestName=(if VA.CreditNo > 0 then(select G.Name from DBA.Guests as G,DBA.ServiceCredits as SC where G.GuestNum = SC.GuestNo and SC.CreditNo = VA.CreditNo)
    else '-' endif),Komentar='',Operator=ADMIN.ShortName,
    ContractName=C.ShortName,
    Country=(if GuestNum > 0 then(select CC.Description from DBA.Countries as CC,DBA.Guests as G where CC.CountryKod = G.NativeCountry and G.GuestNum = GuestNum)
    else '-' endif),Nationality=(if GuestNum > 0 then(select HK.Description from DBA.HumanKinds as HK,DBA.Guests as G where HK.HumanKindNo = G.HumanKind and G.GuestNum = GuestNum)
    else '-' endif),RoomNo=(if(GuestNum > 0) then(select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.VaucherArticles as VA,
    DBA.VaucherInvoices as VI,
    DBA.Services as S,
    DBA.VATRates as VR,
    DBA.Admin,
    DBA.Contracts as C,
    DBA.Currencies as Cr where
    VA.InvoiceNo = VI.VaucherInvoiceNo and
    VA.ServiceNo = S.ServiceNo and
    VR.ID = 4 and
    VI.KodAdmin = Admin.AdminNo and
    C.ContractNo = VI.ContractNo and
    VI.InvoiceDateDt >= bvrData1 and
    VI.InvoiceDateDt <= bvrData2 and
    Cr.KodCurrency = VI.KodCurrency and
    ((select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)        -- [v.1 --]
        -- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates
        -- [v.3 26.May.2008] - gnikolov, добавена колона ДДС, p1902
        -- [v.4 11.02.2009] - Milko Korigirano pole edCena izlizashe s nuleva cena, imenata na uslugite da gi izpisva s pylno ime

go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=-1))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, -1, 1)
end if

go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=1))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, 1, 1)
end if

go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=2))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, 2, 1)
end if

go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=3))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, 3, 1)
end if

go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=4))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, 4, 1)
end if



go

if (not exists(select N from InformPermission where RelationN=30351 and AdminGroupNo=5))
then insert into InformPermission (N,RelationN,AdminGroupNo,Stoinost)
values ((select Max(N) + 1 from InformPermission), 30351, 5, 1)
end if