Can Lookout provide database management and reporting?

[SCADADog]

I need to display current readings (and calculated values) collected on an hourly basis, allow users to retrieve/display historical values for a specified time period, allow users to change "bad" values and write (replace) these value in the database, and print formatted reports as needed.  Assuming all capabilities exist within Lookout, any suggestions on best approaches?  Not looking to re-invent the wheel.  Also, if using external apps is a better course, please advise.  I need to provide all functionality within the operator environment and anything I use must be Win7-compliant.

[Mike@Forshock]

You cannot change the values in the Citadel database through ODBC, not really anyway I know of actually.

That being said, we developed a PHP/MySQL system for our clients to do almost exactly what you are asking.  The is built for our system "templates" and are not really designed as a cookie-cutter system.

Pull data over ODBC to excel, Access, SQL Server, etc.

This would allow you full control of the data (such as "editing" numbers) and you can then present however you would prefer.

[SCADADog]

Mike,

I was pretty sure that you cannot modify values in the Citadel DB though ODBC but hoping someone knew something different.  Is your PHP/MySQL system setup to be a product that you sell?  I am interested in talking further about it.

Thanks,

Bill

[Mike@Forshock · Accepted solution]

It is heavily tailored towards our systems (think templates).

That being said, what we do is very simple (Will show runtimes, starts and stops for pumps.)

BS = Status, BM = Monitor/Running, BC = Control/Run, $f_item["db_column"] = folder name in Lookout\Citadel

We take the values from ODBC via SQL  command to Citadel

PHP Code:

$f_sql = "SELECT LocalTime, \"etm{".$f_item["db_column"]."\BC}\", "
."\"starts{".$f_item["db_column"]."\BC}\", \"stops{".$f_item["db_column"]."\BC}\", "
."\"etm{".$f_item["db_column"]."\BM}\", \"starts{".$f_item["db_column"]."\BM}\", "
."\"stops{".$f_item["db_column"]."\BM}\", \"etm{".$f_item["db_column"]."\BS}\", "
."\"starts{".$f_item["db_column"]."\BS}\", \"stops{".$f_item["db_column"]."\BS}\" "
."FROM TRACES WHERE LocalTime >= \"".$f_report_start_true." 23:59:59\" AND LocalTime <=\"".$f_report_stop." 23:59:59\" AND Interval=".$f_report_interval;

$result = odbc_exec($f_db_lookout, $f_sql);

We then loop through the data and decide what we want to do with it.

So if wanted a CSV file:

PHP Code:

$f_rows=0;
$f_csv_data = '"Date","BC-ETM","BC-Starts","BC-Stops","BM-ETM","BM-Starts","BM-Stops","BS-ETM","BS-Starts","BSStops"'."\n";
while(odbc_fetch_row($result)){
$f_rows++;
if($f_rows>1){ //Toss first row!
$f_csv_data .= '"'.odbc_result($result, 1).'",'
. '"'.odbc_result($result, 2).'",'
. '"'.odbc_result($result, 3).'",'
. '"'.odbc_result($result, 4).'",'
. '"'.odbc_result($result, 5).'",'
. '"'.odbc_result($result, 6).'",'
. '"'.odbc_result($result, 7).'",'
. '"'.odbc_result($result, 8).'",'
. '"'.odbc_result($result, 9).'",'
. '"'.odbc_result($result, 10)."\"\n";
}
} //END WHILE
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"".$f_item['display_name']." (".$f_report_start." to ".$f_report_stop.")..csv\"");
echo $f_csv_data; // We should offer as a download