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

DBProject/UpdateScripts/Arj/Script001.sql
3 942 строки111 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
/*
Run this script on:

        RK1AA_HOME\SQLEXPRESS.ALRS-UA1DZ-CUP-2017    -  This database will be modified

to synchronize it with:

        alrs.ua1dz.ru.ALRS-UA1DZ-CUP-2018

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.3.8 from Red Gate Software Ltd at 10.06.2018 12:30:58

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping foreign keys from [dbo].[LogFiles_BandResults_Mults]'
GO
ALTER TABLE [dbo].[LogFiles_BandResults_Mults] DROP CONSTRAINT [LogFiles_BandResults_Mult_FK02]
ALTER TABLE [dbo].[LogFiles_BandResults_Mults] DROP CONSTRAINT [LogFiles_BandResults_Mult_FK03]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping foreign keys from [dbo].[QSOs_Mults]'
GO
ALTER TABLE [dbo].[QSOs_Mults] DROP CONSTRAINT [FK_QSOs_Points_Mults_Mults_Type]
ALTER TABLE [dbo].[QSOs_Mults] DROP CONSTRAINT [FK_QSOs_Mults_Results_Type]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping foreign keys from [dbo].[QSOs_Points]'
GO
ALTER TABLE [dbo].[QSOs_Points] DROP CONSTRAINT [FK_QSOs_Points_Results_Type]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[CallCheck_Table]'
GO
ALTER TABLE [dbo].[CallCheck_Table] DROP CONSTRAINT [aaaaaCallCheck_Table_PK]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[emails]'
GO
ALTER TABLE [dbo].[emails] DROP CONSTRAINT [PK_emails]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [ID] from [dbo].[CallCheck_Table]'
GO
DROP INDEX [ID] ON [dbo].[CallCheck_Table]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[emails]'
GO
DROP TABLE [dbo].[emails]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[CallCheck_Table]'
GO
DROP TABLE [dbo].[CallCheck_Table]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[CalcOneResult]'
GO
ALTER Procedure dbo.CalcOneResult
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    28.01.2012 21:51:59
-- <UpdateDate>    
-- <Parameters>
@My_Callsign Varchar(20),
@My_Category Varchar(50),
@Res_Type Int = Null,		-- тип результатов: 1 - заявленные, 2 - подтвержденные
@Year Int = Null,			-- год положения, подсчет очков меняется
@ContestName Varchar(255) = Null,	-- соревнования
@Batch Bit = Null,			-- в пакете не очищаются таблицы результатов, так как это делается снаружи
@Debug Bit = 0				-- сообщения для контроля выполнения процедуры

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>True<Checker>Кузнецов Евгений<CopyDate>18.07.2013 23:43:36<Copier>John<Version>7<CopyOrder>
-- <CheckOut>False<CheckOutDate>05.04.2018 19:58<CheckOutAuthor>Кузнецов Евгений
--<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
    Select
    	@LAST_MULT = count(*)
    From Mults_Type
    --/
    
    --+ Диапазоны и виды работы
 	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 = 9
 	Set @ALL_MODES = 0
 	Set @LAST_MODE = 3
 	
 	Set @MODE_CW = 1
 	Set @MODE_PH = 2
    --/
    --/
     
	--+ Корреция параметров
	If isnull(@Year, 0) = 0
		Set @Year = cast(dbo.Settings_Get('Year') As Int)
		
	If isnull(@ContestName, '') = ''
		Set @ContestName = cast(dbo.Settings_Get('ContestName') As Varchar(255))
		
	Set @Batch = isnull(@Batch, 0)
	Set @Debug = isnull(@Debug, 0)
	If isnull(@Res_Type, 0) = 0
		Set @Res_Type = @CLAIMED_RESULTS
		
	If isnull(@My_Category, '') = ''
		Select Top 1
			@My_Category =  Category
		From LogFilesCategory
		Where Call = @My_Callsign
		
	Declare @My_Mode Int
	Select
		@My_Mode = Mode
	From Category
	Where Category = @My_Category
	--/
	
	--+ Переменные	
	Declare @My_OblID Varchar(2)
	Declare @My_CountryID Varchar(10)
    Declare @My_Loc Varchar(15)    
    Declare @Callsign Varchar(20)
    Declare @CountryID Varchar(10)
    Declare @OblID Varchar(2)
    Declare @Loc Varchar(15)    
    Declare @RDA Varchar(5)
    
    Declare @i Int
    Declare @j Int
    Declare @k Int
    
    Declare @Bands_count Int    
    Declare @Modes_count Int    
    Declare @QSOs_count Int    
    
    Declare @IsDupe Bit
    
    Declare @Mult_ID Int        
    Declare @Band_ID Int
    Declare @Mode_ID Int    
    Declare @Modes_Min_ID Int
    Declare @QSO_ID Int
    Declare @Dupe_ID Int
    Declare @QSOs_Min_ID Int
    
    Declare @i_Band Int
    Declare @i_Mode Int
    Declare @i_QSO Int
    
    Declare @ID Int    
    
    Declare @QSOnumber Int
    Declare @Points Int
    Declare @Score Int 
    Declare @MultFound Bit       
    Declare @Time Varchar(4)
    Declare @DupeTime Varchar(4)
    
    Declare @Time_Begin Varchar(4)
    Declare @Time_End Varchar(4)
    Declare @Time2_Begin Varchar(4)
    Declare @Time2_End Varchar(4)
    
	Set @Time_Begin = cast(dbo.Settings_Get('Time_Begin') As Varchar(4))
	Set @Time_End = cast(dbo.Settings_Get('Time_End') As Varchar(4))
	Set @Time2_Begin = cast(dbo.Settings_Get('Time2_Begin') As Varchar(4))
	Set @Time2_End = cast(dbo.Settings_Get('Time2_End') As Varchar(4))
	
	Declare @Month Int
	
	Select
		@Month = isnull([Month], 0)
	From Category 
	Where Category.Category = @My_Category
	
	--/
	
    --+ Временные таблицы        
	Declare @QSOs_Mults  Table
	(
		ID Int identity (1, 1) Primary Key,
		ID_QSO Int,
	    MultType Int,
		ResType Int,
   		Mult Varchar(10)
	)
   
	Declare @LogFiles_BandResults Table
	(
		ID Int Not Null identity (1, 1) Primary Key Clustered,
		Band_ID Int Not Null,
		Mode_ID Int Null,
		QSOs Int Null,
		Points Int Null,
		Score Int Null
	)
   
	Declare @LogFiles_BandResults_Mults Table
	(
	   ID Int Not Null identity (1, 1) Primary Key Clustered,
	   ID_Parent Int Not Null,
	   Mode_ID Int Not Null,
	   Type Int Not Null,
	   Mult Int Not Null
	)
	
    Declare @DupeArr Table
    (
    	Callsign Varchar(20) Primary Key Clustered,
    	[Time] Varchar(4),
    	QSO_ID Int
    )
    
    Declare @MultArr Table
    (
    	Mult_ID Int,
    	Mode_ID Int,
    	Value Varchar(10)
    )
    
    Declare @IsMult Table
    (
    	Mult_ID Int,
    	Mode_ID Int,
    	Value Bit
    )
    
    Declare @Bands Table
    (
    	Band_ID Int,
    	ID Int identity(1, 1) Primary Key 
    )
    
    Declare @Modes Table
    (
    	Mode_ID Int,
    	ID Int identity(1, 1) Primary Key 
    )        
    
    Declare @QSOs Table
    (
    	QSO_ID Int,
    	ID Int identity(1, 1) Primary Key
    )
    --/
    
    --+ Определяем страну и область
    If @Batch = 0
    	Begin
        	Exec ResolvePrefix
                @Callsign = @My_Callsign, 
                @Country = @My_CountryID Output
                
            If left(@My_CountryID, 2) = 'UA'
           		Set @My_OblID = dbo.ResolveOblast(@My_Callsign)
           	Else
           		Set @My_OblID = Null
           		
		    Update L
		    Set L.OblID = @My_OblID,
		    	L.CountryID = @My_CountryID
		    From LogFiles L
		    Where L.Call = @My_Callsign
    	End    	
    Else
	    Select
	    	@My_OblID = L.OblID,
	    	@My_CountryID = L.CountryID
	    From LogFiles L
	    Where L.Call = @My_Callsign
	--/
	    	
	If @Batch = 0
		Begin			
			--+ Удаляем старые результаты, если есть
			Delete LogFiles_BandResults_Mults
			From LogFiles_BandResults_Mults
			Join LogFiles_BandResults On LogFiles_BandResults.ID = LogFiles_BandResults_Mults.ID_Parent 
			Where LogFiles_BandResults.Call = @My_Callsign			
			And LogFiles_BandResults.Type = @Res_Type
			And LogFiles_BandResults.Category = @My_Category
			
			Delete LogFiles_BandResults
			Where Call = @My_Callsign
			And Type = @Res_Type
			And Category = @My_Category
							
			Delete QSOs_Points
			From QSOs_Points
			Join QSOs On QSOs.ID = QSOs_Points.ID_QSO
			Where QSOs.Call1 = @My_Callsign
			And QSOs_Points.ResType = @Res_Type				
			And (month(QSOs.Date) = @Month Or @Month = 0)
			
			Delete QSOs_Mults
			From QSOs_Mults
			Join QSOs On QSOs.ID = QSOs_Mults.ID_QSO
			Where QSOs.Call1 = @My_Callsign
			And QSOs_Mults.ResType = @Res_Type				
			And QSOs_Mults.Category = @My_Category
			And (month(QSOs.Date) = @Month Or @Month = 0)
			--/	
		End
		
	--+ Инициализация таблиц результатов	
	Insert @LogFiles_BandResults
	(
		Band_ID,
		Mode_ID,
		QSOs,
		Points,
		Score            		
	)
	Select
		Bands.ID,
		Modes.ID,
		0,
		0,
		0
	From
		Bands,
		Modes				
    --/		        
		
    --+ Инициализация множителей
	Insert @LogFiles_BandResults_Mults
    (
    	ID_Parent,
    	Type,
    	Mode_ID,								    	
    	Mult
    )
	Select
		BR.ID,
		Mults_Type.ID,
		Modes.ID,
		0
	From
		@LogFiles_BandResults BR,
		Modes,
		Mults_Type
    --/
		
    --+ Цикл по диапазонам
    Insert @Bands
	Select
		Bands.ID As Band_ID
	From QSOs
	Join Bands On Bands.Band = QSOs.Band
	Join CategoryBands CB On CB.Band = Bands.ID
	Join Category On Category.ID = CB.Category
	Where QSOs.Call1 = @My_Callsign
	And (month(QSOs.Date) = @Month Or @Month = 0)
	And Category.Category = @My_Category
	And Bands.Enabled = 1
	Group By Bands.ID
	Order By Bands.ID
	
	Select
		@Bands_count = count(*)
	From @Bands
	
	Set @i_Band = 1
  
    While @i_Band <= @Bands_Count
    
    	Begin
                       
    		If @Debug = 1
    			Print 'Band: ' + cast(@i_Band As Varchar)
    			
            Select 
            	@Band_ID = Band_ID
            From @Bands
            Where ID = @i_Band
            
		    If @ContestName = 'SPB-OPEN-VHF'
				Delete @DupeArr

            --+ Сброс массивов диапазонных множителей
        	Delete @MultArr
        	Where Mult_ID In (@MULT_DXCC, @MULT_OBL)
        	And Mode_ID = @ALL_MODES
            --/
            
		    --+ Цикл по видам работы
            Delete @Modes
            
            Insert @Modes
			Select
				Modes.ID As Mode_ID
			From QSOs
			Inner Join Modes On Modes.Mode = QSOs.Mode
			Where QSOs.Call1 = @My_Callsign
			And QSOs.Call2 <> 'ERROR'
			And (month(QSOs.Date) = @Month Or @Month = 0)
			And Modes.Enabled = 1
			Group By Modes.ID
			Order By Modes.ID
						
			Select 
				@Modes_count = count(*),
				@Modes_Min_ID = min(ID)
			From @Modes
						
			Set @i_Mode = @Modes_Min_ID
                        
            While @i_Mode <= @Modes_Min_ID + @Modes_count - 1
            
                Begin
                                
                    Select
                    	@Mode_ID = Mode_ID
                    From @Modes
                    Where ID = @i_Mode
                    
		            --+ Сброс массивов диапазонных множителей
		        	Delete @MultArr
		        	Where Mult_ID In (@MULT_DXCC, @MULT_OBL)
		        	And Mode_ID = @Mode_ID
		            --/
            
					If @Debug = 1
                		Print '==== Обрабатываем диапазон ' + cast(@Band_ID As Varchar) + ' и вид работы ' + cast(@Mode_ID As Varchar)
                	
		            If @ContestName <> 'SPB-OPEN-VHF'
						Delete @DupeArr

                    Delete @QSOs
                    
                    --+ Цикл по всем связям на текущем диапазоне текущим видом работы
                    Insert @QSOs
					Select
						QSOs.ID
					From QSOs As QSOs
					Join Modes On Modes.Mode = QSOs.Mode					
					Join Bands On Bands.Band = QSOs.Band
					Where QSOs.Call1 = @My_Callsign
					And (month(QSOs.Date) = @Month Or @Month = 0)
					And Bands.ID = @Band_ID
					And (Modes.ID = @Mode_ID Or @Mode_ID = 0)
					And (Modes.ID = @My_Mode Or @My_Mode = 0)
					And (QSOs.ErrType = '+' Or @Res_Type = @CLAIMED_RESULTS)
					And 
					(
						(QSOs.Time Between @Time_Begin And @Time_End And @My_Category = 'SINGLE-OP 160M-80M-40M')
						Or (QSOs.Time Between @Time2_Begin And @Time2_End And @My_Category = 'SINGLE-OP 20M-15M-10M')
						Or @My_Category Not In ('SINGLE-OP 20M-15M-10M', 'SINGLE-OP 160M-80M-40M')
					)
                    Order By QSOs.ID 
                    
                    Select
                    	@QSOs_count = count(*),
                    	@QSOs_Min_ID = min(ID)
                    From @QSOs
                    
                	Set @QSOs_count = isnull(@QSOs_count, 0)
                	Set @QSOs_Min_ID = isnull(@QSOs_Min_ID, 1)                    
                    
					If @Debug = 1
	                    Print 'Всего записей: ' + cast(@QSOs_count As Varchar)+ ', начиная с ' + cast(@QSOs_Min_ID As Varchar)
                    
                    Set @i_QSO = @QSOs_Min_ID
                    
                    While @i_QSO <= @QSOs_count + @QSOs_Min_ID - 1
                    
                        Begin
                        
                            Set @Points = 0
                                                                                    
                            Select 
                            	@CountryID = isnull(QSOs.CountryID, ''),
                            	@OblID = isnull(QSOs.OblID, ''),
                            	@Callsign = isnull(QSOs.Call2, ''),
                            	@Loc = isnull(QSOs.NrRcvd, ''),
                            	@My_Loc = isnull(QSOs.NrSent, ''),
                            	@QSO_ID = QSOs.ID,
                            	@Points = isnull(PTS.Points, 0),
                            	@Time = isnull(QSOs.[Time], '')
                            From @QSOs QSOs_ID
                            Join QSOs QSOs On QSOs.ID = QSOs_ID.QSO_ID
                            left Join QSOs_Points PTS
                            	On PTS.ID_QSO = QSOs.ID
                            	And PTS.ResType = @Res_Type
                            Where QSOs_ID.ID = @i_QSO
                                                        
                            If @Batch = 0
                            	Begin
					            	Exec ResolvePrefix
					                    @Callsign = @Callsign, 
					                    @Country = @CountryID Output
					                    
				                    If left(@CountryID, 2) = 'UA'
				                   		Set @OblID = Nullif(dbo.ResolveOblast(@Callsign), '')
				                   	Else
				                   		Set @OblID = Null
				                   						                   		
					            End
					            
                            --+ Проверка дуплей                                                
                            Set @IsDupe = 0
            	
	                         If @ContestName = 'RF-CHAMP-SSB'
	                         	Begin
		                            Set @DupeTime = Null
		                            
		                            Select 
		                            	@DupeTime = [Time]
		                        	From @DupeArr
		                        	Where Callsign = @Callsign
		                        	
		                        	If @DupeTime Is Null
		                        		Set @IsDupe = 0
		                        	Else
			                       		If abs(dbo.GetTimeDifference(@DupeTime, @Time)) > 240
			                       			Set @IsDupe = 0
			                       		Else
			                       			Set @IsDupe = 1

	                         	End
                        	Else If @ContestName = 'LO-OPEN-VHF'
								Begin
									-- туры по 15 минут
									
									-- определяем начало и конец тура, в котором проведена связь
									Declare @Time_min Int = cast(right(@Time, 2) As Int)
									Declare @Hour_quarter Int = @Time_min / 15
									
									Declare @Tour_begin Char(4) = left(@Time, 2) + right('0' + cast(@Hour_quarter * 15 As Char(2)), 2)
									Declare @Tour_end Char(4) = left(@Time, 2) + right('0' + cast((@Hour_quarter + 1) * 15 - 1 As Char(2)), 2)
									
		                            Set @DupeTime = Null
		                            Set @Dupe_ID = Null
		                            
		                            Select 
		                            	@DupeTime = [Time],
		                            	@Dupe_ID = QSO_ID
		                        	From @DupeArr
		                        	Where Callsign = @Callsign
		                        	And @DupeTime >= @Tour_begin
		                        	And @DupeTime <= @Tour_end
		                        	
		                        	If @DupeTime Is Null
		                        		Set @IsDupe = 0
		                        	Else
		                        		Begin
		                        			Set @IsDupe = 1
				                       		If dbo.GetTimeDifference(@DupeTime, @Time) > 0
			                       				Set @Dupe_ID = @QSO_ID				                       						                       	
			                       		End
								End
                        	Else
								Begin
								
		                            Set @DupeTime = Null
		                            Set @Dupe_ID = Null
		                            
		                            Select 
		                            	@DupeTime = [Time],
		                            	@Dupe_ID = QSO_ID
		                        	From @DupeArr
		                        	Where Callsign = @Callsign
		                        	
		                        	If @DupeTime Is Null
		                        		Set @IsDupe = 0
		                        	Else
		                        		Begin
		                        			Set @IsDupe = 1
				                       		If dbo.GetTimeDifference(@DupeTime, @Time) > 0
			                       				Set @Dupe_ID = @QSO_ID				                       						                       	
			                       		End
								End
									                            	
							If @Debug = 1
        	                    Print 'Обрабатываем запись №' + cast(@i_QSO As Varchar) + ': ' + isnull(@Callsign, '')
                            
                            If @IsDupe = 0
                            	Begin
                            		Delete @DupeArr
                            		Where Callsign = @Callsign
                            		
	                            	Insert @DupeArr
	                            	Select
	                            		@Callsign,
	                            		@Time,
	                            		@QSO_ID	                            
	                            End
                                                            
                            If @IsDupe = 1
                            And @Res_Type = @CONFIRMED_RESULTS
                            	Update QSOs
                            	Set ErrType = 'D'
                            	Where ID = @Dupe_ID
                            --/
                                                
                            If @IsDupe = 1
                            	Begin
	                            	If @Batch = 1                            		
	                            		Begin
	                            			-- Если дупль, то обнуляем рассчитанные заранее скопом очки
				                        	Update QSOs_Points
				                        	Set Points = 0
				                        	Where @Dupe_ID = ID_QSO
				                        	And @Res_Type = ResType                            		
			                        	End
								End
                            Else                            
	                            Begin
	                            
	                            	If @Batch = 0
	                            		Begin
											Set @Points = dbo.CalcPoints 
												( 
													  @Callsign 
													, @CountryID
													, @OblID 
													, @Loc 
													, @My_Callsign 
													, @My_CountryID 
													, @My_OblID 
													, @My_Loc 
													, @Year
													, @ContestName
													, @Band_ID
												)
										End
	                                
	                                --+ Инициализация буфера множителей конкретной связи	                                
	                                Delete @IsMult
	                                --/
	                                
	                                If (@My_OblID = 'SP'
	                                Or @My_OblID = 'LO')
	                                And @ContestName = 'ALRS-UA1DZ-CUP'
		                                Begin
		                                	--+ Проверка множителя для участников из СПб и области
		                                	Set @j = @ALL_MODES
		                                	
		                                    While @j <= @LAST_MODE
		                                    
			                                    Begin
			                                    
			                                        If @CountryID <> ''
				                                        Begin
				                                        
				                                        	If Exists
				                                        	(
				                                        		Select
				                                        			Value
				                                        		From @MultArr
				                                        		Where Value = @CountryID
				                                        		And Mult_ID = @MULT_DXCC
				                                        		And Mode_ID = @j
				                                        	)
				                                        		Set @MultFound = 0
				                                        	Else
				                                        		Set @MultFound = 1
				                                        			                               
				                                            If @MultFound = 1
																Insert @MultArr
																Select
																	@MULT_DXCC,
																	@j,
																	@CountryID
														End
			                                        Else
			                                        	Set @MultFound = 0
			                                        
													Insert @IsMult
													Select
														@MULT_DXCC,
														@j,
														@MultFound
														
			                                        If @OblID <> ''
			                                        	Begin
			                                        	
			                                        		If Exists
			                                        		(
				                                        		Select
				                                        			Value
				                                        		From @MultArr
				                                        		Where Value = @OblID
				                                        		And Mult_ID = @MULT_OBL
				                                        		And Mode_ID = @j				                                        		
			                                        		)
			                                        			Set @MultFound = 0
			                                        		Else
			                                        			Set @MultFound = 1			                                        			
				                                            
				                                            If @MultFound = 1
																Insert @MultArr
																Select
																	@MULT_OBL,
																	@j,
																	@OblID
				                                        End
			                                        Else
			                                        	Set @MultFound = 0
			                                    
													Insert @IsMult
													Select
														@MULT_Obl,
														@j,
														@MultFound
													Union All	
													Select
														@MULT_RDA,
														@j,
														0
																	                                        
			                                        Set @j = @j + 1
			                                        
			                                    End
		                                    --/
		                                End                                    
	                                Else                                
		                                Begin
		                                	--+ Проверка множителя для остальных участников
		                                	Set @j = @ALL_MODES
		                                	
		                                    While @j <= @LAST_MODE
		                                    
			                                    Begin
			                                    	
													If @ContestName = 'ALRS-UA1DZ-CUP'
														Begin
					                                    	--+ Кубок UA1DZ
						                                    If @OblID = 'LO' 
						                                    Or @OblID = 'SP'
							                                    Begin
							                                        Set @RDA = @Loc
							                                        
							                                        If left(@RDA, 2) = 'LO' 
							                                        Or left(@RDA, 2) = 'SP' 
								                                        Begin
								                                        				                                            
								                                            If Exists
								                                            (
								                                            	Select
								                                            		value
								                                            	From @MultArr
								                                            	Where value = @RDA
								                                            	And Mult_ID = @MULT_RDA
								                                            	And Mode_ID = @j
								                                            )
								                                            	Set @MultFound = 0
								                                            Else
								                                            	Set @MultFound = 1
								                                            					                                            
								                                            If @MultFound = 1
								                                            	Insert @MultArr
								                                            	Select
								                                                	@MULT_RDA, 
								                                                	@j,
								                                                	@RDA
								                                            
								                                        End
																	Else     
																		Set @MultFound = 0                                   
																		
							                                    End                                    
						                                    Else                                    
							                                    Set @MultFound = 0

				                                        	Insert @IsMult
				                                        	Select
				                                        		@MULT_RDA,
				                                        		@j,
				                                        		@MultFound
				                                        	Union All
				                                        	Select
				                                        		@MULT_DXCC,
				                                        		@j,
				                                        		0
				                                        	Union All
				                                        	Select
				                                        		@MULT_OBL,
				                                        		@j,
				                                        		0
					                                        --/					                                       
				                                    	End
				                                    Else If @ContestName = 'RF-CHAMP-SSB'
				                                    	Begin
				                                    		--+ Чемпионат РФ
				                                    		
				                                    		--+ Область - сквозной множитель, как RDA в кубке UA1DZ
				                                    		If isnull(@OblId, '') <> ''
				                                    			Begin
							                                        Set @RDA = @OblID
							                                        
						                                            If Exists
						                                            (
						                                            	Select
						                                            		value
						                                            	From @MultArr
						                                            	Where value = @RDA
						                                            	And Mult_ID = @MULT_RDA
						                                            	And Mode_ID = @j
						                                            )
						                                            	Set @MultFound = 0
						                                            Else
						                                            	Set @MultFound = 1
						                                            					                                            
						                                            If @MultFound = 1
						                                            	Insert @MultArr
						                                            	Select
						                                                	@MULT_RDA, 
						                                                	@j,
						                                                	@RDA
						                                                	
						                                        	Insert @IsMult
						                                        	Select
						                                        		@MULT_RDA,
						                                        		@j,
						                                        		@MultFound
																End				                                        		
				                                    		--/
				                                    		
				                                    		--+ Зона - диапазонный множитель, как страна или область для питерцев в кубке DZ
				                                    		If len(@Loc) = 4
				                                    			Begin
				                                    				Set @CountryID = cast(left(@Loc, 1) As Int)
				                                    								                                    		
							                                        If @CountryID Between 1 And 7
								                                        Begin
								                                        
								                                        	If Exists
								                                        	(
								                                        		Select
								                                        			Value
								                                        		From @MultArr
								                                        		Where Value = @CountryID
								                                        		And Mult_ID = @MULT_DXCC
								                                        		And Mode_ID = @j
								                                        	)
								                                        		Set @MultFound = 0
								                                        	Else
								                                        		Set @MultFound = 1
								                                        			                               
								                                            If @MultFound = 1
																				Insert @MultArr
																				Select
																					@MULT_DXCC,
																					@j,
																					@CountryID
																		End
							                                        Else
							                                        	Set @MultFound = 0
							                                        
																	Insert @IsMult
																	Select
																		@MULT_DXCC,
																		@j,
																		@MultFound				                                    		
																End
				                                    		--/
				                                    		
				                                        	Insert @IsMult
				                                        	Select
				                                        		@MULT_OBL,
				                                        		@j,
				                                        		0
				                                    		--/
				                                    	End
				                                    			                                        		
		                                        	Set @j = @j + 1
												End
		                                    --/
		                                End		                                
	                               
	                                If @ContestName = 'VHF-ACTIVITY'
	                                Or @ContestName = 'SPB-OPEN-VHF'
		                                Begin
		                                
		                                	--+ Проверка новых больших квадратов
		                                	Set @j = @ALL_MODES
		                                	
		                                    While @j <= @LAST_MODE
		                                    
			                                    Begin
			                                    	Declare @BigLoc As Varchar(4)
			                                    	
			                                    	If @ContestName = 'VHF-ACTIVITY'
			                                    	And len(@Loc) In (9, 10)
			                                    		Set @BigLoc = left(right(@Loc, 6), 4)
			                                    	Else If @ContestName = 'SPB-OPEN-VHF'
			                                    	And len(@Loc) >= 8
			                                    		Set @BigLoc = left(right(@Loc, 6), 4)
			                                    	Else
			                                    		Set @BigLoc = ''
			                                    
			                                        If @BigLoc <> ''
				                                        Begin
				                                        
				                                        	If Exists
				                                        	(
				                                        		Select
				                                        			Value
				                                        		From @MultArr
				                                        		Where Value = @BigLoc
				                                        		And Mult_ID = @MULT_DXCC
				                                        		And Mode_ID = @j
				                                        	)
				                                        		Set @MultFound = 0
				                                        	Else
				                                        		Set @MultFound = 1
				                                        			                               
				                                            If @MultFound = 1
																Insert @MultArr
																Select
																	@MULT_DXCC,
																	@j,
																	@BigLoc
														End
			                                        Else
			                                        	Set @MultFound = 0
			                                        
													Insert @IsMult
													Select
														@MULT_DXCC,
														@j,
														@MultFound
																															                                        
			                                        Set @j = @j + 1
			                                        
			                                    End
		                                    --/
		                                End                                    
	                               
	                                --+ Проставляем мульт
	                                Set @Mult_ID = @FIRST_MULT
	                                
	                                While @Mult_ID <= @LAST_MULT
		                                Begin
		                                        
		                                    Set @j = @ALL_MODES
		                                    
		                                    While @j <= @LAST_MODE
			                                    Begin
			                                    
			                                        If
			                                        (
			                                        	Select
			                                        		value
			                                        	From @IsMult
			                                        	Where Mult_ID = @Mult_ID
			                                        	And Mode_ID = @j
			                                        ) = 1
				                                        Begin
				                                        
				                                            If @j = @ALL_MODES
			                                                	Insert @QSOs_Mults
			                                                	(
			                                                		ID_QSO,
			                                                		ResType,
			                                                		MultType,
			                                                		Mult
			                                                	)
			                                                	Select 
			                                                		@QSO_ID,
			                                                		@Res_Type,
			                                                		@Mult_ID,
			                                                		Case @Mult_ID 
			                                                			When @MULT_DXCC
			                                                				Then 
			                                                					Case @ContestName
			                                                						When 'VHF-ACTIVITY'
			                                                							Then @BigLoc
			                                                						When 'SPB-OPEN-VHF'
			                                                							Then @BigLoc
			                                                						Else
			                                                							@CountryID
			                                                					End
			                                                			When @MULT_OBL
			                                                				Then @OblID
			                                                			When @MULT_RDA
			                                                				Then @RDA
			                                                		End
					                                            
					                                        Update Mult
					                                        Set Mult = Mult + 1
					                                        From @LogFiles_BandResults_Mults Mult
					                                        Join @LogFiles_BandResults Res On Res.ID = Mult.ID_Parent 
					                                        Where Mult.Type = @Mult_ID
					                                        And Mult.Mode_ID = @j
					                                        And Res.Band_ID In (@Band_ID, @ALL_BANDS)
					                                        And Res.Mode_ID In (@Mode_ID, @ALL_MODES)
					                                        
				                                        End
				                                        
													Set @j = @j + 1
			                                        
			                                    End
			                                    
			                            	Set @Mult_ID = @Mult_ID + 1
			                                    
		                                End
	                                
	                            End
                            --/
                            
                            --+ Проставляем очки
							If @Batch = 0
	                        	Insert QSOs_Points
	                        	(
	                        		ID_QSO,
	                        		ResType,
	                        		Points
	                        	)
	                        	Select 
	                        		@QSO_ID,
	                        		@Res_Type,
	                        		@Points
	                        --/
                            
                            Set @i_QSO = @i_QSO + 1
                            
                        End
                    --/
                    
                    Set @i_Mode = @i_Mode + 1
                    
                End
            --/
                            
            Set @i_Band = @i_Band + 1
                        
    	End
    --/
    
    --+ Подсчет количества QSO по диапазонам и видам работы    
    Set @Band_ID = @ALL_BANDS
    
    While @Band_ID <= @LAST_BAND
	    Begin
	    
		    Set @Mode_ID = @ALL_MODES
		    
	    	While @Mode_ID <= @LAST_MODE
	    		Begin	    
	    			If @Res_Type = @CLAIMED_RESULTS
			            Select
			                  @QSONumber = count(QSOs.ID),
			                  @Points = isnull(sum(isnull(PTS.Points, 0)), 0)
			            From QSOs As QSOs
			            Join QSOs_Points As PTS On PTS.ID_QSO = QSOs.ID
			            Join Bands On Bands.[Band] = QSOs.Band
			            Join Modes On Modes.Mode = QSOs.Mode
			            Join Category On Category.Category = @My_Category
			            Join CategoryBands CB On CB.Category = Category.ID
			            					  And CB.Band = Bands.ID			            
			            Where QSOs.Call1 = @My_Callsign
						And (month(QSOs.Date) = @Month Or @Month = 0)
						And 
						(
							(QSOs.Time Between @Time_Begin And @Time_End And @My_Category = 'SINGLE-OP 160M-80M-40M')
							Or (QSOs.Time Between @Time2_Begin And @Time2_End And @My_Category = 'SINGLE-OP 20M-15M-10M')
							Or @My_Category Not In ('SINGLE-OP 20M-15M-10M', 'SINGLE-OP 160M-80M-40M')
						)
			            And @Band_ID In (Bands.ID, @ALL_BANDS)
			            And @Mode_ID In (Modes.ID, @ALL_MODES)
						And (Modes.ID = @My_Mode Or @My_Mode = 0)
			            And PTS.ResType = @Res_Type
			        Else
			            Select
			                  @QSONumber = count(QSOs.ID),
			                  @Points = isnull(sum(isnull(PTS.Points, 0)), 0)
			            From QSOs As QSOs
			            Join QSOs_Points As PTS On PTS.ID_QSO = QSOs.ID
			            Join Bands On Bands.[Band] = QSOs.Band
			            Join Modes On Modes.Mode = QSOs.Mode
			            Join Category On Category.Category = @My_Category
			            Join CategoryBands CB On CB.Category = Category.ID
			            					  And CB.Band = Bands.ID			            
			            Where QSOs.Call1 = @My_Callsign
						And (month(QSOs.Date) = @Month Or @Month = 0)
						And 
						(
							(QSOs.Time Between @Time_Begin And @Time_End And @My_Category = 'SINGLE-OP 160M-80M-40M')
							Or (QSOs.Time Between @Time2_Begin And @Time2_End And @My_Category = 'SINGLE-OP 20M-15M-10M')
							Or @My_Category Not In ('SINGLE-OP 20M-15M-10M', 'SINGLE-OP 160M-80M-40M')
						)
			            And @Band_ID In (Bands.ID, @ALL_BANDS)
			            And @Mode_ID In (Modes.ID, @ALL_MODES)
						And (Modes.ID = @My_Mode Or @My_Mode = 0)
			            And QSOs.ErrType = '+'
			            And PTS.ResType = @Res_Type
		                                    		                                    
		            Update @LogFiles_BandResults
		            Set QSOs = @QSONumber,
		            	Points = @Points
		            Where Band_ID = @Band_ID
		            And Mode_ID = @Mode_ID
		            
		            Set @Mode_ID = @Mode_ID + 1	            
	            End
			Set @Band_ID = @Band_ID + 1	            
	 	End
    --/
       
    --+ Подсчет числа очков 
    Set @Mode_ID = @ALL_MODES
    
    While @Mode_ID <= @LAST_MODE
	    Begin
			If @ContestName = 'ALRS-UA1DZ-CUP'
				Begin
			        If @My_OblID = 'SP'
			        Or @My_OblID = 'LO'
			        	Select
			        		@Score = sum(Mult.Mult) * Res.Points
				        From @LogFiles_BandResults Res
				        Join @LogFiles_BandResults_Mults Mult On MULT.ID_Parent = Res.ID
				        Where Res.Band_ID = @ALL_BANDS
				        And Res.Mode_ID = @Mode_ID
				        And Mult.Type In (@MULT_DXCC, @MULT_OBL)
				        And Mult.Mode_ID = @Mode_ID
				        Group By
				        	Res.ID,
				        	Res.Points		        
				    Else
			        	Select
			        		@Score = Case When @Year >= 2012 Then sum(Mult.Mult) * 300 + Res.Points Else sum(Mult.Mult) * Res.Points End
				        From @LogFiles_BandResults Res
				        Join @LogFiles_BandResults_Mults Mult On MULT.ID_Parent = Res.ID
				        Where Res.Band_ID = @ALL_BANDS
				        And Res.Mode_ID = @Mode_ID
				        And Mult.Type = @MULT_RDA
				        And Mult.Mode_ID = @Mode_ID
				        Group By
				        	Res.ID,
				        	Res.Points		        
				End
			Else If @ContestName = 'RF-CHAMP-SSB'		    	
				Begin
		        	Select
		        		@Score = sum(Mult.Mult) * 50 + Res.Points
			        From @LogFiles_BandResults Res
			        Join @LogFiles_BandResults_Mults Mult On MULT.ID_Parent = Res.ID
			        Where Res.Band_ID = @ALL_BANDS
			        And Res.Mode_ID = @Mode_ID
				        And Mult.Type In (@MULT_DXCC, @MULT_RDA)
			        And Mult.Mode_ID = @Mode_ID
			        Group By
			        	Res.ID,
			        	Res.Points		        
				End
				
			Else If @ContestName = 'TWOCAPITALS'		    	
				Begin
		        	Select
		        		@Score = Res.Points
			        From @LogFiles_BandResults Res
			        Where Res.Band_ID = @ALL_BANDS
			        And Res.Mode_ID = @Mode_ID
			        Group By
			        	Res.ID,
			        	Res.Points		        				        	
				End
				
			Else If @ContestName = 'LO-OPEN-VHF'
				Begin
		        	Select
		        		@Score = Res.Points
			        From @LogFiles_BandResults Res
			        Where Res.Band_ID = @ALL_BANDS
			        And Res.Mode_ID = @Mode_ID
			        Group By
			        	Res.ID,
			        	Res.Points		        
				End
			Else If @ContestName = 'VHF-ACTIVITY' Or @ContestName = 'SPB-OPEN-VHF'
				Begin
		        	Select
		        		@Score = sum(Mult.Mult) * 500 + Res.Points
			        From @LogFiles_BandResults Res
			        Join @LogFiles_BandResults_Mults Mult On MULT.ID_Parent = Res.ID
			        Where Res.Band_ID = @ALL_BANDS
			        And Res.Mode_ID = @Mode_ID
				    And Mult.Type = @MULT_DXCC
			        And Mult.Mode_ID = @Mode_ID
			        Group By
			        	Res.ID,
			        	Res.Points				        	
				End
				
				
	        Update @LogFiles_BandResults
	        Set Score = @Score
	        Where Band_ID = @ALL_BANDS
	        And Mode_ID = @Mode_ID
	        
	        Set @Mode_ID = @Mode_ID + 1
	        
	    End
    --/
    
    --+ Переносим временные таблицы в постоянные
    Insert LogFiles_BandResults
    (
    	Call,
    	Category,
    	Type,
    	Band_ID,
    	Mode_ID,
    	QSOs,
    	Points,
    	Score
    )
    Select
    	@My_Callsign,
    	@My_Category,
    	@Res_Type,
		Band_ID,
		Mode_ID,
		QSOs,
		Points,
		Score
	From @LogFiles_BandResults
	Order By ID
	
	Declare @ID_old Int
	Declare @ID_new Int
	
	Select @ID_old = max(ID) From @LogFiles_BandResults
	Select @ID_new = max(ID) From LogFiles_BandResults
	
    Insert LogFiles_BandResults_Mults
    (
	   ID_Parent,
	   Mode_ID,
	   Type,
	   Mult
    )
    Select
	   M.ID_Parent + @ID_new - @ID_old,
	   M.Mode_ID,
	   M.Type,
	   M.Mult
    From @LogFiles_BandResults_Mults M
    	
	/*
    Insert LogFiles_BandResults_Mults
    (
	   ID_Parent,
	   Mode_ID,
	   Type,
	   Mult
    )
    Select
	   BRNEW.ID,
	   M.Mode_ID,
	   M.Type,
	   M.Mult
    From @LogFiles_BandResults_Mults M
    Join @LogFiles_BandResults BR On BR.ID = M.ID_Parent
    Join LogFiles_BandResults BRNEW
    	On BRNEW.Call = @My_Callsign
    	And BRNEW.Type = @Res_Type
    	And BRNEW.Band_ID = BR.Band_ID
    	And BRNEW.Mode_ID = BR.Mode_ID	
    */
    
    Insert QSOs_Mults
    (
    	ID_QSO,
    	Category,
    	MultType,
    	ResType,
    	Mult
    )
    Select
    	ID_QSO,
    	@My_Category,
    	MultType,
    	ResType,
    	Mult    	
    From @QSOs_Mults M
    --/
    
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SuperCategory_Callsigns]'
GO
CREATE TABLE [dbo].[SuperCategory_Callsigns]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SuperCategory] [int] NULL,
[Callsign] [varchar] (30) COLLATE Cyrillic_General_CI_AS NULL,
[Exclude] [bit] NULL CONSTRAINT [DF__SuperCate__Exclu__1C5231C2] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__SuperCat__3214EC271A69E950] on [dbo].[SuperCategory_Callsigns]'
GO
ALTER TABLE [dbo].[SuperCategory_Callsigns] ADD CONSTRAINT [PK__SuperCat__3214EC271A69E950] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[IsSuperCategorySatisfied2]'
GO
CREATE Function [dbo].[IsSuperCategorySatisfied2]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    04.09.2016 23:18:48
-- <UpdateDate>    
-- <Parameters>
(
@SuperCategoryID Int,
@Callsign Varchar(30)
)
Returns Bit

