I am currently working on a re-occurring broadcast schedule. The whole idea is to setup a cron/task job that will update the broadcast flag "isLive" in a database table. Are there other options to optimize the PHP code below?
Live Table (livevent)
+----------+------------------+------------+--------+-----------+---------+
| branchId | timezone | date | isLive | type | reoccur |
+----------+------------------+------------+--------+-----------+---------+
| 0 | Africa/Lagos | 2015-06-15 | 0 | broadcast | 1 |
| 1 | America/Toronto | 2015-06-17 | 1 | broadcast | 1 |
| 2 | America/New York | 2015-06-19 | 1 | broadcast | 0 |
+----------+------------------+------------+--------+-----------+---------+
Occurrence Table (live_occurrence)
+----------+----------------------------+--------------+-----------+------------+----------+
| branchid | month | dow_of_month | dayofweek | start_time | end_time |
+----------+----------------------------+--------------+-----------+------------+----------+
| 0 | 1,2,3,4,5,6,7,8,9,10,11,12 | | 3 | 10:00:00 | 16:00:00 |
| 0 | 1,2,3,4,5,6,7,8,9,10,11,12 | 0,1 | 6 | 10:00:00 | 12:50:00 |
| 1 | 1,2,4,7,9,10,12,6 | 2 | 5 | 10:00:00 | 15:00:00 |
+----------+----------------------------+--------------+-----------+------------+----------+
PHP Code (cronjob.php)
<?php
/* APACHE PHP Logger log4php */
include('log4php/Logger.php');
/* Tell log4php to use our configuration file. */
Logger::configure('log4php.xml');
/* Fetch a logger, it will inherit settings from the root logger */
$log = Logger::getLogger('main');
$log->info("Starting Cron Job.........");
$updateQuery = "UPDATE livevent SET isLive=? WHERE branchId=?";
/* Create MySQL Connection */
$conn = mysqli_connect($SERVER, $USERNAME, $PASSWORD, $DBNAME);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
$log->error("Connect failed: ".mysqli_connect_error());
exit();
}
/* Get list of broadcast events */
$query = 'SELECT * FROM livevent WHERE reoccur=1';
$result = mysqli_query($conn, $query);
//If no data is found then exit
if(!$result) {
die('Could not retrieve data: '+ $conn->mysqli_connect_error);
$log->error("Could not retrieve data: ".mysqli_connect_error());
}
$numBroadcast = 0; //number of current broadcast
//Iterate through event result list and check if there is broadcast for current day
while($row = mysqli_fetch_assoc($result)){
/* Important time zone first! set timezone of the country the broadcast is taking place otherwise there will be time confusion :) */
/* Set time zone for current country where broadcast is taking place */
date_default_timezone_set($row['timezone']);
$branchId = $row['branchId'];
$log->info("Timezone: ".$row['timezone']);
$log->info("BranchId: ".$row['branchId']);
$log->info("------");
$curDate = date('Y-m-d');
$curDayofweek = date('w');
$curMonth = date('n');
$curTime= time();
$log->info("Current Date: ".$curDate);
$log->info("Current Day Of Week: ".$curDayofweek);
$log->info("Current Month: ".$curMonth);
$log->info("Current Time: ".date('H:i:s', $curTime));
$log->info("--");
echo '*****************************************************<br>';
echo 'Timeozone: '.$row['timezone'].' <br>';
echo 'Current Date '.$curDate.' <br>';
echo 'Current Time '.date('H:i:s', $curTime).' <br>';
echo 'Current Day of Week '.$curDayofweek.' <br>';
echo 'Current Month: '.$curMonth.' <br>';
echo '--------------------------- <br>';
/* Check re-occur table if there is currently a live event */
$reoccurStmt = mysqli_prepare($conn, "SELECT * FROM live_occurrence WHERE branchId=?");
mysqli_stmt_bind_param($reoccurStmt, "i", $branchId); //s=string, i=integer, d=double etc
mysqli_stmt_execute($reoccurStmt);
$reoccurResult = mysqli_stmt_get_result($reoccurStmt);
//$log->info("Query: ".$reoccurStmt);
/* Iterate if any, through the re-occurrence list for current branchId */
while($row2 = mysqli_fetch_assoc($reoccurResult)){
//echo 'Event # '.$row2['branchId'].'<br>'
/* Month occurrence */
$schMonths = explode(',', $row2['month']);
/* Day of Week of the Month e.g. (First Saturday of month, 2nd & 3rd Friday of the month) where 0=1st, 1=2nd, 2=3rd, 3=4th*/
$schDowOfMonths = array_filter(explode(',', $row2['dow_of_month']), 'strlen');
echo "What count!!! ".count($schDowOfMonths).'<br>';
/* Day of Week (0=Sunday to 6=Saturday) */
$schDow = $row2['dayofweek'];
/* Start time */
$schStartTime = strtotime($row2['start_time']);
/* End time */
$schEndTime = strtotime($row2['end_time']);
$log->info("Scheduled Month(s): ".$row2['month']);
$log->info("Scheduled Day of Week of Month(s): ".$row2['dow_of_month']);
$log->info("Scheduled Day Of Week: ".$schDow);
$log->info("Start Time: ".$row2['start_time']);
$log->info("End Time: ".$row2['end_time']);
$log->info("--");
/* Check if current month is in scheduled months */
if(in_array($curMonth, $schMonths)){
/* check if it re-occur at weekly interval e.g. every Tuesday */
if(empty($schDowOfMonths)){
$log->info("Weekly Schedule..");
/* Check if current day is in broadcast scheduled day */
if($curDayofweek==$schDow){
/* Check time frame is within specified period */
if($curTime>=$schStartTime && $curTime<=$schEndTime){
/* BINGO!!!!!!! we have an event: update status */
$isLive = 1;
$numBroadcast++;
$log->info("Weekly Schedule: branch $branchId is currently broadcasting");
}
else{ //no event
$isLive = 0;
$log->info("There is a weekly broadcast today for branchId ".$branchId." but not within time frame");
}
/* Update database table */
$isLiveStmt = mysqli_prepare($conn, $updateQuery);
mysqli_stmt_bind_param($isLiveStmt, "ii", $isLive, $branchId); //s=string, i=integer, d=double etc
mysqli_stmt_execute($isLiveStmt);
mysqli_stmt_close($isLiveStmt);
//$log->info("Update Query: ".$isLiveStmt);
} //end check curDayofweek
} //end occurrence with weekly interval
/* It re-occurs at monthly interval i.e. combination of all or either 1st/2nd/3rd/4th day of week of month*/
else {
$log->info("Monthly Schedule..");
//converts integer DOW to 3 letter representation e.g. Sat, Sun.
$toTxtDow = date('D', strtotime("Sunday + $schDow Days"));
$log->info("Expected broadcast day of Week Text: ".$toTxtDow);
/* Iterate through nth interval */
foreach($schDowOfMonths as $n){
//build the date and check if it's broadcast day
$expectedDate = date('Y-m-d', strtotime("+$n week $toTxtDow ".date('F'). date('Y').""));
$log->info("Expected Interval Broadcast Date: ".$expectedDate);
/* Check if current day is broadcast day*/
if($expectedDate==$curDate){
/* Check time */
if($curTime>=$schStartTime && $curTime<=$schEndTime){
/* BINGO!!! It's broadcast day for monthly interval */
$isLive = 1;
$numBroadcast++;
$log->info("Monthly Schedule: branch $branchId is currently broadcasting");
}
else {
$isLive = 0;
$log->info("There is a monthly broadcast today for branchId ".$branchId." but not within time frame");
}
/* Update database table */
$isLiveStmt = mysqli_prepare($conn, $updateQuery);
mysqli_stmt_bind_param($isLiveStmt, "ii", $isLive, $branchId);
mysqli_stmt_execute($isLiveStmt);
mysqli_stmt_close($isLiveStmt);
} //end check expected day
//$log->info("Update Query: ".$isLiveStmt);
} //end for each schedule schDowOfMonths
} //end occurrence with monthly interval
} //end current month check
} //end result fetch for current branchId
//close statement and free cursor
mysqli_stmt_close($reoccurStmt);
mysqli_free_result($reoccurResult);
$log->info("...............................................");
} //end while: all branch with occurrence
$log->info(" ");
$log->info("Number of Current Broadcast: ".$numBroadcast);
/* Free cursor memory */
mysqli_free_result($result);
/* close MySQL connection */
mysqli_close($conn);
$log->info("End Cron Job...");
$log->info("*******************************************************************");
?>
Output Log
2015-06-19T20:52:15+02:00 [INFO] main: Starting Cron Job.........
2015-06-19T19:52:15+01:00 [INFO] main: Timezone: Africa/Lagos
2015-06-19T19:52:15+01:00 [INFO] main: BranchId: 0
2015-06-19T19:52:15+01:00 [INFO] main: ------
2015-06-19T19:52:15+01:00 [INFO] main: Current Date: 2015-06-19
2015-06-19T19:52:15+01:00 [INFO] main: Current Day Of Week: 5
2015-06-19T19:52:15+01:00 [INFO] main: Current Month: 6
2015-06-19T19:52:15+01:00 [INFO] main: Current Time: 19:52:15
2015-06-19T19:52:15+01:00 [INFO] main: --
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Month(s): 1,2,3,4,5,6,7,8,9,10,11,12
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Day of Week of Month(s):
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Day Of Week: 3
2015-06-19T19:52:15+01:00 [INFO] main: Start Time: 10:00:00
2015-06-19T19:52:15+01:00 [INFO] main: End Time: 16:00:00
2015-06-19T19:52:15+01:00 [INFO] main: --
2015-06-19T19:52:15+01:00 [INFO] main: Weekly Schedule..
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Month(s): 1,2,3,4,5,6,7,8,9,10,11,12
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Day of Week of Month(s): 0,1
2015-06-19T19:52:15+01:00 [INFO] main: Scheduled Day Of Week: 6
2015-06-19T19:52:15+01:00 [INFO] main: Start Time: 10:00:00
2015-06-19T19:52:15+01:00 [INFO] main: End Time: 12:50:00
2015-06-19T19:52:15+01:00 [INFO] main: --
2015-06-19T19:52:15+01:00 [INFO] main: Monthly Schedule..
2015-06-19T19:52:15+01:00 [INFO] main: Expected broadcast day of Week Text: Sat
2015-06-19T19:52:15+01:00 [INFO] main: Expected Interval Broadcast Date: 2015-06-06
2015-06-19T19:52:15+01:00 [INFO] main: Expected Interval Broadcast Date: 2015-06-13
2015-06-19T19:52:15+01:00 [INFO] main: ...............................................
2015-06-19T14:52:15-04:00 [INFO] main: Timezone: America/Toronto
2015-06-19T14:52:15-04:00 [INFO] main: BranchId: 1
2015-06-19T14:52:15-04:00 [INFO] main: ------
2015-06-19T14:52:15-04:00 [INFO] main: Current Date: 2015-06-19
2015-06-19T14:52:15-04:00 [INFO] main: Current Day Of Week: 5
2015-06-19T14:52:15-04:00 [INFO] main: Current Month: 6
2015-06-19T14:52:15-04:00 [INFO] main: Current Time: 14:52:15
2015-06-19T14:52:15-04:00 [INFO] main: --
2015-06-19T14:52:15-04:00 [INFO] main: Scheduled Month(s): 1,2,4,7,9,10,12,6
2015-06-19T14:52:15-04:00 [INFO] main: Scheduled Day of Week of Month(s): 2
2015-06-19T14:52:15-04:00 [INFO] main: Scheduled Day Of Week: 5
2015-06-19T14:52:15-04:00 [INFO] main: Start Time: 10:00:00
2015-06-19T14:52:15-04:00 [INFO] main: End Time: 15:00:00
2015-06-19T14:52:15-04:00 [INFO] main: --
2015-06-19T14:52:15-04:00 [INFO] main: Monthly Schedule..
2015-06-19T14:52:15-04:00 [INFO] main: Expected broadcast day of Week Text: Fri
2015-06-19T14:52:15-04:00 [INFO] main: Expected Interval Broadcast Date: 2015-06-19
2015-06-19T14:52:15-04:00 [INFO] main: Monthly Schedule: branch 1 is currently broadcasting
2015-06-19T14:52:15-04:00 [INFO] main: ...............................................
2015-06-19T14:52:15-04:00 [INFO] main:
2015-06-19T14:52:15-04:00 [INFO] main: Number of Current Broadcast: 1
2015-06-19T14:52:15-04:00 [INFO] main: End Cron Job...
2015-06-19T14:52:15-04:00 [INFO] main: *******************************************************************
