Populating a PIE Chart from an Equipment Schedule

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??

I am confused why there is a Named Query and a Prep Query call? You should use the data from the named query in a binding and then pass that data around not call it multiple times. Utilize a custom property and then pass it down to the properties of your schedule and the pie chart.