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, -- 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 --Dobrin - where /* /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) 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,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'__Options__','','',0,0,0,0,'Printer.Orientation=1','',80,'',0,0,'',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_DaylyListForPeriod','bvrData1','Дневен лист за период','От дата','Dayly list for period','From Date',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',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_DaylyListForPeriod','bvrData2','Дневен лист за период','До дата','Dayly list for period','From Date',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData1',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_DaylyListForPeriod','GuestNum','Дневен лист за период','Гост','Dayly list for period','GuestNum',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'GuestNum','','',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_DaylyListForPeriod','Name','Дневен лист за период','Име','Dayly list for period','Name',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Name','','',0,0,'','','','',200,'',0,0,1,today(*)) Go INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData) VALUES('v_DaylyListForPeriod','ArriveDate','Дневен лист за период','Дата прист.','Dayly list for period','ArriveDate',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'ArriveDate','','',0,1,'','','','',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_DaylyListForPeriod','LeaveDate','Дневен лист за период','Дата замин.','Dayly list for period','LeaveDate',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'LeaveDate','','',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('v_DaylyListForPeriod','LastRoomName','Дневен лист за период','Стая','Dayly list for period','LastRoom',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'LastRoom','','',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_DaylyListForPeriod','NumNights','Дневен лист за период','Нощувки','Dayly list for period','NumNights',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'NumNights','','',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_DaylyListForPeriod','Price','Дневен лист за период','Цена','Dayly list for period','Price',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Price','','# ### ##0.00',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_DaylyListForPeriod','Suma','Дневен лист за период','Платено','Dayly list for period','Suma',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(Suma)','Sum(qrData.Платено)','# ### ##0.00',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_DaylyListForPeriod','Balance','Дневен лист за период','Баланс','Dayly list for period','Balance',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Sum(Balance)','Sum(qrData.Баланс)','# ### ##0.00',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_DaylyListForPeriod','Passport','Дневен лист за период','Паспорт','Dayly list for period','Passport',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Passport','','',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('v_DaylyListForPeriod','EGN','Дневен лист за период','ЕГН','Dayly list for period','EGN',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'EGN','','',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('v_DaylyListForPeriod','Country','Дневен лист за период','Държава','Dayly list for period','Country',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Country','','',0,0,'','','select Description from Countries where Active=1','',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_DaylyListForPeriod','AddressData','Дневен лист за период','Местожителство','Dayly list for period','AddressData',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'AddressData','','',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('v_DaylyListForPeriod','ContractName','Дневен лист за период','Договор','Dayly list for period','ContractName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'ContractName','','',0,0,'','','select FullName from Contracts 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_DaylyListForPeriod','Admin','Дневен лист за период','Потребител','Dayly list for period','Admin',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Admin','','',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('v_DaylyListForPeriod','UName','Дневен лист за период','Университет','Dayly list for period','UName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'UName','','',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('v_DaylyListForPeriod','SName','Дневен лист за период','Специалност','Dayly list for period','SName',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'SName','','',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('v_DaylyListForPeriod','Kurs','Дневен лист за период','Курс','Dayly list for period','Kurs',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'Kurs','','',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_DaylyListForPeriod','FN','Дневен лист за период','Факултетен номер','Dayly list for period','FN',2,GetFreeNInRelations('Dayly list for period', Null, Null, 10),'FN','','',0,0,'','','','',110,'',0,0,1,today(*)) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_DaylyListForPeriod'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_DaylyListForPeriod'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; 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=PK.Description,//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 - -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008 else((-1)*ART.Qty) endif endif endif, EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS = 2 then 1 else 0 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=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.DDSType = INV.DDSType 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=Services.ServiceName, Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif, EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 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)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif))),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.DDSType = INV.DDSType 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) GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegServicesPeriod'))) THEN Drop view v_RegServicesPeriod END IF GO create view DBA.v_RegServicesPeriod as select RegDate=DateCreditDt, /*Maria +, 1728*/ RealDateTime=ServiceCredits.RealDateTime, /*Maria -*/ GuestName=Guests.Name, Account=Guests.GuestNum, --Maria --RoomNo=v_ArriveStayLeaveGuestBook.LastRoom, RoomNo=v_ArriveStayLeaveGuestBook.LastRoomName, --Maria Service=Services.ServiceName || ' в стая ' || B.Room,//Dobrin 2195 - Promqna na imeto na uslugata ServiceNo=Services.ServiceNo, ServicesNum=ServiceCredits.NumberServices, /*ServiceSngPrice=if ServiceCredits.KodCurrency=2 then DayRates.ExchangeRate*ServiceCredits.SumCredit else ServiceCredits.SumCredit endif, */ ServiceSngPrice=(if(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1) then(select SpaCurrencies.ExchangeRate from DBA.SpaCurrencies where ServiceCredits.KodCurrency = SpaCurrencies.KodCurrency) else Currencies.ExchangeRate endif)*ServiceCredits.SumCredit, AllSum=ServiceCredits.NumberServices*ServiceSngPrice, Plateni=ServiceCredits.PayedServices*ServiceSngPrice, Dogovor=Contracts.ShortName, Admin=Admin.ShortName, ShowService=if Services.IsMonthlyBill=1 and Plateni=AllSum then 0 else 1 endif//Dobrin 2195 - ako e komunalna i e platena da ne se pokazva from DBA.ServiceCredits left outer join DBA.Admin on ServiceCredits.KodAdmin = Admin.AdminNo, DBA.Services, DBA.Guests, DBA.Currencies, DBA.v_ArriveStayLeaveGuestBook, DBA.Contracts, DBA.Book B//Dobrin 2195 where ShowService=1 and B.Guest=Guests.GuestNum and//Dobrin 2195 ServiceCredits.Service = Services.ServiceNo and ServiceCredits.KodCurrency = Currencies.KodCurrency and ServiceCredits.DateCreditDt >= bvrData1 and ServiceCredits.DateCreditDt <= bvrData2 and ServiceCredits.NumberServices <> 0 and Guests.Contract = Contracts.ContractNo and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and ServiceCredits.GuestNo = Guests.GuestNum and v_ArriveStayLeaveGuestBook.GuestNum = Guests.GuestNum and( (ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1)) GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetMonthsForBook'))) THEN Drop function GetMonthsForBook END IF GO create function GetMonthsForBook(in @RegNo integer,in @Tip integer)//1-plateno kolischestvo,0-cqlo kolichestvo returns float begin declare @rez float; declare @dateB date; declare @dateE date; declare @GNo integer; if @Tip=1 then select FloatToDate(DateReg),FloatToDate(DateReg+PayedLodgings),Guest into @dateB,@dateE,@GNo from Book where RegNum=@RegNo else select FloatToDate(DateReg),FloatToDate(DateReg+NumLodgings),Guest into @dateB,@dateE,@GNo from Book where RegNum=@RegNo end if; set @rez=0.0; while ((MONTH(@dateE)+1<>MONTH(@dateB)) AND (MONTH(@dateE)<>MONTH(@dateB) AND (DAY(@dateB)<>1))) loop set bvrSpravkaM=MONTH(@dateB); set bvrSpravkaY=YEAR(@dateB); set @rez=@rez+GetQtyArtForMonth(@GNo,@Tip); set @dateB = dateadd(month,1,@dateB); end loop; set @rez=Round(@rez,2); return(@rez); end GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegBaseServicesPeriod'))) THEN Drop view v_RegBaseServicesPeriod END IF GO create view DBA.v_RegBaseServicesPeriod( RegDate,RealDateTime, GuestName, Account, RoomNo, Service, ServicesNum, ServiceSngPrice, AllSum, Dogovor, Plateni) as select RegDate=DateRegDt,RealDateTime=RealDateTime, GuestName=Guests.Name, Account=BK.Guest, --Maria --RoomNo=BK.Room, RoomNo=R.RoomName,Service='Нощувка и застраховка', --Maria ServicesNum=GetMonthsForBook(BK.RegNum,0),//DateIntersection(bvrData1,bvrData2,DateRegDt,Days(DateRegDt,NumLodgings)) Dobrin 2195 ServiceSngPrice=Currencies.ExchangeRate*BK.Price, AllSum=ServicesNum*ServiceSngPrice, Dogovor=Contracts.ShortName, -- Plateni=null Plateni=if PK.NachinPlat=0 then BK.PayedLodgings*ServiceSngPrice else GetMonthsForBook(BK.RegNum,1)*ServiceSngPrice endif from//Dobrin 2195 - promqna na nachina na izchislenie na platenite PriceKinds as PK,//Dobrin 2195 --Maria DBA.Rooms as R, --Maria DBA.Book as BK,dba.Guests,DBA.Currencies,DBA.Contracts where PK.PriceKind=BK.PriceKind and//Dobrin 2195 --Maria BK.Room = R.RoomNum and --Maria Guests.GuestNum = BK.Guest and Guests.Contract = Contracts.ContractNo and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and BK.KodCurrency = Currencies.KodCurrency and Guests.Status <> 0 and ServicesNum <> 0 // 04.August.2006 - Yanko - променено Plateni, за да се показва в Регистрирани услуги // 21.December.2007 - Maria - добавена колоната RealDateTime // 12.January.2009 - Dobrin - интегрирано за месечните наеми да не взима брой платени нощувки, а бр платени месеци GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetAllPKComma'))) THEN Drop function GetAllPKComma END IF GO create function GetAllPKComma() returns text begin declare @rez text; set @rez=''; for f1 as curs scroll cursor for select PriceKind,Description from PriceKinds where PriceKind>1 order by pricekind asc do set @rez=@rez || '"C' || PriceKind || '" integer,'; end for; return(@rez); end Go IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetNumBookForPKDay'))) THEN Drop function GetNumBookForPKDay END IF GO create function GetNumBookForPKDay(in @D date, @PK integer) returns integer begin declare @rez integer; set @rez=(select count(*) from Book where PriceKind=@PK and @D>=FloatToDate(Datereg) and @D<=FloatToDate(Datereg+NumLodgings-1)); return(@rez); end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillRLegloDni'))) THEN Drop procedure FillRLegloDni END IF GO create procedure FillRLegloDni() begin declare @sqlTemp text; declare @tmpSum integer; declare @tmpD1 date; declare @tmpD2 date; execute('delete from #RLegloDni'); set @tmpD1=bvrData1; set @tmpD2=bvrData2; if bvrData1<=bvrData2 then while (bvrData1<=bvrData2) loop set @tmpSum=0; set @sqlTemp = 'insert into #RLegloDni Values('''||DateFormat(bvrData1,'yyyy.mm.dd')||''','; for f3 as curs3 scroll cursor for select PriceKind from PriceKinds where PriceKind>1 order by PriceKind ASC do set @tmpSum=@tmpSum+GetNumBookForPKDay(bvrData1,PriceKind); set @sqlTemp=@sqlTemp||GetNumBookForPKDay(bvrData1,PriceKind)||','; end for; set @sqlTemp=@sqlTemp||@tmpSum||',1.10,'||1.1*@tmpSum||')'; execute(@sqlTemp); set bvrData1=DateAdd(Day,1,bvrData1); end loop; end if; set bvrData1=@tmpD1; set bvrData2=@tmpD2; end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('PrepareSpravkaRLegloDni'))) THEN Drop procedure PrepareSpravkaRLegloDni END IF GO CREATE Procedure PrepareSpravkaRLegloDni() begin Delete from Relations where lcase(TableName) = lcase('#RLegloDni'); 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('#RLegloDni','__OPTIONS__','Реализирани леглодни','__OPTIONS__','Beddays used','__OPTIONS',1,GetFreeNInRelations('Beddays used', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,today(*)); 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('#RLegloDni','bvrData1','Реализирани леглодни','От дата','Beddays used','From Date',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',0,0,1,today(*)); 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('#RLegloDni','bvrData2','Реализирани леглодни','До дата','Beddays used','From Date',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData1',72,'',0,0,1,today(*)); 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('#RLegloDni','Den','Реализирани леглодни','Ден','Beddays used','Den',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'Den','Sum(qrData.Den)','',0,0,'','','','',100,'',0,0,1,today(*)); for f1 as curs1 scroll cursor for select PriceKind,Description from PriceKinds where PriceKind>1 order by pricekind asc do 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('#RLegloDni','C'||PriceKind,'Реализирани леглодни',Description,'Beddays used','C'||PriceKind,2,GetFreeNInRelations('Beddays used', Null, Null, 10),'C'||PriceKind,'Sum(qrData.C'||PriceKind||')','',0,0,'','','','',150,'',0,0,1,today(*)); end for; 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('#RLegloDni','Total','Реализирани леглодни','Общо','Beddays used','Total',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'Total','Sum(qrData.Total)','',0,0,'','','','',80,'',0,0,1,today(*)); 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('#RLegloDni','Normativ','Реализирани леглодни','Норматив','Beddays used','Normativ',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'Sum(qrData.Normativ)','','',0,0,'','','','',120,'',0,0,1,today(*)); 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('#RLegloDni','TotalNorm','Реализирани леглодни','Общо норматив','Beddays used','TotalNorm',2,GetFreeNInRelations('Beddays used', Null, Null, 10),'Sum(qrData.TotalNorm)','','',0,0,'','','','',150,'',0,0,1,today(*)); delete from InformPermission where RelationN=( select N from Relations where TableName='#RLegloDni'and ColName='__OPTIONS__'); for f2 as curs2 scroll cursor for select N as grNo 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='#RLegloDni'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; call FillRLegloDni(); end; Go Delete from Relations where lcase(TableName) = lcase('#RLegloDni'); 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('#RLegloDni','__OPTIONS__','Реализирани леглодни','__OPTIONS__','Beddays used','__OPTIONS',1,GetFreeNInRelations('Beddays used', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,today(*)); Go for f1 as curs scroll cursor for select N as grNo 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='#RLegloDni'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; 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); 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.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('CheckGuestBalanceForMonth'))) THEN Drop function CheckGuestBalanceForMonth END IF GO create function CheckGuestBalanceForMonth(in @GNo integer) returns tinyint begin declare @rez tinyint; declare @DateA date; declare @DateL date; select top 1 FloatToDate(DateReg), FloatToDate(DateReg+NumLodgings) into @DateA, @DateL from Book where Guest=@GNo order by RegNum DESC; if ((bvrSpravkaY-YEAR(@DateA))*12 + bvrSpravkaM-MONTH(@DateA))>=0 AND ((YEAR(@DateL)-bvrSpravkaY)*12+MONTH(@DateL)-bvrSpravkaM>=0) then set @rez=1 else set @rez=0 end if; return(@rez); end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetQtyArtForMonth'))) THEN Drop function GetQtyArtForMonth END IF GO create function GetQtyArtForMonth(in @GNo integer,in @Tip tinyint)//@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo returns float begin declare @rez float; declare @DateA date; declare @DateL date; declare @DateP date; select top 1 FloatToDate(DateReg), FloatToDate(DateReg+NumLodgings), FloatToDate(DateReg+PayedLodgings) into @DateA, @DateL, @DateP from Book where Guest=@GNo order by RegNum DESC; if MONTH(@DateA)=bvrSpravkaM AND YEAR(@DateA)=bvrSpravkaY then set @rez=cast(DOM(bvrSpravkaM,bvrSpravkaY)-DAY(@DateA)+1 as float)/DOM(bvrSpravkaM,bvrSpravkaY); else if MONTH(@DateL)=bvrSpravkaM AND YEAR(@DateL)=bvrSpravkaY then set @rez=cast(DAY(@DateL)-1 as float)/DOM(bvrSpravkaM,bvrSpravkaY); else set @rez=CheckGuestBalanceForMonth(@GNo); end if end if; if DAY(@DateL)@DateP) then if (year(@DateP) < bvrSpravkaY) then set @rez=0; else if (year(@DateP) = bvrSpravkaY)then if (month(@DateP) < bvrSpravkaM) then set @rez = 0; else if (month(@DateP) = bvrSpravkaM) and (@DateL <> @DateP) then set @rez = 0; end if; end if; end if; end if; end if; end if; end if; return(@rez); end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetNoshtuvkaNameForMonth'))) THEN Drop function GetNoshtuvkaNameForMonth END IF GO create function GetNoshtuvkaNameForMonth(in @GNo integer,in @IsLodg integer default 0)//@IsLodg 0-uslugata e za mesec,1-uslugata e za noshtuvka returns varchar(60) begin declare @rez varchar(60); declare @ServiceName varchar(40); declare @RoomName varchar(6); declare @NeplDni varchar(6); declare @KoefNeplDni real; set @KoefNeplDni = GetQtyArtForMonth(@GNo,0); if @KoefNeplDni<>1 then//ne e za celiq mesec set @NeplDni=' ' || Round(@KoefNeplDni*DOM(bvrSpravkaM,bvrSpravkaY),0) || '/' || DOM(bvrSpravkaM,bvrSpravkaY); end if; select top 1 ServiceName into @ServiceName from Services S, PriceKinds PK, Book B where B.Guest=@GNo and B.PriceKind=PK.PriceKind and PK.ServiceNo=S.ServiceNo order by RegNum DESC; select top 1 Room into @RoomName from Rooms join Book on Rooms.RoomNum=Book.Room where Book.Guest=@GNo order by RegNum DESC; if @IsLodg=1 then if (Select top 1 isrest from Book where Guest=@GNo order by RegNum DESC)=1 then set @ServiceName='Почивка ' else set @ServiceName='Нощувка ' end if end if; if @IsLodg=0 then set @rez= @ServiceName || ' ' || ImeMesec(bvrSpravkaM) || ' в стая ' || @RoomName || @NeplDni else set @rez= @ServiceName || 'в стая ' || @RoomName; end if; return(@rez); end GO IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_OwePaidLodgMonth'))) THEN Drop View v_OwePaidLodgMonth END IF GO create view v_OwePaidLodgMonth as select Guest as GNo, Month=ImeMesec(bvrSpravkaM), Year=bvrSpravkaY, G.Name, Uni=U.FullName, Kurs=K.Name, Spec=S.Name, G.FN, G.Address, City=(select SettleMentName from Settlements where N = G.SettlementN), G.Sex, PriceKind=PK.Description, Usluga=GetNoshtuvkaNameForMonth(GNo,1), Cena=B.Price*C.ExchangeRate, Kol=B.NumLodgings, SumOwe=Round(Kol*Cena,2), KolPaid=B.PayedLodgings, SumPaid=Round(KolPaid*Cena,2), Razlika=SumOwe-SumPaid, BrStudenti=1, RName=R.RoomName from Book B, PriceKinds PK, Guests G, Kursove K, Specialities S, Universities U, Currencies C, Rooms R where B.PriceKind=PK.PriceKind AND G.GuestNum=B.Guest AND PK.NachinPlat=0 AND CheckGuestBalanceForMonth(Guest)=1 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 B.RegNum=(Select MAX(RegNum) from Book join PriceKinds PK on Book.PriceKind=PK.PriceKind where Guest=Gno and PK.NachinPlat=0) GO Delete from Relations where lcase(TableName) = lcase('v_OwePaidLodgMonth') 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_OwePaidLodgMonth','__OPTIONS__','Дължими и платени нощувки - месец','__OPTIONS__','Monthly lodgings information','__OPTIONS',1,GetFreeNInRelations('Monthly lodgings information', 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_OwePaidLodgMonth','bvrSpravkaY','Дължими и платени нощувки - месец','Година','Monthly lodgings information','Year',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','bvrSpravkaM','Дължими и платени нощувки - месец','Месец','Monthly lodgings information','Month',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','GNo','Дължими и платени нощувки - месец','Клиентски номер','Monthly lodgings information','GNo',2,GetFreeNInRelations('Monthly lodgings information', Null, Null, 10),'GNo','','',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('v_OwePaidLodgMonth','Name','Дължими и платени нощувки - месец','Студент','Monthly lodgings information','Name',2,GetFreeNInRelations('Monthly lodgings information', Null, Null, 10),'Name','','',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('v_OwePaidLodgMonth','Uni','Дължими и платени нощувки - месец','Университет','Monthly lodgings information','Uni',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Spec','Дължими и платени нощувки - месец','Специалност','Monthly lodgings information','Spec',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Kurs','Дължими и платени нощувки - месец','Курс','Monthly lodgings information','Kurs',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','FN','Дължими и платени нощувки - месец','ФН','Monthly lodgings information','FN',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','City','Дължими и платени нощувки - месец','Град','Monthly lodgings information','City',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Address','Дължими и платени нощувки - месец','Адрес','Monthly lodgings information','Address',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Sex','Дължими и платени нощувки - месец','Пол','Monthly lodgings information','Sex',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','PriceKind','Дължими и платени нощувки - месец','Ценоразпис','Monthly lodgings information','PriceKind',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Usluga','Дължими и платени нощувки - месец','Услуга','Monthly lodgings information','Usluga',2,GetFreeNInRelations('Monthly lodgings information', Null, Null, 10),'Usluga','','',0,0,'','','select ServiceName from Services','',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_OwePaidLodgMonth','Kol','Дължими и платени нощувки - месец','Количество','Monthly lodgings information','Kol',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Cena','Дължими и платени нощувки - месец','Цена','Monthly lodgings information','Cena',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','SumOwe','Дължими и платени нощувки - месец','Дължима Сума','Monthly lodgings information','SumOwe',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','SumPaid','Дължими и платени нощувки - месец','Платена Сума','Monthly lodgings information','SumPaid',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','Razlika','Дължими и платени нощувки - месец','Разлика','Monthly lodgings information','Razlika',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','RName','Дължими и платени нощувки - месец','Стая','Monthly lodgings information','RName',2,GetFreeNInRelations('Monthly lodgings 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('v_OwePaidLodgMonth','BrStudenti','Дължими и платени нощувки - месец','Брой Студенти','Monthly lodgings information','BrStudenti',2,GetFreeNInRelations('Monthly lodgings information', Null, Null, 10),'Sum(BrStudenti)','Sum(qrData.Брой Студенти)','',0,0,'','','','',90,'',0,0,1,today(*)) go delete from InformPermission where RelationN=( select N from Relations where TableName='v_OwePaidLodgMonth'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_OwePaidLodgMonth'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; go IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OwePaidRentMonth'))) THEN Drop view v_OwePaidRentMonth END IF GO create view DBA.v_OwePaidRentMonth as select GNo=Guest, Month=DBA.ImeMesec(bvrSpravkaM), Year=bvrSpravkaY, G.Name, Uni=U.FullName, Kurs=K.Name, Spec=S.Name, G.FN, G.Address, City=(select SettleMentName from Settlements where N = G.SettlementN), G.Sex, PriceKind=PK.Description, Usluga=DBA.GetNoshtuvkaNameForMonth(GNo), Cena=B.Price*C.ExchangeRate, Kol=Round(DBA.GetQtyArtForMonth(GNo,0),2), SumOwe=Round(DBA.GetQtyArtForMonth(GNo,0)*Cena,2), KolPaid=Round(DBA.GetQtyArtForMonth(GNo,1),2), SumPaid=Round(DBA.GetQtyArtForMonth(GNo,1)*Cena,2), Razlika=SumOwe-SumPaid, Koef=Kol, BrStudenti=1, RName=R.RoomName, CountryName=Ctr.Description from DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S, DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R, DBA.Countries as Ctr where B.PriceKind = PK.PriceKind and G.GuestNum = B.Guest and PK.NachinPlat = 1 and DBA.CheckGuestBalanceForMonth(Guest) = 1 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 RegNum=(select MAX(RegNum) from Book where Book.Guest=GNo) and G.NativeCountry=Ctr.CountryKod Go Delete from Relations where lcase(TableName) = lcase('v_OwePaidRentMonth') 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_OwePaidRentMonth','__OPTIONS__','Дължим и платен наем - месец','__OPTIONS__','Monthly rent information','__OPTIONS',1,GetFreeNInRelations('Monthly rent information', 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_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('v_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('v_OwePaidRentMonth','Name','Дължим и платен наем - месец','Студент','Monthly rent information','Name',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Name','','',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('v_OwePaidRentMonth','Uni','Дължим и платен наем - месец','Университет','Monthly rent information','Uni',2,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('v_OwePaidRentMonth','Spec','Дължим и платен наем - месец','Специалност','Monthly rent information','Spec',2,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('v_OwePaidRentMonth','Kurs','Дължим и платен наем - месец','Курс','Monthly rent information','Kurs',2,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('v_OwePaidRentMonth','FN','Дължим и платен наем - месец','ФН','Monthly rent information','FN',2,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('v_OwePaidRentMonth','City','Дължим и платен наем - месец','Град','Monthly rent information','City',2,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('v_OwePaidRentMonth','Address','Дължим и платен наем - месец','Адрес','Monthly rent information','Address',2,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('v_OwePaidRentMonth','CountryName','Дължим и платен наем - месец','Държава','Monthly rent information','Country',2,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('v_OwePaidRentMonth','Sex','Дължим и платен наем - месец','Пол','Monthly rent information','Sex',2,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('v_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('v_OwePaidRentMonth','Usluga','Дължим и платен наем - месец','Услуга','Monthly rent information','Usluga',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Usluga','','',0,0,'','','select ServiceName from Services','',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_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('v_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('v_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('v_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('v_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('v_OwePaidRentMonth','RName','Дължим и платен наем - месец','Стая','Monthly rent information','RName',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('v_OwePaidRentMonth','BrStudenti','Дължим и платен наем - месец','Брой Студенти','Monthly rent information','BrStudenti',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(brStudenti)','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('v_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 delete from InformPermission where RelationN=( select N from Relations where TableName='v_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='v_OwePaidRentMonth'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; go if (exists(select RegisterName from Registers where RegisterName='AddressCardBulgaria')) then delete from Registers where RegisterName='AddressCardBulgaria' end if go insert into Registers (RegisterName, IntStojnost, Komentar) values('AddressCardBulgaria',1,'Reports=AllBGCards.qri'||Char(13)||Char(10)||'ReportBr=2'||Char(13)||Char(10)||'Report1=Karton1'||Char(13)||Char(10)||'Report2=Karton2'||Char(13)||Char(10)||'Variables+='||Char(13)||Char(10)||'Karton1Osnovanie=Настанителна заповед'||Char(13)||Char(10)||'Variables-='); if (exists(select RegisterName from Registers where RegisterName='AddressCardForeign')) then delete from Registers where RegisterName='AddressCardForeign' end if go insert into Registers (RegisterName, IntStojnost, Komentar) values('AddressCardForeign',1,'Reports=AllBGCards.qri'||Char(13)||Char(10)||'ReportBr=2'||Char(13)||Char(10)||'Report1=Karton1'||Char(13)||Char(10)||'Report2=Karton2'||Char(13)||Char(10)||'Variables+='||Char(13)||Char(10)||'Karton1Osnovanie=Настанителна заповед'||Char(13)||Char(10)||'Variables-='); go