PHP：帮助这个逻辑

Question

I have a problem. I have a table of support tickets (wh_task). Each task has a date_completed (d/m/Y) and has_met_sla field (0 or -1). I want to allow the user to search this table by date_completed and display a chart based on the results.

The charts data has to look like this so I can populate a barchart (fusion charts):

Year: 2010 | Month: Nov | SLA Met: 12 | SLA Missed: 2

Year: 2010 | Month: Oct | SLA Met: 15 | SLA Missed: 1

The chart will have the numbers up the x-axis and "Nov 2010" along the y. Each month along the y has 2 columns, met and not met.

so, I can create this kind of chart no problem but it's generating the data I'm having trouble coming up with. Below is my query:

        $tsql = "SELECT task_id, has_met_service_level_agreement, date_completed ".
                "FROM wh_task ".
                "WHERE (task_status_id = 5) AND (account_id =$atid)";

        $stmt = sqlsrv_query( $conn, $tsql);
        if( $stmt === false)
        {
                 echo "Error in query preparation/execution.
";
                 die( print_r( sqlsrv_errors(), true));
        }


        //SLA counters
        $met = 0;
        $missed = 0;



        /* Retrieve each row as an associative array and display the results.*/
        while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
        {
            $date = $row['date_completed'];
            $monthnumber = date_format($date, "n");
            $year = date_format($date, "Y");
            $hasmetsla = $row['has_met_service_level_agreement'];

        }


    }

Can you give me a hand with the logic here? I'm guessing I need to store the data in an array containing the month, the year, the met total, and the not met total. Then for each task check if the year month combination already exist in the array and if so ammend the totals based on $hasmetsla and if not add it to array??

Thanks all!

Jonesy

Answer 1

Here's what I would do if I had to do this in PHP (using SQL would be the better option, but here's one method of doing it post-facto):

First, I'd setup a multi-dimensional array with the following structure:

array(
    'year1' => array(
        'month1' => array(
            'met' => 0,
            'missed' => 0,
        ),
    ),
),

Then, I'd change the while loop to do something like this:

$yearInfo = array();
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
    $date = $row['date_completed'];
    $monthnumber = date_format($date, "n");
    $year = date_format($date, "Y");
    $hasmetsla = $row['has_met_service_level_agreement'];
    if (!isset($yearInfo[$year])) {
        $yearInfo[$year] = array(
            $monthnumber => array(
                'met' => 0, 
                'missed' => 0
            )
        );
    } elseif (!isset($yearInfo[$year][$monthnumber])) {
        $yearInfo[$year][$monthnumber] = array(
            'met' => 0,
            'missed' => 0,
        );
    }
    $key = $hasmetsla ? 'met' : 'missed';
    $yearInfo[$year][$monthnumber][$key]++;
}

Then, when you display:

$data = '';
foreach ($yearInfo as $year => $months) {
    foreach ($months as $month => $status) {
        $data .= 'Year: '.$year.' | '.
              'Month: '.$month.' | '.
              'SLA Met: '.$status['met'].' | '.
              'SLA Missed: '.$status['missed']."
";
    }
}