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