2
\$\begingroup\$

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: ******************************************************************* 
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.