IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('VaucherRecalcTotals'))) THEN Drop procedure VaucherRecalcTotals END IF GO Create Procedure VaucherRecalcTotals() begin declare @SubTotal double; declare @RowN integer; -------------------------------------- -- preizchisliava subtotalite v invois -------------------------------------- -- RowTypes: -- 11 - SubTotal za gost -- 3 SubTotal na Vaucher - suma na nostuvkite -- 5 SubTotal na vsichki vaucheri -- 4 SubTotal na Extreses - suma na dopulnitelnite uslugi -- 6 Super Total = suma na Vsichko -------------------------------------- -- Sub-total za gost: for f1 as curs_f1 scroll cursor for select Distinct VaucherN as @VN, TempGuestID as @ID from #VaucherArticles where (TempGuestID <> 0) and (TempGuestID is not null) do set @RowN = (select Max(ArticleNumber) from #VaucherArticles where VaucherN = @VN and TempGuestID = @ID); set @RowN = (select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > @RowN and RowType = 11); set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @ID and RowType in (1,2,10,13)),0); --update #VaucherArticles set GrandTotal = @SubTotal where ArticleNumber = @RowN and RowType = 11; update #VaucherArticles set GrandTotal = @SubTotal where ArticleNumber = @RowN; end for; -- Sub-total za vaucher: for f1 as curs_f2 scroll cursor for select Distinct ForVaucherN as @VN from #VaucherArticles where (ForVaucherN <> 0) and (ForVaucherN is not null) and (RowType <> 3) do set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VN and RowType <> 3),0); update #VaucherArticles set GrandTotal = @SubTotal where ForVaucherN = @VN and RowType = 3; end for; -- Sub-total na vsichki vaucheri: set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType = 3),0); if @SubTotal = 0 then set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN <> 0 and ForVaucherN is not null and RowType <> 3),0) end if; update #VaucherArticles set GrandTotal = @SubTotal where RowType = 5; -- Sub-total na extreses: set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType = 2),0); update #VaucherArticles set GrandTotal = @SubTotal where RowType = 4; -- Grand Total: set @SubTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType in (4,5,7)),0); update #VaucherArticles set GrandTotal = @SubTotal where RowType = 6; end; -- v.1 [16.Jan.2008] - Yanko, Created GO IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('SumContrDepositsInCurrency'))) THEN Drop procedure SumContrDepositsInCurrency END IF GO create function SumContrDepositsInCurrency(in @ContractNo integer, in @Currency integer) returns decimal(12,4) begin declare @R decimal(12,4); select Sum(R.Sum * C.ExchangeRate) into @R from ResMoney as R,Currencies as C where R.ContractNo = @ContractNo and R.Status = 2 and R.Currency = C.KodCurrency; if @R is null then set @R=0 end if; if @Currency <> 1 then set @R = @R / (select ExchangeRate from Currencies where KodCurrency = @Currency); end if; return(@R) end GO IF (EXISTS(Select * 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 1 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 -- Yanko + --set @lGrTotal = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0); set @Total = IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @ForVaucherN and RowType=1),0); -- Yanko - 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 -- Yanko + --set @total=IsNull((select SUM(IsNull(GrandTotal,0)) from #VaucherArticles where RowType=2),0); set @Total=IsNull((select SUM(IsNull(GrandTotal,0)) from #VaucherArticles where RowType=2 and ForVaucherN = @ForVaucherN),0); else if (@RowType=2) then set @Total = (@ArticleSum * @oldRate) / @NewRate; if @Discount <> 0 then if @ArticleSum < 0 then set @Total = -1; else set @Total = 1; end if; set @lGrTotal = IsNull((select SUM(IsNull(GrandTotal,0)) from #VaucherArticles where RowType=5),0); set @Total = @Total * (@lGrTotal * @Discount) / 100; end if; -- set @Total = @Total * (1 + (Select Rate from VATRates where ID=4)); -- Yanko - else if @RowType=6 then set @Total=(select SUM(GrandTotal) from #VaucherArticles where RowType in (4,5,7)); else set @price = @Artprice * @oldRate; set @price = @price / @newRate; set @Total = (@ArticleSum * @oldRate) / @newRate; end if end if end if end if end if; end if; -- prevod 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 in (12,13)) 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 in (12,13)) 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) and (@RowType <> 13) 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; */ 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; if @RowType = 7 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; update #VaucherArticles set TranslateName = @NewName where ArticleNumber = @ArticleNumber; end if; end if; end if; end for; call SetSubtotalNames(@VidSmetka); Message '+ VaucherArticlesByVidSmetka END'; -- [21.Dec.2007] - Yanko, fix pri smiana na valutata za poveche ot edin vaucher end 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 -- 13 - Bezplatni noshtuvki po gost 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,13) order by ArticleNo, TempGuestID 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/nadcenki 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,13)) 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 RowType = 10 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, -- Yanko + --null,null,null,3,0) null,null,null,3,@VaN) -- Yanko - 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 //[v.7 21.Dec.2007] - Yanko, fix - zapis na ForVaucherN za total na vaucher Go IF (EXISTS(Select * from sys.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 -- ---------------------------------------------------------------------- -- 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 -- 13 - Bezplatni noshtuvki po gost -- ---------------------------------------------------------------------- message '+ PrepareVauchersBill START' type info to console; 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 ostanovena 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); -- dobaviane red za bezplatni noshtuvki, zadadeni v dogovora 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); -- dobaviane red za bezpl noshtuvki, zadadeni vav formata za otstapki/nadcenki za vaucher, kato dvata vida -- bezplatni noshtuvki se razlichavat kato logika - cenata na noshtuvkata na vtorite e namalena s otstykata 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); -- dobaviane na drugi otstypki ili nadcenki po vaucher/gost 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); 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 set @TotalSumForVaucher=IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0); -- ако нямаме разбиване по гост, трием тотал за гост if @HasGuestExtres = 0 then delete from #VaucherArticles where RowType = 11 end if; -- Yanko - call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForVaucher,@ContractNo,@VaucherNo,@VatType,-1,0); //------------------------------- 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; -- presmiatane Total na vauchera, s vsichki noshtuvki, uslugi i otstypki/nadcenki za vsuchki gosti po nego 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; -- presmiatane na Grand Total select sum(IsNull(GrandTotal,0)) into @TotalSum from #VaucherArticles where RowType in(4,5,7); 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) ; -- ako v registrite e ukazano smetka bez subtotali, triem subtotalite 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; -- iztrivane total na vaucher, ako v invoisa ima samo edin vaucher 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' type info to console end //[v.1 xx.Jul.2007] - Ivaylo //[v.2 13.Jul.2007] - Yanko, razdeliane po gosti //[v.3 08.Nov.2007] - Yanko, fix otstypki za celia vaucher Go IF (EXISTS(Select * from sys.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; declare @VatRate double; -- ------------------------------------------------------ message '+ VaucherDisc START' type info to console; message '========================== @TotalSumForVaucher1=' || @TotalSumForVaucher1 type info to console; set @TotalAddSum=0; set @DiskSum=0; set @mnsign=1; set @VatRate = (Select Rate from VATRates where ID=4); 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; 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 set @DiskSum=(@TotalSumForVaucher1*@TotDisk)/100; else if @VatType = 1 then set @DiskSum=(@FreeSum-(@FreeSum*(@VatRate * 100))/(100 + @VatRate * 100)) 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 + @VatRate) set @Deposit = @Deposit + SumContrDepositsInCurrency(@ContractNo1, @Currency) / (1 + @VatRate); else --set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo1) set @Deposit = @Deposit + SumContrDepositsInCurrency(@ContractNo1, @Currency); 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' type info to console end -- v.2 [11.Jan.2008] - Yanko - fix % otstypki, DDS da se vzema ot VatRates Go IF (EXISTS(Select * from sys.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; -- -------------------------------------- -- 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 -- 13 - Bezplatni noshtuvki po gost -- -------------------------------------- message '+ VaucherGuestExtres START' type info to console; 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+(select Rate from VatRates where ID = 4))) else set @RealPrice=@RealPrice end if; set @RealPrice=Round((IsNull(@RealPrice,0)),2); 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); if @GuestDiscount is null then set @GuestDiscount=0 end if; --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, 13,@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, 13,@VaucherNo,@TmpGuestID) ; set @ArticleNumber=@ArticleNumber+1 end if end if end loop; message '+ VaucherGuestExtres END' type info to console end //v.1 [17.Jul.2007] - Yanko, razdeliane po gosti //v.2 [14.Sep.2007] - Yanko, fix na @DiskName //v.3 [19.Sep.2007] - Yanko, proverka za null na @GuestDiscount + stoinostta na DDS da se vzema ot VatRates // + zakrygliane na cenata do vtoriq znak Go IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('VaucherDiscNadcenka'))) THEN Drop procedure VaucherDiscNadcenka END IF GO create procedure DBA.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 -- dobavia otstypki/nadcenki, zadadeni v +- za vaucher, bez bezplatnite noshtuvki, kym #VaucherArticles -- procedurata se vika poveche ot 1 pyt - za gost i za vaucher, kato -- ako GuestAgeGroup e >= 0, se zapisvat samo otstapki/nadbavki za gost, inache (-1) vsichki ostanali -- ( otstypki i nadbavki pri razbivane po vaucher/gost imat RowType = 10 ! ) 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 -- --------------------------------- -- 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 -- 13 - Bezplatni noshtuvki po gost -- --------------------------------- message '+ VaucherDiscNadcenka START' type info to console; 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 and -- Yanko +, razbivane po gost ((DiskType in( 0,1) and @GuestAgeGroup >= 0) or(DiskType not in( 0,1) and @GuestAgeGroup < 0)) order by DiskType desc do -- Yanko - 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 set @Discount=@FreeSum; set @GrantTotal=@TotalSumForVaucher1*(@Discount/100) 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 else -- za vaucheri if @DiskType = 2 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,@Discount,@ServNo, @Currency,@ArtName,null, null,12,@VaucherN1,0) ; set @ArticleNumber=@ArticleNumber+1 end if end if end if end for; message '+ VaucherDiscNadcenka END' type info to console end //[v.1 13.Jul.2007] - Yanko, created, razdeliane po gosti //[v.2 08.Nov.2007] - Yanko, fix otstypki za celia vaucher Go IF (EXISTS(Select * from sys.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; --Maria + --declare @TrueInvoiceNo integer; declare @TrueInvoiceNo numeric(10,0); --Maria - 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' type info to console; -- Yanko + /* 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; */ set @NullDepositCount=(select Count(*) from #VaucherArticles where ServiceNo = -8 and TotalNights = 0); -- Yanko - //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 --Maria + --set @TrueInvoiceNo=GetCounter('TrueVaucherInvoiceNo') //TrueVaucherInvoiceNo ivaylo set @TrueInvoiceNo=GetFakturaCounter('TrueVaucherInvoiceNo') --Maria - else --Maria + --set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') //TrueVaucherInvoiceNo ivaylo set @TrueInvoiceNo=GetFakturaCounter('TrueInvoiceNo') --Maria - end if else set @TrueInvoiceNo=0 end if; //-------------------------------------------------- ivaylo -- Yanko +, mahane na subtotalite ako po dogovor niama razbivane na invoisa po vaucher ili gost if(select count(*) from OtherDiskForVaucher where ContractNo = @ContractNo) = 0 then delete from #VaucherArticles where RowType in( 3,4,5,6,11) end if; -- Yanko - 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 -- Yanko + if @NullDepositCount = 0 then if((@BillType = 7) or(@BillType = 8)) and(@ContractNo <> 0) then call CheckContractDeposits(@ContractNo,@KodAdmin,null,@NullDepositCount,@InvoiceNo) end if end if; -- Yanko - //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' type info to console; return(@InvoiceNo) end // v.x [] // v.x1 [01.Oct.2007] - Yanko, Mahane na subtotalite ako po dogovor niama razbivane na invoisa po vaucher ili gost //[v 18.01.2008] - Niki - depositi za dogovor // v.x2 [14.March.2008] - Maria, TrueInvoiceNo da e numeric i funkcia GetFakturaCounter, koiato dava stoinostta na TrueVaucherInvoiceNo ot Counters GO IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('CheckContractDeposits'))) THEN Drop procedure CheckContractDeposits END IF GO IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FinishBill'))) THEN Drop procedure FinishBill END IF GO create procedure DBA.CheckContractDeposits(in @ContractNo integer,in @KodAdmin integer,in @InvoiceNo integer,in @ZeroCountDeposit integer,in @VaucherInvoiceNo integer) //Za daden dogovor nulira istinskite mu depositi kato vkarva vuv ResMoney //zapisi s otricatelna stojnost i otricatelen N, za da ne izlizat v otchetite begin declare @DepositNo integer; declare @ExchangeRate decimal(12,6); declare @InvNo integer; // if(@InvoiceNo is null) then set @InvNo=@VaucherInvoiceNo else set @InvNo=@InvoiceNo end if; call SetInvoiceNoDeposit(@InvNo,@ContractNo); for f as curs scroll cursor for select Sum(ResMoney.Sum) as @CurrSum,ResMoney.Currency as @KodCurrency from DBA.ResMoney where ResMoney.ContractNo = @ContractNo and ResMoney.Status = 2 group by ResMoney.Currency do // select min(DepositNo)-1 into @DepositNo from DBA.ResMoney; if @DepositNo >= 0 then set @DepositNo=-1 end if; message 'Sumata na neobrabotenite depoziti do momenta -> '+cast(@CurrSum as varchar) type info to console; if @CurrSum <> 0 then insert into DBA.ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo, Status,GroupNo,ContractNo,InvoiceNo,RoomNo,AccountNumber,VaucherInvoiceNo) values( 0,0,@KodAdmin,-@CurrSum,@KodCurrency,1,'',0,@DepositNo,2,0,@ContractNo,@InvoiceNo,0,0,@VaucherInvoiceNo) ; // select Currencies.ExchangeRate into @ExchangeRate from DBA.Currencies where Currencies.KodCurrency = @KodCurrency; insert into DBA.ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,ContractNo, InvoiceNo,RoomNo,AccountNumber,VaucherInvoiceNo) values( 0,0,@KodAdmin,@CurrSum*@ExchangeRate,1,1,'',0,@DepositNo-1,(if @ZeroCountDeposit <> 0 then 2 else 1 endif),0,@ContractNo,@InvoiceNo,0,0,@VaucherInvoiceNo) end if end for // end //[v 00.05.28] //[v 18.01.2008] - Niki - depositi za dogovor GO create function DBA.FinishBill(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 @WithoutVAT decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer,in @DDSType integer) 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; --Maria + --declare @TrueInvoiceNo integer; declare @TrueInvoiceNo numeric(10,0); --Maria - declare @CancelInvoiceNo integer; declare @ServiceNo integer; declare @CreditNo integer; declare @ResMoneyStatus integer; declare @SmetkaCurrency integer; declare @NullDepositCount integer; declare @InvoiceMask integer; //ivaylo declare @DepContractNo integer; //// ivaylo 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 #Articles where ArticleQty = 0; //Vmukva dannite za fakturata set @InvoiceNo=GetCounter('InvoiceCounter'); if @IsInvoice = 1 then --Maria + --set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') set @TrueInvoiceNo=GetFakturaCounter('TrueInvoiceNo') --Maria - else set @TrueInvoiceNo=0 end if; if @BillType = -1 then set @CancelInvoiceNo=@No else set @CancelInvoiceNo=0 end if; set @ResMoneyStatus=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 //ivaylo insert into Invoices( InvoiceNo,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,DDSType) values( @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin, @Whom,'','','','',1,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),1.0,@VidSmetka,@ContractNo,@InvoiceMask,@DDSType) ; for f1 as curs1 scroll cursor for select ArticleNumber as @ArticleNumber,ArticleName as @ArticleName,ArticleQty as @ArticleQty, ArticlePrice as @ArticlePrice,RegNo as @RegNo,CreditNo as @CreditNum,ArticleSum as @ArticleSum, TipDDS as @TipDDS,Discount as @Discount,ServiceNo as @Service,ArticleSumInvCurrency as @SumInvCurrency, RegDate as @RDate from #Articles do //Ako e swobodna smetka vmukva vuv uslugite na gosta uslugite ot svobodnata smetka if @BillType = 5 then select ServiceNo into @ServiceNo from Services where ServiceNo = @Service; if(@ServiceNo <> 1005) and(@ServiceNo <> 1004) and(@ServiceNo <> -8) then set @CreditNo=GetCounter('CreditCounter'); insert into ServiceCredits( CreditNo,DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,PayedServices,WhoPays) values( @CreditNo,@InvoiceDate,@No,@ServiceNo,@ArticleSum,1,@KodAdmin,@ArticleQty,0,@No) else set @ResMoneyStatus=2; set @CreditNo=0 end if else set @CreditNo=@CreditNum end if; //Insertva zapis vuv Articles insert into Articles( InvoiceNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo, ArticleName,TipDDS,Discount,ServiceNo,RegDate,DDSType) values( @InvoiceNo,@RegNo,@ArticlePrice,@CreditNo,@ArticleSum,@SumInvCurrency, @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service,@RDate,@DDSType) ; //Promenia noshtuvkite if @RegNo > 0 then update Book set PayedLodgings = PayedLodgings+sign(@BillType)*@ArticleQty where RegNum = @RegNo end if; //Promenia uslugite if @CreditNo > 0 then update ServiceCredits set PayedServices = PayedServices+sign(@BillType)*@ArticleQty where CreditNo = @CreditNo end if 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 InvoiceNo = @No and DateMoney = 0; update Invoices set CancelInvoiceNo = -1 where InvoiceNo = @No; select max(Status) into @ResMoneyStatus from ResMoney where InvoiceNo = @No; //////////////////////////////////////////////// ivaylo set @DepContractNo=0; select MAX(ContractNo) into @DepContractNo from ResMoney where InvoiceNo = @No; if(@DepContractNo <> 0) then update DepositForContracts set GlobalInvoice = 0 where GlobalInvoice = @No and ContractNo = @DepContractNo end if end if; //////////////////////////////////////////////// ivaylo //Nulira predishnite depoziti syotvetno na gost, grupa, staia i reservacia //Nulira depositi, ako e smetka za gost if @BillType = 1 then call CheckDeposits(@No,@KodAdmin,@InvoiceNo,@NullDepositCount) end if; //Nulira depositi, ako e smetka za grupa if @BillType = 2 then for f2 as curs2 scroll cursor for select GuestNum as @GuestNum from Guests where "Group" = @No do call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for end if; //Nulira Depositi ako e smetka za staia if @BillType = 3 then for f3 as curs3 scroll cursor for select Guest as @GuestNum from Book where Room = @No and Staying = 1 do call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for end if; //Nulira depositi, ako e smetka za grupa if @BillType = 4 then for f4 as curs4 scroll cursor for select GuestNum as @GuestNum from Guests where Reservation = @No do call CheckDeposits(@GuestNum,@KodAdmin,@InvoiceNo,@NullDepositCount) end for end if; //Nulira depositi, ako e smetka po dogovor - fixed if(@BillType = 7) and(@ContractNo <> 0) then call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount,null) end if; //Vmukva plashtaniata ot wremennata tablica za plashtania for f6 as curs6 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 //,ContractNo as @Contract #PaymentTypes do set @DepositNo=GetCounter('DepositCounter'); ///////////////////////////////////// ivaylo if((@ContractNo <> 0) and(@BillType = 5) and(@mGuestNo = 0)) then insert into DepositForContracts( GlobalInvoice,SubInvoice,ContractNo) values( 0,@InvoiceNo,@ContractNo) end if; //////////////////////////////////// ivaylo insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo, ContractNo,InvoiceNo,RoomNo,AccountNumber,PTVid) values( @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) end for; return(@InvoiceNo) end //[v 00.11.18] //[v 01.07.09] - Niki - za vidovete smetki //[v 18.09.02] - Dobaven e parametyr za N na Dogovora vyv vryska s Smetki po dogovori //[v 18.02.2003] Jorko //[ 02.11.2007] Kozludjov - fix depoziti po dogovor //[v 18.01.2008] - Niki - depositi za dogovor //[v 14.03.2008] Maria - tip na TrueInvoiceNo e promenen i se vika funkcia GetFakturaCounter, koiato dava nomera na fakturata GO IF (Not EXISTS(select * from sys.syscolumn as C, sys.systable as T where LCase(T.table_name) = LCase('VidSmetki') and LCase(C.column_name) = LCase('InvoiceAfterProform') and C.table_id=T.table_id)) THEN alter table VidSmetki add InvoiceAfterProform integer END IF GO COMMENT ON COLUMN VidSmetki.InvoiceAfterProform IS 'Номер на фактурата от VidSmetki, която може да се издаде по тази проформа. Тази колона има смисъл само ако реда е Проформа.' GO