Location: PHPKode > projects > Legal Case Management System > run_rep.php
<?php

/*
	This file is part of the Legal Case Management System (LCM).
	(C) 2004-2006 Free Software Foundation, Inc.

	This program is free software; you can redistribute it and/or modify it
	under the terms of the GNU General Public License as published by the
	Free Software Foundation; either version 2 of the License, or (at your
	option) any later version.

	This program is distributed in the hope that it will be useful, but
	WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
	or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
	for more details.

	You should have received a copy of the GNU General Public License along
	with this program; if not, write to the Free Software Foundation, Inc.,
	59 Temple Place, Suite 330, Boston, MA  02111-1307, USA

	$Id: run_rep.php,v 1.36 2007/03/26 15:34:31 mlutfy Exp $
*/

include('inc/inc.php');
include_lcm('inc_obj_reportgen');

function panic_not_implemented($table1, $table2) {
	// [ML] Eventually we should print a more user-friendly message,
	// but for now, lcm_panic() is the easiest to debug.

	$GLOBALS['errors']['join'] = "Report not implemented: join of $table1 and $table2.
	Please write to legalcase-hide@address.com and explain the report you are
	trying to generate. If possible, please send a sample report with fictive
	values. It is possible that either it is possible to generate this report
	by another way, or that it may be necessary to write a custom report.";

	lcm_panic($GLOBALS['errors']['join']);
}

function get_table_suffix($table) {
	if ($table == 'lcm_author')
		return "a";
	elseif ($table == 'lcm_followup')
		return "fu";
	elseif ($table == 'lcm_case')
		return "c";
	elseif ($table == 'lcm_client')
		return "cl";
	elseif ($table == 'lcm_case_client_org')
		return "cco";
	elseif ($table == 'lcm_case_author')
		return "ca";
	elseif ($table == 'lcm_stage')
		return "s";

	return "";
}

function suffix_table($table) {
	$suffix = get_table_suffix($table);

	if ($suffix)
		return " as " . get_table_suffix($table) . " ";
	else
		return "";
}

function prefix_field($table, $field) {
	$suffix = get_table_suffix($table);

	if (preg_match("/^IF/", $field))
		return $field;

	if (preg_match("/^count\(\*\)/", $field))
		return $field;

	if ($suffix)
		return $suffix . "." . $field;
	else
		return $table . "." . $field;
}

function join_tables($table1, $table2 = '', $id1 = 0, $id2 = 0, $report = null, $query = '') {
	$from  = ""; // select .. FROM [here] (for dependancies between tables)
	$from_glue = ""; // for joining the FROM
	$where = ""; // the usual stuff (LEFT JOIN ON .., WHERE ..)

	if ($report)
		$report->addComment("join_tables: " . $table1 . " - " . $table2 . " id1 = " . $id1 . " id2 = " . $id2);
		
	// lcm_debug("join_tables: " . $table1 . " - " . $table2 . " id1 = " . $id1 . " id2 = " . $id2);
	// lcm_debug(lcm_getbacktrace(false));

	$table_keys = array(
		"lcm_case" => "id_case",
		"lcm_author" => "id_author",
		"lcm_followup" => "id_followup",
		"lcm_client" => "id_client",
		"lcm_org" => "id_org",
		"lcm_keyword_case" => "id_keyword");

	if ($table1 == $table2)
		panic_not_implemented($table1, $table2);

	switch($table1) {
		case 'lcm_author':
			switch($table2) {
				case 'lcm_case':
					// $from = " LEFT JOIN lcm_case_author as ca ON (a.id_author = ca.id_author AND c.id_case = ca.id_case) ";
					$from   = " , lcm_case_author as ca ";
					$from_glue .= " a.id_author = ca.id_author AND c.id_case = ca.id_case ";
					$where .= " a.id_author = ca.id_author ";
					break;
				case 'lcm_followup':
					$where .= " a.id_author = fu.id_author ";
					break;
				case 'lcm_client':
					panic_not_implemented($table1, $table2);
					break;
				case 'lcm_org':
					panic_not_implemented($table1, $table2);
					break;
				case 'lcm_stage':
					/* TESTCASE: Count number of cases concluded by author
					 *  - row = lcm_author (name, family)
					 *  - col = lcm_stage (count)
					 *  - filter = lcm_stage.date_conclusion date_in (...)
					 */
					$from = " , lcm_case_author as ca ";
					$from_glue .= " a.id_author = ca.id_author AND s.id_case = ca.id_case ";
					$where .= " a.id_author = ca.id_author ";
					break;
				case '':
					break;
				default:
					panic_not_implemented($table1, $table2);
					break;
			}

			break;

		case 'lcm_case':
			switch($table2) {
				case '':
					break;
				case 'lcm_followup':
					$where .= " c.id_case = fu.id_case ";
					break;
				case 'lcm_author':
					// [ML] This may not generate anything interested. Was implemented just for fun.
					$from   = " , lcm_case_author as ca ";
					$from_glue .= " c.id_case = ca.id_case AND ca.id_author = a.id_author ";
					$where .= " ca.id_author = a.id_author ";
					break;
				default:
					panic_not_implemented($table1, $table2);
			}

			break;

		case 'lcm_followup':
			switch($table2) {
				case '':
					break;
				case 'lcm_author':
					$where .= " fu.id_author = a.id_author ";
					break;
				default:
					panic_not_implemented($table1, $table2);
			}

			break;

		case 'lcm_client':
			switch($table2) {
				case '':
					break;
				case 'lcm_case':
					$from   = " , lcm_case_client_org as cco ";
					$from_glue .= " cl.id_client = cco.id_client AND c.id_case = cco.id_case ";
					$where .= " cl.id_client = cco.id_client ";
					break;
				default:
					panic_not_implemented($table1, $table2);
			}
			break;

		case 'lcm_org':
			switch($table2) {
				default:
					panic_not_implemented($table1, $table2);
			}

			break;

		default:
			// Try to process as a keyword group
			$kwg = get_kwg_from_name($table1);

			switch($kwg['type']) {
				case 'case':
					$from   = " , lcm_keyword_case as kc ";
					$from_glue .= " kc.id_case = ca.id_case ";
					$table1 = 'lcm_keyword_case';
					// $where .= " cl.id_client = cco.id_client ";
					break;
			}
	}

	if ($id1) {
		if ($id2) { // [ML] TEST THIS
			$where .= " AND " . prefix_field($table1, $table_keys[$table1]) . " = $id1 ";

			if (isset($report))
				$report->addComment(" AND " .  prefix_field($table1, $table_keys[$table1]) . " = $id1 ");
		} elseif (($field = $report->getLineKeyField())) {
			// If joining some special query, check if $field in $query
			// [ML] this really needs more testing
			if ($query && preg_match('/^(.+)\./', $field, $regs)) {
				$table = $regs[1];

				if (preg_match('/as ' . $table . '/', $query)) {
					$where .= " AND $field = $id1 ";
				} elseif ($table == 'a' && preg_match('/as ca/', $query)) {
					$where .= " AND ca.id_author = $id1 ";
				} elseif ($table == 'a' && preg_match('/as fu/', $query)) {
					$where .= " AND fu.id_author = $id1 ";
				} else {
					$where .= " AND " . prefix_field($table1, $table_keys[$table1]) . " = $id1 ";
				}
			} else {
				$where .= " AND $field = $id1 ";
			}

			if (isset($report))
				$report->addComment($where);
		}
	}
	
	if ($id2) // [ML] TEST THIS
		$where .= " AND " . prefix_field($table2, $table_keys[$table2]) . " = $id2 ";

	return array($from, $from_glue, $where);
}

