DBProject/UpdateScripts/Script007.sql - master - AstroJohn/MailRobot

DBProject/UpdateScripts/Script007.sql
206 строк5 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
Alter Procedure [dbo].[АНАЛИЗ Результат]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    03.02.2012 19:43:44
-- <UpdateDate>    
-- <Parameters>
@Callsign Varchar(20),
@Category Varchar(255) = Null,
@Res_Type Int = Null

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate>18.07.2013 23:43:37<Copier>John<Version>3<CopyOrder>
-- <CheckOut>False<CheckOutDate>23.11.2016 13:42<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
	
	--+ Перечисления и константы
	--+ Вид результатов
	Declare @CLAIMED_RESULTS Int
	Declare @CONFIRMED_RESULTS Int
	
	Set @CLAIMED_RESULTS = 1
	Set @CONFIRMED_RESULTS = 2
	--/
	
    --+ Тип множителя
    Declare @MULT_DXCC Int
    Declare @MULT_OBL Int
    Declare @MULT_RDA Int
    
    Declare @FIRST_MULT Int
    Declare @LAST_MULT Int
    
    Set @MULT_DXCC = 1
    Set @MULT_RDA = 2
    Set @MULT_OBL = 3
    
    Set @FIRST_MULT = 1
    Set @LAST_MULT = 3
    --/
    
    --+ Диапазоны и виды работы
 	Declare @ALL_BANDS Int
 	Declare @LAST_BAND Int
 	Declare @ALL_MODES Int
 	Declare @LAST_MODE Int
 	
 	Declare @MODE_CW Int
 	Declare @MODE_PH Int
 	
 	Set @ALL_BANDS = 0
 	Set @LAST_BAND = 6
 	Set @ALL_MODES = 0
 	Set @LAST_MODE = 2
 	
 	Set @MODE_CW = 1
 	Set @MODE_PH = 2
    --/
    --/
    
    If isnull(@Category, '') = ''
	    Select Top 1
	    	@Category = Category
	    From LogFilesCategory 
	    Where Call = @Callsign	    
	    Order By ID
	
	If isnull(@Res_Type, 0) In (1, 2)
		Select
			L.Call Callsign,
			C.Description Category,
			isnull(Nullif(Bands.Band_Long, ''), 'Total') Band,
			isnull(Nullif(Modes.Mode, ''), 'All') Mode,
			BR.QSOs,
			BR.Points,
			M1.Mult Mult1,
			M2.Mult Mult2,
			M3.Mult Mult3,
			Nullif(BR.Score, 0) Score
		From LogFiles L
		Join LogFilesCategory_Select LFC On LFC.Call = L.Call
		Join Category C On C.Category = LFC.Category
		Join LogFiles_BandResults BR
			On BR.Call = L.Call
			And BR.Type = @Res_Type
			And (BR.Mode_ID <> @ALL_MODES Or BR.Band_ID = @ALL_BANDS)
			And BR.Category = LFC.Category
		Join Bands On Bands.ID = BR.Band_ID
		Join Modes On Modes.ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M1
			On M1.ID_Parent = BR.ID
			And M1.Type = @MULT_DXCC
			And M1.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M2
			On M2.ID_Parent = BR.ID
			And M2.Type = @MULT_RDA
			And M2.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M3
			On M3.ID_Parent = BR.ID
			And M3.Type = @MULT_OBL
			And M3.Mode_ID = BR.Mode_ID
		Where L.Call = @Callsign
		And LFC.Category = @Category
		And (Bands.Enabled = 1 Or Bands.ID = @ALL_BANDS)
		And Modes.Enabled = 1
		And
		(
			(
				BR.Mode_ID In (C.Mode, @ALL_MODES)
				And Case When BR.Mode_ID = C.Mode Then BR.Band_ID Else -1 End <> @ALL_BANDS
			)
			Or C.Mode = @ALL_MODES
		)
		Order By
			Case When Bands.ID = 0 Then 1 Else 0 End
			, Bands.SortOrder
			, Case When Modes.ID = 0 Then 1 Else 0 End
			, Modes.ID
	Else
		Select
			L.Call Callsign,
			C.Description Category,
			isnull(Nullif(Bands.Band_Long, ''), 'Total') Band,
			isnull(Nullif(Modes.Mode, ''), 'All') Mode,
			BR_Claimed.QSOs,
			BR_Claimed.Points,
			MC1.Mult Mult1,
			MC2.Mult Mult2,
			MC3.Mult Mult3,
			Nullif(BR_Claimed.Score, 0) Score,		 
			BR.QSOs QSOs_CFM,
			BR.Points Points_CFM,
			M1.Mult Mult1_CFM,
			M2.Mult Mult2_CFM,
			M3.Mult Mult3_CFM,
			Nullif(BR.Score, 0) Score_CFM		 
		From LogFiles L
		Join LogFilesCategory_Select LFC On LFC.Call = L.Call
		Join Category C On C.Category = LFC.Category
		Join LogFiles_BandResults BR_Claimed
			On BR_Claimed.Call = L.Call
			And BR_Claimed.Type = @CLAIMED_RESULTS
			And (BR_Claimed.Mode_ID <> @ALL_MODES Or BR_Claimed.Band_ID = @ALL_BANDS)
			And BR_Claimed.Category = LFC.Category
		Join LogFiles_BandResults BR
			On BR.Call = L.Call
			And BR.Type = @CONFIRMED_RESULTS
			And BR.Band_ID = BR_Claimed.Band_ID
			And BR.Mode_ID = BR_Claimed.Mode_ID
			And BR.Category = LFC.Category
		Join Bands On Bands.ID = BR.Band_ID
		Join Modes On Modes.ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M1
			On M1.ID_Parent = BR.ID
			And M1.Type = @MULT_DXCC
			And M1.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M2
			On M2.ID_Parent = BR.ID
			And M2.Type = @MULT_RDA
			And M2.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults M3
			On M3.ID_Parent = BR.ID
			And M3.Type = @MULT_OBL
			And M3.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults MC1
			On MC1.ID_Parent = BR_Claimed.ID
			And MC1.Type = @MULT_DXCC
			And MC1.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults MC2
			On MC2.ID_Parent = BR_Claimed.ID
			And MC2.Type = @MULT_RDA
			And MC2.Mode_ID = BR.Mode_ID
		left Join LogFiles_BandResults_Mults MC3
			On MC3.ID_Parent = BR_Claimed.ID
			And MC3.Type = @MULT_OBL
			And MC3.Mode_ID = BR.Mode_ID
		Where L.Call = @Callsign
		And LFC.Category = @Category
		And (Bands.Enabled = 1 Or Bands.ID = @ALL_BANDS)
		And Modes.Enabled = 1
		And
		(
			(
				BR_Claimed.Mode_ID In (C.Mode, @ALL_MODES)
				And Case When BR_Claimed.Mode_ID = C.Mode Then BR_Claimed.Band_ID Else -1 End <> @ALL_BANDS
			)
			Or C.Mode = @ALL_MODES
		)
		Order By
			Case When Bands.ID = 0 Then 1 Else 0 End
			, Bands.SortOrder
			, Case When Modes.ID = 0 Then 1 Else 0 End
			, Modes.ID	
	Return
	
End