my_sql_header.h

/* 
 * File:   my_sql_header.h
 *
 *  This was written by Matthew Bennett
 * (abishur on the raspberry pi forums - http://www.raspberrypi.org/phpBB3/)
 */

#include <stdio.h>
#include <stdlib.h>
#include <iostream>

using namespace std;

#ifndef MY_SQL_HEADER_H
#define    MY_SQL_HEADER_H

// Builds a query in the acceptable const char* format with a variable
// int "x" it the end of the string i.e. 
// "select * from pinID where pinNum = '" x "' ;";


// Builds a query in the acceptable const char* format with a variable
// int "x" it the end of the string i.e. 
// "select * from pinID where pinNum = '" x "' ;";
const char* query_build(string str, int x)
{
    const char* c;
    string intHold;
    intHold = '0'+x;
        str += intHold;
        str += "' ;";
        c = str.c_str();
        return c;
}

// Builds a query in the acceptable const char* format with a variable
// int "x" in the middle of the string i.e.
// "Select id = '" x "' from pinNum;"
const char* query_build(string str1, int x, string str2)
{
    const char* c;
    string intHold;
    intHold = '0'+x;
        str1 += intHold;
        str1 += "' '";
        str1 += str2;
        str1 += "' ;";
        c = str1.c_str();
        return c;
}

#endif	/* MY_SQL_HEADER_H */

sprinkler.cpp

/* 
 * File:   sprinkler.cpp
 * Author: m.bennett
 *
 *  This was written by Matthew Bennett
 * (abishur on the raspberry pi forums - http://www.raspberrypi.org/phpBB3/)
 */

// Includes
#include <wiringPi.h> // By Gordon
#include <mysql/mysql.h> //mysql connector library for C++
#include "my_sql_header.h" // customer header for making SQL queries
#include "time_calcs.h" // performs math operations on struct tm objects

using namespace std;

// change these to match your system
#define HOST "localhost" // PC hosting database
#define USER "SQL USERNAME HERE" // User authorized to connecto to database
#define PASSWD "SQL PASSWORD HERE" // User's password
#define DB "gpio" // Database name
#define waitTime 5 // time to wait between gpio reads

// ----------- DO NOT CHANGE ANYTHING BELOW THIS LINE -----------------------------

MYSQL *con, mysql; // Needed for mysql connections
MYSQL_RES *result; // Needed for mysql connections
MYSQL_ROW row, rowRes; // Needed for mysql connections
struct tm channelTimeStart[8]; // Time for sprinkler to turn on
struct tm channelTimeEnd[8]; // Time for sprinkler to turn off
struct tm* theTime; // holds the "current" time
int gpio_status[8]; // Reads current status of the physical GPIO
int pinStart[8]; // User request to turn GPIO on or off
bool dayRun[8]; // Is today one of the days it's supposed to run
bool garageUpdate;

// Functions
struct tm getTime(int x); // gets start time from mysql
int compTimes(struct tm &time1, struct tm &time2);// compares two times
void init (); // initializes system
struct tm * UpdateTime(); // Gets current time
int query_result(const char* query1); // returns SQL query as an integer
void gpioUpdate(int x); // updates current status of GPIO
void autoRun(struct tm &start, struct tm &end, int x);
void gpioInit();
//string direction_result(const char* query1);
void garage();