-- <Returns>

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

	Declare @Result Bit
	
	If @Callsign In (Select Callsign From SuperCategory_Callsigns Where SuperCategory = @SuperCategoryID And isnull(Exclude, 0) = 1)
		Set @Result = 0
	Else
		Set @Result = 1
				
    Return @Result

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[LogFiles_BandResults_Select]'
GO
ALTER Function [dbo].[LogFiles_BandResults_Select]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>
(
)

-- <Returns>
Returns Table

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate>27.06.2013 02:02:04<Copier>John<Version>6<CopyOrder>
-- <CheckOut>False<CheckOutDate>23.11.2016 13:42<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Return
(
Select Top 100 Percent
	Results.Type
	, Results.ID
	, SuperCategory.SuperCategory
	, SuperCategory.ID SuperCategory_ID
	, Case When SuperCategory.UniteCategories = 1 Then '' Else LFC.Category End As Category
	, LogFiles.Call
	, Results.Score
	, Results.Place
	, row_number() Over 
	(
		Partition 
		By 
			Results.Type, 
			SuperCategory.SuperCategory, 
			Case When SuperCategory.UniteCategories = 1 Then '' Else LFC.Category End 
		Order By Results.Score Desc
	) As Rank
From LogFiles

left Join LogFilesAudio LFA
	On LFA.Call = LogFiles.Call
	
Join LogFilesCategory_Select LFC
	On LogFiles.Call = LFC.Call
	
Join Category
	On Category.Category = LFC.Category
	
Join CategoryHierarchy 
	On CategoryHierarchy.Category = Category.ID
	
Join SuperCategory
	On CategoryHierarchy.SuperCategory = SuperCategory.ID
	
Join LogFiles_BandResults As Results
	On LogFiles.Call = Results.Call
	And isnull(Results.Category, '') = Case When SuperCategory.UniteCategories = 1 Then '' Else LFC.Category End
	
Where Category.DoNotSetPlaces = 0
And Results.Band_ID = 0
And Results.Mode_ID = SuperCategory.Mode
And dbo.IsSuperCategorySatisfied(SuperCategory.ID, LogFiles.CountryID, LogFiles.OblID) = 1
And dbo.IsSuperCategorySatisfied2(SuperCategory.ID, LogFiles.Call) = 1

Order By
	Results.Type
	, SuperCategory.SuperCategory
	, LFC.Category
	, Results.Score Desc
	, Results.Points Desc
	, Results.QSOs Desc
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[VHF_MakeEquipmentDescription]'
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 @Equip E On L.Call= E.Call
	Where left(E.Equip, 255) <> ''
				
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[FullCycle]'
GO
ALTER Procedure [dbo].[FullCycle]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    03.02.2012 19:31:10
-- <UpdateDate>    
-- <Parameters>

-- <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 @InTran Bit	
	Set @InTran = 0

	Declare @Year Int
	Declare @ContestName Varchar(255)
	Declare @ContestType Varchar(3)
	Declare @Import_excel_logs Bit

	Set @Year = cast(dbo.Settings_Get('Year') As Int)	
	Set @ContestName = cast(dbo.Settings_Get('ContestName') As Varchar(255))	
	Set @ContestType = cast(dbo.Settings_Get('Contest_type') As Varchar(3))	
	Set @Import_excel_logs = cast(dbo.Settings_Get('Import_excel_logs') As Bit)

	Begin Try

		If @Import_excel_logs = 1
			Exec XLS_LOGS_Create

		Exec CreateLinkedServer

		If @Import_excel_logs = 1
			Exec XLS_LOGS_Import

		Begin Tran

			Set @InTran = 1

			Exec DeleteAll

			--+ Правим наблюдателей
			Update SWLs
			Set SWLs.Call1 = VV.Call2,
				SWLs.Call2 = VV.Call1,
				SWLs.NrSent = VV.NrRcvd,
				SWLs.NrRcvd = Null
			From SWLs
			Join SWLs VV On VV.ID = SWLs.ID
			Where VV.NrSent Is Null

			Update SWLs
			Set SWLs.Points = 3
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call1
				And QSOs.Call2 = SWLs.Call2
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrSent = SWLs.NrSent
				And QSOs.NrRcvd = SWLs.NrRcvd
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0			

			Update SWLs
			Set SWLs.Points = 3
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call2
				And QSOs.Call2 = SWLs.Call1
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrSent = SWLs.NrRcvd
				And QSOs.NrRcvd = SWLs.NrSent
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0			

			Update SWLs
			Set SWLs.Points = 1
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call1
				And QSOs.Call2 = SWLs.Call2
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrSent = SWLs.NrSent
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0
			Where SWLs.NrRcvd Is Null				

			Update SWLs
			Set SWLs.Points = 1
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call2
				And QSOs.Call2 = SWLs.Call1
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrRcvd = SWLs.NrSent
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0
			Where SWLs.NrRcvd Is Null				

			Update SWLs
			Set SWLs.Points = 1
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call1
				And QSOs.Call2 = SWLs.Call2
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrSent = SWLs.NrSent
				And QSOs.NrRcvd <> SWLs.NrRcvd
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0			
			Where SWLs.NrRcvd Is Not Null

			Update SWLs
			Set SWLs.Points = 1
			From SWLs
			Join QSOs
				On QSOs.Call1 = SWLs.Call1
				And QSOs.Call2 = SWLs.Call2
				And QSOs.Band = SWLs.Band
				And QSOs.Mode = SWLs.Mode
				And QSOs.NrSent <> SWLs.NrSent
				And QSOs.NrRcvd = SWLs.NrRcvd
				And dbo.CheckTime(QSOs.Time, SWLs.Time) = 0			
			Where SWLs.NrRcvd Is Not Null			
			--/

			If @ContestName = 'TWOCAPITALS'
				Begin
					-- убираем незначащие нули из номеров
					Update QSOs
					Set NrSent = cast(cast(left(NrSent, len(NrSent) - 1) As Int) As Varchar) + right(NrSent, 1)
					Where isnumeric(right(NrSent, 1)) = 0
					And isnumeric(left(NrSent, len(NrSent) - 1)) = 1

					Update QSOs
					Set NrRcvd = cast(cast(left(NrRcvd, len(NrRcvd) - 1) As Int) As Varchar) + right(NrRcvd, 1)
					Where isnumeric(right(NrRcvd, 1)) = 0
					And isnumeric(left(NrRcvd, len(NrRcvd) - 1)) = 1

					Update QSOs
					Set NrSent = cast(cast(NrSent As Int) As Varchar)
					Where isnumeric(NrSent) = 1

					Update QSOs
					Set NrRcvd = cast(cast(NrRcvd As Int) As Varchar)
					Where isnumeric(NrRcvd) = 1					
				End
			If @ContestName = 'RF-CHAMP-SSB'
				Delete LogFilesCategory
				Where Category = 'LB + HB'

			Exec Make_Result

			If @ContestName = 'RF-CHAMP-SSB'
				Insert LogFilesCategory
				(
					Call,
					Category
				)
				Select 
					t.Call,
					'LB + HB'
				From LogFilesCategory t
				Where Category In ('SINGLE-OP 160M-80M-40M', 'SINGLE-OP 20M-15M-10M')
				Group By t.Call

			Update LogFilesCategory
			Set LogFilesCategory.Category = LogFiles_ChangeCategory.Category_new
			From LogFilesCategory
			Join LogFiles_ChangeCategory On LogFiles_ChangeCategory.Call = LogFilesCategory.Call
			And LogFiles_ChangeCategory.Category = LogFilesCategory.Category

			Exec CalcResults @Year, @ContestName, 1

		Commit Tran

		Set @InTran = 0

		If @ContestType = 'VHF'
		And @ContestName <> 'VHF-ACTIVITY'
			Exec VHF_MakeEquipmentDescription

		Begin Try

			If @ContestType = 'VHF'
				Exec Parse_OPERATORS_VHF
			Else If @ContestType = 'HF'
				Exec Parse_OPERATORS

		End Try		
		Begin Catch
			Declare @message Nvarchar(max)
			Set @message = ERROR_MESSAGE()
			Print 'ОШИБКА в PARSE_Operators!'
			Print @message
		End Catch

		Exec ExportDataToMySQL_UA1DZ

	End Try

	Begin Catch 
		If @InTran = 1
			Rollback Tran		
		Exec Work_Throw
	End Catch

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[АНАЛИЗ Отбор для создания моделей]'
GO
Create procedure [dbo].[АНАЛИЗ Отбор для создания моделей]
as
Select Call2, count(*), 1 From QSOs t Where isnull(t.OblId, '') = '' And t.ErrType = 'nl' Group By call2 Having count(*) >= 10
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(*) >= 10
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(*) >= 10
Order By 2 desc
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[АНАЛИЗ Модель отчета]'
GO
ALTER Procedure dbo.[АНАЛИЗ Модель отчета]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    05.02.2012 18:05:10
-- <UpdateDate>    
-- <Parameters>
@Callsign Varchar(20)

-- <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
		isnull(Bands.Band_Mid, Bands.Freq) Band
		, Modes.Mode
		, convert(Varchar(10), QSOs.[Date], 120) As [Date]
		, QSOs.Call2 As Call1
		, left(QSOs.[Time], 2) Hour
		, right(QSOs.[Time], 2) Minute
		, QSOs.Call1 As Call2
		, Modes.Default_report As RprtSent
		, QSOs.NrRcvd As NrSent
		, Modes.Default_report As RprtRcvd
		, QSOs.NrSent As NrRcvd
		, dbo.Compare(QSOs.Call2, @Callsign) As [Identity]
	From QSOs
	Join Modes On QSOs.Mode = Modes.Mode
	Join Bands On QSOs.Band = Bands.Band
	Where dbo.Compare(QSOs.Call2, @Callsign) Between 0.7 And 1
	And (QSOs.ErrType In ('nl', 'NIL') Or QSOs.ID2 Is Null)
	Order By QSOs.[Time]
	
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[АНАЛИЗ Модель отчета текст]'
GO
CREATE Procedure dbo.[АНАЛИЗ Модель отчета текст]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    10.05.2018 11:14:30
-- <UpdateDate>    
-- <Parameters>
@Callsign Varchar(20),
@Log Varchar(max) = Null Out

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate><Copier><Version><CopyOrder>
-- <CheckOut>False<CheckOutDate>10.05.2018 11:19<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin
    Set Nocount On
    
	Declare @Model Table
	(
		Band Varchar(5),
		Mode Nvarchar(15),
		[Date] Varchar(10),
		Call1 Nvarchar(15),
		Hour Nvarchar(2),
		Minute Nvarchar(2),
		Call2 Nvarchar(15),
		RprtSent Nvarchar(3),
		NrSent Nvarchar(15),
		RprtRcvd Nvarchar(3),
		NrRcvd Nvarchar(15),
		[Identity] Float Null,
    	ID Int identity(1, 1) Primary Key 
	)
	
	Insert @Model
	Exec dbo.[АНАЛИЗ Модель отчета] @Callsign
		
	Set @log =
'START-OF-LOG: 3.0
CONTEST: ALRS-UA1DZ-CUP
CALLSIGN: ' + @Callsign + '
CATEGORY: MODEL
NAME: Model
ADDRESS: Model
LOCATION: DX
EMAIL: rk1aa@mail.ru
OPERATORS: Фамилия Имя Отчество 1900 б/р ' + @Callsign + ' 4
'

	Declare @qsoCount Int
	Declare @i Int = 0
	Declare @logLine Varchar(255)
	
	Select @qsoCount = count(*) From @Model
	
	While @i < @qsoCount
		Begin
			Set @i = @i + 1
			
			Select Top 1
				@logLine = 
					'QSO: ' + 
					right('     ' + Band, 5) + ' ' +
					right('  ' + Mode, 2) + ' ' +
					right('          ' + [Date], 10) + ' ' +
					right('  ' + Hour, 2) + 
					right('  ' + Minute, 2) + ' ' +
					left(Call1 + '             ', 13) + ' ' +
					left(RprtSent + '   ', 3) + ' ' +
					left(NrSent + '      ', 6) + ' ' +					
					left(Call2 + '             ', 13) + ' ' +
					left(RprtRcvd + '   ', 3) + ' ' +
					left(NrRcvd + '      ', 6)
			From @Model Where ID = @i
			
			Set @log = @log + @logLine + Char(13) + Char(10)
					
		End
	
	Set @log = @log + 'END-OF-LOG:'
	
	Print @log

    Return
    
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[АНАЛИЗ Отчет]'
GO
ALTER Procedure [dbo].[АНАЛИЗ Отчет]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    03.02.2012 19:43:44
-- <UpdateDate>    
-- <Parameters>
@Callsign Varchar(20),
@Category Varchar(50) = Null,
@Res_Type Int = Null,
@Err_Type Varchar(10) = 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>---------------------------------------------------------------------------------------
-- With Recompile
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
    
    Set @MULT_DXCC = 1
    Set @MULT_RDA = 2
    Set @MULT_OBL = 3
    --/
    
	Declare @ContestName Varchar(255)
	
	Set @ContestName = cast(dbo.Settings_Get('ContestName') As Varchar(255))	
    
    --/
    
    If @ContestName = 'VHF-ACTIVITY'
		Exec dbo.[АНАЛИЗ Отчет за активность]
		   @Callsign,
		   @Res_Type,
		   @Err_Type    
    Else
    	Begin
		    
		    If isnull(@Category, '') = ''
				Select Top 1
					@Category = Category
		    	From LogFilesCategory
		    	Where Call = @Callsign
					
			If isnull(@Res_Type, 0) = 0
				Set @Res_Type = 2
			
			--+ Временные таблицы
			
			Create Table #QSOs
			(
				ID Int,
				ID2 Int,
				Call1 Nvarchar(30),
				Call2 Nvarchar(30),
				Band Nvarchar(10),
				Mode Nvarchar(6),
				[Time] Nvarchar(8),
				NrSent Nvarchar(30),
				NrRcvd Nvarchar(30),
				ErrType Varchar(5),
				ErrPar Varchar(255),
				Freq Nvarchar(10),
				[Date] Datetime,
				IsLogRcvd Bit,
				CorrectCall Nvarchar(30),
				ErrText Nvarchar(255),
				Category Nvarchar(50),
				Band_Long Nvarchar(255),
				Band_ID Int,
				BandCoef Int
			)
			
			If @Err_Type Is Null
				Insert #QSOs
				Select 
					QSOs.ID,
					QSOs.ID2,
					QSOs.Call1,
					QSOs.Call2,
					QSOs.Band,
					QSOs.Mode,
					QSOs.[Time],
					QSOs.NrSent,
					QSOs.NrRcvd,
					QSOs.ErrType,
					QSOs.ErrPar,
					QSOs.Freq,
					QSOs.[Date],
					QSOs.IsLogRcvd,
					Corr.Call1 CorrectCall,
					Errors.[Text] ErrText,
					Null Category,
					Bands.Band_Long,
					Bands.ID Band_ID,
					Bands.BandCoef
				From QSOs		
				Join Bands 
					On Bands.Band = QSOs.Band
				left Join QSOs Corr
					On Corr.ID = QSOs.ID2
				left Join Errors
					On Errors.[Error] = QSOs.ErrType
				Where QSOs.Call1 = @Callsign	
			Else
				Insert #QSOs
				Select 
					QSOs.ID,
					QSOs.ID2,
					QSOs.Call1,
					QSOs.Call2,
					QSOs.Band,
					QSOs.Mode,
					QSOs.[Time],
					QSOs.NrSent,
					QSOs.NrRcvd,
					QSOs.ErrType,
					QSOs.ErrPar,
					QSOs.Freq,
					QSOs.[Date],
					QSOs.IsLogRcvd,
					Corr.Call1 CorrectCall,
					Errors.[Text] ErrText,
					Null Category,
					Bands.Band_Long,
					Bands.ID Band_ID,
					Bands.BandCoef
				From QSOs		
				Join Bands 
					On Bands.Band = QSOs.Band
				left Join QSOs Corr
					On Corr.ID = QSOs.ID2
				left Join Errors
					On Errors.[Error] = QSOs.ErrType
				Where QSOs.Call1 = @Callsign	
				And QSOs.ErrType = @Err_Type
			
			Create Index IX_QSOs_Temp1 On #QSOs (ID)
			
			Create Table #Mults
			(
				ID_QSO Int,
				Category Varchar(50),
				MultType Int,
				ResType Int,
				Mult Varchar(10)
			)
			
			Insert #Mults
			Select
				M.ID_QSO,
				M.Category,
				M.MultType,
				M.ResType,
				M.Mult
			From QSOs_Mults M
			Join #QSOs Q 
				On Q.ID = M.ID_QSO
			
			Create Index IX_Mults_Temp1 On #Mults (ID_QSO)
			
			Create Table #Points
			(
				ID_QSO Int,
				ResType Int,
				Points Int
			)
			
			Insert #Points
			Select
				ID_QSO,
				ResType,
				Points
			From QSOs_Points P
			Join #QSOs Q
				On Q.ID = P.ID_QSO
			
			Create Index IX_Points_Temp1 On #Points (ID_QSO) 
			--/
				
			Select 				
				Q.Band,
				Q.Freq,
				Q.Mode,
				Q.Call1,
				Q.[Time],
				Q.Call2,
				Q.NrSent,
				Q.NrRcvd,
				isnull(PTS.Points, 0) Points,
				isnull(PTS_Claimed.Points, 0) Points_Claimed,
				M1.Mult Mult1,
				M2.Mult Mult2,
				M3.Mult Mult3,
				Q.ErrType,
				Q.ErrText,
				Q.ErrPar,
				Q.IsLogRcvd,
				Q.ID,
				Q.ID2,
				Q.CorrectCall,
				Case When @ContestName = 'LO-OPEN-VHF' Then Null Else isnull(PTS_Claimed.Points, 0) / Q.BandCoef End Distance,
				Q.[Date],
				Q.Band_Long,
				Q.Band_ID Band_ID
			From #QSOs Q
			left Join #Points PTS
				On PTS.ID_QSO = Q.ID
				And PTS.ResType = @Res_Type
			left Join #Points PTS_Claimed
				On PTS_Claimed.ID_QSO = Q.ID
				And PTS_Claimed.ResType = @CLAIMED_RESULTS
			left Join #Mults M1
				On M1.ID_QSO = Q.ID
				And M1.ResType = @Res_Type
				And M1.MultType = @MULT_DXCC
				And M1.Category = @Category
			left Join #Mults M2
				On M2.ID_QSO = Q.ID
				And M2.ResType = @Res_Type
				And M2.MultType = @MULT_RDA
				And M2.Category = @Category
			left Join #Mults M3
				On M3.ID_QSO = Q.ID
				And M3.ResType = @Res_Type
				And M3.MultType = @MULT_OBL
				And M3.Category = @Category
			Order By
				Q.[Date],
				Q.[Time],
				Q.NrSent
	
		End
			
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SaveTextFile2]'
GO
CREATE Procedure [dbo].[SaveTextFile2]
@Text As Varchar(max),
@FileName As Varchar(1000)
As
Begin

	Set Nocount On
		
	Declare @OLE Int 
	Declare @FileID Int
	Declare @Result Int
	
	Execute sp_oacreate 'Scripting.FileSystemObject', @OLE Out 
	Execute sp_oamethod @OLE, 'CreateTextFile', @FileID Out, @FileName, 8, 1 
	Execute sp_oamethod @FileID, 'WriteLine', Null, @Text
	Execute sp_oadestroy @FileID 
	Execute sp_oadestroy @OLE 
	
	Execute @Result = sp_oamethod @OLE, 'OpenTextFile', @FileID Out, @FileName, 8, 1
	
	Return isnull(@Result, -1)
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[АНАЛИЗ Создать модели отчетов]'
GO
CREATE Procedure [dbo].[АНАЛИЗ Создать модели отчетов]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       SkladNetAppServ
-- <CreateDate>    10.05.2018 12:01:14
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy><Checker><CopyDate><Copier><Version><CopyOrder>
-- <CheckOut><CheckOutDate><CheckOutAuthor>
--<EndHeader>---------------------------------------------------------------------------------------
As 
Begin
    Set Nocount On
    
    Declare @folder Varchar(255) = 'c:\LogModels\'

	Declare @logs Table
	(
		Call Varchar(30),
		QSOsCount Int,
		Type Int,
		ID Int identity(1, 1) Primary Key
	)
	
	Insert @logs
	Exec [АНАЛИЗ Отбор для создания моделей]
	
	Declare @logsCount Int
	Declare @i Int = 0
	Declare @Call Varchar(30)
	Declare @log Varchar(max)
	Declare @fileName Varchar(255)
	
	Select @logsCount = count(*) From @logs
	
	While @i < @logsCount
		Begin
			Set @i = @i + 1			
			Select @Call = Call From @logs Where ID = @i			
			Exec dbo.[АНАЛИЗ Модель отчета текст] @Call, @log Out			
			Set @fileName = @folder + replace(@call, '/', '-') + '.log'
			Exec SaveTextFile2 @log, @fileName							
		End
		
    Return
    
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ЗачетПоРегионам_UA1DZ]'
GO
ALTER Procedure [dbo].[ЗачетПоРегионам_UA1DZ]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>
@SuperCategoryID Int

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>SkladNetAppServ<CopyDate>22.05.2018 14:46<Copier>SkladNetAppServ<Version>6<CopyOrder>
-- <CheckOut>False<CheckOutDate>22.05.2018 14:44<CheckOutAuthor>SkladNetAppServ
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
	
	Declare @Res Table 
	(
		OblID Varchar(2),
		Call Varchar(15),
		Call_Category Varchar(255),
		Place Int,
		Score Int
	)
	
	Declare @Res2 Table 
	(
		Obl Varchar(2),
		Name Varchar(255),
		TotalScore Int,
		Place Int identity(1, 1) Primary Key
	)
	
	Insert @Res
	Select
		L.OblID,	
		L.Call,	
		BR.Category,
		row_number() Over (Partition By L.OblID Order By BR.Score Desc) As Rank,
		BR.Score
	From LogFiles_BandResults BR
	Join LogFiles L On L.Call = BR.Call
	Join LogFilesCategory LFC On LFC.Call = L.Call
	Join Category On Category.Category = LFC.Category
	Join CategoryHierarchy On CategoryHierarchy.Category = Category.Id
	Join SuperCategory On CategoryHierarchy.SuperCategory = SuperCategory.Id
	Where BR.Type = 2
	And CategoryHierarchy.SuperCategory = @SuperCategoryId
	And BR.Mode_ID = SuperCategory.Mode
	And BR.Band_ID = 0
	And BR.Score > 0
	And BR.Category Is Not Null

	
	Insert @Res2
	Select
		OblID, 
		OblDat.RusName,
		sum
		(
			Case
				When Place <= 3
					Then Score
				Else 0
			End
		) Очки
	From @Res Res
	Join OblDat On OblDat.ShortName = Res.OblID
	Group By
		Res.OblID,
		OblDat.RusName
	Order By 3 Desc
	
	Select
		Place Место,
		Name Субъект_РФ,
		TotalScore Результат
	From @Res2
	Order By Place
	
	Select
		Res2.Place Место,
		Res2.Name Субъект_РФ, 
		Res2.TotalScore Результат_области,
		Res.Call Позывной,
		Res.Score Результат
	From @Res Res
	Join @Res2 Res2 On Res2.Obl = Res.OblID
	Where Res.Place <= 3
	Order By 
		Res2.Place,
		Res.Place
				
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[VHF_Activity_Dates_Fill]'
GO
ALTER Procedure [dbo].[VHF_Activity_Dates_Fill]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>
@year As Int = Null

