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

DBProject/UpdateScripts/Script005.sql
254 строки4 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
Create Procedure dbo.MakeResultNew
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       John
-- <CreateDate>    29.08.2019 13:19:00
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

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

	Set Nocount On
	
	Delete QSOs 
	Where Call2 = 'ERROR'
	
	Update QSOs
	Set ErrType = Null,
		ErrPar = Null,
		ID2 = Null
	
	-- Delete_Invalid
	
	Print 1
	
	Delete QSOs
	Where QSOs.Call1 = QSOs.Call2
	
	-- Create_CrossCheck
	
	Print 2
	
	Insert Into CrossCheck
	Select
		QSOs.ID
		, QSOs.Call1
		, QSOs.Call2
		, QSOs.[Band]
		, QSOs.Mode
		, QSOs.Time
		, QSOs.NrSent
		, QSOs.NrRcvd
		, QSOs_1.ID As ID2
	From QSOs
	Join QSOs As QSOs_1
		On QSOs.NrSent = QSOs_1.NrRcvd
		And QSOs.Band = QSOs_1.Band
		And QSOs.Call1 = QSOs_1.Call2
		And QSOs.NrRcvd = QSOs_1.NrSent
		And QSOs.Mode = QSOs_1.Mode
		And QSOs.Call2 = QSOs_1.Call1
	Where dbo.CheckTime(QSOs.Time, QSOs_1.Time) = 0		
	And month(QSOs.Date) = month(QSOs_1.Date)
			
	-- Exec CrossCheckSameQSOs_Delete
				
	Print 3
	
	-- Exec Create_NILs
	
	Print 4
	
	Insert NILs
	Select
		QSOs.ID,
		left(QSOs.Call1, 10),
		left(QSOs.Call2, 10),
		QSOs.Band,
		QSOs.Mode,
		QSOs.Time,
		QSOs.NrSent,
		QSOs.NrRcvd,
		month(QSOs.Date)
   	From QSOs
	left Join CrossCheck On QSOs.ID = CrossCheck.ID
	Where Crosscheck.ID Is Null
		
	-- Exec Create_CheckAll_Table
	-- Exec Create_CheckAll_Table_Calls
	
	Print 5
	
	Insert CheckAll_Table
	Select
		Check_All.*
	From Check_All
		
	-- Exec _Create_NILs2
	
	Print 6
	
	Insert NILs2
	Select
		NILs.*
	From NILs
	left Join CheckAll_Table
		On NILs.ID = CheckAll_Table.ID
	Where CheckAll_Table.ID Is Null
	
	-- Exec _Create_CheckAll_Table
	
	Print 7
	
	Insert [_CheckAll_Table]
	Select *
	From [_Check_ALL]
	
	-- Exec __Create__CheckAll_Table
	
	Print 8
	
	Insert [__CheckAll_Table]
	(
		Call1,
		Call2,
		Band1,
		Mode1,
		Time1,
		NrSent1,
		NrRcvd1,
		ErrPar,
		ErrType,
		ID,
		ID2,
		[Identity],
		Identity2
	)
	Select
		*
	From [__Check_All]
		
	-- Exec _Update_CheckAll_Table
	
	Print 9
	
	Insert CheckAll_Table
	(
		Call1,
		Call2,
		Band1,
		Mode1,
		Time1,
		NrSent1,
		NrRcvd1,
		ErrPar,
		ErrType,
		ID,
		ID2,
		[Identity],
		Identity2
	)
	Select
		t.Call1,
		t.Call2,
		t.Band1,
		t.Mode1,
		t.Time1,
		t.NrSent1,
		t.NrRcvd1,
		t.ErrPar,
		t.ErrType,
		t.ID,
		t.ID2,
		t.[Identity],
		t.Identity2
	From _CheckAll_Table t
	
	Print 10
	
	Update QSOs
	Set ErrType = '+',
		ErrPar = '',
		ID2 = CrossCheck.ID2
	From QSOs
	Join CrossCheck On CrossCheck.ID = QSOs.ID
	left Join LogFiles On LogFiles.Call = CrossCheck.Call2
	
	Update QSOs
	Set ErrType = isnull(CT.ErrType, ''),
		ErrPar = isnull(CT.ErrPar, ''),
		ID2 = CT.ID2
	From QSOs
	Join CheckAll_Table CT On CT.ID = QSOs.ID
	left Join LogFiles On LogFiles.Call = CT.Call2
	
	Update QSOs
	Set IsLogRcvd = Case When LogFiles.Call Is Null Then 0 Else 1 End
	From QSOs
	left Join LogFiles On LogFiles.Call = QSOs.Call2
	
	
	Update QSOs_1
	Set QSOs_1.ErrType = '+',
		QSOs_1.ErrPar = QSOs_SystemErrors.Comment
	From QSOs_SystemErrors
	Join QSOs
		On QSOs_SystemErrors.Call1 = QSOs.Call1
		And QSOs_SystemErrors.[Time] = QSOs.[Time]
		And QSOs_SystemErrors.Call2 = QSOs.Call2
	Join QSOs As QSOs_1
		On QSOs.ID2 = QSOs_1.ID 		
		
	Update QSOs
	Set ErrType = 'DSQ',
		ErrPar = QSOs_DSQ.Comment
	From QSOs
	Join QSOs_DSQ
		On QSOs_DSQ.Call2 = QSOs.Call2
		And QSOs_DSQ.[Time] = QSOs.[Time]
		And QSOs_DSQ.Call1 = QSOs.Call1
				
	--+ Опечатки
	Update QSOs_1
	Set QSOs_1.ErrPar = QSOs.ErrPar + ' ' + 'dig.0/let.O',
		QSOs_1.ErrType = '+'
	From [КОНТРОЛЬ Опечатки] KON
	Join QSOs
		On KON.Call1 = QSOs.Call1
		And KON.Call2 = QSOs.Call2
		And KON.Band = QSOs.Band
		And KON.Mode = QSOs.Mode
		And KON.[Time] = QSOs.[Time]
	Join QSOs As QSOs_1
		On QSOs.ID2 = QSOs_1.ID 
	Where QSOs.ErrType = 'S'
	Or QSOs.ErrType = 'R'
		
	Update QSOs
	Set QSOs.ErrType = '+',
		QSOs.ErrPar = QSOs.ErrPar + ' ' + 'dig.0/let.O'
	From [КОНТРОЛЬ Опечатки] KON
	Join QSOs
		On KON.Call1 = QSOs.Call1
		And KON.Call2 = QSOs.Call2
		And KON.Band = QSOs.Band
		And KON.Mode = QSOs.Mode
		And KON.[Time] = QSOs.[Time]
	Where QSOs.ErrType = 'S'
	Or QSOs.ErrType = 'R'
	--/
	
	Return

End