int main ()
{
    mysql_init(&mysql); //initialize mysql connection library
    try {
        con = mysql_real_connect(&mysql,HOST,USER,PASSWD,DB,0,0,0); //connect to mysql database
        while (con==NULL)
        {
            sleep(1);
            con = mysql_real_connect(&mysql,HOST,USER,PASSWD,DB,0,0,0); //connect to mysql database
        };
    }
    catch(int){}

    int needUpdate[8];
    int x; // Every program has to have an int x for loop control
    int y; // If you need more than one loop control, the second one has to be y.  It's a rule
    int loop = 1; // controls loop
    const char* query; // holds query to SQL database
    int pinManMode[8]; // Is the pin being controlled manual?
    int pinAutoMode[8]; // What auto mode is it in?
    int channelDuration[8]; // Activation time in minutes
    string channelstr[8];
    bool firstRun = true;

    init();
while (1){
    while (loop == 1) {
        x = 0;
        do {
            gpioUpdate(x);
            theTime = UpdateTime();
            gpio_status[x] = digitalRead(x);
            pinManMode[x] = query_result(query_build("SELECT pinMode FROM `pinMode` WHERE pinNumber = '", x));
            if (pinManMode[x] == 1) {
                pinStart[x] = query_result(query_build("SELECT pinStart FROM `manual_start` WHERE pinNumber = '", x));
		channelDuration[x] = query_result(query_build("SELECT manualDuration FROM `manual_start` WHERE pinNumber = '", x));
                needUpdate[x] = 1;
                if ((pinStart[x] == 1) && (gpio_status[x] != pinStart[x])) {
			cout << pinStart[x] << endl;
                    while (digitalRead(x) != 1) {
                        digitalWrite(x, 1);
			sleep(0.05);
                    };
                    gpio_status[x] = digitalRead(x);
                    channelTimeStart[x] = *theTime;
                    channelTimeEnd[x] = calc_end(channelTimeStart[x], channelDuration[x]);
                }
                if ((compTimes(*theTime, channelTimeEnd[x]) != -1) || (pinStart[x] == 0)){
                    while (digitalRead(x) != 0) {
                        digitalWrite(x, 0);
			sleep(0.05);
                    };
                    mysql_query(con,query_build("UPDATE `gpio`.`manual_start` SET `pinStart` = '0' WHERE `manual_start`.`pinNumber` = '", x));
                }
            }
            else { // Pin is in auto mode
                pinAutoMode[x] = query_result(query_build("SELECT pinAutoMode FROM `pinAutoMode` WHERE pinNumber = '", x));
                if ((needUpdate[x] == 1)||(firstRun)) {
                    channelTimeStart[x] = getTime(x); // Gets auto run start time
                    channelDuration[x] = query_result(query_build("SELECT autoDuration FROM `auto_start` WHERE pinNumber = '", x));
                    channelTimeEnd[x] = calc_end(channelTimeStart[x], channelDuration[x]);
                    channelstr[x] = countDays(query_result(query_build("SELECT pinDays FROM `pinDays` WHERE pinNumber = '", x)));
                    mysql_query(con,query_build("UPDATE `gpio`.`auto_start` SET `needUpdate` = '0' WHERE `auto_start`.`pinNumber` = '", x));
                }

                switch (pinAutoMode[x]){
                    case 0: // Runs on even numbered days
                        if (theTime->tm_mday%2 == 0){
                            autoRun(channelTimeStart[x], channelTimeEnd[x], x);
                        }

                        break;
                    case 1: // Runs on odd numbered days
                        if (theTime->tm_mday %2 != 0){
                            autoRun(channelTimeStart[x], channelTimeEnd[x], x);
                        }

                        break;
                    case 2: // Runs on user defined days days are defined as day_num^2 (sunday = 1, monday = 2; etc)
                        if (((theTime->tm_hour == 0) && (theTime->tm_min == 0)) || (firstRun)|| (needUpdate[x] == 1)){
                         dayRun[x] = isDay(theTime->tm_wday, channelstr[x]);
                        }
                        if (dayRun[x]){
                            autoRun(channelTimeStart[x], channelTimeEnd[x], x);
                        }
                        break;
                }
            }

                needUpdate[x] = query_result(query_build("SELECT needUpdate FROM `auto_start` WHERE pinNumber = '", x));
		if (digitalRead(x) == 1){
   		    mysql_query(con,query_build("UPDATE `gpio`.`pinStatus` SET `pinStatus` = '1' WHERE `pinStatus`.`pinNumber` = '", x));
		} else mysql_query(con,query_build("UPDATE `gpio`.`pinStatus` SET `pinStatus` = '0' WHERE `pinStatus`.`pinNumber` = '", x));

                x++;
		garage();
                } while (x<8);

                firstRun = false;
                loop = query_result(query_build("SELECT doLoop FROM `doLoop` WHERE numbers = '", 1));
               sleep (waitTime);
    };

};

	cout << "What? How did you get here?" << endl;
return 0;
}