-- <Returns>

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

	If isnull(@year, 0) = 0
		Set @year = year(getdate()) + 1 

	Truncate Table VHF_Activity_Dates

	Set datefirst 1

	Set language english

	Declare @y Int = @year
	Declare @m Int = 0
	Declare @d Int
	Declare @h1 Int
	Declare @h2 Int
	Declare @date Datetime

	While @m < 12
	Begin
		Set @m = @m + 1

		Set @d = 0

		While @d < 7
		Begin
			Set @d = @d + 1
			Set @date = cast(cast(@y As Varchar) + '-' +cast(@m As Varchar) + '-' + cast(@d As Varchar) As Datetime)
			If datepart(dw, @date) = 2
			Begin
				If @m Between 4 And 10
					Begin
						Set @h1 = 17
						Set @h2 = 21
					End
				Else
					Begin
						Set @h1 = 18
						Set @h2 = 22
					End

				Insert VHF_Activity_Dates
				(
					Date1,
					Date2,
					Band
				)
				Select
					dateadd(hh, @h1, @date),
					dateadd(hh, @h2, @date),
					7

				Set @date = dateadd(dd, 7, @date)

				Insert VHF_Activity_Dates
				(
					Date1,
					Date2,
					Band
				)
				Select
					dateadd(hh, @h1, @date),
					dateadd(hh, @h2, @date),
					8

				Set @date = dateadd(dd, 7, @date)

				Insert VHF_Activity_Dates
				(
					Date1,
					Date2,
					Band
				)
				Select
					dateadd(hh, @h1, @date),
					dateadd(hh, @h2, @date),
					9

				Set @d = 7
			End

		End

	End

	Select
		*
	From VHF_Activity_Dates

	Return

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[АНАЛИЗ Результаты по подгруппам]'
GO
ALTER Function [dbo].[АНАЛИЗ Результаты по подгруппам]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    30.01.2012 19:06:43
-- <UpdateDate>    
-- <Parameters>
(
)

