DBProject/UpdateScripts/Arj/Script004.sql - master - AstroJohn/MailRobot

DBProject/UpdateScripts/Arj/Script004.sql
217 строк5 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
Alter Procedure dbo.[АНАЛИЗ Результат Текст]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    06.02.2012 23:20:35
-- <UpdateDate>    
-- <Parameters>
@Callsign Varchar(20),
@Category Varchar(50),
@Recalc Bit = 1,
@Out Varchar(max) = Null Out

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>True<Checker>Кузнецов Евгений<CopyDate>18.07.2013 23:43:37<Copier>John<Version>3<CopyOrder>
-- <CheckOut>False<CheckOutDate>01.03.2019 13:27<CheckOutAuthor>Кузнецов Евгений
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On		
	
	Declare @CLAIMED_RESULTS Int
	Set @CLAIMED_RESULTS = 1
	
	Declare @Year Int
	Set @Year = cast(dbo.Settings_Get('Year') As Int)
	
	Declare @ContestName Varchar(255)
	Set @ContestName = cast(dbo.Settings_Get('ContestName') As Varchar(255))
	
	If isnull(@Category, '') = ''
		Select Top 1
			@Category = Category
		From LogFilesCategory
		Where Call = @Callsign
	
	If @Recalc = 1
		Exec CalcOneResult
			@My_Callsign = @Callsign,
			@My_Category = @Category,
			@Res_Type = @CLAIMED_RESULTS,
			@Year = @Year,
			@ContestName = @ContestName,
			@Batch = 0,
			@Debug = 0
	
	Declare @OblID Varchar(2)
	
	Select Top 1
		@OblID = OblId
	From LogFiles LF
	Where LF.Call = @Callsign
	
	Declare @Res Table
	(
		Callsign Varchar(20),
		Category Varchar(50),
		Band Varchar(10),
		Mode Varchar(10),
		QSOs Int,
		Points Int,
		Mult1 Int,
		Mult2 Int,
		Mult3 Int,
		Score Int,
		ID Int identity(1, 1) Primary Key
	)
	
	Insert @Res
	Exec [АНАЛИЗ Результат] @Callsign, @Category, @CLAIMED_RESULTS
	
	If cast(dbo.Settings_Get('SP_LO') As Bit) = 1 
		Begin
			If @OblID In ('SP', 'LO') 
			And left(@Category, 6) != 'SP LO '		
				Set @Category = 'SP LO ' + @Category
			Else If @OblID Not In ('SP', 'LO') 
			And left(@Category, 6) = 'SP LO '		
				Set @Category = replace(@Category, 'SP LO ', '')
		End		
				
	Select 
		@Category = C.Description
	From Category C
	Where C.Category = @Category
	
	Declare @Band Varchar(10)
	Declare @Mode Varchar(10)
	Declare @QSOs Int
	Declare @Points Int
	Declare @Mult1 Int
	Declare @Mult2 Int
	Declare @Mult3 Int
	Declare @Score Int
	Declare @CRLF Varchar(2)
	Declare @Obl Varchar(10)
		
	Set @CRLF = Char(13) + Char(10)
	
	Declare @i Int
	Declare @N Int
	
	Set @i = 0
	
	Select
		@N = count(*)
	From @Res
	
	While @i < @N
		Begin
		
			Set @i = @i + 1
			
			Select
				@Band = Res.Band,
				@Mode = Res.Mode,
				@QSOs = Res.QSOs,
				@Points = Res.Points,
				@Mult1 = Res.Mult1,
				@Mult2 = Res.Mult2,
				@Mult3 = Res.Mult3,
				@Score = Res.Score,
				@Obl = LogFiles.OblID
			From @Res Res
			Join LogFiles On LogFiles.Call = Res.Callsign
			Where Res.ID = @i
			
			If @i = 1
				Begin
					Set @Out = 'CALLSIGN: ' + @Callsign + @CRLF
					Set @Out = @OUT + 'CATEGORY: ' + isnull(@Category, '') + @CRLF + @CRLF
					Set @Out = @Out + '=======================================' + @CRLF					
					
					If @ContestName = 'ALRS-UA1DZ-CUP'
						If @Obl In ('SP', 'LO')					
							Set @Out = @Out + '    Band  Mode  QSOs Points  DXCC   OBL' + @CRLF					
						Else
							Set @Out = @Out + '    Band  Mode  QSOs Points   RDA' + @CRLF					
					Else If @ContestName = 'RF-CHAMP-SSB'			
						Set @Out = @Out + '    Band  Mode  QSOs Points  Zones  Obl' + @CRLF					
					Else If @ContestName = 'SPB-OPEN-VHF'	
					Or @ContestName = 'VHF-ACTIVITY'	
						Set @Out = @Out + '    Band  Mode  QSOs Points WWLoc' + @CRLF					
					Else If @ContestName = 'LO-OPEN-VHF'	
						Set @Out = @Out + '    Band  Mode  QSOs Points' + @CRLF					
					Else If @ContestName = 'TWOCAPITALS'	
						Set @Out = @Out + '    Band  Mode  QSOs Points' + @CRLF					
						
					Set @Out = @Out + '=======================================' + @CRLF					
					
				End
				
			If @ContestName = 'RF-CHAMP-SSB' 
			And (@Mode = 'CW' Or (@Mode = 'PH' And @Band = 'Total'))
				Set @Out = @Out
			If @ContestName = 'LO-OPEN-VHF' 
			And (@Mode = 'CW' Or @Mode = 'SSB')
				Set @Out = @Out
			Else
				Begin
					If
					(
						@QSOs = 0
						And @ContestName = 'VHF-ACTIVITY'
					)
					Or
					(
						@Band = 'Total'
						And @Mode <> 'All'
					)
						Set @Out = @Out + ''
					Else
						Begin
							Set @Out = @Out +
								right('      ' + @Band, 8) +
								right('      ' + @Mode, 6) +
								right('      ' + cast(@QSOs As Varchar), 6) +
								right('       ' + cast(@Points As Varchar), 7)
							
							If @ContestName = 'ALRS-UA1DZ-CUP'
								If @Obl In ('SP', 'LO')	
									Set @Out = @Out +		
										right('      ' + cast(@Mult1 As Varchar), 6) +
										right('      ' + cast(@Mult3 As Varchar), 6)
								Else
									Set @Out = @Out +		
										right('      ' + cast(@Mult2 As Varchar), 6)
							Else If @ContestName = 'RF-CHAMP-SSB'			
								Set @Out = @Out +		
									right('      ' + cast(@Mult1 As Varchar), 6) +
									right('      ' + cast(@Mult2 As Varchar), 6)
							Else If @ContestName = 'VHF-ACTIVITY'			
							Or @ContestName = 'SPB-OPEN-VHF'
								Set @Out = @Out +		
									right('      ' + cast(@Mult1 As Varchar), 6)
																		
							Set @Out = @Out + @CRLF
						End
				End
				
			If @i = @N
				Begin
					Set @Out = @Out + '=======================================' + @CRLF					
					Set @Out = @Out + 'Score: ' + cast(@Score As Varchar)+ @CRLF					
				End
			
		End
	
	Return
	
End