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

DBProject/UpdateScripts/Arj/Script002.sql
467 строк9 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Altering [dbo].[ExportDataToMySQL_UA1DZ]'
GO

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,
				ODX.Band,
				ODX.ODX_Distance,
				ODX.ODX_WWLoc,
				isnull(ODX.ODX_Count, 1),
				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.QSOs_ODX(0) ODX
				On Res.Call = ODX.Callsign
			left Join Bands On Bands.Band = ODX.Band
			left Join dbo.VHF_EVSK_Points() EVSK
				On EVSK.Call = RES.Call
			left Join LogFiles_BandSummary BS
				On ODX.Band = BS.Band
				And ODX.Callsign = BS.Call
			Where isnull(ODX.[Month], 0) = 0
			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

PRINT N'Altering [dbo].[MySQL_UA1DZ_Awards_Prizers_Insert_VHF]'

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
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