A report script error occurs. It is the same message as the previous error, but the cause cannot be found. Help me!!
from system.dataset import toDataSet
#from datetime import datetime
#start_date = data["StartDate"]
#end_date = data["EndDate"]
#query1 = """SELECT "RackNo", "RackID", "FeederNo", "CustomerNo", "PowerDistributeNo", "PowerType", "ContractPower" FROM tblRackInfo WHERE "CustomerNo" > 5"""
query1 = (
"SELECT "
"A.RackNo, A.RackID, A.FeederNo, A.CustomerNo, A.PowerDistributeNo, "
"A.PowerType, A.ContractPower, B.CustomerID, B.CustomerName, C.PowerDistributeName, D.GrpNo, D.GrpName "
"FROM tblRackInfo AS A "
"JOIN tblCustomerInfo AS B "
"ON A.CustomerNo = B.CustomerNo "
"JOIN tblPowerDistInfo AS C "
"ON A.PowerDistributeNo = C.PowerDistributeIndex "
"JOIN tblMCGrpName AS D "
"ON C.GroupNo = D.GrpNo "
"WHERE A.CustomerNo > 5 "
"ORDER BY D.GrpNo, C.PowerDistributeName ASC "
)
data1 = system.db.runQuery(query1)
rack_data = {}
for row in data1:
rack_id = row["RackID"]
rack_no = row["RackNo"]
feeder_no = row["FeederNo"]
customer_no = row["CustomerNo"]
pd_index = row["PowerDistributeNo"]
power_type = row["PowerType"]
upper_power = row["ContractPower"]
customer_id = row["CustomerID"]
customer_name = row["CustomerName"]
pd_name = row["PowerDistributeName"]
group_no = row["GrpNo"]
group_name = row["GrpName"]
index = rack_id + "-" + str(pd_index) + "-" + str(feeder_no)
rack_data[index] = {
"rack_id": rack_id,
"rack_no": rack_no,
"feeder_no": int(feeder_no),
"customer_no": customer_no,
"customer_id": customer_id,
"customer_name": customer_name,
"pd_index": pd_index,
"pd_name": pd_name,
"group_no": group_no,
"group_name": group_name,
"center": unicode("부산센터"),
"power_type": power_type,
"voltage": 0.0,
"current": 0.0,
"rack_current": 0.0,
"power": 0.0,
"rack_power": 0.0,
"upper_power": upper_power,
"state": "",
"exceed_power": 0.0,
"max_power": 0.0,
"powerkWh": 0.0,
"rack_pd": rack_id + "-" + pd_name,
"subtotal": 0
}
################################################################################################
#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-18 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)
for index, rack_info in rack_data.items():
pd_name = rack_info["pd_name"]
currents = []
if rack_info["power_type"] == 1:
var_list = ["InputVA", "CurrentA", "CurrentB", "CurrentC", "ActPower3P", "ActPowerkWh3P_cal"]
elif rack_info["power_type"] == 0:
var_list = ["InputV", "Current", "ActPower", "ActPowerkWh"]
# else: continue
## 히스토리안 데이터 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 + "/Feeder" + str(rack_info["rack_no"]) + "/" + 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
if tag_id:
if var == "ActPowerkWh3P_cal" or var == "ActPowerkWh":
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:
if fisrtVal:
if (lastVal - fisrtVal) > 0:
val = lastVal - fisrtVal
else:
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
#print(round(val,2))
if "InputV" in var:
rack_data[index]["voltage"] = val
elif "InputVA" in var:
rack_data[index]["voltage"] = val
elif "Current" in var:
current = val
currents.append(current)
elif "CurrentA" in var:
current = val
currents.append(current)
elif "CurrentB" in var:
current = val
currents.append(current)
elif "CurrentC" in var:
current = val
currents.append(current)
elif "ActPowerkWh" in var:
rack_data[index]["powerkWh"] = val/1000
elif "ActPowerkWh3P_cal" in var:
rack_data[index]["powerkWh"] = val
elif "ActPower" in var:
power = val / 1000
rack_data[index]["power"] = power
rack_data[index]["max_power"] = 0
elif "ActPower3P" in var:
power = val / 1000
rack_data[index]["power"] = power
rack_data[index]["max_power"] = 0
rack_data[index]["current"] = round(sum(currents),2)
#print("Current sum: " + str(rack_data[index]["current"]))
# 랙 별로 rack_current, rack_power, exceed_power 계산 (+ state)
sum_currents = {}
sum_powers = {}
for index, rack_info in rack_data.items():
rack_pd = rack_info['rack_pd']
current = rack_info['current']
power = rack_info['power']
sum_currents.setdefault(rack_pd, 0)
sum_powers.setdefault(rack_pd, 0)
sum_currents[rack_pd] += current
sum_powers[rack_pd] += power
print("index: " + str(index))
print(rack_pd)
for index, rack_info in rack_data.items():
rack_pd = rack_info['rack_pd']
rack_info['rack_current'] = sum_currents.get(rack_pd, 0)
rack_info['rack_power'] = sum_powers.get(rack_pd, 0)
rack_info['exceed_power'] = rack_info['rack_power'] - rack_info['upper_power']
rack_info['state'] = unicode('초과') if rack_info['exceed_power'] > 0 else unicode('적정')
headers = ["customer_id", "customer_name", "center", "group_name", "rack_id", "pd_name", "feeder_no", "voltage", "current",
"rack_current", "power", "rack_power", "upper_power", "state", "exceed_power", "max_power", "powerkWh", "subtotal"]
result = []
skips = []
for index, rack_info in rack_data.items():
customer_id = rack_info["customer_id"]
customer_name = rack_info["customer_name"]
center = rack_info["center"]
group_name = rack_info["group_name"]
rack_id = rack_info["rack_id"]
pd_name = rack_info["pd_name"]
feeder_no = rack_info["feeder_no"]
voltage = rack_info["voltage"]
current = rack_info["current"]
rack_current = rack_info["rack_current"]
power = rack_info["power"]
rack_power = rack_info["rack_power"]
upper_power = rack_info["upper_power"]
state = rack_info["state"]
exceed_power = rack_info["exceed_power"]
max_power = rack_info["max_power"]
powerkWh = rack_info["powerkWh"]
subtotal = rack_info["subtotal"]
print(str(index))
result.append([customer_id, customer_name, center, group_name, rack_id, pd_name, feeder_no, voltage, current, rack_current, power, rack_power, upper_power, state, exceed_power, max_power, powerkWh, subtotal])
result = sorted(result, key=lambda x: (x[0], x[17], x[3], x[4], x[5]))
Ddataset = toDataSet(headers, result)
data['result'] = Ddataset
error message:
Traceback (most recent call last):
File "", line 232, in
TypeError: com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet indices must be integers