-- <Returns>
Returns @Result Table
(
SuperCategory_ID Int,
SuperCategory Varchar(255),
Category Varchar(255),
Call Varchar(20),
Place Int,
AudioExists Bit,
QSOs_Claimed Int,
Points_Claimed Int,
Mult1_Claimed Int,
Mult2_Claimed Int,
Mult3_Claimed Int,
Score_Claimed Int,
QSOs Int,
Points Int,
Mult1 Int,
Mult2 Int,
Mult3 Int,
Score Int,
Link Varchar(1000),
Location Varchar(255),
Soapbox Varchar(1000),
Band_ID Int,
Category_ID Int,
[Month] Int,
SuperCategory_SortOrder Int
)

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate>18.07.2013 23:43:37<Copier>John<Version>4<CopyOrder>
-- <CheckOut>False<CheckOutDate>23.11.2016 13:42<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin
	
	--+ Перечисления и константы
	--+ Вид результатов
	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
    --/
    --/
      
    Declare @UBN_Path Varchar(255)
    
    Set @UBN_Path = cast(dbo.Settings_Get('UBN_Path') As Varchar(255))
    
	Insert @Result
	Select
		SC.ID,
		SC.SuperCategory,
		Case
			When SC.UniteCategories = 1 
				Then ''
			Else
				Category.Description
		End,
		LogFiles.Call,
		
		--Confirmed.Place,
		Case 
			When Category.DoNotSetPlaces = 1
				Then Null 
			Else
				row_number() Over 
				(
					Partition 
					By 
						SC.ID,
						Case When SC.UniteCategories = 1 Then '' Else LFC.Category End 
					Order By 
						Confirmed.Score Desc,
						Confirmed.QSOs
				) 
		End As Place,		
		
		Case When LFA.Call Is Null Then 0 Else 1 End AudioExists,
		Claimed.QSOs As QSOs_Claimed,
		Claimed.Points As Points_Claimed,
		Mult1_Claimed.Mult As Mult1_Claimed,
		Mult2_Claimed.Mult As Mult2_Claimed,
		Mult3_Claimed.Mult As Mult3_Claimed,
		Claimed.Score As Score_Claimed,
		Confirmed.QSOs As QSOs,
		Confirmed.Points,
		Mult1.Mult Mult1,
		Mult2.Mult Mult2,
		Mult3.Mult Mult3,
		Confirmed.Score,
		'<a href=' + @UBN_Path + replace(lower(LogFiles.Call), '/', '-') + '>' +  LogFiles.Call + '</a>',
		isnull(LFC.Location, LogFiles.Location) Location,
		LogFiles.SOAPBOX,
		Category.Band Band_ID,
		Category.ID Category_ID,
		Category.[Month],
		SC.SortOrder		
	From LogFiles
	left Join LogFilesAudio LFA On LFA.Call = LogFiles.Call
	Join LogFilesCategory_Select LFC On LFC.Call = LogFiles.Call
	Join Category
		On Category.Category = LFC.Category
	Join CategoryHierarchy CH
		On CH.Category = Category.ID
	Join SuperCategory SC
		On SC.ID = CH.SuperCategory
	Join LogFiles_BandResults As Claimed
		On LogFiles.Call = Claimed.Call			
		And isnull(Claimed.Category, '') = Case When SC.UniteCategories = 1 Then '' Else isnull(LFC.Category, '') End
	Join LogFiles_BandResults As Confirmed
		On LogFiles.Call = Confirmed.Call
		And isnull(Confirmed.Category, '') = Case When SC.UniteCategories = 1 Then '' Else isnull(LFC.Category, '') End
	left Join LogFiles_BandResults_Mults As Mult1
		On Mult1.ID_Parent = Confirmed.ID
		And Mult1.Type = @MULT_DXCC
		And Mult1.Mode_ID = SC.Mode		
	left Join LogFiles_BandResults_Mults As Mult2
		On Mult2.ID_Parent = Confirmed.ID
		And Mult2.Type = @MULT_RDA
		And Mult2.Mode_ID = SC.Mode
	left Join LogFiles_BandResults_Mults As Mult3
		On Mult3.ID_Parent = Confirmed.ID
		And Mult3.Type = @MULT_OBL
		And Mult3.Mode_ID = SC.Mode
	left Join LogFiles_BandResults_Mults As Mult1_Claimed
		On Mult1_Claimed.ID_Parent = Claimed.ID
		And Mult1_Claimed.Type = @MULT_DXCC
		And Mult1_Claimed.Mode_ID = SC.Mode
	left Join LogFiles_BandResults_Mults As Mult2_Claimed
		On Mult2_Claimed.ID_Parent = Claimed.ID
		And Mult2_Claimed.Type = @MULT_RDA
		And Mult2_Claimed.Mode_ID = SC.Mode
	left Join LogFiles_BandResults_Mults As Mult3_Claimed
		On Mult3_Claimed.ID_Parent = Claimed.ID
		And Mult3_Claimed.Type = @MULT_OBL
		And Mult3_Claimed.Mode_ID = SC.Mode
	Where Claimed.Band_ID = @ALL_BANDS
	And Claimed.Mode_ID = SC.Mode
	And Claimed.Type = @CLAIMED_RESULTS
	And Confirmed.Band_ID = @ALL_BANDS
	And Confirmed.Mode_ID = SC.Mode
	And Confirmed.Type = @CONFIRMED_RESULTS
	And dbo.IsSuperCategorySatisfied(SC.ID, LogFiles.CountryID, LogFiles.OblID) = 1
	And dbo.IsSuperCategorySatisfied2(SC.ID, LogFiles.Call) = 1
	Order By
		SC.SortOrder,
		Category.Description,
		Confirmed.Score Desc,
		Confirmed.QSOs
			
    Return

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[QSOs_ODX]'
GO
ALTER Function dbo.QSOs_ODX
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    26.06.2013 11:25:51
-- <UpdateDate>    
-- <Parameters>
(
@Month Int = Null
)

