{'Guid': 'VARCHAR(64) NOT NULL', 'StationId': 'VARCHAR(64) NOT NULL', 'Name': 'VARCHAR(255)', 'Capacity': 'DECIMAL(10,2) DEFAULT 0', 'Location': 'VARCHAR(512)', 'Latitude': 'DECIMAL(12,8) DEFAULT 0', 'Longitude': 'DECIMAL(12,8) DEFAULT 0', 'Status': 'VARCHAR(32)', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'UpdateDate': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'PRIMARY_KEY': 'StationId, Package'}
{'Guid': 'VARCHAR(64) NOT NULL', 'StationId': 'VARCHAR(64) NOT NULL', 'DeviceSn': 'VARCHAR(128) NOT NULL', 'DeviceId': 'VARCHAR(128)', 'DeviceType': 'VARCHAR(64)', 'DisplayName': 'VARCHAR(255)', 'ConnectStatus': 'INT DEFAULT 0', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'UpdateDate': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'PRIMARY_KEY': 'DeviceSn, Package'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'StationId': 'VARCHAR(64) NOT NULL', 'DeviceSn': 'VARCHAR(128) NOT NULL', 'Payload': 'JSON', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'CreateDate': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'StationId': 'VARCHAR(64) NOT NULL', 'DeviceSn': 'VARCHAR(128) NOT NULL', 'DeviceModel': 'VARCHAR(128)', 'DeviceTime': 'VARCHAR(32)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Package': 'VARCHAR(255)', 'Module': 'VARCHAR(255)', 'PvVoltage1': 'DECIMAL(10,2) DEFAULT 0', 'PvVoltage2': 'DECIMAL(10,2) DEFAULT 0', 'PvVoltage3': 'DECIMAL(10,2) DEFAULT 0', 'PvVoltage4': 'DECIMAL(10,2) DEFAULT 0', 'PvCurrent1': 'DECIMAL(10,2) DEFAULT 0', 'PvCurrent2': 'DECIMAL(10,2) DEFAULT 0', 'PvCurrent3': 'DECIMAL(10,2) DEFAULT 0', 'PvCurrent4': 'DECIMAL(10,2) DEFAULT 0', 'PvPower1': 'DECIMAL(10,2) DEFAULT 0', 'PvPower2': 'DECIMAL(10,2) DEFAULT 0', 'PvPower3': 'DECIMAL(10,2) DEFAULT 0', 'PvPower4': 'DECIMAL(10,2) DEFAULT 0', 'PvPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'PvDailyGeneration': 'DECIMAL(10,2) DEFAULT 0', 'TotalGeneration': 'DECIMAL(12,2) DEFAULT 0', 'DailyGeneration': 'DECIMAL(10,2) DEFAULT 0', 'InvVoltageL1': 'DECIMAL(10,2) DEFAULT 0', 'InvVoltageL2': 'DECIMAL(10,2) DEFAULT 0', 'InvVoltageL3': 'DECIMAL(10,2) DEFAULT 0', 'InvCurrentL1': 'DECIMAL(10,2) DEFAULT 0', 'InvCurrentL2': 'DECIMAL(10,2) DEFAULT 0', 'InvCurrentL3': 'DECIMAL(10,2) DEFAULT 0', 'InvFrequency': 'DECIMAL(8,2) DEFAULT 0', 'InvPowerL1': 'DECIMAL(10,2) DEFAULT 0', 'InvPowerL2': 'DECIMAL(10,2) DEFAULT 0', 'InvPowerL3': 'DECIMAL(10,2) DEFAULT 0', 'InvPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'InvTemperature': 'DECIMAL(8,2) DEFAULT 0', 'GridVoltageL1': 'DECIMAL(10,2) DEFAULT 0', 'GridVoltageL2': 'DECIMAL(10,2) DEFAULT 0', 'GridVoltageL3': 'DECIMAL(10,2) DEFAULT 0', 'GridCurrentL1': 'DECIMAL(10,2) DEFAULT 0', 'GridCurrentL2': 'DECIMAL(10,2) DEFAULT 0', 'GridCurrentL3': 'DECIMAL(10,2) DEFAULT 0', 'GridPowerL1': 'DECIMAL(10,2) DEFAULT 0', 'GridPowerL2': 'DECIMAL(10,2) DEFAULT 0', 'GridPowerL3': 'DECIMAL(10,2) DEFAULT 0', 'GridPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'GridFrequency': 'DECIMAL(8,2) DEFAULT 0', 'GridStatus': 'VARCHAR(64)', 'GridRelayStatus': 'VARCHAR(64)', 'CtPowerL1': 'DECIMAL(10,2) DEFAULT 0', 'CtPowerL2': 'DECIMAL(10,2) DEFAULT 0', 'CtPowerL3': 'DECIMAL(10,2) DEFAULT 0', 'CtPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'BatteryStatus': 'VARCHAR(32)', 'BatteryVoltage': 'DECIMAL(8,2) DEFAULT 0', 'BatteryCurrent': 'DECIMAL(8,2) DEFAULT 0', 'BatteryPower': 'DECIMAL(10,2) DEFAULT 0', 'BatterySoc': 'DECIMAL(5,1) DEFAULT 0', 'BatteryType': 'VARCHAR(32)', 'BatteryTemperature': 'DECIMAL(8,2) DEFAULT 0', 'BatteryRatedCapacity': 'DECIMAL(10,2) DEFAULT 0', 'BatteryDailyCharge': 'DECIMAL(10,2) DEFAULT 0', 'BatteryDailyDischarge': 'DECIMAL(10,2) DEFAULT 0', 'BatteryTotalCharge': 'DECIMAL(12,2) DEFAULT 0', 'BatteryTotalDischarge': 'DECIMAL(12,2) DEFAULT 0', 'BmsVoltage': 'DECIMAL(8,2) DEFAULT 0', 'BmsCurrent': 'DECIMAL(8,2) DEFAULT 0', 'BmsTemperature': 'DECIMAL(8,2) DEFAULT 0', 'BmsChargeVoltage': 'DECIMAL(8,2) DEFAULT 0', 'BmsDischargeVoltage': 'DECIMAL(8,2) DEFAULT 0', 'BmsChargeCurrentLimit': 'DECIMAL(10,2) DEFAULT 0', 'BmsDischargeCurrentLimit': 'DECIMAL(10,2) DEFAULT 0', 'BmsSoc': 'DECIMAL(5,1) DEFAULT 0', 'ConsumptionVoltageL1': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionVoltageL2': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionVoltageL3': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionPowerL1': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionPowerL2': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionPowerL3': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionVaTotal': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionDaily': 'DECIMAL(10,2) DEFAULT 0', 'ConsumptionTotal': 'DECIMAL(12,2) DEFAULT 0', 'LoadFrequency': 'DECIMAL(8,2) DEFAULT 0', 'EnergyFromBatteryDaily': 'DECIMAL(10,2) DEFAULT 0', 'EnergyToBatteryDaily': 'DECIMAL(10,2) DEFAULT 0', 'EnergyFromBatteryTotal': 'DECIMAL(12,2) DEFAULT 0', 'EnergyToBatteryTotal': 'DECIMAL(12,2) DEFAULT 0', 'GenPowerL1': 'DECIMAL(10,2) DEFAULT 0', 'GenPowerL2': 'DECIMAL(10,2) DEFAULT 0', 'GenPowerL3': 'DECIMAL(10,2) DEFAULT 0', 'GenVoltageL1': 'DECIMAL(10,2) DEFAULT 0', 'GenVoltageL2': 'DECIMAL(10,2) DEFAULT 0', 'GenVoltageL3': 'DECIMAL(10,2) DEFAULT 0', 'GenPowerTotal': 'DECIMAL(10,2) DEFAULT 0', 'GenDailyEnergy': 'DECIMAL(10,2) DEFAULT 0', 'GenTotalEnergy': 'DECIMAL(12,2) DEFAULT 0', 'GenRuntime': 'DECIMAL(10,2) DEFAULT 0', 'RatedPower': 'DECIMAL(10,2) DEFAULT 0'}
INSERT INTO def_solarman_plants
(Guid, StationId, Name, Capacity, Location,
Latitude, Longitude, Status, Package, Module)
VALUES
('{guid}', '{station_id}', '{name}', {capacity},
'{location}', {latitude}, {longitude},
'{status}', '{package}', 'ObjServiceSolarman')
ON DUPLICATE KEY UPDATE
Name = VALUES(Name),
Capacity = VALUES(Capacity),
Location = VALUES(Location),
Latitude = VALUES(Latitude),
Longitude = VALUES(Longitude),
Status = VALUES(Status),
Guid = VALUES(Guid)
INSERT INTO def_solarman_devices
(Guid, StationId, DeviceSn, DeviceId, DeviceType,
DisplayName, ConnectStatus, Package, Module)
VALUES
('{guid}', '{station_id}', '{device_sn}', '{device_id}',
'{device_type}', '{display_name}', {connect_status},
'{package}', 'ObjServiceSolarman')
ON DUPLICATE KEY UPDATE
StationId = VALUES(StationId),
DeviceId = VALUES(DeviceId),
DeviceType = VALUES(DeviceType),
DisplayName = VALUES(DisplayName),
ConnectStatus = VALUES(ConnectStatus),
Guid = VALUES(Guid)
INSERT INTO def_solarman_readings
(Guid, StationId, DeviceSn, Payload, Package, Module)
VALUES
('{guid}', '{station_id}', '{device_sn}',
'{payload}', '{package}', 'ObjServiceSolarman')
INSERT INTO data_solar
(Guid, StationId, DeviceSn, DeviceModel, DeviceTime,
Package, Module,
PvVoltage1, PvVoltage2, PvVoltage3, PvVoltage4,
PvCurrent1, PvCurrent2, PvCurrent3, PvCurrent4,
PvPower1, PvPower2, PvPower3, PvPower4,
PvPowerTotal, PvDailyGeneration,
TotalGeneration, DailyGeneration,
InvVoltageL1, InvVoltageL2, InvVoltageL3,
InvCurrentL1, InvCurrentL2, InvCurrentL3,
InvFrequency,
InvPowerL1, InvPowerL2, InvPowerL3, InvPowerTotal,
InvTemperature,
GridVoltageL1, GridVoltageL2, GridVoltageL3,
GridCurrentL1, GridCurrentL2, GridCurrentL3,
GridPowerL1, GridPowerL2, GridPowerL3, GridPowerTotal,
GridFrequency, GridStatus, GridRelayStatus,
CtPowerL1, CtPowerL2, CtPowerL3, CtPowerTotal,
BatteryStatus, BatteryVoltage, BatteryCurrent,
BatteryPower, BatterySoc, BatteryType,
BatteryTemperature, BatteryRatedCapacity,
BatteryDailyCharge, BatteryDailyDischarge,
BatteryTotalCharge, BatteryTotalDischarge,
BmsVoltage, BmsCurrent, BmsTemperature,
BmsChargeVoltage, BmsDischargeVoltage,
BmsChargeCurrentLimit, BmsDischargeCurrentLimit, BmsSoc,
ConsumptionVoltageL1, ConsumptionVoltageL2, ConsumptionVoltageL3,
ConsumptionPowerL1, ConsumptionPowerL2, ConsumptionPowerL3,
ConsumptionPowerTotal, ConsumptionVaTotal,
ConsumptionDaily, ConsumptionTotal, LoadFrequency,
EnergyFromBatteryDaily, EnergyToBatteryDaily,
EnergyFromBatteryTotal, EnergyToBatteryTotal,
GenPowerL1, GenPowerL2, GenPowerL3,
GenVoltageL1, GenVoltageL2, GenVoltageL3,
GenPowerTotal, GenDailyEnergy, GenTotalEnergy,
GenRuntime, RatedPower)
VALUES
('{guid}', '{station_id}', '{device_sn}',
'{device_model}', '{device_time}',
'{package}', 'ObjServiceSolarman',
{pv_voltage_1}, {pv_voltage_2}, {pv_voltage_3}, {pv_voltage_4},
{pv_current_1}, {pv_current_2}, {pv_current_3}, {pv_current_4},
{pv_power_1}, {pv_power_2}, {pv_power_3}, {pv_power_4},
{pv_power_total}, {pv_daily_generation},
{total_generation}, {daily_generation},
{inv_voltage_l1}, {inv_voltage_l2}, {inv_voltage_l3},
{inv_current_l1}, {inv_current_l2}, {inv_current_l3},
{inv_frequency},
{inv_power_l1}, {inv_power_l2}, {inv_power_l3}, {inv_power_total},
{inv_temperature},
{grid_voltage_l1}, {grid_voltage_l2}, {grid_voltage_l3},
{grid_current_l1}, {grid_current_l2}, {grid_current_l3},
{grid_power_l1}, {grid_power_l2}, {grid_power_l3}, {grid_power_total},
{grid_frequency}, '{grid_status}', '{grid_relay_status}',
{ct_power_l1}, {ct_power_l2}, {ct_power_l3}, {ct_power_total},
'{battery_status}', {battery_voltage}, {battery_current},
{battery_power}, {battery_soc}, '{battery_type}',
{battery_temperature}, {battery_rated_capacity},
{battery_daily_charge}, {battery_daily_discharge},
{battery_total_charge}, {battery_total_discharge},
{bms_voltage}, {bms_current}, {bms_temperature},
{bms_charge_voltage}, {bms_discharge_voltage},
{bms_charge_current_limit}, {bms_discharge_current_limit}, {bms_soc},
{consumption_voltage_l1}, {consumption_voltage_l2}, {consumption_voltage_l3},
{consumption_power_l1}, {consumption_power_l2}, {consumption_power_l3},
{consumption_power_total}, {consumption_va_total},
{consumption_daily}, {consumption_total}, {load_frequency},
{energy_from_battery_daily}, {energy_to_battery_daily},
{energy_from_battery_total}, {energy_to_battery_total},
{gen_power_l1}, {gen_power_l2}, {gen_power_l3},
{gen_voltage_l1}, {gen_voltage_l2}, {gen_voltage_l3},
{gen_power_total}, {gen_daily_energy}, {gen_total_energy},
{gen_runtime}, {rated_power})
SELECT * FROM data_solar WHERE DeviceSn = '{device_sn}' AND Package = '{package}' ORDER BY CreateTime DESC LIMIT 1
SELECT * FROM data_solar WHERE DeviceSn = '{device_sn}'
AND Package = '{package}'
AND CreateTime BETWEEN '{start_time}' AND '{end_time}'
ORDER BY CreateTime
SELECT
DATE(CreateTime) AS ReadingDate,
MAX(DailyGeneration) AS DailyGeneration,
MAX(ConsumptionDaily) AS DailyConsumption,
MAX(BatteryDailyCharge) AS DailyBatteryCharge,
MAX(BatteryDailyDischarge) AS DailyBatteryDischarge,
MAX(PvPowerTotal) AS PeakSolarPower,
MAX(ConsumptionPowerTotal) AS PeakConsumptionPower,
MIN(BatterySoc) AS MinBatterySoc,
MAX(BatterySoc) AS MaxBatterySoc,
COUNT(*) AS ReadingCount
FROM data_solar WHERE DeviceSn = '{device_sn}'
AND Package = '{package}'
AND CreateTime BETWEEN '{start_time}' AND '{end_time}'
GROUP BY DATE(CreateTime) ORDER BY ReadingDate
SELECT
DATE(CreateTime) AS ReadingDate,
HOUR(CreateTime) AS ReadingHour,
AVG(PvPowerTotal) AS AvgSolarPower,
AVG(ConsumptionPowerTotal) AS AvgConsumptionPower,
AVG(GridPowerTotal) AS AvgGridPower,
AVG(BatteryPower) AS AvgBatteryPower,
AVG(BatterySoc) AS AvgBatterySoc,
COUNT(*) AS ReadingCount
FROM data_solar WHERE DeviceSn = '{device_sn}'
AND Package = '{package}'
AND CreateTime BETWEEN '{start_time}' AND '{end_time}'
GROUP BY DATE(CreateTime), HOUR(CreateTime) ORDER BY ReadingDate, ReadingHour
SELECT StationId, Name, Capacity, Status FROM def_solarman_plants WHERE Package = '{package}' ORDER BY Name
SELECT DeviceSn, DeviceId, DeviceType, DisplayName, ConnectStatus FROM def_solarman_devices WHERE StationId = '{station_id}' AND Package = '{package}' ORDER BY DisplayName
SELECT Payload, CreateDate FROM def_solarman_readings WHERE DeviceSn = '{device_sn}' AND Package = '{package}' ORDER BY CreateDate DESC LIMIT 1