Jump to content

PHP script, add rows to total data for a CSV


thenorman138

Recommended Posts

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));

}```

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

```

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)); 
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.