Report Date

I want to run a report, within a certain date (Date Range) and also with a condition (Radio Button).

The query is something like:
select this,that,other from table where date >= start and date <= end

here’s the condition: if one radio button is depressed, the query needs to have ‘AND number = 12345’

There is a MySQL IF statement, but that only returns a scalar, not a complete query.

When setting up the data for the Report Designer (or any control), there is an option for Expression and an option for SQL Query. Is there some way to make a hybrid, so FPMI will do a little processing (IF statement) and also SQL. Perhaps there could be a Query command in the function list for the Expression page or the ability to interpret expression syntax in the SQL Query page.

Another way I saw to do this was to have a separate button called ‘Run Report.’

report.data = toDataSet(runQuery) would retrieve the data.

The main snag here seems to be the date format for the Date Range. Using a Message Box, I ran the query and it was in a format like ‘Jan 01 00:00:00 CST 2008.’ There is a MySQL str_to_date function that allows one to specify the format, but unfortunately it doesn’t have the ‘CST’ or timezone option. When using the ‘SQL Binding’ option for the Report Control, this is automagically handled, and the select works. Am I missing something here? Is there a way to adjust the date format so it returns something proper to MySQL?

Thanks for the help,
Matt

Absolutely! In fact, this technique is really important to making interesting data-rich scada apps. You actually hit it right on the head. Use an expression to create the actual SQL query (or parts of it). Store this query in a String dynamic property. Then bind the dataset to a SQL query binding, where the query is that dynamic property. Ta-da - a dynamically generated SQL query.

So your query property's expression might look like this:

