#!/usr/bin/perl -w

# by Mark Holler  Started 1/29/13
# creating command interface 2/7/13 Got commands going into commands table. 
# moved to camnets5 to add actuation from here.  7/6/13
# First got actuation working 7/10/13 M.H. 
# Got Actuation queue working through sqlite table. 7/24/13 M.H. 
# Cleaning up the U.I.   7/25/13 M.H.   
# Adding Base radio configuration 11/29/13 M.H. 
# Added Timeout of command if no response.  12/2/13 M.H. 
# Don't resend commands, mark sent and don't resend 12/14/13 M.H. 
# Added link to commissioing page  12/15/13 M.H. 

use strict;
use CGI qw(:standard);
use RRDs;
use DBI;

my $netID = "";  
my $nodeID = "";
my $nodeHistory =5400; # Length of node history that is of interest (seconds). 172800= 2 days, 86400=1 day 
my $networkHistory = 86400; # Length of network connect history of interest in seconds. 
my $commandHistory = 7200; # Command History in seconds.  7200 = 2 hours.

#************** Connect to cs3 sqlite DB;  snetServe creates this DB and table  ************************************
my $dbh = DBI->connect('DBI:SQLite:/home/user/sqliteDB/cs3.sqlite')
            or print "Couldn't connect to database: ". DBI->errstr."\n";

#************ Display Title, Logo and Links to Trend Charts *********************************************************
print header;
#print '<META  HTTP-EQUIV="REFRESH" CONTENT="20"/>'; # refresh this page every 25 seconds automatically. O.K if form boxes are cleared 
# with an execute when all boxes are empty,  The refresh reexecutes the null command.   

print "<title>CS3 Command/Control</title>";
print '<font size="6" color="#666633"><img src="logo100CamNets.jpg" alt="" width="143" height="94" align="right"><strong>Camalie Networks System 3</strong></font><font color="#666633"><strong><span class="style7"></span></strong></font></p>';
print '<p><font size="4">Manual Command and Control -- All Networks, Nodes, Pins, and Commands</font></p>';
print '<a href="http://camalie.net/cgi-bin/drraw.cgi?Mode=view;Dashboard=1373397558.7569">Trend Graph Dashboard for CS3 Networks on camalie.net </a><br>';
print '<a href="http://camalie.net/cs3/cs3Config.cgi">Port/Node Configurations CS3 Networks on camalie.net</a><br>';
print '<a href="http://camalie.net/cs3/cs3Commission.cgi">Node Commisioning CS3 Networks on camalie.net</a><br><br>';

#**********  ENTER a COMMAND and Execute it  ********************************************************************************
print '<form action="../cs3/cs3Control.cgi" id="actForm"> '; #<!--form ends at end of code, submit reexecutes this page -->
print "Command to Execute: <br>";
print 'Network <input type=\"text\" id=netID size="8" value="" align=right name=netID /> Node <input type=\"text\" id=nodeID size="11" value="" align=right name=nodeID /> Command <input type=\"text\" id=command size="4" value="" align=right name=command /> Value<input type=\"text\" id=commandValue size="4" value="" align=right name=commandValue /> ';
print '<input id="refreshx" type="submit" name="executeCommand" value="Execute"/>'; # Execute button
print '<font size="1">  Some Values: 4=Digital LOW/OFF, 5=Digital HIGH/ON, 2=Analog Input</font>';

# Store command to sqlite database, Format:
  # (NetID INTEGER, nodeID INTEGER, command TEXT,targValue TEXT, cmdTime INTEGER,  respTxt TEXT, respInt INTEGER, respTime INTEGER  )
if (param('netID') && param('nodeID') && param('command')) {  #a little command parameter checking here.  
   my $sth4 = $dbh->prepare('INSERT INTO commands VALUES (?,?,?,?,?,?,?,?)')
      or print "Couldn't prepare command table creation statement: " .$dbh->errstr."\n";
   my $unixTime = time; #cmdTime
   my $netID = param('netID');
   my $nodeID = param('nodeID');
   my $command = param('command');
   my $commandValue = param('commandValue');
   #print "command to store: $netID, $nodeID, $command, $commandValue, $unixTime, xyz<br>";
   if ($sth4->execute("$netID",$nodeID, $command, $commandValue, $unixTime ,"xx","yy","zz"))  {
      #print "Successful store of command to sqlite table. ";
   }else {
      print "Couldn't store command to sqliteDB cs3.sqlite-table commands " . $sth4->errstr."\n";
   }
} #end if command content submitted

