private bool getListSignalResponse(DataTable tableListSignals)
{
bool bRes = true;
SIGNAL sgnl;
string[] description;
string name_main = string.Empty
, name_detail = string.Empty;
foreach (DataRow r in tableListSignals.Rows) {
try {
switch (_key._current_type) {
case CONN_SETT_TYPE.DATA_AISKUE:
sgnl = new SIGNAL() {
id = (int)r[@"ID"]
, kks_code = AIISKUE_KKSCODE.ToKKSCode((int)r[@"USPD_CODE"], (int)r[@"CODE"])
, name_shr = string.Format(@"{0}", (string)r[@"NAME"])
, name = string.Format(@"{0}", (string)r[@"NAME"])
};
break;
case CONN_SETT_TYPE.DATA_SOTIASSO:
description = ((string)r[@"description"]).Split(new char[] { '.' });
name_main = description[1].Split(' ')[1];
name_detail = description[1].Split(' ')[description[1].Split(' ').Length - 1];
sgnl = new SIGNAL() {
id = (int)(decimal)r[@"id"]
, kks_code = string.Format(@"{0}", (string)r[@"name"])
, name_shr = string.Format(@"{0} {1}", name_main, name_detail.Equals(name_main) == false ? name_detail : string.Empty)
, name = string.Format(@"{0}", (string)r[@"description"])
};
break;
default:
sgnl = new SIGNAL();
break;
}
} catch (Exception e) {
bRes = false;
sgnl = new SIGNAL();
Logging.Logg().Exception(e, string.Format(@"::getListSignalResponse () - ..."), Logging.INDEX_MESSAGE.NOT_SET);
}
if (sgnl.id > 0)
_signals.Add(sgnl);
else
; // Logging.Logg().Error(string.Format(@""), Logging.INDEX_MESSAGE.NOT_SET)
}
return bRes;
}
private string getValuesRequest()
{
string strRes = string.Empty;
string db_table = string.Empty
, kks_code = string.Empty;
AIISKUE_KKSCODE.SENSOR sensor;
int parnumber = -1
, i_agregate = -1
, UTC_OFFSET = -1;
DateTime sql_datetime_start = DateTime.MinValue
, sql_datetime_end = DateTime.MinValue;
switch (_key._current_type) {
case CONN_SETT_TYPE.DATA_AISKUE:
sensor = AIISKUE_KKSCODE.ToSensor(_key._kks_code);
parnumber = 12;
sql_datetime_start = UserDate.Value.Date;
sql_datetime_end = sql_datetime_start.AddDays(1F);
UTC_OFFSET = 0;
strRes = string.Format("SELECT [OBJECT], [ITEM], [DATA_DATE], [VALUE0]" +
" FROM [DATA]" +
" WHERE" +
" PARNUMBER={2}" +
" AND [OBJECT]={0}" +
" AND [ITEM]={1}" +
" AND [DATA_DATE] > '{3:yyyyMMdd HH:mm:ss}' AND [DATA_DATE] <= '{4:yyyyMMdd HH:mm:ss}'" +
" ORDER BY [DATA_DATE]"
, sensor.id_object, sensor.num_item, parnumber, sql_datetime_start, sql_datetime_end);
break;
case CONN_SETT_TYPE.DATA_SOTIASSO:
db_table = "states_real_his_0";
kks_code = _key._kks_code;
sql_datetime_start = UserDate.Value.Date.AddHours(-1 * UserDate.UTC_OFFSET);
sql_datetime_end = sql_datetime_start.AddDays(1F);
i_agregate = 30;
UTC_OFFSET = 0;
// [last_changed_at] переименовывается в [DATA_DATE], [Value] -> [VALUE0] для однообразия при обработке результатов (по аналогии с АИИСКУЭ)
strRes = string.Format("SELECT [id], DATEADD(MINUTE, {5}, dateadd(MINUTE, (datediff(MINUTE, '{2:yyyyMMdd HH:mm:ss}', [last_changed_at]) / {4}) * {4}, '{2:yyyyMMdd HH:mm:ss}')) as [last_changed_at]" + // last_changed_at -> DATA_DATE
", AVG([Value]) * 1000 AS [Value]" + // Value -> VALUE0
", COUNT(*)" +
" FROM (" +
"SELECT [id], dateadd(SECOND, (datediff(SECOND, '{2:yyyyMMdd HH:mm:ss}', [last_changed_at]) / 60) * 60, '{2:yyyyMMdd HH:mm:ss}') as [last_changed_at]" +
", CONVERT(DECIMAL(10, 6), (SUM(CONVERT(DECIMAL(10, 6), [Value]) * [tmdelta]) / SUM([tmdelta]))) AS [VALUE]" +
", COUNT(*) as [COUNT]" +
" FROM [dbo].[{0}]" +
" WHERE ID IN (" +
"SELECT [ID]" +
" FROM [dbo].[reals_v]" +
" WHERE [NAME] LIKE '%{1}%'" +
")" +
" AND [last_changed_at] >= '{2:yyyyMMdd HH:mm:ss}' AND [last_changed_at] < '{3:yyyyMMdd HH:mm:ss}'" +
" GROUP BY dateadd(SECOND, (datediff(SECOND, '{2:yyyyMMdd HH:mm:ss}', [last_changed_at]) / 60) * 60, '{2:yyyyMMdd HH:mm:ss}'), id" +
") res" +
" GROUP BY dateadd(MINUTE, (datediff(MINUTE, '{2:yyyyMMdd HH:mm:ss}', [last_changed_at]) / {4}) * {4}, '{2:yyyyMMdd HH:mm:ss}'), id" +
" ORDER BY [last_changed_at], [id]" // last_changed_at -> DATA_DATE
, db_table, kks_code, sql_datetime_start, sql_datetime_end, i_agregate, (UTC_OFFSET * 60 + 30));
break;
default:
break;
}
return strRes;
}
