------------------------------------------------------Smetki----------------------------------------------------------------------------------------- --[1] - Add column RoomName to BulSystAccess if (select count(*) from sys.syscolumns where tname='BulSystAccess' and cname='RoomName')=0 then alter table BulSystAccess Add RoomName varchar(6) end if; GO update BulSystAccess set RoomName=(select RoomName from Rooms where RoomNum=RoomN) GO --[2] - PrepareGuestBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareGuestBill')))THEN Drop procedure PrepareGuestBill END IF GO create procedure DBA.PrepareGuestBill(in @place varchar(50)) result(@Disc integer) begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc double; // declare @DateCr date; delete from #Articles; select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; for g as curs1 scroll cursor for select GuestNo as @GuestNo from #GuestsForBill do for f as curs scroll cursor for select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest, B.Room as @Room,/*M*/R.RoomName as @RoomName/*M*/,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency, IsRest as @IsRest,B.DateRegDt as @RDate from Book as B left outer join Rooms as R on B.Room=R.RoomNum/*,Rooms as R*/ where B.WhoPays = @GuestNo and(B.NumLodgings-B.PayedLodgings) <> 0 /*and B.Room=R.RoomNum*/ do set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest, @ArticleNumber,@place,@RDate) end for end for; for gg as curs11 scroll cursor for select GuestNo as @GuestNo from #GuestsForBill do select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @GuestNo; for ff as cursf scroll cursor for select SC.CreditNo as @CreditNo, S.ServiceName as @ArticleName, SC.RoomNum as @Room,/*M*/R.RoomName as @RoomName /*M*/, (SC.SumCredit*C.ExchangeRate) as @Price, SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty, VS.TipDDS as @TipDDS, SC.DateCreditDt as @DateCr, SC.AutomaticService as @AutomaticService from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum=R.RoomNum,Services as S,VidSdelki as VS,Currencies as C/*,Rooms as R*/ where SC.WhoPays = @GuestNo and (NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency /*and SC.RoomNum=R.RoomNum*/ and ((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) do if(@Room is not null) then if @AutomaticService = 1 then --Maria --set @ArticleName='* ' || @ArticleName || @place || cast(@Room as varchar) set @ArticleName='* ' || @ArticleName || @place || @RoomName else --Maria --set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName end if else set @ArticleName=@ArticleName end if; // if(@Disc<>0) then // set @Price=CalcPriceWithDiscount(@Disc,@Price) // end if // call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr); set @ArticleNumber=@ArticleNumber+1 end for end for; set @Deposit=0; for gk as cursgk scroll cursor for select GuestNo as @GuestNo from #GuestsForBill do set @Deposit=@Deposit+CalcDepositSum(@GuestNo)/(1+bvrDDSPart) end for; if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1005,null) end if end //[v 18.02.2003] Jorko GO --[3] - FillLodgingsForBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FillLodgingsForBill'))) THEN Drop procedure FillLodgingsForBill END IF GO create function DBA.FillLodgingsForBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50),in @RRDate date) returns integer // Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles // na noshtuvka(pochivka) i uslugite ot paketa, za opredelen zapis ot Book // Tia se vika ot PrepareGuestBill, PrepareGroupBill i PrepareReserveBill begin declare @ArticleName varchar(40); declare @ArticlePrice double; declare @ArticlePrWithDDS double; //nt declare @PackagePrice double; declare @PackagePrWithDDS double; //nt declare @TipDDS integer; declare @ServiceNoLodging integer; declare @I integer; declare @Disc double; --Maria declare @RoomName varchar(5); --Maria select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Book.RegNum = @RegNo; //nt select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum; select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency; set @ArticlePrWithDDS=@ArticlePrice; //nt select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice from Packages,Currencies,Services,VidSdelki where Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N; if @PackagePrice is null then set @PackagePrice=0; set @PackagePrWithDDS=0 else set @PackagePrWithDDS=@PackagePrice end if; select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N; if @TipDDS = 2 then set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata set @TipDDS=1 end if; set @ArticlePrice=@ArticlePrice-@PackagePrice; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata set @TipDDS=2 end if; --Maria set @RoomName=(select RoomName from Rooms where RoomNum=@Room); //if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm') if @IsRest = 1 then set @ArticleName='Почивка ' || @place || @RoomName +' за '+dateformat(@DateReg,'Dd.Mm') --Maria else --Maria //set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') set @ArticleName='Нощувка ' || @place || @RoomName +' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') --Maria end if; set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'Бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double), ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ; set @I=1; for f as curs scroll cursor for select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency, Services.ServiceName as @ServiceName from Packages,Services where RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency; select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Packages.RegNo = Book.RegNum and Book.RegNum = @RegNo; //nt select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata set @TipDDS=2 end if; set @ArticlePrWithDDS=@ArticlePrice; //nt --Maria +, dopulniteln uslugi set @ServiceName=@ServiceName || @place || cast(@Room as varchar(5)); --Maria - insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'Бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ; set @I=@I+1; set @Disc=0 end for; return(@I) end GO --[4] - FillLodgingsForContractBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FillLodgingsForContractBill'))) THEN Drop procedure FillLodgingsForContractBill END IF GO create function DBA.FillLodgingsForContractBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50)) returns integer begin declare @ArticleName varchar(40); declare @ArticlePrice double; declare @PackagePrice double; declare @PackagePrWithDDS double; //nt declare @TipDDS integer; declare @ServiceNoLodging integer; declare @I integer; declare @Disc double; //nt declare @ArticlePrWithDDS double; //nt declare @IsForAlServices integer; declare @ContractNo integer; --Maria declare @RoomName varchar(5); --Maria //Proverqva dali ima otstypka po dogovora i dali tazi otstypka e za vsi4ki services select C.IsDiscForAll,C.Discount,c.ContractNo into @IsForAlServices, @Disc, @ContractNo from Contracts as C,Book as B where B.ContractPays = C.ContractNo and B.RegNum = @Regno; if(@IsForAlServices = 0) then set @Disc=0 end if; //@ServiceNoLodging e N-na na noshtuvka ili pochivka syotvetno za nashenec ili chujdenec select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum; //Izchislenie cenata na noshtuvkata v leva syobrazno otstypkata po dogovora ako ima otstypka select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency; set @ArticlePrWithDDS=@ArticlePrice; //Podvajda sumata na uslugite ot paketa ot select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice from Packages,Currencies,Services,VidSdelki where Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N; if @PackagePrice is null then set @PackagePrice=0; set @PackagePrWithDDS=0 //nt else set @PackagePrWithDDS=@PackagePrice //nt end if; //Izchisliava se tipa DDS za noshtuvkata select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N; if @TipDDS = 2 then set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata set @TipDDS=1 end if; set @ArticlePrice=@ArticlePrice-@PackagePrice; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata set @TipDDS=2 end if; --Maria set @RoomName=(select RoomName from Rooms where RoomNum=@Room); --if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm') if @IsRest = 1 then set @ArticleName='Почивка ' || @place || @RoomName +' за '+dateformat(@DateReg,'Dd.Mm') --Maria else --Maria -- set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') set @ArticleName='Нощувка ' || @place || @RoomName+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') --Maria end if; if(@Disc = 0) then select srv.Discount into @Disc from ServicesForContract as srv,Book as b where b.ContractPays = srv.ContractNo and b.RegNum = @RegNo and srv.ServiceNo = @ServiceNoLodging end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice) set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt //Insertva noshtuvka ili pochivka insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double), ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ; set @I=1; set @Disc=0; // if(@IsForAlServices=0) then set @Disc=0 // end if // ; //Insertva i uslugite ot paketa for f as curs scroll cursor for select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency, Services.ServiceName as @ServiceName from Packages,Services where RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency; //Izchisliava se tipa DDS za uslugata // select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract=Contracts.ContractNo and Guests.GuestNum=Book.Guest and Packages.RegNo=Book.RegNum and Book.RegNum=@RegNo; //nt select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata set @TipDDS=2 end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice); if(@Disc = 0) then select Discount into @Disc from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @ServiceNo end if; // set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice); //Insertva uslugata vuv #Articles insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ; set @I=@I+1; set @Disc=0 end for; return(@I) end GO --[5] - PrepareContractBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareContractBill'))) THEN Drop procedure PrepareContractBill END IF GO create procedure DBA.PrepareContractBill(in @ContractNo integer,in @place varchar(50)) // Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles // sus noshtuvkite,pochivkite i uslugite koito triabva da plashtat gostite nastaneni po daden dogovor begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc decimal(12,2); declare @PrWithDisc decimal(12,2); declare @IsForAlServices integer; declare @HumanKind integer; delete from #Articles; select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; //Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor select C.IsDiscForAll,C.Discount into @IsForAlServices,@Disc from Contracts as C where C.ContractNo = @ContractNo; if(@IsForAlServices = 0) then set @Disc=0 end if; for f2 as curs2 scroll cursor for select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest, B.Room as @Room,/*M*/R.RoomName as @RoomName/*M*/,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,IsRest as @IsRest from Book as B left outer join Rooms as R on B.Room=R.RoomNum/*,Rooms as R*/ where B.ContractPays = @ContractNo and(B.NumLodgings-B.PayedLodgings) <> 0 do //select(if HumanKind=1 then-1 else-2 endif) into @HumanKind from Guests where GuestNum=@Guest; // if(@Disc=0) then // select Discount into @Disc from ServicesForContract where ServiceNo=@HumanKind and ContractNo=@ContractNo; // set @Price=CalcPriceWithDiscount(@Disc,@Price) // end if // ; // message 'Cenata s otstupkata - >'+cast(@Price as varchar); //set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for; set @ArticleNumber=@ArticleNumber+FillLodgingsForContractBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for; // if(@IsForAlServices=0) then set @Disc=0 // end if //Naliva uslugi po dogovor if(@IsForAlServices = 0) then set @Disc=0 end if; for f1 as curs1 scroll cursor for select SC.CreditNo as @CreditNo, S.ServiceName as @ArticleName,SC.RoomNum as @Room,/*М*/R.RoomName as @RoomName,/*M*/ (SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS, SC.DateCreditDt as @DateCr from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum = R.RoomNum,Services as S,VidSdelki as VS,Currencies as C/*,Rooms as R*/ where SC.ContractPays = @ContractNo and(NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency /*and SC.RoomNum = R.RoomNum*/ do if(@Room is not null) then --Maria --set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName else --Maria set @ArticleName=@ArticleName end if; if(@Disc = 0) then select Discount into @Disc from ServicesForContract where ServiceNo = @Service and ContractNo = @ContractNo; message 'Otstypka - >'+cast(@Disc as varchar) type info to console; //set @Price=CalcPriceWithDiscount(@Disc,@Price); message 'Cenata s otstupkata - >'+cast(@Price as varchar) type info to console end if; call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr); set @Disc=0; set @ArticleNumber=@ArticleNumber+1 end for; set @Deposit=0; set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo)/(1+bvrDDSPart); if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозити(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1004,today(*)) end if end //[v 18.02.2003] Jorko //[v 18.07.2003] Jorko Pribavia data na uslugata 1004 GO --[6] - PrepareReserveBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareReserveBill'))) THEN Drop procedure PrepareReserveBill END IF GO create procedure DBA.PrepareReserveBill(in @ReserveNo integer,in @place varchar(50)) begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc double; delete from #Articles; select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; for f0 as curs0 scroll cursor for select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,B.WhoPays as @WhoPays, B.Room as @Room,/*M*/R.RoomName as @RoomName/*M*/,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency, B.IsRest as @IsRest,B.DateRegDt as @RDate from Book as B left outer join Rooms as R on B.Room=R.RoomNum,Guests/*,Rooms as R*/ where (B.NumLodgings-B.PayedLodgings) <> 0 and B.WhoPays = Guests.GuestNum and Guests.Reservation = @ReserveNo /*and B.Room=R.RoomNum*/ do set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest, @ArticleNumber,@place,@RDate) end for; for f1 as curs1 scroll cursor for select SC.CreditNo as @CreditNo, S.ServiceName as @ArticleName, SC.RoomNum as @Room, /*M*/R.RoomName as @RoomName,/*M*/ (SC.SumCredit*C.ExchangeRate) as @Price, SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty, VS.TipDDS as @TipDDS, SC.WhoPays as @WhoPays, SC.DateCreditDt as @DateCr from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum=R.RoomNum,Services as S,VidSdelki as VS,Currencies as C,Guests/*,Rooms as R*/ where (NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency and SC.WhoPays = Guests.GuestNum and Guests.Reservation = @ReserveNo /*and SC.RoomNum=R.RoomNum*/do if(@Room is not null) then --Maria //set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName --Maria else set @ArticleName=@ArticleName end if; select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @WhoPays; /* if(@Disc<>0) then set @Price=CalcPriceWithDiscount(@Disc,@Price) end if ; */ call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr); set @ArticleNumber=@ArticleNumber+1 end for; select sum(ResMoney.Sum*Currencies.ExchangeRate) into @Deposit from ResMoney left outer join Guests on ResMoney.GuestNo = Guests.GuestNum,Currencies where ResMoney.Status = 2 and ResMoney.Currency = Currencies.KodCurrency and (ResMoney.ReserveNo = @ReserveNo or Guests.Reservation = @ReserveNo); if @Deposit is null then set @Deposit=0 end if; set @Deposit=@Deposit/(1+bvrDDSPart); if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1005,null) end if end //[v 18.02.2003] GO --[7] - PrepareRoomBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareRoomBill'))) THEN Drop procedure PrepareRoomBill END IF GO create procedure DBA.PrepareRoomBill(in @RoomNo integer,in @place varchar(50)) // Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles // sus noshtuvkite ,pochivkite i uslugite koito triabva da plashtat gostite, koito sa v dadena staia begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc double; //nt --Maria --declare @RoomName varchar(5); --Maria //Iztriva wremennata tablica za smetka delete from #Articles; // select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; //Vkarva noshtuvkite, pochivkite i zastrahovkite, koito triabva da plashtat ot gostite ot staiata for f0 as curs0 scroll cursor for select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,B.WhoPays as @WhoPays, B.Room as @Room,/*M*/R.RoomName as @RoomName/*M*/,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency, B.IsRest as @IsRest,B.DateRegDt as @RDate from Book as B left outer join Rooms as R on B.Room=R.RoomNum,Book as BB/*,Rooms as R*/ where (B.NumLodgings-B.PayedLodgings) <> 0 and BB.Room = @RoomNo and B.WhoPays = BB.Guest and BB.Staying = 1 /*and B.Room=R.RoomNum*/ do //Vmukva vuv Vremennata tablica set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest, @ArticleNumber,@place,@RDate) end for; //Vkarva uslugite, koito triabva da plashtat gostite ot staiata for f1 as curs1 scroll cursor for select SC.CreditNo as @CreditNo,S.ServiceName as @ArticleName,SC.RoomNum as @Room,/*M*/R.RoomName as @RoomName,/*M*/ (SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS,SC.WhoPays as @WhoPays, SC.DateCreditDt as @DateCr from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C,Book as B,/*M*/Rooms as R/*M*/ where (NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency and SC.WhoPays = B.Guest and B.Staying = 1 and B.Room = @RoomNo and ((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1))/*M*/and B.Room=R.RoomNum/*M*/ do if(@Room is not null) then --Maria --set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName --Maria else set @ArticleName=@ArticleName end if; select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @WhoPays; //nt /* if(@Disc<>0) then //nt set @Price=CalcPriceWithDiscount(@Disc,@Price) end if //nt ; */ call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr); set @ArticleNumber=@ArticleNumber+1 end for; //Izchisliava dali ima depositi i gi vkarva select sum(ResMoney.Sum*Currencies.ExchangeRate) into @Deposit from ResMoney,Currencies,Book where ResMoney.Status = 2 and ResMoney.Currency = Currencies.KodCurrency and ResMoney.GuestNo = Book.Guest and Book.Staying = 1 and Book.Room = @RoomNo; if @Deposit is null then set @Deposit=0 end if; set @Deposit=@Deposit/(1+bvrDDSPart); if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1005,today(*)) end if end Go --[8] - VtdFillLodgingsForContractBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('VtdFillLodgingsForContractBill'))) THEN Drop procedure VtdFillLodgingsForContractBill END IF GO create function DBA.VtdFillLodgingsForContractBill(in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50)) returns integer begin declare @ArticleName varchar(40); declare @ArticlePrice double; declare @PackagePrice double; declare @PackagePrWithDDS double; //nt declare @TipDDS integer; declare @ServiceNoLodging integer; declare @I integer; declare @Disc double; //nt declare @ArticlePrWithDDS double; //nt declare @IsForAlServices integer; declare @ContractNo integer; --Maria declare @RoomName varchar(5); --Maria //Proverqva dali ima otstypka po dogovora i dali tazi otstypka e za vsi4ki services select C.IsDiscForAll,C.Discount,c.ContractNo into @IsForAlServices, @Disc, @ContractNo from Contracts as C,Book as B where B.ContractPays = C.ContractNo and B.RegNum = @Regno; if(@IsForAlServices = 0) then set @Disc=0 end if; //@ServiceNoLodging e N-na na noshtuvka ili pochivka syotvetno za nashenec ili chujdenec select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum; //Izchislenie cenata na noshtuvkata v leva syobrazno otstypkata po dogovora ako ima otstypka select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency; set @ArticlePrWithDDS=@ArticlePrice; //Podvajda sumata na uslugite ot paketa ot select Sum(if VidSdelki.TipDDS = 2 then(1.07)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif) into @PackagePrice from Packages,Currencies,Services,VidSdelki where Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency and Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N; if @PackagePrice is null then set @PackagePrice=0; set @PackagePrWithDDS=0 //nt else set @PackagePrWithDDS=@PackagePrice //nt end if; //Izchisliava se tipa DDS za noshtuvkata select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N; if @TipDDS = 2 then set @ArticlePrice=@ArticlePrice*(1.07); //ako dds-to e vkliucheno v cenata set @TipDDS=1 end if; set @ArticlePrice=@ArticlePrice-@PackagePrice; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(1.07); //ako dds-to e vkliucheno v cenata set @TipDDS=2 end if; --Maria set @RoomName=(select RoomName from Rooms where RoomNum=@Room); --if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm') if @IsRest = 1 then set @ArticleName='Почивка ' || @place || @RoomName+' за '+dateformat(@DateReg,'Dd.Mm') --Maria else --Maria --set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') set @ArticleName='Нощувка ' || @place || @RoomName +' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm') --Maria end if; if(@Disc = 0) then select srv.Discount into @Disc from ServicesForContract as srv,Book as b where b.ContractPays = srv.ContractNo and b.RegNum = @RegNo and srv.ServiceNo = @ServiceNoLodging end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice) set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt //Insertva noshtuvka ili pochivka insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double), ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ; set @I=1; set @Disc=0; // if(@IsForAlServices=0) then set @Disc=0 // end if // ; //Insertva i uslugite ot paketa for f as curs scroll cursor for select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency, Services.ServiceName as @ServiceName from Packages,Services where RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency; //Izchisliava se tipa DDS za uslugata // select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract=Contracts.ContractNo and Guests.GuestNum=Book.Guest and Packages.RegNo=Book.RegNum and Book.RegNum=@RegNo; //nt select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N; if @TipDDS = 1 then set @ArticlePrice=@ArticlePrice/(1.07); //ako dds-to e vkliucheno v cenata set @TipDDS=2 end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice); if(@Disc = 0) then select Discount into @Disc from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @ServiceNo end if; // set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice); //Insertva uslugata vuv #Articles insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber+@I,@ServiceName,'бр.', @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100), @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null) ; set @I=@I+1; set @Disc=0 end for; return(@I) end GO --[9] - VtdPrepareContractBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('VtdPrepareContractBill'))) THEN Drop procedure VtdPrepareContractBill END IF GO create procedure DBA.VtdPrepareContractBill(in @ContractNo integer,in @place varchar(50)) // Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles // sus noshtuvkite,pochivkite i uslugite koito triabva da plashtat gostite nastaneni po daden dogovor begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc decimal(12,2); declare @PrWithDisc decimal(12,2); declare @IsForAlServices integer; declare @HumanKind integer; delete from #Articles; select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; //Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor select C.IsDiscForAll,C.Discount into @IsForAlServices,@Disc from Contracts as C where C.ContractNo = @ContractNo; if(@IsForAlServices = 0) then set @Disc=0 end if; for f2 as curs2 scroll cursor for select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest, B.Room as @Room,R.RoomName as @RoomName,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,IsRest as @IsRest from Book as B left outer join Rooms as R on B.Room=R.RoomNum where B.ContractPays = @ContractNo and(B.NumLodgings-B.PayedLodgings) <> 0 /*and B.Room=R.RoomNum*/ do set @ArticleNumber=@ArticleNumber+VtdFillLodgingsForContractBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for; //Naliva uslugi po dogovor if(@IsForAlServices = 0) then set @Disc=0 end if; for f1 as curs1 scroll cursor for select SC.CreditNo as @CreditNo, S.ServiceName as @ArticleName, SC.RoomNum as @Room, --Maria R.RoomName as @RoomName, --Maria (SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS, SC.DateCreditDt as @DateCr from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum=R.RoomNum,Services as S,VidSdelki as VS,Currencies as C/*, Rooms as R*/ where SC.ContractPays = @ContractNo and(NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency /*and SC.RoomNum=R.RoomNum*/ do if(@Room is not null) then --Maria --set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName --Maria else set @ArticleName=@ArticleName end if; if(@Disc = 0) then select Discount into @Disc from ServicesForContract where ServiceNo = @Service and ContractNo = @ContractNo end if; //set @Price=CalcPriceWithDiscount(@Disc,@Price); call VtdFillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr); set @Disc=0; set @ArticleNumber=@ArticleNumber+1 end for; set @Deposit=0; set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo)/(1.07); if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозити(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1004,today(*)) end if end //[v 18.02.2003] Jorko //[v 18.07.2003] Jorko Pribavia data na uslugata 1004 GO --[10] - SpaPrepareGuestBill IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('SpaPrepareGuestBill'))) THEN Drop procedure SpaPrepareGuestBill END IF GO create procedure DBA.SpaPrepareGuestBill(in @place varchar(50),in SpaServiceNo integer,in @SingalInvoice integer,in @SpaResID integer,in @AllServices integer) result(@Disc integer) begin declare @ArticleNumber integer; declare @Deposit double; declare @Disc double; // declare @DateCr date; delete from #Articles; select Max(ArticleNumber)+1 into @ArticleNumber from #Articles; if @ArticleNumber is null then set @ArticleNumber=1 end if; for gg as curs11 scroll cursor for select GuestNo as @GuestNo from #GuestsForBill do select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @GuestNo; for ff as cursf scroll cursor for select SC.CreditNo as @CreditNo, S.ServiceName as @ArticleName, SC.RoomNum as @Room, --Maria R.RoomName as @RoomName, --Maria (SC.SumCredit*C.ExchangeRate) as @Price, SC.Service as @Service, (NumberServices-PayedServices) as @ArticleQty, VS.TipDDS as @TipDDS, SC.DateCreditDt as @DateCr, SC.AutomaticService as @AutomaticService, SpRes.DiscountStn as @DiscountStn from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum=R.RoomNum,Services as S,VidSdelki as VS,SpaCurrencies as C, SpaReservations as SpRes/*,Rooms as R*/ where SpRes.N = SC.SPaReservationFk and SC.WhoPays = @GuestNo and (NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and (((@AllServices = 1) and(S.ServiceType = 2)) or((@AllServices = 0) and(S.ServiceNo = SpaServiceNo))) and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency and ((@SingalInvoice = 0) or((@SingalInvoice = 1) and(SPaReservationFk = @SpaResID))) /*and SC.RoomNum=R.RoomNum*/ do if(@Room is not null) then if @AutomaticService = 1 then --Maria //set @ArticleName='* ' || @ArticleName || @place || cast(@Room as varchar) set @ArticleName='* ' || @ArticleName || @place || @RoomName --Maria else --Maria //set @ArticleName=@ArticleName || @place || cast(@Room as varchar) set @ArticleName=@ArticleName || @place || @RoomName --Maria end if else set @ArticleName=@ArticleName end if; call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@DiscountStn,@DateCr); set @ArticleNumber=@ArticleNumber+1 end for end for; set @Deposit=0; for gk as cursgk scroll cursor for select GuestNo as @GuestNo from #GuestsForBill do set @Deposit=@Deposit+CalcDepositSum(@GuestNo)/(1+bvrDDSPart) end for; if @Deposit <> 0 then insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo, CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0, 0,0,-@Deposit,2,1005,null) end if end GO --[11] - v_OwnersRoomsSelected IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OwnersRoomsSelected'))) THEN Drop view v_OwnersRoomsSelected END IF GO create view DBA.v_OwnersRoomsSelected( ContractNo,OwnerNo,Owner_Name,GroupNo,RoomName,st_Active) as select c.ContractNo as ContractNo, o.OwnerN as OwnerNo, c.FullName as Owner_Name, c.GroupNo as GroupNo, r.RoomName as RoomName, o.Active as st_Active from dba.Contracts as c,dba.OwnersRooms as o,dba.Rooms r where c.GroupNo = 2 and o.OwnerN = (select OwnerN from DBA.Owners where Owners.ContractN = c.ContractNo) and o.Room=r.RoomNum //Maria - vmesto nomer na staia da se izpolzva imeto na staiata GO -------------------------------------------------------Spravki------------------------------------------------------------------------------------------- --[1] - VIP IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_VIP'))) THEN Drop view v_VIP END IF GO create view DBA.v_VIP as select GuestName=Guests.Name, RoomNo=Rooms.RoomName, ArriveDate=Book.DateRegDt, DaysToStay=Book.NumLodgings, LeaveDate=Days(ArriveDate,Book.NumLodgings), VIP=VIP.VipName from DBA.Guests,DBA.Book,DBA.VIP,DBA.Rooms where Book.Guest = Guests.GuestNum and Guests.Status = 1 and Guests.TipVip = VIP.N and Book.Room=Rooms.RoomNum GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_VIP' and ColName='RoomNo' GO --[2] - за справка "Анулирани резервации" IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetResrRoomsTxt'))) THEN Drop procedure GetResrRoomsTxt END IF GO create function DBA.GetResrRoomsTxt(in @ReserveNo integer,in @FromDateDt date) returns text begin declare @R text; declare @L integer; declare @Stage integer; // set @R=''; set @Stage=-999; for F as Curs scroll cursor for --Maria //select RoomNo as @RoomNo from ReservePlan where select ReservePlan.RoomNo as @Roomno,R.RoomName as @RoomName from ReservePlan,Rooms R where --Maria ReserveNo = @ReserveNo and ReservePlan.RoomNo <> 0 and ReservePlan.Status = 1 and FromDateDt = @FromDateDt and R.RoomNum=ReservePlan.RoomNo order by ReservePlan.RoomNo asc do set @L=Floor(@RoomNo/100); if @R <> '' then set @R=@R || ', '; if @Stage <> @L then set @R=@R || bvrNEW_LINE end if end if; --Maria //set @R=@R || @RoomNo; set @R=@R || @RoomName; --Maria set @Stage=@L; set @L=@L end for; return(@R) end //[v 00.12.25] //[v 01.07.26] - Niki - proverka za data GO --[3] - Движение на гост IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegBaseServicesPeriodForOazis'))) THEN Drop view v_RegBaseServicesPeriodForOazis END IF GO create view DBA.v_RegBaseServicesPeriodForOazis as select RegDate=DateRegDt, GuestName=Guests.Name, Account=BK.Guest, --Maria //RoomNo=BK.Room, RoomNo=R.RoomName, --Maria Service='Нощувка и застраховка', ServicesNum=NumLodgings, ServiceSngPrice=Currencies.ExchangeRate*BK.Price, AllSum=BK.NumLodgings*ServiceSngPrice, Dogovor=Contracts.ShortName, Plateni=ServiceSngPrice*BK.PayedLodgings, DateEnd=Days(DateRegDt,NumLodgings) from --Maria //DBA.Book as BK,dba.Guests,DBA.Currencies,DBA.Contracts where DBA.Book as BK,dba.Rooms as R,dba.Guests,DBA.Currencies,DBA.Contracts where --Maria Guests.GuestNum = BK.Guest and Guests.Contract = Contracts.ContractNo and BK.KodCurrency = Currencies.KodCurrency and --Maria BK.Room=R.RoomNum and --Maria BK.ContractPays = 0 and((RegDate >= bvrData1) and(RegDate <= bvrData2)) union select RegDate=DateRegDt, GuestName=Guests.Name, Account=BK.Guest, --Maria //RoomNo=BK.Room, RoomNo=R.RoomName, --Maria Service='Нощувка и застраховка', ServicesNum=NumLodgings, ServiceSngPrice=Currencies.ExchangeRate*BK.Price, AllSum=BK.NumLodgings*DBA.CalcPriceWithDiscount(scr.Discount,ServiceSngPrice), Dogovor=Contracts.ShortName, Plateni=BK.PayedLodgings*DBA.CalcPriceWithDiscount(scr.Discount,ServiceSngPrice), DateEnd=Days(DateRegDt,NumLodgings) from --Maria //DBA.Book as BK,dba.Guests,DBA.Currencies,DBA.Contracts,dba.ServicesForContract as scr where DBA.Book as BK,dba.Rooms as R,dba.Guests,DBA.Currencies,DBA.Contracts,dba.ServicesForContract as scr where --Maria Guests.GuestNum = BK.Guest and Guests.Contract = Contracts.ContractNo and BK.KodCurrency = Currencies.KodCurrency and --Maria BK.Room=R.RoomNum and --Maria BK.ContractPays <> 0 and BK.ContractPays = scr.ContractNo and scr.ServiceNo in( -1,-2) and((RegDate >= bvrData1) and(RegDate <= bvrData2)) GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_RegBaseServicesPeriodForOazis' and ColName='RoomNo' GO --[4] - Дневен лист IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_DaylyList'))) THEN Drop view v_DaylyList END IF GO create view DBA.v_DaylyList as select GuestNum=Guests.GuestNum, GuestName=Guests.Name, --Maria //RoomNum=Bk1.Room, RoomNum=R.RoomName, --Maria ArriveDate=BK2.ArriveDate, LeaveDate=BK2.LeaveDate, NumNight=BK2.NumNights, PriceNight=string(BK1.Price,' ',Currencies.Symbol), SumPayedServ=(BK1.PayedLodgings*BK1.Price*DayRates.ExchangeRate)+ (if v_PayedServicesByGuest.Stoinost is null then 0 else v_PayedServicesByGuest.Stoinost endif)+ (if PDG.Stoinost is null then 0 else PDG.Stoinost endif), Balance=Guests.Balance, TotalDebt=SumPayedServ+Balance, Country=Countries.Description from --Maria DBA.Rooms as R, --Maria DBA.Book as BK1,dba.v_ArriveStayLeaveGuestBook as BK2,DBA.Guests,dba.DayRates,dba.v_PayedServicesByGuest, dba.v_PayedDepositsByGuest as PDG,dba.Currencies,dba.Countries where --Maria BK1.Room=R.RoomNum and --Maria BK1.Guest = Guests.GuestNum and BK2.GuestNum = Guests.GuestNum and BK1.Guest *= v_PayedServicesByGuest.GuestNum and BK1.Guest *= PDG.GuestNum and BK1.KodCurrency = Currencies.KodCurrency and BK1.Staying = 1 and bvrData1 >= BK1.DateRegDt and bvrData1 < Days(BK1.DateRegDt,BK1.NumLodgings) and DayRates.DateDt = bvrData1 and DayRates.KodCurrency = BK1.KodCurrency and Guests.NativeCountry = Countries.CountryKod GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_DaylyList' and ColName='RoomNum' GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ArriveStayLeaveGuestBook'))) THEN Drop view v_ArriveStayLeaveGuestBook END IF GO create view DBA.v_ArriveStayLeaveGuestBook as select GuestNum=Guest, ArriveDate=Min(Book.DateRegDt), LastRoom=MAX(if Book.Staying = 2 then 0 else Book.Room endif), --Maria LastRoomName=MAX(if Book.Staying = 2 then '' else R.RoomName endif), --Maria NumNights=Sum(Book.NumLodgings), LeaveDate=Days(ArriveDate,NumNights), RegPrice=SUM(Book.NumLodgings*Book.Price), Admin=Max(Admin.FullName) from --Maria //DBA.Book,DBA.Admin where DBA.Book,DBA.Admin,DBA.Rooms as R where Book.Room=R.RoomNum and --Maria Book.KodAdmin = Admin.AdminNo and Book.IsRest <> 1 group by Guest GO --[5] - Дневен лист за дата IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegisterGuests'))) THEN Drop view v_RegisterGuests END IF GO create view DBA.v_RegisterGuests as select G.GuestNum, ArriveDate, G.Name, G.EGN, CO.Description as BirthCountry, string(BirthDateDt,', ',G.City) as BirthData, String(G.PassportSeria,G.PassportNumber) as PassportData, /*PassportData=String(G.PassportSeria,G.PassportNumber,', ',G.PassportDateDt,' ',G.PassportWhere),*/ Years(BirthDateDt,current date) as Age, G.Address as AddressData, --Maria //LastRoom as RoomNo, LastRoomName as RoomNo, --Maria LeaveDate, Days(ArriveDate,LeaveDate) as BedNights, String(G.CarNumber,' ',G.CarModel) as MPSData, G.Reservation, Ctr.ShortName from DBA.v_ArriveStayLeaveGuestBook as BK,DBA.Guests as G,DBA.Countries as CO,DBA.Contracts as Ctr where BK.GuestNum = G.GuestNum and G.NativeCountry = CO.CountryKod and LeaveDate > bvrData1 and ArriveDate <= bvrData2 and ArriveDate <> LeaveDate and G.Contract = Ctr.ContractNo GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_NewArriveStayLeaveGuestBook'))) THEN Drop view v_NewArriveStayLeaveGuestBook END IF GO create view DBA.v_NewArriveStayLeaveGuestBook as select GuestNum=Guest,GuestName=min(G.Name), ArriveDate=Min(Book.DateRegDt), LastRoom=MAX(if Book.Staying = 2 then 0 else Book.Room endif), --Maria LastRoomName=MAX(if Book.Staying = 2 then '' else R.RoomName endif), --Maria NumNights=Sum(Book.NumLodgings), LeaveDate=Days(ArriveDate,NumNights), -- Yanko + -- RegPrice=max(Book.Price||' '||C.Symbol), RegPrice=Book.Price || ' ' || C.Symbol, -- Yanko - PayNightPrice=sum(Book.PayedLodgings*Book.Price*DR.ExchangeRate), Admin=Max(Admin.FullName), PriceKind=PRK.Description from DBA.Book,DBA.Admin,dba.Guests as G,dba.DayRates as DR,/*Maria*/dba.Rooms as R,/*Maria*/ dba.Currencies as C,DBA.PriceKinds as PRK where --Maria Book.Room = R.RoomNum and --Maria Book.KodAdmin = Admin.AdminNo and Book.IsRest <> 1 and Book.Guest = G.GuestNum and DR.KodCurrency = Book.KodCurrency and DR.DateDt = Book.DateRegDt and C.KodCurrency = Book.KodCurrency and Book.PriceKind = PRK.PriceKind group by Guest,RegPrice,PriceKind //[v. 22.06.2006] //[v. 29.05.2008] - Maria, dobavena kolona PriceKind GO --[6] - Дневен лист за период 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, ContractName=Ctr.FullName,BK.Admin from dba.v_NewArriveStayLeaveGuestBook as BK, dba.Guests as G, dba.Countries as C, dba.Contracts as Ctr where /* /Krum/ BK.ArriveDate <> BK.LeaveDate and */ 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 IF ((select count(*) from Relations where TableName='v_DaylyListForPeriod' and ColName='LastRoom')>0) THEN update Relations set ColName='LastRoomName' where TableName='v_DaylyListForPeriod' and ColName='LastRoom'; END IF GO --[7] - Заминаващи за дата IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_LeavingForDate'))) THEN Drop view v_LeavingForDate END IF GO create view DBA.v_LeavingForDate --Maria //as select distinct NumRoom=Book.Room, as select distinct NumRoom=Rooms.RoomName, --Maria RoomType=Classes.ShortName, AccountNo=Guests.GuestNum, GuestName=Guests.Name, Elderly=if Guests.AdultType = 0 then 1 else 0 endif, Children=if Guests.AdultType > 0 then 1 else 0 endif, ArriveDate=Book.DateRegDt, StayDays=Book.NumLodgings, Agent=Contracts.ShortName, Balance=Guests.Balance, AdminName=Admin.ShortName from DBA.Book,DBA.Guests left outer join dba.Contracts on (Guests.Contract = Contracts.ContractNo), DBA.Admin,DBA.Rooms,DBA.Classes where Book.Guest = Guests.GuestNum and bvrData2 = Days(ArriveDate,StayDays) and Book.NumLodgings <> 0 and Book.Staying <> 2 and Book.IsRest <> 1 and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and Book.KodAdmin = Admin.AdminNo and Book.Room = Rooms.RoomNum and Rooms.RoomCat = Classes.Class //[v 00.06.13] //[v 01.07.03] - Niki - dobaven tip staia //[v 01.07.04] - Niki - mahnati pochivkite //[v 30.10.02] - Niky - promeni svarzani s dogovorite GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_LeavingForDate' and ColName='NumRoom' GO --[8] - Издадени сметки по услуги 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, Service=Services.ServiceName, /*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 else((-1)*ART.Qty) endif endif -- Yanko - 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, --Maria //RoomNo=convert(varchar,BK.Room), RoomNo=R.RoomName, --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%' from DBA.Articles as ART, DBA.Services, DBA.Invoices as INV, DBA.Book as BK, --Maria DBA.Rooms as R, --Maria DBA.Guests as G, DBA.Admin as ADMIN, DBA.Contracts, DBA.Countries, DBA.HumanKinds as HK, DBA.VATRates as VR where --Maria BK.Room = R.RoomNum and --Maria 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, 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, --Maria //RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif), RoomNo=(if(G.GuestNum <> 0) then (select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif), --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%' from -- 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) 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 --and ART.ServiceNo not in(1004,1005) 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), 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), --Maria //RoomNo=max(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif), RoomNo=max(if(G.GuestNum <> 0) then (select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif), --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%' from --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)) 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 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, 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) -- [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 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), --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), RoomNo=(if(GuestNum > 0) then (select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum) else null endif), --Maria DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%' 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 GO --[9] - Камериерки IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_Kamerierki'))) THEN Drop view v_Kamerierki END IF GO create view DBA.v_Kamerierki as select RoomType=Max(Classes.ShortName), RoomNum=Rooms.RoomNum, --Maria RoomName=Rooms.RoomName, --Maria GuestNo=Min(Book.Guest), -- Maria +, Status na sraia za period RoomStatus=(if(select count(*) from DBA.RoomStatusForPeriod where NRoom = Rooms.RoomNum and StartDate <= bvrData1 and EndDate >= bvrData1) > 0 then (select RS.Description from DBA.RoomStatusForPeriod as RSFP,DBA.RoomStatus as RS where RSFP.NRoom = Rooms.RoomNum and RSFP.StartDate <= bvrData1 and RSFP.EndDate >= bvrData1 and RSFP.NStatus = RS.RoomStatusNo) else Max(RoomStatus.Description) endif), --Maria - Elderly=Sum(if(Book.DateRegDt <= bvrData1) and (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) and (Guests.AdultType = 0) and (Guests.Status = 1) then 1 else 0 endif), Children=Sum(if(Book.DateRegDt <= bvrData1) and (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) and (Guests.AdultType > 0) and (Guests.Status = 1) then 1 else 0 endif), RegDate=DBA.GetLeaveArriveDate(1,GuestNo), DaysClean=max(Registers.IntStojnost), Clean=(if Mod("date"(bvrData1)-RegDate,DaysClean) <> 0 then ' ' else 'П,' endif), ChangeSheets=(if Mod("date"(bvrData1)-RegDate,(select Max(r.IntStojnost) from DBA.Registers as r where r.RegisterName = 'CleanPerDays')) <> 0 then '' else 'СБ' endif), ToDo=Clean+LeaveStat, LeaveDate=DBA.GetLeaveArriveDate(2,GuestNo), GuestName=Max(if(Book.DateRegDt <= bvrData1) and (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) then Guests.Name else null endif),VIP=Max(if(Book.DateRegDt <= bvrData1) and (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) then VIP.VipName else null endif), KamerierkaName=(select FullName from DBA.Admin where AdminNo = Rooms.Kamerierka), LeaveStat=if LeaveDate = bvrData1 then 'З' else ' ' endif, IsReserve=Max(if RP.ReserveNo <> 0 then if(select Lr.Status from DBA.Reserve as Lr where Lr.ReserveNo = RP.ReserveNo) = 2 then null else 'Резервирана' endif else null endif),Note1='',Note2='' from DBA.Book left outer join DBA.Guests on Book.Guest = Guests.GuestNum, DBA.Rooms left outer join DBA.ReservePlan as RP on RP.FromDateDt = bvrData1 and Rooms.RoomNum = RP.RoomNo, DBA.Rooms left outer join DBA.Book on Rooms.RoomNum = Book.Room and Book.DateRegDt < bvrData1 and Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1 and Book.NumLodgings > 0, DBA.Guests left outer join DBA.VIP on Guests.TipVip = VIP.N, DBA.Guests,DBA.Classes,DBA.RoomStatus,DBA.VIP,DBA.Registers where Rooms.RoomCat = Classes.Class and Rooms.Status = RoomStatus.RoomStatusNo and Registers.RegisterName = 'Kamerierki' group by Rooms.RoomNum,KamerierkaName,/*Maria*/RoomName GO IF ((select count(*) from Relations where TableName='v_Kamerierki' and ColName='RoomNum')>0) THEN update Relations set ColName='RoomName' where TableName='v_Kamerierki' and ColName='RoomNum'; END IF GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_Kamerierki' and ColName='RoomName' GO --[10] - Начислени услуги по договор IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_UsedServicesByContract'))) THEN Drop view v_UsedServicesByContract END IF GO create view DBA.v_UsedServicesByContract( GNum,Guest,DateCredit,Service,Quantity,PrWithoutDisc, curKod,SumWithoutDisc,Disc,Discount,PrWithDisc,SumWithDisc,BgSumWithDisc,Dogovor, RoomNum,BgPayedServiceWhitDisc,N) as select distinct --Maria,1628 --GNum=g.GuestNum, GNum=convert(varchar,g.GuestNum), --Maria Guest=g.Name, DateCredit=b.DateRegDt, --Maria //Service=(if b.IsRest = 0 then 'Нощувка в стая '+convert(varchar,b.Room) else 'Почивка в стая '+convert(varchar,b.Room) endif), Service=(if b.IsRest = 0 then 'Нощувка в стая '+r.RoomName else 'Почивка в стая '+r.RoomName endif), --Maria Quantity=b.NumLodgings, PrWithoutDisc=b.Price, curKod=cur.Symbol, SumWithoutDisc=Quantity*PrWithoutDisc, Disc=scr.Discount, Discount=scr.Discount, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName, --Maria, 1628 --RoomNum=b.Room, --Maria //RoomNum=convert(varchar,b.Room), RoomNum=r.RoomName, --Maria --Maria BgPayedServiceWhitDisc=(if b.PayedLodgings = 0 then 0 else((b.PayedLodgings*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif), N=b.Regnum from /*Maria*/Rooms as r,/*Maria*/ dba.Book as b,dba.Guests as g,dba.Contracts as c,dba.Currencies as cur,dba.ServicesForContract as scr where --Maria b.Room = r.RoomNum and --Maria b.ContractPays = c.ContractNo and b.DateRegDt between bvrData1 and bvrData2 and b.Guest = g.GuestNum and b.ContractPays = scr.ContractNo and scr.ServiceNo in( -1,-2) and b.KodCurrency = cur.KodCurrency and c.ContractNo <> 0 union all select distinct --Maria,1628 --GNum=g.GuestNum, GNum=convert(varchar,g.GuestNum), --Maria Guest=g.Name, DateCredit=sc.DateCreditDt, s.ServiceName, Quantity=(select Sum(NumberServices) from dba.ServiceCredits where Service = s.ServiceNo and GuestNo = g.GuestNum and KodCurrency = cur.KodCurrency and CreditNo = sc.CreditNo), PrWithoutDisc=sc.SumCredit, curKod=cur.Symbol, SumWithoutDisc=(Quantity*PrWithoutDisc), Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo), Discount=if Disc is null then 0 else Disc endif, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName, --Maria, 1628 --sc.RoomNum, --Maria //convert(varchar,sc.RoomNum), r.RoomName, --Maria --Maria BgPayedServiceWhitDisc=(if sc.PayedServices = 0 then 0 else((sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif), N=sc.CreditNo from /*Maria*/Rooms as r,/*Maria*/ dba.ServiceCredits as sc,dba.Contracts as c,dba.Guests as g,dba.Services as s,dba.Currencies as cur where --Maria sc.RoomNum = r.RoomNum and --Maria sc.ContractPays = c.ContractNo and sc.DateCreditDt between bvrData1 and bvrData2 and sc.GuestNo = g.GuestNum and sc.ContractPays <> 0 and s.ServiceNo = sc.Service and sc.KodCurrency = cur.KodCurrency and g.GuestNum <> 0 union all --Maria, 1628 select distinct GNum='',Guest='', DateCredit=sc.DateCreditDt, s.ServiceName, Quantity=(select Sum(NumberServices) from dba.ServiceCredits where Service = s.ServiceNo and KodCurrency = cur.KodCurrency and CreditNo = sc.CreditNo), PrWithoutDisc=sc.SumCredit, curKod=cur.Symbol, SumWithoutDisc=(Quantity*PrWithoutDisc), Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo), Discount=if Disc is null then 0 else Disc endif, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName,RoomNum='', BgPayedServiceWhitDisc=(if sc.PayedServices = 0 then 0 else((sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif), N=sc.CreditNo from dba.ServiceCredits as sc,dba.Contracts as c,dba.Services as s,dba.Currencies as cur where sc.ContractPays = c.ContractNo and sc.DateCreditDt between bvrData1 and bvrData2 and sc.GuestNo = 0 and sc.ContractPays <> 0 and s.ServiceNo = sc.Service and sc.KodCurrency = cur.KodCurrency // v.1 [] // v.2 [19.Nov.2007] - Maria, usluga po dogovor GO --[11] - Начислени услуги по собственици IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_NonPayedServicesByContract'))) THEN Drop view v_NonPayedServicesByContract END IF GO create view DBA.v_NonPayedServicesByContract( GNum,Guest,DateCredit,Service,Quantity,PrWithoutDisc, curKod,SumWithoutDisc,Disc,Discount,PrWithDisc,SumWithDisc,BgSumWithDisc,Dogovor, RoomNum,BgNonPayedServiceWithDisc,N) as select distinct --Maria,1628 --GNum=g.GuestNum, GNum=convert(varchar,g.GuestNum), --Maria Guest=g.Name, DateCredit=b.DateRegDt, --Maria //Service=(if b.IsRest = 0 then 'Нощувка в стая '+convert(varchar,b.Room) else 'Почивка в стая '+convert(varchar,b.Room) endif), Service=(if b.IsRest = 0 then 'Нощувка в стая '+r.RoomName else 'Почивка в стая '+r.RoomName endif), --Maria Quantity=b.NumLodgings, PrWithoutDisc=b.Price, curKod=cur.Symbol, SumWithoutDisc=Quantity*PrWithoutDisc, Disc=scr.Discount, Discount=scr.Discount, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName, --Maria, 1628 --RoomNum=b.Room, --Maria //RoomNum=convert(varchar,b.Room), RoomName=r.RoomName, --Maria --Maria BgNonPayedServiceWithDisc=SumWithDisc-(b.PayedLodgings*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), N=b.Regnum from dba.Book as b,/*Maria*/Rooms as r,/*Maria*/dba.Guests as g,dba.Contracts as c,dba.Currencies as cur,dba.ServicesForContract as scr where --Maria b.Room = r.RoomNum and --Maria b.ContractPays = c.ContractNo and b.DateRegDt between bvrData1 and bvrData2 and b.Guest = g.GuestNum and b.ContractPays = scr.ContractNo and scr.ServiceNo in( -1,-2) and b.KodCurrency = cur.KodCurrency and c.ContractNo <> 0 and c.GroupNo = 2 union all /* Contracts.GroupNo=2 (група Собственици) по искане на Кети */ /* Внимание ! Не всички бази имат тази група или е възможно номера на групата да е различен !*/ select distinct --Maria,1628 --GNum=g.GuestNum, GNum=convert(varchar,g.GuestNum), --Maria Guest=g.Name, DateCredit=sc.DateCreditDt, s.ServiceName, Quantity=(select Sum(NumberServices) from dba.ServiceCredits where Service = s.ServiceNo and GuestNo = g.GuestNum and KodCurrency = cur.KodCurrency and CreditNo = sc.CreditNo), PrWithoutDisc=sc.SumCredit, curKod=cur.Symbol, SumWithoutDisc=(Quantity*PrWithoutDisc), Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo), Discount=if Disc is null then 0 else Disc endif, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName, --Maria, 1628 --sc.RoomNum, --Maria //convert(varchar,sc.RoomNum), r.RoomName, --Maria --Maria BgNonPayedServiceWithDisc=SumWithDisc-(sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), N=sc.CreditNo from dba.ServiceCredits as sc,/*Maria*/Rooms as r,/*Maria*/dba.Contracts as c,dba.Guests as g,dba.Services as s,dba.Currencies as cur where --Maria sc.RoomNum = r.RoomNum and --Maria sc.ContractPays = c.ContractNo and sc.DateCreditDt between bvrData1 and bvrData2 and sc.GuestNo = g.GuestNum and sc.ContractPays <> 0 and s.ServiceNo = sc.Service and sc.KodCurrency = cur.KodCurrency and c.GroupNo = 2 and g.GuestNum <> 0 union all /* Contracts.GroupNo=2 - виж по-горе */ --Maria, 1628 select distinct GNum='',Guest='', DateCredit=sc.DateCreditDt, s.ServiceName, Quantity=(select Sum(NumberServices) from dba.ServiceCredits where Service = s.ServiceNo and KodCurrency = cur.KodCurrency and CreditNo = sc.CreditNo), PrWithoutDisc=sc.SumCredit, curKod=cur.Symbol, SumWithoutDisc=(Quantity*PrWithoutDisc), Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo), Discount=if Disc is null then 0 else Disc endif, PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc), SumWithDisc=Quantity*PrWithDisc, BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), Dogovor=c.FullName,RoomNum='', BgNonPayedServiceWithDisc=SumWithDisc-(sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency), N=sc.CreditNo from dba.ServiceCredits as sc,dba.Contracts as c,dba.Services as s,dba.Currencies as cur where sc.ContractPays = c.ContractNo and sc.DateCreditDt between bvrData1 and bvrData2 and sc.GuestNo = 0 and sc.ContractPays <> 0 and s.ServiceNo = sc.Service and sc.KodCurrency = cur.KodCurrency and c.GroupNo = 2 // v.1 [] // v.2 [19.Nov.2007] - Maria, usluga po dogovor GO -- за справка "Нерегистрирани телефонни разговори" IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('SelectRoomByPBXNumber'))) THEN Drop procedure SelectRoomByPBXNumber END IF GO create function DBA.SelectRoomByPBXNumber(in @PBXNumber integer) --Maria //returns integer returns varchar(5) --Maria // Wrushta nomer na staia po zadaden nomer na telefon begin --Maria //declare @RoomNo integer; declare @RoomName varchar(5); //select RoomNum into @RoomNo from Rooms where PBXNumber like string('%,',@PBXNumber,',%'); select RoomName into @RoomName from Rooms where PBXNumber like string('%,',@PBXNumber,',%'); //if @RoomNo is null then set @RoomNo=0 if @RoomName is null then set @RoomName='' --Maria end if; --Maria //return @RoomNo return @RoomName --Maria end //[v 00.05.28] GO --[12] - Нерегистрирани телефонни разговори IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_TelTalksNotRegistered'))) THEN Drop view v_TelTalksNotRegistered END IF GO create view DBA.v_TelTalksNotRegistered as select CallDate,CallTime,IntLine,ExtLine,DialNumber,Duration,PulseCount,CallPrice,RoomNum from dba.v_NotRegisterCalls where --Maria //RoomNum <> 0 //Za da ne pokazva slujebnite RoomNum <> '' //Za da ne pokazva slujebnite --Maria //[v 01.06.15] //[v 10.10.2002] GO --[13] - Неуредени сметки IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_WaitingBills'))) THEN Drop view v_WaitingBills END IF GO create view DBA.v_WaitingBills as select G.GuestNum, GuestName=G.Name, Qty=(SC.NumberServices-SC.PayedServices), Price=string(SC.SumCredit,' ',CrS.Symbol), PriceLv=SC.SumCredit*CrS.ExchangeRate, Total=PriceLv*Qty, --Maria --RoomNo = ASL.LastRoom, RoomNo = ASL.LastRoomName, --Maria S.ServiceName, G.Note, DateService=SC.DateCreditDt from dba.Guests as G,dba.ServiceCredits as SC,dba.Currencies as CrS, dba.v_ArriveStayLeaveGuestBook as ASL,dba.Services as S where SC.WhoPays = G.GuestNum and SC.WhoPays = ASL.GuestNum and SC.KodCurrency = CrS.KodCurrency and SC.NumberServices <> SC.PayedServices and SC.Service = S.ServiceNo and G.ServiceSum <> 0 and G.Status = 2 and Balance <> 0 and ((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) union all /* Yanko +*/ select GuestNum=B.Guest, GuestName=G.Name, Qty=(B.NumLodgings-B.PayedLodgings), Price=string(B.Price,' ',Crs.Symbol), PriceLv=B.Price*Crs.ExchangeRate, Total=PriceLv*Qty, --Maria --RoomNo=B.Room, RoomNo=R.RoomName, --Maria ServiceName='Нощувка', G.Note, DateService=B.DateRegDt from dba.Book as B,dba.Guests as G,dba.Currencies as CrS/*Maria*/,Rooms as R/*Maria*/ where --Maria B.Room = R.RoomNum and --Maria B.WhoPays = G.GuestNum and B.KodCurrency = CrS.KodCurrency and B.PayedLodgings <> B.NumLodgings and B.Price <> 0 and G.Status = 2 and B.WhoPays <> 0 union all /* Yanko -*/ select GuestNum=RM.GuestNo, GuestName=G.Name, Qty=(-1), Price=string(RM.Sum,' ',C.Symbol), PriceLv=RM.Sum*C.ExchangeRate, Total=Qty*PriceLv,RoomNo='0',ServiceName='Депозит', G.Note, DateService=RM.DateMoneyDt from dba.ResMoney as RM,dba.Guests as G,dba.Currencies as C where RM.Status = 2 and RM.InvoiceNo = any(select InvoiceNo from dba.Invoices where CancelInvoiceNo = 0) and RM.GuestNo <> 0 and RM.DepositNo > 0 and G.GuestNum = RM.GuestNo and G.Balance <> 0 and G.Status = 2 and C.KodCurrency = RM.Currency GO --[14] - Отчет туристи по резервации IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OtchetTurReserve'))) THEN Drop view v_OtchetTurReserve END IF GO create view DBA.v_OtchetTurReserve as select OtchetTurReserve.NumSign, OtchetTurReserve.RoomType, OtchetTurReserve.RoomNum, --Maria R.RoomName, --Maria OtchetTurReserve.Vaucher, OtchetTurReserve.Ime, OtchetTurReserve.TypeVip, OtchetTurReserve.Account, OtchetTurReserve.ReserveNo, OtchetTurReserve.Elderly, OtchetTurReserve.Children, OtchetTurReserve.ResMadeDate, OtchetTurReserve.ReserveDate, OtchetTurReserve.DaysToStay, OtchetTurReserve.TogetherWith, OtchetTurReserve.LeaveDate, NumRooms=DBA.DifferRoom(OtchetTurReserve.RoomNum,OtchetTurReserve.ReserveNo), OtchetTurReserve.BoardPlan, OtchetTurReserve.Agent, OtchetTurReserve.HourArrive, OtchetTurReserve.HourLeaving, OtchetTurReserve.GuestSex, OtchetTurReserve.GuestStatus, OtchetTurReserve.NumBreakfast, OtchetTurReserve.NumLunch, OtchetTurReserve.NumDinner from dba.OtchetTurReserve, --Maria dba.Rooms as R where OtchetTurReserve.RoomNum = R.RoomNum --Maria //v [1.04.2003] Jorko //v [29.10.2003] Jorko GO IF ((select count(*) from Relations where TableName='v_OtchetTurReserve' and ColName='RoomNum')>0) THEN update Relations set ColName='RoomName' where TableName='v_OtchetTurReserve' and ColName='RoomNum'; END IF GO --[15] - Пристигащи за дата IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ArrivingForDate'))) THEN Drop view v_ArrivingForDate END IF GO create view dba.v_ArrivingForDate as select GuestNum=Guests.GuestNum, GuestName=Guests.Name, --Maria --RoomNum=Bk1.Room, RoomNum=R.RoomName, --Maria ArriveDate=BK2.ArriveDate, LeaveDate=BK2.LeaveDate, NumNight=BK2.NumNights, PriceNight=string(BK1.Price,' ',Currencies.Symbol), SumLodgings=BK2.NumNights*BK1.Price*DayRates.ExchangeRate, SumPayedLodgings=0, SumNoPayed=(SumLodgings-SumPayedLodgings), Country=Countries.Description, Admin=a.FullName, Agent=c.ShortName, InvNo=null, VaucherNo=Guests.VaulcherNo from DBA.Book as BK1,dba.v_ArriveStayLeaveGuestBook as BK2,DBA.Guests,dba.DayRates, --Maria dba.Rooms as R, --Maria dba.Currencies,dba.Countries,dba.Admin as a,dba.Contracts as c where --Maria BK1.Room = R.RoomNum and --Maria BK1.Guest = Guests.GuestNum and BK2.GuestNum = Guests.GuestNum and BK1.KodCurrency = Currencies.KodCurrency and BK1.Staying = 1 and bvrData1 >= BK1.DateRegDt and bvrData1 < Days(BK1.DateRegDt,BK1.NumLodgings) and DayRates.DateDt = bvrData1 and DayRates.KodCurrency = BK1.KodCurrency and Guests.NativeCountry = Countries.CountryKod and BK1.KodAdmin = a.AdminNo and BK2.ArriveDate = bvrData1 and Guests.Contract = c.ContractNo and (select Count(a.QTY) from dba.Articles as a,dba.Invoices as I where a.InvoiceNo = I.InvoiceNo and I.whom = Guests.Name) = 0 and BK1.NumLodgings <> BK1.PayedLodgings union select GuestNum=Guests.GuestNum, GuestName=Guests.Name, --Maria --RoomNum=Bk1.Room, RoomNum=R.RoomName, --Maria ArriveDate=BK2.ArriveDate, LeaveDate=BK2.LeaveDate, NumNight=BK2.NumNights, PriceNight=string(BK1.Price,' ',Currencies.Symbol), SumLodgings=BK2.NumNights*BK1.Price*DayRates.ExchangeRate, SumPayedLodgings=DBA.GetPayedLodgingsWithInvoice(I.InvoiceNo,BK1.Price,DayRates.ExchangeRate,BK1.RegNum), SumNoPayed=DBA.GetUnpayedLodgings(I.InvoiceNo,BK1.RegNum,BK1.NumLodgings,BK1.Price,DayRates.ExchangeRate), /* ((select Qty from "dba".Articles where InvoiceNo=I.InvoiceNo and ServiceNo in(-1,-2)))*BK1.Price*DayRates.ExchangeRate as SumPayedLodgings,*/ /* (NumLodgings-(select Sum(a.Qty) from "dba".Articles as a,"dba".Invoices as Inv where a.InvoiceNo=Inv.InvoiceNo and a.ServiceNo in(-1,-2) and Inv.InvoiceNo<=I.InvoiceNo and Inv.whom=Guests."Name"))*BK1.Price*DayRates.ExchangeRate as SumNoPayed,*/ Country=Countries.Description, Admin=a.FullName, Agent=c.ShortName, InvNo=I.InvoiceNo, VaucherNo=Guests.VaulcherNo from DBA.Book as BK1,dba.v_ArriveStayLeaveGuestBook as BK2,DBA.Guests,dba.DayRates, --Maria dba.Rooms as R, --Maria dba.Currencies,dba.Countries,dba.Admin as a,dba.Contracts as c,dba.Invoices as I,dba.articles as ar where --Maria BK1.Room = R.RoomNum and --Maria BK1.Guest = Guests.GuestNum and BK2.GuestNum = Guests.GuestNum and BK1.KodCurrency = Currencies.KodCurrency and BK1.Staying = 1 and bvrData1 >= BK1.DateRegDt and bvrData1 < Days(BK1.DateRegDt,BK1.NumLodgings) and DayRates.DateDt = bvrData1 and DayRates.KodCurrency = BK1.KodCurrency and Guests.NativeCountry = Countries.CountryKod and BK1.KodAdmin = a.AdminNo and BK2.ArriveDate = bvrData1 and Guests.Contract = c.ContractNo and ar.InvoiceNo = I.InvoiceNo and ar.ServiceNo in( -1,-2) and ar.RegNo = BK1.RegNum // and BK1.NumLodgings<>BK1.PayedLodgings GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_ArrivingForDate' and ColName='RoomNum' GO -- [16] - за справка "Регистрирани услуги" 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, --Maria Service='Нощувка и застраховка', ServicesNum=DBA.DateIntersection(bvrData1,bvrData2,DateRegDt,Days(DateRegDt,NumLodgings)), ServiceSngPrice=Currencies.ExchangeRate*BK.Price, AllSum=ServicesNum*ServiceSngPrice, Dogovor=Contracts.ShortName, -- Plateni=null Plateni=BK.PayedLodgings*BK.Price from --Maria DBA.Rooms as R, --Maria DBA.Book as BK,dba.Guests,DBA.Currencies,DBA.Contracts where --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 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, 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 from DBA.ServiceCredits left outer join DBA.Admin on ServiceCredits.KodAdmin = Admin.AdminNo, DBA.Services, DBA.Guests, DBA.Currencies, DBA.v_ArriveStayLeaveGuestBook, DBA.Contracts where 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 update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_RegAllServicesPeriod' and ColName='RoomNo' GO -- [17] - РЕГИСТЪР БЪЛГАРИ IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegisterBG'))) THEN Drop view v_RegisterBG END IF GO create view DBA.v_RegisterBG as select Guests.GuestNum, GuestName=Guests.Name, Guests.EGN, GuestAddress=Guests.Address, Passport=Guests.PassportSeria || ' ' || Guests.PassportNumber || ' ' || Guests.PassportDateDt, Guests.City, Guests.BirthDateDt, Guests.PassportDateDt, --Maria -- Book.Room, R.RoomName, --Maria ASL.ArriveDate, ASL.LeaveDate, ImaDanni=if Guests.HasData = 1 then 'Да' else 'Не' endif, Nights=Book.NumLodgings from dba.Guests,dba.Book,dba.v_ArriveStayLeaveGuestBook as ASL,/*Maria*/dba.Rooms as R/*Maria*/ where --Maria Book.Room = R.RoomNum and --Maria Guests.GuestNum = ASL.GuestNum and Guests.HumanKind = 1 and Book.Guest = Guests.GuestNum and Book.DateRegDt = ASL.ArriveDate and Book.NumLodgings > 0 GO IF ((select count(*) from Relations where TableName='v_RegisterBG' and ColName='Room')>0) THEN update Relations set ColName='RoomName' where TableName='v_RegisterBG' and ColName='Room'; END IF Go --[18] - РЕГИСТЪР ЧУЖДЕНЦИ IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RegisterFG'))) THEN Drop view v_RegisterFG END IF GO create view DBA.v_RegisterFG as select Guests.GuestNum, GuestName=Guests.Name, Guests.NameLatin, Guests.EGN, GuestAddress=Guests.Address, Passport=Guests.PassportSeria || ' ' || Guests.PassportNumber || ' ' || Guests.PassportDateDt, BirthCountry=if NativeCountry = 0 then '' else BC.Description endif,Guests.PassportWhere, Guests.City, Guests.BirthDateDt, Guests.PassportDateDt, --Maria --Book.Room, R.RoomName, --Maria DateEnterBG, KPPName=KPP.Description, GoalName=Goals.Description, ASL.ArriveDate, ASL.LeaveDate, ImaDanni=if Guests.HasData = 1 then 'Да' else 'Не' endif, Nights=Book.NumLodgings from dba.Guests,dba.Book,dba.v_ArriveStayLeaveGuestBook as ASL,/*Maria*/dba.Rooms as R,/*Maria*/ dba.Countries as BC,dba.Goals,dba.KPP where --Maria Book.Room = R.RoomNum and --Maria Guests.GuestNum = ASL.GuestNum and Guests.HumanKind <> 1 and Guests.NativeCountry *= BC.CountryKod and Guests.Goal *= Goals.KodGoal and Guests.KPP *= KPP.KPPKod and Book.Guest = Guests.GuestNum and Book.DateRegDt = ASL.ArriveDate and Book.NumLodgings > 0 Go IF ((select count(*) from Relations where TableName='v_RegisterFG' and ColName='Room')>0) THEN update Relations set ColName='RoomName' where TableName='v_RegisterFG' and ColName='Room'; END IF GO --[19] - Рожденни дни IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_HappyBirthDay'))) THEN Drop view v_HappyBirthDay END IF GO create view DBA.v_HappyBirthDay --Maria --RoomNo=Book.Room, as select RoomNo=Rooms.RoomName, --Maria GuestName=Guests.Name from DBA.Guests,DBA.Book,dba.Rooms where --Maria Book.Room = Rooms.RoomName and --Maria Book.Guest = Guests.GuestNum and Guests.Status = 1 and Day(Guests.BirthDateDt) = Day(bvrData2) and Month(Guests.BirthDateDt) = Month(bvrData2) GO --[20] - Руминг лист IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RoomingList'))) THEN Drop view v_RoomingList END IF GO create view DBA.v_RoomingList as select BookDate=GuestBoardPlans.PlanDate, Agent=Contracts.ShortName, --Maria --RoomNum=Book.LastRoom, RoomNum=Book.LastRoomName, --Maria GuestNum=Guests.GuestNum, GuestName=Guests.Name, Elderly=if Guests.AdultType = 0 then 1 else 0 endif, Children=if Guests.AdultType > 0 then 1 else 0 endif, ArriveDate=ArriveDate, LeaveDate=LeaveDate, BB=if GuestBoardPlans.BoardPlan in( 4,5,6,7,8) then 1 else 0 endif, L=if GuestBoardPlans.BoardPlan in( 2,3,7,8) then 1 else 0 endif, D=if GuestBoardPlans.BoardPlan in( 1,3,5,6,7) then 1 else 0 endif, MPS=Guests.CarNumber from DBA.v_ArriveStayLeaveGuestBook as Book,DBA.Guests,DBA.GuestBoardPlans, DBA.BoardPlan,DBA.Contracts where Guests.GuestNum = Book.GuestNum and Guests.GuestNum = GuestBoardPlans.Guest and GuestBoardPlans.BoardPlan = BoardPlan.N and Guests.Status = 1 and Guests.Contract = Contracts.ContractNo and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) //[v 01.06.01] //[v 31.10.02] niky - dobaven e filtyr za grupa dogovori //[v 28.01.2004] Jorko opraveni vuzrastite na decata GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_RoomingList' and ColName='RoomNum' GO -- [21] - за справка "Руминг лист - вариант ресторант" IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetLastRoom'))) THEN Drop procedure GetLastRoom END IF GO create function DBA.GetLastRoom(in @Guest integer) --Maria --returns integer returns varchar(5) --Maria begin --Maria --declare @Room integer; declare @Room varchar(5); --Maria --select Room into @Room from Book where RegNum = (select max(RegNum) from -- Book as BB where BB.Guest = @Guest); select R.RoomName into @Room from Book as B,Rooms as R where B.RegNum = (select max(RegNum) from Book as BB where BB.Guest = @Guest) and B.Room = R.RoomNum; return(@Room) end //[24.08.2005] Jorko GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RoomingListRestaurantArriving'))) THEN Drop view v_RoomingListRestaurantArriving END IF GO create view DBA.v_RoomingListRestaurantArriving as select RestDate=GuestBoardPlans.PlanDate, --Maria --RoomNum=RP.RoomNo, RoomNum=R.RoomName, --Maria GuestName=Guests.Name, Elderly=if Guests.AdultType = 0 then 1 else 0 endif, Children=if Guests.AdultType > 0 then 1 else 0 endif, ArriveDate=(if RP.FromDateDt is null then Reserve.DateReserveDt else RP.FromDateDt endif), LeaveDate=(if RP.FromDateDt is null then Days(ArriveDate,Reserve.Days) else Days(ArriveDate,RP.NumDays) endif), BB=(if GuestBoardPlans.BoardPlan in( 4,5,6,7,8,9) then 1 else 0 endif), L=(if GuestBoardPlans.BoardPlan in( 2,3,6,7,8,9) then 1 else 0 endif), D=(if GuestBoardPlans.BoardPlan in( 1,3,5,6,7,9) then 1 else 0 endif), HB=(if GuestBoardPlans.BoardPlan in( 5,6) then BoardPlan.BoardPlanName else null endif), Board=BoardPlan.BoardPlanName, ReserveName=Reserve.Name,GStatus='Очакв.', TourOperator=(select FullName from dba.Contracts where ContractNo = Guests.Contract) from DBA.Reserve,DBA.Guests,DBA.GuestBoardPlans,DBA.BoardPlan,DBA.ReservePlan as RP,DBA.Rooms as R where --Maria RP.RoomNo = R.RoomNum and --Maria Guests.Reservation = Reserve.ReserveNo and Reserve.Status = 1 and Guests.Reservation <> 0 and Guests.Status = 0 and Guests.GuestNum = GuestBoardPlans.Guest and GuestBoardPlans.BoardPlan = BoardPlan.N and Reserve.DateReserveDt >= "Date"(Now(*)) and Guests.ReservePlanNo = RP.N //[v 01.01.02] //[v 04.06.2003] Jorko AdultType>0 GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_Union_RLR_RLRArriving' and ColName='RoomNum' GO -- [22] - Свободни и напускащи стаи IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_FreeRooms'))) THEN Drop view v_FreeRooms END IF GO create view DBA.v_FreeRooms --Maria --as select Room=Rooms.RoomNum, as select Room=Rooms.RoomName, --Maria Class=Max(CL.ShortName), Status=(if count(Book.RegNum) = 0 then ' Свободна' else if min(Book.DateRegDt+Book.NumLodgings) = bvrData2 then 'Напуска' else ' Заета' endif endif), ReserveName=max(Reserve.Name) from DBA.Rooms left outer join dba.Book on Book.NumLodgings <> 0 and Rooms.RoomNum = Book.Room and Days(Book.DateRegDt,Book.NumLodgings) >= bvrData2 and Book.DateRegDt <= bvrData2 left outer join DBA.ReservePlan on Rooms.RoomNum = ReservePlan.RoomNo and ReservePlan.FromDateDt = bvrData2 left outer join DBA.Reserve on ReservePlan.ReserveNo = Reserve.ReserveNo, DBA.Classes as CL, DBA.Reserve, DBA.RoomStatusForPeriod as RSFP where Rooms.RoomCat = CL.Class and Rooms.Status <> 1 and Rooms.Status <> 3 and Rooms.Status <> 10 and(not Rooms.RoomNum = any(select* from DBA.v_RoomStatusForData2)) --maria --group by Rooms.RoomNum order by group by Rooms.RoomName order by --maria Room asc GO -- [23] - Сейф IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_GuestListBySafe'))) THEN Drop view v_GuestListBySafe END IF GO create view DBA.v_GuestListBySafe as select Account=Guests.GuestNum, --Maria --NumRoom=BK.LastRoom, NumRoom=BK.LastRoomName, --Maria GuestName=Guests.Name, NumSafe=Guests.SafeNo, Country=Countries.Description, Agent=Contracts.ShortName, StayDays=NumNights, SafeSuma=DBA.GetGuestServiceSum(Guests.GuestNum,2) from dba.Contracts left outer join dba.Guests on(Contracts.ContractNo = Guests.Contract), dba.Countries, DBA.v_ArriveStayLeaveGuestBook as BK where Guests.GuestNum = BK.GuestNum and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and Guests.NativeCountry = Countries.CountryKod and Guests.Status = 1 //[v 00.07.26] //[v 31.10.02] niky - dobaven e filtyr za grupa dogovori GO --[24] - Сейф услуги IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ServicesSaif'))) THEN Drop view v_ServicesSaif END IF GO create view DBA.v_ServicesSaif as select Account=g.GuestNum, ServiceDate=DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY'), Service=s.ServiceName, GuestName=g.Name, Country=Countries.Description, Agent=Contracts.ShortName, PayedSum=sc.PayedServices*sc.SumCredit, Balance=(sc.PayedServices-sc.NumberServices)*sc.SumCredit, AdminName=Admin.ShortName, --Maria --Room=ASL.LastRoom, Room=ASL.LastRoomName, --Maria Currency=cur.Symbol from dba.ServiceCredits as sc, DBA.Services as s, DBA.Contracts left outer join DBA.Guests as g on Contracts.ContractNo = g.Contract, DBA.Countries, DBA.Admin, DBA.Currencies as cur, DBA.v_ArriveStayLeaveGuestBook as ASL where sc.Service = s.ServiceNo and sc.GuestNo = g.GuestNum and Contracts.GroupNo = any(select distinct(showtype) from dba.GrContracts where GrNo = -1) and g.CountryArrive = Countries.CountryKod and sc.KodAdmin = Admin.AdminNo and ASL.GuestNum = g.GuestNum and sc.KodCurrency = cur.KodCurrency and ((s.ServiceName like '%сейф%') or(s.ServiceName like '%Сейф%')) and ((sc.SPaReservationFk = -1) or(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1)) GO --[25] - Сметка за стая - Справка IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_SmetkaRoom'))) THEN Drop view v_SmetkaRoom END IF GO create view dba.v_SmetkaRoom --Maria --as select B.Room, as select Room=R.RoomName, --Maria SG.GuestName,SG.ServiceName, SG.ServicePrice, SG.Qty, SG.TotalSum from DBA.Book as B,DBA.v_SmetkaGuest as SG, dba.Rooms as R where B.Staying = 1 and B.Guest = SG.GuestNo --Maria and B.Room = R.RoomNum GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_SmetkaGuest'))) THEN Drop view v_SmetkaGuest END IF GO create view dba.v_SmetkaGuest as select GuestNo=B.WhoPays, GuestName=G.Name,ServiceName=string('Нощувка от ', --Maria -- "Date"(Days(B.DateRegDt,B.PayedLodgings)),' до ',"Date"(Days(B.DateRegDt,B.NumLodgings)),' в стая ',B.Room), "Date"(Days(B.DateRegDt,B.PayedLodgings)),' до ',"Date"(Days(B.DateRegDt,B.NumLodgings)),' в стая ',R.RoomName), --Maria ServicePrice=B.Price*Cr.ExchangeRate, Qty=B.NumLodgings-B.PayedLodgings, TotalSum=ServicePrice*Qty from --Maria --DBA.Book as B,DBA.Guests as G,DBA.Currencies as Cr where DBA.Book as B,DBA.Guests as G,DBA.Currencies as Cr, DBA.Rooms as R where B.Room = R.RoomNum and --Maria B.NumLodgings-B.PayedLodgings <> 0 and B.Guest = G.GuestNum and B.KodCurrency = Cr.KodCurrency union all select GuestNo=SC.WhoPays, GuestName=G.Name, S.ServiceName, ServicePrice=SC.SumCredit* (if(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1) then(select SpaCr.ExchangeRate from DBA.SpaCurrencies as SpaCr where SC.KodCurrency = SpaCr.KodCurrency) else Cr.ExchangeRate endif), Qty=SC.NumberServices-SC.PayedServices, TotalSum=ServicePrice*Qty from DBA.ServiceCredits as SC,DBA.Guests as G,DBA.Currencies as Cr,DBA.Services as S where SC.NumberServices-SC.PayedServices <> 0 and SC.GuestNo = G.GuestNum and SC.Service = S.ServiceNo and SC.KodCurrency = Cr.KodCurrency and ((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) union all select RM.GuestNo, GuestName=max(G.Name),ServiceName='Депозит', ServicePrice=-sum(RM.Sum*Cr.ExchangeRate), Qty=1, TotalSum=ServicePrice from DBA.ResMoney as RM,DBA.Guests as G,DBA.Currencies as Cr where RM.Status = 2 and RM.GuestNo = G.GuestNum and RM.Currency = Cr.KodCurrency group by RM.GuestNo GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_SmetkaRoom' and ColName='Room' GO --[26] - СПА отчет услуги IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_SpaProdajbiPoUslugi'))) THEN Drop view v_SpaProdajbiPoUslugi END IF GO create view DBA.v_SpaProdajbiPoUslugi as select 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, Service=Services.ServiceName, Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif, EdCena=round(abs(ART.Price)*(1+bvrDDSPart*(if ART.TipDDS = 2 then 1 else 0 endif)),4), Stoinost=round(Kol*EdCena,4), StoinostBDDS=round(Kol*abs(ART.Price),4), Discount=ART.Discount, EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=round(Kol*EdCenaWithDisc,4), Klient=INV.Whom,GuestType='Гост от Хотел', Operator=ADMIN.ShortName, --Maria --RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif) from RoomNo=(if(G.GuestNum <> 0) then (select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif) from --Maria DBA.SpaArticles as ART,dba.Services,DBA.SpaInvoices as INV,DBA.ServiceCredits as SC,DBA.Guests as G, dba.Admin as ADMIN where ART.InvoiceNo = INV.InvoiceNo and ART.CreditNo = SC.CreditNo and ART.CreditNo <> 0 and INV.KodAdmin = ADMIN.AdminNo and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and ART.ServiceNo = Services.ServiceNo and SC.SpaGuestTypeFlag = 0 and G.GuestNum = SC.WhoPays union select 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, Service=Services.ServiceName, Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif, EdCena=round(abs(ART.Price)*(1+bvrDDSPart*(if ART.TipDDS = 2 then 1 else 0 endif)),4), Stoinost=round(Kol*EdCena,4), StoinostBDDS=round(Kol*abs(ART.Price),4), Discount=ART.Discount, EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=round(Kol*EdCenaWithDisc,4), Klient=INV.Whom,GuestType='СПА Гост', Operator=ADMIN.ShortName, --Maria --RoomNo=-1 from RoomNo='' from --Maria DBA.SpaArticles as ART,dba.Services,DBA.SpaInvoices as INV,DBA.ServiceCredits as SC, dba.Admin as ADMIN where ART.InvoiceNo = INV.InvoiceNo and ART.CreditNo = SC.CreditNo and ART.CreditNo <> 0 and INV.KodAdmin = ADMIN.AdminNo and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and ART.ServiceNo = Services.ServiceNo and SC.SpaGuestTypeFlag = 1 GO --[27] - Спа Процедури IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_SpaPocedures'))) THEN Drop view v_SpaPocedures END IF GO create view DBA.v_SpaPocedures as select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from DBA.SpaRegisters where RegisterName = 'TimeStep') as "Time", minutes(SR.BeginTime,"Time") as Endtime,SW.FullName as Worker,SM.Name as Kabinet, --Maria --(select max(RoomNum) from DBA.ServiceCredits where WhoPays = SG.GuestNum) as Room, (select max(R.RoomName) from DBA.ServiceCredits as SC,DBA.Rooms as R where SC.RoomNum=R.RoomNum and SC.WhoPays = SG.GuestNum) as Room, --Maria 'Приключена' as Status1, (if SR.IfPayed = 0 then 'Неплатена' else if SR.IfPayed = 1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,Status1 || ' ' || Status2 as Status,S.ServiceName as Usluga, SR.ServicePrice,SR.DiscountStn as Discount,DBA.CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) as PriceWithDiscount, (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from DBA.SpaArticles where CreditNo = (select max(SC1.CreditNo) from DBA.ServiceCredits as SC1 where SC1.SpaReservationFk = SR.N)) as PayedSum, (select FullName from DBA.Admin where AdminNo = (select max(KodAdmin) from DBA.Servicecredits where CreditNo = (select max(SC2.CreditNo) from DBA.ServiceCredits as SC2 where SC2.SpaReservationFk = SR.N))) as Admin, SG.Name as GuestName from DBA.SpaReservations as SR,DBA.SpaWorkers as SW,DBA.SpaMedicalOffices as SM,DBA.Services as S,DBA.SpaGuests as SG where SW.N = SR.Spaworker and SM.N = SR.MedicalOffice and S.ServiceNo = SR.MedicalService and SG.GuestNum = SR.GuestFk and SR.GuestType = 1 and BeginDate >= bvrData1 and BeginDate <= bvrData2 and SR.ReservetionType = 2 union all select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from DBA.SpaRegisters where RegisterName = 'TimeStep') as "Time", minutes(SR.BeginTime,"Time") as Endtime,SW.FullName as Worker,SM.Name as Kabinet, --Maria --(select max(RoomNum) from DBA.ServiceCredits where WhoPays = G.GuestNum) as Room, (select max(R.RoomName) from DBA.ServiceCredits as SC,Rooms as R where SC.RoomNum=R.RoomNum and SC.WhoPays = G.GuestNum) as Room, --Maria 'Приключена' as Status1, (if SR.IfPayed = 0 then 'Неплатена' else if SR.IfPayed = 1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,Status1 || ' ' || Status2 as Status,S.ServiceName as Usluga, SR.ServicePrice,SR.DiscountStn as Discount,DBA.CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) as PriceWithDiscount, (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from DBA.SpaArticles where CreditNo = (select max(SC1.CreditNo) from DBA.ServiceCredits as SC1 where SC1.SpaReservationFk = SR.N)) as PayedSum, (select FullName from DBA.Admin where AdminNo = (select max(KodAdmin) from DBA.Servicecredits where CreditNo = (select max(SC2.CreditNo) from DBA.ServiceCredits as SC2 where SC2.SpaReservationFk = SR.N))) as Admin, G.Name as GuestName from DBA.SpaReservations as SR,DBA.SpaWorkers as SW,DBA.SpaMedicalOffices as SM,DBA.Services as S,DBA.Guests as G where SW.N = SR.Spaworker and SM.N = SR.MedicalOffice and S.ServiceNo = SR.MedicalService and G.GuestNum = SR.GuestFk and SR.GuestType = 0 and BeginDate >= bvrData1 and BeginDate <= bvrData2 and SR.ReservetionType = 2 GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_SpaPocedures' and ColName='Room' GO --[28] - Спа Резервации IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_SpaReservations'))) THEN Drop view v_SpaReservations END IF GO create view DBA.v_SpaReservations as select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from DBA.SpaRegisters where RegisterName = 'TimeStep') as "Time", minutes(SR.BeginTime,"Time") as Endtime,SW.FullName as Worker,SM.Name as Kabinet, --Maria --(select max(RoomNum) from DBA.ServiceCredits where WhoPays = SG.GuestNum) as Room, (select max(R.RoomName) from DBA.ServiceCredits as SC, Rooms as R where SC.RoomNum = R.RoomNum and SC.WhoPays = SG.GuestNum) as Room, --Maria (if SR.ReservetionType = 1 then 'Резервирана' else if SR.ReservetionType = 2 then 'Приключена' else 'Отменена' endif endif) as Status1,(if SR.IfPayed = 0 then 'Неплатена' else if SR.IfPayed = 1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,Status1 || ' ' || Status2 as Status,S.ServiceName as Usluga, SR.ServicePrice,SR.DiscountStn as Discount,DBA.CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) as PriceWithDiscount, (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from DBA.SpaArticles where CreditNo = (select max(SC1.CreditNo) from DBA.ServiceCredits as SC1 where SC1.SpaReservationFk = SR.N)) as PayedSum, (select FullName from DBA.Admin where AdminNo = (select max(KodAdmin) from DBA.Servicecredits where CreditNo = (select max(SC2.CreditNo) from DBA.ServiceCredits as SC2 where SC2.SpaReservationFk = SR.N))) as Admin, SG.Name as GuestName from DBA.SpaReservations as SR,DBA.SpaWorkers as SW,DBA.SpaMedicalOffices as SM,DBA.Services as S,DBA.SpaGuests as SG where SW.N = SR.Spaworker and SM.N = SR.MedicalOffice and S.ServiceNo = SR.MedicalService and SG.GuestNum = SR.GuestFk and SR.GuestType = 1 and BeginDate >= bvrData1 and BeginDate <= bvrData2 union all select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from DBA.SpaRegisters where RegisterName = 'TimeStep') as "Time", minutes(SR.BeginTime,"Time") as Endtime,SW.FullName as Worker,SM.Name as Kabinet, --Maria --(select max(RoomNum) from DBA.ServiceCredits where WhoPays = G.GuestNum) as Room, (select max(R.RoomName) from DBA.ServiceCredits as SC, Rooms as R where SC.RoomNum=R.RoomNum and SC.WhoPays = G.GuestNum) as Room, --Maria (if SR.ReservetionType = 1 then 'Резервирана' else if SR.ReservetionType = 2 then 'Приключена' else 'Отменена' endif endif) as Status1,(if SR.IfPayed = 0 then 'Неплатена' else if SR.IfPayed = 1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,Status1 || ' ' || Status2 as Status,S.ServiceName as Usluga, SR.ServicePrice,SR.DiscountStn as Discount,DBA.CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) as PriceWithDiscount, (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from DBA.SpaArticles where CreditNo = (select max(SC1.CreditNo) from DBA.ServiceCredits as SC1 where SC1.SpaReservationFk = SR.N)) as PayedSum, (select FullName from DBA.Admin where AdminNo = (select max(KodAdmin) from DBA.Servicecredits where CreditNo = (select max(SC2.CreditNo) from DBA.ServiceCredits as SC2 where SC2.SpaReservationFk = SR.N))) as Admin, G.Name as GuestName from DBA.SpaReservations as SR,DBA.SpaWorkers as SW,DBA.SpaMedicalOffices as SM,DBA.Services as S,DBA.Guests as G where SW.N = SR.Spaworker and SM.N = SR.MedicalOffice and S.ServiceNo = SR.MedicalService and G.GuestNum = SR.GuestFk and SR.GuestType = 0 and BeginDate >= bvrData1 and BeginDate <= bvrData2 GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_SpaReservations' and ColName='Room' GO --[29] - Справка минибар IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_MinibarReport'))) THEN Drop view v_MinibarReport END IF GO create view DBA.v_MinibarReport --Maria --as select Room=SC.RoomNum, as select Room=R.RoomName, --Maria /* Room=(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),*/ Suma=ART.Qty*(ART.Price*(1+bvrDDSPart*(if ART.TipDDS = 2 then 1 else 0 endif))) from DBA.Articles as ART,dba.Services,DBA.Invoices as INV,DBA.ServiceCredits as SC,DBA.Guests as G, DBA.Rooms as R where --Maria SC.RoomNum = R.RoomNum and --Maria ART.InvoiceNo = INV.InvoiceNo and ART.CreditNo = SC.CreditNo and SC.GuestNo = G.GuestNum and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and ART.ServiceNo = Services.ServiceNo and ART.ServiceNo = 1006 //[02.04.02] nt versiq /* Starata versiq na tova view select Room=SC.RoomNum, Suma=round((if TipDDS=2 then Ar.TotalSum*(1+.2) else Ar.TotalSum endif),2) from "dba".Articles as Ar,"dba".ServiceCredits as SC,"dba".Book as B where Ar.CreditNo=SC.CreditNo and SC.Service=1006 and SC.DateCreditDt>=bvrData1 and SC.DateCreditDt<=bvrData2 and SC.DateCreditDt>B.DateRegDt and B.DateRegDt>=SC.DateCreditDt */ Go --[30] - Статистика по гости IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_Statistics'))) THEN Drop view v_Statistics END IF GO create view DBA.v_Statistics as select CountryNo=Guests.NativeCountry, Country=Countries.Description, DogovorNo=Guests.Contract, Dogovor=Contracts.ShortName, BK.GuestNum, --Maria --Room=BK.LastRoom, Room=BK.LastRoomName, --Maria RoomCat=Classes.ShortName, Gost=Guests.Name, ArriveDate, LeaveDate, StayDays=DBA.DateIntersection(bvrData1,bvrData2,ArriveDate,LeaveDate), NewComer=if(DBA.DateIntersection(bvrData1,bvrData2,ArriveDate,LeaveDate) > 0) or((DBA.DateIntersection(bvrData1,bvrData2,ArriveDate,LeaveDate) = 0) and(bvrData2 = ArriveDate)) then 1 else 0 endif, WalkInEldNights=if Guests.AdultType = 0 and Guests.Contract = 0 then StayDays else 0 endif, WalkInChiNights=if Guests.AdultType > 0 and Guests.Contract = 0 then StayDays else 0 endif, WalkInTotNights=WalkInEldNights+WalkInChiNights, FitEldNights=if Guests.AdultType = 0 and Guests.Contract <> 0 then StayDays else 0 endif, FitChiNights=if Guests.AdultType > 0 and Guests.Contract <> 0 then StayDays else 0 endif, FitTotNights=FitEldNights+FitChiNights, EldNights=WalkInEldNights+FitEldNights, ChiNights=WalkInChiNights+FitChiNights, TotNights=WalkInTotNights+FitTotNights, WalkInEld=Sign(WalkInEldNights)*NewComer, WalkInChi=Sign(WalkInChiNights)*NewComer, WalkInTot=WalkInEld+WalkInChi, FitEld=Sign(FitEldNights)*NewComer, FitChi=Sign(FitChiNights)*NewComer, FitTot=FitEld+FitChi, TotEld=WalkInEld+FITEld, TotChi=WalkInChi+FITChi, TotTot=TotEld+TotChi from DBA.v_ArriveStayLeaveGuestBook as BK,dba.Guests,dba.Countries,DBA.Contracts, DBA.Rooms,DBA.Classes where Guests.NativeCountry = Countries.CountryKod and /* and NewComer>0*/ Guests.GuestNum = BK.GuestNum and Guests.Contract = Contracts.ContractNo and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and Guests.Status <> 0 and StayDays <> 0 and BK.LastRoom = Rooms.RoomNum and Rooms.RoomCat = Classes.Class //[v 00.07.20] //[v 31.10.02] niky - dobaven e filtyr za grupa dogovori //[v 28.01.2004] Jorko opraveni vuzrastite na decata GO --[31] - Статус на стаи за период IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_RoomStatusForPeriod'))) THEN Drop view v_RoomStatusForPeriod END IF GO create view DBA.v_RoomStatusForPeriod as --Maria --select RoomNum=R.RoomNum, select RoomNum=R.RoomName, --Maria Class=C.ShortName, StartDate=RSFP.StartDate, EndDate=dateadd(day,1,RSFP.EndDate), Status=RS.Description, Komentar=RSFP.Komentar from DBA.Rooms as R,DBA.RoomStatusForPeriod as RSFP,DBA.Classes as C,DBA.RoomStatus as RS where R.RoomCat = C.Class and RS.RoomStatusNo = RSFP.NStatus and R.RoomNum = RSFP.NRoom and RSFP.NStatus <> 0 and bvrData1 <= RSFP.EndDate and bvrData1 >= RSFP.StartDate union all --Maria --select RoomNum=R.RoomNum, select RoomNum=R.RoomName, --Maria Class=C.ShortName, StartDate=null, EndDate=null, Status=RS.Description,Komentar='' from DBA.Rooms as R,DBA.RoomStatus as RS,DBA.Classes as C where R.Status = RS.RoomStatusNo and R.RoomCat = C.Class and (not R.RoomNum = any(select RoomNum from DBA.v_RoomsFromRoomStatusForPeriod)) group by RoomNum,Class,Status,StartDate,EndDate,Komentar order by RoomNum asc GO --[32] - Сторно операции IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ServiceCorrections'))) THEN Drop view v_ServiceCorrections END IF GO create view DBA.v_ServiceCorrections as select Service=Services.ServiceName, ServicesNum=ServiceCredits.NumberServices, ServiceSngPrice=if ServiceCredits.KodCurrency = 2 then (if(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1) then SpaDayRates.ExchangeRate else DayRates.ExchangeRate endif)*ServiceCredits.SumCredit else ServiceCredits.SumCredit endif,AllSum=ServiceCredits.NumberServices*ServiceSngPrice, GuestName=Guests.Name, Account=Guests.GuestNum, --Maria --RoomNo=BK.LastRoom, RoomNo=BK.LastRoomName, --Maria AdminName=Admin.ShortName from dba.ServiceCredits,dba.Services,dba.Guests,DBA.v_ArriveStayLeaveGuestBook as BK, DBA.admin,DBA.DayRates,DBA.SpaDayRates where (ServiceCredits.NumberServices < 0 or ServiceCredits.SumCredit < 0) and ServiceCredits.Service = Services.ServiceNo and ServiceCredits.DateCreditDt = DayRates.DateDt and ServiceCredits.KodCurrency = DayRates.KodCurrency and ServiceCredits.DateCreditDt = SpaDayRates.DateDt and ServiceCredits.KodCurrency = SpaDayRates.KodCurrency and ServiceCredits.DateCreditDt >= bvrData1 and ServiceCredits.DateCreditDt <= bvrData2 and ServiceCredits.GuestNo = Guests.GuestNum and ServiceCredits.GuestNo = BK.GuestNum and ServiceCredits.KodAdmin = Admin.AdminNo and ((ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1)) GO update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_ServiceCorrections' and ColName='RoomNo' GO --[33] - Телефонни разговори IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_TelTalks'))) THEN Drop view v_TelTalks END IF GO create view DBA.v_TelTalks as select CallDate,CallTime,IntLine,ExtLine,DialNumber,Duration,PulseCount,CallPrice,Notes, --Maria --RoomNum from RoomName from --Maria DBA.Rooms,DBA.PBXCallsLog where Rooms.PBXNumber like string('%,',PBXCallsLog.IntLine,',%') //[v. 01.06.15] GO IF ((select count(*) from Relations where TableName='v_TelTalks' and ColName='RoomNum')>0) THEN update Relations set ColName='RoomName' where TableName='v_TelTalks' and ColName='RoomNum'; END IF Go --[34] - Телефонни разговори за гост IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_TelTalksGuests'))) THEN Drop view v_TelTalksGuests END IF GO create view DBA.v_TelTalksGuests as select GuestNum,Guests.Name, CallDate,CallTime, IntLine,ExtLine, DialNumber,Duration, PulseCount,CallPrice, --Maria --ServiceCredits.RoomNum from Rooms.RoomName from --Maria DBA.Rooms,DBA.PBXCallsLog,DBA.ServiceCredits,DBA.Guests where Rooms.PBXNumber like string('%,',PBXCallsLog.IntLine,',%') and PBXCallsLog.N = ServiceCredits.PBXLogN and ServiceCredits.WhoPays = Guests.GuestNum --Maria and ServiceCredits.RoomNum = Rooms.RoomNum //[v 01.06.15] GO IF ((select count(*) from Relations where TableName='v_TelTalksGuests' and ColName='RoomNum')>0) THEN update Relations set ColName='RoomName' where TableName='v_TelTalksGuests' and ColName='RoomNum'; END IF GO --[35] - Туристи чрез тур-оператори IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_TouristsWithTur'))) THEN Drop view v_TouristsWithTur END IF GO create view DBA.v_TouristsWithTur as select Cl.Class as RoomTypeNo, Cl.ShortName as RoomType, --Maria --Rooms.RoomNum as RoomNo, Rooms.RoomName as RoomNo, --Maria Guests.GuestNum as Account, Contracts.ShortName as Agent, Guests.CarNumber as CarNumber, Guests.SafeNo as Safe, Guests.Name as Ime, Guests.BirthDateDt as BirthDate, Rsv.ReserveNo as ReserveNo, if Guests.AdultType = 0 then 1 else 0 endif as Elderly, if Guests.AdultType > 0 then 1 else 0 endif as Children, Book.PriceKind as PriceKind, ASL.ArriveDate as ArriveDate, ASL.LeaveDate as LeaveDate, ASL.NumNights as NightsToStay, Book.Price as PriceNight, NightsToStay*PriceNight as AllSuma, Guests.Balance as Balance, Guests.VaulcherNo as VaucherNo from DBA.Reserve as Rsv,DBA.Classes as Cl,dba.Contracts, dba.Guests,DBA.Book,DBA.Rooms,DBA.v_ArriveStayLeaveGuestBook as ASL where Book.DateRegDt <= bvrData2 and Book.DateRegDt+Book.NumLodgings > bvrData2 and Book.Room = Rooms.RoomNum and Rooms.RoomCat = Cl.Class and Book.Guest = Guests.GuestNum and Guests.Reservation *= Rsv.ReserveNo and Guests.Contract is not null and Guests.Contract = Contracts.ContractNo and Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and Book.Guest = ASL.GuestNum Go update Relations set FilterList='select RoomName from Rooms where Status <> 1 and Status <> 3', DftFilter='' where TableName='v_TouristsWithTur' and ColName='RoomNo' GO --[36] - Услуги IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_Services'))) THEN Drop view v_Services END IF GO create view /*SQL-1003-0002689930*/ DBA.v_Services as select Account=g.GuestNum, ServiceDate=DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY'), /*Maria +, 1728*/ ServiceDateTime=sc.RealDateTime, /*Maria -*/ Service=s.ServiceName, GuestName=g.Name, Country=Countries.Description, Agent=Contracts.ShortName, PayedSum=sc.PayedServices*sc.SumCredit, Balance=(sc.PayedServices-sc.NumberServices)*sc.SumCredit, AdminName=Admin.ShortName, --Maria --Room=ASL.LastRoom, Room=ASL.LastRoomName, --Maria Currency=(if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then(select Spacur.Symbol from DBA.SpaCurrencies as Spacur where sc.KodCurrency = Spacur.KodCurrency) else cur.Symbol endif), ExplanationColumn=if(sc.PBXLogN is not null or sc.KodAdmin = 1002) then 'PBX' else if(select count(A.AdminNo) from dba.Admin as A where A.AdminGroup = 0 and A.AdminNo = sc.KodAdmin) > 0 then 'POS' else if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then 'СПА' else null endif endif endif from dba.ServiceCredits as sc, DBA.Services as s, DBA.Contracts left outer join DBA.Guests as g on Contracts.ContractNo = g.Contract, DBA.Countries, DBA.Admin, DBA.Currencies as cur, DBA.v_ArriveStayLeaveGuestBook as ASL where sc.Service = s.ServiceNo and sc.GuestNo = g.GuestNum and Contracts.GroupNo = any(select distinct(showtype) from dba.GrContracts where GrNo = -1) and g.CountryArrive = Countries.CountryKod and sc.KodAdmin = Admin.AdminNo and ASL.GuestNum = g.GuestNum and sc.KodCurrency = cur.KodCurrency and ((sc.SPaReservationFk = -1) or(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1)) /*Opravia filtriraneto na data na spravkata*/ //[17.03.2005] Safa, optimizatsia //[21.12.2007] Maria, dobavena kolonata ServiceDateTime, realnata data i chas GO --[37] - ХОТЕЛСКИ РЕГИСТЪР IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OfficialRegister'))) THEN Drop view v_OfficialRegister END IF GO create view DBA.v_OfficialRegister as select G.GuestNum, G.OfficialNum, /*Maria + 1696, dobavena kolona EGN*/ G.EGN, /*Maria -*/ G.VaulcherNo, ArriveDate,Signature='', G.Name, BirthCountry=CO.Description, BirthData=String(BirthDateDt,', ',G.City), PassportData=String(G.PassportSeria,G.PassportNumber), /*PassportData=String(G.PassportSeria,G.PassportNumber,', ',G.PassportDateDt,' ',G.PassportWhere),*/ Age=Years(BirthDateDt,current date), ImaTaksa=DBA.IsResortTaxed(BirthCountry,BirthDateDt,G.Sex), AddressData=G.Address, RoomNo=LastRoom, --Maria RoomName=LastRoomName, --Maria BNights=Days(BK.ArriveDate,BK.LeaveDate), TNights=ImaTaksa*BNights, BedNights=DBA.Restr(BNights), TaxNights=DBA.Restr(TNights), LeaveDate=convert(date,Days(BK.ArriveDate,BedNights)), MPSData=String(G.CarNumber,' ',G.CarModel), G.Reservation, G.Balance, Pol=if G.Sex <> '' then G.Sex /* v[17.03.2005] Jorko dobavena kolona PaspValDate*/ else '' endif,Realpol=if G.Sex = 'M' then 'М' else 'Ж' endif, G.PaspValDate, Sex=if Pol <> '' then Realpol else '' endif from DBA.v_ArriveStayLeaveGuestBook as BK,DBA.Guests as G,DBA.Countries as CO,DBA.Contracts as Ctr where BK.GuestNum = G.GuestNum and G.NativeCountry = CO.CountryKod and Mod(RoomNo,bvrOfficDivider) < bvrOfficReminder and ArriveDate >= bvrData1 and ArriveDate <= bvrData2 and ArriveDate <> LeaveDate and G.OfficialNum >= 0 and G.Contract = Ctr.ContractNo and (Ctr.ContractNo = 0 or Ctr.ShortName like bvrOfficContrIncl) and G.HasData = 1 GO IF ((select count(*) from Relations where TableName='v_OfficialRegister' and ColName='RoomNo')>0) THEN update Relations set ColName='RoomName' where TableName='v_OfficialRegister' and ColName='RoomNo'; END IF GO --[38] - ХОТЕЛСКИ РЕГИСТЪР - ДАНЪЧНИ ФАКТУРИ IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OfficialRegisterDanFak'))) THEN Drop view v_OfficialRegisterDanFak END IF GO create view DBA.v_OfficialRegisterDanFak as select G.GuestNum, G.OfficialNum, ArriveDate,Signature='', LeaveDate=convert(date,Days(BK.ArriveDate,BedNights)), G.Name, BirthCountry=CO.Description, BirthData=String(BirthDateDt,', ',G.City), PassportData=String(G.PassportSeria,G.PassportNumber), Age=Years(BirthDateDt,current date), ImaTaksa=DBA.IsResortTaxed(BirthCountry,BirthDateDt,G.Sex), AddressData=G.Address, RoomNo=LastRoom, --Maria RoomName=LastRoomName, --Maria BNights=Days(BK.ArriveDate,BK.LeaveDate), TNights=ImaTaksa*BNights, BedNights=DBA.Restr(BNights), TaxNights=DBA.Restr(TNights), MPSData=String(G.CarNumber,' ',G.CarModel), G.Reservation, G.Balance from DBA.v_ArriveStayLeaveGuestBook as BK, DBA.Guests as G, DBA.Countries as CO, DBA.Contracts as Ctr where BK.GuestNum = G.GuestNum and G.NativeCountry = CO.CountryKod and Mod(RoomNo,bvrOfficDivider) < bvrOfficReminder and ArriveDate >= bvrData1 and ArriveDate <= bvrData2 and G.OfficialNum >= 0 and G.Contract = Ctr.ContractNo and (G.GuestNum = any(select GuestNum from DBA.v_OfficialRegGuests) or G.Contract <> 0) and G.HasData = 1 -- v.2 [16.08.2007] - za Lion, Yanko, dobaveno or G.Contract <> 0) Go IF ((select count(*) from Relations where TableName='v_OfficialRegisterDanFak' and ColName='RoomNo')>0) THEN update Relations set ColName='RoomName' where TableName='v_OfficialRegisterDanFak' and ColName='RoomNo'; END IF GO --[39] - ХОТЕЛСКИ РЕГИСТЪР - Реализирани нощувки IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OfficialRegisterRealNights'))) THEN Drop view v_OfficialRegisterRealNights END IF GO create view DBA.v_OfficialRegisterRealNights as select G.GuestNum, G.OfficialNum, G.VaulcherNo, ArriveDate,Signature='', G.Name, BirthCountry=CO.Description, BirthData=String(BirthDateDt,', ',G.City), PassportData=String(G.PassportSeria,G.PassportNumber), /*PassportData=String(G.PassportSeria,G.PassportNumber,', ',G.PassportDateDt,' ',G.PassportWhere),*/ Age=Years(BirthDateDt,current date), ImaTaksa=DBA.IsResortTaxed(BirthCountry,BirthDateDt,G.Sex), AddressData=G.Address, RoomNo=LastRoom, --Maria RoomName=LastRoomName, --Maria BNights=Days(if bvrData1 > BK.ArriveDate then bvrData1 else BK.ArriveDate endif,if bvrData2 > BK.LeaveDate then BK.LeaveDate else bvrData2 endif), TNights=ImaTaksa*BNights, BedNights=DBA.Restr(BNights), TaxNights=DBA.Restr(TNights), LeaveDate=BK.LeaveDate, --convert(date,Days(BK.ArriveDate,BedNights)), MPSData=String(G.CarNumber,' ',G.CarModel), G.Reservation, G.Balance, Pol=if G.Sex <> '' then G.Sex /* v[17.03.2005] Jorko dobavena kolona PaspValDate*/ --and ArriveDate >= bvrData1 --and bvrData2 >=ArriveDate else '' endif,Realpol=if G.Sex = 'M' then 'М' else 'Ж' endif, G.PaspValDate, Sex=if Pol <> '' then Realpol else '' endif from DBA.v_ArriveStayLeaveGuestBook as BK, DBA.Guests as G, DBA.Countries as CO, DBA.Contracts as Ctr where BK.GuestNum = G.GuestNum and G.NativeCountry = CO.CountryKod and Mod(RoomNo,bvrOfficDivider) < bvrOfficReminder and ArriveDate <= bvrData2 and bvrData1 <= BK.LeaveDate and ArriveDate <> BK.LeaveDate and G.OfficialNum >= 0 and G.Contract = Ctr.ContractNo and (Ctr.ContractNo = 0 or Ctr.ShortName like bvrOfficContrIncl) and G.HasData = 1 GO IF ((select count(*) from Relations where TableName='v_OfficialRegisterRealNights' and ColName='RoomNo')>0) THEN update Relations set ColName='RoomName' where TableName='v_OfficialRegisterRealNights' and ColName='RoomNo'; END IF GO --[40] - Членове на VIP клуб IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_MemberOfVipClub'))) THEN Drop view v_MemberOfVipClub END IF GO create view DBA.v_MemberOfVipClub as select ContrName=c.FullName, ContrDep=Max(DBA.CalcContractDepositSum(c.ContractNo)), PredSaldo=CurrSaldo+SumForPer, CurrSaldo=(-1)*c.Balance, SumForPer=MAX(DBA.CalcUseSuma(bvrdata1,bvrdata2,c.ContractNo)), LastDataReg=(select Max(dateregDt) from dba.book where ContractPays = c.ContractNo), --Maria --LastRoomReg=(select Max(Room) from dba.book where ContractPays = c.ContractNo and DateRegDt = LastDataReg), LastRoomReg=(select Max(R.RoomName) from dba.book as b,dba.rooms as r where b.room = r.roomnum and ContractPays = c.ContractNo and DateRegDt = LastDataReg), --Maria GroupContract=(select FullName from dba.Contracts where ContractNo = c.ContractNode) from dba.ResMoney as rm,dba.contracts as c where rm.ContractNo = c.ContractNo and c.contractNo <> 0 and c.IsGroupContract <> 1 and c.GroupNo = 1 group by c.ContractNo,c.FullName,c.Balance,c.ContractNode GO ------------------------------------------------------Triggers--------------------------------------------------------------------------------------- IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigBookIns'))) THEN Drop trigger TrigBookIns END IF GO create trigger //Tozi skript e za promqna na trigerite v tablicata Book vyv vryska s na`islqwaneto na noshtuvki ili po`iwki po dogovor ako //te sa ot paketa uslugi na dogovor TrigBookIns after insert order 1 on DBA.Book referencing new as BookNew for each row begin declare tipv char; declare Ktr integer; declare @Date date; declare @AdminName varchar(10); // message '-- TrigBookIns...' type info to console; set @Date=FloatToDate(BookNew.DateReg); update Guests set DateEnterBG = @Date where GuestNum = BookNew.Guest; update Guests set Status = 1 where GuestNum = BookNew.Guest; select TipVaulcher,Contract into TipV, Ktr from guests where GuestNum = BookNew.Guest; //Pravi proverka za dogovoi po tip vaucher i promenq ContractPays ako ima vaucheri ot tip (A, I) if Ktr <> 0 and TipV in( 'A','I') then update Book set ContractPays = Ktr,WhoPays = 0 where Book.RegNum = BookNew.RegNum end if; //Pravi proverka za dogovoi po tip vaucher i promenq ContractPays ako ima vaucheri ot tip C if(Ktr <> 0) and((select count(*) from ServicesForContract where contractNo = Ktr and(ServiceNo in( -1,-2,-5,-6) )) <> 0) then update Book set ContractPays = Ktr,WhoPays = 0 where Book.RegNum = BookNew.RegNum end if; update Book set DateRegDt = @Date where RegNum = BookNew.Regnum; call SaveDateRate(BookNew.KodCurrency,@Date); call CalcBoardPlanBook(BookNew.RegNum); if BookNew.WhoPays <> 0 then call CalcBalance(BookNew.WhoPays) end if; if BookNew.ContractPays <> 0 then call CalcBalanceContract(BookNew.ContractPays) end if; //Otbeliazwa zapisa wuw historito na gosta select ShortName into @AdminName from Admin where AdminNo = BookNew.KodAdmin; // update Guests set History = History || '(' || current date || ' / ' || @AdminName || ') Начислени ' || --Maria + --BookNew.NumLodgings || ' Нощувка в стая ' || BookNew.Room || "char"(13) where BookNew.NumLodgings || ' Нощувка в стая ' || (select RoomName from Rooms where RoomNum = BookNew.Room) || "char"(13) where --Maria + GuestNum = BookNew.Guest end //[v 01.03.09] GO IF (EXISTS(Select * from sys.systrigger where LCase(trigger_name) = LCase('TrigBookUpd'))) THEN Drop trigger TrigBookUpd END IF GO create trigger TrigBookUpd after update order 2 on DBA.Book referencing old as BKOLD new as BKNEW for each row begin atomic declare @DateReg date; declare @NumLodgin integer; declare @AdminName varchar(10); declare @NewHistory varchar(150); declare @Symbol varchar(6); declare @CardN integer; declare @Flag integer; select count(*) into @Flag from Book where Guest = BKNEW.Guest and Staying < 3; // @Flag =0 kogato e napusnal 1 ako ima niakavo nastaniavane if((BKNEW.Staying = 3) and(@Flag = 0)) then // Napusnal update Guests set Status = 2 where GuestNum = BKNEW.Guest end if; /* message '-- TrigBookUpd...'; if BKNEW.Staying=3 then // Napusnal update Guests set Status=2 where GuestNum=BKNEW.Guest; select cardN into @CardN from Guests as G where G.GuestNum=BKNEW.Guest; if((@CardN<>0) or(@CardN is not null)) and((select Guests.Balance from Guests where Guests.GuestNum=BKNEW.Guest)<0) then message 'Ima depozit i Klientska karta...'; message 'N na Karta -> '+cast(@CardN as varchar); update GuestCardInfo set Depozit=(select ABS(Balance) from Guests where GuestNum=BKNEW.Guest) where GuestCardInfoN=@CardN end if end if ; */ set @DateReg=FloatToDate(BKNEW.DateReg); if BKNEW.DateRegDt <> @DateReg then update Book set DateRegDt = @DateReg where RegNum = BKNEW.Regnum end if; if BKOLD.WhoPays <> 0 then call CalcBalance(BKOLD.WhoPays) end if; if BKNEW.WhoPays <> 0 then call CalcBalance(BKNEW.WhoPays) end if; if BKOLD.ContractPays <> 0 then call CalcBalanceContract(BKOLD.ContractPays) end if; if BKNEW.ContractPays <> 0 then call CalcBalanceContract(BKNEW.ContractPays) end if; call CalcBoardPlanBook(BKNEW.RegNum); // //Otbeliazva zapisa vuv Historito na gosta select ShortName into @AdminName from Admin where AdminNo = BKNEW.KodAdmin; // if BKNEW.NumLodgings <> BKOLD.NumLodgings then update Guests set History = History || '(' || current date || ' / ' || current time || ' / ' || @AdminName || ') Променен престоя в стая ' || --Maria + --BKOLD.Room || '/' || BKOLD.DateRegDt || ' от ' || (select RoomName from Rooms where RoomNum = BKOLD.Room) || '/' || BKOLD.DateRegDt || ' от ' || --Maria + BKOLD.NumLodgings || ' нощувки на ' || BKNEW.NumLodgings || ' нощувки' || "char"(13) where GuestNum = BKNEW.Guest end if; // if BKNEW.Price <> BKOLD.Price then select Symbol into @Symbol from Currencies where KodCurrency = BKOLD.KodCurrency; set @NewHistory='(' || current date || ' / ' || current time || '/' || @AdminName || ') Променена цената в тая ' || --Maria + --BKOLD.Room || '/' || BKOLD.DateRegDt || ' от ' || BKOLD.Price || ' ' || @Symbol; (select RoomName from Rooms where RoomNum = BKOLD.Room) || '/' || BKOLD.DateRegDt || ' от ' || BKOLD.Price || ' ' || @Symbol; --Maria + select Symbol into @Symbol from Currencies where KodCurrency = BKNEW.KodCurrency; set @NewHistory=@NewHistory || ' на ' || BKNEW.Price || ' ' || @Symbol || "char"(13); message @NewHistory type info to console; update Guests set History = History || @NewHistory where GuestNum = BKNEW.Guest end if; // Otbeliazva v historito, ako se prehvurlia smetka if(BKNEW.WhoPays <> BKOLD.WhoPays) and BKNEW.WhoPays <> 0 then select Name into @NewHistory from Guests where GuestNum = BKNEW.WhoPays; update Guests set History = History || '(' || current date || '/' || current time || ' / ' || @AdminName || ') Прехвърлена нощувка в стая ' || --Maria + --BKOLD.Room || '/ ' || BKOLD.DateRegDt || ' към гост "' || @NewHistory || '"' || "char"(13) where GuestNum = BKOLD.WhoPays; (select RoomName from Rooms where RoomNum = BKOLD.Room) || '/ ' || BKOLD.DateRegDt || ' към гост "' || @NewHistory || '"' || "char"(13) where GuestNum = BKOLD.WhoPays; --Maria + //Promenia historyto i na gosta, na kojto se prehvurlia select Name into @NewHistory from Guests where GuestNum = BKOLD.WhoPays; update Guests set History = History || '(' || current date || '/' || current time || ' / ' || @AdminName || ') Прехвърлена нощувка в стая ' || --Maria + --BKOLD.Room || '/ ' || BKOLD.DateRegDt || ' от гост "' || @NewHistory || '"' || "char"(13) where GuestNum = BKNEW.WhoPays (select RoomName from Rooms where RoomNum = BKOLD.Room) || '/ ' || BKOLD.DateRegDt || ' от гост "' || @NewHistory || '"' || "char"(13) where GuestNum = BKNEW.WhoPays end if --Maria + end //[v 12.07.2004] Ivo ---------------------------------------------------------------------------------------------------------------------------------------------------- GO Update Countries set Active=1 where CountryKod=-1 Go IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('BookReserveGuest'))) THEN Drop procedure BookReserveGuest END IF GO create function DBA.BookReserveGuest(in @DateReg real,in @ReserveNo integer,in @RoomNo integer,in @RN integer,in @NastKind integer,in @HumanKind integer,in @PriceKindNo integer,in @AdultType integer,in @KodAdmin integer) returns varchar(100) // Nastaniava edin gost ot rezervacia @ReserveNo v staia @RoomNo. // // @DateReg - data za registracia // @ReserveNo - nomer rezervacia // @RoomNo - nomer staia // @RN - ReservePlan.N ili Book.RegNum // @NastKind: // 0: Nastaniavane v nezaeta staia - staiata se prispada ot rezervaciata // 1: Donastaniavane (v zaeta staia) - staiata ne se prispada ot rezervaciata // @PriceKindNo - cenorazpisa, po koito se nastaniava // @AdultType - 0 - vuzrasten, 1 - dete // Return: // '' - OK // - Not OK begin atomic declare @R varchar(100); declare @GuestN integer; declare @Days integer; declare @GuestName varchar(40); declare @ContractNo integer; declare @BoardPlanDef integer; declare @Price decimal(12,2); declare @Currency integer; declare @S char(100); declare @RoomCat integer; declare @BookN integer; declare @RPlan integer; declare @TariffNo integer; declare @Description varchar(30); declare @IsEgnEnch integer; --Maria + declare @RoomName varchar(5); set @RoomName=(select RoomName from Rooms where RoomNum = @RoomNo); --Maria + if @HumanKind = 1 then set @IsEgnEnch=1 else set @IsEgnEnch=0 end if; set @R=''; if @NastKind = 0 then set @RPlan=@RN else set @RPlan=0 end if; // //Izbor na gost: set @GuestN=null; select MIN(GuestNum) into @GuestN from Guests as G,ReservePlan as RP where G.ReservePlanNo = RP.N and RP.RoomNo = @RoomNo and G.Status = 0 and G.Reservation = @ReserveNo and G.ResPriceKind = @PriceKindNo and G.AdultType = @AdultType; if @GuestN is null then select MIN(GuestNum) into @GuestN from Guests as G,ReservePlan as RP where G.ReservePlanNo = RP.N and RP.RoomNo = @RoomNo and G.Status = 0 and G.Reservation = @ReserveNo and G.AdultType = @AdultType end if; if @GuestN is null then select MIN(GuestNum) into @GuestN from Guests as G where G.Status = 0 and G.Reservation = @ReserveNo and G.ReservePlanNo = 0 and G.AdultType = @AdultType end if; if @GuestN is null then select MIN(GuestNum) into @GuestN from Guests as G where G.Status = 0 and G.Reservation = @ReserveNo and G.ReservePlanNo = 0 and HasData = 0 end if; if @GuestN is null then // Create new guest; set @GuestN=GetCounter('GuestCounter'); select Name || ' ' || @GuestN,Days,ContractNo,BoardPlanDef into @GuestName, @Days,@ContractNo, @BoardPlanDef from Reserve where ReserveNo = @ReserveNo; insert into Guests( GuestNum,HumanKind,Name,Reservation,HasData, Contract,BoardPlanDef,ResNumLogin,ResPriceKind,Status,ReservePlanNo,AdultType,IsEgnEnch) values( @GuestN,@HumanKind,@GuestName,@ReserveNo,0, @ContractNo,@BoardPlanDef,@Days,@PriceKindNo,0,@RPlan,@AdultType,@IsEgnEnch) else // update guest update Guests set HumanKind = @HumanKind,AdultType = @AdultType,IsEgnEnch = @IsEgnEnch where GuestNum = @GuestN end if; // //Izbor na cena: select RoomCat into @RoomCat from Rooms where RoomNum = @RoomNo; select HumanKind into @HumanKind from Guests where GuestNum = @GuestN; if @NastKind = 0 then select NumDays into @Days from ReservePlan where N = @RN else select NumLodgings into @Days from Book where RegNum = @RN end if; // select PriceAll,KodCurrency,TariffNo into @Price,@Currency, @TariffNo from PriceList,Registers where ClassNo = @RoomCat and PriceKindNo = @PriceKindNo and AdultType = @AdultType and HumanKind = @HumanKind and SeasonNo = Registers.IntStojnost and Registers.RegisterName = 'ActivePriceSeason'; // if @TariffNo is null then select Description into @Description from PriceKinds where PriceKind = @PriceKindNo; set @R='Не е зададена цена за :' || "char"(13) || "char"(13) || 'ценоразпис "' || @Description || '"' || ',' || ' за стая N: ' || --Maria + --@RoomNo || ', '; @RoomName || ', '; --Maria + select Description into @Description from HumanKinds where HumanKindNo = @HumanKind; set @R=@R || @Description || ', '; select Description into @Description from AdultTypes where AdultNo = @AdultType; set @R=@R || @Description || '!'; return(@R) end if; // set @BookN=GetCounter('BookCounter'); insert into Book( RegNum,DateReg,NumLodgings,PayedLodgings,Payed,Staying,PriceKind, KodAdmin,Room,Price,Guest,WhoPays,KodCurrency,IsRest) values( @BookN,@DateReg,@Days,0,0,1,@PriceKindNo, @KodAdmin,@RoomNo,@Price,@GuestN,@GuestN,@Currency,0) ; update Rooms set Buzy = 2,NumPeople = NumPeople+1 where RoomNum = @RoomNo; update Guests set Status = 1,ResPriceKind = @PriceKindNo,IsEgnEnch = @IsEgnEnch, /*Maria +*/ --ReservePlanNo = if @NastKind = 0 then @RN else ReservePlanNo endif where ReservePlanNo = if @NastKind = 0 then @RN else ReservePlanNo endif,NativeCountry=(if @HumanKind=1 then 0 else -1 endif) where /*Maria -*/ GuestNum = @GuestN; for f as curs scroll cursor for select ServiceNo as @ServiceNo,Price as @ServicePrice,KodCurrency as @ServiceCurrency from Prices where TariffNo = @TariffNo do message 'RegNo=',@BookN,'ServiceNo=',@ServiceNo type info to console; insert into Packages( RegNo,ServiceNo,Price,KodCurrency) values( @BookN,@ServiceNo,@ServicePrice,@ServiceCurrency) end for; if @NastKind = 0 then update ReservePlan set Status = 0,RoomNo = @RoomNo where N = @RN end if; return(@R) end //[v 01.01.09]