from system.dataset import toDataSet
from datetime import datetime
query = (
"SELECT "
"P.PowerDistributeIndex, "
"P.PowerDistributeName, "
"P.GroupNo, "
"P.FeederCount, "
"G.GrpName "
"FROM "
"tblPowerDistInfo AS P "
"JOIN "
"tblMCGrpName AS G ON P.GroupNo = G.GrpNo "
"ORDER BY P.GroupNo,P.PowerDistributeName ASC "
)
data = system.db.runQuery(query)
pd_data = {}
for row in data:
pd_index = row["PowerDistributeIndex"]
pd_name = row["PowerDistributeName"]
group_no = row["GroupNo"]
grp_name = row["GrpName"]
if len(grp_name.split(" ")) < 2:
floor = ""
location = grp_name
else:
floor, location = grp_name.split(" ")
pd_data[pd_index] = {
"pd_name": pd_name,
"group_no" : group_no,
"floor" : floor,
"location" : location,
"use_rate" : "",
"vol_r" : 0.0,
"vol_s" : 0.0,
"vol_t" : 0.0,
"cur_r" : 0.0,
"cur_s" : 0.0,
"cur_t" : 0.0,
"power_r" : 0.0,
"power_s" : 0.0,
"power_t" : 0.0,
"power_sum" : 0.0,
"powerkWh" : 0.0
}
############################################################################################################################
# load tag data
############################################################################################################################
start_date = data["StartDate"]
end_date = data["EndDate"]
#start_date = system.date.parse('2023-12-01 00:00:00', 'yyyy-MM-dd hh:mm:ss')
#end_date = system.date.parse('2023-12-05 23:59:59', 'yyyy-MM-dd hh:mm:ss')
start_date_timestamp = system.date.toMillis(start_date)
end_date_timestamp = system.date.toMillis(end_date)
var_list = ["InputVA", "InputVB", "InputVC", "CurrentA", "CurrentB", "CurrentC", "ActPowerA", "ActPowerB", "ActPowerC", "ActPower3P", "ActPowerkWh3P_cal"]
for pd_index, pd_dict in pd_data.items():
pd_name = pd_dict["pd_name"]
val_list = []
## ํ์คํ ๋ฆฌ์ ๋ฐ์ดํฐ db๋ ์๋ง๋ค ๋ฐ๋๋ฏ๋ก ์ฌ์ฉ๋๋ db ์ ํ
tblnamequery = (
"SELECT TOP 1 [pname] "
"FROM [sqlth_partitions] "
"WHERE drvid = 1 "
"ORDER BY start_time DESC "
)
tblname = system.db.runScalarPrepQuery(tblnamequery)
#print(tblname)
for var in var_list:
# ์ฌ๊ธฐ์์ feeder_no ๋ณ๋ก ํน์ variable์ tag history ๋ถ๋ฌ์ค๊ธฐ
tag_path = (pd_name + "/Feeder1/" + var).lower()
#query3 = """SELECT "id" FROM sqlth_te WHERE "tagpath"=?"""
#print(tag_path)
query3 = (
"SELECT TOP 1 [id] "
"FROM [sqlth_te] "
"WHERE [tagpath] = ?"
)
tag_id = system.db.runScalarPrepQuery(query3, [tag_path])
lastVal = 0.0
fisrtVal = 0.0
val = 0.0
subval = 0
#print(unicode(tag_id))
if tag_id:
#data3 = system.db.runPrepQuery(query3, [tag_path])
#if not data3: continue
#tag_id = data3.getValueAt(0, 0)
if var <> "ActPowerkWh3P_cal":
query4 = (
"SELECT AVG(floatvalue) AS avgVal "
"FROM " + unicode(tblname) + " "
"WHERE tagid = " + unicode(tag_id) + " "
"AND t_stamp BETWEEN " + unicode(start_date_timestamp) + " AND " + unicode(end_date_timestamp)
)
subval = system.db.runScalarPrepQuery(query4)
if subval:
val = subval
else:
queryL = (
"SELECT TOP 1 floatvalue AS lasVal "
"FROM " + unicode(tblname) + " "
"WHERE tagid = " + unicode(tag_id) + " "
"AND t_stamp BETWEEN " + unicode(start_date_timestamp) + " AND " + unicode(end_date_timestamp) + " "
"ORDER BY t_stamp DESC"
)
lastVal = system.db.runScalarPrepQuery(queryL)
queryF = (
"SELECT TOP 1 floatvalue AS lasVal "
"FROM " + unicode(tblname) + " "
"WHERE tagid = " + unicode(tag_id) + " "
"AND t_stamp BETWEEN " + unicode(start_date_timestamp) + " AND " + unicode(end_date_timestamp) + " "
"ORDER BY t_stamp ASC"
)
fisrtVal = system.db.runScalarPrepQuery(queryF)
if (lastVal - fisrtVal) > 0:
val = lastVal - fisrtVal
else:
val = 0.0
val_list.append(val)
#query4 = """SELECT "floatvalue" FROM sqlt_data_1_2023_12 WHERE "tagid" = ? AND "t_stamp" BETWEEN ? AND ?"""
#data4 = system.db.runPrepQuery(query4, [tblname, tag_id, start_date_timestamp, end_date_timestamp])
#data_list = [data4.getValueAt(i, j) for j in range(data4.getColumnCount()) for i in range(data4.getRowCount())]
# '์ ํจ์ ๋ ฅ๋ - ๋์ '์ผ ๊ฒฝ์ฐ
#if len(data_list) == 0:
# val_list.append(0)
#elif var == "ActPowerkWhC":
# val_list.append(data_list[len(data_list) - 1] - data_list[0])
#else:
# val_list.append(sum(data_list) / len(data_list))
print(val_list)
# val_list์ ์์๋ฅผ ํ๋ํ๋ ๊ฐ์ ธ์์ pd_data์ ์ ์ฅ.
if len(val_list) > 0:
pd_data[pd_index]["vol_r"] = val_list[0]
pd_data[pd_index]["vol_s"] = val_list[1]
pd_data[pd_index]["vol_t"] = val_list[2]
pd_data[pd_index]["cur_r"] = val_list[3]
pd_data[pd_index]["cur_s"] = val_list[4]
pd_data[pd_index]["cur_t"] = val_list[5]
pd_data[pd_index]["power_r"] = val_list[6] / 1000
pd_data[pd_index]["power_s"] = val_list[7] / 1000
pd_data[pd_index]["power_t"] = val_list[8] / 1000
pd_data[pd_index]["power_sum"] = val_list[9] / 1000
pd_data[pd_index]["ActPowerkWh3P_cal"] = val_list[10]
############################################################################################################################
# result
############################################################################################################################
headers = ["floor", "location", "pd_name", "use_rate", "vol_r", "vol_s", "vol_t", "cur_r", "cur_s", "cur_t", "power_r",
"power_s", "power_t", "power_sum", "powerkWh_c"]
result = []
for pd_index, items in pd_data.items():
floor = items["floor"]
location = items["location"]
pd_name = items["pd_name"]
use_rate = items["use_rate"]
vol_r = items["vol_r"]
vol_s = items["vol_s"]
vol_t = items["vol_t"]
cur_r = items["cur_r"]
cur_s = items["cur_s"]
cur_t = items["cur_t"]
power_r = items["power_r"]
power_s = items["power_s"]
power_t = items["power_t"]
power_sum = items["power_sum"]
powerkWh3P = items["ActPowerkWh3P_cal"]
# ๋ฆฌ์คํธ์ ์ ์๋ก ๋ณํ๋ ๊ฐ์ ์ถ๊ฐ
result.append([floor, location, pd_name, use_rate, vol_r, vol_s, vol_t, cur_r, cur_s, cur_t, power_r, power_s, power_t, power_sum, powerkWh3P])
# ํํ๋ก ์ ๋ ฌ
result = sorted(result, key=lambda x: x[0:2])
# ๋ฆฌ์คํธ๋ฅผ ๋ฐ์ดํฐ์
์ผ๋ก ๋ณํ
dataset = system.dataset.toDataSet(headers, result)
data["result"] = dataset
When you finally do "data["result"] = dataset ", the following error occurs.
error
Traceback (most recent call last):
File "", line 195, in
TypeError: com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet indices must be integers