-- <Returns>
Returns @Result Table
(
Callsign Varchar(20),
[Month] Int,
Band Varchar(10),
ODX Varchar(20),
ODX_Distance Int,
ODX_WWLoc Varchar(6),
ODX_Count Int,
ODX_Count_By_Band Int
)

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

	Declare @ContestName Varchar(255) = cast(dbo.Settings_Get('ContestName') As Varchar(255))	

	If @ContestName = 'LO-OPEN-VHF'
		Begin
			Insert @Result		
			Select 
				LFC.Call,
				0,
				LFC.Band,
				Null,
				Null,
				Null,
				(Select count(*) From LogFilesCategory t Where t.Call = LFC.Call),
				1
			From LogFilesCategory LFC
			
			Return		
		End

	Set @Month = isnull(@Month, 0)

	Declare @QSOs_MaxPoints Table
	(
		Call Varchar(30),
		[Month] Int,
		Band Varchar(10),
		Points Int
	)
	
	Declare @QSOs_ODX_Count Table
	(
		Call Varchar(30),
		[Month] Int,
		Band Varchar(10),
		ODX_Count Int
	)
	
	Insert @QSOs_MaxPoints
	Select
		QSOs.Call1 Call,
		month(QSOs.[Date]) [Month],
		QSOs.Band,
		max(PTS.Points) As Points
	From QSOs
	left Join QSOs_Points PTS
		On PTS.ID_QSO = QSOs.ID
		And PTS.ResType = 2	-- подтвержденные
	Where QSOs.ErrType = '+'
	And (month(QSOs.[Date]) = @Month Or @Month = 0)
	Group By
		QSOs.Call1,
		month(QSOs.[Date]),
		QSOs.Band
	Union All
	Select
		QSOs.Call1 Call,
		0 [Month],
		QSOs.Band,
		max(PTS.Points) As Points
	From QSOs
	left Join QSOs_Points PTS
		On PTS.ID_QSO = QSOs.ID
		And PTS.ResType = 2	-- подтвержденные
	Where QSOs.ErrType = '+'
	Group By
		QSOs.Call1,
		QSOs.Band

	Insert @Result
	Select Distinct
		QSOs.Call1 As Callsign,
		QSOs_MaxPoints.[Month],
		QSOs_MaxPoints.Band,
		QSOs.Call2 As ODX,
		PTS.Points / Bands.BandCoef As ODX_Distance,
		isnull(LogFiles.Location, right(QSOs.NrRcvd, 6)) As ODX_WWLoc,
		Null As ODX_Count,
		Null As ODX_Count_By_Band
	From QSOs
	left Join LogFiles
		On LogFiles.Call = QSOs.Call2
	left Join Bands
		On Bands.Band = QSOs.Band
	left Join QSOs_Points PTS
		On PTS.ID_QSO = QSOs.ID
		And PTS.ResType = 2	-- подтвержденные
	Join @QSOs_MaxPoints QSOs_MaxPoints
		On QSOs.Band = QSOs_MaxPoints.Band
		And PTS.Points = QSOs_MaxPoints.Points
		And QSOs.Call1 = QSOs_MaxPoints.Call
		And (month(QSOs.[Date]) = QSOs_MaxPoints.[Month] Or QSOs_MaxPoints.[Month] = 0)
	Where (month(QSOs.[Date]) = @Month Or @Month = 0)

	Insert @QSOs_ODX_Count
	Select
		Callsign,
		[Month],
		Null,
		count(*)
	From @Result
	Group By
		Callsign,
		[Month]
		
	Update Res
	Set ODX_Count = ODX_Count.ODX_Count
	From @Result Res
	Join @QSOs_ODX_Count ODX_Count
		On Res.Callsign = ODX_Count.Call
		And Res.[Month] = ODX_Count.[Month]
	
	Delete @QSOs_ODX_Count
	
	Insert @QSOs_ODX_Count
	Select
		Callsign,
		[Month],
		Band,
		count(*)
	From @Result
	Group By
		Callsign,
		[Month],
		Band
	
	Update Res
	Set ODX_Count_By_Band = ODX_Count.ODX_Count
	From @Result Res
	Join @QSOs_ODX_Count ODX_Count
		On Res.Callsign = ODX_Count.Call
		And Res.[Month] = ODX_Count.[Month]
		And REs.Band = ODX_Count.Band
	
    Return
    
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Ranks_GetHF_EVSK]'
GO
ALTER Function [dbo].[Ranks_GetHF_EVSK]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    29.03.2016 17:35:04
-- <UpdateDate>    
-- <Parameters>
(
@Rank Int,
@MS Int,
@KMS Int,
@R1 Int,
@TOTAL Int
)
Returns Varchar(10)

