Best way to manage Cron job for enrollment site

Question

I manage an enrollment website which needs some new features for our agents. What we are trying to accomplish is the following:

• For every 3 members and agent enrolls, he receives a gift card (email sent to administrator to mail out)
• For 10 enrollments in a month, the agent receives his/her marketing materials free of charge for a year (flag in our db changes)
• For 25 total enrollments at any time, the agent receives his/her marketing materials free of charge for a year

I have no problem with creating the actions which occur AFTER the criteria is met (simply php script running off cron job).

The issue I am facing however is difference in opinion with other members of my team. At the end of every enrollment, a member registration record is created with a number of variables, including the memberID and agentID.

Essentially, they want to query the member registration table for the cron job based on the total number of records for a given agentID over a set period of time. The issue I see, however, is that the cron job does not know what to look for or how to process the actions in a loop.

For example, Lets assume our table looks like this (simplified, headers in first row):

memberid     agentid     date_enrolled
12345       7           2010-10-17 12:00:00
39283       3           2010-10-17 12:00:00
33839       4           2010-10-17 12:00:00
44848       7           2010-10-18 12:00:00
38383       5           2010-10-17 12:00:00
38383       7           2010-10-19 12:00:00

Based on the above, if the weekly cron job was run, it would find that AgentID 7 is due a gift card because he enrolled 3 members during the current week. If I were looking at this from an individual query perspective I would have no problem building but seeing as we have over 300 agents and the information may vary greatly, I do not know if the 'desired method' will work.

If it were my way, I think I'd build a new table with a counter each time an enrollment occured then simply create chron jobs to clear out entire columns once the time barrier passed (each week, clear column 2 and restart counter, each month, clear column 3 and restart counter).

Is there even a way to build the script and cron job in the manner our team wants?

EDIT 1:

Based on the team's requirements, I believe my proposed sql query would be something a long the lines of:

SELECT COUNT(AGENT) FROM tablename GROUP BY AGENT WHERE enroll_date > DATE_SUB(CURDATE(),INTERVAL 7 DAY)

EDIT 2:

Not getting a lot of love here ;) I have decided to make my query more stringent but am having a tough time parsing out the results I initially thought would be easy. I need to use the results returned by one query into another.

Objective: Select all from tablename where the enrollment data is greater than 7 days ago, group by the agent number having a count greater than 3. Once results are returned, update unique flag field for EACH agent record returned in the result set from the previous query.

My initial code is:

$sql = $db->query("SELECT * FROM tablename WHERE enroll_date > DATE_SUB(CURDATE(),INTERVAL 7 DAYS) GROUP BY agentid HAVING COUNT(*) >= 3");
            $result = $sql->fetchALL(PDO::FETCH_ASSOC);
            foreach($result as $key=>$val) {
                echo $key.' - '.$val.'<br />';
            }

EDIT 3 - "final" script?

<?php

    try {   
            $db = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->beginTransaction();

            $sql = $db->query("SELECT AGENT FROM tablename WHERE enroll_date > DATE_SUB(CURDATE(),INTERVAL 1 WEEK) GROUP BY AGENT HAVING COUNT(*) >= 10 ORDER BY enroll_date ASC");

            $result = $sql->fetchALL(PDO::FETCH_ASSOC);
            foreach($result as $key=>$val) {
                foreach($val as $ball=>$face) {
                    echo $ball." - ".$face.'<br />';

                    $sql2 = "UPDATE tablename2 SET bonus_set=?, method=? WHERE agentID = '$face'";

                    $stmt = $db->prepare($sql2);
                    $stmt->execute(array("Y", "free"));


                    //NEED TO FIGURE OUT HOW TO EMAIL THE ENTIRE LIST TO MYSELF next...

                }

            }       

            $db->commit();
            $db->null;
            exit;
        }

    catch (PDOException $e)
        {
            $db->rollback();
            echo $e->getMessage();

            exit;
        }
?>

Answer 1

The suggested query would work, with the small change of moving the GROUP BY AGENT to the end. The second and third rules would follow the same pattern.

You could keep track of rewards in a separate table (agent, reward, date) to avoid situations like someone qualifying Mon-Sun and then again Tues-Mon (double-counting the overlapping days). Could run a join on these tables or just a separate query to see if the agent was already been awarded the gift cards within the last seven days, etc.