Build: Facility lock monitor, broadcast status

Community Exhibition / Gallery
1

First time posting, first Arduino project, etc.
While I'm waiting on my hardware, I wanted to begin by outlining the project, what better place to do than here.

The issue at hand is my warehouse is sometimes left unlocked by any number of people for any reason... cheapest fix is to set the rules but it will be far easier and WAY more fun to monitor the lock status with a simple tool as follows.

Arduino will monitor digital lines on 7 doors by way of proximity sensors at the deadbolt.
In a loop, it will compare each line to a last set value and if not equal, begin broadcast procedure.

The warehouse is prone to lightening hits so tying anything excessive into my network is frowned upon. The YUN will be a suitable device for this since I have Wifi there.

I could set the YUN to act as a web server but my firewall needs to remain 100% and I'm double NAT'ing so it will be more appropriate to broadcast to a web server using cURL calls.

The receiving end of the status updates is a single php page which will populate a MySQL table. Another php page will present the data as recorded. Further options and notification tweaking (sms) is to be decided as the project becomes more complete.

I guess this is a pretty good start at Arduino. At the least, I like the idea that it will be useful.
I welcome any advice but its not necessary.
Happy New Year!

Future projects after this: Employee Time Clock, Miniature working model of a massive composting system we manufacture, Fire-proof safe for networked hard drive cooled by peltier system.

2

Skip the employe timeclock.

3

Sorry, I don't follow your loose sarcasm.

I 100% need a system to perform a time calculation and keep a digital record for accounting purposes and I am not interested in spending thousands on an overrated mainstream system. My current system requires paper, a punch, a central location and someone better than the idiot I currently have, tabulating times.

Is the preprogrammed chip related to the timeclock comment? Is that a facetious comment regarding the idea that the cost involved for properly recording employee time should cost no more than 90 cents per person?

Please be more constructive, as excessive sarcasm is more harmful to a community than it is useful for fleshing out mooching noobs.

4

libelle:
... and someone better than the idiot I currently have, tabulating times.

Not sure I can find the most appropriate (non-animated) smiley for this but ... XD

5

What I ment is that the Arduino does not have a RTC.

It will cost you more than the Arduino to make it keep track of time.

6

janost:
What I ment is that the Arduino does not have a RTC.

It will cost you more than the Arduino to make it keep track of time.

That certainly isn't true. The below $20 shield contains both a RTC and a SD socket which both would make
a good start on an employee time keeping system.

7

janost:
What I ment is that the Arduino does not have a RTC.

It will cost you more than the Arduino to make it keep track of time.