-- <Returns>

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

	Declare @Result Varchar(10)
	Declare @ID_MS Int = 3
	Declare @ID_KMS Int = 4
	Declare @ID_R1 Int = 5
	Declare @ID_R2 Int = 6
	Declare @ID_R3 Int = 7
	
	If @Rank = @ID_MS
		Set @Rank = 
			Case 
				When (@MS < 8)
					Then @ID_KMS
				Else
					@Rank
			End
			
	If @Rank = @ID_KMS
		Set @Rank = 
			Case
				When (@MS + @KMS < 5)
					Then @ID_R1
				Else
					@Rank
			End
			
	If @Rank = @ID_R1
		Set @Rank = 
			Case 
				When (@MS + @KMS + @R1 < 5)
					Then @ID_R2
				Else
					@Rank
			End
	
	If @Rank = @ID_R2
		Set @Rank = 
			Case 
				When (@Total < 8)
					Then @ID_R3
				Else
					@Rank
			End
	
	If @Rank >= @ID_R3
		Set @Rank = 
			Case 
				When (@Total < 9)
					Then @Rank
				Else
					Null
			End
	
	Select Top 1 
		@Result = Rank 
	From Ranks 
	Where ID = @Rank
	
    Return @Result 

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[АНАЛИЗ Результаты по подгруппам с разрядами]'
GO
ALTER Function [dbo].[АНАЛИЗ Результаты по подгруппам с разрядами]
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    04.02.2012 3:08:53
-- <UpdateDate>    
-- <Parameters>
()

