Clock In/Out System

[BlaineSch]

Clock In/Out System

Well I started working hourly recently. I found out that I am horrible about keeping track of my hours. I also wanted to know if my quotes are accurate. I figured the best way to solve both of these problems it to make me a system that tracks my times. This is the tutorial showing you how to do this yourself! You can track your hours, and since you also track hours for non hourly projects you can start giving way more accurate quotes.

Table:
id, client(varchar(100)), punch(datetime), comment(longtext)

Inserting:
Well inserting should be the first concern you have, since you cannot do math on times you cannot work with. Here is my HTML form:

<?PHP
$query = mysql_query("SELECT client FROM `timecards` GROUP BY `client`");
while($row=mysql_fetch_assoc($query)) {
    $cc .= '<option value="'.$row['client'].'">'.$row['client'].'</option>';
}
?>
    <h3>Add Punch</h3>
    <form method="post">
<pre>
     Current Client: <select name="client"><?=$cc?><option value="0">New</option></select>
         New Client: <input type="text" name="clientz">
        Comment: <textarea name="comment"></textarea>
                 <input type="submit" value="Punch Card">
</pre>
     </form>

The query simply gets all my previous clients and puts them into the select box. How all this really needs to do is to put the contents in the database. Because I believe I am the only one with access to my page I do not have very much filtering on it at all.

if($_POST) {
    $now = date('Y-m-d H:i:s');
    $client = preg_replace('/[^a-zA-Z0-9 ]+/', '', ($_POST['client']=="0")?$_POST['clientz']:$_POST['client']);
    $cmt = base64_encode($_POST['comment']);
    mysql_query("INSERT INTO `timecards` SET `client`='$client', `punch`='$now', `comment`='$cmt'");
} 

Basically if you have post data, generate the time in YYYY-MM-DD HH:MM:SS format, I did do a small filter on the client name and then did an if statement to see which client name I should get, the one from the dropdown or the one from the input box, based on which one is longer, I encode the comemnt to make it easier no me, then I insert it into the database.
Now for the fun stuff!

Reviewing:
So with reviewing your timecard you need a way to input two times, the start and end times, get them from the database, display them, display how long each set took, then add up a total at the bottom. So here is my HTML for that. It uses the same $cc variable to get the client list so just make sure your query is closer to the top of the page (before both calls)

    <h3>Search</h3>
    <form method="get">
    <pre>
    Client: <select name="client"><?=$cc?></select>
     Start: <input type="text" name="s" value="Last Week">
       End: <input type="text" name="e" value="Now">
            <input type="submit" value="Search Now!">
    </pre>
    </form>

So converting a string to a time should not be too hard so here is a query for that:

if($_GET) {
    $start = date('Y-m-d H:i:s', strtotime($_GET['s']));
    $end = date('Y-m-d H:i:s', strtotime($_GET['e']));
    $client = preg_replace('/[^a-zA-Z0-9 ]+/', '', $_GET['client']);
    $query = mysql_query("SELECT * FROM `timecards` WHERE `client`='$client' && `punch` BETWEEN '$start' AND '$end' ORDER BY `punch`");
} 

I have a temporary variable I am using to tell if I am the first or second time. If this is null I am first, if it is not I am not first. My formatting code is here:

    while($row=mysql_fetch_assoc($query)) {
        if($t===null) {
            $t = $row['punch'];
            $results .= "<br />".format($row['punch']);
        } else {
            $results .= "to ".format($row['punch'])." = ".timediff($t, $row['punch']);
            $t = null;
        }
    }
    if($t !== null) {
        $results .= " STILL CLOCKED IN";
    }
    $results .= "<br /><br /><strong>Total:</strong> ".His($total); 

I used a format function which is just what I used to make it look hardly any nicer.

function format($date) {
    return date('M jS g:i:sA', strtotime($date));
} 

I won't cover it but it makes it look like: Aug 30th 1:43:01AM
The function that tells me the time difference gets the start and end time and puts it into seconds, subtracts them, and gets the time based on that. The His I named since the date('His') will hopefully format it the same way

