-- Скрипт 0585 -- Съдържа: -- Промени, свързани с разбиването на Инвойс по ваучер и по гост, и отделни отстъпки за ваучер/инвойс. Направено -- за х-л Алба. Промените са много, просто няма смисъл да се описват поотделно. if (select count(*) from syscolumn where column_name='ForVaucherN' and table_id=(select table_id from systable where table_name='ProVaucherArticles')) =0 then alter table ProVaucherArticles add ForVaucherN integer NULL DEFAULT 0; update ProVaucherArticles set ForVaucherN=VaucherN where VaucherN<>0; end if GO IF (not EXISTS(Select * from systable where LCase(table_name) = LCase('OtherDiskForVaucherType'))) then Create table OtherDiskForVaucherType ( ID integer NOT NULL, Deskription varchar(30) NOT NULL, PRIMARY KEY ("ID") ); COMMENT ON COLUMN "DBA"."OtherDiskForVaucherType"."ID" IS 'Unikalen nomer'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucherType"."Deskription" IS 'Opisanie na tipowete otstapki ili nadcenki za Vaucher'; insert into OtherDiskForVaucherType(ID,Deskription) values(0,'За Нощувки'); insert into OtherDiskForVaucherType(ID,Deskription) values(1,'За Гости'); insert into OtherDiskForVaucherType(ID,Deskription) values(2,'За Ваучер'); END IF; GO IF (not EXISTS(Select * from systable where LCase(table_name) = LCase('OtherDiskForVaucher'))) then Create table OtherDiskForVaucher ( DiskForContractNo integer NOT NULL DEFAULT AUTOINCREMENT, DiskName varchar(50) NOT NULL , Sign varchar(1) NOT NULL , ContractNo integer NULL , FreeSum numeric(12,2) NOT NULL , PersentOrSum integer NOT NULL, DiskType integer NOT NULL, AdultType integer NULL , PRIMARY KEY ("DiskForContractNo") ); COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."DiskForContractNo" IS 'Unikalen nomer'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."DiskName" IS 'Naimenovanie na otstapkata ili na nadcenkata'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."Sign" IS 'Znak opredeliash dali e otstapka ili nadcenak'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."ContractNo" IS 'N na dogovor ot tabl.Contracts'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."FreeSum" IS 'suma ili procent na otstypkata ili nadcenkata'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."PersentOrSum" IS 'Flag akoe 0 FreeSum e suma ako e 1 FreeSum e procent'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."DiskType" IS 'ID ot tablica OtherDiskForVaucherType'; COMMENT ON COLUMN "DBA"."OtherDiskForVaucher"."AdultType" IS 'ID ot OtherDiskForVaucherType e 1 to tova pole opredelia tipa na gosta dali e vazrasten 0 ili dete 1'; END IF; GO IF (not EXISTS(Select * from systable where LCase(table_name) = LCase('HotelMessages'))) THEN Create Table HotelMessages ( ID integer NOT NULL, AppMessage varchar(50) NOT NULL, PRIMARY KEY ("ID") ); COMMENT ON COLUMN "DBA"."HotelMessages"."ID" IS 'Unique ID'; COMMENT ON COLUMN "DBA"."HotelMessages"."AppMessage" IS 'Message used in the application, in the appropriate language'; insert into HotelMessages(ID, AppMessage) values(1,'ДА'); insert into HotelMessages(ID, AppMessage) values(2,'НЕ'); insert into HotelMessages(ID, AppMessage) values(3,'Всички'); insert into HotelMessages(ID, AppMessage) values(4,'Възрастни'); insert into HotelMessages(ID, AppMessage) values(5,'Деца'); insert into HotelMessages(ID, AppMessage) values(6,'Сума'); insert into HotelMessages(ID, AppMessage) values(7,'%'); insert into HotelMessages(ID, AppMessage) values(8,'В началото на престоя'); insert into HotelMessages(ID, AppMessage) values(9,'В края на престоя'); insert into HotelMessages(ID, AppMessage) values(10,'В по-силния сезон'); insert into HotelMessages(ID, AppMessage) values(11,'В по-слабия сезон'); END IF; GO IF (select count(*) from SYS.SYSTABLE as st, SYS.SYSCOLUMN as SC where st.table_id = sc.table_id and LCase(st.table_name) = LCase('VaucherArticles') and LCase(sc.column_name) = LCase('RowType')) = 0 THEN alter table VaucherArticles add RowType integer; END IF; GO IF (select count(*) from SYS.SYSTABLE as st, SYS.SYSCOLUMN as SC where st.table_id = sc.table_id and LCase(st.table_name) = LCase('VaucherArticles') and LCase(sc.column_name) = LCase('TempGuestID')) = 0 THEN alter table VaucherArticles add TempGuestID integer default 0; END IF; Go Update VaucherArticles set RowType = 1 where RowType is null and VaucherN <> 0 and ForVaucherN <> 0; Go Update VaucherArticles set TempGuestID = ArticleNo where TempGuestID is null and RowType = 1; Go Update VaucherArticles set RowType = 2 where RowType is null and VaucherN = 0 and ForVaucherN = 0 and ServiceNo <> -8; Go Update VaucherArticles set RowType = 7 where RowType is null and VaucherN = 0 and ServiceNo = -8; Go IF (select count(*) from SYS.SYSTABLE as st, SYS.SYSCOLUMN as SC where st.table_id = sc.table_id and LCase(st.table_name) = LCase('ProVaucherArticles') and LCase(sc.column_name) = LCase('RowType')) = 0 THEN alter table ProVaucherArticles add RowType integer; END IF; Go IF (select count(*) from SYS.SYSTABLE as st, SYS.SYSCOLUMN as SC where st.table_id = sc.table_id and LCase(st.table_name) = LCase('ProVaucherArticles') and LCase(sc.column_name) = LCase('TempGuestID')) = 0 THEN alter table ProVaucherArticles add TempGuestID integer default 0; END IF; Go Update ProVaucherArticles set RowType = 1 where RowType is null and VaucherN <> 0 and ForVaucherN <> 0; Go Update VaucherArticles set TempGuestID = ArticleNo where TempGuestID is null and RowType = 1; Go Update ProVaucherArticles set RowType = 2 where RowType is null and VaucherN = 0 and ForVaucherN = 0 and ServiceNo <> -8; Go Update ProVaucherArticles set RowType = 7 where RowType is null and VaucherN = 0 and ServiceNo = -8; GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherDiscNadcenka'))) THEN Drop procedure VaucherDiscNadcenka END IF GO create procedure VaucherDiscNadcenka( inout @ArticleNumber integer, in @Currency integer, in @TotalSumForVaucher1 double, in @ContractNo1 integer, in @VaucherN1 integer, in @VatType integer, in @GuestAgeGroup integer, in @TmpGuestID integer ) begin -- Ako GuestAgeGroup e >= 0, se zapisvat samo otstapki/nadbavki za gost, inache (-1) vsichki ostanali declare @mnsign integer; declare @Ads integer; declare @Childs integer; declare @ObshtoHora integer; declare @Lodgings integer; declare @SumWithoutVat double; declare @Discount double; declare @GrantTotal double; declare @VatRate double; // Yanko -- --------------------------------- Message '+ VaucherDiscNadcenka START'; set @VatRate = 100 * (select Rate from VATRates where ID=4); select AdultsNumber, ChildNumber, ValNumLodgings into @Ads, @Childs, @Lodgings from VtdVauchers where VaucherN = @VaucherN1; for f3 as curs3 scroll cursor for select (if sign='-' then -20 else -21 endif) as @ServNo, DiskName as @ArtName, sign as @sign, FreeSum as @FreeSum, PersentOrSum as @PersentOrSum, DiskType as @DiskType, AdultType as @AdultType from OtherDiskForVaucher where ContractNo = @ContractNo1 -- Yanko +, razbivane po gost and ((DiskType in (0,1) and @GuestAgeGroup >= 0) or (DiskType not in (0,1) and @GuestAgeGroup < 0)) order by DiskType desc -- Yanko - do if @sign = '+' then set @mnsign=1 else set @mnsign=-1 end if; if @VatType=1 then set @SumWithoutVat=(@FreeSum-(@FreeSum*@VatRate)/(100 + @VatRate)); else set @SumWithoutVat=@FreeSum; end if; set @TotalSumForVaucher1=IsNull(@TotalSumForVaucher1,0); if @PersentOrSum=0 then set @Discount=0; set @GrantTotal=@SumWithoutVat; else if @VatType=1 then set @Discount=(@FreeSum-(@FreeSum*@VatRate)/(100 + @VatRate)); set @GrantTotal=( (@TotalSumForVaucher1*1.07)*(@FreeSum-(@FreeSum*@VatRate)/(100 + @VatRate)))/100; else set @Discount=@FreeSum; set @GrantTotal=(@TotalSumForVaucher1*@FreeSum)/100; end if; end if; -- za noshtuvki if @DiskType=0 then insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,null, 0, @mnsign*@GrantTotal, @ArtName, 0,@FreeSum,@ServNo, @Currency,@ArtName,null, null,10,@VaucherN1,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1; else -- za gosti if @DiskType=1 then -- Yanko +, razbivane po gost if ((@Ads>0 and @AdultType=0 and @GuestAgeGroup=0) or (@Childs>0 and @AdultType=1 and @GuestAgeGroup=1)) then set @ObshtoHora=1; -- Yank0 - insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,@ObshtoHora, @GrantTotal, @mnsign*@GrantTotal, @ArtName, 0,@Discount,@ServNo, @Currency,@ArtName,null, null,10,@VaucherN1,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1; end if; -- za vaucheri else if @DiskType=1 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,1, 0, @mnsign*@GrantTotal, @ArtName, 0,@Discount,@ServNo, @Currency,@ArtName,null, null,12,@VaucherN1,0) ; set @ArticleNumber=@ArticleNumber+1; end if end if end if; end for; Message '+ VaucherDiscNadcenka END'; end; //[v.1 xx.07.2007] - Ivaylo //[v.2 13.07.2007] - Yanko, razdeliane po gosti GO IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('VaucherGuestExtres'))) THEN Drop procedure VaucherGuestExtres END IF GO Create Procedure DBA.VaucherGuestExtres( inout @ArticleNumber integer, in @VaucherNo integer, in @VaucherNum varchar(30), in @BoardPlanNo integer, in @NumAdult integer, in @Numchild integer, in @ServiceCount integer, in @ServiceNum integer, in @NumPeople integer, in @ArticleName varchar(50), in @GuestName varchar(50), in @Disc double, in @HumanKind integer, in @AdultType integer, in @HasExtraBed integer, in @RoomType integer, in @ContractNo integer, in date1 date, in date2 date, in @NumLodgings integer, in @VatType integer, in @TmpGuestID integer ) begin -- prerabotena ot VaucherExtresType - zapisva samo sumata na bezpl. noshtuvki s obraten znak declare @SeassonPriority varchar(200); declare @TimePriority varchar(200); // declare date1 date; // declare date2 date; declare cdate date; declare @Season integer; declare br integer; declare @SeasonStr varchar(50); declare @FreeDays integer; declare @OstDays integer; declare @SeaIndex integer; declare @SeaDays integer; declare @FreeDaysStr varchar(50); declare @ExtType integer; declare @BrSeas integer; declare @MaxIndex integer; declare @MinIndex integer; declare @MaxSeason integer; declare @MinSeason integer; declare @cIndex integer; declare ii integer; declare @Sea1 integer; declare @brNights integer; declare @brFreeDays integer; declare @PriceKindStr varchar(50); declare @PriceK varchar(50); declare @PriceKindInt integer; declare @Seasson varchar(50); declare @BoardPlan varchar(20); declare @Room varchar(20); declare @AdditionBed varchar(20); declare @RealPrice double; declare @Currency integer; declare @Adult integer; declare @Child integer; declare @DiscName varchar(50); declare @GuestDiscount double; -- -------------------------------------- Message '+ VaucherGuestExtres START'; set @SeassonPriority=''; set @TimePriority=''; set @PriceKindStr=''; select Min(DiskName), cast(Min(FreeSum) as Integer) as OstDays, Min(PersentOrSum) as ExtType into @DiscName, @OstDays, @ExtType from OtherDiskForVaucher where ContractNo = @ContractNo and DiskType = 3; for f5 as curs5 scroll cursor for select distinct SeassonNo as @Sea from ContractPrices where Contract = @ContractNo do set @SeassonPriority=@SeassonPriority || @Sea end for; set br=0; set @TimePriority=''; set @SeasonStr=''; set cdate=days(date1,-1); set @Season=(select GetSeassonByDate(@ContractNo,date1)); set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,date1)); set @PriceK=(cast(@PriceKindInt as char(3))); set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK; set @TimePriority=@Season; while(cdate < date2) loop set br=br+1; set cdate=days(cdate,1); set @Season=(select GetSeassonByDate(@ContractNo,cdate)); if(@Season <> substr(@TimePriority,length(@TimePriority),1)) then if @SeasonStr = '' then set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br-1)) as char(3)) || br-1 else set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br end if; set @TimePriority=@TimePriority || @Season; set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,cdate)); set @PriceK=(cast(@PriceKindInt as char(3))); set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK; // message 'br='||br; set br=0 end if end loop; if length(@TimePriority) = 1 then set br=br-1 end if; set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br; set @BrSeas=CEILING(length(@SeasonStr)/3); if @ExtType = 0 then set @SeaIndex=1; set @SeaDays=substr(@SeasonStr,1,3); set @FreeDaysStr=''; -- формиране на @FreeDaysStr - по 3 символа (число с водещи нули) за сезон while @OstDays > @SeaDays loop set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays; set @OstDays=@OstDays-@SeaDays; set @SeaIndex=@SeaIndex+1; -- Yanko +, fix - ако безплатните дни са повече от заявените, не искаме да изпаднем в безкраен цикъл if substr(@SeasonStr,(@SeaIndex-1)*3+1,3) = '' then set @OstDays = 0 end if; -- Yanko - set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3) end loop; if @OstDays > 0 then set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if; while @SeaIndex < @BrSeas loop set @FreeDaysStr=@FreeDaysStr || '000'; set @SeaIndex=@SeaIndex+1 end loop end if; if @ExtType = 1 then set @SeaIndex=@BrSeas; // message '@SeaIndex='||@SeaIndex; set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3); // message '@SeaDays='||@SeaDays; set @FreeDaysStr=''; // set @SeaIndex=@SeaIndex-1; while @OstDays > @SeaDays loop set @FreeDaysStr=cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays || @FreeDaysStr; set @OstDays=@OstDays-@SeaDays; set @SeaIndex=@SeaIndex-1; set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3) end loop; // message '@SeaDays in loop='||@SeaDays; // message '@OstDays in loop='||@OstDays; if @OstDays > 0 then set @SeaIndex=@SeaIndex-1; set @FreeDaysStr=cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays || @FreeDaysStr end if; // message '@SeaIndex step last='||@SeaIndex; while @SeaIndex > 0 loop set @FreeDaysStr='000' || @FreeDaysStr; set @SeaIndex=@SeaIndex-1 end loop end if; if @ExtType = 2 then set @FreeDaysStr=''; set @MaxIndex=0; set @cIndex=1; set ii=0; while ii < length(@TimePriority) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); set @cIndex=locate(@SeassonPriority,cast(@Season as char(1))); if @MaxIndex < @cIndex then set @MaxIndex=@cIndex end if end loop; set @MaxSeason=substr(@SeassonPriority,@MaxIndex,1); set ii=0; while(ii < length(@TimePriority)) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); if @Season <> @MaxSeason then set @FreeDaysStr=@FreeDaysStr || '000' else set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3); if @OstDays > @SeaDays then set @OstDays=@SeaDays end if; set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if end loop end if; if @ExtType = 3 then set @FreeDaysStr=''; set @MinIndex=20; set @cIndex=1; set ii=0; while ii < length(@TimePriority) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); set @cIndex=locate(@SeassonPriority,cast(@Season as char(1))); if @MinIndex > @cIndex then set @MinIndex=@cIndex end if end loop; set @MinSeason=substr(@SeassonPriority,@MinIndex,1); set ii=0; while(ii < length(@TimePriority)) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); if @Season <> @MinSeason then set @FreeDaysStr=@FreeDaysStr || '000' else set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3); if @OstDays > @SeaDays then set @OstDays=@SeaDays end if; set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if end loop end if; if @FreeDaysStr is null then set @FreeDaysStr=substr('000000000000000000000000',1,length(@TimePriority)*3) end if; set br=length(@TimePriority); set ii=0; while ii < br loop set ii=ii+1; set @Sea1=substr(@TimePriority,ii,1); set @brNights=substr(@SeasonStr,(ii-1)*3+1,3); set @brFreeDays=substr(@FreeDaysStr,(ii-1)*3+1,3); set @brNights=@brNights-@brFreeDays; set @PriceKindInt=substr(@PriceKindStr,(ii-1)*3+1,3); select Description into @Seasson from PriceSeasons where SeasonNo = @Sea1; select BoardPlanName into @BoardPlan from BoardPlan where N = @BoardPlanNo; select ShortName into @Room from Classes where Class = @RoomType; select Deskription into @AdditionBed from ExtraBed where N = @HasExtraBed; select PriceAll,KodCurrency into @RealPrice, @Currency from ContractsPriceList where ContractNo = @ContractNo and ClassNo = @RoomType and PriceKindNo = @PriceKindInt and HumanKind = @HumanKind and AdultType = @AdultType and SeasonNo = @Sea1 and BoardPlan = @BoardPlanNo and ExtraBed = @HasExtraBed; if @VatType = 1 then set @RealPrice=(@RealPrice/1.07) else set @RealPrice=@RealPrice end if; set @RealPrice = IsNull(@RealPrice,0); if @AdultType = 0 then set @Adult=@NumPeople; set @Child=0 else set @Adult=0; set @Child=@NumPeople end if; set @GuestDiscount = (select first FreeSum from OtherDiskForVaucher where ContractNo=@ContractNo and DiskType=0); --set @Disc = @Disc + @GuestDiscount; set @Disc = @GuestDiscount; set @DiscName = (select first DiskName from OtherDiskForVaucher where ContractNo=@ContractNo and DiskType=3) || ' (' || @Seasson || ')'; if @ExtType = 0 then if @brFreeDays > 0 then // Tuk sa Nostuvki koito sa bezplatni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,0,'','','', '','','бр.',null,null, null,null,null,(-1)*CalcPriceWithDiscount(@Disc,(@NumPeople*@brFreeDays*@RealPrice)), @DiscName,'',0,@Disc,@ServiceNum,@Currency, @DiscName,@Seasson,@AdditionBed,'',@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 12,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if; else if @brFreeDays > 0 then // Tuk sa Nostuvki koito sa bezplatni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,0,'','','', '','','бр.',null,null, null,null,null,(-1)*CalcPriceWithDiscount(@Disc,(@NumPeople*@brFreeDays*@RealPrice)), @DiscName,'',0,@Disc,@ServiceNum,@Currency, @DiscName,@Seasson,@AdditionBed,'',@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 12,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if end if end loop; Message '+ VaucherGuestExtres END'; end //[v.1 17.07.2007] - Yanko, razdeliane po gosti GO IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('PrepareVauchersBill'))) THEN Drop procedure PrepareVauchersBill END IF GO create procedure DBA.PrepareVauchersBill(in @ContractNo integer,in @place varchar(50),in @VatType integer) // TaziF procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles // S uslugite koito triabva da plashtat gostite nastaneni po vaucheri ot daden dogovor begin declare @Currency integer; declare @ArticleNumber integer; // declare @FreeDays integer; declare @People integer; // declare @Deposit decimal(12,2); declare @Adult integer; declare @Child integer; declare @exCount integer; declare @exType integer; declare @PayedLodgings integer; declare @valPrice decimal(12,2); declare @Disc decimal(12,2); declare @TotalSum double; declare @ActiveSeasson integer; declare @IsForAlServices integer; declare @dobavka integer; declare @MaxPrice decimal(12,2); declare @MinPrice decimal(12,2); declare @TotalSumForVaucher double; declare @ConNo integer; declare @VVVAucherNum varchar(30); declare @FlagWithSubTotals integer; declare @BroiSubTotals integer; declare @DateArrive date; declare @LArticleNumber integer; // ivaylo declare @MinAn integer; declare @TotalSumForGuest decimal(12,2); // Yanko declare @GuestArticleNo integer; // Yanko declare @TempGuestID integer; // Yanko declare @HasGuestExtres integer; // Yanko -- ---------------------------------------------------------------------- -- za redowete v #VaucherArticles pri RowType = -- 1 Red za Vaucherna usluga za daden gost -- 2 Red za Dopulnitelna usluga za daden gost -- 3 SubTotal na Vaucher - suma na nostuvkite -- 4 SubTotal na Extreses - suma na dopulnitelnite uslugi -- 5 Total Vaucher = suma na nostuvkite + suma na dopulnitelnite uslugi -- 6 Super Total = suma na Vsichko -- 7 depozit -- 10 - Otstapka/nadbavka po vaucher/gost -- 11 - SubTotal za gost -- 12 - Bezplatni noshtuvki po gost -- ---------------------------------------------------------------------- Message '+ PrepareVauchersBill START'; delete from #VaucherArticles; set bvrBroi=0; set @People=0; set @Adult=0; set @Child=0; set @TotalSum=0; set @dobavka=0; set @MaxPrice=0; set @MinPrice=0; set @ActiveSeasson=0; set @TotalSumForVaucher=0; set @BroiSubTotals=0; select IntStojnost into @FlagWithSubTotals from Registers where RegisterName = 'VSmetkaSubTotals'; set @ArticleNumber = 1; set @HasGuestExtres = if (select count(*) from OtherDiskForVaucher where ContractNo=@ContractNo)>0 then 1 else 0 endif; -- Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor select C.Discount into @Disc from Contracts as C where C.ContractNo = @ContractNo; if (@Disc is null) then set @Disc=0 end if; select ExtresCount, ExtresType into @exCount, @exType from Contracts as c where c.ContractNo = @ContractNo; for f1 as curs1 scroll cursor for select v.VaucherNo as @VaucherNo,v.VaucherNum as @VaucherNum,s.ServiceName as @ArticleName, v.ContractNo,v.VaucherBoardPlan as @BoardPlanNo,v.NumAdults as @NumAdult,v.NumChild as @Numchild, v.NumLodgings as @ServiceCount,v.ServiceNo as @ServiceNum from VauchersForInvoice as v left outer join Services as s on (v.ServiceNo = s.ServiceNo) where v.ContractNo = @ContractNo do if @HasGuestExtres = 1 then -- ако има отсъпки/надценки по ваучер for f2 as curs2 scroll cursor for select AdultType as @AdultType, HumanKind as @HumanKind, RoomType as @RoomType, HasExtraBed as @HasExtraBed, Name as @GuestName from VaucherGuests where VaulcherNo = @VaucherNo order by AdultType asc,HasExtraBed asc do select DateArrive into @DateArrive from VtdVauchers where VaucherN = @VaucherNo and VaucherNum = @VaucherNum; set @GuestArticleNo = @ArticleNumber; set @TempGuestID = IsNull((select Max(TempGuestID)+1 from #VaucherArticles),1); call VaucherExtresType(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo, @NumAdult,@Numchild,@ServiceCount,@ServiceNum, 1, @ArticleName,@GuestName,@Disc, @HumanKind,@AdultType,@HasExtraBed,@RoomType, @ContractNo,@DateArrive,days(@DateArrive,@ServiceCount),@ServiceCount,@VatType,@TempGuestID); set @TotalSumForGuest = (select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID); call VaucherGuestExtres(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo, @NumAdult,@Numchild,@ServiceCount,@ServiceNum, 1, @ArticleName,@GuestName,@Disc, @HumanKind,@AdultType,@HasExtraBed,@RoomType,@ContractNo,@DateArrive, days(@DateArrive,@ServiceCount),@ServiceCount,@VatType,@TempGuestID); set @Currency = IsNull((select Max(KodCurrency) from ContractsPriceList where ContractNo = @ContractNo),1); call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForGuest,@ContractNo,@VaucherNo,@VatType,@AdultType,@TempGuestID); set @TotalSumForGuest = (select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID); set @ArticleNumber=@ArticleNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSumForGuest,null, null,null,null,null,null, null,null,11,0,@TempGuestID) ; set @ArticleNumber=@ArticleNumber+1; end for; else -- няма отстъпки/надценки, разбити по ваучер и гост for f222 as curs222 scroll cursor for select AdultType as @AdultType, HumanKind as @HumanKind, RoomType as @RoomType, HasExtraBed as @HasExtraBed, min(Name) as @GuestName, count(*) as @NumPeople from VaucherGuests where VaulcherNo = @VaucherNo group by AdultType, HumanKind, RoomType, HasExtraBed order by AdultType asc,HasExtraBed asc do select DateArrive into @DateArrive from VtdVauchers where VaucherN = @VaucherNo and VaucherNum = @VaucherNum; set @GuestArticleNo = @ArticleNumber; set @TempGuestID = IsNull((select Max(TempGuestID)+1 from #VaucherArticles),1); call VaucherExtresType(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo, @NumAdult,@Numchild,@ServiceCount,@ServiceNum, @NumPeople,@ArticleName,@GuestName,@Disc, @HumanKind,@AdultType,@HasExtraBed,@RoomType, @ContractNo,@DateArrive,days(@DateArrive,@ServiceCount),@ServiceCount,@VatType,@TempGuestID); set @Currency = IsNull((select Max(KodCurrency) from ContractsPriceList where ContractNo = @ContractNo),1); call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForGuest,@ContractNo,@VaucherNo,@VatType,@AdultType,@TempGuestID); set @TotalSumForGuest = (select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID); set @ArticleNumber=@ArticleNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSumForGuest,null, null,null,null,null,null, null,null,11,0,@TempGuestID) ; set @ArticleNumber=@ArticleNumber+1; end for; end if; set @VVVAucherNum=cast(@VaucherNum as varchar(30)); //------------------------------- ivaylo -- Yanko +, razbivane po gost select sum(IsNull(GrandTotal,0)) into @TotalSumForVaucher from #VaucherArticles where ForVaucherN = @VaucherNo and RowType=11; -- ако нямаме разбиване по гост, трием тотал за гост if @HasGuestExtres = 0 then delete from #VaucherArticles where RowType=11 end if; -- Yanko - call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForVaucher,@ContractNo,@VaucherNo,@VatType,-1,@TempGuestID); //------------------------------- ivaylo -- Yanko +, razbivane po gost; za da ne se smiata v totalite nadolu update #VaucherArticles set VaucherN = null where RowType=11; -- Yanko - set @TotalSumForVaucher = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0); insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArticleNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSumForVaucher,null, null,null,null,null,null, null,null,3,@VaucherNo) ; set @ArticleNumber=@ArticleNumber+1 end for; -- Smqta GrandTotal na vsi4ki noshtuvki po vaucheri i vmykva prazen red set @TotalSum = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType = 3),0); if (@TotalSum <> 0) then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArticleNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSum,null, null,null,null,null,' ', null,null,5,0) ; set @ArticleNumber=@ArticleNumber+1; set @ADult=0; set @Child=0 end if; -- Vzema i smetkosva svobodnite uslugi koito sa % ili suma call VaucherDisc(@ArticleNumber,@Currency,@TotalSum,@ContractNo,@VatType); set @ArticleNumber=@ArticleNumber+1; select sum(IsNull(GrandTotal,0)) into @TotalSum from #VaucherArticles where RowType in(4,5) ; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArticleNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSum,null, null,null,null,null,' ', null,null,6,0) ; if @FlagWithSubTotals = 0 then delete from #VaucherArticles where RowType = 3; delete from #VaucherArticles where RowType = 0 and ArticleNumber >= 1 and ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1) end if; -- delete from #VaucherArticles where RowType = 6; /* select count(*) into @BroiSubTotals from #VaucherArticles where RowType = 3; if @BroiSubTotals = 1 then delete from #VaucherArticles where RowType = 3 end if; */ if (select count(*) from #VaucherArticles where RowType = 3) = 1 then delete from #VaucherArticles where RowType = 3; end if; -- sortirane na zapisite //update #VaucherArticles set ArticleNumber = Number(); // Yanko while((select Max(ArticleNumber) from #VaucherArticles) > 0) loop set @MinAn=(select Min(ArticleNumber) from #VaucherArticles); if @MinAn > 0 then set @MinAn=0 end if; update #VaucherArticles set ArticleNumber = @MinAn-1 where ArticleNumber = (select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0) end loop; update #VaucherArticles set ArticleNumber = ArticleNumber*(-1); Message '+ PrepareVauchersBill END'; end //[v.1 xx.07.2007] - Ivaylo //[v.2 13.07.2007] - Yanko, razdeliane po gosti GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('PreparePrAgainVaucherBill'))) THEN Drop procedure PreparePrAgainVaucherBill END IF GO create procedure DBA.PreparePrAgainVaucherBill(in @SmetkaNo integer,in @OkCancel integer) -- Tazi procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles -- sus articulite ot opredelena smetka pri powtoren pechat na smetka ili Anulirane na smetka po dogovor - vaucher -- kato ako e PrintAgain @OkCancel e 1, a pri Cancel -1 -- Poleto RowType opredelia kakuv tip e reda -- 1 Red za Vaucherna usluga za daden gost -- 2 Red za Dopulnitelna usluga za daden gost -- 3 SubTotal na Vaucher - suma na nostuvkite -- 4 SubTotal na Extreses - suma na dopulnitelnite uslugi -- 5 Total Vaucher = suma na nostuvkite + suma na dopulnitelnite uslugi -- 6 Super Total = suma na Vsichko -- 7 depozit -- 10 - Otstapka/nadbavka po gost -- 11 - SubTotal za gost -- 12 - Otstapka/nadbavka po vaucher begin declare @ArtNumBr integer; declare @TotalSum double; declare @TotalAddSum double; declare @OldForVaucherN integer; declare @TotalSumForVaucher double; declare @FlagWithSubTotals integer; declare @BroiSubTotals integer; declare @LArticleNumber integer; // ivaylo declare @MinAn integer; // declare @ArtNum1 integer; declare @SubTotalValue double; // Yanko declare @SuperTotalValue double; //Yanko declare @VaucherDiscountValue double; //Yanko -- ----------------------------------- Message '+ PreparePrAgainVaucherBill START'; //Iztriva wremennata tablica za artikulite delete from #VaucherArticles; //Iztriva wremennata tablica za nacinite na plashtane delete from #PaymentTypes; set @TotalSum=0; set @TotalAddSum=0; set @ArtNumBr=0; set @OldForVaucherN=0; //Popylva wremennata tablica za artikulite for ff2 as cursff2 scroll cursor for select distinct VaucherN as @VaN from VaucherArticles where InvoiceNo = @SmetkaNo and VaucherN <> 0 order by VaucherN asc do set @TotalSumForVaucher=0; for f1 as curs1 scroll cursor for select ArticleNo as @OldArtNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum, VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room, VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice, GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc, ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason, TranslateBed as @TranslateBed,GuestName as @GuestName, TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN, TempGuestID as @TempGuestID from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN <> 0 and VaucherN = @VaN and VaucherN <> 0 and not RowType in (10,11,12) order by TempGuestID, ArticleNo asc do if @GrTotal is null then set @GrTotal=0; end if; -- set @TotalSumForVaucher=@TotalSumForVaucher+@GrTotal; set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType, RowType,ForVaucherN,TempGuestID) values( @ArtNumBr,@VaucherNo,@VaucherNum,@Seasson, @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child, @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName, @TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType, 1,@ForVaucherN,@TempGuestID); -- Yanko +, razbivane po gost - zapis na otstypiki/dadcenki po gost if @OldArtNo = (Select Max(ArticleNo) from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN <> 0 and VaucherN = @VaN and VaucherN <> 0 and TempGuestID = @TempGuestID and not RowType in (10,11,12)) then for mig21 as cursmig21 scroll cursor for select ArticleNo as @ArtNo21, TotalNights as @TotalNights21, SinglePrice as @RealPrice21, GrandTotal as @GrTotal21, ArticleName as @ArticleName21, Discount as @Disc21, ServiceNo as @ServiceNum21, Currency as @Currency21,CreditNo as @CreditNo21, SeasonNo as @SeassonNo21, ForVaucherN as @ForVaucherN21, RowType as @RowType21 from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN <> 0 and ForVaucherN=@VaN and VaucherN = 0 and RowType in (10,12) and TempGuestID = @TempGuestID order by ArticleNo asc do set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType, Measure, Adult, Child, Nights, TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo, Discount, ServiceNo, Currency, TranslateName, TranslateSeason, TranslateBed, RowType, ForVaucherN, TempGuestID) values(@ArtNumBr, 0, '', '', '', '', '', 'бр.', null, null, null, @TotalNights21, @OkCancel*@RealPrice21, @OkCancel*@GrTotal21, @ArticleName21, 0, @Disc21, @ServiceNum21, @Currency21, @ArticleName21, null, null, @RowType21, @ForVaucherN21, @TempGuestID) ; -- set @TotalSumForVaucher=@TotalSumForVaucher+IsNull(@GrTotal21,0); end for; set @SubTotalValue = IsNull((select sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID),0); set @TotalSumForVaucher = @TotalSumForVaucher + @SubTotalValue; -- zapis sub-total za gost set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumBr,0,null,null, null,null,null,null,null,null, null,null,null,@SubTotalValue,null, null,null,null,null,null,null, null,null,null,11,0); end if; -- Yanko - end for; // ************************** //------------------------------------------- ivaylo for f5 as curs5 scroll cursor for select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum, VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room, VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice, GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc, ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason, TranslateBed as @TranslateBed,GuestName as @GuestName, TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN <> 0 and ForVaucherN=@VaN and VaucherN = 0 and TempGuestID = 0 and RowType = 12 order by ArticleNo asc do set @TotalSumForVaucher = @TotalSumForVaucher + IsNull(@OkCancel*@GrTotal,0); set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType, RowType,ForVaucherN) values( @ArtNumBr,0,@VaucherNum,@Seasson, @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child, @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName, @TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType, 12,@ForVaucherN) ; end for; //------------------------------------------- ivaylo -- zapis sub-total na vaucher set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumBr,0,null,null, null,null,null,null,null,null, null,null,null,@TotalSumForVaucher,null, null,null,null,null,null,null, null,null,null,3,0) end for; -- zapis total na vsichki vaucheri set @TotalSum = IsNull((select sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType = 3),0); set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumBr,0,null,null, null,null,null,null,null,null, null,null,null,@TotalSum,null, null,null,null,null,null,null, null,null,null,5,0) ; // Sega Dopulnitelnite uslugi set @VaucherDiscountValue = 0; for f2 as curs2 scroll cursor for select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum, VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room, VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice, GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc, ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason, TranslateBed as @TranslateBed,GuestName as @GuestName, TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN = 0 and VaucherN = 0 and ServiceNo <> -8 order by ArticleNo asc do set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType, RowType,ForVaucherN) values( @ArtNumBr,@VaucherNo,@VaucherNum,@Seasson, @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child, @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName, @TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType, 2,0) ; set @VaucherDiscountValue = @VaucherDiscountValue + IsNull(@GrTotal,0); end for; select sum(IsNull(GrandTotal,0)) into @TotalAddSum from VaucherArticles where InvoiceNo = @SmetkaNo and ForVaucherN = 0 and VaucherN = 0; if @VaucherDiscountValue <> 0 then set @ArtNumBr = @ArtNumBr + 1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumBr,0,null,null, null,null,null,null,null,null, null,null,null,@OkCancel*@VaucherDiscountValue,null, null,null,null,null,null,null, null,null,null,4,0) ; end if; //set @ArtNumBr=@ArtNumBr+1; // *********************** end for **************************************** //------------------------------------------------------------------------------ Ivaylo Depozit for f4 as curs4 scroll cursor for select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum, VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room, VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice, GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc, ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason, TranslateBed as @TranslateBed,GuestName as @GuestName, TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN from VaucherArticles where InvoiceNo = @SmetkaNo and ServiceNo=-8 and VaucherN = 0 order by ArticleNo asc do set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType, RowType) values( @ArtNumBr,@VaucherNo,@VaucherNum,@Seasson, @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child, @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName, @TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType, 7) ; end for; -- zapis Total na Invoisa select sum(IsNull(GrandTotal,0)) into @SuperTotalValue from #VaucherArticles where RowType in (4,5,7); set @ArtNumBr=@ArtNumBr+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumBr,0,null,null, null,null,null,null,null,null, null,null,null,@SuperTotalValue,null, null,null,null,null,null,null, null,null,null,6,0); //------------------------------------------------------------------------------ Ivaylo Depozit for f3 as curs3 scroll cursor for select RM.Sum as @Sum,RM.Currency as @KodCurrency,RM.Payment as @PaymentType,Cr.Symbol as @Symbol, RM.AccountNumber as @AccountNumber,RM.GuestNo as @GuestNo,RM.GroupNo as @GroupNo,RM.RoomNo as @RoomNo,RM.ReserveNo as @ReserveNo, RM.PTVid as @PTVid from DBA.ResMoney as RM,DBA.Currencies as Cr where RM.VaucherInvoiceNo = @SmetkaNo and RM.DateMoney <> 0 and RM.Currency = Cr.KodCurrency do insert into #PaymentTypes( PaymentType,Sum,KodCurrency,AccountNumber,Symbol,GuestNo,GroupNo,RoomNo,ReserveNo,PTVid) values( @PaymentType,@OkCancel*@Sum,@KodCurrency,@AccountNumber,@Symbol,@GuestNo,@GroupNo,@RoomNo,@ReserveNo,@PTVid) end for; select IntStojnost into @FlagWithSubTotals from Registers where RegisterName = 'VSmetkaSubTotals'; /* select count(*) into @BroiSubTotals from #VaucherArticles where RowType = 3; if @BroiSubTotals = 1 then delete from #VaucherArticles where RowType = 3; end if; */ if @FlagWithSubTotals = 0 then delete from #VaucherArticles where RowType = 3; end if; //update #VaucherArticles set ArticleNumber = Number(); // Yanko while ((Select Max(ArticleNumber) from #VaucherArticles) > 0) loop set @MinAn = (select Min(ArticleNumber) from #VaucherArticles); if @MinAn > 0 then set @MinAn = 0 end if; Update #VaucherArticles set ArticleNumber = @MinAn - 1 where ArticleNumber = (Select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0); end loop; Update #VaucherArticles set ArticleNumber = ArticleNumber * (-1); Message '+ PreparePrAgainVaucherBill END'; end //[v 17.05.2003] Jorko //[v 01.07.2003] Jorko premahva reda na dopylnitelnite otstupki ako e 0.00 //[v 01.08.2003] Jorko prenapisana nacialo s nova ideologia za redovete RowType //[v 23.12.2003] Jorko preposana pak //[v.5 xx.07.2007] - Ivaylo - otstypki za vseki edin vaucher //[v.6 14.07.2007] - Yanko - razbivane po gosti GO IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('VaucherExtresType'))) THEN Drop procedure VaucherExtresType END IF GO create procedure DBA.VaucherExtresType( inout @ArticleNumber integer, in @VaucherNo integer, in @VaucherNum varchar(30), in @BoardPlanNo integer, in @NumAdult integer, in @Numchild integer, in @ServiceCount integer, in @ServiceNum integer, in @NumPeople integer, in @ArticleName varchar(50), in @GuestName varchar(50), in @Disc double, in @HumanKind integer, in @AdultType integer, in @HasExtraBed integer, in @RoomType integer, in @ContractNo integer, in date1 date, in date2 date, in @NumLodgings integer, in @VatType integer, in @TmpGuestID integer ) begin declare @SeassonPriority varchar(200); declare @TimePriority varchar(200); // declare date1 date; // declare date2 date; declare cdate date; declare @Season integer; declare br integer; declare @SeasonStr varchar(50); declare @FreeDays integer; declare @OstDays integer; declare @SeaIndex integer; declare @SeaDays integer; declare @FreeDaysStr varchar(50); declare @ExtType integer; declare @BrSeas integer; declare @MaxIndex integer; declare @MinIndex integer; declare @MaxSeason integer; declare @MinSeason integer; declare @cIndex integer; declare ii integer; declare @Sea1 integer; declare @brNights integer; declare @brFreeDays integer; declare @PriceKindStr varchar(50); declare @PriceK varchar(50); declare @PriceKindInt integer; declare @Seasson varchar(50); declare @BoardPlan varchar(20); declare @Room varchar(20); declare @AdditionBed varchar(20); declare @RealPrice double; declare @Currency integer; declare @Adult integer; declare @Child integer; -- -------------------------------------- Message '+ VaucherExtresType START'; set @SeassonPriority=''; set @TimePriority=''; // set @OstDays=2; set @PriceKindStr=''; select ExtresCount, ExtresType into @OstDays, @ExtType from Contracts as c where c.ContractNo = @ContractNo; // set @ExtType=3; for f5 as curs5 scroll cursor for select distinct SeassonNo as @Sea from ContractPrices where Contract = @ContractNo do set @SeassonPriority=@SeassonPriority || @Sea end for; // set date1='20040610'; // set date2='20040825'; set br=0; set @TimePriority=''; set @SeasonStr=''; set cdate=days(date1,-1); set @Season=(select GetSeassonByDate(@ContractNo,date1)); set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,date1)); set @PriceK=(cast(@PriceKindInt as char(3))); set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK; set @TimePriority=@Season; while(cdate < date2) loop set br=br+1; set cdate=days(cdate,1); set @Season=(select GetSeassonByDate(@ContractNo,cdate)); if(@Season <> substr(@TimePriority,length(@TimePriority),1)) then if @SeasonStr = '' then set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br-1)) as char(3)) || br-1 else set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br end if; set @TimePriority=@TimePriority || @Season; set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,cdate)); set @PriceK=(cast(@PriceKindInt as char(3))); set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK; // message 'br='||br; set br=0 end if end loop; if length(@TimePriority) = 1 then set br=br-1 end if; set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br; set @BrSeas=CEILING(length(@SeasonStr)/3); if @ExtType = 0 then set @SeaIndex=1; set @SeaDays=substr(@SeasonStr,1,3); set @FreeDaysStr=''; -- формиране на @FreeDaysStr - по 3 символа (число с водещи нули) за сезон while @OstDays > @SeaDays loop set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays; set @OstDays=@OstDays-@SeaDays; set @SeaIndex=@SeaIndex+1; -- Yanko +, fix - ако безплатните дни са повече от заявените, не искаме да изпаднем в безкраен цикъл if substr(@SeasonStr,(@SeaIndex-1)*3+1,3) = '' then set @OstDays = 0 end if; -- Yanko - set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3) end loop; if @OstDays > 0 then set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if; while @SeaIndex < @BrSeas loop set @FreeDaysStr=@FreeDaysStr || '000'; set @SeaIndex=@SeaIndex+1 end loop end if; if @ExtType = 1 then set @SeaIndex=@BrSeas; // message '@SeaIndex='||@SeaIndex; set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3); // message '@SeaDays='||@SeaDays; set @FreeDaysStr=''; // set @SeaIndex=@SeaIndex-1; while @OstDays > @SeaDays loop set @FreeDaysStr=cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays || @FreeDaysStr; set @OstDays=@OstDays-@SeaDays; set @SeaIndex=@SeaIndex-1; set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3) end loop; // message '@SeaDays in loop='||@SeaDays; // message '@OstDays in loop='||@OstDays; if @OstDays > 0 then set @SeaIndex=@SeaIndex-1; set @FreeDaysStr=cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays || @FreeDaysStr end if; // message '@SeaIndex step last='||@SeaIndex; while @SeaIndex > 0 loop set @FreeDaysStr='000' || @FreeDaysStr; set @SeaIndex=@SeaIndex-1 end loop end if; if @ExtType = 2 then set @FreeDaysStr=''; set @MaxIndex=0; set @cIndex=1; set ii=0; while ii < length(@TimePriority) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); set @cIndex=locate(@SeassonPriority,cast(@Season as char(1))); if @MaxIndex < @cIndex then set @MaxIndex=@cIndex end if end loop; set @MaxSeason=substr(@SeassonPriority,@MaxIndex,1); set ii=0; while(ii < length(@TimePriority)) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); if @Season <> @MaxSeason then set @FreeDaysStr=@FreeDaysStr || '000' else set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3); if @OstDays > @SeaDays then set @OstDays=@SeaDays end if; set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if end loop end if; if @ExtType = 3 then set @FreeDaysStr=''; set @MinIndex=20; set @cIndex=1; set ii=0; while ii < length(@TimePriority) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); set @cIndex=locate(@SeassonPriority,cast(@Season as char(1))); if @MinIndex > @cIndex then set @MinIndex=@cIndex end if end loop; set @MinSeason=substr(@SeassonPriority,@MinIndex,1); set ii=0; while(ii < length(@TimePriority)) loop set ii=ii+1; set @Season=substr(@TimePriority,ii,1); if @Season <> @MinSeason then set @FreeDaysStr=@FreeDaysStr || '000' else set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3); if @OstDays > @SeaDays then set @OstDays=@SeaDays end if; set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays end if end loop end if; if @FreeDaysStr is null then set @FreeDaysStr=substr('000000000000000000000000',1,length(@TimePriority)*3) end if; set br=length(@TimePriority); set ii=0; while ii < br loop set ii=ii+1; set @Sea1=substr(@TimePriority,ii,1); set @brNights=substr(@SeasonStr,(ii-1)*3+1,3); set @brFreeDays=substr(@FreeDaysStr,(ii-1)*3+1,3); set @brNights=@brNights-@brFreeDays; set @PriceKindInt=substr(@PriceKindStr,(ii-1)*3+1,3); select Description into @Seasson from PriceSeasons where SeasonNo = @Sea1; select BoardPlanName into @BoardPlan from BoardPlan where N = @BoardPlanNo; select ShortName into @Room from Classes where Class = @RoomType; select Deskription into @AdditionBed from ExtraBed where N = @HasExtraBed; select PriceAll,KodCurrency into @RealPrice,@Currency from ContractsPriceList where ContractNo = @ContractNo and ClassNo = @RoomType and PriceKindNo = @PriceKindInt and HumanKind = @HumanKind and AdultType = @AdultType and SeasonNo = @Sea1 and BoardPlan = @BoardPlanNo and ExtraBed = @HasExtraBed; if @VatType = 1 then set @RealPrice=(@RealPrice/1.07) else set @RealPrice=@RealPrice end if; set @RealPrice = IsNull(@RealPrice,0); if @AdultType = 0 then set @Adult=@NumPeople; set @Child=0 else set @Adult=0; set @Child=@NumPeople end if; if @ExtType = 0 then if @brFreeDays > 0 then // Tuk sa Nostuvki koito sa bezplatni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName, @GuestName,0,@Disc,@ServiceNum,@Currency, @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 1,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if; if @brNights > 0 then // Tuk sa Nostuvki koito sa plateni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName, @GuestName,0,@Disc,@ServiceNum,@Currency, @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 1,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if else if @brNights > 0 then // Tuk sa Nostuvki koito sa plateni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName, @GuestName,0,@Disc,@ServiceNum,@Currency, @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 1,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if; if @brFreeDays > 0 then // Tuk sa Nostuvki koito sa bezplatni insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName, @GuestName,0,@Disc,@ServiceNum,@Currency, @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1, @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType, 1,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if end if end loop; Message '+ VaucherExtresType END'; end //[v.1 xx.07.2007] - Ivaylo //[v.2 13.07.2007] - Yanko, razdeliane po gosti GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherDisc'))) THEN Drop procedure VaucherDisc END IF GO create procedure DBA.VaucherDisc(inout @ArticleNumber integer,in @Currency integer,inout @TotalSumForVaucher1 double,in @ContractNo1 integer,in @VatType integer) begin declare @BrojHora integer; declare @Broi integer; declare @BroiVuz integer; declare @BroiDeca integer; declare @TotalAddSum double; declare @Deposit double; declare @DiskSum double; declare @mnsign integer; declare @TotalNights integer; declare @Ads integer; declare @Childs integer; declare @DDSFlag integer; declare @ObshtoHora integer; set @TotalAddSum=0; set @DiskSum=0; set @mnsign=1; -- ------------------------------------------------------ Message '+ VaucherDisc START'; message '========================== @TotalSumForVaucher1='||@TotalSumForVaucher1; select sum(GrandTotal) into @TotalSumForVaucher1 from #VaucherArticles where RowType = 3; if @VatType=1 then set @TotalSumForVaucher1=@TotalSumForVaucher1*1.07; else set @TotalSumForVaucher1=@TotalSumForVaucher1; end if; select(sum(Adult)+sum(Child)) into @BrojHora from #VaucherArticles; set @BroiVuz=0; set @BroiDeca=0; set @Broi=0; for fVauch as cursVauch scroll cursor for select distinct VaucherN as @Vauch from #VaucherArticles where VaucherN <> 0 do select AdultsNumber into @Ads from VtdVauchers where VaucherN = @Vauch; select ChildNumber into @Childs from VtdVauchers where VaucherN = @Vauch; set @BroiVuz=@BroiVuz+@Ads; set @BroiDeca=@BroiDeca+@Childs end for; // select sum(Adult) into @Broi from #VaucherArticles; // set @BroiVuz=@Broi; // select sum(Child) into @Broi from #VaucherArticles; // set @BroiDeca=@Broi; for f3 as curs3 scroll cursor for select DiskForContractNo as @DiskForContractNo, (if sign='-' then -20 else -21 endif) as @ServNo, DiskName as @ArtName, DiskPersent as @TotDisk, sign as @sign, FreeSum as @FreeSum, ServiceCount as @fSrvCouns, AdultType as @AdultType, ServiceCount as @SrvCouns,NightsOrGuests as @NightsOrGuests from OtherDiskForContract where ContractNo = @ContractNo1 do if(@TotDisk <> 0) then // ako e na procent if @VatType=1 then set @TotDisk=@TotDisk-(@TotDisk*7)/107; set @DiskSum=(@TotalSumForVaucher1*@TotDisk )/100; else set @DiskSum=(@TotalSumForVaucher1*@TotDisk)/100; end if; else if @VatType=1 then set @DiskSum=(@FreeSum-(@FreeSum*7)/107); else set @DiskSum=@FreeSum; end if; end if; if @sign = '+' then set @mnsign=1 else set @mnsign=-1 end if; if(@TotDisk = 0) then // ako e na nostuvki, ne e na procent select sum(TotalNights) into @SrvCouns from #VaucherArticles where RowType = 1; if((@mnsign*@DiskSum*@SrvCouns) <> 0) then if @NightsOrGuests = 0 then // na nostuvki insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,OtherDiskN) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,@SrvCouns*@fSrvCouns, @DiskSum, @mnsign*@DiskSum*@SrvCouns, @ArtName, 0,@TotDisk,@ServNo, @Currency,@ArtName,null, null,2,0,@DiskForContractNo) ; set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns) else // na gosti if @AdultType=0 then set @ObshtoHora=(@BroiVuz+@BroiDeca) else if @AdultType=1 then set @ObshtoHora=@BroiVuz else set @ObshtoHora=@BroiDeca end if end if; if (((@AdultType=1) and (@BroiVuz>0)) or ((@AdultType=2) and (@BroiDeca>0)) or (@AdultType=0) or (@AdultType is null)) then if @AdultType = 1 then set @BroiDeca = 0; else if @AdultType <> 0 then set @BroiVuz = 0; end if end if; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,OtherDiskN) values( @ArticleNumber,0,'','','','','','бр.', @BroiVuz, @BroiDeca,null,@ObshtoHora, @DiskSum, @mnsign*@DiskSum*@ObshtoHora, @ArtName, 0,@TotDisk,@ServNo, @Currency,@ArtName,null, null,2,0,@DiskForContractNo) ; set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca)) end if; end if; set @ArticleNumber=@ArticleNumber+1 end if else // ako e na Percent if @NightsOrGuests = 0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,OtherDiskN) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,@mnsign*@SrvCouns, 0,@mnsign*@DiskSum,@ArtName, 0,@TotDisk,@ServNo, @Currency,@ArtName,null, null,2,0,@DiskForContractNo) ; set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns) else insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN,OtherDiskN) values( @ArticleNumber,0,'','','','','','бр.', null, null,null,null, 0,@mnsign*@DiskSum*(@BroiVuz+@BroiDeca),@ArtName, 0,@TotDisk,@ServNo, @Currency,@ArtName,null, null,2,0,@DiskForContractNo) ; set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca)) end if; set @ArticleNumber=@ArticleNumber+1 end if end for; //Smqta GrandTotal na svobodnite uslugi i vmykva prazen red if @TotalAddSum <> 0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum, VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Measure,Adult, Child,Nights,TotalNights, SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason, TranslateBed,RowType,ForVaucherN) values( @ArticleNumber,0,null, null,null,null, null,'бр.',null, null,null,null, null,@TotalAddSum,null, null,null,null, null,null,null, null,4,0) ; set @ArticleNumber=@ArticleNumber+1; end if; set @Deposit=0; if @VatType=1 then set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo1)/(1.07); else set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo1); end if; if @Deposit<>0 then insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType) values(@ArticleNumber,0,'','','', '','','бр.',0,0, -1,-1,@Deposit,(-1)*@Deposit,'Депозити(и)', 0,0,-8,@Currency,'Депозит(ид)', null,null,7); set @ArticleNumber=@ArticleNumber+1; end if; Message '+ VaucherDisc END'; end GO IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('FinishVaucherBill'))) THEN Drop procedure FinishVaucherBill END IF GO create function DBA.FinishVaucherBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT decimal(12,2),in @VAT decimal(12,2),in @GrandTotal decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer,in @WithoutVAT decimal(12,2)) returns integer //Insertva dannite za fakturata vuv Invoices i Articles i insertva parite v ResMoney //@No e syotvetno nomer na grupa, rezervaciq, gost, staia i anulirana smetka, ako smetkata e za grupa, rezervacia, gost, staia ili anulirashta begin atomic declare @InvoiceNo integer; declare @DepositNo integer; declare @TrueInvoiceNo integer; declare @CancelInvoiceNo integer; declare @ServiceNo integer; declare @CreditNo integer; declare @ArtNum integer; declare @ResMoneyStatus integer; declare @SmetkaCurrency integer; declare @NullDepositCount integer; declare @InvoiceMask integer; //ivaylo declare @p integer; -- ------------------------------------- Message '+ FinishVaucherBill START'; if @BillType <> 7 then select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1005 end if; if @BillType = 7 then select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1004 end if; //Vzima Tipa valuta ot vida smetka select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka; //Iztriva nulevite kolichestva delete from #VaucherArticles where TotalNights = 0; //Vmukva dannite za fakturata //-------------------------------------------------- ivaylo set @p=(select IntStojnost from Registers where RegisterName = 'VTDCommonCounter'); if @p = 1 then set @InvoiceNo=GetCounter('VaucherInvoiceCounter') // //ivaylo else set @InvoiceNo=GetCounter('InvoiceCounter') // //ivaylo end if; if @IsInvoice = 1 then if @p = 1 then set @TrueInvoiceNo=GetCounter('TrueVaucherInvoiceNo') //TrueVaucherInvoiceNo ivaylo else set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') //TrueVaucherInvoiceNo ivaylo end if else set @TrueInvoiceNo=0 end if; //-------------------------------------------------- ivaylo if @BillType = -1 then set @CancelInvoiceNo=@No else set @CancelInvoiceNo=0 end if; set @ResMoneyStatus=1; set @ArtNum=1; select IntStojnost into @InvoiceMask from Registers where RegisterName = 'MaskaFaktura'; //ivaylo //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka insert into VaucherInvoices( VaucherInvoiceNo,Status,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten, UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency, TotalNoVAT,VAT,GrandTotal,CancelInvoiceNo,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,MaskaFaktura) values( @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin, @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,@InvoiceMask) ; //Insertva kato zapis sumata ot vsi4ki vau4eri po dogovor v ServiceKredits kato usluga "VTD" set @CreditNo=GetCounter('CreditCounter'); insert into ServiceCredits( DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,CreditNo,PayedServices,WhoPays,GroupPays,ContractPays,RoomPays,PBXLogN,DateCreditDt,RoomNum) select @InvoiceDate,0,ServiceNo,sum(GrandTotal),@SmetkaCurrency,@KodAdmin,1,@CreditNo,1,0,0,@ContractNo,0,null,FloatToDate(@InvoiceDate),null from #VaucherArticles where VaucherN <> 0 group by ServiceNo; //Insertva zapis vuv Articles za suma s vaucheri po dogovor for f0 as curs0 scroll cursor for select VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,VaucherBoard as @BoardPlan, VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,Nights as @Nights, TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,TranslateName as @TranslateName, TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,GuestName as @GuestName,TranslateGuestName as @TranslateGuestName, SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan, RoomType as @RoomType,ForVaucherN as @ForVaucherN, RowType as @RowType, TempGuestID as @TempGuestID from #VaucherArticles where VaucherN <> 0 order by VaucherN asc do insert into VaucherArticles( InvoiceNo,ArticleNo,VaucherN, VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,ForVaucherN, RowType, TempGuestID) values( @InvoiceNo,@ArtNum,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights, @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@ForVaucherN, @RowType, @TempGuestID) ; set @ArtNum=@ArtNum+1 end for; for f2 as curs2 scroll cursor for select v.VaucherN as @Vaucher from #VaucherArticles as va left outer join VtdVauchers as v on (v.VaucherN = va.VaucherN) where v.VaucherN is not null and v.ContractNo = @ContractNo group by v.VaucherN do if @BillType <> -1 then update VtdVauchers set TrueInvoceNo = @TrueInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher; update VtdVauchers set InvoiceNo = @InvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher else update VtdVauchers set TrueInvoceNo = 0,CancelInvoiceNo = @CancelInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher; update VtdVauchers set InvoiceNo = 0 where ContractNo = @ContractNo and VaucherN = @Vaucher end if end for; for f3 as curs3 scroll cursor for select SinglePrice as @ArticlePrice,GrandTotal as @ArticleSum, Adult as @Adult, Child as @Child, TotalNights as @ArticleQty,ArticleName as @ArticleName,Discount as @Discount, ServiceNo as @Service,TranslateName as @TranslateName,TranslateSeason as @TranslateSeason, TranslateBed as @TranslateBed,ForVaucherN as @ForVaucherN, RowType as @RowType, TempGuestID as @TempGuestID from #VaucherArticles where(VaucherN = 0) and(Creditno is not null) do insert into VaucherArticles( InvoiceNo,ArticleNo,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,CreditNo,Discount,ServiceNo, Currency,TranslateName,TranslateSeason,TranslateBed,ForVaucherN,RowType,TempGuestID) values( @InvoiceNo,@ArtNum,'','','','','','',@Adult,@Child,@ArticleQty,@ArticleQty,@ArticlePrice,@ArticleSum,@ArticleName, 0,@Discount,@Service,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@ForVaucherN,@RowType,@TempGuestID); set @ArtNum=@ArtNum+1 end for; //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva //Promenia CancelInvoiceNo na anuliranata smetka na -1 if @BillType = -1 then delete from DBA.ResMoney where VaucherInvoiceNo = @No and DateMoney = 0; update VaucherInvoices set CancelInvoiceNo = -1 where VaucherInvoiceNo = @No; select max(Status) into @ResMoneyStatus from ResMoney where VaucherInvoiceNo = @No end if; //Nulira depositi, ako e smetka po dogovor if((@BillType = 7) or(@BillType = 8)) and(@ContractNo <> 0) then call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount) end if; //Vmukva plashtaniata ot wremennata tablica za plashtania for f4 as curs4 scroll cursor for select PaymentType as @PaymentType,Sum as @Sum,KodCurrency as @KodCurrency,AccountNumber as @AccountNumber, GuestNo as @mGuestNo,GroupNo as @mGroupNo,RoomNo as @mRoomNo,ReserveNo as @mReserveNo,PTVid as @PTVid from #PaymentTypes do set @DepositNo=GetCounter('DepositCounter'); insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo, ContractNo,VaucherInvoiceNo,RoomNo,AccountNumber,PTVid) values( @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) end for; Message '+ FinishVaucherBill END'; return(@InvoiceNo); end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FinishVaucherProformBill'))) THEN Drop function FinishVaucherProformBill END IF GO create function DBA.FinishVaucherProformBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT double,in @VAT double,in @GrandTotal double,in @WithoutVAT double,in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate double,in @ContractNo integer) returns integer //Insertva dannite fakturata vuv Proform i ProVaucherArticles i sled tova po tazi Proforma moje da se izdava faktura za smetka dogovor - vaucher begin atomic declare @ProformNo integer; declare @DepositNo integer; declare @TrueInvoiceNo integer; declare @CancelInvoiceNo integer; declare @ServiceNo integer; //declare @CreditNo integer; declare @ResMoneyStatus integer; declare @SmetkaCurrency integer; declare @ArtNo integer; -- --------------------------------- Message '+ FinishVaucherProformBill START'; //Vzima Tipa valuta ot vida smetka select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka; //Iztriva nulevite kolichestva delete from #VaucherArticles where TotalNights = 0; //Vmukva dannite za fakturata set @ProformNo=GetCounter('TrueProfNo'); //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka insert into Proform( ProformNo,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten, UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency, TotalNoVAT,VAT,GrandTotal,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,DDSType) values( @ProformNo,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin, @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,2) ; set @ArtNo=1; // return; for f1 as curs1 scroll cursor for select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName, SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType, RowType as @RowType, TempGuestID as @TempGuestID from #VaucherArticles where RowType = 1 order by ArticleNumber asc do insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN, VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,ForVaucherN,RowType,TempGuestID) values( @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights, @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@VaucherNo,@RowType,@TempGuestID) ; set @ArtNo=@ArtNo+1 end for; //----------------------------------------- ivaylo for f4 as curs4 scroll cursor for select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName, SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan, RoomType as @RoomType,ForVaucherN as @ForVaucherN,RowType as @RowType,TempGuestID as @TempGuestID from #VaucherArticles where RowType in (10,12) order by ArticleNumber asc do insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN, VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,ForVaucherN,RowType,TempGuestID) values( @ProformNo,@ArtNo,0,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights, @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@ForVaucherN,@RowType,@TempGuestID) ; set @ArtNo=@ArtNo+1 end for; //----------------------------------------- ivaylo for f2 as curs2 scroll cursor for select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName, SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,RowType as @RowType,TempGuestID as @TempGuestID from #VaucherArticles where RowType = 2 order by ArticleNumber asc do insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN, VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,RowType,TempGuestID) values( @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights, @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@RowType,@TempGuestID) ; set @ArtNo=@ArtNo+1 end for; //------------------------------------------------------------------- ivaylo Depozit for f3 as curs3 scroll cursor for select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName, SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed, BoardPlan as @ValBoardPlan,RoomType as @RoomType,RowType as @RowType,TempGuestID as @TempGuestID from #VaucherArticles where RowType = 7 order by ArticleNumber asc do insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN, VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName, SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,ForVaucherN,RowType,TempGuestID) values( @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights, @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName, @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@VaucherNo,@RowType,@TempGuestID) ; set @ArtNo=@ArtNo+1 end for; //------------------------------------------------------------------- ivaylo Depozit Message '+ FinishVaucherProformBill END'; return(@ProformNo); end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('PreparePrintAgainVaucherProform'))) THEN Drop procedure PreparePrintAgainVaucherProform END IF Go Create Procedure DBA.PreparePrintAgainVaucherProform(in @ProformNo integer) // Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles za povtoren pe4at na proforma begin declare @ArtNumber integer; declare @TotalAddSum decimal(12,2); declare @SuperTotalSum decimal(12,2); declare @BrExtras integer; declare @FlagWithSubTotals integer; declare @MinAn integer; declare @TotalSumForVaucher double; // Yanko declare @SubTotalValue double; // Yanko declare @SuperTotalValue double; // Yanko declare @BroiSubTotals integer; // Yanko -- ------------------------------------------------- Message '+ PreparePrintAgainVaucherProform START'; //Iztriva wremennata tablica za artikulite delete from #VaucherArticles; set @ArtNumber=1; //Popylva wremennata tablica za artikulite for f2 as curs2 scroll cursor for select distinct VaucherN as @VV from ProVaucherArticles where VaucherN <> 0 and ProformNo = @ProformNo order by VaucherN asc do set @TotalSumForVaucher = 0; for f1 as curs1 scroll cursor for select ArticleNo as @OldArtNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed, BoardPlan as @BPNum, RoomType as @RoomType, RowType as @RowType, TempGuestID as @TempGuestID from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = @VV order by TempGuestID, ArticleNo asc do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArtNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency, @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo, @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType, 1,@VaucherNo,@TempGuestID); -- Yanko +, razbivane po gost - zapis na otstypiki/dadcenki po gost if @OldArtNo = (Select Max(ArticleNo) from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = @VV and TempGuestID = @TempGuestID) then for mig21 as cursmig21 scroll cursor for select ArticleNo as @ArtNo21, TotalNights as @TotalNights21, SinglePrice as @RealPrice21, GrandTotal as @GrTotal21, ArticleName as @ArticleName21, Discount as @Disc21, ServiceNo as @ServiceNum21, Currency as @Currency21,CreditNo as @CreditNo21, SeasonNo as @SeassonNo21, ForVaucherN as @ForVaucherN21 from ProVaucherArticles where ProformNo = @ProformNo and ForVaucherN <> 0 and ForVaucherN=@VV and VaucherN = 0 and RowType in (10,12) and TempGuestID = @TempGuestID order by ArticleNo asc do set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType, Measure, Adult, Child, Nights, TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo, Discount, ServiceNo, Currency, TranslateName, TranslateSeason, TranslateBed, RowType, ForVaucherN, TempGuestID) values(@ArtNumber, 0, '', '', '', '', '', 'бр.', null, null, null, @TotalNights21, @RealPrice21, @GrTotal21, @ArticleName21, 0, @Disc21, @ServiceNum21, @Currency21, @ArticleName21, null, null, 10, @ForVaucherN21, @TempGuestID) ; end for; select sum(IsNull(GrandTotal,0)) into @SubTotalValue from #VaucherArticles where TempGuestID = @TempGuestID; set @TotalSumForVaucher = @TotalSumForVaucher + @SubTotalValue; -- zapis sub-total za gost set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumber,0,null,null, null,null,null,null,null,null, null,null,null,@SubTotalValue,null, null,null,null,null,null,null, null,null,null,11,0); end if; -- Yanko - set @ArtNumber=@ArtNumber+1; end for; // end for f1 //-------------------------------------------------- ivaylo for f5 as curs5 scroll cursor for select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed,ForVaucherN as @ForVaucherN, BoardPlan as @BPNum,RoomType as @RoomType from ProVaucherArticles where ProformNo = @ProformNo and @VaucherNo=0 and @ForVaucherN<>0 and @ForVaucherN = @VV and TempGuestID = 0 order by ArticleNo asc do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN) values( @ArtNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency, @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo, @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType, 10,@ForVaucherN) ; set @TotalSumForVaucher = @TotalSumForVaucher + IsNull(@GrTotal,0); set @ArtNumber=@ArtNumber+1 end for; //-------------------------------------------------- ivaylo -- zapis sub-total na vaucher insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSumForVaucher,null, null,null,null,null,null, null,null,3,0) ; set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,null,null, null,null,null,null,null, null,null,0,0) ; set @ArtNumber=@ArtNumber+1 end for; // end for f2 select sum(IsNull(GrandTotal,0)) into @SuperTotalSum from #VaucherArticles where RowType = 3; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@SuperTotalSum,null, null,null,null,null,null, null,null,5,0) ; set @ArtNumber=@ArtNumber+1; // extra discounts select count(*) into @BrExtras from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0; if @BrExtras > 0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,null,null, null,null,null,null,null, null,null,0,0) ; set @ArtNumber=@ArtNumber+1 end if; set @TotalAddSum=0; for f3 as curs3 scroll cursor for select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,ForVaucherN as @ForVaucherN, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0 and ServiceNo<>-8 and ForVaucherN=0 do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName, null,0,@Disc,@ServiceNum,@Currency, @TranslateName,null,null,null,null, null,null,null,null,null, 2,@VaucherNo) ; set @ArtNumber=@ArtNumber+1; set @TotalAddSum = @TotalAddSum + @GrTotal1 end for; if @TotalAddSum<>0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalAddSum,null, null,null,null,null,null, null,null,4,0) ; end if; //------------------------------------------------------------------------------------- Ivaylo Depozit for f4 as curs4 scroll cursor for select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0 and ServiceNo=-8 do set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName, null,0,@Disc,@ServiceNum,@Currency, @TranslateName,null,null,null,null, null,null,null,null,null, 7) ; end for; //------------------------------------------------------------------------------------- Ivaylo Depozit -- zapis Total na Invoisa select sum(IsNull(GrandTotal,0)) into @SuperTotalValue from #VaucherArticles where RowType in (4,5,7); set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumber,0,null,null, null,null,null,null,null,null, null,null,null,@SuperTotalValue,null, null,null,null,null,null,null, null,null,null,6,0); select IntStojnost into @FlagWithSubTotals from Registers where RegisterName = 'VSmetkaSubTotals'; if @FlagWithSubTotals = 0 then delete from #VaucherArticles where RowType = 3; -- Yanko + /* delete from #VaucherArticles where RowType = 0 and ArticleNumber >= 1 and ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1) */ end if; delete from #VaucherArticles where RowType = 0; /* select count(*) into @BroiSubTotals from #VaucherArticles where RowType = 3; if @BroiSubTotals = 1 then delete from #VaucherArticles where RowType = 3; end if; */ //update #VaucherArticles set ArticleNumber = Number(); while ((Select Max(ArticleNumber) from #VaucherArticles) > 0) loop set @MinAn = (select Min(ArticleNumber) from #VaucherArticles); if @MinAn > 0 then set @MinAn = 0 end if; Update #VaucherArticles set ArticleNumber = @MinAn - 1 where ArticleNumber = (Select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0); end loop; Update #VaucherArticles set ArticleNumber = ArticleNumber * (-1); Message '+ PreparePrintAgainVaucherProform END'; -- Yanko - end // [v 1] // [v 15.Jan.2007] Yanko - poreden N sled iztrivane na red GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('PrepareValBillByProform'))) THEN Drop procedure PrepareValBillByProform END IF Go create procedure DBA.PrepareValBillByProform(@ProformNo integer) // Tazi procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles //se pravi smetka dogovor-vaucher po predvaritelno izbrana proforma begin atomic declare @ArtNumber integer; declare @TotalSum double; declare @TotalAddSum double; declare @SuperTotalSum double; declare @BrExtras integer; declare @FlagWithSubTotals integer; declare @MinAn integer; declare @SubTotalValue double; // Yanko declare @SuperTotalValue double; // Yanko declare @BroiSubTotals integer; // Yanko -- ---------------------------------------- Message '+ PrepareValBillByProform START'; set @ArtNumber=0; set @TotalSum=0; set @TotalAddSum=0; delete from #VaucherArticles; //Zima si4ki no6tuvki po vaucheri ot proforma for f2 as curs2 scroll cursor for select distinct VaucherN as @VV from ProVaucherArticles where VaucherN <> 0 and ProformNo = @ProformNo order by VaucherN asc do set @TotalSum=0; for f1 as curs1 scroll cursor for select ArticleNo as @OldArtNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed, BoardPlan as @BPNum,RoomType as @RoomType, TempGuestID as @TempGuestID from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = @VV order by TempGuestID, ArticleNo asc do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN,TempGuestID) values( @ArtNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency, @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo, @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType, 1,@VaucherNo,@TempGuestID) ; -- Yanko +, razbivane po gost - zapis na otstypiki/dadcenki po gost if @OldArtNo = (Select Max(ArticleNo) from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = @VV and TempGuestID = @TempGuestID) then for mig21 as cursmig21 scroll cursor for select ArticleNo as @ArtNo21, TotalNights as @TotalNights21, SinglePrice as @RealPrice21, GrandTotal as @GrTotal21, ArticleName as @ArticleName21, Discount as @Disc21, ServiceNo as @ServiceNum21, Currency as @Currency21,CreditNo as @CreditNo21, SeasonNo as @SeassonNo21, ForVaucherN as @ForVaucherN21 from ProVaucherArticles where ProformNo = @ProformNo and ForVaucherN <> 0 and ForVaucherN=@VV and VaucherN = 0 and RowType in (10,12) and TempGuestID = @TempGuestID order by ArticleNo asc do set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType, VaucherBedType, Measure, Adult, Child, Nights, TotalNights, SinglePrice, GrandTotal, ArticleName, CreditNo, Discount, ServiceNo, Currency, TranslateName, TranslateSeason, TranslateBed, RowType, ForVaucherN, TempGuestID) values(@ArtNumber, 0, '', '', '', '', '', 'бр.', null, null, null, @TotalNights21, @RealPrice21, @GrTotal21, @ArticleName21, 0, @Disc21, @ServiceNum21, @Currency21, @ArticleName21, null, null, 10, @ForVaucherN21, @TempGuestID) ; end for; select sum(IsNull(GrandTotal,0)) into @SubTotalValue from #VaucherArticles where TempGuestID = @TempGuestID; set @TotalSum = @TotalSum + @SubTotalValue; -- zapis sub-total za gost set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumber,0,null,null, null,null,null,null,null,null, null,null,null,@SubTotalValue,null, null,null,null,null,null,null, null,null,null,11,0); end if; -- Yanko - set @ArtNumber=@ArtNumber+1; end for; // end for f1 //------------------------------------------------------------- ivaylo for f5 as curs5 scroll cursor for select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo,ForVaucherN as @ForVaucherN, HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed, BoardPlan as @BPNum,RoomType as @RoomType from ProVaucherArticles where ProformNo = @ProformNo and ForVaucherN<>0 and ForVaucherN=@VV and VaucherN =0 and TempGuestID = 0 order by ArticleNo asc do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN) values( @ArtNumber,0,@VaucherNum,@Seasson,@BoardPlan, @Room,@AdditionBed,'бр.',@Adult,@Child, @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName, @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency, @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo, @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType, 10,@ForVaucherN) ; if @GrTotal is null then set @GrTotal=0; end if; set @TotalSum=@TotalSum+@GrTotal; set @ArtNumber=@ArtNumber+1 end for; //------------------------------------------------------------- ivaylo insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalSum,null, null,null,null,null,null, null,null,3,0) ; -- zapis sub-total na vaucher set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,null,null, null,null,null,null,null, null,null,0,0) ; set @ArtNumber=@ArtNumber+1 end for; // end for f2 select sum(GrandTotal) into @SuperTotalSum from #VaucherArticles where RowType = 3; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@SuperTotalSum,null, null,null,null,null,null, null,null,5,0) ; set @ArtNumber=@ArtNumber+1; // extra discounts select count(*) into @BrExtras from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0; if @BrExtras > 0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,null,null, null,null,null,null,null, null,null,0,0) ; set @ArtNumber=@ArtNumber+1 end if; set @TotalAddSum=0; for f3 as curs3 scroll cursor for select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,ForVaucherN as @ForVaucherN, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0 and ForVaucherN=0 and ServiceNo<>-8 do insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName, null,0,@Disc,@ServiceNum,@Currency, @TranslateName,null,null,null,null, null,null,null,null,null, 2,@VaucherNo) ; set @ArtNumber=@ArtNumber+1; set @TotalAddSum=@TotalAddSum+@GrTotal1 end for; if @TotalAddSum<>0 then insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,RowType,ForVaucherN) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,null,null,@TotalAddSum,null, null,null,null,null,null, null,null,4,0) ; end if; //------------------------------------------------------------------------------------- Ivaylo Depozit for f4 as curs4 scroll cursor for select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson, VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child, Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName, Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo, TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed, GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0 and ServiceNo=-8 do set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard, VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency, TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo, HumanKind,AdultType,ExtraBed,BoardPlan,RoomType, RowType) values( @ArtNumber,0,null,null,null, null,null,'бр.',null,null, null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName, null,0,@Disc,@ServiceNum,@Currency, @TranslateName,null,null,null,null, null,null,null,null,null, 7) ; end for; //------------------------------------------------------------------------------------- Ivaylo Depozit -- zapis Total na Invoisa select sum(IsNull(GrandTotal,0)) into @SuperTotalValue from #VaucherArticles where RowType in (4,5,7); set @ArtNumber=@ArtNumber+1; insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason, VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child, Nights,TotalNights,SinglePrice,GrandTotal,ArticleName, GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName, TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( @ArtNumber,0,null,null, null,null,null,null,null,null, null,null,null,@SuperTotalValue,null, null,null,null,null,null,null, null,null,null,6,0); /* select count(*) into @BroiSubTotals from #VaucherArticles where RowType = 3; if @BroiSubTotals = 1 then delete from #VaucherArticles where RowType = 3; end if; */ select IntStojnost into @FlagWithSubTotals from Registers where RegisterName = 'VSmetkaSubTotals'; if @FlagWithSubTotals = 0 then delete from #VaucherArticles where RowType = 3; -- Yanko + /* delete from #VaucherArticles where RowType = 0 and ArticleNumber >= 1 and ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1) */ end if; delete from #VaucherArticles where RowType = 0; //update #VaucherArticles set ArticleNumber = Number(); while ((Select Max(ArticleNumber) from #VaucherArticles) > 0) loop set @MinAn = (select Min(ArticleNumber) from #VaucherArticles); if @MinAn > 0 then set @MinAn = 0 end if; Update #VaucherArticles set ArticleNumber = @MinAn - 1 where ArticleNumber = (Select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0); end loop; Update #VaucherArticles set ArticleNumber = ArticleNumber * (-1); Message '+ PrepareValBillByProform END'; -- Yanko - end // [v 1] // [v 15.Jan.2007] Yanko - poreden N sled iztrivane na red GO alter table VidSmetki Modify SmetkaIme varchar(60) NOT NULL; GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('SetSubtotalNames'))) THEN Drop procedure SetSubtotalNames END IF GO create procedure SetSubtotalNames(in @VidSmetka integer) begin declare @NewName varchar(40); declare @DeskN integer; -- -------------------------- for f0 as curs0 scroll cursor for select ArticleNumber as @ArticleNumber, RowType as @RowType from #VaucherArticles where RowType in (3,4,5,6,11) order By @ArticleNumber do set @NewName = null; if @RowType = 11 then set @DeskN = 1; else if @RowType = 3 then set @DeskN = 2; else if @RowType = 4 then set @DeskN = 3; else if @RowType = 5 then set @DeskN = 4; else if @RowType = 6 then set @DeskN = 5; end if; end if; end if; end if; end if; select SS.Stoinost into @NewName from SvoVaucherStn as SS, VaucherInvAttrib as VIA, VidSmetki as VS where VIA.N = SS.Glava and SS.Svoistvo = VS.SvoService and SS.SvoKind = 'atr' and VS.N = @VidSmetka and VIA.N = @DeskN; if (@NewName is null) then set @NewName = (Select Deskription from VaucherInvAttrib where N = @DeskN); end if; update #VaucherArticles set ArticleName = @NewName, TranslateName = @NewName where ArticleNumber = @ArticleNumber; end for; end; GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherArticlesByVidSmetka'))) THEN Drop procedure VaucherArticlesByVidSmetka END IF GO create procedure DBA.VaucherArticlesByVidSmetka(in @VidSmetka integer,in @oldRate double,in @newRate double,in @NewCurrency integer) // Ako se smeni vida na smetkata tazi procedura zapisva korektnite stojnosti // i promenia imeto, ako e vuvedeno begin declare @NewName varchar(40); declare @Season varchar(40); declare @ExtraBed varchar(40); // declare @ExtraBedN integer; declare @Price double; declare @Total double; declare @NightsTotal double; declare @ExtresTotal double; declare @lGrTotal double; declare pos integer; declare endpos integer; -- ----------------------------------- Message '+ VaucherArticlesByVidSmetka START'; set @price=0; set @lGrTotal=0; for f0 as curs0 scroll cursor for select ArticleNumber as @ArticleNumber,VaucherN as @VaucherNo,SinglePrice as @ArtPrice, ArticleName as @ArticleName,VaucherSeason as @VaucherSeason,VaucherBedType as @VaucherBedType, ExtraBed as @ExtraBedN,Discount as @Discount,RowType as @RowType,TotalNights as @TotalNights, GrandTotal as @ArticleSum,ServiceNo as @ServiceNo,Nights as @Nights,ForVaucherN as @ForVaucherN, OtherDiskN as @OtherDiskN, TranslateName as @OldTranslateName from #VaucherArticles order By @ArticleNumber do set @NewName=null; set @Season=null; set @ExtraBed=null; if @oldRate = @newRate then set @Price=@Artprice; set @total=@ArticleSum else if @RowType=3 then set @lGrTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0); set @total=@lGrTotal; else if @RowType=5 then if (Select Count(*) from #VaucherArticles where RowType=3) > 0 then set @total=IsNull((select SUM(IsNull(GrandTotal,0)) from #VaucherArticles where RowType=3),0); else set @Total = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN is not null and ForVaucherN > 0),0); end if; else if @RowType=4 then set @total=IsNull((select SUM(IsNull(GrandTotal,0)) from #VaucherArticles where RowType=2),0); else if ((@RowType=2) and (@Discount<>0)) then if (Select Count(*) from #VaucherArticles where RowType=3) > 0 then set @total=(select SUM(GrandTotal) from #VaucherArticles where RowType=3); else set @Total = (select Sum(GrandTotal) from #VaucherArticles where RowType=5); set @Total = (@Total * @OldRate) / @NewRate; end if; set @total=@total*(1+(Select Rate from VATRates where ID=4)); set @total=(@total*@Discount)/100; else if @RowType=6 then set @total=(select SUM(GrandTotal) from #VaucherArticles where RowType in (4,5)); else set @price = @Artprice * @oldRate; set @price = @price / @newRate; set @total = @ArticleSum * @oldRate; set @total = @total / @newRate; end if end if end if end if end if; end if; if ((@VaucherNo <> 0) or ((@VaucherNo=0) and (@ForVaucherN<>0) and (@Discount<>0))) then select SS.Stoinost into @NewName from VidSmetki as VS,SvoServicesStn as SS where VS.N = @VidSmetka and VS.SvoService = SS.Svoistvo and SS.Glava = @ServiceNo; if(@NewName is null) then set @NewName=@ArticleName end if; set Pos = Locate(@OldTranslateName, '('); if (@RowType = 12) and (Pos > 0) then set endpos = Locate(@OldTranslateName, ')'); set @VaucherSeason = SubString(@OldTranslateName, Pos + 1, endpos-pos-1); end if; select SS.Stoinost into @Season from SvoVaucherStn as SS,PriceSeasons as PS,VidSmetki as VS where PS.SeasonNo = SS.Glava and SS.Svoistvo = VS.SvoService and SS.SvoKind = 'sea' and VS.N = @VidSmetka and PS.Description = @VaucherSeason; if(@Season is null) then set @Season=@VaucherSeason end if; select SS.Stoinost into @ExtraBed from SvoVaucherStn as SS,ExtraBed as EB,VidSmetki as VS where EB.N = SS.Glava and SS.Svoistvo = VS.SvoService and SS.SvoKind = 'bed' and VS.N = @VidSmetka and EB.N = @ExtraBedN; if(@ExtraBed is null) then set @ExtraBed=@VaucherBedType end if; if (@RowType = 12) then set @NewName = @OldTranslateName; if Pos > 0 then set @NewName = SubString(@NewName,1,Pos-2); end if; set @NewName = @NewName + ' (' + @Season + ')'; set @Season = null; end if; update #VaucherArticles set TranslateName = @NewName,TranslateSeason = @Season,TranslateBed = @ExtraBed, SinglePrice = @Price,GrandTotal = @Total,Currency = @NewCurrency where ArticleNumber = @ArticleNumber; else if (@ForVaucherN=0) and (@RowType <> 12) then select SS.Stoinost into @NewName from VidSmetki as VS,SvoVaucherStn as SS where VS.N = @VidSmetka and VS.SvoService = SS.Svoistvo and SS.Glava=@OtherDiskN and SS.SvoKind = 'srv'; if(@NewName is null) then set @NewName=@ArticleName end if; if((@RowType<>2) or ((@RowType=2) and (@Discount<>0))) then update #VaucherArticles set TranslateName = @NewName,SinglePrice = @Price,GrandTotal = @Total, Currency = @NewCurrency where ArticleNumber = @ArticleNumber; else update #VaucherArticles set SinglePrice = @Price,GrandTotal = @Total, Currency = @NewCurrency where ArticleNumber = @ArticleNumber; end if; else update #VaucherArticles set SinglePrice = @Price,GrandTotal = @Total, Currency = @NewCurrency where ArticleNumber = @ArticleNumber; end if; end if; end for; call SetSubtotalNames(@VidSmetka); Message '+ VaucherArticlesByVidSmetka END'; end GO IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('SetVtdWithoutVatProf'))) THEN Drop procedure SetVtdWithoutVatProf END IF GO create procedure SetVtdWithoutVatProf() begin declare InvName varchar(50); declare SetName varchar(50); declare MinN integer; set InvName='Опростена Проформа договор - ваучер в '; for f as curs scroll cursor for select KodCurrency as @KodCurrency,Symbol as @Symbol from Currencies where KodCurrency<=3 order By KodCurrency do set SetName=InvName+@Symbol; if (select count(*) from VidSmetki where SmetkaIme=SetName)=0 then select MIN(N) into MinN from VidSmetki; if MinN>0 then set MinN=0; end if; insert into VidSmetki(N,SmetkaIme,KodCurrency,SmetkaFile,IsFaktura,SvoService,TextPrint,SlujSmetka,Language) values ((MinN-1),SetName,@KodCurrency,'OprVaucherProEng7.qri',1,0,0,6,0); end if end for; end; GO Call SetVtdWithoutVatProf(); GO IF (Select Count(*) from OtherDiskForVaucherType where ID=3) = 0 THEN Insert Into OtherDiskForVaucherType(ID,Deskription) values(3,'Безплатни нощувки'); END IF