SQL:
SELECT
AP.PARENTASSET || '-' || WO.BUSINESSUNIT AS BU,
WO.WORKORDERNAME,
TO_CHAR(WO.WORKORDERDATE, 'MM') || '^' || TO_CHAR(WO.WORKORDERDATE, 'YYYY') AS LOC,
CASE
WHEN SUM(
CASE
WHEN (NOT WOOP.POSITION = 1 AND WOOP.OPERATIONSTATE IS NULL OR NOT WOOP.OPERATIONSTATE = 'OK')
THEN 1
ELSE 0
END
) > 0
THEN 1
ELSE 0
END AS STATES
FROM
WORKORDER WO
JOIN
WORKORDEROPERATION WOOP ON WOOP.WORKORDER = WO.WORKORDER AND WOOP.COMPANY = WO.COMPANY
JOIN
OPERATION OP ON OP.OPERATION = WOOP.OPERATION AND WO.COMPANY = OP.COMPANY
JOIN
ASSETPARENTASSET AP ON WO.COMPANY = AP.COMPANY AND WO.ASSET = AP.ASSET
JOIN
ASSET A2 ON AP.COMPANY = A2.COMPANY AND AP.PARENTASSET = A2.ASSET
WHERE
WO.COMPANY = 'APPLE'
AND A2.COMPANYLEVEL = 2
AND WO.WORKPROCEDURE = 'SIS10'
AND WO.WORKORDERDATE BETWEEN :StartDate AND :EndDate
GROUP BY
WO.WORKORDERDATE,
AP.PARENTASSET || '-' || WO.BUSINESSUNIT,
WO.WORKORDERNAME
ORDER BY
1
Script transform 1:
def transform(self, value, quality, timestamp):
if quality.isGood() and value is not None:
py_data = system.dataset.toPyDataSet(value)
month_year_set = set(row['LOC'] for row in py_data)
month_year_list = sorted(list(month_year_set))
columns = ['Sede', 'WORKORDERNAME'] + month_year_list
data = []
WORKORDERNAME pair
data_dict = {}
for row in py_data:
sede_workorder = (row['BU'], row['WORKORDERNAME'])
if sede_workorder not in data_dict:
data_dict[sede_workorder] = {month: None for month in month_year_list}
data_dict[sede_workorder][row['LOC']] = row['STATES']
for (sede, workorder), month_data in data_dict.items():
new_row = [sede, workorder] + [month_data[month] for month in month_year_list]
data.append(new_row)
new_dataset = system.dataset.toDataSet(columns, data)
return new_dataset
else:
return value
Script transform 2 for styling the columns,
def transform(self, value, quality, timestamp):
if quality.isGood() and value is not None:
output = []
py_data = system.dataset.toPyDataSet(value)
for row in py_data:
newRow = {}
for col in py_data.getColumnNames():
cell_value = row[col]
if cell_value == 1:
bgColor = "#FF555588"
fontColor = "#ffffff"
display_value = ""
elif cell_value == 0:
bgColor = "#50BE8788"
fontColor = "#FFFFFF"
display_value = ""
else:
bgColor = ""
fontColor = ""
display_value = cell_value
newRow[col] = {
"value": display_value,
"style": {
"backgroundColor": bgColor if bgColor else "transparent",
"color": fontColor,
"fontWeight": "bold" if bgColor else "normal",
"textAlign": "center",
"borderLeft": "1px solid #FFFFFF" if bgColor else "none",
"borderRight": "1px solid #FFFFFF" if bgColor else "none"
}
}
output.append(newRow)
return output
else:
return value
When I aply script transfrom 2 to style the cells red and green the order change.
Current result:
the original data from the query comes with all months in one column
maybe I should find another way to aply the style to the cell