using System using System Collections Generic using System Data using

  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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
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;
}
}
}