796 lines
30 KiB
ObjectPascal
796 lines
30 KiB
ObjectPascal
unit uNILMManager;
|
|
|
|
{
|
|
NILM 장비 / 센서 설정 CRUD 매니저
|
|
- DB(PostgreSQL) 와 연동하여 장비·센서 설정을 관리
|
|
- JSON 내보내기 / 가져오기 지원
|
|
}
|
|
|
|
interface
|
|
|
|
uses
|
|
System.SysUtils, System.Classes, System.Generics.Collections,
|
|
System.JSON,
|
|
FireDAC.Comp.Client, FireDAC.Stan.Param,
|
|
Data.DB,
|
|
U_DM, uNILMTypes;
|
|
|
|
type
|
|
TNILMManager = class
|
|
private
|
|
function GetConnection: TFDConnection;
|
|
procedure ExecSQL(const ASQL: string; AParams: array of Variant);
|
|
function QuerySingleInt(const ASQL: string; AParams: array of Variant): Integer;
|
|
|
|
procedure LoadSensorsForDevice(ADeviceID: Integer; AList: TNILMSensorConfigList);
|
|
public
|
|
// ─── DDL ────────────────────────────────────────────────────────
|
|
/// DB 테이블이 없으면 자동 생성
|
|
procedure EnsureTables;
|
|
|
|
// ─── 장비 CRUD ──────────────────────────────────────────────────
|
|
/// DB에서 전체 장비 목록 로드
|
|
function LoadDevices: TList<TNILMDevice>;
|
|
/// device_id 로 단일 장비 로드
|
|
function LoadDevice(ADeviceID: Integer): TNILMDevice;
|
|
/// 장비 저장 (ID=0 이면 INSERT, 아니면 UPDATE)
|
|
function SaveDevice(var ADevice: TNILMDevice): Boolean;
|
|
/// 장비 삭제 (연관 센서도 함께 삭제)
|
|
function DeleteDevice(ADeviceID: Integer): Boolean;
|
|
/// 장비 device_id 중복 여부 확인
|
|
function DeviceIDExists(ADeviceID, AExcludeID: Integer): Boolean;
|
|
|
|
// ─── 센서 설정 CRUD ─────────────────────────────────────────────
|
|
/// 특정 장비의 센서 설정 저장 (UPSERT)
|
|
function SaveSensors(ADeviceID: Integer; AList: TNILMSensorConfigList): Boolean;
|
|
|
|
// ── JSON 내보내기 / 가져오기 ──────────────────────────────────
|
|
procedure ExportToJSON(const AFilePath: string; ADevices: TList<TNILMDevice>);
|
|
function ImportFromJSON(const AFilePath: string): TList<TNILMDevice>;
|
|
|
|
// ── 히스토리 데이터 저장 ─────────────────────────────────
|
|
/// MQTT 수신 데이터를 nilm_data에 저장
|
|
procedure SaveNILMData(ADeviceID, ASeq, APreSeq, ACommStatus: Integer;
|
|
const APayloadJSON: string; AData: TJSONObject; ASensorList: TNILMSensorConfigList);
|
|
/// 이벤트 로그 저장
|
|
procedure SaveEventLog(const AEventType: string; ADeviceID: Integer;
|
|
const AMessage, ADetail: string);
|
|
/// 히스토리 조회 (TFDQuery 반환 — 호출자가 Free 책임)
|
|
function QueryNILMHistory(ADeviceID: Integer;
|
|
ADateFrom, ADateTo: TDateTime;
|
|
ACommStatusFilter: Integer; // -1=전체, 0=정상, 255=오류
|
|
AMaxRows: Integer): TFDQuery;
|
|
/// 이벤트 로그 조회
|
|
function QueryEventLog(ADeviceID: Integer;
|
|
ADateFrom, ADateTo: TDateTime;
|
|
const AEventType: string; // ''=전체
|
|
AMaxRows: Integer): TFDQuery;
|
|
/// 장비목록 콤보용 (id, name)
|
|
function LoadDevicesSimple: TFDQuery;
|
|
end;
|
|
|
|
implementation
|
|
|
|
uses
|
|
System.IOUtils;
|
|
|
|
{ TNILMManager }
|
|
|
|
function TNILMManager.GetConnection: TFDConnection;
|
|
begin
|
|
Result := DM.fdConnNilm;
|
|
if not Result.Connected then
|
|
raise Exception.Create('DB(NILM)가 연결되지 않았습니다. DB 탭에서 먼저 연결해주세요.');
|
|
end;
|
|
|
|
procedure TNILMManager.ExecSQL(const ASQL: string; AParams: array of Variant);
|
|
var
|
|
Q: TFDQuery;
|
|
i: Integer;
|
|
begin
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text := ASQL;
|
|
for i := 0 to High(AParams) do
|
|
Q.Params[i].Value := AParams[i];
|
|
Q.ExecSQL;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.QuerySingleInt(const ASQL: string;
|
|
AParams: array of Variant): Integer;
|
|
var
|
|
Q: TFDQuery;
|
|
i: Integer;
|
|
begin
|
|
Result := 0;
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text := ASQL;
|
|
for i := 0 to High(AParams) do
|
|
Q.Params[i].Value := AParams[i];
|
|
Q.Open;
|
|
if not Q.IsEmpty then
|
|
Result := Q.Fields[0].AsInteger;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
procedure TNILMManager.EnsureTables;
|
|
const
|
|
DDL_DEVICE =
|
|
'CREATE TABLE IF NOT EXISTS nilm_device (' +
|
|
' id SERIAL PRIMARY KEY,' +
|
|
' device_id INTEGER NOT NULL UNIQUE,' +
|
|
' device_name VARCHAR(100) NOT NULL DEFAULT '''',' +
|
|
' location VARCHAR(200),' +
|
|
' phase_type VARCHAR(10) NOT NULL DEFAULT ''3PHASE'',' +
|
|
' is_active BOOLEAN NOT NULL DEFAULT TRUE,' +
|
|
' mqtt_topic VARCHAR(200),' +
|
|
' memo TEXT,' +
|
|
' created_at TIMESTAMP NOT NULL DEFAULT NOW(),' +
|
|
' updated_at TIMESTAMP NOT NULL DEFAULT NOW()' +
|
|
')';
|
|
|
|
DDL_SENSOR_CONFIG =
|
|
'CREATE TABLE IF NOT EXISTS nilm_sensor_config (' +
|
|
' id SERIAL PRIMARY KEY,' +
|
|
' device_id INTEGER NOT NULL,' +
|
|
' sensor_group VARCHAR(50) NOT NULL,' +
|
|
' field_key VARCHAR(50) NOT NULL,' +
|
|
' field_label VARCHAR(100),' +
|
|
' is_enabled BOOLEAN NOT NULL DEFAULT TRUE,' +
|
|
' display_order INTEGER NOT NULL DEFAULT 0,' +
|
|
' json_key VARCHAR(100),' +
|
|
' CONSTRAINT fk_nsc_device FOREIGN KEY (device_id) REFERENCES nilm_device(device_id) ON DELETE CASCADE,' +
|
|
' CONSTRAINT uq_nsc UNIQUE (device_id, field_key)' +
|
|
')';
|
|
|
|
DDL_DATA =
|
|
'CREATE TABLE IF NOT EXISTS nilm_data (' +
|
|
' id BIGSERIAL PRIMARY KEY,' +
|
|
' device_id INTEGER NOT NULL UNIQUE,' +
|
|
' seq INTEGER,' +
|
|
' pre_seq INTEGER,' +
|
|
' comm_status INTEGER DEFAULT 0,' +
|
|
' received_at TIMESTAMP NOT NULL DEFAULT NOW(),' +
|
|
' payload_json JSONB,' +
|
|
' pf_l1 NUMERIC(8,4), pk_l1 NUMERIC(10,4), v_l1 NUMERIC(8,2), i_l1 NUMERIC(10,4), w_l1 NUMERIC(10,2), var_l1 NUMERIC(10,2), va_l1 NUMERIC(10,2),' +
|
|
' pf_l2 NUMERIC(8,4), pk_l2 NUMERIC(10,4), v_l2 NUMERIC(8,2), i_l2 NUMERIC(10,4), w_l2 NUMERIC(10,2), var_l2 NUMERIC(10,2), va_l2 NUMERIC(10,2),' +
|
|
' pf_l3 NUMERIC(8,4), pk_l3 NUMERIC(10,4), v_l3 NUMERIC(8,2), i_l3 NUMERIC(10,4), w_l3 NUMERIC(10,2), var_l3 NUMERIC(10,2), va_l3 NUMERIC(10,2),' +
|
|
' temp_int NUMERIC(6,2), temp_ext NUMERIC(6,2),' +
|
|
' acc_x NUMERIC(8,4), acc_y NUMERIC(8,4), acc_z NUMERIC(8,4), vibration NUMERIC(8,4),' +
|
|
' gyro_x NUMERIC(8,4), gyro_y NUMERIC(8,4), gyro_z NUMERIC(8,4), tilt_angle NUMERIC(6,2)' +
|
|
'); ' +
|
|
'CREATE INDEX IF NOT EXISTS idx_nilm_data_device_received ON nilm_data (device_id, received_at DESC)';
|
|
|
|
DDL_EVENT_LOG =
|
|
'CREATE TABLE IF NOT EXISTS nilm_event_log (' +
|
|
' id BIGSERIAL PRIMARY KEY,' +
|
|
' event_time TIMESTAMP NOT NULL DEFAULT NOW(),' +
|
|
' event_type VARCHAR(20) NOT NULL,' +
|
|
' device_id INTEGER,' +
|
|
' message TEXT,' +
|
|
' detail TEXT' +
|
|
'); ' +
|
|
'CREATE INDEX IF NOT EXISTS idx_nilm_event_log_time ON nilm_event_log (event_time DESC); ' +
|
|
'CREATE INDEX IF NOT EXISTS idx_nilm_event_log_device ON nilm_event_log (device_id, event_time DESC)';
|
|
begin
|
|
ExecSQL(DDL_DEVICE, []);
|
|
ExecSQL(DDL_SENSOR_CONFIG, []);
|
|
ExecSQL(DDL_DATA, []);
|
|
ExecSQL(DDL_EVENT_LOG, []);
|
|
end;
|
|
|
|
{ ─── 장비 CRUD ─────────────────────────────────────────────────────── }
|
|
|
|
function TNILMManager.LoadDevices: TList<TNILMDevice>;
|
|
var
|
|
Q : TFDQuery;
|
|
Device : TNILMDevice;
|
|
begin
|
|
Result := TList<TNILMDevice>.Create;
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text :=
|
|
'SELECT id, device_id, device_name, location, phase_type, ' +
|
|
' is_active, mqtt_topic, memo, created_at, updated_at ' +
|
|
'FROM nilm_device ORDER BY device_id';
|
|
Q.Open;
|
|
while not Q.EOF do
|
|
begin
|
|
Device := TNILMDevice.CreateNew;
|
|
Device.ID := Q.FieldByName('id').AsInteger;
|
|
Device.DeviceID := Q.FieldByName('device_id').AsInteger;
|
|
Device.DeviceName := Q.FieldByName('device_name').AsString;
|
|
Device.Location := Q.FieldByName('location').AsString;
|
|
Device.PhaseType := Q.FieldByName('phase_type').AsString;
|
|
Device.IsActive := Q.FieldByName('is_active').AsBoolean;
|
|
Device.MqttTopic := Q.FieldByName('mqtt_topic').AsString;
|
|
Device.Memo := Q.FieldByName('memo').AsString;
|
|
Device.CreatedAt := Q.FieldByName('created_at').AsDateTime;
|
|
Device.UpdatedAt := Q.FieldByName('updated_at').AsDateTime;
|
|
|
|
LoadSensorsForDevice(Device.DeviceID, Device.SensorList);
|
|
|
|
Result.Add(Device);
|
|
Q.Next;
|
|
end;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.LoadDevice(ADeviceID: Integer): TNILMDevice;
|
|
var
|
|
Q: TFDQuery;
|
|
begin
|
|
Result := TNILMDevice.CreateNew;
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text :=
|
|
'SELECT id, device_id, device_name, location, phase_type, ' +
|
|
' is_active, mqtt_topic, memo, created_at, updated_at ' +
|
|
'FROM nilm_device WHERE device_id = :did';
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.Open;
|
|
if not Q.IsEmpty then
|
|
begin
|
|
Result.ID := Q.FieldByName('id').AsInteger;
|
|
Result.DeviceID := Q.FieldByName('device_id').AsInteger;
|
|
Result.DeviceName:= Q.FieldByName('device_name').AsString;
|
|
Result.Location := Q.FieldByName('location').AsString;
|
|
Result.PhaseType := Q.FieldByName('phase_type').AsString;
|
|
Result.IsActive := Q.FieldByName('is_active').AsBoolean;
|
|
Result.MqttTopic := Q.FieldByName('mqtt_topic').AsString;
|
|
Result.Memo := Q.FieldByName('memo').AsString;
|
|
Result.CreatedAt := Q.FieldByName('created_at').AsDateTime;
|
|
Result.UpdatedAt := Q.FieldByName('updated_at').AsDateTime;
|
|
LoadSensorsForDevice(Result.DeviceID, Result.SensorList);
|
|
end;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
procedure TNILMManager.LoadSensorsForDevice(ADeviceID: Integer;
|
|
AList: TNILMSensorConfigList);
|
|
var
|
|
Q : TFDQuery;
|
|
SC : TNILMSensorConfig;
|
|
begin
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text :=
|
|
'SELECT id, device_id, sensor_group, field_key, field_label, is_enabled, display_order, json_key ' +
|
|
'FROM nilm_sensor_config WHERE device_id = :did ORDER BY display_order';
|
|
{
|
|
'SELECT id, device_id, sensor_group, field_key, field_label, ' +
|
|
' is_enabled, display_order ' +
|
|
'FROM nilm_sensor_config WHERE device_id = :did ORDER BY display_order';
|
|
}
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.Open;
|
|
while not Q.EOF do
|
|
begin
|
|
SC.ID := Q.FieldByName('id').AsInteger;
|
|
SC.DeviceID := Q.FieldByName('device_id').AsInteger;
|
|
SC.SensorGroup := Q.FieldByName('sensor_group').AsString;
|
|
SC.FieldKey := Q.FieldByName('field_key').AsString;
|
|
SC.FieldLabel := Q.FieldByName('field_label').AsString;
|
|
SC.IsEnabled := Q.FieldByName('is_enabled').AsBoolean;
|
|
SC.DisplayOrder := Q.FieldByName('display_order').AsInteger;
|
|
SC.JsonKey := Q.FieldByName('json_key').AsString;
|
|
AList.Add(SC);
|
|
Q.Next;
|
|
end;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function SafeDBStr(const S: string): string;
|
|
begin
|
|
if S = '' then Result := '' else Result := StringReplace(S, #0, '', [rfReplaceAll]);
|
|
end;
|
|
|
|
function TNILMManager.SaveDevice(var ADevice: TNILMDevice): Boolean;
|
|
var
|
|
Q : TFDQuery;
|
|
IsInsert : Boolean;
|
|
begin
|
|
Result := False;
|
|
ADevice.MqttTopic := ADevice.BuildTopic;
|
|
ADevice.UpdatedAt := Now;
|
|
|
|
IsInsert := (ADevice.ID = 0);
|
|
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
if IsInsert then
|
|
begin
|
|
Q.SQL.Text :=
|
|
'INSERT INTO nilm_device ' +
|
|
' (device_id, device_name, location, phase_type, is_active, mqtt_topic, memo, created_at, updated_at) ' +
|
|
'VALUES (:did, :dname, :loc, :ptype, CAST(:active AS BOOLEAN), :topic, :memo, NOW(), NOW()) ' +
|
|
'RETURNING id';
|
|
Q.ParamByName('did').AsInteger := ADevice.DeviceID;
|
|
Q.ParamByName('dname').AsString := ADevice.DeviceName;
|
|
Q.ParamByName('loc').AsString := ADevice.Location;
|
|
Q.ParamByName('ptype').AsString := ADevice.PhaseType;
|
|
if ADevice.IsActive then
|
|
Q.ParamByName('active').AsString := 'true'
|
|
else
|
|
Q.ParamByName('active').AsString := 'false';
|
|
Q.ParamByName('topic').AsString := ADevice.MqttTopic;
|
|
Q.ParamByName('memo').AsString := ADevice.Memo;
|
|
Q.Open;
|
|
if not Q.IsEmpty then
|
|
ADevice.ID := Q.Fields[0].AsInteger;
|
|
end
|
|
else
|
|
begin
|
|
Q.SQL.Text :=
|
|
'UPDATE nilm_device SET ' +
|
|
' device_id = :did, device_name = :dname, location = :loc,' +
|
|
' phase_type = :ptype, is_active = CAST(:active AS BOOLEAN), mqtt_topic = :topic,' +
|
|
' memo = :memo, updated_at = NOW() ' +
|
|
'WHERE id = :id';
|
|
Q.ParamByName('did').AsInteger := ADevice.DeviceID;
|
|
Q.ParamByName('dname').AsString := ADevice.DeviceName;
|
|
Q.ParamByName('loc').AsString := ADevice.Location;
|
|
Q.ParamByName('ptype').AsString := ADevice.PhaseType;
|
|
if ADevice.IsActive then
|
|
Q.ParamByName('active').AsString := 'true'
|
|
else
|
|
Q.ParamByName('active').AsString := 'false';
|
|
Q.ParamByName('topic').AsString := ADevice.MqttTopic;
|
|
Q.ParamByName('memo').AsString := ADevice.Memo;
|
|
Q.ParamByName('id').AsInteger := ADevice.ID;
|
|
Q.ExecSQL;
|
|
end;
|
|
|
|
// 센서 설정도 저장
|
|
if Assigned(ADevice.SensorList) and (ADevice.SensorList.Count > 0) then
|
|
SaveSensors(ADevice.DeviceID, ADevice.SensorList);
|
|
|
|
Result := True;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.DeleteDevice(ADeviceID: Integer): Boolean;
|
|
var
|
|
Q: TFDQuery;
|
|
begin
|
|
Result := False;
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
// 센서 설정은 ON DELETE CASCADE 로 자동 삭제
|
|
Q.SQL.Text := 'DELETE FROM nilm_device WHERE device_id = :did';
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.ExecSQL;
|
|
Result := True;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.DeviceIDExists(ADeviceID, AExcludeID: Integer): Boolean;
|
|
var
|
|
Cnt: Integer;
|
|
begin
|
|
Cnt := QuerySingleInt(
|
|
'SELECT COUNT(*) FROM nilm_device WHERE device_id = :did AND id <> :eid',
|
|
[ADeviceID, AExcludeID]);
|
|
Result := (Cnt > 0);
|
|
end;
|
|
|
|
{ ─── 센서 설정 ─────────────────────────────────────────────────────── }
|
|
|
|
function TNILMManager.SaveSensors(ADeviceID: Integer;
|
|
AList: TNILMSensorConfigList): Boolean;
|
|
var
|
|
Q : TFDQuery;
|
|
SC : TNILMSensorConfig;
|
|
begin
|
|
Result := False;
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
for SC in AList do
|
|
begin
|
|
// UPSERT: 같은 (device_id, field_key) 가 있으면 UPDATE, 없으면 INSERT
|
|
Q.SQL.Text :=
|
|
{
|
|
'INSERT INTO nilm_sensor_config ' +
|
|
' (device_id, sensor_group, field_key, field_label, is_enabled, display_order) ' +
|
|
'VALUES (:did, :sgrp, :fkey, :flbl, CAST(:enabled AS BOOLEAN), :dord) ' +
|
|
'ON CONFLICT (device_id, field_key) DO UPDATE SET ' +
|
|
' sensor_group = EXCLUDED.sensor_group,' +
|
|
}
|
|
'INSERT INTO nilm_sensor_config ' +
|
|
' (device_id, sensor_group, field_key, field_label, is_enabled, display_order, json_key) ' +
|
|
'VALUES (:did, :sgrp, :fkey, :flbl, CAST(:enabled AS BOOLEAN), :dord, :jkey) ' +
|
|
'ON CONFLICT (device_id, field_key) DO UPDATE SET ' +
|
|
' sensor_group = EXCLUDED.sensor_group,' +
|
|
' field_label = EXCLUDED.field_label,' +
|
|
' is_enabled = EXCLUDED.is_enabled,' +
|
|
' display_order = EXCLUDED.display_order,' +
|
|
' json_key = EXCLUDED.json_key';
|
|
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.ParamByName('sgrp').AsString := SC.SensorGroup;
|
|
Q.ParamByName('fkey').AsString := SC.FieldKey;
|
|
Q.ParamByName('flbl').AsString := SC.FieldLabel;
|
|
if SC.IsEnabled then
|
|
Q.ParamByName('enabled').AsString := 'true'
|
|
else
|
|
Q.ParamByName('enabled').AsString := 'false';
|
|
Q.ParamByName('dord').AsInteger := SC.DisplayOrder;
|
|
Q.ParamByName('jkey').AsString := SC.JsonKey;
|
|
Q.ExecSQL;
|
|
end;
|
|
Result := True;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
{ ─── JSON 내보내기 / 가져오기 ───────────────────────────────────────── }
|
|
|
|
procedure TNILMManager.ExportToJSON(const AFilePath: string;
|
|
ADevices: TList<TNILMDevice>);
|
|
var
|
|
Root : TJSONObject;
|
|
DevArr : TJSONArray;
|
|
DevObj : TJSONObject;
|
|
SenArr : TJSONArray;
|
|
SenObj : TJSONObject;
|
|
Device : TNILMDevice;
|
|
SC : TNILMSensorConfig;
|
|
begin
|
|
Root := TJSONObject.Create;
|
|
try
|
|
DevArr := TJSONArray.Create;
|
|
for Device in ADevices do
|
|
begin
|
|
DevObj := TJSONObject.Create;
|
|
DevObj.AddPair('id', TJSONNumber.Create(Device.ID));
|
|
DevObj.AddPair('device_id', TJSONNumber.Create(Device.DeviceID));
|
|
DevObj.AddPair('device_name', Device.DeviceName);
|
|
DevObj.AddPair('location', Device.Location);
|
|
DevObj.AddPair('phase_type', Device.PhaseType);
|
|
DevObj.AddPair('is_active', TJSONBool.Create(Device.IsActive));
|
|
DevObj.AddPair('mqtt_topic', Device.MqttTopic);
|
|
DevObj.AddPair('memo', Device.Memo);
|
|
|
|
SenArr := TJSONArray.Create;
|
|
if Assigned(Device.SensorList) then
|
|
for SC in Device.SensorList do
|
|
begin
|
|
SenObj := TJSONObject.Create;
|
|
SenObj.AddPair('sensor_group', SC.SensorGroup);
|
|
SenObj.AddPair('field_key', SC.FieldKey);
|
|
SenObj.AddPair('field_label', SC.FieldLabel);
|
|
SenObj.AddPair('is_enabled', TJSONBool.Create(SC.IsEnabled));
|
|
SenObj.AddPair('display_order', TJSONNumber.Create(SC.DisplayOrder));
|
|
SenArr.AddElement(SenObj);
|
|
end;
|
|
DevObj.AddPair('sensors', SenArr);
|
|
DevArr.AddElement(DevObj);
|
|
end;
|
|
Root.AddPair('exported_at', FormatDateTime('yyyy-mm-dd hh:nn:ss', Now));
|
|
Root.AddPair('devices', DevArr);
|
|
|
|
TFile.WriteAllText(AFilePath, Root.Format, TEncoding.UTF8);
|
|
finally
|
|
Root.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.ImportFromJSON(const AFilePath: string): TList<TNILMDevice>;
|
|
var
|
|
Raw : string;
|
|
Root : TJSONObject;
|
|
DevArr : TJSONArray;
|
|
DevObj : TJSONObject;
|
|
SenArr : TJSONArray;
|
|
SenObj : TJSONObject;
|
|
Device : TNILMDevice;
|
|
SC : TNILMSensorConfig;
|
|
i, j : Integer;
|
|
begin
|
|
Result := TList<TNILMDevice>.Create;
|
|
Raw := TFile.ReadAllText(AFilePath, TEncoding.UTF8);
|
|
Root := TJSONObject.ParseJSONValue(Raw) as TJSONObject;
|
|
if Root = nil then Exit;
|
|
try
|
|
DevArr := Root.GetValue<TJSONArray>('devices');
|
|
if DevArr = nil then Exit;
|
|
|
|
for i := 0 to DevArr.Count - 1 do
|
|
begin
|
|
DevObj := DevArr.Items[i] as TJSONObject;
|
|
Device := TNILMDevice.CreateNew;
|
|
Device.ID := DevObj.GetValue<TJSONNumber>('id').AsInt;
|
|
Device.DeviceID := DevObj.GetValue<TJSONNumber>('device_id').AsInt;
|
|
Device.DeviceName := DevObj.GetValue<TJSONString>('device_name').Value;
|
|
Device.Location := DevObj.GetValue<TJSONString>('location').Value;
|
|
Device.PhaseType := DevObj.GetValue<TJSONString>('phase_type').Value;
|
|
Device.IsActive := DevObj.GetValue<TJSONBool>('is_active').AsBoolean;
|
|
Device.MqttTopic := DevObj.GetValue<TJSONString>('mqtt_topic').Value;
|
|
Device.Memo := DevObj.GetValue<TJSONString>('memo').Value;
|
|
|
|
SenArr := DevObj.GetValue<TJSONArray>('sensors');
|
|
if Assigned(SenArr) then
|
|
for j := 0 to SenArr.Count - 1 do
|
|
begin
|
|
SenObj := SenArr.Items[j] as TJSONObject;
|
|
SC.ID := 0;
|
|
SC.DeviceID := Device.DeviceID;
|
|
SC.SensorGroup := SenObj.GetValue<TJSONString>('sensor_group').Value;
|
|
SC.FieldKey := SenObj.GetValue<TJSONString>('field_key').Value;
|
|
SC.FieldLabel := SenObj.GetValue<TJSONString>('field_label').Value;
|
|
SC.IsEnabled := SenObj.GetValue<TJSONBool>('is_enabled').AsBoolean;
|
|
SC.DisplayOrder := SenObj.GetValue<TJSONNumber>('display_order').AsInt;
|
|
Device.SensorList.Add(SC);
|
|
end;
|
|
|
|
Result.Add(Device);
|
|
end;
|
|
finally
|
|
Root.Free;
|
|
end;
|
|
end;
|
|
|
|
{ ─── 히스토리 데이터 저장 ─────────────────────────────────────────────── }
|
|
|
|
procedure TNILMManager.SaveNILMData(ADeviceID, ASeq, APreSeq, ACommStatus: Integer;
|
|
const APayloadJSON: string; AData: TJSONObject; ASensorList: TNILMSensorConfigList);
|
|
|
|
function GetNum(const AKey: string): Double;
|
|
var JV: TJSONValue;
|
|
begin
|
|
Result := 0;
|
|
JV := AData.GetValue(AKey);
|
|
if Assigned(JV) then
|
|
Result := StrToFloatDef(JV.Value, 0);
|
|
end;
|
|
|
|
function GetJsonKey(const AFieldKey, ADefault: string): string;
|
|
var SC: TNILMSensorConfig;
|
|
begin
|
|
Result := ADefault;
|
|
if Assigned(ASensorList) then
|
|
for SC in ASensorList do
|
|
if SameText(SC.FieldKey, AFieldKey) then
|
|
begin
|
|
if SC.JsonKey <> '' then
|
|
Result := SC.JsonKey;
|
|
Exit;
|
|
end;
|
|
end;
|
|
|
|
var
|
|
Q: TFDQuery;
|
|
bSeqChanged: Boolean;
|
|
begin
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
|
|
bSeqChanged := True; // 기본적으로 변경되었다고 가정 (최초 데이터인 경우)
|
|
|
|
// 1. 기존 seq 확인 (로그 기록 여부 판단용)
|
|
Q.SQL.Text := 'SELECT seq FROM nilm_data WHERE device_id = :did';
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.Open;
|
|
if not Q.IsEmpty then
|
|
begin
|
|
if Q.FieldByName('seq').AsInteger = ASeq then
|
|
bSeqChanged := False; // 변경사항 없음
|
|
end;
|
|
Q.Close;
|
|
|
|
// 2. 최신 실시간 데이터 무조건 UPSERT (1행만 유지)
|
|
Q.SQL.Text :=
|
|
'INSERT INTO nilm_data' +
|
|
' (device_id,seq,pre_seq,comm_status,received_at,payload_json,' +
|
|
' pf_l1,pk_l1,v_l1,i_l1,w_l1,var_l1,va_l1,' +
|
|
' pf_l2,pk_l2,v_l2,i_l2,w_l2,var_l2,va_l2,' +
|
|
' pf_l3,pk_l3,v_l3,i_l3,w_l3,var_l3,va_l3,' +
|
|
' temp_int,temp_ext,acc_x,acc_y,acc_z,vibration,' +
|
|
' gyro_x,gyro_y,gyro_z,tilt_angle)' +
|
|
' VALUES' +
|
|
' (:did,:seq,:preseq,:comm,NOW(),CAST(:pjson AS JSONB),' +
|
|
' :pfl1,:pkl1,:vl1,:il1,:wl1,:varl1,:val1,' +
|
|
' :pfl2,:pkl2,:vl2,:il2,:wl2,:varl2,:val2,' +
|
|
' :pfl3,:pkl3,:vl3,:il3,:wl3,:varl3,:val3,' +
|
|
' :tint,:text,:ax,:ay,:az,:vib,' +
|
|
' :gx,:gy,:gz,:tilt)' +
|
|
' ON CONFLICT (device_id) DO UPDATE SET ' +
|
|
' seq=EXCLUDED.seq, pre_seq=EXCLUDED.pre_seq, comm_status=EXCLUDED.comm_status, received_at=NOW(), payload_json=EXCLUDED.payload_json, ' +
|
|
' pf_l1=EXCLUDED.pf_l1, pk_l1=EXCLUDED.pk_l1, v_l1=EXCLUDED.v_l1, i_l1=EXCLUDED.i_l1, w_l1=EXCLUDED.w_l1, var_l1=EXCLUDED.var_l1, va_l1=EXCLUDED.va_l1, ' +
|
|
' pf_l2=EXCLUDED.pf_l2, pk_l2=EXCLUDED.pk_l2, v_l2=EXCLUDED.v_l2, i_l2=EXCLUDED.i_l2, w_l2=EXCLUDED.w_l2, var_l2=EXCLUDED.var_l2, va_l2=EXCLUDED.va_l2, ' +
|
|
' pf_l3=EXCLUDED.pf_l3, pk_l3=EXCLUDED.pk_l3, v_l3=EXCLUDED.v_l3, i_l3=EXCLUDED.i_l3, w_l3=EXCLUDED.w_l3, var_l3=EXCLUDED.var_l3, va_l3=EXCLUDED.va_l3, ' +
|
|
' temp_int=EXCLUDED.temp_int, temp_ext=EXCLUDED.temp_ext, ' +
|
|
' acc_x=EXCLUDED.acc_x, acc_y=EXCLUDED.acc_y, acc_z=EXCLUDED.acc_z, vibration=EXCLUDED.vibration, ' +
|
|
' gyro_x=EXCLUDED.gyro_x, gyro_y=EXCLUDED.gyro_y, gyro_z=EXCLUDED.gyro_z, tilt_angle=EXCLUDED.tilt_angle' +
|
|
' WHERE nilm_data.seq IS DISTINCT FROM EXCLUDED.seq OR nilm_data.pre_seq IS DISTINCT FROM EXCLUDED.pre_seq';
|
|
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.ParamByName('seq').AsInteger := ASeq;
|
|
Q.ParamByName('preseq').AsInteger := APreSeq;
|
|
Q.ParamByName('comm').AsInteger := ACommStatus;
|
|
Q.ParamByName('pjson').AsString := APayloadJSON;
|
|
|
|
Q.ParamByName('pfl1').AsFloat := GetNum(GetJsonKey('pfL1', 'pfA'));
|
|
Q.ParamByName('pkl1').AsFloat := GetNum(GetJsonKey('pkL1', 'pkA'));
|
|
Q.ParamByName('vl1').AsFloat := GetNum(GetJsonKey('vL1', 'voltageA'));
|
|
Q.ParamByName('il1').AsFloat := GetNum(GetJsonKey('iL1', 'currentA'));
|
|
Q.ParamByName('wl1').AsFloat := GetNum(GetJsonKey('wL1', 'wA'));
|
|
Q.ParamByName('varl1').AsFloat:= GetNum(GetJsonKey('varL1', 'Var_A'));
|
|
Q.ParamByName('val1').AsFloat := GetNum(GetJsonKey('vaL1', 'Va_A'));
|
|
|
|
Q.ParamByName('pfl2').AsFloat := GetNum(GetJsonKey('pfL2', 'pfB'));
|
|
Q.ParamByName('pkl2').AsFloat := GetNum(GetJsonKey('pkL2', 'pkB'));
|
|
Q.ParamByName('vl2').AsFloat := GetNum(GetJsonKey('vL2', 'voltageB'));
|
|
Q.ParamByName('il2').AsFloat := GetNum(GetJsonKey('iL2', 'currentB'));
|
|
Q.ParamByName('wl2').AsFloat := GetNum(GetJsonKey('wL2', 'wB'));
|
|
Q.ParamByName('varl2').AsFloat:= GetNum(GetJsonKey('varL2', 'Var_B'));
|
|
Q.ParamByName('val2').AsFloat := GetNum(GetJsonKey('vaL2', 'Va_B'));
|
|
|
|
Q.ParamByName('pfl3').AsFloat := GetNum(GetJsonKey('pfL3', 'pfC'));
|
|
Q.ParamByName('pkl3').AsFloat := GetNum(GetJsonKey('pkL3', 'pkC'));
|
|
Q.ParamByName('vl3').AsFloat := GetNum(GetJsonKey('vL3', 'voltageC'));
|
|
Q.ParamByName('il3').AsFloat := GetNum(GetJsonKey('iL3', 'currentC'));
|
|
Q.ParamByName('wl3').AsFloat := GetNum(GetJsonKey('wL3', 'wC'));
|
|
Q.ParamByName('varl3').AsFloat:= GetNum(GetJsonKey('varL3', 'Var_C'));
|
|
Q.ParamByName('val3').AsFloat := GetNum(GetJsonKey('vaL3', 'Va_C'));
|
|
|
|
Q.ParamByName('tint').AsFloat := GetNum(GetJsonKey('tempInt', 'tempInt'));
|
|
Q.ParamByName('text').AsFloat := GetNum(GetJsonKey('tempExt', 'tempExt'));
|
|
Q.ParamByName('ax').AsFloat := GetNum(GetJsonKey('accX', 'accX'));
|
|
Q.ParamByName('ay').AsFloat := GetNum(GetJsonKey('accY', 'accY'));
|
|
Q.ParamByName('az').AsFloat := GetNum(GetJsonKey('accZ', 'accZ'));
|
|
Q.ParamByName('vib').AsFloat := GetNum(GetJsonKey('vibration', 'vibration'));
|
|
Q.ParamByName('gx').AsFloat := GetNum(GetJsonKey('gyroX', 'gyroX'));
|
|
Q.ParamByName('gy').AsFloat := GetNum(GetJsonKey('gyroY', 'gyroY'));
|
|
Q.ParamByName('gz').AsFloat := GetNum(GetJsonKey('gyroZ', 'gyroZ'));
|
|
Q.ParamByName('tilt').AsFloat := GetNum(GetJsonKey('tiltAngle', 'tiltAngle'));
|
|
|
|
Q.ExecSQL;
|
|
Q.Close;
|
|
|
|
// 3. 변경 이력 저장 (nilm_event_log) - seq가 변경되었을 때만 기록
|
|
if bSeqChanged then
|
|
SaveEventLog('DATA', ADeviceID, 'NILM Data Update (Seq: ' + IntToStr(ASeq) + ')', APayloadJSON);
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
procedure TNILMManager.SaveEventLog(const AEventType: string; ADeviceID: Integer;
|
|
const AMessage, ADetail: string);
|
|
var
|
|
Q: TFDQuery;
|
|
begin
|
|
Q := TFDQuery.Create(nil);
|
|
try
|
|
Q.Connection := GetConnection;
|
|
Q.SQL.Text :=
|
|
'INSERT INTO nilm_event_log (event_type, device_id, message, detail)' +
|
|
' VALUES (:etype, :did, :msg, :det)';
|
|
Q.ParamByName('etype').AsString := AEventType;
|
|
Q.ParamByName('did').AsInteger := ADeviceID;
|
|
Q.ParamByName('msg').AsString := AMessage;
|
|
Q.ParamByName('det').AsString := ADetail;
|
|
Q.ExecSQL;
|
|
finally
|
|
Q.Free;
|
|
end;
|
|
end;
|
|
|
|
function TNILMManager.QueryNILMHistory(ADeviceID: Integer;
|
|
ADateFrom, ADateTo: TDateTime;
|
|
ACommStatusFilter: Integer; AMaxRows: Integer): TFDQuery;
|
|
var
|
|
SQL: string;
|
|
begin
|
|
Result := TFDQuery.Create(nil);
|
|
Result.Connection := GetConnection;
|
|
|
|
SQL :=
|
|
'SELECT received_at, device_id, seq, pre_seq, comm_status,' +
|
|
' pf_l1, v_l1, i_l1, w_l1, var_l1, va_l1,' +
|
|
' pf_l2, v_l2, i_l2, w_l2, var_l2, va_l2,' +
|
|
' pf_l3, v_l3, i_l3, w_l3, var_l3, va_l3,' +
|
|
' temp_int, temp_ext, vibration, tilt_angle' +
|
|
' FROM nilm_data' +
|
|
' WHERE device_id > 0 ';
|
|
// ' WHERE received_at >= :dtfrom AND received_at <= :dtto';
|
|
|
|
if ADeviceID > 0 then
|
|
SQL := SQL + ' AND device_id = :did';
|
|
if ACommStatusFilter >= 0 then
|
|
SQL := SQL + ' AND comm_status = :comm';
|
|
|
|
// SQL := SQL + ' ORDER BY received_at DESC';
|
|
SQL := SQL + ' ORDER BY device_id ';
|
|
|
|
if AMaxRows > 0 then
|
|
SQL := SQL + ' LIMIT ' + IntToStr(AMaxRows);
|
|
|
|
Result.SQL.Text := SQL;
|
|
// Result.ParamByName('dtfrom').AsDateTime := ADateFrom;
|
|
// Result.ParamByName('dtto').AsDateTime := ADateTo;
|
|
|
|
if ADeviceID > 0 then
|
|
Result.ParamByName('did').AsInteger := ADeviceID;
|
|
if ACommStatusFilter >= 0 then
|
|
Result.ParamByName('comm').AsInteger := ACommStatusFilter;
|
|
|
|
Result.Open;
|
|
end;
|
|
|
|
function TNILMManager.QueryEventLog(ADeviceID: Integer;
|
|
ADateFrom, ADateTo: TDateTime;
|
|
const AEventType: string; AMaxRows: Integer): TFDQuery;
|
|
var
|
|
SQL: string;
|
|
begin
|
|
Result := TFDQuery.Create(nil);
|
|
Result.Connection := GetConnection;
|
|
|
|
SQL :=
|
|
'SELECT event_time, event_type, device_id, message, detail' +
|
|
' FROM nilm_event_log' +
|
|
' WHERE event_time >= :dtfrom AND event_time <= :dtto';
|
|
|
|
if ADeviceID > 0 then
|
|
SQL := SQL + ' AND device_id = :did';
|
|
if AEventType <> '' then
|
|
SQL := SQL + ' AND event_type = :etype';
|
|
|
|
SQL := SQL + ' ORDER BY event_time DESC';
|
|
|
|
if AMaxRows > 0 then
|
|
SQL := SQL + ' LIMIT ' + IntToStr(AMaxRows);
|
|
|
|
Result.SQL.Text := SQL;
|
|
Result.ParamByName('dtfrom').AsDateTime := ADateFrom;
|
|
Result.ParamByName('dtto').AsDateTime := ADateTo;
|
|
|
|
if ADeviceID > 0 then
|
|
Result.ParamByName('did').AsInteger := ADeviceID;
|
|
if AEventType <> '' then
|
|
Result.ParamByName('etype').AsString := AEventType;
|
|
|
|
Result.Open;
|
|
end;
|
|
|
|
function TNILMManager.LoadDevicesSimple: TFDQuery;
|
|
begin
|
|
Result := TFDQuery.Create(nil);
|
|
Result.Connection := GetConnection;
|
|
Result.SQL.Text :=
|
|
'SELECT device_id, device_name FROM nilm_device' +
|
|
' WHERE is_active = TRUE ORDER BY device_id';
|
|
Result.Open;
|
|
end;
|
|
|
|
end.
|
|
|