void garage(){
	if ((digitalRead(10) == 0) && (garageUpdate)) {
	    mysql_query(con, query_build("UPDATE `gpio`.`garage` SET `isOpen` = '1' WHERE `garage`.`pinID` = '", 1));
	    garageUpdate = false;
	}
	if ((digitalRead(10) == 1) && (garageUpdate==false)){
	     mysql_query(con,query_build("UPDATE `gpio`.`garage` SET `isOpen` = '0' WHERE `garage`.`pinID` = '", 1));
	     garageUpdate = true;
	}

	return;
}

void autoRun(struct tm &start, struct tm &end, int x){
    if ((compTimes(start, *theTime) == -1) and (compTimes(*theTime, end) == -1)) {
                                digitalWrite(x, 1);
                                while (digitalRead(x) != 1) {
                                        digitalWrite(x, 1);
                                        sleep(0.05);
                                };
                            }
                            else {
                                digitalWrite(x, 0);
                                while (digitalRead(x) != 0) {
                                        digitalWrite(x, 0);
                                        sleep(0.05);
                                };
                            }
    return;
}

// compares two times to see value between them, returns -1 if time2 is greater than time1
// otherwise returns difference between time1 and time 2 in minutes
int compTimes(struct tm &time1, struct tm &time2)
{
        int diffTime = 0; // value in difference of minutes between time1 minus time 2
        int time1day, time2day, dayDiff;
	time1day = time2day = dayDiff = 0; //day of the month (1-31)
        int time1hour, time2hour, hourDiff;
	time1hour = time2hour = hourDiff = 0; // hours since midnight (0-23)
        int time1min, time2min, minDiff;
	time1min = time2min = minDiff = 0; // minutes after the hour (0-59)

        time1day = time1.tm_mday;
        time2day = time2.tm_mday;

        time1hour = time1.tm_hour;
        time2hour = time2.tm_hour;

        time1min = time1.tm_min;
        time2min = time2.tm_min;

        if (time1day >= time2day) {
            dayDiff = time1day-time2day;
            diffTime += dayDiff*24*60;
            if (time1hour >= time2hour){
                hourDiff = time1hour-time2hour;
                diffTime += hourDiff*60;
                if (time1min >= time2min){
        	    minDiff = time1min - time2min;
        	    diffTime += minDiff;
       	        }
	   }
	}

        if (diffTime <= 0) diffTime = -1;
        return diffTime;
}

struct tm getTime(int x)
{
    struct tm get;

    get = *theTime;

    get.tm_hour = query_result(query_build("SELECT pinHours FROM `auto_start` WHERE pinNumber = '", x));
    get.tm_min = query_result(query_build("SELECT pinMins FROM `auto_start` WHERE pinNumber = '", x));

    return get;

}

void gpioUpdate(int x) {
int temp;
        temp = 0;
        temp = query_result(query_build("SELECT pinDirection FROM `pinDirection` WHERE pinNumber = '", x));
       if (temp == 1) pinMode(x, INPUT);
       else pinMode(x, OUTPUT);
    return;
}

void init ()
{
    for (int x=0;x<8;x++){
        dayRun[x] =0;
}
    wiringPiSetup();
    pinMode(10, INPUT);
    if (digitalRead(10) == 0) {
	garageUpdate = true;
    }
    else {
	garageUpdate =false;
    }
    return;
}

int query_result(const char* query1)
{
    int query_state=-2; // Needed for mysql connections
    int rowInt;
    query_state = mysql_query(con, query1);
    if (query_state != -2){
       result = mysql_store_result(con);
       row = mysql_fetch_row(result);
       rowInt = atoi(row[0]);
       mysql_free_result(result);
       return rowInt;
    }
    return -1;
}

struct tm* UpdateTime()
{
        struct tm *theCurrentTime;
        time_t long_time;
        time ( &long_time );
        theCurrentTime = localtime ( &long_time);
        return theCurrentTime;
}

time_calcs.h

/* 
 * File:   time_calcs.h
 * Author: m.bennett
 *
 *  This was written by Matthew Bennett
 * (abishur on the raspberry pi forums - http://www.raspberrypi.org/phpBB3/)
 */

#include <cmath>
#include <time.h>
#include <sstream>
#include <string>

using namespace std;

