CODEWITHSUNDEEP
phparraysmultidimensional-arraylogic
iamjonesy
iamjonesy

Reputation: 25122

PHP: help with this logic

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.\n";
                 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

Upvotes: 0

Views: 236

Answers (2)

ircmaxell
ircmaxell

Reputation: 165201

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']."\n";
    }
}

Upvotes: 1

tdammers
tdammers

Reputation: 20721

I think you're better off doing this kind of processing in SQL. Use a two-step solution: First, find a query that gives you the year, month, and sla_met for each row. Put that in a view. Then do a query on that view, using a clever combination of group_by, sum() and count() to calculate the desired result:

CREATE VIEW vw_sla AS SELECT DATEPART(year, date_completed) AS year, DATEPART(month, date_completed), CASE has_met_service_level_agreement WHEN 0 THEN 0 ELSE 1 END as sla_met

SELECT year, month, sum(sla_met), count(*) - sum(sla_met) FROM vw_sla GROUP BY year, month ORDER BY year DESC month DESC

The all you need to do is get the data from the database, and display it in a table.

Upvotes: 3

Related Questions