It works

Well, CAATT is now "functional". After scratching my head over how to do the scheduler for a while I had a pretty good theory, so I sat down to code it yesterday afternoon. hence the post clarifying some of the design details. At the end of the night last night I had annual, semi-annual, quarterly and monthly recurring tasks scheduling themselves. It was about 500 lines of php code (I choose php because the rest of CAATT is written in php... it's a LAMP application really at this point, so all the database access methods were fresh in my head). This afternoon, between a trip to Urgent Care for my wife and the superbowl, I finished weekly, daily, and daily+weekends scheduling, and implemented skip scheduling for all. scheduler.sh (still a php script) is now only 127 lines. Why the drop? Because having written the scheduler for 4 virtually identical scheduling patterns I recognized the similarities and collapsed the core of the scheduler into a loop with a small switch statement that covers the only difference. The skip scheduling was virtually the same as the offset scheduling too, so it was factored into the same loop. That's what it really all came down to in terms of cutting it down to size... yesterday I wrote the first four scheduling protocols to learn how to do it. Today, I refactored them into common code, and then factored an entirely different set of scheduling protocols into it.

for ($lcv=1; $lcv < 8; $lcv++) {
 $query[$lcv]['tasks']="select * from tasks where sched_process=$lcv";
 $query[$lcv]['exist']="select work_id from work_queue where task=%s ";
}

$query[1]['exist'].="and year(input) = year(now())";
$query[2]['exist'].="and year(input) = year(now())
      and (month(input) / 6 = month(now()) / 6)";
$query[3]['exist'].="and year(input) = year(now())
      and (month(input) / 3 = month(now()) / 3)";
$query[4]['exist'].="and year(input) = year(now())
      and month(input) = month(now())";
$query[5]['exist'].="and year(input) = year(now())
      and month(input) = month(now())
      and week(input,0) = week(now(),0)";
$query[6]['exist'].="and year(input) = year(now())
      and month(input) = month(now())
      and day(input) = day(now())";
$query[7]['exist'].="and year(input) = year(now())
      and month(input) = month(now())
      and day(input) = day(now())";

$today=date("N", time());
if ($today == 1 || $today == 7) {
 $startat=7;
} else {
 $startat=1;
}
//$startat=1; //for debug.

for ($lcv=$startat; $lcv < 8; $lcv++) {
 echo "scheduling type $lcv \n";
 $result = mysql_query($query[$lcv]['tasks']);
 if ($results === false) die("FAILED: {$query[$lcv]['tasks']}\n". mysql_error());
 while ($task = mysql_fetch_assoc($result)) {
  echo "  evaluating task {$task['task_id']}\n";

  //look for it already assigned
  $cmd=sprintf($query[$lcv]['exist'], $task["task_id"]);
  $existing = mysql_query($cmd);
  if ($existing === false) die("FAILED: $cmd\n". mysql_error());
  $dosched=false;
  $now['d'] = date("d", time());
  $now['m'] = date("m", time());
  $now['w'] = date("W", time());
  $now['z'] = date("z", time());
  if (mysql_num_rows($existing) == 0) {
   if (isset($task['sched_offset'])) {
    echo "    task has offset\n";
    $schedoff = strtotime($task['sched_offset']);
    $schedat['d'] = date("d", $schedoff);
    $schedat['m'] = date("m", $schedoff);
    $schedat['w'] = date("W", $schedoff);

    switch ($lcv) {
     case 1: //annual
      if ($schedat['d'] == $now['d'] &&
       $schedat['m'] == $now['m'] )  $dosched = true;
      break;
     case 2: //half
      if ($schedat['d']     == $now['d']     &&
       $schedat['m'] % 6 == $now['m'] % 6 )  $dosched = true;
      break;
     case 3: //quarter
      if ($schedat['d']     == $now['d']     &&
       $schedat['m'] % 3 == $now['m'] % 3 )  $dosched = true;
      break;
     case 4: //month
      if ($schedat['d'] == $now['d']) $dosched = true;
      break;
     case 5: //weekly
      if ($schedat['w'] == $now['w']) $dosched = true;
      break;
     default: //daily, daily+weekend
      die("task {$task['task_id']} doesn't make sense, it has an offset, but that isn't applicable!");
    }
   } elseif ($task['sched_skip'] != 0) {
    echo "    task has a skip\n";
    mysql_free_result($existing);
    $cmd="select work_id,date(input) as input from work_queue where task={$task["task_id"]} order by input desc limit 1";
    $existing = mysql_query($cmd);
    if ($existing === false) die("FAILED: $cmd\n". mysql_error());
    $work = mysql_fetch_assoc($existing);
    $lastassign = strtotime($work['input']);
    $last['z'] = date("z", $lastassign);
    $last['m'] = date("m", $lastassign);
    $last['w'] = date("W", $lastassign);

    switch ($lcv) {
     case 4: //month
      $lookat='m'; break;
     case 5: //weekly
      $lookat='w'; break;
     case 6: //daily
     case 7: //daily+weekend
      $lookat='z'; break;
     default: //annual, half, quarter
      die("task {$task['task_id']} doesn't make sense, it has a skip, but that isn't applicable!");
    }
    if ($last[$lookat] + $task['sched_skip'] < $now[$lookat]) $dosched = true;
        
   } else {
    echo"     task has no schedule offset or skip, and no satisfying schedule. yet....\n";
    $dosched=true;
   }
   if ($dosched) {
    echo "  >>  need to assign task ",$task["task_id"],"... "; 
    $cmd="insert into work_queue set task={$task["task_id"]}, doer={$task["usual_doer"]}";
    mysql_query($cmd) or die ("FAILED: $cmd\n". mysql_error());
    echo "inserted as work_id ",mysql_insert_id(),"\n";
   } else {
    echo "  --  task does not need to be scheduled.\n";
   }
  } else { //there are satisfying instances in work_queue
   echo "    task {$task['task_id']} has ",mysql_num_rows($existing)," satisfying instances in work_queue: ";
   while($work = mysql_fetch_assoc($existing)) echo $work['work_id']," ";
   echo "\n";
  }
  mysql_free_result($existing);
 }
 mysql_free_result($result);
}

Now that I've finished writing the first draft of that, I read Wolf's take on programmers and why we write code. Wolf nails it on the head, as usual. It's not the writing code I enjoy, it's the problem solving, yesterday's code writing was a learning process, so that today I could really make it work. By the way, the above code (and all code for this project) was written with TextWrangler, and is covered under GPLv2.

Now all we need to do to make it useful is get some of the tasks around the house into it and start using it! I've already put many of mine... like the cat box, the water filters, the softener salt, the air filters, etc. And this isn't 100% complete... still missing the ability to lock out the users internet connection at the firewall until they're done with their chores, and the time/priority based scheduling (so you can put in a whole bunch of "once a week" jobs and tell the system to only allocate 100 minutes a day worth of work let's say and have *it* figure out what order to put those various weekly jobs in.) is still missing... but they're more "advanced features".

And of course, having just posted this, I now realize the first bug in the system... scheduled offsets in anything other than daily+weekends, will fail to be scheduled on the weekend. Oops.