Report Mailer for the ZenDesk API

zendesk-report-mailer.php

<?php
// This script pulls two reports from ZenDesk and optionally emails them. 
//   Search for all instances of YOUR_ in this document and replace them. 
//   Uncomment the email-related lines at the bottom to enable sending emails.

// Copyright (c) 2012, Will Bradley, will@zyphon.com
// All rights reserved.
// Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
// Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
// Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
// Neither the name of the <ORGANIZATION> nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

date_default_timezone_set('America/Phoenix');
ini_set("SMTP","YOUR_EMAIL_SERVER"); 
ini_set("smtp_port","25");
$from = "YOUR_FROM_ADDRESS";
$to = "YOUR_TO_ADDRESS";
define("ZDAPIKEY", "YOUR_ZENDESK_API_KEY");
define("ZDUSER", "YOUR_ZENDESK_USER");
define("ZDURL", "https://trillium.zendesk.com/api/v2");
/* Note: do not put a trailing slash at the end of v2 */

/* Functions */
function curlWrap($url, $json, $action)
{
  $ch = curl_init();
	curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
	curl_setopt($ch, CURLOPT_MAXREDIRS, 10 );
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
	curl_setopt($ch, CURLOPT_CAINFO, getcwd() . "\usertrust-root.crt");  // YOUR_ROOT_SSL_CERTIFICATE_HERE
	curl_setopt($ch, CURLOPT_URL, ZDURL.$url);
	curl_setopt($ch, CURLOPT_USERPWD, ZDUSER."/token:".ZDAPIKEY);
	switch($action){
		case "POST":
			curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
			curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
			break;
		case "GET":
			curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
			break;
		case "PUT":
			curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
			curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
			break;
		case "DELETE":
			curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
			break;
		default:
			break;
	}

	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json'));
	curl_setopt($ch, CURLOPT_USERAGENT, "MozillaXYZ/1.0");
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_TIMEOUT, 10);
	$output = curl_exec($ch);
	//var_dump(curl_error($ch));
	curl_close($ch);
	$decoded = json_decode($output);
	return $decoded;
}

function lookupUserName($userid, $userarray) {
/* Expect:
  [0]=>
  object{ 
    ["id"]=>int
    ["name"]=>string
    ["url"]=>string}
  [1]...
  */

  foreach($userarray as $user) {
    if($user->id == $userid) {
	  return $user->name;
	}
  }
  return null;
}


function lookupOrganization($orgid, $orgarray) {
  foreach($orgarray->organizations as $org) {
    if($org->id == $orgid) {
	  return $org->name;
	}
  }
  return null;
}

function trimString($input, $maxLen = 30)
{
    if(strlen($input) < $maxLen)
    {
        return $input;
    }
	else {
		$output = substr($input,0,$maxLen-3);
		return $output."...";
	}
}

function humanTime($timeString) {
	$time = strtotime($timeString);
	
	if(time() - $time > 172800) {
	  return date("M d",$time);
	}
	elseif(time() - $time > 86400) {
	  return "Yesterday ".date("g a",$time);
	}
	else {
	  return "Today ".date("g a",$time);
	}
}

