DECLARE MACHINE_ID UNIQUEIDENTIFIER SET MACHINE_ID 91F8C79A-B49C-479B-

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
DECLARE @MACHINE_ID UNIQUEIDENTIFIER
SET @MACHINE_ID = '91F8C79A-B49C-479B-A432-9118009F5E8A'
DECLARE @MACHINE_MODEL_SETTING_ID UNIQUEIDENTIFIER
SELECT @MACHINE_MODEL_SETTING_ID = MACHINE_MODEL_SETTING_ID FROM TMACHINES WHERE MACHINE_ID = @MACHINE_ID
DECLARE @VIBRO_CHANNELS INT
DECLARE @TACHO_CHANNELS INT
DECLARE @REGIME_PARAMETERS INT
--СПИСОК ВЫБРОКАНАЛОВ, ТАХОКАНАЛОВ И РЕЖИМНЫХ ПАРАМЕТРОВ СДЕЛАН ЧЕРЕЗ ВРЕМЕННЫЕ ТАБЛИЦЫ ЧТОБЫ НЕ СТРОИТЬ
--ОДИН И ТОТ-ЖЕ КОРТЕЖ ДЛЯ КАЖДОГО СОБЫТИЯ !!!! ПО ОДНОМУ И ТОМУ ЖЕ АГРЕГАТУ !!!!
CREATE TABLE #TEMPTABLE_VIBRO_CHANNELS
(
POINT_ID UNIQUEIDENTIFIER,
CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
PARAMETER_SETTING_ID UNIQUEIDENTIFIER,
)
CREATE TABLE #TEMPTABLE_TACHO_CHANNELS
(
POINT_ID UNIQUEIDENTIFIER,
CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
PARAMETER_SETTING_ID UNIQUEIDENTIFIER,
)
CREATE TABLE #TEMPTABLE_REGIME_PARAMETERS
(
POINT_ID UNIQUEIDENTIFIER,
CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
PARAMETER_SETTING_ID UNIQUEIDENTIFIER,
)
INSERT INTO #TEMPTABLE_VIBRO_CHANNELS
SELECT P.POINT_ID, CS.CHANNEL_SETTING_ID, PS.PARAMETER_SETTING_ID
FROM TMACHINE_MODEL_SETTINGS MMS
INNER JOIN TELEMENT_MODEL_IN_MM_SETTINGS EMMS ON EMMS.MACHINE_MODEL_SETTING_ID = MMS.MACHINE_MODEL_SETTING_ID
INNER JOIN TPOINT_ELEMENT_MM_LINKS PEMML ON PEMML.ELEMENT_MODEL_IN_MM_SETTING_ID = EMMS.ELEMENT_MODEL_IN_MM_SETTING_ID
INNER JOIN TPOINT P ON P.POINT_ID = PEMML.POINT_ID
INNER JOIN TCHANNEL_SETTINGS CS ON CS.POINT_SETTING_ID = P.POINT_SETTING_ID
INNER JOIN TCHANNEL_PARAMETER_LINKS CPL ON CPL.CHANNEL_SETTING_ID = CS.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS PS ON PS.PARAMETER_SETTING_ID = CPL.PARAMETER_SETTING_ID AND PS.SIGNAL_TYPE_ID = 1
WHERE EMMS.MACHINE_MODEL_SETTING_ID = @MACHINE_MODEL_SETTING_ID
INSERT INTO #TEMPTABLE_TACHO_CHANNELS
SELECT P.POINT_ID, CS.CHANNEL_SETTING_ID, PS.PARAMETER_SETTING_ID
FROM TMACHINE_MODEL_SETTINGS MMS
INNER JOIN TPOINT_MM_SETTINGS_LINKS PMMS ON PMMS.MACHINE_MODEL_SETTING_ID = MMS.MACHINE_MODEL_SETTING_ID
INNER JOIN TPOINT P ON P.POINT_ID = PMMS.POINT_ID
INNER JOIN TCHANNEL_SETTINGS CS ON CS.POINT_SETTING_ID = P.POINT_SETTING_ID
INNER JOIN TCHANNEL_PARAMETER_LINKS CPL ON CPL.CHANNEL_SETTING_ID = CS.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS PS ON PS.PARAMETER_SETTING_ID = CPL.PARAMETER_SETTING_ID AND PS.SIGNAL_TYPE_ID = 5
WHERE MMS.MACHINE_MODEL_SETTING_ID = @MACHINE_MODEL_SETTING_ID
INSERT INTO #TEMPTABLE_REGIME_PARAMETERS
SELECT P.POINT_ID, CS.CHANNEL_SETTING_ID, PS.PARAMETER_SETTING_ID
FROM TMACHINE_MODEL_SETTINGS(NOLOCK) MS
INNER JOIN TPOINT_MM_SETTINGS_LINKS(NOLOCK) PMMS ON PMMS.MACHINE_MODEL_SETTING_ID = MS.MACHINE_MODEL_SETTING_ID
INNER JOIN TPOINT(NOLOCK) P ON P.POINT_ID = PMMS.POINT_ID
INNER JOIN TCHANNEL_SETTINGS(NOLOCK) CS ON CS.POINT_SETTING_ID = P.POINT_SETTING_ID
INNER JOIN TPARENT_CHANNEL_PARAMETER_LINKS(NOLOCK) CPL ON CPL.CHANNEL_SETTING_ID = CS.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS(NOLOCK) PS ON PS.PARAMETER_SETTING_ID = CPL.PARAMETER_SETTING_ID AND PS.SIGNAL_TYPE_ID = 6
WHERE
MS.MACHINE_MODEL_SETTING_ID = @MACHINE_MODEL_SETTING_ID
SELECT @VIBRO_CHANNELS = COUNT(*) FROM #TEMPTABLE_VIBRO_CHANNELS
SELECT @TACHO_CHANNELS = COUNT(*) FROM #TEMPTABLE_TACHO_CHANNELS
SELECT @REGIME_PARAMETERS = COUNT(*) FROM #TEMPTABLE_REGIME_PARAMETERS
SELECT
ME.MACHINE_EVENT_ID, ME.EVENT_TIME, ME.IS_BASIC, ME.STATE,
@VIBRO_CHANNELS AS VIBRO_CHANNELS,
@TACHO_CHANNELS AS TACHO_CHANNELS,
@REGIME_PARAMETERS AS REGIME_PARAMETERS,
(
SELECT COUNT(*) FROM TMEASUREMENTS M
WHERE M.MACHINE_EVENT_ID = ME.MACHINE_EVENT_ID
AND EXISTS
(
SELECT TT.POINT_ID FROM #TEMPTABLE_VIBRO_CHANNELS TT
WHERE TT.POINT_ID = M.POINT_ID AND TT.CHANNEL_SETTING_ID = M.CHANNEL_SETTING_ID AND TT.PARAMETER_SETTING_ID = M.PARAMETER_SETTING_ID
)
) AS VIBRO_CHANNELS_MEAS,
(
SELECT COUNT(*) FROM TMEASUREMENTS M
WHERE M.MACHINE_EVENT_ID = ME.MACHINE_EVENT_ID
AND EXISTS
(
SELECT TT.POINT_ID FROM #TEMPTABLE_TACHO_CHANNELS TT
WHERE TT.POINT_ID = M.POINT_ID AND TT.CHANNEL_SETTING_ID = M.CHANNEL_SETTING_ID AND TT.PARAMETER_SETTING_ID = M.PARAMETER_SETTING_ID
)
) AS TACHO_CHANNELS_MEAS,
(
SELECT COUNT(*) FROM TMEASUREMENTS M
WHERE M.MACHINE_EVENT_ID = ME.MACHINE_EVENT_ID
AND EXISTS
(
SELECT TT.POINT_ID FROM #TEMPTABLE_REGIME_PARAMETERS TT
WHERE TT.POINT_ID = M.POINT_ID AND TT.CHANNEL_SETTING_ID = M.CHANNEL_SETTING_ID AND TT.PARAMETER_SETTING_ID = M.PARAMETER_SETTING_ID
)
) AS REGIME_PARAMETERS_MEAS
FROM
TMACHINE_EVENTS ME
WHERE ME.MACHINE_ID = @MACHINE_ID
DROP TABLE #TEMPTABLE_VIBRO_CHANNELS
DROP TABLE #TEMPTABLE_TACHO_CHANNELS
DROP TABLE #TEMPTABLE_REGIME_PARAMETERS