runreport.php

jsp程序开发系统

<?php
// +-------------------------------------------------------------+
// | DeskPRO v [2.0.1 Production]
// | Copyright (C) 2001 - 2004 Headstart Solutions Limited
// | Supplied by WTN-WDYL
// | Nullified by WTN-WDYL
// | Distribution via WebForum, ForumRU and associated file dumps
// +-------------------------------------------------------------+
// | DESKPRO IS NOT FREE SOFTWARE
// +-------------------------------------------------------------+
// | License ID : Full Enterprise License =) ...
// | License Owner : WTN-WDYL Team
// +-------------------------------------------------------------+
// | $RCSfile: runreport.php,v $
// | $Date: 2004/02/10 01:34:25 $
// | $Revision: 1.128 $
// +-------------------------------------------------------------+
// | File Details:
// | - Report generator (administration interface)
// +-------------------------------------------------------------+

error_reporting(E_ALL & ~E_NOTICE);
require_once('./global.php');

// License checks

//Nullify WTN-WDYL Team
feature_check('reports');

// Stop JPgraph from installing an error handler here, since it causes problems.
define('NO_GRAPHIC_HANDLER', 'true');

// Need the calendar functions here for date slinging.
include "./../includes/functions/calendar_functions.php";

// default do
$_REQUEST['do'] = trim($_REQUEST['do']);
if (!isset($_REQUEST['do']) or $_REQUEST['do'] == "") {
	$_REQUEST['do'] = "runreport";
}

// globalise variables
$global = array	(
			array('id', 'number')
);
rg($global);

// MODE: previewstat

// If the administrator has requested a preview of a statistic, 
// we generate it here. The reporting engine runs 365 days into
// the past to gather statistics to show the preview (so this
// doesn't always look as good as it should.