function get_filters_sql($report, $obj_type = '', $obj_name = '') {
	$ret = "";

	$is_missing_filters = false;
	$my_filters = get_filters($report->getId(), $obj_type, $obj_name);
	$clauses = array();

	// Apply the filter values and check for missing values
	foreach ($my_filters as $f) {
		if ($f['value']
			|| isset($_REQUEST['filter_val' . $f['id_filter']]) // text or number
			|| isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'], 'year_only') // date
			|| (isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_start", 'year_only') // interval
			   && isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_end", 'year_only')))
		{
			$filter_sql = apply_filter($f);

			if ($filter_sql)
				array_push($clauses, $filter_sql);
		} else {
			// For now, we ignore filters without type (eq/lt/gt/..) 
			// because it's a bit messy to allow input at runtime
			// (because of fields for filter value)
			if ($f['type'])
				$is_missing_filters = true;
		}
	}

	if ($is_missing_filters) {
		if (! ($report->getOption('headers_sent') == 'yes'))
			lcm_page_start(_T('title_rep_run') . " " . remove_number_prefix($rep_info['title']), '', '', 'report_intro');

		show_page_subtitle(_T('rep_subtitle_filters'), 'reports_edit', 'filters');
		echo '<p class="normal_text">';

		include_lcm('inc_conditions');
		show_report_filters($report->getId(), true);

		echo "</p>\n";
		lcm_page_end();
		exit;
	}

	$ret1 = implode(" AND ", $clauses);
	return $ret1;
}

function show_filters_info($report) {
	if (! $report->getOption('headers_sent'))
		return;

	$my_filters = get_filters($report->getId());

	if (count($my_filters))
		echo '<p class="normal_text">';

	foreach ($my_filters as $f) {
		if (! $f['value']) {
			// Value may be provided by $_REQUEST
			if (isset($_REQUEST['filter_val' . $f['id_filter']])) { 
				// text or number
				$f['value'] = $_REQUEST['filter_val' . $f['id_filter']];
			} elseif (isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'], 'year_only')) {
				// Date
				$f['value'] = get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'], 'year_only');
			} elseif (isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_start", 'year_only')
				&& isset_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_end", 'year_only'))
			{
				// Date interval
				$f['value'] = get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_start", 'start');
				$f['value'] .= ";";
				$f['value'] .= get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . "_end", 'end');
			} else {
				// Should never happen
				$f['value'] = _T('info_not_available');
			}
		}

		// TODO: If field.type == text and field.value is based on keyword or
		// list, use translation if known.
		if ($f['enum_type']) {
			$enum = explode(":", $f['enum_type']);

			if ($enum[0] == 'keyword') {
				if ($enum[2])
					$f['value'] = _Tkw($enum[2], $f['value']);
			} elseif ($enum[0] == 'list') {
				if ($enum[2])
					$f['value'] = _T($enum[2] . $f['value']);
			}
		}
	
		// Example: "Follow-up - Start: in 1 Apr 05, 00h00 - 31 Dec 05, 23h59"
		// or.....: Table - Field: type_filter value
		echo _T('rep_info_table_' . $f['table_name']) . " - " . _Ti($f['description'])
			. _T('rep_filter_' . $f['type']) . " ";

		switch($f['type']) {
			case 'date_in':
				$values = split(";", $f['value']);
				echo format_date($values[0], 'short') . " - " . format_date($values[1], 'short');
				break;
			case 'date_eq':
			case 'date_ge':
			case 'date_gt':
			case 'date_le':
			case 'date_lt':
				echo format_date($f['value'], 'short');
				break;
			default:
				echo $f['value'];
		}

		echo "<br />\n";
	}

	if (count($my_filters))
		echo "</p>\n";
}

function get_filters($id_report, $obj_type = '', $obj_name = '') {
	$my_filters = array();

	$q_fil = "SELECT v.id_filter, f.table_name, f.field_name, f.description, f.enum_type, v.type, v.value 
		FROM lcm_rep_filter as v, lcm_fields as f
		WHERE v.id_field = f.id_field
		AND v.id_report = " . $id_report;
	
	// XXX not sure how to deal with keywords
	if ($obj_type && $obj_name) {
		// special situation (apply a stage filter on a list of cases,
		// ex: show number of concluded case, by author, by case crime type)
		if ($obj_name == "'lcm_case'") 
			$obj_name = "'lcm_case', 'lcm_stage'";

		$q_fil .= " AND f.table_name IN ($obj_name) ";
	}

	$result = lcm_query($q_fil);

	while ($row = lcm_fetch_array($result))
		array_push($my_filters, $row);

	return $my_filters;
}

function apply_filter($f) {
	$ret = '';

	$filter_conv = array(
			"neq" => "!=",
			"eq" => "=",
			"lt" => "<",
			"le" => "<=",
			"gt" => ">",
			"ge" => ">="
			);

	if (! $f['type'])
		return '';

	if ($f['type'] == 'date_in') {
		$dates = array();

		if ($f['value']) {
			$dates = explode(";", $f['value']);
		} else {
			$dates[0] = get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . '_start', 'start');
			$dates[1] = get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter'] . '_end', 'end');
		}

		$ret .= "(DATE_FORMAT(" . prefix_field($f['table_name'], $f['field_name']) . ", '%Y-%m-%d') "
			.   " >= DATE_FORMAT('" . $dates[0] . "', '%Y-%m-%d')"
			. " AND DATE_FORMAT(" . prefix_field($f['table_name'], $f['field_name']) . ", '%Y-%m-%d') "
			.   " <= DATE_FORMAT('" . $dates[1] . "', '%Y-%m-%d')) ";
	} else {
		$foo = explode("_", $f['type']); // ex: date_eq
		$filter_type = $foo[0]; // date
		$filter_op = $foo[1]; // eq

		if (! $f['value'])
			if (isset($_REQUEST['filter_val' . $f['id_filter']]))
				$f['value'] = $_REQUEST['filter_val' . $f['id_filter']];

		// FIELD OPERATOR 'VALUE'
		if ($filter_conv[$filter_op]) {
			switch($filter_type) {
				case 'date':
					$ret .= "DATE_FORMAT(" . prefix_field($f['table_name'], $f['field_name']) . ", '%Y-%m-%d')"
						. " " . $filter_conv[$filter_op] . " " 
						. "DATE_FORMAT('" . get_datetime_from_array($_REQUEST, 'filter_val' . $f['id_filter']) . "', '%Y-%m-%d') ";
					break;
				case 'text':
					$ret .= $f['field_name'] 
						. " " . $filter_conv[$filter_op] . " "
						. "'" . $f['value'] . "' ";
					break;
				default: // number
					if ($f['description'] == 'time_input_length')
						$f['value'] = " " . $f['value'] . " * 3600 ";

					$ret .= $f['field_name']
						. " " . $filter_conv[$filter_op] . " "
						. $f['value'] . " ";
			}
		} else {
			lcm_log("no filter_conv for $filter_op ?");
			return '';
		}
	}

	return $ret;
}


global $author_session;

// Restrict page to administrators
if ($author_session['status'] != 'admin') {
	lcm_page_start(_T('title_rep_run'), '', '', 'report_intro');
	echo '<p class="normal_text">' . _T('warning_forbidden_not_admin') . "</p>\n";
	lcm_page_end();
	exit;
}


$_SESSION['errors'] = array();
$rep = intval(_request('rep', 0));

if (! $rep) {
	lcm_header('Location: listreps.php');
	exit;
}

//
// Show title and description of the report
//

$q = "SELECT *
		FROM lcm_report
		WHERE id_report = " . $rep;

$result = lcm_query($q);

if (! ($rep_info = lcm_fetch_array($result)))
	lcm_panic("Report # " . $rep . " doest not exist.");

if ((! $rep_info['line_src_name']) && (! $rep_info['filecustom'])) {
	$_SESSION['errors']['rep_line'] = _T('rep_warning_atleastlineinfo');
	lcm_header('Location: rep_det.php?rep=' . $rep);
	exit;
}

if ($rep_info['filecustom']) {
	include_custom_report($rep_info['filecustom']);
	$report = new CustomReportGen(intval(_request('rep')), _request('export', 'html'), _request('debug'));
} else {
	$report = new LcmReportGenUI(intval(_request('rep')), _request('export', 'html'), _request('debug'));
}

$report->printStartDoc($rep_info['title'], $rep_info['description'], 'report_intro');
	
if ($rep_info['line_src_type'] == 'table')
	$my_line_table = "lcm_" . $rep_info['line_src_name'];
else
	$my_line_table = $rep_info['line_src_name'];

//
// For report headers (used later)
//

// $do_grouping = false;
$report->setOption('do_grouping', 'no');

//
// Get report line fields, store into $report->lines for later
//

$report->setupReportLines();

//
// Get report columns fields, store into $report->columns for later
//

$do_special_join = false;

// if ($row['src_type' == 'table' && ! preg_match('/^lcm_/', $src_name))
//	$src_name = 'lcm_' . $src_name;

$q = "SELECT *
		FROM lcm_rep_col as c, lcm_fields as f
		WHERE c.id_report = " . $report->getId() . "
			AND c.id_field = f.id_field
		ORDER BY c.col_order, id_column ASC";

$result = lcm_query($q);

while ($row = lcm_fetch_array($result)) {
	$my_col_table = $row['table_name'];

	if ($row['field_name'] == "count(*)")
		$report->setOption('do_grouping', 'yes'); // $do_grouping = true;
	
	if ($row['enum_type']) {
		$enum = split(":", $row['enum_type']);

		if ($enum[0] == 'keyword') {
			if ($enum[1] == 'system_kwg') {
				// There is a 'bug' in this, because reporting sum(fu-type-time) for each case,
				// will not show hidden keywords, which include 'case_stage'. But it's odd to 
				// put a time in case_stage anyway, and showing 'hidden' keywords is not very
				// appropriate in this situation.
				$kws = get_keywords_in_group_name($enum[2]);

				// Get filters that might apply
				$tmp_my_filters = get_filters_sql($report, 'table', "'" . $row['table_name'] . "'");
				$sql_filter = ($tmp_my_filters ? " AND " . $tmp_my_filters : "");

				foreach ($kws as $k) {
					// This is for the various types of system_kwg.
					// Not very efficient, but works for now.
					if ($enum[2] == 'followups') {
						// Crossing lcm_followup with either lcm_author or lcm_case
						$sql = "SELECT sum(IF(UNIX_TIMESTAMP(fu.date_end) > UNIX_TIMESTAMP(fu.date_start), UNIX_TIMESTAMP(fu.date_end)-UNIX_TIMESTAMP(fu.date_start), 0)) FROM lcm_followup as fu WHERE type = '" . $k['name'] . "' AND fu.hidden = 'N' " . $sql_filter;
					
						// For report headers
						$k['filter_special'] = 'time_length'; // XXX
					} elseif ($enum[2] == 'conclusion' || $enum[2] == '_crimresults' || $enum[2] == 'sentence' || $enum[2] == 'stage') {
						$tmp_kw = ($enum[2] == '_crimresults' ? 'result' : $enum[2]);
						$tmp_kw = ($enum[2] == 'stage' ? 'case_stage' : $enum[2]);

						if ($my_line_table == 'lcm_author') {
							// Crossing lcm_stage with lcm_author (conclusions by author)
							$sql = "SELECT count(*) "
								. " FROM lcm_stage as s, lcm_case_author as ca, lcm_case as c "
								. " WHERE s.kw_" . $tmp_kw . " = '" . $k['name'] . "' "
								. "   AND s.id_case = ca.id_case "
								. "   AND c.id_case = ca.id_case "
								. $sql_filter;
						} elseif ($rep_info['line_src_type'] == 'keyword') {
							$sql = "SELECT count(*) "
								. " FROM lcm_stage as s, lcm_keyword_case as kc, lcm_case as c "
								. " WHERE s.kw_" . $tmp_kw . " = '" . $k['name'] . "' "
								. "   AND s.id_case = kc.id_case AND c.id_case = s.id_case "
								. $sql_filter;
						} else {
							lcm_panic("unknown enum[2] = " . $enum[2]);
						}
					} else {
						lcm_panic("unknown enum[2] = " . $enum[2]);
					}

					// For report headers
					$report->addHeader($k['title'], 'number', $k['enum_type'], $k['filter_special']);

					// Store special SQL command.
					$special_id = $report->addSpecial($sql);
					$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");
				}

				//
				// TOTAL for this enum
				//
				$sql = "";
				if ($enum[2] == 'followups') {
					$sql = "SELECT sum(IF(UNIX_TIMESTAMP(fu.date_end) > UNIX_TIMESTAMP(fu.date_start), UNIX_TIMESTAMP(fu.date_end)-UNIX_TIMESTAMP(fu.date_start), 0)) FROM lcm_followup as fu WHERE fu.hidden = 'N' " . $sql_filter;

					// For report headers
					$k['filter_special'] = 'time_length'; // XXX
				} elseif ($enum[2] == 'conclusion' || $enum[2] == '_crimresults' || $enum[2] == 'sentence' || $enum[2] == 'stage') {
					if ($my_line_table == 'lcm_author') {
						$sql = "SELECT count(*) "
							. " FROM lcm_stage as s, lcm_case_author as ca, lcm_case as c "
							. " WHERE s.id_case = ca.id_case "
							. "   AND c.id_case = ca.id_case "
							. $sql_filter;
					} elseif ($rep_info['line_src_type'] == 'keyword') {
						$sql = "SELECT count(*) "
							. " FROM lcm_stage as s, lcm_keyword_case as kc "
							. " WHERE  "
							. "   s.id_case = kc.id_case "
							. $sql_filter;
					}
				}

				// For report headers
				$report->addHeader(_Th('generic_input_total'), $k['filter'], $k['enum_type'], $k['filter_special']);

				// Store special SQL command.
				$special_id = $report->addSpecial($sql);
				$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");

				$report->setOption('do_grouping', 'yes');
				// $do_grouping = true;
			} else {
				echo "\n\n QUERY = " . $report->getSQL() . " \n\n";
				lcm_panic("Not yet implemented -" . $enum[1] . "-");
			}
		} elseif ($enum[0] == 'list') {
			$items = split(',', $enum[1]);

			foreach($items as $i) {
				// XXX should add 'where' clauses only (kwg above too..)
				$special_id = $report->addSpecial("cl.gender = '$i'");
				$report->addColumn("2 as \"LCM_SQL:special:$special_id\"");

				// $tmp = array('description' => _T($enum[2] . $i), 'filter' => 'number');
				$report->addHeader(_T($enum[2] . $i), 'number');
			}
		} else {
			echo "\n\n QUERY = " . $report->getSQL() . " \n\n";
			lcm_panic("Not yet implemented -" . $enum[0] . "-");
		}
	} elseif ($my_line_table == 'lcm_author' && $row['table_name'] == 'lcm_case' && $row['field_name'] == 'count(*)') {
		// TODO: ADD FILTERS?
		$kws = get_keywords_in_group_name('stage');

		// Get filters that might apply
		$tmp_my_filters = get_filters_sql($report, 'table', "'lcm_case'");
		$sql_filter = ($tmp_my_filters ? " AND " . $tmp_my_filters : "");

		foreach ($kws as $k) {
			$sql = "SELECT count(*) FROM lcm_case as c "
				. " LEFT JOIN lcm_case_author as ca ON (c.id_case = ca.id_case) "
				. " WHERE c.stage = '" . $k['name'] . "' "
				. $sql_filter;

			// For report headers
			// $k['filter'] = 'number';
			// $k['description'] = $k['title'];
			$report->addHeader(_Th($k['title']), 'number', $k['enum_type']);

			// Store special SQL command.
			$special_id = $report->addSpecial($sql);
			$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");
		}

		// TOTAL for this enum
		$sql = "SELECT count(*) FROM lcm_case as c, lcm_case_author as ca "
			. " WHERE c.id_case = ca.id_case "
			. $sql_filter;

		// $k['filter'] = 'number';
		// $k['description'] = _Th('generic_input_total');
		$report->addHeader(_Th('generic_input_total'), 'number', $k['enum_type']);

		// Store special SQL command. 
		$special_id = $report->addSpecial($sql);
		$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");

		// $do_grouping = true;
		$report->setOption('do_grouping', 'yes');
	} else {
		$report->addColumn(prefix_field($row['table_name'], $row['field_name']));
		$report->addHeader($row['description'], $row['filter'], $row['enum_type']);
		$do_special_join = true;
	}
}

if ($rep_info['col_src_type'] == 'keyword' && $rep_info['col_src_name'] && ! count($report->getColumns())) {
	$all_kw_names = array();
	$all_kw_ids = array();
	$kwg = get_kwg_from_name($rep_info['col_src_name']);
	$kws = get_keywords_in_group_name($rep_info['col_src_name']);

	$tmp_my_filters = get_filters_sql($report, 'table', "'lcm_case'");
	$sql_filter = ($tmp_my_filters ? " AND " . $tmp_my_filters : "");

	// Test whether there are any lcm_stage filters
	// lcm_stage filters are already caught with the above get_filters_sql(..., 'lcm_case')
	// [ML] Note: we don't want to systematically join with lcm_stage because
	// when a case has multiple stages, it will confuse the count().. well, at
	// least, that's how I prefer to leave it for compatibility between 0.6.x releases
	$tmp_my_filters_stage = get_filters_sql($report, 'table', "'lcm_stage'");
	$exists_stage_filter = false;
	if ($tmp_my_filters_stage)
		$exists_stage_filter = true;

	if ($kwg['type'] == $rep_info['line_src_name']) {
		$report->addHeader(_Th(remove_number_prefix($kwg['title'])), 'text');
		$report->addColumn("k.title as 'TRAD'");
	//	$report->addWhere("k.id_group = " . $kwg['id_group']);

	} else {

		// TODO: For the moment, this is limited to crossing the author table
		// with 'case' keywords. Ex: type-of-crimes, per author, where
		// line = author(name) and col = kw.type-of-crime
		foreach ($kws as $kw) {

			if ($kwg['type'] == 'system') 
				lcm_panic("not supported yet");

			if ($my_line_table == 'lcm_author') {
				// TODO: can't we use k.id_keyword instead? and drop lcm_keyword?
				$report->addHeader(_Th(remove_number_prefix($kw['title'])), 'number', $kw['enum_type']);

				$sql = "SELECT count(*) FROM lcm_keyword_" . $kwg['type'] . " as ka, "
					. " lcm_case_author as ca, lcm_keyword as k, lcm_case as c " . ($exists_stage_filter ? ", lcm_stage as s " : "")
					. " WHERE k.id_keyword = ka.id_keyword "
					. " AND c.id_case = ca.id_case "
					. ($exists_stage_filter ? " AND c.id_case = s.id_case " : "")
					. " AND ca.id_case = ka.id_case " // XXX
					. " AND k.name = '" . $kw['name'] . "'"
					. $sql_filter;
			} elseif ($my_line_table == 'lcm_followup') {
				$report->addHeader(_Th(remove_number_prefix($kw['title'])), 'time_input_length', '', 'time_length');

				$sql = "SELECT sum(IF(UNIX_TIMESTAMP(fu.date_end) > UNIX_TIMESTAMP(fu.date_start), UNIX_TIMESTAMP(fu.date_end)-UNIX_TIMESTAMP(fu.date_start), 0)) "
					. "FROM lcm_keyword_" . $kwg['type'] . " as ka, lcm_followup as fu, lcm_case as c "
					. "WHERE fu.id_case = ka.id_case "
					. " AND c.id_case = fu.id_case "
					. " AND ka.id_keyword = " . $kw['id_keyword']
					. $sql_filter;

				// $do_grouping = true;
				$report->setOption('do_grouping', 'yes');
			}

			// Store special SQL command. This was stored as "LCM_SQL: very long SQL",
			// but it caused problems on some installations (MySQL 4.1.x on W32) and
			// would cut the SQL string at the 256th character.
			$special_id = $report->addSpecial($sql);
			$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");

			$all_kw_names[] = $kw['name'];
			$all_kw_ids[] = $kw['id_keyword'];
	}

	// Items WITHOUT keyword
	// [ML] HIGHLY EXPERIMENTAL, but hey, it works! :-)
	// well, it works for: show new cases by case-type (e.g. crime), for each author
	if (isset($_REQUEST['show_nokw']) && $_REQUEST['show_nokw']) {
		if ($my_line_table == 'lcm_author') {

		$sql = "SELECT count(*) FROM "
			. " lcm_case_author as ca," /* lcm_keyword as k, */ . " lcm_case as c " . ($exists_stage_filter ? ", lcm_stage as s " : "")
			. " LEFT JOIN lcm_keyword_" . $kwg['type'] . " as ka ON ka.id_case = c.id_case "
			. "  AND ka.id_keyword IN (" . implode(",", $all_kw_ids) . ")"
			. " WHERE c.id_case = ca.id_case "
			. ($exists_stage_filter ? " AND c.id_case = s.id_case " : "")
			// . " AND ca.id_case = ka.id_case " // XXX
			. " AND ka.id_case IS NULL "
			. $sql_filter;
		}

		// [ML] NOTE: if crossing lcm_followup with case-keyword,
		// I am not bothering with the "having no keyword" column
		// because I am fed up of this mess (well, the SELECT for the
		// lines of the reports needs to be fixed for this) XXX

		$report->addHeader("Test", 'number', $k['enum_type']);

		$special_id = $report->addSpecial($sql);
		$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");
		$report->setOption('show_nokw', 'yes');
		$report->setOption('allow_show_nokw', 'yes');
	} else {
		$report->setOption('allow_show_nokw', 'yes');
	}

	// TOTAL for this enum
	// Note: the k.name IN (...) is because other keywords might be associated 
	// with this case/client/etc
	if ($my_line_table == 'lcm_author') {
		$report->addHeader(_Th('generic_input_total'), 'number', $k['enum_type']);

		$sql = "SELECT count(*) FROM lcm_keyword_" . $kwg['type'] . " as ka, "
			. " lcm_case_author as ca, lcm_keyword as k, lcm_case as c " . ($exists_stage_filter ? ", lcm_stage as s " : "")
			. " WHERE k.id_keyword = ka.id_keyword "
			. " AND c.id_case = ca.id_case "
			. ($exists_stage_filter ? " AND c.id_case = s.id_case " : "")
			. " AND ca.id_case = ka.id_case " // XXX
			. " AND k.id_keyword IN (" . implode(",", $all_kw_ids) . ")"
			. $sql_filter;
	} elseif ($my_line_table == 'lcm_followup') {
		$report->addHeader(_Th('generic_input_total'), 'time_input_length', $k['enum_type'], 'time_length');

		$sql = "SELECT sum(IF(UNIX_TIMESTAMP(fu.date_end) > UNIX_TIMESTAMP(fu.date_start), UNIX_TIMESTAMP(fu.date_end)-UNIX_TIMESTAMP(fu.date_start), 0)) "
			. "FROM lcm_followup as fu, lcm_case as c "
			. "WHERE fu.id_case = c.id_case " . $sql_filter;
	}

	// Store special SQL command. 
	$special_id = $report->addSpecial($sql);
	$report->addColumn("1 as \"LCM_SQL:special:$special_id\"");

	}
}

//
// Add implicit fields if there will be a join table
// For example, if we select fields from lcm_author, we should include id_author
// even if we don't want to show it (for table joining, later).
// 

if ($rep_info['line_src_type'] == 'table'
	&& preg_match("/^lcm_(.*)$/", $my_line_table, $regs)
	&& count($report->getColumns()))
{

	// Check first if any id_foo was provided.
	// for example, crossing lcm_following and case-keyword using id_case explicitely
	// Note: it may be ex: fu.id_case, hence the strange regexp
	$tmp_lines = $report->getLines();

	foreach ($tmp_lines as $l) {
		if (preg_match("/^(.+\.)?id_/", $l)) {
			if (! $report->getLineKeyField())
				$report->setLineKeyField($l);
		}
	}

	if (! $report->getLineKeyField()) {
		$temp = get_table_suffix($my_line_table);
		if ($temp) {
			$temp .= ".id_" . $regs[1];
			$report->setLineKeyField($temp);
		}
	}
} elseif ($rep_info['line_src_type'] == 'keyword' && count($report->getColumns())) {
	$report->setLineKeyField('k.id_keyword');
}

//
// Start building the SQL query for the report lines
//

$my_line_fields = implode(", ", $report->getLines());
$my_col_fields  = implode(", ", $report->getColumns());

$report->addSQL("SELECT " . $my_line_fields);

// Hide implicit fields, but allow them to be in 'group by' if necessary
if ($my_line_fields && $report->getLineKeyField()) {
	$my_line_fields .= ", " . $report->getLineKeyField(); // [ML] only for backward compat
	$report->addSQL(", " . $report->getLineKeyField() . " as 'LCM_HIDE_ID' ");
	$report->addLine($report->getLineKeyField() . " as 'LCM_HIDE_ID'");
}

if ($my_col_fields)
	$report->addSQL(", " . $my_col_fields);

if ($rep_info['line_src_type'] == 'table') {
	$report->addSQL(" FROM " . $my_line_table . suffix_table($my_line_table));

	if ($rep_info['col_src_type'] == 'keyword' && $rep_info['col_src_name']) {
		$kwg = get_kwg_from_name($rep_info['col_src_name']);
		$kws = get_keywords_in_group_name($rep_info['col_src_name']);
		$kw_list_id = array();

		foreach($kws as $k)
			$kw_list_id[] = $k['id_keyword'];

		// FIXME lcm_case and lcm_followup specific!
		if ($my_line_table == 'lcm_case') {
			$report->addSQL(" LEFT JOIN lcm_keyword_case as kc "
					. " ON (kc.id_case = c.id_case AND kc.id_keyword IN (" . join(',', $kw_list_id) . ")) ");
			$report->addSQL(" LEFT JOIN lcm_keyword as k ON (k.id_keyword = kc.id_keyword) ");
		} elseif ($my_line_table == 'lcm_followup') {
			$report->addSQL(" LEFT JOIN lcm_keyword_case as kc "
					. " ON (kc.id_case = fu.id_case AND kc.id_keyword IN (" . join(',', $kw_list_id) . ")) ");
			$report->addSQL(" LEFT JOIN lcm_keyword as k ON (k.id_keyword = kc.id_keyword) ");
		}

	}

} elseif ($rep_info['line_src_type'] == 'keyword') {
	$report->addSQL(" FROM lcm_keyword as k "
		. " LEFT JOIN lcm_keyword_group as kwg "
		. " ON (k.id_group = kwg.id_group AND kwg.name = '" . $rep_info['line_src_name'] . "')");
		
	$report->addWhere("kwg.name IS NOT NULL");
}

// Join condition
if ($rep_info['line_src_type'] == 'table') {
	// join my_line_table with my_col_table
	if ($my_col_table && $do_special_join) {
		// from join (ex: dependancy on middle tables, such as Author-Case => lcm_case_author)
		$deps = join_tables($my_line_table, $my_col_table);
	
		if ($deps[0])
			$report->addSQL($deps[0]);

		if ($deps[1])
			$report->addWhere($deps[1]);

		$report->addSQL(" LEFT JOIN " . $my_col_table . suffix_table($my_col_table) . " ON (" . $deps[2] . " ) ");
	}
} elseif ($rep_info['line_src_type'] == 'keyword') {
	switch($rep_info['line_src_name']) {
		default:
			switch($my_col_table) {
				case '':
					break;
				case 'lcm_case':
					$report->addSQL(" LEFT JOIN lcm_keyword_case as kc ON (kc.id_keyword = k.id_keyword) ");
					break;
				case 'lcm_client':
					$report->addSQL(" LEFT JOIN lcm_keyword_client as kc ON (kc.id_keyword = k.id_keyword) ");

					if ($my_line_table != 'lcm_client')
						$report->addSQL(" LEFT JOIN lcm_client as cl ON (cl.id_client = kc.id_client) ");
					break;
				case 'lcm_stage':
					// Program goes here when crossing: case-keyword + lcm_stage
					/*
					lcm_panic("If you are trying to count the number of cases
						depending on their conclusion/result/sentence, try to use
						only 'case stage' as the report row, with the fields:
						conclusion (or result/sentence) + count");
					*/
					break;
				default:
					lcm_panic($report->getSQL() . " <br/> Not implemented: line_src = " . $rep_info['line_src_name'] 
							. ", col_table = " . $my_col_table);
			}
	}
}

//
// Fetch all filters for this report
//

$tmp_tables = "'$my_line_table'";
if ($my_col_table && $do_special_join)
	$tmp_tables .= ",'$my_col_table'";

$my_filters_sql = get_filters_sql($report, 'table', $tmp_tables);

if ($my_filters_sql)
	$report->addSQL("WHERE " . $my_filters_sql);

//
// Add the last "where" conditions
//

if (count($report->getWhere())) {
	if ($my_filters_sql)
		$report->addSQL(" AND ");
	else
		$report->addSQL(" WHERE ");
	
	$report->addSQL(implode(" AND ", $report->getWhere()));
}

if ($report->getOption('do_grouping') == 'yes') { // $do_grouping) {
	$group_fields = "";
	$tmp = array();
	$my_lines = $report->getLines();

	foreach($my_lines as $l)
		if (preg_match("/(.*) as .*/", $l, $regs))
			$tmp[] = $regs[1];
		elseif (! preg_match("/.*count\(\*\)/", $l))
			$tmp[] = $l;

	$group_fields = implode(',', $tmp);
	$report->addSQL(" GROUP BY " . $group_fields);
}

//
// Ready!
//

// [ML] The SQL dump is also shown at the end of the report, but when
// special queries fail, it is useful to have it before the SQL error.
if ($report->getOption('headers_sent') == 'yes' && $_REQUEST['debug'] == 2) {
	echo "\n\n<!-- QUERY = " . $report->getSQL() . " -->\n\n";
	for($cpt = 0; $cpt < $report->getSpecialCount(); $cpt++)
		echo "<!-- \t - $cpt: " . $report->getSpecial($cpt) . " -->\n";

	if (isset($_REQUEST['debug'])) {
		$dbg = $report->getJournal();

		foreach($dbg as $line)
			echo $line;
	}
}

if ($rep_info['filecustom']) {
	$result = null;
} else {
	$result = lcm_query($report->getSQL(), true);

	if (! $result) {
		if (isset($_REQUEST['debug'])) {
			echo "The report could not be generated. Please send the following
				information to the software developers if you think that this is a
				bug."; // TRAD

				echo "SQL = " . $report->getSQL();

			$dbg = $report->getJournal();

			foreach($dbg as $line)
				echo $line;
		} else {
			$tmp_link = new Link();
			$tmp_link->addVar('debug', '2');

			echo "The report could not be generated. Try running again using the "
				. '<a href="' . $tmp_link->getUrl() . '">debug mode</a>.'; // TRAD
		}

		exit;
	}
}

//
// Show filters applied
//

show_filters_info($report);

//
// Ready for report line
//

$report->printHeaderValueStart();

$my_headers = $report->getHeaders();

foreach ($my_headers as $h)
	$report->printHeaderValue($h['description']);

$report->printHeaderValueEnd();

if ($rep_info['filecustom']) 
	$report->run();

for ($cpt_lines = $cpt_col = 0; $result && ($row = lcm_fetch_array($result)); $cpt_lines++) {
	$report->printStartLine();

	foreach ($row as $key => $val) {
		if ((! is_numeric($key)) && ($key != 'LCM_HIDE_ID')) {
			$cpt_items = 0;

			$css = 'class="tbl_cont_' . ($cpt_lines % 2 ? "light" : "dark") . '"';
			$align = 'align="left"';

			//
			// Special cases
			//
			if ($my_headers[$cpt_col]['field_name'] == 'description')
				$val = get_fu_description($row);

			if ($val == "1" && preg_match("/^LCM_SQL:special:(.*)/", $key, $regs)) {
				$deps = join_tables($my_line_table, '', $row['LCM_HIDE_ID'], 0, $report, $report->getSpecial($regs[1]));
				$q_col = $report->getSpecial($regs[1]); // Fetch special rule
				$q_col .= $deps[2]; // WHERE [...]

				$allow_zoom = false;
				$zooming = false;

				// [ML] Limit zooming to queries involving cases, because we have no 
				// test cases for other uses
				if (preg_match("/lcm_case/", $q_col))
					$allow_zoom = true;

				if (isset($_REQUEST['zoom' . $cpt_lines . "-" . $cpt_col])) {
					$zooming = true;
					// FIXME (specific to reports involving cases (?))
					$q_col = preg_replace("/count\(\*\)/", "c.title, c.id_case", $q_col);
				}

				$report->addComment("[$cpt_lines:$cpt_col] $q_col");
				$result_tmp = lcm_query($q_col);
				$val = "";

				if ($zooming) 
					$val = '<div align="left"><ul style="padding: 0; padding-left: 1em; margin: 0;">';

				while($row_tmp = lcm_fetch_array($result_tmp)) {
					if ($zooming) {
						$tmp_link = new Link("case_det.php");
						$tmp_link->addVar('case', $row_tmp['id_case']); // XXX specific

						// This puts <td> in values ... 
						// $row_tmp[0] = get_ui_print_value($row_tmp[0], $my_headers[$cpt_col]);

						// FIXME [ML] $report should have a method, such as $r->supportsHtml()
						// or $r->addHtml() .. 
						if ($_REQUEST['export'] == 'csv' || $_REQUEST['export'] == 'ods') {
							$val .= $row_tmp[0];
						} else {
							$val .= '<li style="padding: 0; margin: 0;">';
							$val .= '<a class="content_link" href="' . $tmp_link->getUrl() . '">';
							$val .= $row_tmp[0];
							$val .= "</a></li>\n";
						}

						$cpt_items++;
					} else {
						if ($allow_zoom && $row_tmp[0] != 0) {
							$tmp_link = new Link();
							$tmp_link->addVar('zoom' . $cpt_lines . "-" . $cpt_col, 1);

							if ($_REQUEST['export'] == 'csv' || $_REQUEST['export'] == 'ods') { // FIXME
								$val .= $row_tmp[0];
							} else {
								$val .= '<a class="content_link" style="display: block;" href="' . $tmp_link->getUrl() . '">';
								$val .= $row_tmp[0];
								$val .= "</a>";
							}
						} else {
							$val .= $row_tmp[0];
						}

						$cpt_items += $row_tmp[0];
					}
				}

				if ($zooming) {
					$val .= "</ul></div>";

					$tmp_link = new Link();
					$tmp_link->delVar('zoom' . $cpt_lines . "-" . $cpt_col);
					$val .= '(<a class="content_link" href="' . $tmp_link->getUrl() . '">x</a>)';
				}

			} elseif ($val == "2" && preg_match("/^LCM_SQL:special:(.*)/", $key, $regs)) {
				$tmp = $report->getSpecial($regs[1]); // Fetch special rule

				$q_col = "SELECT count(*) ";
				$q_col .= strstr($report->getSQL(), "FROM");

				//
				// Experimental magic
				//
				$tmp .= " AND k.id_keyword = " . $row['LCM_HIDE_ID'];

				if (preg_match("/.*WHERE.*/", $q_col))
					$q_col = preg_replace("/WHERE/", "WHERE $tmp AND ", $q_col);
				else
					$q_col .= " WHERE $tmp ";

				$foo = split(" ", $tmp);
				if (preg_match("/.*GROUP BY.*/", $q_col))
					$q_col = preg_replace("/GROUP BY.*/", "GROUP BY " . $foo[0], $q_col);
				else
					$q_col .= " GROUP BY " . $foo[0];

				$result_tmp = lcm_query($q_col);

				$val = "";

				while($row_tmp = lcm_fetch_array($result_tmp)) {
					$val .= $row_tmp[0];

					if (is_numeric($row_tmp[0]) && $row_tmp[0] > 0)
						$cpt_items += $row_tmp[0];
				}
			} elseif ($key == 'TRAD') {
				$val = remove_number_prefix(_Th($val));

				// [ML] I don't remember what $val might be, but it is probably 
				// numeric (if we are translating it), but just in case..
				if (is_numeric($val) && $val > 0)
					$cpt_items += $val;
			} else {
				if (is_numeric($val) && $val > 0)
					$cpt_items += $val;
			}

			// Translate values based on keywords (ex: fu.type)
			if ($my_headers[$cpt_col]['enum_type']) {
				$enum = split(":", $my_headers[$cpt_col]['enum_type']);

				if ($enum[0] == 'keyword') {
					if ($enum[1] == 'system_kwg') {
						if ($val) // XXX lcm_panic if kw does not exist
							$val = _Tkw($enum[2], $val);
					}
				} elseif ($enum[0] == 'list') {
					if ($enum[2])
						$val = _T($enum[2] . $val);
				}
			}

			// For end 'total' (works with datetime/number)
			$report->addTotal($cpt_col, $cpt_items);
			$report->printValue($val, $my_headers[$cpt_col], $css);
			$cpt_col = ($cpt_col + 1) % count($my_headers);
		}
	}

	$report->printEndLine();
	$report->incrementLine();
}

// 
// Footer
//
$css = 'class="tbl_cont_' . (($cpt_lines + 1) % 2 ? "light" : "dark") . '"';
$cpt_tmp = 0;

$report->printStartLine();

$my_headers = $report->getHeaders();

foreach ($my_headers as $h) {
	if ((! preg_match('/^(.+\.)?id_.+/', $h['field_name']))
		&& ($h['filter'] == 'number' || $h['filter'] == 'currency' || $h['filter_special'] == 'time_length'))
		$report->printValue($report->getTotal($cpt_tmp), $h, $css);
	elseif ($cpt_tmp == 0)
		$report->printValue(_Th('generic_input_total'), $h, $css);
	else
		$report->printValue('', $h, $css);
	
	$cpt_tmp++;
}

$report->printEndLine();
$report->printEndDoc();

if ($report->getOption('headers_sent') == 'yes') {
	echo "</table>\n";

	echo "<p>Number of rows: " . $report->getRowCount() . "</p>\n"; // TRAD

	// Report footnotes (ex: signed by manager, etc. -- allow HTML)
	echo $rep_info['notes'];

	if ($report->getOption('allow_show_nokw') == 'yes') {
		$tmp_link = new Link();
		$tmp_link->delVar('show_nokw');

		if ($report->getOption('show_nokw') == 'yes') {
			echo '<p><a href="' . $tmp_link->getUrl() . '" class="run_lnk">' . _T('rep_button_nokw_hide') . "</a></p>\n";
		} else {
			$tmp_link->addVar('show_nokw', "1");
			echo '<p><a href="' . $tmp_link->getUrl() . '" class="run_lnk">' . _T('rep_button_nokw_show') . "</a></p>\n";
		}
	}

	echo '<p><a href="rep_det.php?rep=' . $report->getId() . '" class="run_lnk">' . _T('rep_button_goback') . "</a></p>\n";

	//
	// Make a link to export the report
	//
	echo '<p>';

	$link_csv = new Link();
	$link_csv->delVar('export');
	$link_csv->addVar('export', 'csv');

	echo '<a href="' . $link_csv->getUrl() . '" class="exp_lnk">' . _T('rep_button_exportcsv') . '</a> ';

	$link_ods = new Link();
	$link_ods->delVar('export');
	$link_ods->addVar('export', 'ods');

	echo '<a href="' . $link_ods->getUrl() . '" class="exp_lnk">' .  _T('rep_button_exportcsv') . ' (ODS)' . '</a>'; // TRAD
	
	echo "</p>\n";

	//
	// Print debug information, if requested
	//
	if (isset($_REQUEST['debug'])) {
		$dbg = $report->getJournal();

		foreach($dbg as $line)
			echo $line;
	}
	
	lcm_page_end();
}


?>
Return current item: Legal Case Management System