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

DBProject/UpdateScripts/Arj/Script003.sql
871 строка18 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
Alter Procedure dbo.ExportDataToMySQL_UA1DZ
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    03.02.2012 19:21:28
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

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

	Set Nocount On
	
	Declare @ExportData Bit
	Declare @ContestType Varchar(3)
	Declare @ContestName Varchar(255)
	
	Set @ExportData = cast(dbo.Settings_Get('ExportData') As Bit)
	Set @ContestType = cast(dbo.Settings_Get('Contest_type') As Varchar(3))	
	Set @ContestName = cast(dbo.Settings_Get('ContestName') As Varchar(255))	
	
	If @ExportData = 0
		Return
	
	Declare @Results_Table Varchar(255)
	Declare @DateStamp_Table Varchar(255)
	Declare @Pivot_Table Varchar(255)

	Set @Results_Table = cast(dbo.Settings_Get('Results_Table') As Varchar(255))
	Set @DateStamp_Table = cast(dbo.Settings_Get('DateStamp_Table') As Varchar(255))
	Set @Pivot_Table = cast(dbo.Settings_Get('Pivot_Table') As Varchar(255))
	
	Declare @SQL Nvarchar(max)
	
	Set @SQL =  'Delete From ' + @DateStamp_Table 	
	Execute (@SQL) at MySQL_UA1DZ
		
	If @ContestName = 'SPB-OPEN-VHF'	
	Or @ContestName = 'LO-OPEN-VHF'	
		Begin
			Declare @NeedLogsTable Table 
			(
				NeedLogs Varchar(max),
				NeedLogsText Varchar(max)
			) 
			
			Declare @NeedLogs Int
			Declare @NeedLogsText Varchar(max) 
			
			Insert @NeedLogsTable
			Exec dbo.УникалыПечать
			
			Select Top 1 
				@NeedLogs = NeedLogs,
				@NeedLogsText = NeedLogsText
			From @NeedLogsTable
			
			Set @SQL = '
			Insert MySQL_UA1DZ...' + @DateStamp_Table + '
			(
				[Date],
				Logs,
				NeedLogs,
				NeedLogsText
			)
			Select 
				convert(varchar(19), getdate(), 120),
				count(*),
				' + cast(@NeedLogs As Varchar(5)) + ',
				''' + @NeedLogsText + '''
			From LogFilesCategory_Select
			Where Category <> ''SWL'''
			
			Exec sp_executesql @SQL
			
		End
	Else
		Begin
			
			Set @SQL = '
			Insert MySQL_UA1DZ...' + @DateStamp_Table + '
			(
				[Date],
				Logs
			)
			Select 
				convert(varchar(19), getdate(), 120),
				count(*)
			From LogFilesCategory_Select
			Where Category <> ''SWL'''
			
			Exec sp_executesql @SQL
			
		End
			
	Set @SQL =  'Delete From ' + @Results_Table 
	
	Execute (@SQL) at MySQL_UA1DZ
	
	If @ContestType = 'VHF'
		If @ContestName = 'VHF-ACTIVITY'
			Begin
			
				Set @SQL =  'Delete From ' + @Pivot_Table
				
				Execute (@SQL) at MySQL_UA1DZ
				
				Set @SQL = '
				Insert MySQL_UA1DZ...' + @Pivot_Table + '
				(
					SuperCategory,
					Call,
					Band,
					Link,
					Place,
					M1,
					M2,
					M3,
					M4,
					M5,
					M6,
					M7,
					M8,
					M9,
					M10,
					M11,
					M12,
					M0
				)
				Select
					SuperCategory,
					Call,
					Band,
					Link,
					Place,
					Январь,
					Февраль,
					Март,
					Апрель,
					Май,
					Июнь,
					Июль,
					Август,
					Сентябрь,
					Октябрь,
					Ноябрь,
					Декабрь,
					Сумма
				From dbo.[АНАЛИЗ Результаты дней активности сводка]()
				/* Where SuperCategory_ID = 1 */
				Order By 
					SuperCategory_ID,
					Band_ID,
					Place'
					
				-- Print @SQL
				
				Exec sp_executesql @SQL	
				
				Set @SQL = '				Insert MySQL_UA1DZ...' 
				+ @Results_Table
				+ '				(
					Summary,
					Category1,
					Category2,
					Place,
					Callsign,
					QSOs,
					Points,
					Mult1,
					Score,
					QSOs_CFM,
					Points_CFM,
					Mult1_CFM,
					Score_CFM,
					WWLoc,
					Equipment,
					ODX,
					ODX_Band,
					ODX_Distance,
					ODX_WWLoc,
					ODX_Count,
					Comment,
					Band_ID,
					[Month]
				)
				Select
					Summary,
					SuperCategory,
					Category,
					Place,
					Call,
					QSOs_Claimed,
					Points_Claimed,
					Mult1_Claimed,
					Score_Claimed,
					QSOs,
					Points,
					Mult1,
					Score,
					Location,
					Equipment,
					ODX,
					Band,
					ODX_Distance,
					ODX_WWLoc,
					ODX_Count,
					Link,
					Band_ID,
					[Month]
	   			From dbo.[АНАЛИЗ Результаты дней активности]()
	   			/* Where SuperCategory_ID = 1 */
	   			Order By
	   				Summary Desc,
	   				SuperCategory_ID,
	   				[Month],
	   				Band_ID,
	   				Place
				'
	
			End
		Else
			Set @SQL = '
			Insert MySQL_UA1DZ...' + @Results_Table + '
			(
				Category1,
				Category2,
				Place,
				Callsign,
				QSOs,
				Score,
				QSOs_CFM,
				Score_CFM,
				Operators,
				WWLoc,
				Equipment,
				Rank,
				Birth_Year,
				ODX,
				ODX_Band,
				ODX_Distance,
				ODX_WWLoc,
				ODX_Count,
				EVSK,
				EVSK_Points	,
				Comment,
				BandQSOs,
				BandPoints,
				BandMult,
				BandQSOsClaimed,
				BandPointsClaimed,
				BandMultClaimed
			)
			Select 
				RES.SuperCategory,
				RES.Category,
				RES.Place,
				RES.Call,
				RES.QSOs_Claimed,
				RES.Score_Claimed,
				RES.QSOs,
				RES.Score,
				RES.FIO,
				RES.Location,
				replace(RES.SOAPBOX, Char(13), ''<br>''),
				RES.Rank0,
				RES.BirthYear,
				ODX.ODX,
				BS.Band,
				ODX.ODX_Distance,
				ODX.ODX_WWLoc,
				Case 
					When BandsCount.BandsCount > ODX.ODX_Count 
						Then BandsCount.BandsCount
					Else
						ODX.ODX_Count					
				End ODX_Count,
				Case
					When RES.Place = 1
					And left(RES.SuperCategory, 1) = ''3''
						Then EVSK.EVSK /*''МС''*/
					Else
						EVSK.EVSK
				End EVSK,
				EVSK.EVSK_Points,
				RES.Link,
				isnull(BS.ConfirmedQSOs, 0) BandQSOsCfm,
				isnull(BS.ConfirmedPoints, 0) BandPointsCfm,
				isnull(BS.ConfirmedMult, 0) BandMultCfm,
				isnull(BS.ClaimedQSOs, 0) BandQSOs,
				isnull(BS.ClaimedPoints, 0) BandPoints,
				isnull(BS.ClaimedMult, 0) BandMult
			From dbo.[АНАЛИЗ Результаты по подгруппам с разрядами]() RES
			left Join dbo.VHF_EVSK_Points() EVSK
				On EVSK.Call = RES.Call
			left Join LogFiles_BandSummary BS
				On RES.Call = BS.Call
			left Join Bands
				On Bands.Band = BS.Band
			left Join dbo.QSOs_ODX(0) ODX
				On BS.Call = ODX.Callsign				
				And BS.Band = ODX.Band
				And isnull(ODX.[Month], 0) = 0
			left Join 
			(
				Select 
					Call, 
					count(*) BandsCount 
				From LogFiles_BandSummary 
				Group By Call			
			) BandsCount
				On BandsCount.Call = RES.Call
			Order By 
				RES.SuperCategory,
				RES.Category,
				RES.Place,
				Bands.SortOrder
			'	
	Else If @ContestType = 'HF'
		Begin
			Set @SQL = '
			Insert MySQL_UA1DZ...' + @Results_Table + '
			(
				Category1,
				Category2,
				Place,
				Callsign,
				QSOs_Claimed,
				Points_Claimed,
				Mult1_Claimed,
				Mult2_Claimed,
				Mult3_Claimed,
				Score_Claimed,
				QSOs,
				Points,
				Mult1,
				Mult2,
				Mult3,
				Score,
				Proc_VIP,
				Rank,
				VIP,
				Comment,
				AudioExists,
				FIO,
				Rank0,
				BirthYear,
				MS,
				KMS,
				TOTAL,
				Category1_SortOrder,
				R1
			)
			Select 
				SuperCategory,
				Category,
				Place,
				Call,
				QSOs_Claimed,
				Points_Claimed,
				Mult1_Claimed,
				Mult2_Claimed,
				Mult3_Claimed,
				Score_Claimed,
				QSOs,
				Points,
				Mult1,
				Mult2,
				Mult3,
				Score,
				Proc_VIP,
				Rank,
				VIP,
				Link,
				AudioExists,
				FIO,
				Rank0,
				BirthYear,
				MS,
				KMS,
				TOTAL,
				SuperCategory_SortOrder,
				R1
			From dbo.[АНАЛИЗ Результаты по подгруппам с разрядами]()'
			
			If @ContestName = 'ALRS-UA1DZ-CUP'
				Begin
					Execute ('Delete From UA1DZ_NoLogs') at MySQL_UA1DZ
					
					Declare @NoLogsSQL Nvarchar(max)
					
					Set @NoLogsSQL = '
					Insert MySQL_UA1DZ...UA1DZ_NoLogs
					(
						Call,
						QSOs,
						Type
					)
					Select Call2, count(*), 1 From QSOs t Where isnull(t.OblId, '''') = '' And t.ErrType = ''nl'' Group By call2 Having count(*) >= 5
					Union All
					Select Call2, count(*), 2 From QSOs t Where isnull(t.OblId, '''') Not In (''SP'', ''LO'', '''')  And t.ErrType = ''nl'' Group By call2 Having count(*) >= 5
					Union All
					Select Call2, count(*), 3 From QSOs t Where isnull(t.OblId, '''') In (''SP'', ''LO'') And t.ErrType = ''nl'' Group By call2 Having count(*) >= 5'
					
					Exec sp_executesql @NoLogsSQL	
					
				End
				
			If @ContestName = 'TWOCAPITALS'	
				Exec TWOCAPITALS_Teams
			
		End
	Exec sp_executesql @SQL	
	
End


GO

Alter Procedure dbo.VHF_MakeEquipmentDescription
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate>27.06.2013 02:02:05<Copier>John<Version>1<CopyOrder>
-- <CheckOut>False<CheckOutDate>23.11.2016 13:42<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
	
	Declare @Equip Table
	(
		Call Nvarchar(20),
		Equip Nvarchar(1000)
	)
	
	Declare @EquipByBand Table
	(
		Call Nvarchar(20),
		Equip Nvarchar(1000)
	)
	
	Insert @Equip
	Select
		LFC.Call, LFC.Equipment
	From LogFilesCategory LFC
	Group By LFC.Call, LFC.Equipment
	Having count(*) > 1
			
	Insert @EquipByBand
	Select
		LFC0.Call,
	    stuff
	    (
			(
	        	Select
					Char(13) + Char(10) + LFC.Band + ': ' + ltrim(rtrim(isnull(LFC.Equipment, '')))
				From LogFilesCategory LFC
				Join Bands On Bands.Band = LFC.BAnd
				Where LFC.Call = LFC0.Call
				Order By Bands.SortOrder
				For Xml PATH(''), TYPE
			).value('.', 'varchar(max)'),
	        1,
	        2,
	        ''
	    ) Equip
	From LogFilesCategory As LFC0
	Where LFC0.Call Not In (Select Call From @Equip)
	Group By LFC0.Call
	
	Insert @EquipByBand
	Select
		*
	From @equip
	
	Update L
	Set SOAPBOX = left(E.Equip, 255)
	From LogFiles L
	
	Join @EquipByBand E
		On L.Call = E.Call
		
	Where left(E.Equip, 255) <> ''
				
	Return
	
End

GO

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>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
	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 @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
	
	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
				@Category = Res.Category,
				@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

GO

ALter Procedure [dbo].[УникалыПечать]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate><Copier><Version><CopyOrder>
-- <CheckOut>False<CheckOutDate>18.06.2017 15:04<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
	
	Declare @uniques Table 
	(
		Call Varchar(30), 
		QSOs Int, 
		Id Int identity(1, 1)
	)
	
	Insert @uniques
	Select
		QSOs.Call2 As Call,
		count(*) As QSOs
	From QSOs
	Where QSOs.ErrType = 'nl'
	Or (QSOs.ErrType = '+' And QSOs.ID2 Is Null)
	Group By
		QSOs.Call2
	Having count(*) >= 3
	Order By QSOs Desc
	
	Declare @count Int 
	
	Select 
		@count = count(*) 
	From @uniques
	
	Declare @columns Int = 3
	Declare @rows Int = @count / @columns
	
	If @count % @columns >= 1
		Set @rows += 1
	
	Declare @i Int = 0
	
	Declare @Result Varchar(max) = ''
	
	While @i < @rows
		Begin
			Set @i += 1
			
			Select 
				@Result = @Result + left(Call + '       ', 7) + right('    ' + cast(QSOs As Varchar(5)), 4) + '   '
			From @uniques
			Where (ID - @i) % @rows = 0 
			Order By ID
			
			Set @Result = left(@Result, datalength(@Result) - 3)
			
			If @i <> @rows
				Set @Result = @Result + Char(13) + Char(10)
			
		End
	
	Select 
		@count NeedLogs, 
		@Result NeedLogsText
	
	Return
	
End

GO

Alter Procedure [dbo].[MySQL_UA1DZ_Awards_Prizers_Insert_VHF]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>
@Test_ID Int

-- <Returns>

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

	Set Nocount On
	
/*	
Select * From MySQL_UA1DZ...Awards_Tests
Select * From MySQL_UA1DZ...Awards_Blocks

Insert MySQL_UA1DZ...Awards_Tests (Test_name, English_name, Test_date, Image_file, Orientation, Format)
Select 
	'Открытый Чемпионат Санкт-Петербурга по радиосвязи на УКВ',
	'Saint-Petersburg Open VHF Championship',
	'20180616',
	'image_files/vhf_summer_2018.png',
	'L',
	'A4'

Insert MySQL_UA1DZ...Awards_Blocks (Awards_Tests_ID, Block_name, Left_X, Top_Y, Font_size, Template)
Select 
	26, Block_name, Left_X, Top_Y, Font_size, Template
From MySQL_UA1DZ...Awards_Blocks
Where Awards_Tests_ID = 22	
*/

	Insert MySQL_UA1DZ...Awards_Prizers
	(
		Awards_Tests_ID,
		Class,
		Place,
		Callsign,
		Operator,
		Points
	)
	Select
		@Test_ID,
		t.Category Category,
		t.Place, 
		t.Call Callsign,
		isnull(ops.ИФП, LF.NAME) Operator,
		t.Points
	From [dbo].[АНАЛИЗ Результаты по подгруппам с разрядами]() t
	left Join Operators2 ops
		On ops.Радиостанция = t.Call
	left Join LogFiles LF 
		On LF.Call = t.Call
	Where SuperCategory_ID <= 2
	Order By 
		SuperCategory_ID,
		Category
	
	
	Return

End