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