runreport.php

jsp程序开发系统

				WHERE ticket.id = ticket_message.ticketid
					AND ((ticket.date_opened - ticket_message.date) < $duration) 
				GROUP BY $stat[variable1]");
			if ($result = $db->num_rows()) {
				$data_tmp[$stat['variable1']] = $result['totals'];
			}
			$db->query("SELECT ticket.id, ticket_message.date
				FROM ticket, ticket_message
				WHERE ((ticket.date_opened - ticket_message.date) < $duration)");
			while ($result = $db->row_array()) {
				$appendix[$result['id']] = 1;
			}
			$length = 1;
			break;
		case '1streply_greater': // Length; all tickets whose first response took more
								 // than the specified amount of time.
			$db->query("SELECT COUNT(*) AS totals, ticket.$stat[variable1], ticket_message.date 
				FROM ticket, ticket_message
				WHERE ticket.id = ticket_message.ticketid
					AND ((ticket.date_opened - ticket_message.date) >= $duration) 
				GROUP BY $stat[variable1]");
			if ($result = $db->num_rows()) {
				$data_tmp[$stat['variable1']] = $result['totals'];
			}
			$db->query("SELECT ticket.id, ticket_message.date
				FROM ticket, ticket_message
				WHERE ((ticket.date_opened - ticket_message.date) >= $duration)");
			while ($result = $db->row_array()) {
				$appendix[$result['id']] = 1;
			}
			$length = 1;
			break;
		case 'span_less': // Length; all tickets whose open-to-close duration is less
						  // than the specified amount of time.
			$db->query("SELECT COUNT(ticket.id) AS totals, ticket.$stat[variable1] 
				FROM ticket, ticket_def
				WHERE ((ticket.date_closed - ticket.date_opened) < $duration) 
					OR ((now() - ticket.date_opened) < $duration)
				GROUP BY $stat[variable1]");
			if ($result = $db->num_rows()) {
				while ($result = $db->row_array()) {
					$data_tmp[$result[$stat['variable1']]] = $result['totals'];
				}
			}
			$db->query("SELECT ticket.id
				FROM ticket
				WHERE ((ticket.date_closed - ticket.date_opened) < $duration)
					OR ((now() - ticket.date_opened) < $duration)");
			while ($result = $db->row_array()) {
				$appendix[$result['id']] = 1;
			}
			$length = 1;
			break;
		case 'span_more': // Length; all tickets whose open-to-close duration is more
						  // than the specified amount of time.
			$db->query("SELECT COUNT(ticket.id) AS totals, ticket.$stat[variable1] 
				FROM ticket
				WHERE ((ticket.date_closed - ticket.date_opened) >= $duration) 
					OR ((now() - ticket.date_opened) >= $duration)
				GROUP BY $stat[variable1]");
			if ($result = $db->num_rows()) {
				while ($result = $db->row_array()) {
					$data_tmp[$result[$stat['variable1']]] = $result['totals'];
				}
			}
			$db->query("SELECT ticket.id
				FROM ticket
				WHERE ((ticket.date_closed - ticket.date_opened) >= $duration)
					OR ((now() - ticket.date_opened) >= $duration)");
			while ($result = $db->row_array()) {
				$appendix[$result['id']] = 1;
			}
			$length = 1;
			break;
	}

	// If there *isn't* an interval, name translations haven't already been
	// done for us, so do them here.

	if (!$interval) {	
		if (isset($data_tmp)) {
			if (is_array($data_tmp)) {
				if (stristr($stat['variable1'], 'custom')) { // If it's a custom field, we have to figure out names ourselves.
					foreach ($data_tmp AS $key => $val) { 
						$items = explode('|||', $key);
						$tmptitle = array();
						foreach ($items AS $ikey => $ival) {
							if (is_array($tfields[$stat['variable1']])) {
								foreach ($tfields[$stat['variable1']] AS $tkey => $tval) {
									if ($tval[0] == $ival) {
										$tmptitle[] = $tval[2];
									}
								}
							} else {
								$tmptitle[] = $tfields_names[$stat['variable1']];
							}
						}
						$change[$stat['variable1']][$key] = join(', ', $tmptitle);
					}
				}
				$data = change_index($data_tmp, $change[$stat['variable1']]);
			}
		}
	} else {
		if (isset($data_tmp)) {
			$data = $data_tmp;
		}
	}

	################# 2ND VARIABLE PROCESSING #################

	// The 2nd variable is processed after the first has been run; each
	// "column" of the 1st variable's series of data is used as a key for the
	// 2nd variable run.

	if ($interval) { // If we're doing an interval search, use the dates as X series data
		switch ($stat['dateaffect']) {
			case 'opened': // By date-opened
				$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable2], ticket.date_opened
				FROM ticket
				WHERE $where
					AND date_opened >= '$startdate'
					AND date_opened < '$enddate'");
				unset($tmp);
				foreach ($intervals as $intkey => $intval) {
					$tmp[$tmpdata[$stat['variable2']]] = 0;
					foreach ($result AS $tmpdata) {
						if ($tmpdata['date_opened'] >= $intval[0] AND $tmpdata['date_opened'] < $intval[1]) {
							$tmp[$tmpdata[$stat['variable2']]]++;
						}
					}
					if (isset($tmp)) {
						$data2[date($dateformat, $intval[0])] = change_index($tmp, $change[$stat['variable2']]);
					} else {
						$data2[date($dateformat, $intval[0])] = NULL;
					}
				}
				break;
			case 'closed': // By date-closed
				$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable2], ticket.date_closed
				FROM ticket
				WHERE $where
					AND date_closed >= '$startdate'
					AND date_closed < '$enddate'");
				unset($tmp);
				foreach ($intervals as $intkey => $intval) {
					$tmp[$tmpdata[$stat['variable2']]] = 0;
					foreach ($result AS $tmpdata) {
						if ($tmpdata['date_closed'] >= $intval[0] AND $tmpdata['date_closed'] < $intval[1]) {
							$tmp[$tmpdata[$stat['variable2']]]++;
						}
					}
					if (isset($tmp)) {
						$data2[date($dateformat, $intval[0])] = change_index($tmp, $change[$stat['variable2']]);
					} else {
						$data2[date($dateformat, $intval[0])] = NULL;
					}
				}
				break;
			case 'reply': // By date of reply.
				$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable2], ticket_message.date
				FROM ticket, ticket_message
				WHERE $where
					AND ticket_message.date >= '$startdate'
					AND ticket_message.date < '$enddate'
					AND ticket.id = ticket_message.ticketid");
				unset($tmp);
				foreach ($intervals as $intkey => $intval) {
					$tmp[$tmpdata[$stat['variable2']]] = 0;
					foreach ($result AS $tmpdata) {
						if ($tmpdata['date'] >= $intval[0] AND $tmpdata['date'] < $intval[1]) {
							$tmp[$tmpdata[$stat['variable2']]]++;
						}
					}
					if (isset($tmp)) {
						$data2[date($dateformat, $intval[0])] = change_index($tmp, $change[$stat['variable2']]);
					} else {
						$data2[date($dateformat, $intval[0])] = NULL;
					}
				}
				break;
		}
	} else {

		// Otherwise, process normally, once for each piece of data in the 1st
		// variable series.

		if (($stat['variable2'] AND isset($data_tmp)) and
			($stat['variable1'] != $stat['variable2'])) {
			foreach ($data_tmp AS $key => $var) {
				$db->query("
					SELECT COUNT(*) AS totals, $stat[variable2]
					FROM ticket
					WHERE $where
					AND $stat[variable1] = '" . mysql_escape_string($key) . "'
					GROUP BY $stat[variable2]
				");

				unset($tmp);
				if ($db->num_rows()) {
					while ($result = $db->row_array()) {
						$tmp[$result[$stat['variable2']]] = $result['totals'];
					}
				}

				if (stristr($stat['variable2'], 'custom')) { // If it's a custom field, we have to figure out names ourselves.
					foreach ($data_tmp AS $key => $val) { 
						$items = explode('|||', $key);
						$tmptitle = array();
						foreach ($items AS $ikey => $ival) {
							if (is_array($tfields[$stat['variable2']])) {
								foreach ($tfields[$stat['variable2']] AS $tkey => $tval) {
									if ($tval[0] == $ival) {
										$tmptitle[] = $tval[2];
									}
								}
							} else {
								$tmptitle[] = $tfields_names[$stat['variable2']];
							}
						}
						$change[$stat['variable2']][$key] = join(', ', $tmptitle);
					}
				}

				// Perform the index exchange so the table/graph rendering
				// functions can label each axis.

				$data_tmp2[$key] = change_index($tmp, $change[$stat['variable2']]);

				$db->query("
					SELECT id
					FROM ticket
					WHERE $where
					AND $stat[variable1] = '" . mysql_escape_string($key) . "'
				");

				while ($result = $db->row_array()) {
					$appendix[$result['id']] = 1;
				}
			}

			$data2 = change_index($data_tmp2, $change[$stat['variable1']]);
		}
	}

	################# GENERATE REPORT ENTRY #################

	// With the data array loaded, the statistic's output can be generated.

	if (isset($appendix)) { // Get the appendix ticket numbers into a list.
		$appendix = array_keys($appendix);
	}

	if (!isset($data)) {
		$data = NULL;
	}

	if (!isset($data2)) {
		$data2 = NULL;
	}

	// Now call the correct function to generate the requested output.

	switch($report['format']) {
		case 'default':
		case 'HTML': // HTML generation
			switch($stat['displaytype']) {
				case 'default':
				case 'data':
					echo make_table($data, $data2, $stat, $style);
					echo "<BR />\n";
					break;
				case 'bar':
					echo make_barchart($data, $data2, $stat, $style);
					break;
				case 'databar':
					echo make_table($data, $data2, $stat, $style);
					echo make_barchart($data, $data2, $stat, $style);
					break;
				case 'pie':
					echo make_piechart($data, $data2, $stat, $style);
					break;
				case 'datapie':
					echo make_table($data, $data2, $stat, $style);
					echo make_piechart($data, $data2, $stat, $style);
					break;
				case 'csv': // If the user wants a CSV, we generate it and exit.
					header('Content-type: text/comma-separated-values');
					echo make_csv($data, $data2, $stat, $style);
					exit;
			}

			// Generate the appendix if it's been requested.

			if (($data OR $data2) AND isset($stat['appendix'])) {
				if (isset($appendix)) {
					echo make_appendix($appendix, $stat['displayfields'], $change);
				}
			}

			echo "<HR>\n";
			break;

		case 'PDF': // PDF generation
			switch($stat['displaytype']) {
				case 'default':
				case 'data':
					make_pdf_table($data, $data2, $stat);
					break;
				case 'bar':
					make_barchart($data, $data2, $stat, $style, 1);
					break;
				case 'databar':
					make_pdf_table($data, $data2, $stat);
					make_barchart($data, $data2, $stat, $style, 1);
					break;
				case 'pie':
					make_piechart($data, $data2, $stat, $style, 1);
					break;
				case 'datapie':
					make_pdf_table($data, $data2, $stat);
					make_piechart($data, $data2, $stat, $style, 1);
					break;
				case 'csv':
					mistake('CSV type not supported in PDFs!');
					exit;
					break;
			}

			// Generate the appendix if it's been requested.

			if (($data OR $data2) AND $stat['appendix']) {
				echo make_pdf_appendix($appendix, $stat['displayfields'], $change);
			}

			break;
	}
}

// Now that all the statistics have been generated, if we have to mail or save
// the report somewhere, gather it up and prepare it.

if (isset($buffer)) { // We're saving or mailing this, not emitting it.
	if ($report['format'] == 'pdf') { // The attachment is a PDF
		$tmpfname = tempnam ("/tmp", "dp2_pdf");
		$pdf->Output($tmpfname);
		$handle = fopen($tmpfname, "rb");
		$output['data'] = fread($handle, filesize($tmpfname));
		fclose($handle);
		unlink($tmpfname);
		$output['name'] = 'report.pdf';
		$output['extension'] = 'pdf';
	} else { // The attachment is HTML
		$output['data'] = ob_get_contents();
		$output['name'] = 'report.html';
		$output['extension'] = 'html';
	}

	if ($report['email']) { // Send e-mail
		global $footer;
		eval(makeemaileval('message', 'TECHBODY_report', $subject));
		dp_mail($report['email'], $subject, $message, '', '', array($output));
		$msg = 'Report mailed to ' . htmlspecialchars_uni($report['email']) . '. ';
	}

	if (isset($handle)) { // Save to disk
		fwrite($handle, $output['data']);
		fclose($handle);
		if (isset($msg)) {
			$msg .= "Report saved in file $filename.";
		} else {
			$msg = "Report saved in file $filename.";
		}
	}

	ob_end_clean();
	if (!defined('CRONZONE')) { 

		// If this is *NOT* being run as a cron job, generate a header and let
		// the admin know the run is complete.

		admin_header('Reports', 'Save/E-Mail Report');
		print $msg;
	}
	exit;
} else {

	// Finally, if the report is just meant to be viewed immediately, show it
	// and exit.

	if ($report['format'] == 'PDF') {
		$pdf->Output();
	}
}

// All done!

// ============================================
// Function make_appendix
// ============================================
// Generates an HTML table containing a list of
// all tickets matched during the reporting run
// ============================================
// Arguments:
// 		appendix	List of ticket numbers in
//					the appendix
//		appendix_fields
//					List of fields to show in
//					the appendix
//		change		Column or row name mappings
//					to use.
// ============================================
// Returns:
//		The HTML to display.
// ============================================

function make_appendix($appendix, $appendix_fields = NULL, $change = array()) {
	global $db, $tfields, $tfields_names;
	$html = "<table cellpadding=\"0\" cellspacing=\"0\" width=\"100%\"><tr><td bgcolor=\"#000000\"><table cellspacing=\"1\" cellpadding=\"3\" width=\"100%\">";
	$fields = explode(',', $appendix_fields);
	$total = count($fields);
	$count = 0;
	
	// If there weren't any tickets matched, or if the admin hasn't specified
	// anything to display, don't bother doing anything.

	if ($appendix_fields AND count($appendix)) {
		$appendix = array2sql($appendix);

		// Remap "user" fields to "userid" to match the ticket table.

		foreach ($fields as $key => $var) {
			if ($var == 'user') {
				$fields[$key] = 'userid';
			}
		}

		$appendix_fields = join(',', $fields);

		// Grab ticket data

		$db->query(
			"SELECT id, $appendix_fields
			FROM ticket
			WHERE id IN $appendix
			");
	
		// If we don't get any rows, don't bother running through any results.
		// Otherwise, generate the table headers and run through the result set
		// showing each entry.

		if ($db->num_rows()) {
			$count++;
			$total++;
			$html .= "<tr bgcolor=\"#FFFF99\"><td align=\"center\" colspan=\"$total\"><B>Found " . $db->num_rows() . " Ticket(s)</B></td></tr>";
			$html .= "<tr>\n";

			// Generate the headings.

			foreach($fields as $key => $val) {
				switch ($val) {
					case 'subject': $label = 'Subject'; break;
					case 'priority' : $label = 'Priority'; break;
					case 'tech' : $label = 'Technician'; break;
					case 'is_open' : $label = 'Open/Closed'; break;
					case 'awaiting_tech' : $label = 'Awaiting...'; break;
					case 'category' : $label = 'Category'; break;
					case 'userid' : $label = 'User'; break;
					case 'id' : next; break;
					default: $label = $val;
				}
				if (stristr($val, 'custom')) {
					$label = $tfields_names[$val];
				}
				$html .= "<td bgcolor=\"#FFFFCC\"><B>$label</B></TD>";
			}
			$html .= "<td align=\"center\" bgcolor=\"#FFFFCC\"><B>View</B></TD>";
			$html .= "</tr>\n";

			// Run through each result and generate the row.

			while ($result = $db->row_array()) {
				$html .= "<tr>\n";
				
				// Figure out what kind of field it is, and "massage" the
				// displayed data accordingly.

				foreach($fields as $key => $val) {
					switch ($val) {
						case 'id':
							next;
							break;
						case 'subject':
							if ($result[$val]) {
								$result[$val] = $result[$val];
							} else {
								$result[$val] = "<I>None</I>";
							}
							break;
						case 'category': $result[$val] = $change['category'][$result[$val]]; break;
						case 'priority': $result[$val] = $change['priority'][$result[$val]]; break;
						case 'tech': 
							if (isset($result[$val])) {