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

GO

create procedure DBA.FillArticleLodgings_All(in @RegNo integer,in @PackagePrice numeric(12,2),in @FillAll integer,in @place varchar(50))
begin
  declare @Price numeric(12,4);
  declare @ArticlePrice numeric(12,4);
  declare @KodCurrency integer;
  declare @NachinPlat integer;
  declare @ServiceNoLodging integer;
  declare @NumDays integer;
  declare @DateReg date;
  declare @TipDDS integer;
  declare @RoomNo integer;
  declare @RoomName varchar(6);
  declare @IsRest integer;
  declare @ArticleName varchar(100);
  declare @ServiceName varchar(40);
  declare @DateStart date;
  declare @MDays integer;
  declare @SeasonN integer;
  declare @ZInd integer;
  set @ZInd=1;
  delete from #ArticleLodgings;
  select B.Price,B.KodCurrency,B.Room,PK.NachinPlat,isNull(PK.ServiceNo,-1),B.NumLodgings,B.DateRegDt,B.IsRest into @Price, /*dateadd(day,B.PayedLodgings,B.DateRegDt)*/ /*-B.PayedLodgings*/
    @KodCurrency,@RoomNo,@NachinPlat,@ServiceNoLodging,@NumDays,@DateReg,
    @IsRest from Book as B,PriceKinds as PK
    where B.RegNum = @RegNo and B.PriceKind = PK.PriceKind;
  if(@NumDays > 0) then
    if @ServiceNoLodging = 0 then
      set @ServiceNoLodging=-1
    end if;
    select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
    select ServiceName into @ServiceName from Services where ServiceNo = @ServiceNoLodging;
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
    if @TipDDS = 2 then
      set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS=1
    end if;
    set @ArticlePrice=@ArticlePrice-@PackagePrice;
    if @TipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
      set @TipDDS=2
    end if;
    set @RoomName=(select RoomName from Rooms where RoomNum = @RoomNo);
    if @NachinPlat = 0 then
      if @IsRest = 1 then
        set @ArticleName='Почивка '+' за '+dateformat(@DateReg,'dd.mm') /*|| @place || @RoomName*/
      end if; //else  Dobrin 2612 - po-dolu shte setna @ArticleName, ako ne e pochivka
      //set @ArticleName=@ServiceName || ' ' || @place || @RoomName+' от '+dateformat(@DateReg,'dd.mm')+' до '+dateformat(dateadd(day,@NumDays,@DateReg),'Dd.Mm')
      --Dobrin 2612 +	  Za da se syobrazqva sys zapisite ot PriceSeasonPeriods - shte e syshtoto kato po-dolu, no malko po-prostichko
      if @IsRest <> 1 then
        set @DateStart=@DateReg;
        while(@NumDays > 0) loop
          set @ArticleName=@ServiceName || ' от ' || dateformat(@DateStart,'dd.mm.yyyy') || ' до '; /*|| ' ' || @place || @RoomName*/
          call GetDataForDateSeasonPeriods(@DateStart,@SeasonN,@MDays,@DateStart,@NumDays);
          if @MDays < @NumDays then
            set @NumDays=@NumDays-@MDays;
            set @ArticleName=@ArticleName || dateformat(dateadd(day,-1,@DateStart),'dd.mm.yyyy')
          else
            set @ArticleName=@ArticleName || dateformat(dateadd(day,-1,dateadd(day,@NumDays-@MDays,@DateStart)),'dd.mm.yyyy');
            set @MDays=@NumDays;
            set @NumDays=0
          end if;
          select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
          set @ArticlePrice=GetRentPriceForDate(@RegNo,null,@SeasonN);
		  if @ArticlePrice is null then 
		    set @ArticlePrice = @Price
		  end if;
          if @TipDDS = 2 then
            set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
            set @TipDDS=1
          end if;
          set @ArticlePrice=@ArticlePrice-@PackagePrice;
          if @TipDDS = 1 then
            set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
            set @TipDDS=2
          end if;
          insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS) 
            values( @RegNo,@ArticleName,@MDays,@ArticlePrice,@MDays,@MDays,0,@ZInd,@TipDDS) ;
          set @ZInd=@ZInd+1
        end loop
      else
        insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS) 
          values( @RegNo,@ArticleName,0,@ArticlePrice,@NumDays,@NumDays,0,0,@TipDDS) 
      end if --Dobrin 2612 -
    else // @NachinPlat =1
      // vurti cikul mesec po mesec i populva tablicata za vseki mesec
      set @DateStart=@DateReg;
      while(@NumDays > 0) loop
        select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,@PackagePrice,@ServiceName,@FillAll,@TipDDS,@place) into @MDays;
        set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
        set @NumDays=@MDays
      end loop
    end if; // @NachinPlat=1
    update #ArticleLodgings set ServiceNoLodging = @ServiceNoLodging where ServiceNoLodging is null
  end if // @NumDays > 0