"SELECT blah, blah2, blah3 FROM mytable WHERE date >= '" + {Root Container.DateChooser.startDate} + "' AND date <= '" + {Root Container.DateChooser.endDate} + if({Root Container.optionRadioButton.selected, "AND number=12345", "")

and then you sql query binding would simply look like

{Root Container.queryVariable}

Hope this helps,

Matth,

Here’s some helper methods that I’ve developed for working with dates:

[code]def getNow():
#Returns a java.util.Date
import java.util.Date
return java.util.Date()

def formatDate(Date = None, DateFormat = ‘yyyy-MM-dd HH:mm:ss’):
import java.util.Date
import java.text.SimpleDateFormat
if Date is None:
Date = java.util.Date()
if Date.class != java.util.Date:
return None
try:
sdf = java.text.SimpleDateFormat(DateFormat)
return sdf.format(Date)
except:
return None
[/code]

Here’s an example:

print formatDate(getNow())

Take a look at the following link for the java.text.SimpleDateFormat class to get more information on formatting options:

http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html

The default format I used is the default for MS SQL Server. You might want to change it to a more appropriate format for MySQL.

A word of caution, a lot of the java.util.Date properties/methods are deprecated. The more up-to-date approach is to use calendar classes (e.g., GregorianCalendar) which are a lot more sophisticated and complicated.

Use in good health!

For date formatting, you might want to use fpmi.db.dateFormat, no java knowledge required.
Manual page is here:
inductiveautomation.com/prod … dateFormat

Java’s calendar class is very useful. To get an instance of one, you’d do something like this:

import java cal = java.util.Calendar.getInstance()

There is also the python calendar (docs.python.org/lib/module-calendar.html) module available for your use.

So, script-o-philes take heart: date manipulation options abound!

Carl,

Touche! I’m making a habit of re-inventing the wheel.

I developed my methods because I was looking for a way to get the current date/time to initialize some calendar components on some FPMI windows. I didn’t see any thing in the FPMI manual on how to do that but then again, I’ve been known to miss obvious stuff. (I think you guys referred to this as the “refrigerator syndrome”)

Now that I think about it, I could have used a SQL Server query to get the current date/time, e.g., SELECT GETDATE(). However, I’m guessing that using my method would be quicker and less resource intensive than using a SQL query. Is that truly the case?

I’m still a little confused, though, about the java.util.Calendar class. The documentation I’ve seen indicates that it’s an abstract base class. My understanding, which is by no means complete, is that one can’t use this class directly but would need to use an instance of a subclass such as the GregorianCalendar. Am I missing something here?

MickeyBob

Getting the date on the client side is theoretically less resource intensive in the grand scheme of the architecture - more specifically, it puts the computing where you want, client side, as opposed to the network, gateway, and SQL database. Keep in mind that you still want the client to be as lightweight as possible, but given that, utilize those resources. But, getting the date from the SQL database should be considered totally negligible in terms of load. I’d recommend getting the date with the CurrentDateTime Client SQLTag. The other advantage is that the datasource doesn’t have to be up.

CURRENT_DATETIME is the more standard to SELECT than a flavor specific date function. I’m not sure where the standard comes from - maybe ANSI SQL.

Sure, but like Nathan said, its not going to be a big deal either way. I'd stick with getting the date from client side scripting if you already have that done. The only potential for goofs is when the client's machine is in a different timezone than the database server.

Yeah, it is an abstract class, which is why you can't make a new one. But calling the static function Calendar.getInstance() will return a concrete subclass of Calendar appropriate to the computer's Locale. So, in most countries, thats the Gregorian Calendar, like you thought.

Hope this helps,

Nathan & Carl,

I dug into this a little more. It appears to me that CURRENT_DATETIME is specific to Oracle.

I also checked on MySQL and it looks like they, as does MS SQL, use a function approach: (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)

Is there a way to access SQLTags from a script? For example, I want to have a button on one window to open another window containing a calendar component with its Date property set to the current date/time (or some value related thereof). I would likely do that by creating a dynamic Date property on the calendar window called "DefaultDateTime" and link it to the Date property of the calendar component. I would then pass the "DefaultDateTime" as a parameter to the fpmi.nav.openWindow() method, e.g.,

fpmi.nav.openWindow("CalendarWindow", {"DefaultDateTime":methodToGetSQLTagValue("CurrentDateTime")})

Sorry Matth! I've kinda hijacked your thread.

MickeyBob

Yeah, its "CURRENT_TIMESTAMP" that is the standard. I'm sure this works on what we consider the 'big 4', MS Sql Server, MySQL, Oracle, and PostgreSQL.

Yeah, its

fpmi.tag.getTagValue("[System]Client/System/CurrentDateTime")

But, even easier, for what you described, drop the custom property and the parameter passing, and just bind the date property of your calendar to the expression:

now(0)

This will assign the current date to the date property when the window starts up, and the 0 poll-rate argument will make sure the expression never runs again.

Ahhh, that why I was confused. It's "CURRENT_TIMESTAMP", NOT "CURRENT_DATETIME". That's what I get for reading too fast. It's wonderful to learn something new every day.

[quote="Carl.Gould"]Yeah, its

fpmi.tag.getTagValue("[System]Client/System/CurrentDateTime")

Ok, another case of "refrigerator syndrome". I remember reading about this in the manual but didn't make the connection.

[quote="Carl.Gould"]But, even easier, for what you described, drop the custom property and the parameter passing, and just bind the date property of your calendar to the expression:

now(0)

This will assign the current date to the date property when the window starts up, and the 0 poll-rate argument will make sure the expression never runs again.[/quote]

I tried looking in the FPMI manual on now() but found nothing in the Expression Language section. Is it missing or do I have a chronic case of the Refrigerator Syndrome? However, I do see it in the expression editor.

This and runScript() are the only expressions I know of with a parameter that controls the poll rate. Are there others?

If I were to use an expression with now() nested, would the expression be evaluated repeatedly or just once? e.g.,

dateArithmetic(now(0), 5, "hour")

Note: In my specific case, I typically want to initialize the calendar component to midnight of the current or previous day, and not the current time, so in practice I would likely have to use a more complex expression.

Thanks for the help and your patience with me.

Carl,

I’ve answered my own question re: whether the expression would be executed once or repeatedly. If I specify now(0), it’s only updated once. If I use now(1000), it’s updated every second. It makes sense.

If I want to initialize the calendar component to midnight of the current day, the following expression works great!

toDate(dateFormat(now(0), "yyyy-MM-dd 00:00:00"))

Thanks again.

1 Like

Clever expression!

Does now() get the date/time from the Gateway? If not, it would be convenient to be able to do so.

Guys,

I still have a problem/challenge. The expression above works great for initializing the calendar component. But suppose I need some pushbuttons that set the calendar date to “predefined” values such as midnight, noon, beginning of first shift (e.g., 7:00 am), etc. I know how to do that with my custom date methods (e.g., getNow(), getMidnight(), etc.) but is there a better way?

Thanks

Yeah, the now() expression was in the manual, but somehow it had been forgotton from the table of contents - oops! That is corrected for 3.1.6.

now() and runScript() are (currently) the only expressions that poll. All other expressions do not poll. Bound property expressions trigger an expression update when the property they’re bound to changes.

now() pulls its time from the OS - not the Gateway.

As for the pushbuttons - I think your custom date methods (implemented in Jython, using either Java or Pythons date functionality) are the way to go. Expressions wouldn’t really be appropriate for this, since its based on a pushbutton action.