How to change the http sending value parameter

phildunlap

Hi Jose,

If I understand correctly, you have already gotten the ID and the Info into SQL data points. You could try a change detector on the ID point, has a Set Point event handler setting a new Alphanumeric point (probably a meta point would be best, one that executes on a cron with no context update, a cron like 0 0 0 1 1 ? 2099 ). The set point event handler will "Set to point value" and use the Info SQL point as the source point. Now you can create a point link from that new Alphanumeric Meta point to the outgoing BACnet point, and put whatever transformation logic you want in the point link.

Alternatively (and perhaps more straightforwardly), you could use a scripting data source. Add the ID point to the context varName 'sqlMaxId', add the Info point to the context varName 'info', create a numeric (or multistate, I suppose) Scripting data point, varName 'lastHandledId', add the BACnet point to the context that you'd like to set varName 'dataOut' . Script executes on cron "0/1 * * * * ?" with a script body like....

/* It looks like the strings in your info point aren't exactly these, so do be sure to modify this function as needed */
function transformInfoToValue( infoValue ) {
  if(infoValue == "RESET")
    return 3;
  else if(infoValue == "ACK")
    return 2;
  else if(infoValue == "SILENCE")
    return 4;
  else
    throw "Unknown value for xyz set: " + source.value;
}

if( sqlMaxId.value != lastHandledId.value ) {
  lastHandledId.set( sqlMaxId.value );
  dataOut.set( transformInfoToValue( info.value ) );
}

This scripting solution won't work so well if your poll times for the SQL are less than two seconds, possibly.

jmatos

Almost there... but, no I can not get the ID and the Info into SQL data points... :-(

As you can notice, the info that I need to extract in among other alfanumeric characters in the field info.

So, how can I get those into the SQL data points?

Thanks in advance,
Jose

phildunlap

Hi Jose,

Data source settings;
Row based query: unchecked, false
(you'll have to handle the connection settings yourself using the in-Mango help, but it looks like this is already working for you)

ID data point:
data type numeric, column name "ID"

info data point
data type alphanumeric, column name "info"

As for parsing what you're after out of the info field, I would do that in the script's "transformInfoToValue" function, and not try to do it in the SQL data source. Here's another possible version of that function:

function transformInfoToValue( infoValue ) {
  //We'll use a regex group to get that part of the string
  var fnc = /FNC="([^"]*)"/.exec( infoValue );
  if( fnc === null )
     throw "FNC regex did not match text in info field";
  if( fnc[1]== "RESET" )
    return 3;
  else if( fnc[1]== "ACK" )
    return 2;
  else if( fnc[1] == "SILENCE" )
    return 4;
  else
    throw "Unknown value for xyz set: " + source.value;
}

jmatos

@phildunlap

The scripts senses one error in line 17

dataOut.set( transformInfoToValue( info.value ) );

What could it be?

phildunlap

This is a scripting data source that this script is in, yes? Only the scripting data source can call set()

I would check...

That we're not hitting the throw condition in the transformInfoToValue function. You could change it to return -1; in the else clause and instead do something like...

var transformedValue = transformInfoToValue( info.value );
if( transformedValue >= 0 )
    dataOut.set( transformedValue );

That the variable name for dataOut is the same in the context and the script body. Same for info.
That dataOut and info are settable.
That dataOut and info are enabled.

jmatos

@phildunlap said in How to change the http sending value parameter:

Hi jmatos,

I'm not sure I completely understand, but it sounds like Point Links may work well for you. It sounds like you want to get values from lots of points and publish them all from the same point. Assuming this is for the publisher, a solution could be something like....

Create a virtual point named 'Alarm Out' with an XID of 'almnr' of type alphanumeric

Create a global script, having the body:
function getPrefix() { /*So that you may change the prefix for all scripts later if required */
    return "4";
}
Create a point link '1002 Alarm' from source 'CDI1 – LP_SENSOR_MANUAL_1002/1_1/4194279' to target 'Alarm Out', with the script body:
return getPrefix() + "1002" + source.value;
Create similar point links to 'Alarm Out' from all other points you will publish this way.

