Delete from Relations where lcase(RelationTable) = lcase('Issued Accounts by Service')

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_ProdajbiPoUslugi','__OPTIONS__','Издадени сметки по услуги','__OPTIONS__','Issued Accounts by Service','__OPTIONS__',1,GetFreeNInRelations('Issued Accounts by Service', 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_ProdajbiPoUslugi','bvrData1','Издадени сметки по услуги','От дата','Issued Accounts by Service','bvrData1',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select bvrData1',56,'',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_ProdajbiPoUslugi','bvrData2','Издадени сметки по услуги','До дата','Issued Accounts by Service','bvrData2',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData2','','',1,0,1,1,'','select bvrData2',56,'',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_ProdajbiPoUslugi','OtchetNumber','Издадени сметки по услуги','Отчет N','Issued Accounts by Service','OtchetNumber',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'OtchetNumber','','',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_ProdajbiPoUslugi','SmetkaNo','Издадени сметки по услуги','С-ка No','Issued Accounts by Service','Account #',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SmetkaNo','','',1,1,'','','','',56,'',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_ProdajbiPoUslugi','FakturaNo','Издадени сметки по услуги','Ф-ра No','Issued Accounts by Service','Invoice Number',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'FakturaNo','','',1,0,'','','','',56,'',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_ProdajbiPoUslugi','DokData','Издадени сметки по услуги','Дата','Issued Accounts by Service','Date',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokData','','',0,0,'','','','',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_ProdajbiPoUslugi','DokTime','Издадени сметки по услуги','Час','Issued Accounts by Service','Time',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokTime','','',0,0,'','','','',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_ProdajbiPoUslugi','Service','Издадени сметки по услуги','Услуга','Issued Accounts by Service','Service',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Service','','',0,0,'','','select ServiceName from Services union
(select ''Нощувка'')  union
(select ''Застраховка'')
','',150,'',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_ProdajbiPoUslugi','Kol','Издадени сметки по услуги','Кол.','Issued Accounts by Service','Quant.',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Kol)','SUM(qrData.Кол.)','0.##',1,0,'','','','',56,'',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_ProdajbiPoUslugi','EdCena','Издадени сметки по услуги','Ед.цена','Issued Accounts by Service','Sng. Price',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCena','','# ### ##0.00',1,0,'','','','',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_ProdajbiPoUslugi','Stoinost','Издадени сметки по услуги','Стойност','Issued Accounts by Service','Value',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Stoinost)','SUM(qrData.Стойност)','# ### ##0.00',1,0,'','','','',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_ProdajbiPoUslugi','StoinostBDDS','Издадени сметки по услуги','Стн. б.ДДС','Issued Accounts by Service','Value (no VAT)',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(StoinostBDDS)','SUM(qrData.Стн. б.ДДС)','# ### ##0.00',1,0,'','','','',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_ProdajbiPoUslugi','DDS','Издадени сметки по услуги','ДДС','Issued Accounts by Service','DDS',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(DDS)','SUM(qrData.ДДС)','# ### ##0.00',0,0,0,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_ProdajbiPoUslugi','Klient','Издадени сметки по услуги','Клиент','Issued Accounts by Service','Client',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Klient','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',160,'',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_ProdajbiPoUslugi','GuestNum','Издадени сметки по услуги','Кл.номер','Issued Accounts by Service','Client Number',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'GuestNum','','',1,0,'','','','',56,'',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_ProdajbiPoUslugi','GuestName','Издадени сметки по услуги','Име гост','Issued Accounts by Service','Guest Name',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'GuestName','','',0,0,'','','SELECT "Name" FROM Guests GROUP BY "Name"','',150,'',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_ProdajbiPoUslugi','ContractName','Издадени сметки по услуги','Фирма','Issued Accounts by Service','ContractName',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'ContractName','','',1,0,'','','select ShortName from Contracts','',150,'',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_ProdajbiPoUslugi','Country','Издадени сметки по услуги','Държава','Issued Accounts by Service','Country',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Country','','',1,0,'','','select Description from Countries where Active=1','',150,'',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_ProdajbiPoUslugi','Komentar','Издадени сметки по услуги','Коментар','Issued Accounts by Service','Comments',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Komentar','','',0,0,'','','','',170,'',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_ProdajbiPoUslugi','Operator','Издадени сметки по услуги','Съставил','Issued Accounts by Service','Signed By',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',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_ProdajbiPoUslugi','Nationality','Издадени сметки по услуги','Националност','Issued Accounts by Service','Nationality',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Nationality','','',0,0,'','','SELECT Description FROM HumanKinds','',150,'',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_ProdajbiPoUslugi','RoomNo','Издадени сметки по услуги','Стая No','Issued Accounts by Service','RoomNo',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'RoomNo','','',1,0,'','','','',50,'',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_ProdajbiPoUslugi','Discount','Издадени сметки по услуги','Отстъпка','Issued Accounts by Service','Discount',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Discount','','',1,0,'','','','',55,'',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_ProdajbiPoUslugi','EdCenaWithDisc','Издадени сметки по услуги','Цена с отстъпка','Issued Accounts by Service','EdCenaWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCenaWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','StoinostWithDisc','Издадени сметки по услуги','Сума с отстъпка','Issued Accounts by Service','StoinostWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'StoinostWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','DDSTypeSt','Издадени сметки по услуги','ДДС Тип','Issued Accounts by Service','DDSTypeSt',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DDSTypeSt','','',0,0,0,0,'select ''20%'' union all select ''7%'' ','',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_ProdajbiPoUslugi','Fiscal','Издадени сметки по услуги','Фискализирана','Issued Accounts by Service','Fiscal',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Fiscal','','',0,0,0,0,'select ''Да'' union all select ''Не'' ','',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_ProdajbiPoUslugi','ServiceName','Издадени сметки по услуги','Име услуга','Issued Accounts by Service','ServiceName',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'ServiceName','','',0,0,'','','select ServiceName from Services','',100,'',0,0,1,today())

