using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Technekon.DomainObjects;
using Technekon.VDECommon;
using Technekon.VDEConnectivity;
namespace Technekon.VDESrv
{
/// <summary>Операция получения пропущенных параматров при обследовании</summary>
class GetInspectionMissingDataOperation : BaseOperation
{
/// <summary>Идентификатор обследования по которому получаем пропущенные параметры</summary>
private readonly Guid m_EventID;
/// <summary>Справочник направлений</summary>
private readonly RefTableDirections m_RefTableDirections;
/// <summary>Список пропущенных обследований</summary>
private readonly InspectionMissingDataDO m_Result = new InspectionMissingDataDO();
#region SQL
private const String m_SetEventBasic = @"DECLARE @MACHINE_ID UNIQUEIDENTIFIER
--DECLARE @EVENT_ID UNIQUEIDENTIFIER
--SET @EVENT_ID = '5E807B65-C110-4A56-A52E-0003BA6DD05A'
SELECT @MACHINE_ID = MACHINE_ID FROM TMACHINE_EVENTS WHERE MACHINE_EVENT_ID = @EVENT_ID
DECLARE @MACHINE_MODEL_SETTING_ID UNIQUEIDENTIFIER
SELECT @MACHINE_MODEL_SETTING_ID = MACHINE_MODEL_SETTING_ID FROM TMACHINES WHERE MACHINE_ID = @MACHINE_ID
--select @MACHINE_MODEL_SETTING_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,
)
CREATE TABLE #TEMPTABLE_MEASUREMENTS
(
POINT_ID UNIQUEIDENTIFIER,
CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
PARAMETER_SETTING_ID UNIQUEIDENTIFIER,
)
INSERT INTO #TEMPTABLE_MEASUREMENTS
SELECT POINT_ID, CHANNEL_SETTING_ID, PARAMETER_SETTING_ID
FROM TMEASUREMENTS
WHERE Machine_event_id = @EVENT_ID
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
PNTS.NAME, CS.DIRECTION_TYPE_ID, P.POINT_NUMBER, PNTS.SUFFIX,
T.POINT_ID, T.CHANNEL_SETTING_ID, T.PARAMETER_SETTING_ID
FROM #TEMPTABLE_VIBRO_CHANNELS T
INNER JOIN TPOINT P ON P.POINT_ID = T.POINT_ID
INNER JOIN TPOINT_SETTINGS PNTS ON PNTS.POINT_SETTING_ID = P.POINT_SETTING_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 = T.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS PS ON PS.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
WHERE NOT EXISTS
(
SELECT * FROM #TEMPTABLE_MEASUREMENTS M
WHERE
M.POINT_ID = T.POINT_ID
AND M.CHANNEL_SETTING_ID = T.CHANNEL_SETTING_ID
AND M.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
)
ORDER BY P.POINT_NUMBER, PNTS.ORDER_ID, PS.ORDER_ID
SELECT
PNTS.NAME, CS.DIRECTION_TYPE_ID, P.POINT_NUMBER, PNTS.SUFFIX,
T.POINT_ID, T.CHANNEL_SETTING_ID, T.PARAMETER_SETTING_ID
FROM #TEMPTABLE_TACHO_CHANNELS T
INNER JOIN TPOINT P ON P.POINT_ID = T.POINT_ID
INNER JOIN TPOINT_SETTINGS PNTS ON PNTS.POINT_SETTING_ID = P.POINT_SETTING_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 = T.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS PS ON PS.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
WHERE NOT EXISTS
(
SELECT * FROM #TEMPTABLE_MEASUREMENTS M
WHERE
M.POINT_ID = T.POINT_ID
AND M.CHANNEL_SETTING_ID = T.CHANNEL_SETTING_ID
AND M.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
)
ORDER BY P.POINT_NUMBER, PNTS.ORDER_ID, PS.ORDER_ID
SELECT
PNTS.NAME, CS.DIRECTION_TYPE_ID, P.POINT_NUMBER, PNTS.SUFFIX,
T.POINT_ID, T.CHANNEL_SETTING_ID, T.PARAMETER_SETTING_ID
FROM #TEMPTABLE_REGIME_PARAMETERS T
INNER JOIN TPOINT P ON P.POINT_ID = T.POINT_ID
INNER JOIN TPOINT_SETTINGS PNTS ON PNTS.POINT_SETTING_ID = P.POINT_SETTING_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 = T.CHANNEL_SETTING_ID
INNER JOIN TPARAMETER_SETTINGS PS ON PS.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
WHERE NOT EXISTS
(
SELECT * FROM #TEMPTABLE_MEASUREMENTS M
WHERE
M.POINT_ID = T.POINT_ID
AND M.CHANNEL_SETTING_ID = T.CHANNEL_SETTING_ID
AND M.PARAMETER_SETTING_ID = T.PARAMETER_SETTING_ID
)
ORDER BY P.POINT_NUMBER, PNTS.ORDER_ID, PS.ORDER_ID
SELECT EVENT_TIME FROM TMACHINE_EVENTS WHERE MACHINE_EVENT_ID = @EVENT_ID
DROP TABLE #TEMPTABLE_VIBRO_CHANNELS
DROP TABLE #TEMPTABLE_TACHO_CHANNELS
DROP TABLE #TEMPTABLE_REGIME_PARAMETERS
DROP TABLE #TEMPTABLE_MEASUREMENTS";
#endregion
public GetInspectionMissingDataOperation(BaseOperation parentOperation, DataControlObject contextObject,
Guid eventID)
: base(parentOperation, contextObject)
{
m_RefTableDirections = contextObject.ReferenseTable.DirectionTypes;
m_EventID = eventID;
m_OperationName = ResourceLoaderStr.GetString("OpGetInspectionsListOperation");
m_OperationAction = OperationActionType.DataRead;
m_CurrentHandler.RequestedBlockState = BlockState.BlockForRead;
m_CurrentHandler.Rights = ReqRights.R;
m_UseSQL = true;
}
public override void DoExecute()
{
SqlCommand commandGetInspectionsMissedData = null;
try
{
commandGetInspectionsMissedData = new SqlCommand(m_SetEventBasic, m_SQLConnection);
commandGetInspectionsMissedData.CommandTimeout = 0;
commandGetInspectionsMissedData.Parameters.AddWithValue("@EVENT_ID", m_EventID);
SqlDataAdapter adapterMissedData = new SqlDataAdapter(commandGetInspectionsMissedData);
DataSet dataSetMissedData = new DataSet();
adapterMissedData.Fill(dataSetMissedData);
//Пропущенные виброканалы
FillData(dataSetMissedData.Tables[0], m_Result.VibroChannels, SignalTypes.SignalTypeWave);
//Пропущенные тахоканалы
FillData(dataSetMissedData.Tables[1], m_Result.TachoChannels, SignalTypes.SignalTypeRPM);
//Пропущенные режимные параметры
FillData(dataSetMissedData.Tables[2], m_Result.RegimeParameters, SignalTypes.SignalTypeRegimeParameter);
m_Result.Date = Convert.ToDateTime(dataSetMissedData.Tables[3].Rows[0]["EVENT_TIME"]);
}
catch (Exception ex)
{
VDException.Report(ex, m_CurrentHandler.CurrentClientHandler.ClientName,
m_CurrentHandler.OperationName);
throw;
}
finally
{
if (commandGetInspectionsMissedData != null)
{
commandGetInspectionsMissedData.Dispose();
}
}
}
/// <summary>Заполнение объекта данныъ о пропущенныъ измерениях</summary>
protected void FillData(DataTable table, List<InspectionParameter> parametersList, SignalTypes signalType)
{
parametersList.Clear();
foreach (DataRow row in table.Rows)
{
String pointName = row["NAME"].ToString();
DirectionTypes directionTypes = (DirectionTypes)row["DIRECTION_TYPE_ID"];
Int32 pointNumber = Convert.ToInt32(row["POINT_NUMBER"]);
String suffix = row["SUFFIX"].ToString();
String directionName = m_RefTableDirections.GetDirection(directionTypes).NameSymbol;
String name;
switch (signalType)
{
case SignalTypes.SignalTypeRegimeParameter:
name = pointName;
break;
case SignalTypes.SignalTypeRPM:
name = CommonRoutines.MakeParamName(pointName, ValueTypes.ValueTypeUndefined,
signalType, pointNumber, "", suffix);
break;
case SignalTypes.SignalTypeWave:
name = CommonRoutines.MakeParamName(pointName, ValueTypes.ValueTypeUndefined,
signalType, pointNumber, directionName, suffix);
break;
default:
name = CommonRoutines.MakeParamName(pointName, ValueTypes.ValueTypeUndefined,
signalType, pointNumber, directionName, suffix);
break;
}
InspectionParameter point = new InspectionParameter();
point.Name = name;
point.PointID = new Guid(row["POINT_ID"].ToString());
point.ChannelSettingsID = new Guid(row["CHANNEL_SETTING_ID"].ToString());
point.ParameterSettingsID = new Guid(row["PARAMETER_SETTING_ID"].ToString());
parametersList.Add(point);
}
}
public override Boolean GetResult(out Object Result)
{
Result = m_Result;
return true;
}
}
}