Ahhh! Thanks for the info. I was aware. But just like this project, I'm only using the Arduino as a notification tool. It sends the message and the database server then logs the time. No biggie, just follow me.
(that's a good shield, lefty)

Back on subject, I'll work my way from the cloud to the ground.

I'll need the database for simplicity of data handling between pages and session timeout issues. 4 fields are enough I suppose: index for line item handling, door, status, and a time stamp. Any ideas on what else I may need to record?

using phpMyAdmin, it might look something like the attached picture.

lockTable.jpg
lockTable.jpg1173×639 91.1 KB

8

To load the db, we need to write some php. This ONLY moves the door's state change sent by the Arduino into the db.

Populate a db access variable in acceptable MySQL format:

	$dbconn = mysqli_connect("localhost", "user", "pass", "db");

and populate a variable for use in an error check:

	$query = mysqli_query($dbconn, "INSERT INTO locklogging (door,state) VALUES ('$door','$state')");

but before I execute this, I must capture the POST data from the cURL the Arduino will send which is only the door and it's state. date() is a php function where we get the time stamp:

	$door=$_GET[door];
	if ($_GET["status"] == 1)
		$status = TRUE;

then by comparing $query result with false, the mysql_query() is performed. If true, we will be returning a code originally sent to us by the Arduino for a test of success for a specific transaction. Haven't tested this, don't know if its even necessary, but I'm implementing it right off the bat. Lastly clearing the variables so nothing gets accidentally re-used.

	if ( $query != FALSE )
		$msg = $code;
	else
		$msg = NULL;
	
	$door=$status=NULL;
	echo $msg;

NOTE: none of this has been verified - I have written this out from the top of my head and have left out a number of error checking. Plus I make no claim to being a professional coder, I use what works for me.
Cheers

Edit: corrected some details

9

I should correct myself here regarding cURL. I'm not up on the exact method of cURL but instead intend to use POST/GET data sent like html Forms. I can see that there could be a security of data usage if someone were so inclined but really, who cares. If anyone has advice on an alternative method of data transfer that requires little overhead, I'm all ears.

Down to displaying recorded data.

This set of code will reside on the same page as the data storage part. These each reside in an IF/ELSE statement where IF this "code" has a value, we will record the data, but if the page is called w/o anything, as a user would by typing in the file name (lock.php), you get a print out of the recorded lock states.

To keep it simple for coding, the doors are numbered and we just use that in a FOR statement. 7 doors.

	for($doornum = 1; $doornum <= 7; $doornum++){

Then, for each iteration, we call the latest log for that door. The MySQL code would look something like this:

	SELECT * FROM locklogging WHERE door = $doornum ORDER BY id DESC LIMIT 1

Pretty self explanatory. So this gets put in the query like you saw in the last post but this time you have to handle the data as an array so php has a specific method to this madness:

	$return = mysqli_query($dbconn, "[i]MySQL code from above[/i]");
	$data = mysqli_fetch_array($return , MYSQLI_ASSOC);

A little data conversion for legibility:

	if($data['status'])
		$status = "locked";
	else
		$status = "open";

And to finish off the FOR, just print it:

	echo "Door ".$doornum." is ".$status." as of ".$data['time']."
";
}

This outputs this. I intend to fix it up a bit with color and some extra dates added in so I can see earlier transitions but that's all moot.

Door 1 is locked as of 2014-01-04 23:59:43
Door 2 is locked as of 2014-01-04 23:59:50
Door 3 is locked as of 2014-01-04 23:59:55
Door 4 is locked as of 2014-01-04 23:59:58
Door 5 is locked as of 2014-01-05 00:00:03
Door 6 is locked as of 2014-01-05 00:00:08
Door 7 is open as of 2014-01-05 00:00:41
10

Yun is at local post office - to be delivered tomorrow.

11

A bit of a delay in posting due to fun with the Arduino, confusion about some factors about Yun (UPDATE THE WEBSITE ADMIN!!), distraction with future possibilities, and of course Real Life®.

I've got a few things to work out but the core functionality of the code is so simple, this is really the easiest part about the whole thing.

//We've got to set up some variables and should do this only once. 
//state1 is a status that the Arduino will remember and compare a new value to. 
//Technically, we don't need to set it upon initialization, but we will assume if 
//the power goes out it will be due to an electrical outage while we aren't there, 
//it will reset with doors locked.
bool state1 = true;
//s will be used as a the town bicycle for loading and comparing... heheh
bool s;
 
//We bring in the Bridge.h for communicating with the Linux processor so we 
//can access the Wifi internet access.
//Using the Ethernet shield might require an alternative method. 
#include <Bridge.h>
//HttpClient.h allows us to make web related calls
#include <HttpClient.h>

void setup() {
  
    Bridge.begin();   //open a channel to Linus

    pinMode(2, INPUT);   //allows us to bring in a digital signal. 

}

void loop() {
  
    s = digitalRead(2);   //First thing first, check the input status

    if(s != state1){        //Perform if input DOES NOT compare equal 

      state1 = s;     //go ahead and update the state
    
      HttpClient client;    //open the com line to the outside world 

                   //Then check which door lock state to broadcast.
      if(s)
        client.get("http://example.com/lock.php?code=1&door=1&status=1");
      else
        client.get("http://example.com/lock.php?code=1&door=1&status=0");
                   //The PHP web server will take it from there.
    }//close IF

}//close loop

So obviously this code doesn't check all the doors but you should be able to expand on this in your own way. I have it working with an array but because I don't have my shield built yet, (and I'm too lazy to breadboard the obvious) I've got too much noise for it to work w/o proper circuitry.

As mentioned before, code has two purposes: one, to signal to the php script to process incoming door state data and two, I would like to include some sort of server response check. I figure the Arduino generate a random number and use that in the code. The server's reply for a successful submittal will be the return of that value. ...but I haven't resolved what to do if the server doesn't respond, try again... , email me... I dunno.

I'm still waiting on the door sensors and I'm about to place an order for shield components so I'll update with all that when it comes in.

btw, Arduino compiler 1.5.5 loves to lock up.

12

libelle:
and populate a variable for use in an error check:

	$query = mysqli_query($dbconn, "INSERT INTO locklogging (door,state) VALUES ('$door','$state')");

Make sure you protect yourself from SQL injection attacks here!
Will this page be accessible to the public internet? Or access controlled somehow so that only the Arduino can hit it?