Sump pump Monitor

sump.pl

#!/usr/bin/perl
 
use Device::SerialPort;
use DBI;
 
require 'sump.inc.pl';
 
# Set up the serial port
# 19200, 81N on the USB ftdi driver
my $port = Device::SerialPort->new($SERIALPORT);
$port->databits(8);
$port->baudrate($SERIALBAUD);
$port->parity("none");
$port->stopbits(1);
 
$count=0;
$OldPump=-1;
$port->read(255);
if($dbh = DBI->connect('DBI:mysql:'.$database.":$host",$user,$pw)) {
	$sql="INSERT INTO `$database`.`pump` (`dt`,`status`) VALUES (CURRENT_TIMESTAMP , NULL);";
#print $sql."\n";
	$c = $dbh->do($sql);
	$sql = "INSERT INTO `$database`.`$tablename` (`dt`, `level`) VALUES (CURRENT_TIMESTAMP, NULL);";
	$c = $dbh->do($sql);
	if(!$quiet) {print " Added $c NULL row\n";}
sleep(1);
$oldlevel=500.0*$DMAX;
	while(1) {
		$port->write("\$S1,Q#");
		$data="";
		while($data !~ /\$C0.*\#/) {
			$data.=$port->read(255);
		}
		@values=split(/,/,$data);
		$levela=(hex($values[3])/256.0)/10.23;
		$levelb=(hex($values[4])/256.0)/10.23;
print $values[5];
print "  ";
		@bin=dec2bin(hex($values[5]));
		if(@bin[1]=='0') {
			$Pump=1;
		} else {
			$Pump=0;
		}
		if($Pump!=$OldPump) {
			$sql="INSERT INTO `$database`.`pump` (`dt`,`status`) VALUES (CURRENT_TIMESTAMP , '$Pump');";
			$c = $dbh->do($sql);
			$OldPump=$Pump;
 
			$sql = "INSERT INTO `$database`.`$tablename` (`dt`, `level`) VALUES (CURRENT_TIMESTAMP, \'$level\');";
			$c = $dbh->do($sql);
			if(!$quiet) {print " Added $c rows";}
			$oldlevel=$level;
			$count=0;
 
 
		}
 
 
		$level=sprintf("%.3f", $levela);
		$levelb=sprintf("%.3f", $levelb);
		$delta=abs($level-$oldlevel);
		print "Count: $count\tA: $level\tB: $levelb\tDelta_A: ";
		print sprintf("%.3f",$delta);
		print "/$DMAX";
		print "\tPump: ";
		if($Pump) {
			print "On ";
		} else {
			print "Off";
		}
		if($DMAX<$delta or $count++>$MAXDURATION) {
			$sql = "INSERT INTO `$database`.`$tablename` (`dt`, `level`) VALUES (CURRENT_TIMESTAMP, \'$level\');";
			$c = $dbh->do($sql);
			if(!$quiet) {print " Added $c rows";}
		$oldlevel=$level;
		$count=0;
		sleep($SLEEPDUR);
		} else {
			sleep($SLEEPDUR);
		}
		print "\n";
	}
 	$dbh->disconnect();
} else {
	if(!$quiet) {print "Could not connect to database: $DBI::errstr";}
}
 
$port->close();
 
sub dec2bin {
    my $str = unpack("B32", pack("N", shift));
    $str =~ s/^0+(?=\d)//;   # otherwise you'll get leading zeros
    @bin = split(//, reverse($str));
    return @bin;
}

sump.inc.pl

# MySQL CONFIG VARIABLES
$host = "127.0.0.1";
$database = "database";
$tablename = "table";
$user = "user";
$pw = "pass";
 
#$DMAX=1.0;
 
$SERIALPORT="/dev/ttyUSB0";
$SERIALBAUD=19200;
 
#Delta needed for SQL update
$DMAX=0.1;
 
$MAXDURATION=600;
 
#Sleep duration between updates/checks, 1 second minimum
$SLEEPDUR=1;

sump.sql

-- --------------------------------------------------------
 
--
-- Table structure for table `pump`
--
 
CREATE TABLE IF NOT EXISTS `pump` (
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY  (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `pump2`
--
 
CREATE TABLE IF NOT EXISTS `pump2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=161 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `sump`
--
 
CREATE TABLE IF NOT EXISTS `sump` (
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `level` float DEFAULT NULL,
  PRIMARY KEY  (`dt`),
  KEY `level` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

config.inc.php

<?
$MYSQL_HOST='127.0.0.1';
$MYSQL_USER='user';
$MYSQL_PASS='pass';
$MYSQL_DB='dbname';
$MYTABLE='sump';
 
#Warn if pump is on for longer than defined time
$PUMPWARN=5*60;	# 5 minutes

$INTERVAL=2;
$YRANGE="5:35";
$datadir='/var/www/sump/data/';
$gnuplot='/usr/bin/gnuplot';
 
?>

/etc/init.d/sump

#!/bin/sh
CMD='/root/sump/sump.pl'
 
test -x $CMD || exit 0
 
. /lib/lsb/init-functions
 
PROG=`basename $CMD`
 
 
 
 
STARTCMD="screen -d -m -S \"Sump\" $CMD"
 
case "$1" in
  start)
	log_daemon_msg "Starting Sump Monitor" "$PROG"
	if $STARTCMD; then
	    log_end_msg 0
	else
	    log_end_msg 1
	fi
	;;
  stop)
	log_daemon_msg "Stopping Sump Monitor" "$PROG"
	if kill `pgrep $PROG`; then
	    log_end_msg 0
	else
	    log_end_msg 1
	fi
	;;
 
  status)
	status_of_proc -p /var/run/sump.pid $CMD $PROG && exit 0 || exit $?
	;;
 
  *)
	log_action_msg "Usage: /etc/init.d/sump {start|stop|status}"
	exit 1
esac
 
exit 0

2.5, 2.7, 3mm tubing. MPXV5004 Datasheet

I want to use a MP3V5050DP pressure sensor and air pump to monitor the water level in a cistern.