#<!-- *************  Generate Recent Commands Table *****************-->
my $commandHistoryMin = $commandHistory/60;
print "<h4>Commands Executed in last $commandHistoryMin minutes</h4>";
print '<p><table border="1" cellpadding="2" >';
print '<tr>';
print '<td>NetID</td>';
print '<td>NodeID</td>';
print '<td>Command</td>';
print '<td>Target</td>';
print '<td>Read</td>';
print '<td>Status</td>';
print '<td>Command TimeUnix</td>';
print '<td>Command Time</td>';
#print '<td>Response Time</td>';
print '<td>Execution Delay (sec.)</td>';
print '<td>Command Age (sec)</td>';
print '</tr>';

my $commandsSince = time - $commandHistory;
#print " current time=$unixTime, commands Since $commandsSince <br>";
#my $sth3 = $dbh->prepare('SELECT * FROM commands')
my $sth3 = $dbh->prepare('SELECT * FROM commands WHERE cmdTime > ?')
            or print "Couldn't prepare select commands statement: " .$dbh->errstr."\n";
#   $sth3->execute()
   $sth3->execute($commandsSince)
            or print "Couldn't execute select commands " . $sth3->errstr."\n";
while( my $commandRowHashRef = $sth3->fetchrow_hashref()){
   my $CAcmdTime=localtime($commandRowHashRef->{cmdTime});
   my $CArespTime = "";
   my $execDelay = "";
   my $cmdAge = "";
      $cmdAge = time - $commandRowHashRef->{cmdTime};
   if ($commandRowHashRef->{respTime} != "zz") {
      $CArespTime=localtime($commandRowHashRef->{respTime});
      $execDelay = $commandRowHashRef->{respTime} - $commandRowHashRef->{cmdTime}; # Time to execute command in seconds.
   }
   #print "$commandRowHashRef->{NetID} $commandRowHashRef->{nodeID} $commandRowHashRef->{command} $commandRowHashRef->{targValue}  $commandRowHashRef->{cmdTime} $CAcmdTime  $commandRowHashRef->{respTxt} $commandRowHashRef->{respInt} $commandRowHashRef->{respTime}  <br>";
   print '<tr>';  
   print "<td>$commandRowHashRef->{NetID}</td>";
   print "<td>$commandRowHashRef->{nodeID}</td>";
   print "<td>$commandRowHashRef->{command}</td>";
   print "<td>$commandRowHashRef->{targValue}</td>";
   print "<td>$commandRowHashRef->{respInt}</td>";
   print "<td>$commandRowHashRef->{respTxt}</td>";
   print "<td>$commandRowHashRef->{cmdTime}</td>";
   print "<td>$CAcmdTime </td>";
#   print "<td>$commandRowHashRef->{respTime}</td>";
#   print "<td>$CArespTime</td>";
   print "<td>$execDelay</td>";
   print "<td>$cmdAge</td>";
   print "</tr>";
} # end while()
print '</table>';
print '</p>';
print '</form>';

my $nodeHistoryMin=$nodeHistory/3600;
print "<h4>Nodes Active in last $nodeHistoryMin Hours</h4>";

#<!-- *************  Generate Active Nodes Table  *****************-->
print '<p><table border="1" cellpadding="2" >';
print '<tr>';  
print '<td>NetID</td>';
print '<td>Node #</td>';
print '<td>Node Name</td>';
print '<td>Location/Notes</td>';
print '<td>Last Heard</td>';
print '</tr>';

my $nodesSince = time - $nodeHistory;
my $sth2 = $dbh->prepare('SELECT * FROM nodes WHERE atTime > ?')
   or print "Couldn't prepare nodes table creation statement: " .$dbh->errstr."\n";
$sth2->execute($nodesSince)
   or print "Couldn't execute select all nodes " . $sth2->errstr."\n";
while( my $nodeRowHashRef = $sth2->fetchrow_hashref()){
   my $CA2time=localtime($nodeRowHashRef->{atTime});
   print '<tr>';  
   print "<td>$nodeRowHashRef->{netID}</td>";
   print "<td>$nodeRowHashRef->{nodeID}</td>";
   print "<td>$nodeRowHashRef->{nodeName}</td>";
   print "<td>$nodeRowHashRef->{location}</td>";
   print "<td>$CA2time </td>";
   print "</tr>";
} # end while()
print '</table>'; 
print '</p>';
print end_html();


#**********************  Function Implementations ********************************************

#**********************  Test to see if a table already exists *******************************
sub tableExists($$) {  # takes two parameters, database handle and  tablename string 
   #print "Checking for table $_[1] \n";
   my @tableNames = $_[0]->tables("main", '%', "$_[1]" ,"TABLE")
            or print "table $_[1] doesn't exist yet " .$_[0]->errstr."\n";
   #print "Checked for table $_[1] \n";
   if (@tableNames) {
      #print "Table $_[1] exists; ";
      return 1;
   } # end if
} # end sub tableExists()  *******************************************************************

#Leftovers
#my $rows = $sth->dump_results();