Complex reporting PHP MySQL

Question

I have four tables in my Database:

Metrics
->id
->description

Agents
->id
->name

Forms
->id
->agent_id
->campaign_id
->calldatetime
->phone

Forms_Responses
->id
->form_id
->metrics_id
->response

And one of the users asked me make a report like so:

Metric ID | Agent_name | Agent_name | Agent_name .. and so on (depends how many from db)
__________|____________|____________|______________
          |            |            |
1         | 90%        | 80%        | 70%
__________|____________|____________|_______________
2         | 80%        | 100%       | 50%   

The report scales horizontally and vertically. Horizontally by Agent_ID

To get the Horizontal scalling (Columns) I have this query

SELECT a.id, b.name FROM forms a
INNER JOIN agents b ON a.agent_id = b.id
WHERE a.calldatetime >= '2015-12-12 00:00:00' 
AND a.calldatetime <= '2015-12-17 23:00:37' AND campaign_id = 22 GROUP BY agent_id ORDER BY id ASC;

I have GROUP BY agent_id so won't have a duplicate data. However my problem is on the ROWS . So Far I have

select form_id, metrics_id, response, remarks
from forms_responses 
where form_id >= 6951 and form_id <= 6953 ORDER BY id ASC;

Don't mind the where form_id >= 6951 and form_id <= 6953 . So that should be my row? Problem is how can I link that in my columns plus the values 90%, 80%.. is based from the formula

Yes Counts / (Yes Counts + No Counts) in the response column of the Forms_Responses table.

So basically this is a scoring report which get's the precentage for each agent on each metrics based on response value in the formula given.

I am doing this in Laravel PHP and will output as an excel with the format mentioned but I dunno what my queries will look like or how the processing goes or is it possbile?

Answer 1

You should do the cross table in PHP, as MySql has no support for such pivot format.

With the SQL query you would aim to output something like this:

metrics_id  agent_name  score
    1       Chewbacca    1
    1       Luke         0.5
    1       Yoda         0
    2       Chewbacca    1
    2       Luke         1
    2       Yoda         1
    3       Chewbacca    1
    3       Luke         0.5
    3       Yoda         0

This can be done with a select like this:

select     r.metrics_id,
           a.name as agent_name, 
           sum(if(r.response='Yes',1,0))/count(*) as score
from       forms_responses r
cross join agents a
inner join forms f
        on f.id = r.form_id
      and  f.agent_id = a.id
      and  f.calldatetime between '2015-12-12 00:00:00' and '2015-12-17 23:00:37'
      and  f.campaign_id = 22
where      a.id in (
              select agent_id
              from   forms
              where  calldatetime between '2015-12-12 00:00:00' and '2015-12-17 23:00:37'
              and    campaign_id = 22
           )
group by   r.metrics_id, a.name;

Here is an SQL fiddle.

This query makes sure that you get all the involved agents for every metric, even if for some metric they have not had a response. This makes the processing in PHP a bit easier.

In the PHP you will do the pivoting:

$header = array("Metric ID");
$scores = array();
$current_metric = "-1";
$rowno = 0;
$mysqli->query($sql); // SQL as provided above
while ($row = $result->fetch_assoc()) { // fetch from query
    if ($row["metrics_id"] <> $current_metric) {
        // new metrics_id, start a new line
        $current_metric = $row["metrics_id"];
        $rowno++;
        $scores[$rowno] = array();
        $scores[$rowno][] = $current_metric;
    }
  if ($rowno == 1) {
      $header[] = $row["name"];
  }
  $scores[$rowno][] = $row["score"];
}
// put header row (with agent names) at top of table
array_unshift($scores, $header);

// output table -- you would do this with CSV or Excel library
echo "<table>";
foreach ($scores as $line) {
    echo "<tr>";
    foreach ($line as $cell) {
        echo "<td>$cell</td>";
    }
    echo "</tr>";
}
echo "</table>";

The above would output something like this:

Metric ID   Chewbacca   Luke    Yoda
   1           1         0.5     0
   2           1         1       1
   3           1         0.5     0

If you prefer to output percentages then you just multiple the score by 100 and add a "%" sign, but note that in Excel this is normally done with formatting, not with the actual number (which stays between 0 and 1).