end
--[04.09.2009] Dobrin 2612 - cenovite sezoni da okazvat vliqnie i kogato cenorazpisa ne e za mesec
--[30.09.2009] Dobrin 2672 - da sloji ZIndex kogato se razdelq na cenovi sezoni za noshtuvka, za da moje da se podrejdat posle artikulite kato svqt

GO

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

GO

create procedure DBA.GetRealDatePayedRentForPeriod_Service(in @RegNo integer,in @StartDate date,in @EndDate date)
result(InvoiceNo integer,ArticleNo integer,Qty numeric(12,4))
begin
  declare @DatePaidTo date;
  declare @PartlyPaidToOnDate date;
  declare @Lodgings integer;
  declare @InvoiceNo integer;
  declare @ArticleNo integer;
  set @PartlyPaidToOnDate=null;
  set @DatePaidTo=dateadd(day,-1,(select DateRegDt from Book where RegNum = @RegNo));
  set @InvoiceNo=0;
  set @ArticleNo=0;
  set @Lodgings=0;
  lb: for f as curs scroll cursor for
    select A.RealLodgingsNum as @Lodg,
      A.InvoiceNo as @InvNo,
      A.ArticleNo as @ArtNo from Invoices as Inv
      ,Articles as A
      where A.InvoiceNo = Inv.InvoiceNo
      and A.RegNo = @RegNo
      and Inv.CancelInvoiceNo = 0 order by
      Inv.InvoiceNo asc do
    set @DatePaidTo=@DatePaidTo+@Lodg;
    if(@DatePaidTo >= @EndDate) then
      set @InvoiceNo=@InvNo;
      set @ArticleNo=@ArtNo;
      set @Lodgings=@Lodg;
      leave lb
    end if;
    if(@DatePaidTo >= @StartDate) and(@DatePaidTo <= @EndDate) then
    end if end for;
  select @InvoiceNo,@ArticleNo,@Lodgings
end

GO

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

GO