Go

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

Go

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

GO

create view
  DBA.v_IzdadeniSmetki as
  select SmetkaNo=Inv.InvoiceNo,
    FakturaNo=if ((Inv.TrueInvoiceNo <> 0) and (Inv.TrueInvoiceNo < 1000000000)) then 
								Inv.MaskaFaktura || substr('0000000000',1,9-length(Inv.TrueInvoiceNo)) || Inv.TrueInvoiceNo
							else 
								convert(varchar,Inv.TrueInvoiceNo)
							endif,
    DokData=Inv.InvoiceDateDt,
    --Maria +, 1975
    DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=Inv.Whom,
    DanNo=Inv.Bulstat,
    Operator=Adm.ShortName,
    SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4),
    DDS=round(Inv.VAT,4),
    Suma=round(Inv.GrandTotal,4),
    PlatVBroi=round(Pari.VBroi,4),
    PlatPoSmetka=round(Pari.PoSmetka,4),
    Plateni=round(Pari.VBroi+Pari.PoSmetka,4),
    DDSTypeSt=(if Inv.DDSType = 1 then '20%' else '7%' endif),
    --Maria +, 1982
    Fiscal=(if Inv.Fiscal = 0 then 'Не' else 'Да' endif) 
	from
    --Maria -
    DBA.Invoices as Inv,DBA.v_PariPoSmetki as Pari,DBA.Admin as Adm 
	where
    Inv.InvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo 
	union all
  select SmetkaNo=Inv.VaucherInvoiceNo,
    FakturaNo=if ((Inv.TrueInvoiceNo <> 0) and(Inv.TrueInvoiceNo < 1000000000)) then 
								Inv.MaskaFaktura || substr('0000000000',1,9-length(Inv.TrueInvoiceNo)) || Inv.TrueInvoiceNo
							else 
								convert(varchar,Inv.TrueInvoiceNo)
							endif,
    DokData=Inv.InvoiceDateDt,
    --Maria +, 1975
    DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=Inv.Whom,
    DanNo=Inv.Bulstat,
    Operator=Adm.ShortName,
    SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4),
    DDS=round(Inv.VAT,4),
    Suma=round(Inv.GrandTotal,4),
    PlatVBroi=round(Pari.VBroi,4),
    PlatPoSmetka=round(Pari.PoSmetka,4),
    Plateni=round(Pari.VBroi+Pari.PoSmetka,4),
		DDSTypeSt='7%',
    --Maria +, 1982
    Fiscal=(if Inv.Fiscal = 0 then 'Не' else 'Да' endif) 
	from
    --Maria -
    DBA.VaucherInvoices as Inv,DBA.v_VtdPariPoSmetki as Pari,DBA.Admin as Adm
	where
    Inv.VaucherInvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo and(
    (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)
-- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
--[30.07.2009] - Yoan - Izchislqva pravilno SumaBDDS

GO

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

GO

create function
DBA.GetRealDatePayedRentForPeriod(in @RegNo integer, in @StartDate date, in @EndDate date)
returns date
begin
  declare @DatePaidTo date;
	declare @PartlyPaidToOnDate date;
	set @PartlyPaidToOnDate = null;
	set @DatePaidTo=dateadd(day,-1,(select DateRegDt from Book where RegNum=@RegNo));
  for f as curs scroll cursor for
    select 
			A.RealLodgingsNum as @Lodgings, 
			Inv.InvoiceDateDt as @InvoiceDateDt
		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 @DatePaidTo=@DatePaidTo+@Lodgings;
    if(@DatePaidTo>=@EndDate) then
      return(@InvoiceDateDt);
		end if;
		if(@DatePaidTo>=@StartDate) and (@DatePaidTo<=@EndDate) then
		  set @PartlyPaidToOnDate=@InvoiceDateDt;
    end if;
	end for;
	return(@PartlyPaidToOnDate);
end

--[04.08.2009] - Yoan - Izchislqva datata, na koqto e platen saotvetniq period.

GO

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

GO

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

GO

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

GO

create procedure DBA.FillOweRentForMonth()
--Spravka Daljim i platen naem
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentForMonth;
	delete from #OwePaidRentMonth;
  while((bvrSpravkaY < bvrSpravkaY2) or((bvrSpravkaY = bvrSpravkaY2) and(bvrSpravkaM <= bvrSpravkaM2))) loop
	  call FillOwePaidRentMonth("date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY),0);
    if(bvrSpravkaM >= 12) then
      set bvrSpravkaM=1;
      set bvrSpravkaY=bvrSpravkaY+1
    else
      set bvrSpravkaM=bvrSpravkaM+1
    end if
  end loop;
	insert into #OwePaidRentForMonth(
	select GuestNo, NameMonth, DaysInMonth, Year, Name, UniName, Kurs, Spec, FN, Address, City, 
																		Sex, PriceKind, Usluga,	Cena,	Kol, SumOwe,	KolPaid, SumPaid, Razlika, 
																		RealDatePaid,	Koef,	1, RoomName,	Country from
        #OwePaidRentMonth);
end
--[07.04.2009] - Nixon - promeneno DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
--[v. 29.07.2009] - Yoan - promeneno da vzema ot vremenna tablica #OwePaidRentMonth

GO

Delete from Relations where lcase(RelationTable) = lcase('Monthly rent information')

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('#OwePaidRentMonth','__OPTIONS__','Дължим и платен наем - месец','__OPTIONS__','Monthly rent information','__OPTIONS',1,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillOwePaidRentMonth("date"(''1.'' || bvrSpravkaM || ''.'' || bvrSpravkaY),1)',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('#OwePaidRentMonth','bvrSpravkaY','Дължим и платен наем - месец','Година','Monthly rent information','Year',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'bvrSpravkaY','','',0,0,1,1,'','select YEAR(today(*))',20,'',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('#OwePaidRentMonth','bvrSpravkaM','Дължим и платен наем - месец','Месец','Monthly rent information','Month',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'bvrSpravkaM','','',0,0,1,1,'','select MONTH(today(*))',20,'',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('#OwePaidRentMonth','Name','Дължим и платен наем - месец','Студент','Monthly rent information','Name',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Name','','',0,1,'','','','',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('#OwePaidRentMonth','UniName','Дължим и платен наем - месец','Университет','Monthly rent information','UniName',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Uni','','',0,0,'','','select FullName from Universities','',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('#OwePaidRentMonth','Spec','Дължим и платен наем - месец','Специалност','Monthly rent information','Spec',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Spec','','',0,0,'','','select Name from Specialities','',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('#OwePaidRentMonth','Kurs','Дължим и платен наем - месец','Курс','Monthly rent information','Kurs',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Kurs','','',0,0,'','','select Name from Kursove','',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('#OwePaidRentMonth','FN','Дължим и платен наем - месец','ФН','Monthly rent information','FN',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'FN','','',0,0,'','','','',50,'',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('#OwePaidRentMonth','City','Дължим и платен наем - месец','Град','Monthly rent information','City',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'City','','',0,0,'','','','',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('#OwePaidRentMonth','Address','Дължим и платен наем - месец','Адрес','Monthly rent information','Address',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Address','','',0,0,'','','','',150,'',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('#OwePaidRentMonth','Country','Дължим и платен наем - месец','Държава','Monthly rent information','Country',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'CountryName','','',0,0,'','','select Description from Countries','',150,'',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('#OwePaidRentMonth','Sex','Дължим и платен наем - месец','Пол','Monthly rent information','Sex',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sex','','',0,0,'','','','',30,'',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('#OwePaidRentMonth','PriceKind','Дължим и платен наем - месец','Ценоразпис','Monthly rent information','PriceKind',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'PriceKind','','',0,0,'','','select Description from PriceKinds','',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('#OwePaidRentMonth','Usluga','Дължим и платен наем - месец','Услуга','Monthly rent information','Usluga',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Usluga','','',0,0,'','','select ServiceName from Services','',250,'',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('#OwePaidRentMonth','Kol','Дължим и платен наем - месец','Количество','Monthly rent information','Kol',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Kol)','Sum(qrData.Количество)','',0,0,'','','','',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('#OwePaidRentMonth','Cena','Дължим и платен наем - месец','Цена','Monthly rent information','Cena',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Cena','','',0,0,'','','','',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('#OwePaidRentMonth','SumOwe','Дължим и платен наем - месец','Дължима Сума','Monthly rent information','SumOwe',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(SumOwe)','Sum(qrData.Дължима Сума)','',0,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('#OwePaidRentMonth','SumPaid','Дължим и платен наем - месец','Платена Сума','Monthly rent information','SumPaid',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(SumPaid)','Sum(qrData.Платена Сума)','',0,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('#OwePaidRentMonth','Razlika','Дължим и платен наем - месец','Разлика','Monthly rent information','Razlika',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Razlika)','Sum(qrData.Разлика)','',0,0,'','','','',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('#OwePaidRentMonth','RoomName','Дължим и платен наем - месец','Стая','Monthly rent information','RoomName',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'RName','','',0,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('#OwePaidRentMonth','Koef','Дължим и платен наем - месец','Коефициент','Monthly rent information','Koef',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Koef)','Sum(qrData.Кофициент)','',0,0,'','','','',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('#OwePaidRentMonth','RealDatePaid','Дължим и платен наем - месец','Дата плащане','Monthly rent information','RealDatePaid',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'RealDatePayed','','',0,0,'','','','',80,'',0,0,1,today())

Go

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

GO

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

GO

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

GO

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

GO

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

GO

Delete from Relations where lcase(RelationTable) = lcase('Free and occupied beds for period')

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('#FreeBedsForPeriod','__OPTIONS__','Свободни и заети стаи за период','__OPTIONS__','Free and occupied beds for period','__OPTIONS',1,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillFreeBedsForPeriod()',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('#FreeBedsForPeriod','bvrData2','Свободни и заети стаи за период','От дата','Free and occupied beds for period','FromDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select today(*)',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('#FreeBedsForPeriod','bvrData1','Свободни и заети стаи за период','До дата','Free and occupied beds for period','ToDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select today(*)',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('#FreeBedsForPeriod','CurrentDate','Свободни и заети стаи за период','Дата','Free and occupied beds for period','CurrentDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'CurrentDate','','',0,1,'','','','',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('#FreeBedsForPeriod','HotelName','Свободни и заети стаи за период','Общежитие','Free and occupied beds for period','HotelName',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'HotelName','','',0,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('#FreeBedsForPeriod','TotalBeds','Свободни и заети стаи за период','Легла','Free and occupied beds for period','TotalBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(TotalBeds)','Sum(qrData.Легла)','',0,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('#FreeBedsForPeriod','Students','Свободни и заети стаи за период','Студенти','Free and occupied beds for period','Students',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(Students)','Sum(qrData.Студенти)','',0,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('#FreeBedsForPeriod','FreeBeds','Свободни и заети стаи за период','Свободни легла','Free and occupied beds for period','FreeBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(FreeBeds)','Sum(qrData.Свободни легла)','',0,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('#FreeBedsForPeriod','Unusable','Свободни и заети стаи за период','Неизползваеми','Free and occupied beds for period','Unusable',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'Unusable','Sum(qrData.Неизползваеми)','',0,0,'','','','',80,'',0,0,1,today())

Go

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

GO