Display sum of a column at the end of the table

Dharani_T · January 18, 2023, 9:46am

Hi,
I would like to display status of machines in vision like below,

Is there any option to display the sum of each column at the end of the table as shown in the pic.

justinedwards.jle · January 18, 2023, 9:49am

How are you populating the table?

Dharani_T · January 18, 2023, 9:50am

by passing the named query via button click.

justinedwards.jle · January 18, 2023, 9:53am

What is the datatype of the time columns?

Dharani_T · January 18, 2023, 10:04am

int.
The query i used:

SELECT  
    EquipmentName as 'Machine No',
    ShiftID as 'Shift',
   TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Idling' THEN  RunTime   ELSE 0 END)),'%H:%i:%s') as 'Idling Time',
     TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Jogging' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Jog Time',
     TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Stopped' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Stop Time',
     TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Not Connected' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'No Connection Time',
    TIME_FORMAT(SEC_TO_TIME(  SUM(CASE WHEN StateChangeFrom = 'Rewinding' THEN RunTime ELSE 0 END) ),'%H:%i:%s')as 'Rewind Time'
FROM tbl_equipment 
JOIN tbl_machinestatus ON tbl_machinestatus.EquipmentID = tbl_equipment.EquipmentID

WHERE tbl_machinestatus.ProcessArea = :iProcessArea  and DATE(DateofProduction) = DATE( :iStartDate )
AND   IF (:iEquipmentID =100,tbl_machinestatus.EquipmentID ,:iEquipmentID) = tbl_machinestatus.EquipmentID
AND IF(:iShift = 'ALL' , tbl_machinestatus.ShiftID ,:iShift)=tbl_machinestatus.ShiftID
GROUP BY EquipmentName
ORDER BY EquipmentName

type or paste code here

justinedwards.jle · January 19, 2023, 3:32am

There are at least two approaches that would work here. You could write a second select statement that automatically generates the sums, and then append it to the last row of your existing query with a union. In this way, the data comes directly from the query with no intermediary scripting required.
The query would look something like this:

Sample Query

SELECT  
  EquipmentName as 'Machine No',
  ShiftID as 'Shift',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Idling' THEN  RunTime   ELSE 0 END)),'%H:%i:%s') as 'Idling Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Jogging' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Jog Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Stopped' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Stop Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Not Connected' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'No Connection Time',
  TIME_FORMAT(SEC_TO_TIME(  SUM(CASE WHEN StateChangeFrom = 'Rewinding' THEN RunTime ELSE 0 END) ),'%H:%i:%s')as 'Rewind Time'
FROM tbl_equipment 
JOIN tbl_machinestatus ON tbl_machinestatus.EquipmentID = tbl_equipment.EquipmentID
WHERE tbl_machinestatus.ProcessArea = :iProcessArea  and DATE(DateofProduction) = DATE( :iStartDate )
AND   IF (:iEquipmentID =100,tbl_machinestatus.EquipmentID ,:iEquipmentID) = tbl_machinestatus.EquipmentID
AND IF(:iShift = 'ALL' , tbl_machinestatus.ShiftID ,:iShift)=tbl_machinestatus.ShiftID
GROUP BY EquipmentName
ORDER BY EquipmentName

UNION

SELECT  
  '',
  'Total',
  SEC_TO_TIME(SUM(TIME_TO_SEC('Idling Time')) ,'%H:%i:%s')as 'Idling Time',
  SEC_TO_TIME(SUM(TIME_TO_SEC('Jog Time')) ,'%H:%i:%s') as 'Jog Time',
  SEC_TO_TIME(SUM(TIME_TO_SEC('Stop Time')) ,'%H:%i:%s') as 'Stop Time',
  SEC_TO_TIME(SUM(TIME_TO_SEC('No Connection Time')) ,'%H:%i:%s') as 'No Connection Time',
  SEC_TO_TIME(SUM(TIME_TO_SEC('Rewind Time')) ,'%H:%i:%s') as 'Rewind Time'
FROM (
SELECT  
  EquipmentName as 'Machine No',
  ShiftID as 'Shift',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Idling' THEN  RunTime   ELSE 0 END)),'%H:%i:%s') as 'Idling Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Jogging' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Jog Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Stopped' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Stop Time',
  TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Not Connected' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'No Connection Time',
  TIME_FORMAT(SEC_TO_TIME(  SUM(CASE WHEN StateChangeFrom = 'Rewinding' THEN RunTime ELSE 0 END) ),'%H:%i:%s')as 'Rewind Time'
FROM tbl_equipment 
JOIN tbl_machinestatus ON tbl_machinestatus.EquipmentID = tbl_equipment.EquipmentID
WHERE tbl_machinestatus.ProcessArea = :iProcessArea  and DATE(DateofProduction) = DATE( :iStartDate )
AND   IF (:iEquipmentID =100,tbl_machinestatus.EquipmentID ,:iEquipmentID) = tbl_machinestatus.EquipmentID
AND IF(:iShift = 'ALL' , tbl_machinestatus.ShiftID ,:iShift)=tbl_machinestatus.ShiftID
GROUP BY EquipmentName
ORDER BY EquipmentName
  )

It is probable that the first two columns will have to be cast as strings to prevent a datatype error, but otherwise, the above query should be pretty close to what you are needing.

The other approach would be to modify the dataset after it has been pulled in by the button but before it has been applied to the table.
Here is an example script that will accomplish this:

Source Code

from datetime import datetime, timedelta
def getSumValue(elapsed_time_strings):#calculates the sum value for each column
	total_elapsed_time = timedelta()
	for elapsed_time_string in elapsed_time_strings:
	    hours, minutes, seconds = [int(x) for x in elapsed_time_string.split(":")]
	    total_elapsed_time += timedelta(hours=hours, minutes=minutes, seconds=seconds)
	total_elapsed_time_string = str(total_elapsed_time)
	if total_elapsed_time_string.startswith('0:'):
		total_elapsed_time_string = total_elapsed_time_string[2:]
	return total_elapsed_time_string

#creates the data for the getSumValue function
dataset = #INSERT NAMED QUERY HERE
sumRow = [None, 'Total']
for column in range(2, dataset.columnCount):
	elapsed_time_strings = []
	for row in range(dataset.rowCount):
		elapsed_time_strings.append(dataset.getValueAt(row, column))
	sumRow.append(getSumValue(elapsed_time_strings))
	
#add the 'Total' row to the querried dataset and apply it to the table
event.source.parent.getComponent('Power Table').data = system.dataset.addRow(dataset, dataset.rowCount, sumRow)

Here is the script output as it appeared in my test table:

pascal.fragnoud · January 19, 2023, 10:36am

dataset.getColumnAsList() will do that for you

sumRow = [None, 'Total'] + [getSumValue(ds.getColumnAsList(col) for col in range(2, ds.columnCount)]

justinedwards.jle · January 19, 2023, 10:40am

Thanks. As I've said before, I'm a huge fan of your refactors and optimizations.

pascal.fragnoud · January 19, 2023, 10:43am

Thanks, but in this case... It's an IA function, I can't take the credit ;p

I could brew something with maps and itertools if you want, but I doubt it would be an optimization !
Could be fun though.

justinedwards.jle · January 19, 2023, 10:45am

The other day, I was really hoping you would jump in on this one

pascal.fragnoud · January 19, 2023, 10:52am

Holy %^$!@ that's a whole lot of ifs !

justinedwards.jle · January 19, 2023, 10:55am

That needs a serious refactor. A thousand lines could probably be eliminated from that easily. The whole time I was working on that, I was ~~wondering~~hoping: is Pascal seeing this? I knew if anybody could fix it, you could.

Dharani_T · January 30, 2023, 5:05am

Incase, is there any possibility to display the background of the last row(Total of each column) with different colour?

justinedwards.jle · January 30, 2023, 6:44am

If you are using a power table, you can add the following script to your configureCell extension function:

#def configureCell([...]):
	if selected:
		return {'background': self.selectionBackground}
	elif rowIndex == self.data.rowCount - 1:
		return {'background': '#DDDDDD'}

Result: