ftp.sanbilgisayar.com/ftp.sanbilgisayar.com.tar/ftp.sanbilgisayar.com/Sejour4/ApollonGolfStrProc.sql

Text File | 2007-01-26 | 7KB | 135 lines

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object:  Stored Procedure dbo.Golf_Transfer_Delete    Script Date: 21/01/2004 12:41:25 ******/
CREATE PROCEDURE Golf_Transfer_Delete
     @GelTrfNo int, @DonTrfNo Int, @Unvan VarChar(4)
AS
 Declare @Yet SmallInt, @Coc SmallInt,@Beb SmallInt
 Select @Yet=0, @Coc=0, @Beb=0
 Select @Yet = Case When @Unvan in ('Mr','Mrs','Grp') then  1 end,
            @Coc = Case When @Unvan = 'Chd' Then  1 end,
            @Beb = Case When @Unvan = 'Inf' Then  1 end
 Select @Yet=IsNull(@Yet,0), @Coc=IsNull(@Coc,0), @Beb=IsNull(@Beb,0)
  /*Geli■ Transfer Bilgisini Silme*/
  if @GelTrfNo> 0
   if Exists (Select * From GolfTrans Where RecNo = @GelTrfNo)
      if(Select IsNull(Yet,0)+IsNull(Coc,0)+IsNull(Beb,0) From GolfTrans Where RecNo = @GelTrfNo ) <= 1
      Delete GolfTrans Where RecNo = @GelTrfNo
    else
      Update GolfTrans Set Yet = IsNull(Yet,0) - @Yet, Coc = IsNull(Coc,0) - @Coc, Beb = IsNull(Beb,0) - @Beb Where RecNo = @GelTrfNo
  /*D÷nⁿ■ Transfer Bilgisini Silme*/
  if @DonTrfNo > 0
   if Exists (Select * From GolfTrans Where RecNo = @DonTrfNo)
    if (Select IsNull(Yet,0)+IsNull(Coc,0)+IsNull(Beb,0) From GolfTrans Where RecNo = @DonTrfNo ) <= 1
      Delete GolfTrans Where RecNo = @DonTrfNo
    else
      Update GolfTrans Set Yet = IsNull(Yet,0) - @Yet, Coc = IsNull(Coc,0) - @Coc, Beb = IsNull(Beb,0) - @Beb Where RecNo = @DonTrfNo

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE Golf_Transfer_Islemi
     @CURMUSNO  Int, @CURSIRA Smallint,@Tip Char(5)
AS
 Declare @MusNo Integer, @RecNo Integer, @Turop VarChar(6), @Sira SmallInt,
         @Tarih DateTime, @GelisYeri VarChar(20), @VarisYeri  VarChar(20),
         @GidTrfTuru VarChar(4), @DonTrfTuru VarChar(4),@Bolge VarChar(3),
         @Yet SmallInt, @Coc SmallInt, @Beb SmallInt, @GelAracNo SmallInt, @DonAracNo SmallInt,
         @Otel VarChar(6), @GidTrfSaat DateTime, @DonTrfSaat DateTime,
         @Yon1 VarChar(3),@Yon2 VarChar(3),
         @TeeTime DateTime
 Select @Yet=0, @Coc=0, @Beb=0
 Select @Yet = Case When Unvan in ('Mr','Mrs','Grp') then  1 end,@Coc = Case When Unvan = 'Chd' Then  1 end,@Beb = Case When Unvan = 'Inf' Then  1 end,
        @Turop = Turop, @GelisYeri = GelYeri, @VarisYeri = DonYeri
 From GolfMus Where MusNo = @CURMUSNO
 Select @Yet=IsNull(@Yet,0), @Coc=IsNull(@Coc,0), @Beb=IsNull(@Beb,0)
 Select @Turop=Turop,
        @GelisYeri=Otel,
        @VarisYeri=Saha,
        @Tarih=GidTrfTar,
        @GidTrfSaat =GidTrfSaat,
        @GidTrfTuru=GidTrfTuru,
        @DonTrfSaat =DonTrfSaat,
        @DonTrfTuru=DonTrfTuru,
        @TeeTime=Saat
 From RezGolf
 Where MusNo = @CURMUSNO and  Sira = @CURSIRA and  Conf='Y' and RezType<>'C'  and Tarih is not null
 if @GidTrfTuru is Null Select @GidTrfTuru = ''
 if @DonTrfTuru is Null Select @DonTrfTuru = ''
/*-----------G▌D▌▐ TRF.▌▐LEM▌---------------*/
 if   @Tip='Gidis'