-- <Returns>
Returns Table

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

	Select
		R.*,
		dbo.Ranks_GetHF_EVSK(Ranks.ID, MS_KMS.MS, MS_KMS.KMS, MS_KMS.R1, MS_KMS.TOTAL) Rank, 
		Ops.ФИО FIO,
		Ops.Разряд Rank0,
		Ops.Год_рождения BirthYear,
		MS_KMS.MS,		
		MS_KMS.KMS,
		MS_KMS.TOTAL,
		MS_KMS.R1
	From
	(
		Select
			Res.*,
			Rank.VIP,
			Case When isnull(Rank.VIP , 0) = 0 Then Null Else floor(Res.Score / Rank.VIP * 100) End Proc_VIP
		From dbo.[АНАЛИЗ Результаты по подгруппам]() Res
		left Join 
		(
			Select
				T.SuperCategory_ID,
				floor(cast(sum(T.Score) As Float) / 3) VIP
			From dbo.[АНАЛИЗ Результаты по подгруппам]() T
			Join SuperCategory SC On SC.ID = T.SuperCategory_ID
			Where T.Place Between 2 And 4
			And SC.CalcRanks = 1
			Group By
				T.SuperCategory_ID
		) Rank On Rank.SuperCategory_ID = Res.SuperCategory_ID
	) R
	left Join Ranks On R.Proc_VIP >= Ranks.MinProc
					And R.Proc_VIP < Ranks.MaxProc
					And Ranks.ID < 8 -- не ниже 3-его разряда
	left Join Operators2 Ops On Ops.Радиостанция = R.Call
	left Join 
	(
		Select
			SC.ID SC_ID
			, sum(Case When isnull(Rs.ID, 100) = 4 Then 1 Else 0 End) KMS
			, sum(Case When isnull(Rs.ID, 100) <= 3 Then 1 Else 0 End) MS
			, sum(Case When isnull(Rs.ID, 100) = 5 Then 1 Else 0 End) R1
			, count(*) TOTAL
		From Operators Ops
		left Join LogFilesCategory_Select LFC
			On LFC.Call = Ops.Радиостанция
		left Join LogFiles
			On LogFiles.Call = LFC.Call
		left Join Category C
			On C.Category = LFC.Category
		left Join CategoryHierarchy CH
			On CH.Category = C.ID
		left Join SuperCategory SC 
			On SC.ID = CH.SuperCategory		
		left Join Ranks Rs
			On Rs.Rank = OPs.Разряд
		Where isnull(Тренер, '') = ''
		And dbo.IsSuperCategorySatisfied(SC.ID, LogFiles.CountryID, LogFiles.OblID) = 1
		And dbo.IsSuperCategorySatisfied2(SC.ID, LogFiles.Call) = 1
		Group By SC.ID
	) MS_KMS
		On MS_KMS.SC_ID = R.SuperCategory_ID
	/*
	Order By
		SuperCategory
		, Category
		, Place
		, Score Desc
		, Points Desc
		, QSOs Desc
	*/
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[RUN_ALL]'
GO

ALTER Procedure dbo.RUN_ALL
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    17.02.2012 14:33:36
-- <UpdateDate>    
-- <Parameters>
@BackupPath Varchar(255) = Null,
@BackupOnly Bit = 0

-- <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
	
	--+ Backup
	
	If isnull(@BackupPath, '') = ''
		Set @BackupPath = cast(dbo.Settings_Get('Backup_path') as varchar(255))
		
	If isnull(@BackupPath, '') = ''
		Set @BackupPath = 'C:\MSSQL\Backup\'
		
	If right(@BackupPath, 1) <> '\'
		Set @BackupPath = @BackupPath + '\'
		
	Declare @DBName Nvarchar(255)
	Set @DBName = db_name()
	
	Declare @DBFileName Varchar(255)
	
	Declare @pathName Nvarchar(512) 
	Set @pathName = @BackupPath + @DBName + '-' + convert(Varchar(8), getdate(), 112) + replace(convert(Varchar(8), getdate(), 8), ':', '') + '.bak' 
	
	Declare @backupName Nvarchar(512) 
	Set @backupName = @DBName + N' - Full Database Backup'
	
	Backup Database @DBName To Disk = @pathName
	With NOFORMAT, NOINIT, NAME = @backupName, SKIP, NOREWIND, Nounload, Stats = 10
	
	Declare @backupSetId As Int
	Select
		@backupSetId = position
	From msdb..backupset
	Where database_name = @DBName
	And backup_set_id = 
		(
			Select
				max(backup_set_id)
			From msdb..backupset
			Where database_name = @DBName
		)
		
	If @backupSetId Is Null
		Raiserror(N'Ошибка верификации. Сведения о резервном копировании не найдены.', 16, 1)
		
	Restore VERIFYONLY From  Disk = @pathName With  File = @backupSetId,  Nounload,  NOREWIND
	
	--/
	
	--+ Shrink
	
	Dbcc SHRINKDATABASE(@DBName)
	
	Declare @FileId as int 
	
	Select Top 1 
		@FileId = file_id 
	From sys.database_files 
	Where [type] = 0
	
	if @FileId is not null
		DBCC SHRINKFILE (@FileId, 0, TRUNCATEONLY)	
	
	Select Top 1
		@FileId = file_id 
	From sys.database_files 
	Where [type] = 1
	
	if @FileId is not null
		DBCC SHRINKFILE (@FileId, 0, TRUNCATEONLY)
		
	Print 'Database ' + @DBName + ' has been shrunk.'		
	
	--/
	
	--+ Run crosscheck
	
	If isnull(@BackupOnly, 0) = 0
		Exec FullCycle
	
	--/
		
	Return
		
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ЗачетПоРегионам_UA1DZ_2]'
GO
ALTER Procedure dbo.ЗачетПоРегионам_UA1DZ_2
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>
@SuperCategoryID Int

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>SkladNetAppServ<CopyDate>22.05.2018 14:46<Copier>SkladNetAppServ<Version>6<CopyOrder>
-- <CheckOut>False<CheckOutDate>22.05.2018 14:08<CheckOutAuthor>SkladNetAppServ
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
	
	Declare @Res Table 
	(
		OblID Varchar(2),
		Callsign Varchar(15),
		Call_Category Varchar(255),
		Place Int,
		Score Int
	)
	
	Declare @Res2 Table 
	(
		Obl Varchar(2),
		Name Varchar(255),
		TotalScore Int,
		Place Int identity(1, 1) Primary Key
	)
	
	Declare @Res3 Table
	(
		Place Int,
		Name Varchar(255), 
		TotalScore Int,
		Callsign Varchar(15),
		Score Int
	)
	
	Insert @Res
	Select
		L.OblID,	
		L.Call,	
		BR.Category,
		row_number() Over (Partition By L.OblID Order By BR.Score Desc) As Rank,
		BR.Score
	From LogFiles_BandResults BR
	Join LogFiles L On L.Call = BR.Call
	Join LogFilesCategory LFC On LFC.Call = L.Call
	Join Category On Category.Category = LFC.Category
	Join CategoryHierarchy On CategoryHierarchy.Category = Category.Id
	Join SuperCategory On CategoryHierarchy.SuperCategory = SuperCategory.Id
	Where BR.Type = 2
	And CategoryHierarchy.SuperCategory = @SuperCategoryId
	And BR.Mode_ID = SuperCategory.Mode
	And BR.Band_ID = 0
	And BR.Score > 0
	And BR.Category Is Not Null
	
	Insert @Res2
	Select
		OblID, 
		OblDat.RusName,
		sum
		(
			Case
				When Place <= 3
					Then Score
				Else 0
			End
		) Очки
	From @Res Res
	Join OblDat On OblDat.ShortName = Res.OblID
	Group By
		Res.OblID,
		OblDat.RusName
	Order By 3 Desc
	
	Insert @Res3		
	Select
		Res2.Place Место,
		Res2.Name Субъект_РФ, 
		Res2.TotalScore Результат_области,
		Res.Callsign Позывной,
		Res.Score Результат
	From @Res Res
	Join @Res2 Res2 On Res2.Obl = Res.OblID
	Where Res.Place <= 3
	Order By 
		Res2.Place,
		Res.Place
	
	Select 
		Place Место,
		Name Субъект_РФ,
		TotalScore Результат,
                    stuff
                    (
                              (
                                        Select
                                                  ', ' + convert(Varchar(max), Callsign)
                                        From @Res3 t2
                                        Where t1.Place = t2.Place
                                        For Xml PATH('') 
                              )
                              , 1, 1, ''
                    ) As Состав_команды
	From @Res3 t1
	Group By
		Place,
		Name,
		TotalScore
	    
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[VHF_EVSK_VIP]'
GO
ALTER Function dbo.VHF_EVSK_VIP
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    16.08.2017 14:19:00
-- <UpdateDate>    
-- <Parameters>
(
)

-- <Returns>
Returns @Result Table
(
SuperCategory Int,
Category Int,
Points Int,
KMS_Count Int
)

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>True<Checker>John<CopyDate>27.06.2013 02:02:05<Copier>John<Version>1<CopyOrder>
-- <CheckOut>False<CheckOutDate>16.08.2017 14:31<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	--+ Перечисления и константы
	--+ Вид результатов
	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
    --/
    --/

	Insert @Result
	Select
		SuperCategory,
		Category,
		cast(cast(sum(Score) As Float) / 3.0 As Int) VIP,
		S.KMS_Count 
	From
	(
		Select
			SC.ID SuperCategory,
			Case When SC.UniteCategories = 1 Then Null Else isnull(Category.ID, Null) End Category,
			Confirmed.Score,
			row_number() Over 
			(
				Partition 
				By 
					SC.ID,
					Case When SC.UniteCategories = 1 Then '' Else LFC.Category End 
				Order By 
					Confirmed.Score Desc,
					Confirmed.QSOs
			) Place		
		From LogFiles
		Join LogFilesCategory_Select LFC
			On LFC.Call = LogFiles.Call
		Join Category
			On Category.Category = LFC.Category
		Join CategoryHierarchy CH
			On CH.Category = Category.ID
		Join SuperCategory SC
			On SC.ID = CH.SuperCategory
		Join LogFiles_BandResults As Confirmed
			On LogFiles.Call = Confirmed.Call
			And isnull(Confirmed.Category, '') = Case When SC.UniteCategories = 1 Then '' Else isnull(LFC.Category, '') End
		Where Confirmed.Band_ID = @ALL_BANDS
		And Confirmed.Mode_ID = SC.Mode
		And Confirmed.Type = @CONFIRMED_RESULTS
		And dbo.IsSuperCategorySatisfied(SC.ID, LogFiles.CountryID, LogFiles.OblID) = 1
		And dbo.IsSuperCategorySatisfied2(SC.ID, LogFiles.Call) = 1
	) T
	Outer apply
	(
		Select
			count(*) KMS_Count
		From Operators O
		Join Ranks R
			On O.Разряд = R.Rank
		Join LogFiles
			On LogFiles.Call = O.Радиостанция
		Join LogFilesCategory_Select LFC
			On LFC.Call = LogFiles.Call
		Join Category
			On Category.Category = LFC.Category
		Join CategoryHierarchy CH
			On CH.Category = Category.ID
		Join SuperCategory SC
			On SC.ID = CH.SuperCategory					
		Where R.ID <= 4
		And SC.ID = T.SuperCategory
		And Category.ID = T.Category
		And dbo.IsSuperCategorySatisfied(SC.ID, LogFiles.CountryID, LogFiles.OblID) = 1
	) S
	
	Where Place In (2, 3, 4)
	Group By 
		SuperCategory,
		Category,
		S.KMS_Count
	
    Return

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[VHF_EVSK_Points]'
GO
ALTER Function dbo.VHF_EVSK_Points
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    26.06.2013 13:45:11
-- <UpdateDate>    
-- <Parameters>
(
)

-- <Returns>
Returns @Result Table
(
SuperCategory Int,
Category Int,
Call Varchar(20),
EVSK_Points Int,
EVSK Varchar(20),
VIP Int
)

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>True<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

	--+ Перечисления и константы
	--+ Вид результатов
	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
    --/
    --/

	Declare @ContestName Varchar(255) = cast(dbo.Settings_Get('ContestName') As Varchar(255))	

	If @ContestName  = 'SPB-OPEN-VHF'
		Insert @Result
		Select
			cast(Null As Int) SuperCategory,
			cast(Null As Int) Category,
			Call,
			cast(round(sum(Points), 0) As Int) EVSK_Points,
			Case 
				When cast(round(sum(Points), 0) As Int) >= 6000
					Then 'КМС'
				When cast(round(sum(Points), 0) As Int) >= 3000
					Then '1'
				When cast(round(sum(Points), 0) As Int) >= 2000
					Then '2'
				When cast(round(sum(Points), 0) As Int) >= 1000
					Then '3'
				Else
					Null
			End EVSK,
			cast(Null As Int) VIP
		From
		(
			Select
				QSOs.Call1 Call,
				Pts.Points / Bands.BandCoef * EVSKCoef Points,
				row_number() Over (Partition By QSOs.Call1 Order By Pts.Points / Bands.BandCoef * Bands.EVSKCoef Desc) As Rank
			From QSOs
			Join LogFiles
				On LogFiles.Call = QSOs.Call1
			Join QSOs_Points Pts
				On Pts.ID_QSO = QSOs.ID
			Join Bands
				On Bands.Band = QSOs.Band
			Where PTS.ResType = 2 -- Confirmed
			And LogFiles.CountryID In ('UA', 'UA9', 'UA2')
		) As EVSK_Points
		Where Rank <= 10
		Group By Call	
	Else If @ContestName  = 'LO-OPEN-VHF'
		Insert @Result
		Select
			SuperCategory,
			Category,
			Call,
			EVSK_Points,
			Case 
				When EVSK_Points >= 80
					Then 'КМС'
				When EVSK_Points >= 60
					Then '1'
				When EVSK_Points >= 50
					Then '2'
				When EVSK_Points >= 40
					Then '3'
				Else
					Null
			End EVSK,
			VIP					
		From 
		(
			Select
				SC.ID SuperCategory,
				Case When SC.UniteCategories = 1 Then Null Else isnull(Category.ID, Null) End Category,
				LogFiles.Call,
				Case When cast(VIP.Points As Float) = 0 Then 0 Else round(cast(Confirmed.Score As Float) / cast(VIP.Points As Float) * 100.0, 0) End EVSK_Points,
				VIP.Points VIP
			From LogFiles
			Join LogFilesCategory_Select LFC
				On LFC.Call = LogFiles.Call
			Join Category
				On Category.Category = LFC.Category
			Join CategoryHierarchy CH
				On CH.Category = Category.ID
			Join SuperCategory SC
				On SC.ID = CH.SuperCategory
			Join LogFiles_BandResults As Confirmed
				On LogFiles.Call = Confirmed.Call
				And isnull(Confirmed.Category, '') = Case When SC.UniteCategories = 1 Then '' Else isnull(LFC.Category, '') End
			Join dbo.VHF_EVSK_VIP() VIP
				On isnull(VIP.Category, 0) = isnull(Category.ID, 0)
				And VIP.SuperCategory = SC.ID
			Where Confirmed.Band_ID = @ALL_BANDS
			And Confirmed.Mode_ID = SC.Mode
			And Confirmed.Type = @CONFIRMED_RESULTS
			And dbo.IsSuperCategorySatisfied(SC.ID, LogFiles.CountryID, LogFiles.OblID) = 1
			And dbo.IsSuperCategorySatisfied2(SC.ID, LogFiles.Call) = 1
		) T
	
	
    Return

End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ЗачетПоРегионам]'
GO
ALTER Procedure dbo.ЗачетПоРегионам
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>SkladNetAppServ<CopyDate>22.05.2018 14:46<Copier>SkladNetAppServ<Version>6<CopyOrder>
-- <CheckOut>False<CheckOutDate>22.05.2018 14:16<CheckOutAuthor>SkladNetAppServ
--<EndHeader>---------------------------------------------------------------------------------------
As
Begin

	Set Nocount On
		
	Exec ЗачетПоРегионам_UA1DZ 5
	Exec ЗачетПоРегионам_UA1DZ 6
	Exec ЗачетПоРегионам_UA1DZ 8
				
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[LogFiles_BandSummary]'
GO
ALTER View dbo.LogFiles_BandSummary
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       
-- <CreateDate>    
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>Кузнецов Евгений<CopyDate><Copier><Version><CopyOrder>
-- <CheckOut>False<CheckOutDate>06.06.2018 18:39<CheckOutAuthor>Кузнецов Евгений
--<EndHeader>---------------------------------------------------------------------------------------
As

Select
	Case 
		When BRConfirmed.Category Like '%MULTI-OP%'
			Then 'MULTI-OP'
		Else
			'SINGLE-OP'
	End Category,	
	row_number() Over 
	(
		Partition 
		By 
			Case 
				When BRConfirmed.Category Like '%MULTI-OP%'
					Then 'MULTI-OP'
				Else
					'SINGLE-OP'
			End,	
			Bands.ID 
		Order By BRConfirmed.Points Desc
	) As Place,		
	BRClaimed.Call,
	Bands.Band,
	BRClaimed.QSOs ClaimedQSOs,
	BRClaimed.Points ClaimedPoints,
	MC1Claimed.Mult ClaimedMult,
	BRConfirmed.QSOs ConfirmedQSOs, 
	BRConfirmed.Points ConfirmedPoints,
	MC1Confirmed.Mult ConfirmedMult
From 
(
	Select Distinct
		Call,
		Category
	From LogFilesCategory 
) LFC
Join LogFiles_BandResults BRClaimed
	On BRClaimed.Call = LFC.Call
	And BRClaimed.Category = LFC.Category
left Join LogFiles_BandResults BRConfirmed
	On BRConfirmed.Call = LFC.Call
	And BRConfirmed.Category = LFC.Category
	And BRClaimed.Band_ID = BRConfirmed.Band_ID
Join Bands
	On Bands.ID = BRClaimed.Band_ID
left Join LogFiles_BandResults_Mults MC1Claimed
	On MC1Claimed.ID_Parent = BRClaimed.ID
	And MC1Claimed.Type = 1 -- @MULT_DXCC
	And MC1Claimed.Mode_ID = 0 -- @ALL_MODES
left Join LogFiles_BandResults_Mults MC1Confirmed
	On MC1Confirmed.ID_Parent = BRConfirmed.ID
	And MC1Confirmed.Type = 1 -- @MULT_DXCC
	And MC1Confirmed.Mode_ID = 0 -- @ALL_MODES
Where BRClaimed.Type = 1
And BRClaimed.Mode_ID = 0
And BRConfirmed.Type = 2
And BRConfirmed.Mode_ID = 0
And Bands.Enabled = 1
And BRClaimed.QSOs > 0
/*
Order By 
	Category,
	Bands.ID,
	Place
*/

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SaveTextFile]'
GO
Create Procedure [dbo].[SaveTextFile]
@Text As Varchar(max),
@FileName As Varchar(1000)
As
Begin

	Set Nocount On
	
	Declare @Cmd As Varchar(100)
	
	Set @Cmd = 'echo ' +  @Text + ' > ' + @FileName
	
	Execute Master.dbo.xp_cmdshell  @Cmd
	
	Return
	
End
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[LogFiles_BandResults_Mults]'
GO
ALTER TABLE [dbo].[LogFiles_BandResults_Mults] WITH NOCHECK  ADD CONSTRAINT [LogFiles_BandResults_Mult_FK02] FOREIGN KEY ([Mode_ID]) REFERENCES [dbo].[Modes] ([ID])
ALTER TABLE [dbo].[LogFiles_BandResults_Mults] WITH NOCHECK  ADD CONSTRAINT [LogFiles_BandResults_Mult_FK03] FOREIGN KEY ([Type]) REFERENCES [dbo].[Mults_Type] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[QSOs_Mults]'
GO
ALTER TABLE [dbo].[QSOs_Mults] WITH NOCHECK  ADD CONSTRAINT [FK_QSOs_Points_Mults_Mults_Type] FOREIGN KEY ([MultType]) REFERENCES [dbo].[Mults_Type] ([ID])
ALTER TABLE [dbo].[QSOs_Mults] WITH NOCHECK  ADD CONSTRAINT [FK_QSOs_Mults_Results_Type] FOREIGN KEY ([ResType]) REFERENCES [dbo].[Results_Type] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[QSOs_Points]'
GO
ALTER TABLE [dbo].[QSOs_Points] WITH NOCHECK  ADD CONSTRAINT [FK_QSOs_Points_Results_Type] FOREIGN KEY ([ResType]) REFERENCES [dbo].[Results_Type] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO