I just don´t know where to paste that Json into my custom.vehicleStatus
1 Like
ah, had no idea can do this, thanks
I have a marker for all of our buildings.
I would like to change the color of each marker depending on the Status value that comes from a Named Querie, status can be 1,2,3,4,5
The column BUSSINESSUNIT match the names of the name in my Json...
the column Status show the statues.
[
{
"latitude": 42.472783,
"longitude": -2.40938,
"name": "05-RIO-MENDA"
},
{
"latitude": 42.789632,
"longit
...
The named querie is something like:
SELECT BUSINESSUNIT,
CASE
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 0 THEN 0 -- La sede no ha tenido presencia ni actividad
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 1 THEN 1 -- La sede no ha tenido presencia pero sí actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 0 THEN 2 -- La sede ha tenido presencia pero no actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 1 THEN 3 -- La sede ha tenido presencia y actividad
WHEN ACC00 = 1 AND ACC60 = 0 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
WHEN ACC00 = 1 AND ACC60 = 0 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
WHEN ACC00 = 1 AND ACC60 = 1 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
WHEN ACC00 = 1 AND ACC60 = 1 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
END AS STATUS
FROM
(
SELECT WO.BUSINESSUNIT,
CASE WHEN SUM(CASE
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('00')
THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS ACC00,
CASE WHEN SUM(CASE
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('60','90')
THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS ACC60,
case when SUM(CASE
WHEN WO.WORKTYPE <> 'ACC'
THEN 1 ELSE 0 END)>0 then 1 else 0 end AS LABOR
/*,case when SUM(CASE
WHEN WO.WORKTYPE <> 'ACC' and WOWL.WORKER IN (SELECT WOWL.WORKER FROM WORKORDER WO INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
WHERE WO.COMPANY = 'ORAPP' AND WOWL.COMPANY = 'ORAPP' AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = to_char(CURRENT_DATE,'DD/MM/YYYY') AND WORKTYPE = 'ACC' AND WORKORDERSTATE IN ('00','60'))
THEN 1 ELSE 0 END)>0 then 1 else 0 end AS LABOR0060*/
FROM WORKORDER WO INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
WHERE WO.COMPANY = 'ORANGE' AND WOWL.COMPANY = 'ORANGE'
AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = to_char(CURRENT_DATE,'DD/MM/YYYY')
AND LENGTH(SUBSTR(WO.BUSINESSUNIT,1,9))=9
GROUP BY WO.BUSINESSUNIT
ORDER BY WO.BUSINESSUNIT
)
I guess I would need to add a Transform to the marker but not sure how to bind the data from the named query into this transform
- We'll make your SQL more readable by adding indents and line breaks in the right places. We can also eliminate two of the WHEN lines.
Tidied SQL
-- Returns two columns, BUSINESSUNIT, STATUS
SELECT
BUSINESSUNIT,
CASE
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 0 THEN 0 -- La sede no ha tenido presencia ni actividad
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 1 THEN 1 -- La sede no ha tenido presencia pero sí actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 0 THEN 2 -- La sede ha tenido presencia pero no actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 1 THEN 3 -- La sede ha tenido presencia y actividad
WHEN ACC00 = 1 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
WHEN ACC00 = 1 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
END AS STATUS
FROM (
SELECT
WO.BUSINESSUNIT,
CASE WHEN SUM(
CASE
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('00') THEN 1 ELSE 0
END
) > 0 THEN 1 ELSE 0
END AS ACC00,
CASE WHEN SUM(
CASE
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('60','90') THEN 1 ELSE 0
END
) > 0 THEN 1 ELSE 0
END AS ACC60,
CASE WHEN SUM(
CASE
WHEN WO.WORKTYPE <> 'ACC' THEN 1 ELSE 0
END
) > 0 THEN 1 ELSE 0
END AS LABOR
FROM WORKORDER WO
INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
WHERE WO.COMPANY = 'ORANGE' AND WOWL.COMPANY = 'ORANGE'
AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = TO_CHAR(CURRENT_DATE,'DD/MM/YYYY')
AND LENGTH(SUBSTR(WO.BUSINESSUNIT, 1, 9)) = 9
GROUP BY WO.BUSINESSUNIT
ORDER BY WO.BUSINESSUNIT
)
- On the Map component create
custom.businessUnitsproperty. Bind that to your query and return the data in JSON format. You should get something like,
[
{"BUSINESSUNIT": "05-RIO-MENDA", "STATUS": 5},
{"BUSINESSUNIT": "05-NAV-CORDO", "STATUS": 3},
{"BUSINESSUNIT": "05-CAL-FORJA", "STATUS": 0},
{"BUSINESSUNIT": "05-CAL-RINCO", "STATUS": 2}
]
- Modify the script transform:
Map - script transform on ui.marker property binding
def transform(self, value, quality, timestamp):
def recursiveCopy(original):
# With credit to https://forum.inductiveautomation.com/u/lrose
# https://forum.inductiveautomation.com/t/copy-dictionary-and-modify-it-without-affecting-the-original/62297/9
from copy import deepcopy
from com.inductiveautomation.ignition.common.script.abc import AbstractMutableJythonMap,AbstractMutableJythonSequence
if isinstance(original,AbstractMutableJythonMap):
return {key:recursiveCopy(value) for key,value in original.iteritems()}
if isinstance(original,AbstractMutableJythonSequence):
return [recursiveCopy(item) for item in original]
return deepcopy(original)
def getStatus(businessUnit):
for bu in self.custom.businessUnits:
if bu['BUSINESSUNIT'] == businessUnit:
return bu['STATUS']
return 6 # BUSINESSUNIT not found.
statusColors = ["white", "red", "orange", "purple", "yellow", "green", "black"]
statusMessages = [
'La sede no ha tenido presencia ni actividad.',
'La sede no ha tenido presencia pero sí actividad.',
'La sede ha tenido presencia pero no actividad.',
'La sede ha tenido presencia y actividad.',
'La sede tiene presencia sin actividad.',
'La sede tiene presencia y actividad.',
'Error.'
]
markers = []
for item in value:
protoMarker = recursiveCopy(self.custom.sampleMarker)
protoMarker['lat'] = item['latitude']
protoMarker['lng'] = item['longitude']
s = getStatus(item['name'])
protoMarker['icon']['color'] = statusColors[s]
protoMarker['tooltip']['content']['text'] = item['name'] + ': ' + statusMessages[s]
markers.append(protoMarker)
return markers
- Result:
2 Likes
Hello, works great, thanks a lot,
I just added a timer to make sure businessUnits were loaded in self.custom because the transform script comes before the custom.businessUnits
import time
timeout = 10
start_time = time.time()
while not hasattr(self.custom, 'businessUnits'):
if time.time() - start_time > timeout:
raise AttributeError("Tiempo de espera agotado: self.custom no tiene el atributo 'businessUnits'. Asegúrate de que esté correctamente definido.")
time.sleep(0.1) # esperar 100 ms antes de verificar nuevamente
def recursiveCopy(original):
from copy import deepcopy
from com.inductiveautomation.ignition.common.script.abc import AbstractMutableJythonMap, AbstractMutableJythonSequence
if isinstance(original, AbstractMutableJythonMap):
return {key: recursiveCopy(value) for key, value in original.items()}
if isinstance(original, AbstractMutableJythonSequence):
return [recursiveCopy(item) for item in original]
return deepcopy(original)
def getStatus(businessUnit):
for bu in self.custom.businessUnits:
# Comparar directamente con businessUnit
if bu['BUSINESSUNIT'] == businessUnit:
return bu['STATUS']
return 6 # BUSINESSUNIT no encontrado.
statusColors = ["white", "red", "orange", "purple", "yellow", "green", "transparent"]
statusMessages = [
'La sede no ha tenido presencia ni actividad.',
'La sede no ha tenido presencia pero sí actividad.',
'La sede ha tenido presencia pero no actividad.',
'La sede ha tenido presencia y actividad.',
'La sede tiene presencia sin actividad.',
'La sede tiene presencia y actividad.',
'Error.'
]
markers = []
for item in value:
protoMarker = recursiveCopy(self.custom.sampleMarker)
protoMarker['lat'] = item['latitude']
protoMarker['lng'] = item['longitude']
s = getStatus(item['name'])
protoMarker['icon']['color'] = statusColors[int(s)] # Asegúrate de que el índice sea un entero
protoMarker['tooltip']['content']['text'] = item['name'] + ': ' + statusMessages[int(s)] # Asegúrate de que el índice sea un entero
markers.append(protoMarker)
return markers
Very bad. If you find yourself using sleep() or timer functions in your scripts that then there is something wrong and you should find a better solution.
- Add a Change Script .. to
custom.businessUnits.
Add the line
self.refreshBinding('props.layers.ui.marker')
That will refresh the markers any time the custom.businessUnits changes.
You can use the same script on the other custom tag so that the markers update if the status changes.
1 Like
Ok, did this, working well! thanks Transistor