function timediff($start,$end, $type=0) {
    $start = strtotime($start);
    $end = strtotime($end);
    if($start!==-1 && $end!==-1) {
        if($end >= $start) {
            return(($type==0)?His($end - $start):($end - $start));
        }
    }
    return false;
}
function His($diff) {
    if($hours=floor($diff/3600)) {
        $diff -= ($hours*3600);
    }
    if($minutes=floor($diff/60)) {
        $diff -= ($minutes*60);
    }
    return "{$hours}:{$minutes}:{$diff}";
}
function question($cmt) {
    return " <a href='javascript:alert(\"{$cmt}\")'>?</a> ";
} 

Here is the full code:

<?PHP
function His($diff) {
    if($hours=floor($diff/3600)) {
        $diff -= ($hours*3600);
    }
    if($minutes=floor($diff/60)) {
        $diff -= ($minutes*60);
    }
    return "{$hours}:{$minutes}:{$diff}";
}
function question($cmt) {
    return " <a href='javascript:alert(\"{$cmt}\")'>?</a> ";
}
function format($date) {
    return date('M jS g:i:sA', strtotime($date));
}
function timediff($start,$end) {
    $start = strtotime($start);
    $end = strtotime($end);
    if($start!==-1 && $end!==-1) {
        if($end >= $start) {
            echo "<br /><br />Start: $end - $start = ".($end-$start)."<br /><br />";
            return $end - $start;
        }
    }
    return false;
}
if($_POST) {
    $now = date('Y-m-d H:i:s');
    $client = preg_replace('/[^a-zA-Z0-9 ]+/', '', ($_POST['client']=="0")?$_POST['clientz']:$_POST['client']);
    $cmt = base64_encode($_POST['comment']);
    mysql_query("INSERT INTO `timecards` SET `client`='$client', `punch`='$now', `comment`='$cmt'");
}
if($_GET) {
    $start = date('Y-m-d H:i:s', strtotime($_GET['s']));
    $end = date('Y-m-d H:i:s', strtotime($_GET['e']));
    $client = preg_replace('/[^a-zA-Z0-9 ]+/', '', $_GET['client']);
    $query = mysql_query("SELECT * FROM `timecards` WHERE `client`='$client' && `punch` BETWEEN '$start' AND '$end' ORDER BY `punch`");
    while($row=mysql_fetch_assoc($query)) {
        $q = question(base64_decode($row['comment']));
        if($t===null) {
            $t = $row['punch'];
            $results .= "<br />".format($row['punch']).$q;
        } else {
            $j = timediff($t, $row['punch']);
            $results .= "to ".format($row['punch']).$q." = ".His($j);
            $total += $j;
            $t = null;
        }
    }
    if($t !== null) {
        $results .= " STILL CLOCKED IN";
    }
    $results .= "<br /><br /><strong>Total:</strong> ".His($total);
}
$query = mysql_query("SELECT client FROM `timecards` GROUP BY `client`");
while($row=mysql_fetch_assoc($query)) {
    $cc .= '<option value="'.$row['client'].'">'.$row['client'].'</option>';
}
?>
<div style="width:400px;float:left">
    <h3>Search</h3>
    <form method="get">
    <pre>
    Client: <select name="client"><?=$cc?></select>
     Start: <input type="text" name="s" value="Last Week">
       End: <input type="text" name="e" value="Now">
            <input type="submit" value="Search Now!">
    </pre>
    </form>
    <?=$results?>
</div><div style="width:400px;float:left;">
    <h3>Add Punch</h3>
    <form method="post">
<pre>
     Current Client: <select name="client"><?=$cc?><option value="0">New</option></select>
         New Client: <input type="text" name="clientz">
        Comment: <textarea name="comment"></textarea>
                 <input type="submit" value="Punch Card">
</pre>
     </form>
</div>

[Jordan]

Very nice work, Blaine! +rep

[BlaineSch]

Thank you, Jordan!

[WingedPanther]

Excellent job! +rep

[princess2313]

Great Work! How exactly did you get the mysql database working?