Good afternoon Team,
I have another one i need some good eyes to look over.
In this i am using an 'Equipment Scheulde' to store data and then pass the data to a pie chart.
For the dataset i use this query:
WITH StatusDurations AS (
SELECT
t_stamp,
Machine,
Status,
LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS next_t_stamp
FROM Machine_Statuses
WHERE Machine LIKE '%LTS%'
AND t_stamp BETWEEN :startDate AND :endDate
)
SELECT
t_stamp,
Machine,
Status,
ISNULL(DATEDIFF(second, t_stamp, next_t_stamp), 0) AS DurationInSeconds
FROM
StatusDurations
ORDER BY
t_stamp;
I have created a 'change script' to track the statuses of the machine.
def valueChanged(self, previousValue, currentValue, origin, missedEvents):
LTS1 = system.dataset.toPyDataSet(self.custom.LTS)
header = ["itemId", "startDate", "endDate", "color", "opacity", "underlay"]
newData = []
index = 0
lLTS = len(LTS1)
for row in LTS1:
if index < (lLTS - 1):
stopDate = LTS1[index+1]['t_stamp']
#else:
# stopDate = system.date.now()
#self.custom.lastDate = stopDate
startDate = row['t_stamp']
if row['Status'] == 0:
color = '#808080'
elif row['Status'] == 2:
color = '#00FF00'
elif row['Status'] == 1:
color = '#FF0000'
opacity = 1
underlay = True
newData.append([1, startDate, stopDate, color, opacity, underlay])
index += 1
self.custom.StatusData = system.dataset.toDataSet(header, newData)
I use this 'change script' and it should update the 'LTS PIE' chart.
def ltsPercentOn(machine, startDate):
"""Calculate machine percentage on from data in the Machine_Statuses table
ARGS: machine(string) - This is the name of the machine, this name should match some records in the Machine column of the table
startDate(date) - This is the start time of the query to the database
runCodes(integer List) - this is a list of run codes that the machine might have. defaults to [1] but sometimes machines have other status codes that still can
mean that the machine is producing product.
"""
query = """WITH StatusDurations AS (
SELECT
t_stamp,
Machine,
Status,
LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS next_t_stamp
FROM Machine_Statuses
WHERE Machine = 'LTS'
AND t_stamp BETWEEN ? AND ?
)
SELECT
t_stamp,
Machine,
Status,
ISNULL(DATEDIFF(second, t_stamp, next_t_stamp), 0) AS DurationInSeconds
FROM
StatusDurations
ORDER BY
t_stamp
"""
totalStart = startDate
totalStop = system.date.now()
rawData = system.db.runPrepQuery(query, [totalStart, totalStop, machine], "IgnitionCWDB")
#totalTime = system.date.minutesBetween(totalStart, totalStop) This was not fine enough for some pieces of equipment.
totalTime = system.date.secondsBetween(totalStart, totalStop)
idleSeconds = 0
runSeconds = 0
runAlarmSeconds = 0
currentIndex = 0
for row in rawData:
eventStart = row['t_stamp']
if currentIndex >= len(rawData) - 1:
eventStop = totalStop
else:
eventStop = rawData[currentIndex + 1]['t_stamp']
if row['Status'] == 0:
idleSeconds += system.date.secondsBetween(eventStart, eventStop)
elif row['Status'] == 2:
runSeconds += system.date.secondsBetween(eventStart, eventStop)
elif row['Status'] == 1:
runAlarmSeconds += system.date.secondsBetween(eventStart, eventStop)
currentIndex += 1
if totalTime > 0:
minutesIdle = float(idleSeconds)/60.0
minutesRun = float(runSeconds)/60.0
minutesRunAlarm = float(runAlarmSeconds)/60.0
else:
minutesIdle = 0
minutesRunAlarm = 0
minutesRun = 0
# Create dataset for chart
header = ['Label', 'Value']
data = []
data.append(['Idle', minutesIdle])
data.append(['Starting up or Safety Issue', minutesRunAlarm])
data.append(['Executing', minutesRun])
dataSet = system.dataset.toDataSet(header, data)
return dataSet
# Get the current start date info
startDate = self.props.dateRange.startDate
# Get data for LTS and populate chart.
LTSData = ltsPercentOn('LTS Uptime', startDate)
self.getSibling("LTS PIE").props.data = LTSData
I have used this same set up to create stauts/uptime charts on different pieces of equipment.
WIth this specific one, the status on the 'Equipment Schedule' updates fine but it does not pass to the pie chart.
Any ideas??