if ($_REQUEST['do'] == 'previewstat') {
	$work[] = $db->query_return("SELECT * FROM report_stat WHERE id = '$id'");
	if (!$db->num_rows()) {
		mistake('No valid statistic specified.');
		exit;
	}
	$work[0]['title'] = "Statistic Preview ({$work[0][title]})";
	$style = array('id' => 1, 'name' => 'style', 'title_colour' => '', 'description_colour' => '');
	$report = array(
		'id'			=>	1,
		'title'			=>	"{$work[0][title]}",
		'name'			=>	'None',
		'description'	=>	'',
		'lastrun'		=>	0,
		'format'		=>	'html',
		'email'			=>	'',
		'repeattype'	=>	'',
		'value1'		=>	'',
		'value2'		=>	'',
		'style'			=>	'1',
		'path'			=>	''
	);
	$date = split('-', strtotime(date('Y-m-d').' - 1 year'));
	$_REQUEST['ydate'] = $date[0];
	$_REQUEST['mdate'] = $date[1];
	$_REQUEST['ddate'] = $date[2];
	$_REQUEST['number'] = 1;
	$_REQUEST['type'] = 'years';
	if ($work['displaytype'] == 'csv') {
		$csv = 1;
	}
} else {
	// MODE: regular report

	// If we're *not* generating a preview, we're actually running the
	// report instead. Run through the given range of time and gather
	// statistics.

	$report = $db->query_return("SELECT * FROM report WHERE id = '$id'");
	$style = $db->query_return("SELECT * FROM report_style WHERE id = '$report[style]'");

	$db->query("
		SELECT report_stat.*
		FROM report_relations
		LEFT JOIN report_stat ON (report_stat.id = report_relations.statid)
		WHERE reportid = '$id'
	");

	$work = array();

	while ($stat = $db->row_array()) {
		$work[] = $stat;
		if ($stat['displaytype'] == 'csv') {
			$csv = 1;
		}
	}
}

// CSV statistics must be standalone; if even one statistic in a report is a
// CSV, we have to ignore the rest and just process the CSV.

if ($csv) { 
	$csv = array();
	foreach ($work as $stat) {
		if ($stat['displaytype'] == 'csv') {
			$csv[] = $stat;
		}
	}
	$work = array($csv[0]);
	$report['format'] = 'html';
}

// If the report's format is PDF, set up the PDF object and prepare it for
// data.

if ($report['format'] == 'PDF') {
	define('FPDF_FONTPATH','../includes/fpdf/font/');
	require('../includes/fpdf/fpdf.php');

	$pdf = new FPDF('P', 'mm', 'Letter');
	$pdf->Open();
	$pdf->AddPage();
	if ($report[title]) {
		$pdf->SetTitle($report['title']);
		$pdf->SetCreator('DeskPro v2.0');
		$pdf->SetFont('Times', 'B', 24);
		$pdf->Cell(0, 10, $report['title'], 0, 1, 'C');
	}
}

// If the report is to be mailed or saved, validate the inputs and set up if
// needed.

if ($report['email'] or $report['path']) { // Check for save-to-disk or e-mail options
	if (!validate_email($report['email'])) { // We've got a valid e-mail address
		$ext_error = 'The e-mail address specified, ' . htmlspecialchars_uni($report['email']) . ', is not valid. Not mailing.';
		$report['email'] = '';
	}

	if ($report['path']) { // Permissions checks for writing report to file.
		$filename = htmlspecialchars_uni($report['path']);
		if (is_dir($report['path'])) { // If it's a directory, we need to make up a name and try creating the file
			$tmpname = tempnam ($report['path'], 'report');
			if ($report['format'] == 'html') {
				$tmpname .= '.html';
			} else {
				$tmpname .= '.pdf';
			}
			$filename = htmlspecialchars_uni($tmpname);
			$handle = fopen($tmpfname, "wb");
			if (!$handle) {
				$ext_error = "Cannot create $filename.";
				$report['path'] = '';
			}
		} elseif (file_exists($report['path'])) { // Else, if it's a file, it'd best not exist yet.
			$ext_error = 'The filename you specified, ' . htmlspecialchars_uni($report['path']) . ', already exists.';
			$report['path'] = '';
		} else {
			$handle = @fopen($report['path'], 'wb');
			if (!$handle) {
				$ext_error = 'Cannot open ' . htmlspecialchars_uni($report['path']);
				$report['path'] = '';
			}
			if (!$handle) {
				$ext_error = "Cannot create $filename; make sure DeskPRO has permission to create files in this location.";
				$report['path'] = '';
			}
		}
	}
}

// Same check, different actions if they're still true now that we've validated some things.

if ($report['email'] or $report['path']) { // Do we need to save this output to a buffer for later handling?
	$buffer = 1;
	ob_start();
} elseif ($ext_error) {
	$report['format'] = 'html';
	print "<B>WARNING:</B> $ext_error Producing HTML output<BR />\n";
}

// Each statistic is run independently of any others in a report. They are
// processed in the order the database query returns them in. This loop runs
// once for each statistic.

foreach ($work as $stat) {
	unset($appendix);
	unset($data);
	unset($data2);
	unset($data_tmp);
	unset($data_tmp2);
	unset($where);
	unset($daterange);
	$interval = 0;

	// Unserialize data fields if any are present.
	
	if (isset($stat['fixed_general'])) {
		$fixed_general = unserialize($stat['fixed_general']);
	}

	if (isset($stat['fixed_user'])) {
		$fixed_user = unserialize($stat['fixed_user']);
	}
	
	if (isset($stat['fixed_ticket'])) {
		$fixed_ticket = unserialize($stat['fixed_ticket']);
	}

	// =========== Begin building the first level query ============

	$where = '1 ';

	################# FIXED GENERAL FIELDS ################# 

	// open / closed
	if ($fixed_general['open_status'] == 'open') {
		$where .= " AND is_open = 1";
	} elseif ($fixed_general['open_status'] == 'closed') {
		$where .= " AND is_open = 0";
	}

	// awaiting tech / user
	if (isset($fixed_general['awaiting_status'])) {
		if ($fixed_general['awaiting_status'] == 'tech') {
			$where .= " AND awaiting_tech";
		} elseif ($fixed_general['awaiting_status'] == 'user') {
			$where .= " AND !awaiting_tech";
		}
	}

	// category
	if (isset($fixed_general['category'])) {
		if (is_array($fixed_general['category'])) {
			$where .= " AND category IN " . array2sql($fixed_general['category']);
		}
	}

	// priority
	if (isset($fixed_general['category'])) {
		if (is_array($fixed_general['priority'])) {
			$where .= " AND priority IN " . array2sql($fixed_general['priority']);
		}
	}

	################# FIXED CUSTOM TICKET FIELDS ################# 

	// For each custom ticket field specified by the statistic, add the correct
	// search clause to the query.

	if (is_array($fixed_ticket)) {
		$values = array();
		$db->query("SELECT * FROM ticket_def");
		while ($result = $db->row_array()) {
			$display_name = unserialize($result['display_name']);
			$tfields_names[$result['name']] = $display_name[1];
			$tfields[$result['name']] = $result;
			$ticket_fields[$result['name']] = $result['formtype'];
		}
		foreach ($fixed_ticket AS $key => $var) {
			if (in_array($key, array_keys($ticket_fields)) AND $fixed_ticket[$key]) {
				$where .= field_search($tfields[$key], 
					$fixed_ticket[$key],
					$fixed_ticket["extra$key"],
					'ticket',
					$fixed_ticket[$key."_match"],
					$fixed_ticket[$key."_not"]);
			}
		}
	}

	################# FIXED CUSTOM USER FIELDS ################# 

	// For each custom user field specified by the statistic, add the correct
	// search clause to the query. This is done by running another query to
	// find all matching users, then using those results to add to the primary
	// query.
	
	if (is_array($fixed_user)) {
		$values = array();
		$db->query("SELECT * FROM user_def");
		while ($result = $db->row_array()) {
			$display_name = unserialize($result['display_name']);
			$ufields_names[$result['name']] = $display_name[1];
			$ufields[$result['name']] = $result;
			$user_fields[$result['name']] = $result['formtype'];
		}

		$where2 = '1';

		foreach ($fixed_user AS $key => $var) {
			if (in_array($key, array_keys($user_fields)) AND $fixed_user[$key]) {
				$where2 .= field_search($ufields[$key], 
					$fixed_user[$key],
					$fixed_user["extra$key"],
					'user',
					$fixed_user[$key."_match"],
					$fixed_user[$key."_not"]);
			}
		}

		$db->query("SELECT user.id FROM user WHERE $where2");
		$techs = array();
		if ($db->num_rows()) {
			while ($result = $db->row_array()) {
				$techs[] = $result['id'];
			}
			$techs = join(',', $techs);
			$where .= " AND ticket.userid IN ($techs)";
		}
	}
	################# DATE ################# 

	$startdate = formatymd("$_REQUEST[ydate]-$_REQUEST[mdate]-$_REQUEST[ddate]");
	if (validate_ymd($startdate) && $_REQUEST['number'] && $_REQUEST['type']) {
		$enddate = strtotime("$startdate + $_REQUEST[number] $_REQUEST[type]");
		$startdate = strtotime($startdate);
		$daterange = " AND date_opened >= '$startdate'
			AND date_opened < '$enddate' ";
	}

	################# PRE PROCESSING (TO CHANGE INDEXES FROM NUMBERS TO VALUES) #################

	$change['is_open'] = array(
		0 => 'Closed',
		1 => 'Open'
	);

	$change['awaiting_tech'] = array(
		0 => 'Awaiting User Response',
		1 => 'Awaiting Technician Response'
	);

	// category
	$db->query("SELECT id, name FROM ticket_cat");
	while ($result = $db->row_array()) {
		$change['category'][$result['id']] = $result['name'];
	}
	$change['category'][0] = 'No category specified';

	// priority
	$db->query("SELECT id, name FROM ticket_pri");
	while ($result = $db->row_array()) {
		$change['priority'][$result['id']] = $result['name'];
	}
	$change['priority'][0] = 'No priority specified';

	// tech
	$db->query("SELECT id, username FROM tech");
	while ($result = $db->row_array()) {
		$change['tech'][$result['id']] = $result['username'];
	}
	$change['tech'][0] = 'Unassigned';

	// users
	$db->query("SELECT id, username FROM user");
	while ($result = $db->row_array()) {
		$change['userid'][$result['id']] = $result['username'];
	}
	$change['userid'][0] = 'Unowned';

	################# 1ST VARIABLE PROCESSING #################

	// If this statistic has a Date Affects setting, then we're doing some
	// custom processing. 

	// If it's an "interval"-style setting, the intervals make up the "first
	// variable" for this statistic, and the real "1st Variable" becomes the
	// "2nd Variable"; we gather all the first-pass query results together to
	// let 2nd Variable processing handle it from there.

	// If it's a "length"-style setting, we're just searching for tickets that
	// match some function-of-time value and so can just be added to the WHERE
	// clause; we process those here.

	if ($stat['dateaffect'] != 'none') {

		// Build the intervals list (dividing the total report range into
		// sections of time as specified by the statistic

		$intervals = explode(',', $stat['variable1times']);
		$i_years = $intervals[0];
		$i_days  = $intervals[1];
		$i_hours = $intervals[2];
		$i_mins  = $intervals[3];

		if (!$i_hours AND !$i_mins) {
			$dateformat = 'm/d/Y';
		} elseif (!$i_years AND !$i_days) {
			$dateformat = 'h:i:sa';
		} else {
			$dateformat = 'm/d/Y h:i:sa';
		}

		$duration = ($i_mins * 60) + ($i_hours * 3600) + ($i_days * 86400) + ($i_years * 31536000);
		$intervals = get_intervals($startdate, $enddate,
			"+ $i_years years $i_days days $i_hours hours $i_mins minutes"
		);
	}

	switch ($stat['dateaffect']) {
		default: // Regular processing
			$db->query("SELECT COUNT(*) AS totals, $stat[variable1]
				FROM ticket
				WHERE $where $daterange
				GROUP BY ticket.$stat[variable1]
			");

			while ($result = $db->row_array()) {
				$data_tmp[$result[$stat['variable1']]] = $result['totals'];
			}


			$db->query("SELECT ticket.id
				FROM ticket
				WHERE $where $daterange
			");

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

			break;
		case 'opened': // Interval: all tickets opened between start and end,
					   // grouped by interval
			$stat['variable2'] = $stat['variable1'];
			$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable1], ticket.date_opened
				FROM ticket
				WHERE $where
					AND date_opened >= '$startdate'
					AND date_opened < '$enddate'");
			foreach ($intervals as $intkey => $intval) {
				$data_tmp[date($dateformat, $intval[0])] = 0;
				foreach ($result AS $tmpdata) {
					if ($tmpdata['date_opened'] >= $intval[0] AND $tmpdata['date_opened'] < $intval[1]) {
						$data_tmp[date($dateformat, $intval[0])]++;
						$appendix[$tmpdata['id']] = 1;
					}
				}
			}
			$interval = 1;
			break;
		case 'closed': // Interval; all tickets closed between start and end,
					   // grouped by interval
			$stat['variable2'] = $stat['variable1'];
			$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable1], ticket.date_closed
				FROM ticket
				WHERE $where
					AND date_closed >= '$startdate'
					AND date_closed < '$enddate'");
			if (is_array($intervals)) {
				foreach ($intervals as $intkey => $intval) {
					$data_tmp[date($dateformat, $intval[0])] = 0;
					if (is_array($result)) {
						foreach ($result AS $tmpdata) {
							if ($tmpdata['date_closed'] >= $intval[0] AND $tmpdata['date_closed'] < $intval[1]) {
								$data_tmp[date($dateformat, $intval[0])]++;
								$appendix[$tmpdata['id']] = 1;
							}
						}
					}
				}
			}
			$interval = 1;
			break;
		case 'reply': // Interval; all tickets replied to between start and end,
					  // grouped by interval
			$stat['variable2'] = $stat['variable1'];
			$result = $db->query_return_array("SELECT ticket.id, ticket.$stat[variable1], 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");
			foreach ($intervals as $intkey => $intval) {
				$data_tmp[date($dateformat, $intval[0])] = 0;
				foreach ($result AS $tmpdata) {
					if ($tmpdata['date'] >= $intval[0] AND $tmpdata['date'] < $intval[1]) {
						$data_tmp[date($dateformat, $intval[0])]++;
						$appendix[$tmpdata['id']] = 1;
					}
				}
			}
			$interval = 1;
			break;
		case '1streply_less': // Length; all tickets whose first response took less
							  // than the specified amount of time.
			$db->query("SELECT COUNT(*) AS totals, ticket.$stat[variable1], ticket_message.date 
				FROM ticket, ticket_message