PHP script, add rows to total data for a CSV

[thenorman138]

I have a php script which runs a sql query and writes it to a CSV, which works perfectly.

 

However, I created a second modified version of the script because I want to write the same CSV, but with an additional row beneath each group of data for totals. Currently, the results are ordered by User Extension (CSRs and phone numbers). So for extension 7200 I want, let's say, all 10 of their calls and then another row to total the calls and the individual columns. I currently have the query returning an 'x' for indicators. So one user might have 10 calls and 4 'x's in the 'inbound' column. I would want that to say 4.

 

Anyway, I started the second version of the script to use an array to do this. The first problem is it's telling my fputcsv() expects parameter 1 to be resource. It looks like this could be an error for the type of array I'm using, but I'm not sure. 

 

I feel like I'm on the right track but I'm not familiar with using arrays and CSV files together like this. Any help is much appreciated. 

 

 

```$result = mysqli_query($conn2,

"SELECT DISTINCT

  firstn

    , lastn

    , extension

    , Recieved

    , RecievedKnown

    , Outbound

    , outboundKnown

    , Missed

    , MissedKnown

    , CallingNumber

    , CalledNumber

    , starttime

    , endtime

  , duration

    , HOLDTIMESECS

    , TERMINATIONREASONCODE

 

FROM (

      SELECT

      u.firstn

    , u.lastn

    , c.extension

    , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved

    , CASE WHEN LEGTYPE1 = 2 AND answered = 1 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS RecievedKnown

    , CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 THEN 'x' ELSE '' END  AS Outbound

    , CASE WHEN LEGTYPE1 = 1 AND FINALLYCALLEDPARTYNO = k.phone_number THEN 'x' ELSE '' END AS outboundKnown

    , CASE WHEN Answered = 0 THEN 'x' ELSE '' END AS Missed

    , CASE WHEN ANSWERED = 0 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS MissedKnown

    , a.CALLINGPARTYNO AS CallingNumber

    , a.FINALLYCALLEDPARTYNO AS CalledNumber

    , b.starttime AS starttime

    , b.endtime AS endtime

    , b.duration

    , a.holdtimesecs

    , a.terminationreasoncode

      FROM ambition.session a

      INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID

      INNER JOIN ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID

      INNER JOIN jackson_id.users u ON c.extension = u.extension

      LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number

      WHERE date(b.ts) >= curdate()

      AND LEGTYPE1 <> 12 -- This keeps the report from having blank spaces due to the 12 legtype.

      AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)

 

      ) x

        ORDER BY lastn") or die(mysqli_error( $conn2));

 

$userDetails = array();

 

 

while ($row = mysqli_fetch_assoc($result)){

 

$userDetails[] = $row;

 

$extension = $row['extension'];

if(!isset($userDetails[$extension])){

 

        $userDetails[$extension]['missedCallCounts'] = 1; /* First time count */

    }else{

        $userDetails[$extension]['missedCallCounts'] += 1; /* Sum up the count */

    }

 

}

 

echo $userDetails;

$fp = fopen('TESTCSV.csv', 'w');

 

foreach($userDetails as $userDetail){

    /* In the following line dump the respective userdetails to csv which will show summary */

    fputcsv($fp, array_values($userDetails));

}```

[requinix]

if(!isset($userDetails[$extension])){

That will never be true because the values in $userDetails are indexed automatically, not by the extension.

 

$userDetails[$extension]['missedCallCounts'] = 1;

This will create a new empty $userDetails record at the end of where the array currently is, so it will be positioned after the first line the extension appears - every time after that the extension is used, this same array entry will be updated. The array also contains just one entry as opposed to the rest of the file which will have 16.

 

echo $userDetails;

$userDetails is an array. You can't simply echo an array.

 

The first problem is it's telling my fputcsv() expects parameter 1 to be resource.

Since the code for that portion is correct, this means that

$fp = fopen('TESTCSV.csv', 'w');

failed to open the file and $fp is false. You need to check why the file cannot be created. Probably some difference between this new script and the other one that works, such as location or permissions or even whether the .csv file already exists.

 

 

CSV is meant for tabular data, where each line has the same columns as every other line. What you're doing about adding totals totally breaks that which means it's likely a bad idea. Why and how do you need the totals?

[thenorman138]

Thank you for the explanation on those points.

 

The reason for the totals: This is a script that creates and mails the CSV, which is great, but the employees who receive the report have the desire to see the totals because some agents in the report have around 80 calls and the totals can't easily be read. As of now, we have to take the created CSV, plug it into an excel workbook with the formulas for totals, and mail it manually. This requires someone to be standing by at the time the script runs and is very inconvenient, and time consuming.

 

As far as how, I'm hoping for something like this:

 

```

Extension | Inbound | Outbound | Missed | Duration

--------------------------------------------------------------------

1234                x                                                87

1234                                   x                             35

1234                                                   x             0

1234                 x                                               25

 

Totals -             2                 1             1             147

```

[requinix]

You're thinking of aggregating data: a summary of the count of a number of things, rather than reporting each of the things in detail. You can't really do both at the same time.

 

How about providing both separately? The detailed report of each call and the summary report with the statistics. Two queries, two CSV files. Both of them are easy enough to do individually.

[thenorman138]

That makes sense. So I would keep my current script that writes the detailed file, but I would use the same structure for the summary file, just change the query?

[taquitosensei]

or you could do some logic in your loop 

foreach($userDetails as $userDetail){
    if(!$totals) { 
      $totals=$userDetail; 
      $totals["Extension"]="Totals-"; 
         // Do this for each field you want to calculate // 
      $totals["Received"]=0; 
    }
       
         // Do this for each field you want to calculate // 
    if($userDetail['Received']=='x') { 
      $totals["Received"]++; 
    }
      
    /* In the following line dump the respective userdetails to csv which will show summary */
    fputcsv($fp, array_values($userDetails));
}
fputcsv($fp,array_values($totals));