function outputReport($title, $rawtickets, $solvedReport = false, $orgFilter = null) {
global $orgs;
$output = "";

if(count($rawtickets->rows) > 0) {
  // preprocess the data into groups
  $groupedtickets = array();
  foreach($rawtickets->rows as $rawticket) {
    $thisOrg = lookupOrganization($rawticket->organization_id,$orgs);
	if($thisOrg == null) { 
	  $thisOrg = "Corporate"; 
	}
	
	// filter out other orgs if orgFilter is set
	if($orgFilter == null || $orgFilter == $rawticket->organization) {
      $groupedtickets[$thisOrg][] = $rawticket;
	}
  }
  ksort($groupedtickets);

  // begin output
  $output .= "<h2 style='font-family: Calibri, Tahoma, Helvetica, sans-serif; font-size: 14pt'>".$title."</h2>";
  $output .= "<table cellpadding='6' cellspacing='0' border='0' style='font-family: Calibri, Tahoma, Helvetica, sans-serif; font-size: 10pt'>";
  $output .= "<tr style='background-color: #aaa; font-weight: bold;'><td>Subject</td><td>Requester</td><td>Created</td>";
  if($solvedReport) {
	$output .= "<td>Solved</td>";
    $output .= "<td>By</td></tr>";
  }
  else {
	$output .= "<td>Last Updated</td>";
    $output .= "<td>Assignee</td></tr>";
  }
  $even = false;
  
  // cancel output if there are no tickets
  if(count($groupedtickets)<1){
    return false;
  }
  
  foreach ($groupedtickets as $organization => $tickets) {
    // output group headers
    $output .= "<tr><th colspan='6' style='text-align: left;'>".$organization."</th></tr>";
  
  
	  // output tickets
	  foreach($tickets as $row) {
	  //var_dump($row);
		$output .= "<tr";
		if($even) {
		  $output .= " style='background-color: #fff'";
		}
		else {
		  $output .= " style='background-color: #eee'";
		}
		$even = !$even;
		$output .= ">";
		$output .= "<td><a style='color: #000' href='YOUR_ZENDESK_TICKETS_URL_HERE".$row->ticket->id."'>".trimString($row->subject,45)."</a></td>";
		$output .= "<td>".trimString(lookupUserName($row->requester_id, $rawtickets->users),18)."</td>";
		$output .= "<td>".humanTime($row->created)."</td>";
		if($solvedReport) {
			$output .= "<td>".humanTime($row->solved)."</td>";
		}
		else {
			$output .= "<td>".humanTime($row->updated)."</td>";
		}
		$agentname = explode(" ",lookupUserName($row->assignee_id, $rawtickets->users));
		$output .= "<td>".$agentname[0]."</td>";
		$output .= "</tr>";
	  }
  }
  $output .= "</table>";
}

return $output;
}

function emailReport($from, $to, $subject, $message) {

	$message = "<html><body>".$message."</body></html>";

	$headers = 'From: '.$from.'' . "\r\n" .
		'Reply-To: '.$from.'' . "\r\n" .
		'MIME-Version: 1.0' . "\r\n" .
		'Content-type: text/html; charset=iso-8859-1' . "\r\n" .
		'X-Mailer: PHP/' . phpversion();
	mail($to, $subject, $message, $headers);
}

// RUNTIME
// $orgs is a global variable for pulling organization info & must be called first
$orgs = curlWrap("/organizations.json", null, "GET");

// "Report: All Open Tickets"
$openTix = curlWrap("/views/YOUR_VIEW_ID_HERE/execute.json", null, "GET");
echo outputReport("Open Tickets",$openTix);

// "Report: Tickets solved this week"
$solvedTix = curlWrap("/views/YOUR_OTHER_VIEW_ID_HERE/execute.json", null, "GET");
echo outputReport("Tickets Solved This Week",$solvedTix,true);


// if this isn't a dry run...
//if(!isset($_GET['private'])) {
    // send emails to each manager...
//	foreach($orgs->organizations as $org) {
	  // but only if there's an email address in the org details...
//	  if(preg_match('/@/',$org->details)) {
//		$report = outputReport($org->name." Open Tickets",$openTix,false,$org->id);
//		$report .= outputReport($org->name." Solved Tickets",$solvedTix,true,$org->id);
		// and if the report isn't blank (returned false)
//		if($report) {
		  // (prepend a header)
//		  $report = "<p style='font-family: Calibri, Tahoma, Helvetica, sans-serif; font-size: 11pt'>Here's a weekly update on your tickets:</p>".$report;
//		  emailReport($from, $org->details, $subject_prefix.$org->name." Weekly Ticket Report", $report);
//		}
//	  }
//	}
//}

// send a summary email to execs/IT:
$report = "<p style='font-family: Calibri, Tahoma, Helvetica, sans-serif; font-size: 11pt'>Here's a weekly update on tickets:</p>";
$report .= outputReport("All Open Tickets",$openTix);
$report .= outputReport("Solved Tickets this Week",$solvedTix,true);
//emailReport($from, $to, $subject_prefix."Weekly Ticket Report", $report);
echo $report;

?>