#ifndef TIME_CALCS_H
#define    TIME_CALCS_H

#include <iostream>
#include <iomanip>
int findDays(int month, int year);
struct tm calc_end(struct tm startEnd, int duration);
string countDays (int days);
bool isDay (int day);

struct tm calc_end(struct tm startEnd, int duration)
{
    int inc=0;
    int remainder = 0;
    int days = 0;
    
    startEnd.tm_min += duration;
    if (startEnd.tm_min > 59 ) {
        remainder = (startEnd.tm_min + 1)%60; // tm_min is a range of 0-59, I want 1-60 for division
        inc = (startEnd.tm_min + 1)/60;
        startEnd.tm_min = remainder - 1; // store it back a range of 0-59
        startEnd.tm_hour += inc; // increase hours by the amount of additional hours
    } 
        
    if (inc == 0) return startEnd; // If it's in the same hour there's no need to go further
    inc = remainder = 0; // housekeeping
    
    if (startEnd.tm_hour > 23) {
        remainder = (startEnd.tm_hour + 1)%24; //tm_hour is a range of 0-23, I want 1-24 for division
        inc = (startEnd.tm_hour + 1)/24;
        startEnd.tm_hour = remainder - 1;
        startEnd.tm_mday += inc;
    }
    
    
    if (inc == 0) return startEnd; // if it's the same day there's no need to go further
    inc = remainder = 0; // housekeeping
    
    days = findDays((startEnd.tm_mon+1), (startEnd.tm_year+1900));
    if (startEnd.tm_mday > days) {
        remainder = startEnd.tm_mday - days;
        inc = 1;
        startEnd.tm_mday = remainder;
        startEnd.tm_mon += 1;
    }
    
    if (inc == 0) return startEnd;
    inc = remainder = 0;
    
    if (startEnd.tm_mon > 11){
        startEnd.tm_mon = 0;
        startEnd.tm_year += 1;
    }
                                
    return startEnd;
}

int findDays(int month, int year)
{
int Days;

if (month == 4 || month == 6 || month == 9 || month == 11) Days = 30;

else if (month == 2) {
    bool isLeapYear = (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);
    if (isLeapYear) Days = 29;
    else Days = 28;
}
else Days = 31;

return Days;
}

string countDays (int days) {
    int x;
    int y;
    int dayArr[7];
    for (x=0;x<7;x++){
        dayArr[x] = 0;
    }
        if ((days % 2)  == 1) {
            dayArr[0] = 1;
            days = days - 1;
        }
    
    while (days != 0) {
        x = 0;
        y = days;
        while (y != 1){
            if (y%2 == 1) {
		y=1;
	    }
	    else {
		y = y / 2;
            	x++;
            }
	};
        dayArr[x] = 1;
        days = (days-(pow(2,x)));
    };
    
    std::ostringstream stm ;
    stm << '[' ;
    for( std::size_t i = 0 ; i < 7 ; ++i ) stm << dayArr[i] << ", " ;

    std::string result = stm.str() ;
    // remove the last ", " and add a ']'
    return result.substr( 0, result.size()-2 ) + ']' ;
}

bool isDay (int day, string channelString){
    
    int y, x;
    int channelDays[7];
    bool boolDay = false;
    for (x=0;x<7;x++){
        channelDays[x]=0;
    }
    y=0;
    for (x=1;x<21;x+=3){ // only works for single int values, first int is in position 1, every 3 char is the next int
        stringstream str;
        str << channelString[x];
        str >> channelDays[y];
        y++;
    }
    
    switch (day){
        case 0:
            if (channelDays[0] == 1) boolDay = true;
            break;
        case 1:
            if (channelDays[1] == 1) boolDay = true;
            break;    
        case 2:
            if (channelDays[2] == 1) boolDay = true;
            break;
        case 3:
            if (channelDays[3] == 1) boolDay = true;
            break;
        case 4:
            if (channelDays[4] == 1) boolDay = true;
            break;
        case 5:
            if (channelDays[5] == 1) boolDay = true;
            break;
        case 6:
            if (channelDays[6] == 1) boolDay = true;
            break;
    }
        
    return boolDay;
}

#endif	/* TIME_CALCS_H */