create procedure DBA.InsertNullSaleOrders()
begin
  declare @SaleID integer;
  declare @SaleLineID integer;
  declare @HotelId integer;
  declare @HotelName varchar(100);
  //declare @ServiceNo integer;
  //declare @CreditNo integer;
  //declare @SumCredit numeric(12,4);
  //declare @Qty numeric(12,4);
  //declare @PayedQty numeric(12,4);
  //declare @ServiceName varchar(40);
  declare @NameToInsert varchar(100);
  //declare @TipDDS integer;
  //declare @DDSType integer;
  declare @StartDate date;
  declare @EndDate date;
  set @HotelID=(select IntStojnost from Registers where RegisterName = 'HotelID');
  set @hotelName=(select HotelName from Hotels where Hotel_ID = @HotelID);
  for f as curs scroll cursor for
    select WhoPays as @Guest from Book as B join Guests as G on WhoPays = G.GuestNum where((Staying in( 1,2) ) or((Staying = 3) and(G.Balance <> 0))) group by WhoPays do /*Guest as @Guest,*/
    set @SaleID=GetCounter('SaleOrderId');
    insert into SaleOrders( Id,DocType,UNP,GuestNum,HotelId,StoreName,WorkPlaceId,FiscalDeviceN,UserId,UserCode) 
      values( @SaleID,0,'OO000000-0000-0000000',@Guest,@HotelID,@HotelName,'',0,10,10) ;
    for f1 as curs1 scroll cursor for
      select RegNum as @RegNo,Room as @place,PriceKind as @priceKind,Price as @Price,DateRegDt as @RDate
        from Book where WhoPays = @Guest and NumLodgings > 0 do
      call FillArticleLodgings_All(@RegNo,0,0,@place);
      for f2 as curs2 scroll cursor for
        select Name as @ArticleName,RealLodgingsNum as @RealLodgings,A.Price as @PriceInvWithDDS,Qty as @Qty1,
          MaxQty as @MaxQty,MinQty as @MinQty,ZIndex as @ZIndex,TipDDS as @TipDDS1,ServiceNoLodging as @ServiceNo1,
          RentMonth as @RentMonth,RentYear as @RentYear,PK.NachinPlat as @NachinPlat
          from #ArticleLodgings as a //join VatRates as VR on a.TipDDS = VR.ID
          join Book as B on A.RegNum = B.RegNum
          join PriceKinds as PK on B.PriceKind = PK.PriceKind order by ZIndex asc do
        set @SaleLineID=GetCounter('SaleOrderLineID');
        insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty) 
          values( @SaleLineID,@SaleID,@ServiceNo1,@MaxQty,@PriceInvWithDDS*(1+(if @TipDDS1 = 2 then abs(bvrDDSPart) else 0
          endif)),0,(if @TipDDS1 = 2 then abs(bvrDDSPart) else 0
          endif),@MaxQty*@PriceInvWithDDS*(1+(if @TipDDS1 = 2 then abs(bvrDDSPart) else 0
          endif)),@ArticleName,convert(varchar(50),NEWID(*)),@MaxQty) ;
        if @NachinPlat = 0 then
          set @StartDate=@RDate;
          set @EndDate=dateadd(dd,@MaxQty,@RDate)
        else
          set @StartDate='01.' || @RentMonth || '.' || @RentYear;
          set @EndDate=(DATEADD(month,1,@StartDate)-DAY(@StartDate))
        end if;
        insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,StartDate,EndDate) 
          values( @SaleLineID,@RegNo,@StartDate,@EndDate) ;
        for f4 as curs4 scroll cursor for
          select Qty as @Payed,InvoiceNo as @InvNo,ArticleNo as @ArtNo from GetRealDatePayedRentForPeriod_Service(@RegNo,@StartDate,@EndDate) do
          if(@Payed = 0) and(@InvNo = 0) and(@ArtNo = 0) then
          else
            insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo) 
              values( @SaleLineID,@InvNo,@ArtNo) ;
            if @NachinPlat = 1 then
              update SaleOrderLines set PayedQty = Qty where ID = @SaleLineID
            else
              update SaleOrderLines set PayedQty = PayedQty+@Payed where ID = @SaleLineID
            end if
          end if end for end for end for;
    //f2
    //f1
    for f3 as curs3 scroll cursor for
      select SC.Service as @ServiceNo,SC.SumCredit as @SumCredit,SC.NumberServices as @Qty,S.ServiceName as @ServiceName,S.TipSdelka as @TipDDS,
        VS.TipDDS as @DDSType,SC.CreditNo as @CreditNo,PayedServices as @PayedQty from ServiceCredits as SC join Services as S on S.ServiceNo = SC.Service
        join VidSdelki as VS on S.TipSdelka = VS.N
        where SC.WhoPays = @Guest do
      set @NameToInsert=isNull((select Stoinost from SvoServiceCreditsStn where Glava = @CreditNo and Svoistvo = 1),'');
      if @NameToInsert = '' then
        set @NameToInsert=@ServiceName
      end if;
      set @SaleLineID=GetCounter('SaleOrderLineID');
      insert into SaleOrderLines( Id,DocId,ItemId,Qty,SellPrice,Discount,VatRate,RowSum,ItemName,SaleLineGuid,RegQty) 
        values( @SaleLineID,@SaleID,@ServiceNo,@Qty,@SumCredit*(1+(if @DDSType = 2 then abs(bvrDDSPart) else 0
        endif)),0,(if @TipDDS = 1 then abs(bvrDDSPart) else 0
        endif),@Qty*@SumCredit*(1+(if @DDSType = 2 then abs(bvrDDSPart) else 0
        endif)),@NameToInsert,convert(varchar(50),NEWID(*)),@Qty) ;
      update SaleOrderLines set PayedQty = PayedQty+@PayedQty where ID = @SaleLineID;
      insert into SaleOrderLines_Links( SaleLineID,Book_RegNum,ServiceCreditNo) 
        values( @SaleLineID,0,@CreditNo) ;
      insert into SaleOrderLines_Articles( SaleLineID,InvoiceNo,ArticleNo) 
        select @SaleLineID,InvoiceNo,ArticleNo from Articles where CreditNo = @CreditNo end for end for;
//f3
//f
  update Invoices
  set UNP = 'OO000000-0000-0000000'
  where InvoiceNo in (select InvoiceNo from SaleOrderLines_Articles group by InvoiceNo)
end;

GO