Note that the 1002 is hardcoded into the script, since there will be 1 point link from each data point you're publishing here to our 'Alarm Out' point.

If you have only a handful to set up, you can do it by hand. If you have hundreds, I can provide a script to generate the point links for you. A helpful regex for parsing out those DeviceID values could be:
^[^/]+_(\d+)/

Hi Phildunlap,

Following this solution I have tried to change the code of one of the point link to send two alarmOut upon a condition. No success... :-(

Is something like this - wrong code - that I need on the specific point link:

if(source.value == 6)
    return getPrefix() + "0101" + source.value;
    return getPrefix() + "1002" + source.value;
else
    return getPrefix() + "1002" + source.value;

The objective is to send two different alarm out when the source.value meets the condition. In any other cases only sends one alarm out.

It's possible?
Thank you
Jose

phildunlap

Hi Jose,

If you need to do something like that you'll want to use the scripting data source as i described in the paragraph starting with "Alternatively (and perhaps more straightforwardly)" as you could simply call 'dataOut.set( )' twice

Alternatively, you could have a second point link which only returns a value in that special case that you need to set two values.

jmatos

@phildunlap said in How to change the http sending value parameter:

Hi jmatos,

I'm not sure I completely understand, but it sounds like Point Links may work well for you. It sounds like you want to get values from lots of points and publish them all from the same point. Assuming this is for the publisher, a solution could be something like....

Create a virtual point named 'Alarm Out' with an XID of 'almnr' of type alphanumeric

Create a global script, having the body:
function getPrefix() { /*So that you may change the prefix for all scripts later if required */
    return "4";
}
Create a point link '1002 Alarm' from source 'CDI1 – LP_SENSOR_MANUAL_1002/1_1/4194279' to target 'Alarm Out', with the script body:
return getPrefix() + "1002" + source.value;
Create similar point links to 'Alarm Out' from all other points you will publish this way.

Note that the 1002 is hardcoded into the script, since there will be 1 point link from each data point you're publishing here to our 'Alarm Out' point.

If you have only a handful to set up, you can do it by hand. If you have hundreds, I can provide a script to generate the point links for you. A helpful regex for parsing out those DeviceID values could be:
^[^/]+_(\d+)/

Hi phildunlap,

Now that we have buy at IAS the Mango solution we gonna step into the full configuration.

As you say in the last paragraph above, you can provide us a script to generate all the points.

How could this be done?

Thank you in advance,
Jose

phildunlap

Hi Jose,

I'm sure I had a clearer picture in my mind of what I would need to write you when I said that. But, here's a python script to generate JSON you can import on the import/export page. You'll need to edit the paths in the script if you want to use it, and it'll intake a CSV file like:

DP_123456,1003
DP_654321,1004

and output JSON for point links you can import. You may also need to modify the target point XID or other things like that. Here's the python:

import json
from StringIO import StringIO

#use double slashes for windows, i.e. "C:\\path\\to\\new-point-links.csv"
csvPointXids = open("/path/to/new-point-links.csv")
#I will treat this as though it's a CSV with two columns, 0 is source point XID, 1 is the code for the point link body, like 1002

basePointLink = """{
         "xid":"PL_17-3-1_gen_%(loopCounter)d",
         "sourcePointId":"%(sourceXid)s",
         "targetPointId":"almnr",
         "event":"CHANGE",
         "logLevel":"NONE",
         "disabled":false,
         "script":"return getPrefix() + '%(outputNumber)s' + source.value",
         "scriptPermissions":{
            "customPermissions":"",
            "dataPointReadPermissions":"superadmin",
            "dataPointSetPermissions":"superadmin",
            "dataSourcePermissions":"superadmin"
         },
         "writeAnnotation":false
      }"""
	  
#uncomment this if you have a header line to consume
#csvPointXids.readline()

outputConfig = {"pointLinks":[]}
loopCounter = 1
for line in csvPointXids.readlines() :
	data = line.replace("\r","").replace("\n","").split(",")
	if len(data) < 2 :
		continue
	pointJson = basePointLink % {"loopCounter": loopCounter, "sourceXid": data[0], "outputNumber": data[1]}
	#print pointJson
	outputConfig["pointLinks"].append( json.load( StringIO( pointJson ) ) )
	loopCounter += 1
	
csvPointXids.close()

#You need to edit this output path as well. C:\\path\\to\\output.json
output = open("/path/to/output.json", "w+")
output.write( json.dumps( outputConfig, indent=4, sort_keys=False, separators=(",",":") ) )
output.close()

jmatos

Hi phildunlap,

:-( Sorry I can't figure out two things:

A) Where do I run this code?

I have run it in my windows python (3.6) but the only out put is:

{
    "pointLinks",[]
}

B) After I run it I have to import the json file to Mango...?!
This is mainly because I can't understand how the code of basePointLink will pass to json... the previous pointLinks is empty.

Thank you,
Jose

phildunlap

Hi Jose,

I would bet that means there are no lines in your CSV or there is not a comma in the line separating the two fields. You can add a statement like

print line

to the for line in csvPointXids.readlines() : block before the if len(data). to see it print at the command line. If there are no lines in your file you will see no output. I think if you have the file path wrong in the initial open() it will error.

It looks like I got the separators backwards (should be separators=(",",":"), so I edited that to fix. I did test that the script generates JSON (but I didn't import the json)

jmatos

It's working!

Thank you very much. :-)

jmatos

Hi phildunlap,

How do I stop "Failed to send email (.)" messages? Is there any way to disable the email service on settings? I don't need it.

thank you

phildunlap

Hi jmatos,

The event level is set in the "System event alarm levels" section of the system settings as "Email send failure"

You can change this to "Do not log" (event handlers of the "Email send failure" event will be notified, but nothing will be stored in the database) or "Ignore" (nothing will happen).

I would wonder why you're sending emails if they're failing and you do not want them? Did you create an email event handler, or have you set your user to receive event emails above a certain threshhold?

jmatos

Hello,

I have a DataSource with several (hundreds) DataPoints that I have imported and I want to discard. Which is the better an quick way to do it?

Thanks in advance,
Jose

phildunlap

Hi Jose,

You can delete the data source if it's all the points on the data source.

You can use the JSON from your import and this script to generate SQL delete statements:

#!/bin/python
import json

importedPoints = open("/path/to/import.json")
imprt = json.load(importedPoints)
importedPoints.close()

outputFile = open("/path/to/output.sql", "w+")
for dp in imprt["dataPoints"] :
    outputFile.write("DELETE FROM dataPoints WHERE xid='"+dp["xid"]+"';\n");
outputFile.close();

Next step would be running the SQL statements in an sql console.

jmatos

Sorry I do not understand where I "choose" the points to be deleted.

In my mind I have (certainly wrong) exported the DS into an csv file and then in Excel I run a function to remove the DataPoints that I do not want. I was hopping that Mango could inversely import this kind of file.

phildunlap

There is no way to delete points through import/export functions. You can generate the SQL from a CSV really easily, though, just get the XID from a column in the CSV...

#!/bin/python

xidColumn = 3 #change this!
points = open("/path/to/points.csv")
outputFile = open("/path/to/output.sql", "w+")
for line in points.readlines() :
    data = line.replace("\r","").replace("\n","").split(",")
    outputFile.write("DELETE FROM dataPoints WHERE xid='"+data[xidColumn]+"';\n");
outputFile.close();

jmatos

@phildunlap

The project is now installed and working on the client. We are in the real test and pay attention to how it behaves.

To try workaround the issue on C) (c) - In the Virtual DATASOURCE there is one "update period" that makes the systems send the last alarm in that period time. Is there some way to deactivate it?) I set the update period to 300000h (about 34 years). Do you see any inconvenience on that?

Thank you very much

phildunlap

Yeah that's fine. What'll happen is every time the data source is enabled or disabled it will poll once, and then it'll be negligible load on the system.

In Mango 3 I added a feature to the virtual data source to disable its polling entirely.