Begin
  Select @Bolge=Bolge, @Yon2= TrfBolge From GolfSaha Where Kodu = @VarisYeri /*Saha*/
  Select @Yon1= TrfBolge  From Otel Where Otel = @GelisYeri /*Otel*/
  Select @RecNo=0
  if @GelisYeri<>'' and  @VarisYeri<>'' and @Tarih is not Null
  begin
    if Exists (Select * From GolfTrans
               Where MusNo = @CURMUSNO and Sira = @CURSIRA and Tarih = @Tarih and Gd = 'G' and Turop = @Turop and GelisYeri = @GelisYeri and VarisYeri = @VarisYeri  and TrfTuru = @GidTrfTuru and TeeTime = @TeeTime and isnull(Saat,0) = isnull(@GidTrfSaat,0))
    begin /*Transfer hareketi bulundu*/
      Select @RecNo = RecNo From GolfTrans
      Where Tarih = @Tarih and Gd = 'G' and Turop = @Turop and GelisYeri = @GelisYeri and VarisYeri = @VarisYeri  and TrfTuru = @GidTrfTuru and TeeTime = @TeeTime and isnull(Saat,0) = isnull(@GidTrfSaat,0)
      Update GolfTrans
      Set Yet = IsNull(Yet,0) + @Yet, Coc = IsNull(Coc,0) + @Coc, Beb = IsNull(Beb,0) + @Beb,
          Yon1 = @Yon1, Yon2 = @Yon2, Bolge = @Bolge, Chg = 'N',Gt='O', Vt='S'
      Where RecNo = @RecNo
    end /*if Exists*/
    else
    begin
      Select @RecNo = IsNull(Max(RecNo),0)+1  From GolfTrans
      Insert Into GolfTrans
        (Tarih, Gd, Turop, GelisYeri, VarisYeri, TrfTuru, TeeTime, Gt, Vt, RecNo, Bolge, Oncelik, Yon1, Yon2, Saat, Yet, Coc, Beb,Chg,MusNo, Sira)
      Values
        (@Tarih, 'G', @Turop, @GelisYeri, @VarisYeri, @GidTrfTuru, @TeeTime, 'O', 'S', @RecNo, @Bolge, '', @Yon1, @Yon2, @GidTrfSaat, @Yet,@Coc,@Beb, 'N', @CURMUSNO, @CURSIRA)
    end /*Else*/
    Update RezGolf Set GelTrfNo=@RecNo, GelAracNo=1 Where MusNo=@CURMUSNO and Sira=@CURSIRA
   end /*Geli■ Tr.*/
 end /* Tip */
 /*-----------D╓N▄▐ TRF.▌▐LEM▌---------------*/
 if @Tip='Donus'
Begin
  Select @Bolge=Bolge, @Yon2= TrfBolge /*@Oncelik = Replace(Str(IsNull(TrfGelSira,0),3),' ',0)*/ From Otel Where Otel = @GelisYeri /*Otel*/
  Select @Yon1= TrfBolge /*@Oncelik = Replace(Str(IsNull(TrfGelSira,0),3),' ',0)*/ From GolfSaha Where Kodu = @VarisYeri /*Saha*/
  Select @RecNo=0
  if @GelisYeri<>'' and  @VarisYeri<>'' and @Tarih is not Null
  begin
    if Exists (Select * From GolfTrans
               Where MusNo = @CURMUSNO and Sira = @CURSIRA and Tarih = @Tarih and Gd = 'D' and Turop = @Turop and GelisYeri = @VarisYeri and VarisYeri = @GelisYeri  and TrfTuru = @DonTrfTuru and TeeTime = @TeeTime and isnull(Saat,0) = isnull(@DonTrfSaat,0))
    begin /*Transfer hareketi bulundu*/
      Select @RecNo = RecNo From GolfTrans
      Where Tarih = @Tarih and Gd = 'D' and Turop = @Turop and GelisYeri = @VarisYeri and VarisYeri = @GelisYeri  and TrfTuru = @DonTrfTuru and TeeTime = @TeeTime and isnull(Saat,0) = isnull(@DonTrfSaat,0)
      Update GolfTrans
      Set Yet = IsNull(Yet,0) + @Yet, Coc = IsNull(Coc,0) + @Coc, Beb = IsNull(Beb,0) + @Beb,
          Yon1 = @Yon1, Yon2 = @Yon2, Bolge = @Bolge, Chg = 'N',Gt='S', Vt='O'
      Where RecNo = @RecNo
    end /*if Exists*/
    else
    begin
      Select @RecNo = IsNull(Max(RecNo),0)+1  From GolfTrans
      Insert Into GolfTrans
        (Tarih, Gd, Turop, GelisYeri, VarisYeri, TrfTuru, TeeTime, Gt, Vt, RecNo, Bolge, Oncelik, Yon1, Yon2, Saat, Yet, Coc, Beb,Chg, MusNo, Sira)
      Values
       (@Tarih, 'D', @Turop, @VarisYeri, @GelisYeri, @DonTrfTuru, @TeeTime, 'S', 'O', @RecNo, @Bolge, '', @Yon1, @Yon2, @DonTrfSaat, @Yet,@Coc,@Beb, 'N', @CURMUSNO, @CURSIRA)
    end /*Else*/
    Update RezGolf Set DonTrfNo=@RecNo, DonAracNo=1 Where MusNo=@CURMUSNO and Sira=@CURSIRA
   end /*Geli■ Tr.*/
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO