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:
