<?php

require_once('commonfunctions.php');

##comment
// Base class for DB field classes. The class implements interface and several methods for
// simple type fields (numbers and strings for example).
// Objects are created using 'create' fabric method.
// Main properties of a field include:
//  - Field type. A subclass for each type is present.
//  - Interval. Has the same meaning as in wizard.
//  - Name. The same as is in the original SQL report query.
//  - Alias. Internal alias for the field in generated sql query.
//  It may be the same for many fields in a query.
//  - Start. Sequential field number in a query. It is used together with alias.
//  One field may generate several entries in select clause of a query.
//
//  The following classes are implemented:
//   
//      ReportField
//      ^    ^    ^
//      |    |    +-- ReportCharField
//      |    |
//      |    +-- ReportNumericField
//      |
//      +-- ReportDateField
##

$report_classfields = array();
function create_reportfield($name, $type, $interval, $alias)
{
	global $report_classfields;
	if($type)
	{
		if(isset($report_classfields[$type]))
		{
			return new $report_classfields[$type]($name, $interval, $alias);
		}
		else
		{
			die('Unsupported group field type: `'.$type.'`');
		}
	}
}

class ReportField
{
	var $_interval = 0;
	var $_name = '';
	var $_alias = '';
	var $_sqlname = '';
	var $_start = 0;
	var $_caseSensitive = false;
	var $_recordBasedRequest = false;
	var $_rowsInSummary = 0;
	var $_rowsInHeader = 0;
	var $_viewFormat = '';
	var $_orderBy = 'ASC';
	var $_oldAlgorithm = false;
	
	function ReportField($name, $interval, $alias)
	{
		$this->_name = db_addslashes($name);
		$this->_interval = $interval;
		$this->_alias = $alias;
		$this->_sqlname = $alias;
	}
	
	##comment
	// Internal helper function. It must be overriden in a child class.
	// It is used in a case, when grouping is done by one expression returned in one string.
	// If used, getSelectSql() and getWhereSql() functions must not be overriden in a child class.
	##
	function getStringSql() { die; }
	
	##comment
	// Returns formatted or not formatted group header. Must be overriden in a child class.
	##
	function getFieldName($fieldValue, $data = null) { die; }

	##comment
	// Returns entry for select clause of SQL request for the field.
	// $hasGrouping - True if SQL query has 'GROUP BY' clause, False otherwise.
	##
	function getSelectSql($hasGrouping = false)
	{
        return $this->getStringSql() . ($this->alias() ? ' as ' . cached_ffn($this->alias()) : '');
	}
	
	##comment
	// Returns SQL entry for 'GROUP BY' clause.
	##
	function getGroupSql()
	{
		return $this->getStringSql();
	}
	
	##comment
	// Returns SQL entry for 'ORDER BY' clause.
	##
	function getOrderSql()
	{
		//return $this->alias().' ASC';
		return $this->getStringSql().' '.$this->_orderBy.' ';
	}
	
	##comment
	// Returns SQL entry for 'WHERE' clause.
	// $groups Is a slice of array returned by ReportBuilder.
	##
	function getWhereSql($groups) { die; }
	
	##comment
	// Returns group id from row of query resultset.
	// Group id is passed to getWhereSql() later.
	// There is no limitations on type of returned value.
	// It may be scalar or PHP array.
	##
	function getGroup($data)
	{
		return $data[$this->alias()];
	}
	
	##comment
	// Helper function which returns key for data row.
	##
	function getKey($data)
	{
		return $data[$this->alias()];
	}
	
	##comment
	// Setter for 'start' property of the field.
	// Returns start value for next field in a query.
	##
	function setStart($start)
	{
		$this->_start = $start;
		$this->_sqlname = $this->alias();
		return $start + 1;
	}
	
	##comment
	// Name property getter.
	##
	function name()
	{
		return $this->_name;
	}
	
	##comment
	// Alias property getter. Start number is automatically added to alias property.
	##
	function alias()
	{
		return $this->_alias . $this->_start;
	}
	
	##comment
	// Returns true if getFieldName() function returns formatted value.
	##
	function overrideFormat()
	{
		return false;
	}
	
	function setCaseSensitive($cs)
	{
		$this->_caseSensitive = $cs;
	}
	
	function cutNull(&$range, $checkEmty = false)
	{
		$ret = false;
		$out = array();
		for($nCnt = 0; $nCnt < count($range); $nCnt++)
		{
			$b = false;
			if($range[$nCnt] === null)
			{
				$b = true;
				$ret = true;
			}
			else
			{
				if($checkEmty && (!$range[$nCnt] || strcasecmp($range[$nCnt], 'null') == 0))
				{
					$b = true;
					$ret = true;
				}
			}
			
			if(!$b)
			{
				$out []= $range[$nCnt];
			}
		}
		$range = $out;
		return $ret;
	}
	
	function getLtGt(&$lt, &$gt)
	{
		if($this->_orderBy != 'ASC')
		{
			$lt = ' >= ';
			$gt = ' <= ';
		}
		else
		{
			$lt = ' <= ';
			$gt = ' >= ';
		}
	}
}

$report_classfields['numeric'] = 'ReportNumericField';
class ReportNumericField extends ReportField
{
	function ReportNumericField($name, $interval, $alias)
	{
		ReportField::ReportField($name, $interval, $alias);
	}
	
	function getStringSql()
	{
		$fname = $this->_oldAlgorithm ? GetFullFieldName($this->_name) : cached_ffn($this->_name);
		if($this->_interval > 0)
		{
##if @BUILDER.nDatabaseType==nDATABASE_MySQL || @BUILDER.nDatabaseType==nDATABASE_MSSQLServer || @BUILDER.nDatabaseType==nDATABASE_PostgreSQL##
			return 'floor('.$fname.'/'.$this->_interval.')*'.$this->_interval;
##elseif @BUILDER.nDatabaseType==nDATABASE_Access##
			return 'Int('.$fname.'/'.$this->_interval.')*'.$this->_interval;
##elseif @BUILDER.nDatabaseType==nDATABASE_Oracle##
			return '(floor('.$fname.'/'.$this->_interval.')*'.$this->_interval.')';
##endif##
		}
		else
		{
			return $fname;
		}
	}
	
	function getFieldName($fieldValue, $data)
	{
		$value = $data[$this->_recordBasedRequest ? $this->_name : $this->_sqlname];
		if($value == null)
			return 'NULL';
		if($this->_interval > 0)
			return intval($value) . ' - ' . (intval($value) + $this->_interval);
		else
			return intval($value);
	}
	
	function getKey($data)
	{
		if($this->_recordBasedRequest)
		{
			if($this->_interval > 0)
				return intval($data[$this->_name]/$this->_interval)*$this->_interval;
			else
				return $data[$this->_name];
		}
		else
			return ReportField::getKey($data);
	}
	
	function getWhereSql($groups)
	{
		$ret = '';
		$ssql = $this->getStringSql();
		$hasNull = $this->cutNull($groups);
	
		if(count($groups) > 0)
		{
			$lt = '';
			$gt = '';
			$this->getLtGt($lt, $gt);
			$ret = '('.$ssql.$gt.$groups[0].' AND '.$ssql.$lt.$groups[count($groups) - 1].')';
		}
		
		if($hasNull)
		{
			$ret .= ($ret ? ' OR ' : '').$ssql.' IS NULL';
		}
		
		return $ret ? '('.$ret.')' : '';
	}
}

$report_classfields['char'] = 'ReportCharField';
class ReportCharField extends ReportField
{
	function ReportCharField($name, $interval, $alias)
	{
		ReportField::ReportField($name, $interval, $alias);
	}
	
	function getStringSql()
	{
		$fname = $this->_oldAlgorithm ? GetFullFieldName($this->_name) : cached_ffn($this->_name);
		if($this->_interval > 0)
		{
##if @BUILDER.nDatabaseType==nDATABASE_MySQL || @BUILDER.nDatabaseType==nDATABASE_PostgreSQL##
            return 'substr('.$fname.', 1, '.$this->_interval.')';
##elseif @BUILDER.nDatabaseType==nDATABASE_MSSQLServer##
            return 'substring('.$fname.', 1, '.$this->_interval.')';
##elseif @BUILDER.nDatabaseType==nDATABASE_Access##
            return 'Mid('.$fname.', 1, '.$this->_interval.')';
##elseif @BUILDER.nDatabaseType==nDATABASE_Oracle##
            return 'SUBSTR('.$fname.', 1, '.$this->_interval.')';
##endif##
		}
		else
		{
			return $fname;
		}
	}
	
	function getFieldName($fieldValue, $data)
	{
		$value = $data[$this->_recordBasedRequest ? $this->_name : $this->_sqlname];
		if($value == null)
			return 'NULL';
		if($this->_interval > 0)
	        return substr($value, 0, $this->_interval);
		else
			return $value;
	}
	
	function getKey($data)
	{
		if($this->_recordBasedRequest)
		{
			if($this->_interval > 0)
			{
				if($this->_caseSensitive)
					return substr($data[$this->_name], 0, $this->_interval);
				else
					return strtolower(substr($data[$this->_name], 0, $this->_interval));
			}
			else
			{
				if($this->_caseSensitive)
					return $data[$this->_name];
				else
					return strtolower($data[$this->_name]);
			}
		}
		else
		{
			if($this->_caseSensitive)
				return $data[$this->alias()];
			else
				return strtolower($data[$this->alias()]);
		}
	}
	
	function getWhereSql($groups)
	{
		$ret = '';
		$ssql = $this->getStringSql();
		$hasNull = $this->cutNull($groups);
		
		if(count($groups) > 0)
		{
			$gr = array();
			foreach($groups as $g)
				$gr []= '\''.$g.'\'';
				
			$lt = '';
			$gt = '';
			$this->getLtGt($lt, $gt);
			
			$ret = '('.$ssql.$gt.'\''.db_addslashes($groups[0]).'\' AND '.$ssql.$lt.'\''.db_addslashes($groups[count($groups) - 1]).'\')';
		}
		
		if($hasNull)
		{
			$ret .= ($ret ? ' OR ' : '').$ssql.' IS NULL';
			$ret .= ' OR '.$ssql.'=\'\'';
		}
		
		return $ret ? '('.$ret.')' : '';
	}
}

$report_classfields['date'] = 'ReportDateField';
class ReportDateField extends ReportField
{
	function ReportDateField($name, $interval, $alias)
	{
		ReportField::ReportField($name, $interval, $alias);
	}
	
	function setStart($start)
	{
		$this->_start = $start;
		if($this->_interval == 0)
			$this->_sqlname = $this->alias();
		else
			$this->_sqlname = $this->alias().'MIN';
		return $start + ($this->_interval > 0 ? $this->_interval : 1);
	}
	
	function getSqlList($all = true)
	{
	    $grp = array();

		$fname = $this->_oldAlgorithm ? GetFullFieldName($this->_name) : cached_ffn($this->_name);
		if($all)
		{
		// array = (year, quarter, month, week, day of week, full date, hour, minute)
		// each element = array(sql begin, sql end, prev index)
##if @BUILDER.nDatabaseType==nDATABASE_MySQL##
		$symbols = array(array('YEAR(', ')', -1), array('QUARTER(', ')', 0), array('MONTH(', ')', 0),
						 array('WEEK(', ')', 0), array('DATE(', ')', -1),
						 array('HOUR(', ')', 4), array('MINUTE(', ')', 5));
##elseif @BUILDER.nDatabaseType==nDATABASE_PostgreSQL##
		$symbols = array(array('date_part(\'year\', ', ')', -1), array('date_trunc(\'quarter\', ', ')', -1), array('date_trunc(\'month\', ', ')', -1),
						 array('date_trunc(\'week\', ', ')', -1), array('date_trunc(\'day\', ', ')', -1),
						 array('date_trunc(\'hour\', ', ')', 1), array('date_trunc(\'minute\', ', ')', -1));
##elseif @BUILDER.nDatabaseType==nDATABASE_MSSQLServer##
		$symbols = array(array('DATEPART(year, ', ')', -1), array('DATEPART(quarter, ', ')', 0), array('DATEPART(month, ', ')', 0),
						 array('DATEPART(week, ', ')', 0), array('DATEPART(day, ', ')', 2),
						 array('DATEPART(hour, ', ')', 4), array('DATEPART(minute, ', ')', 5));
##elseif @BUILDER.nDatabaseType==nDATABASE_Access##
		$symbols = array(array('DatePart(\'yyyy\', ', ')', -1), array('DatePart(\'q\', ', ')', 0), array('DatePart(\'m\', ', ')', 0),
						 array('DatePart(\'ww\', ', ')', 0), array('DatePart(\'d\', ', ')', 2),
						 array('DatePart(\'h\', ', ')', 4), array('DatePart(\'n\', ', ')', 5));
##elseif @BUILDER.nDatabaseType==nDATABASE_Oracle##
		$symbols = array(array('EXTRACT(year from ', ')', -1), array('FLOOR(EXTRACT(month from ', ')/3)', 0), array('EXTRACT(month from ', ')', 0),
						 array('TRUNC(', ', \'D\')', -1), array('TRUNC(', ', \'J\')', -1),
						 array('TRUNC(hour from ', ')', -1), array('TRUNC(minute from ', ')', -1));
##endif##

			$idx = $this->_interval - 1;
			do
			{
				array_unshift($grp, $symbols[$idx][0].cached_ffn($this->_name).$symbols[$idx][1]);
				$idx = $symbols[$idx][2];
			}
			while($idx >= 0);
		}

		return $grp;
	}
	
	function getSelectSql($hasGrouping)
	{
		$fname = $this->_oldAlgorithm ? GetFullFieldName($this->_name) : cached_ffn($this->_name);
		if($this->_interval == 0)
		{
			return $fname . ' as ' . cached_ffn($this->alias());
		}
		else
		{
			$grp = $this->getSqlList();
		
			for($nCnt = 0; $nCnt < count($grp); $nCnt ++)
			{
				$grp[$nCnt] .=  ' as ' . cached_ffn($this->_alias.($nCnt + $this->_start));
			}
		
			if($hasGrouping)
			{
				$grp[] = 'MIN('.$fname.') as '.cached_ffn($this->alias().'MIN');
				$grp[] = 'MAX('.$fname.') as '.cached_ffn($this->alias().'MAX');
			}
			else
				$grp[] = $fname.' as '.cached_ffn($this->alias().'MIN');
			
	        return join(', ', $grp);
		}
	}
	
	function getGroupSql()
	{
		if($this->_interval == 0)
		{
			return cached_ffn($this->_name);
		}
		else
		{
			$grp = $this->getSqlList();
	        return join(', ', $grp);
		}
	}
	
	function getOrderSql()
	{
		if($this->_interval == 0)
		{
			$fname = $this->_oldAlgorithm ? GetFullFieldName($this->_name) : cached_ffn($this->_name);
			return $fname.' '.$this->_orderBy.' ';
		}
		else
		{
			$grp = $this->getSqlList();
			$newgrp = array();
			foreach($grp as $g)
				$newgrp[] = $g . ' '.$this->_orderBy.' ';
	        return join(', ', $newgrp);
		}
	}
	
	function getWhereSql($groups)
	{
		$ret = '';
		$hasNull = $this->cutNull($groups, true);
		
		if(count($groups) > 0)
		{
			$lt = '';
			$gt = '';
			
			if($this->_interval == 0)
			{
				$this->getLtGt($lt, $gt);
				$ret = '('.cached_ffn($this->_name).' '.$gt.' '.db_datequotes($groups[0]).
					' AND '.cached_ffn($this->_name).' '.$lt.' '.db_datequotes($groups[count($groups)-1]).')';
			}
			else
			{
				if($groups[0]['MIN'] <= $groups[count($groups) - 1]['MAX'])
				{
					$ret = '('.cached_ffn($this->_name).' >= '.db_datequotes($groups[0]['MIN']).
						' AND '.cached_ffn($this->_name).' <= '.db_datequotes($groups[count($groups)-1]['MAX']).')';
				}
				else
				{
					$ret = '('.cached_ffn($this->_name).' <= '.db_datequotes($groups[0]['MAX']).
						' AND '.cached_ffn($this->_name).' >= '.db_datequotes($groups[count($groups)-1]['MIN']).')';
				}
			}
		}
			
		if($hasNull)
			$ret .= ($ret ? ' OR ' : '').cached_ffn($this->_name).' IS NULL ';
		
		return $ret ? '('.$ret.')' : '';
	}
	
	function getFieldName($fieldValue, $data)
	{
		global $locale_info;
		
		$value = $data[$this->_recordBasedRequest ? $this->_name : $this->_sqlname];
		if($value == null || !$value || strcasecmp($value, 'null') == 0)
			return 'NULL';
		
		if($this->_interval == 0)
		{
			if($this->_viewFormat)
			{
				return GetDataValue($value, $this->_name, $this->_viewFormat);
			}
			else
			{
				$date = db2time($value);
				return str_format_datetime($date);
			}
		}
		
		switch($this->_interval)
		{
			case 1:
				$date = cached_db2time($value);
				return $date[0];
				
			case 2:
				$date = cached_db2time($value);
				return $date[0] . '/Q' . intval($date[1])/3;
						
			case 3:
				$date = cached_db2time($value);
				return @$locale_info["LOCALE_SABBREVMONTHNAME".$date[1]]." ".$date[0];
					
			case 4:
				return cached_formatweekstart($value);
					
			case 5:
				$date = cached_db2time($value);
				return format_shortdate($date);
					
			case 6:
				$date = db2time($value);
				$date[4]=0;
				$date[5]=0;
				return str_format_datetime($date);
					
			case 7:
				$date = db2time($value);
				$date[5]=0;
				return str_format_datetime($date);
		}
	}
	
	function getGroup($data)
	{
		if($this->_interval == 0)
			return $data[$this->alias()];
		else
		{
			if($data[$this->alias().'MIN'] == null || $data[$this->alias().'MAX'] == null)
				return null;
			else
				return array('MIN' => $data[$this->alias().'MIN'], 'MAX' => $data[$this->alias().'MAX']);
		}
	}

	function getKey($data)
	{
		if(!$this->_recordBasedRequest)
		{
			if($this->_interval == 0)
			{
				return $data[$this->alias()];
			}
			else
			{
		        $key = array();
				for($nCnt = $this->_start; $nCnt < $this->_interval + $this->_start; $nCnt++)
				{
		            $key []= $data[$this->_alias.$nCnt];
		        }
		        return join('-', $key);
			}
		}
		else
		{
			$strdate = $data[$this->_name];
			if($strdate == null)
				return 'NULL';
			
			if($this->_interval == 0)
			{
				return $strdate;
			}
			else
			{
				switch($this->_interval)
				{
					case 1:
						$date = cached_db2time($strdate);
						return $date[0];
						
					case 2:
						$date = cached_db2time($strdate);
						return $date[0].'-'.intval($date[1]/3);
						
					case 3:
						$date = cached_db2time($strdate);
						return $date[0].'-'.$date[1];
					
					case 4:
						$start = cached_getweekstart($strdate);
						return $start[0].'-'.$start[1].'-'.$start[2];
					
					case 5:
						$date = cached_db2time($strdate);
						return $date[0].'-'.$date[1].'-'.$date[2];
					
					case 6:
						$date = db2time($strdate);
						return $date[0].'-'.$date[1].'-'.$date[2].'-'.$date[3];
					
					case 7:
						$date = db2time($strdate);
						return $date[0].'-'.$date[1].'-'.$date[2].'-'.$date[3].'-'.$date[4];
				}
			}
		}
	}
	
	function overrideFormat()
	{
		return true;
	}

	function cutNull(&$range, $checkEmpty = false)
	{
		$ret = false;
		$out = array();
		for($nCnt = 0; $nCnt < count($range); $nCnt++)
		{
			$b = false;
			if($range[$nCnt] === null)
			{
				$b = true;
				$ret = true;
			}
			else
			{
				if($checkEmpty)
				{
					if(is_array($range[$nCnt]))
					{
						if(!$range[$nCnt]['MIN'] || strcasecmp($range[$nCnt]['MIN'], 'null') == 0)
						{
							$b = true;
							$ret = true;
						}
					}
					else
					{
						if(!$range[$nCnt] || strcasecmp($range[$nCnt], 'null') == 0)
						{
							$b = true;
							$ret = true;
						}
					}
				}
			}
			
			if(!$b)
			{
				$out []= $range[$nCnt];
			}
		}
		$range = $out;
		return $ret;
	}
	
}

function getFormattedValue($value, $fieldName, $strViewFormat, $strEditFormat = '')
{
	if($strViewFormat == FORMAT_TIME && is_numeric($value))
	{
		$val = '';
		
		$d = intval($value / 86400);
		$h = intval(($value % 86400) / 3600);
		$m = intval((($value % 86400) % 3600) / 60);
		$s = (($value % 86400) % 3600) % 60;
		
		$val .= $d > 0 ? $d . 'd ' : '';
		$val .= str_format_time(array(0, 0, 0, $h, $m, $s));
	}
	else
	{
		if($strEditFormat == EDIT_FORMAT_LOOKUP_WIZARD || $strEditFormat == EDIT_FORMAT_RADIO)
			$val = DisplayLookupWizard($fieldName, $value, null, '', MODE_LIST);
		else
			$val = GetDataValue($value, $fieldName, $strViewFormat);
	}
		
	return $val;
}

$cache_db2time = array();
function cached_db2time($strtime)
{
	global $cache_db2time;
	if(!isset($cache_db2time[$strtime]))
	{
		$res = db2time($strtime);
		$cache_db2time[$strtime] = $res;
		return $res;
	}
	else
		return $cache_db2time[$strtime];
}

$cache_getdayofweek = array();
function cached_getdayofweek($strtime)
{
	global $cache_getdayofweek;
	if(!isset($cache_getdayofweek[$strtime]))
	{
		$date = cached_db2time($strtime);
		$res = getdayofweek($date);
		$cache_getdayofweek[$strtime] = $res;
		return $res;
	}
	else
		return $cache_getdayofweek[$strtime];
}

$cache_getweekstart = array();
function cached_getweekstart($strtime)
{
	global $cache_getweekstart;
	if(!isset($cache_getweekstart[$strtime]))
	{
		$date = cached_db2time($strtime);
		$res = getweekstart($date);
		$cache_getweekstart[$strtime] = $res;
		return $res;
	}
	else
		return $cache_getweekstart[$strtime];
}

$cache_formatweekstart = array();
function cached_formatweekstart($strtime)
{
	global $cache_formatweekstart;
	if(!isset($cache_formatweekstart[$strtime]))
	{
		$start = cached_getweekstart($strtime);
		$end = adddays($start, 6);
		$res = format_shortdate($start)." - ".format_shortdate($end);
		$cache_formatweekstart[$strtime] = $res;
		return $res;
	}
	else
		return $cache_formatweekstart[$strtime];
}

$cache_fullfieldname = array();
function cached_ffn($field)
{
	global $cache_fullfieldname;
	if(!isset($cache_fullfieldname[$field]))
	{
		$res = AddFieldWrappers($field);
		$cache_fullfieldname[$field] = $res;
		return $res;
	}
	else
		return $cache_fullfieldname[$field];
}

///////////////////////////////////////////////////////////////////////////////////
class SQLStatement
{
	var $_fields = array();
	var $_hasDetails = true;
	var $_originalSql = null;
	var $_order_in;
	var $_order_out;
	var $_order_old;
	var $_aggregates = array();
	var $_skipCount = 0;
	var $_reportPageSummary = true;
	var $_reportGlobalSummary = true;
	var $_reportSummary = true;
	var $_details = true;
	var $_from = 0;
	var $_pagesize = 20;
	var $_limitLevel = 0;
	var $_hasGroups = true;
	var $_where = null;
	var $_recordBasedRequest = false;
	var $_oldAlgorithm = false;
	
	function SQLStatement($sql, $order, $pagesize, $connection)
	{
		if(!is_array($sql))
			die ('Invalid sql parameter');
			
		global $reportCaseSensitiveGroupFields;
			
		$this->_originalSql = $sql;
		$start = 0;
		
		$fields = array();
        ##foreach @TABLE.arrReportGroupFields as @g order nGroupOrder##
        ##foreach Fields as @f##
        ##if @g.strGroupField == @f.strName##
			$add = array();
			$add['name'] = "##@f.strName s##";
			##if IsNumberField(@f)##
			$add['type'] = 'numeric';
			##elseif IsCharField(@f)##
			$add['type'] = 'char';
			$add['case_sensitive'] = $reportCaseSensitiveGroupFields;
			##elseif IsDateField(@f)##
			$add['type'] = 'date';
			##else##
			$add['type'] = 'char';
			##endif##
			$add['interval'] = ##@g.nGroupInterval##;
			$add['viewformat'] = '##@f.strViewFormat s##';
			
			$add['rowsinsummary'] = 1;
			##if @f.bSum##
			$add['rowsinsummary'] ++;
			##endif##
			##if @f.bAvg##
			$add['rowsinsummary'] ++;
			##endif##
			##if @f.bMin##
			$add['rowsinsummary'] ++;
			##endif##
			##if @f.bMax##
			$add['rowsinsummary'] ++;
			##endif##
			
			##if @TABLE.nReportLayout==REPORT_STEPPED##
			$add['rowsinheader'] = 1;
			##elseif @TABLE.nReportLayout==REPORT_BLOCK##
			$add['rowsinheader'] = 0;
			##elseif @TABLE.nReportLayout==REPORT_OUTLINE || @TABLE.nReportLayout==REPORT_ALIGN##
				##if @last##
			$add['rowsinheader'] = 2;
				##else##
			$add['rowsinheader'] = 1;
				##endif##
			##elseif @TABLE.nReportLayout==REPORT_TABULAR##
			$add['rowsinheader'] = 0;
			##endif##
			
			$fields []= $add;
		##endif##
        ##endfor##
        ##endfor##
			
		$this->_hasGroups = count($fields) > 0;

		foreach($fields as $field)
		{
			$f = create_reportfield($field['name'], $field['type'], $field['interval'], 'grp');
			$start = $f->setStart($start);
			if(isset($field['case_sensitive']))
				$f->setCaseSensitive($field['case_sensitive']);
			if(isset($field['rowsinsummary']))
				$f->_rowsInSummary = $field['rowsinsummary'];
			if(isset($field['rowsinheader']))
				$f->_rowsInHeader = $field['rowsinheader'];
			$f->_viewFormat = $field['viewformat'];
			$this->_fields []= $f;
		}
		
		// order
		if($order)
		{
			$order_in = array();
			$order_out = array();
			$order_old = array();
			
			foreach($order as $o)
			{
				$order_in []= $o[2] . ' as ' . cached_ffn('originalorder'.$o[0]);
				$order_out []= cached_ffn('originalorder'.$o[0]).' '.$o[1];
				$order_old []= $o[2].' '.$o[1];
				
	        ##foreach @TABLE.arrReportGroupFields as @g order nGroupOrder##
	        ##foreach Fields as @f##
	        ##if @g.strGroupField == @f.strName##
				if(##@index## == $o[0])
				{
					$n = ##@g.nGroupOrder##-1;
					$this->_fields[$n]->_orderBy = $o[1];
				}
			##endif##
	        ##endfor##
	        ##endfor##
			}
			$this->_order_in = join(', ', $order_in);
			$this->_order_out = join(', ', $order_out);
			$this->_order_old = join(', ', $order_old);
		}
		
        ##foreach Fields as @f##
            ##if @f.bMax##
            $this->_aggregates []= 'MAX('.cached_ffn("##@f.strName s##").') as ##concat @f.strName "MAX" w##';
            ##endif##
            ##if @f.bMin##
            $this->_aggregates []= 'MIN('.cached_ffn("##@f.strName s##").') as ##concat @f.strName "MIN" w##';
            ##endif##
            ##if @f.bAvg##
              ##if !IsDateField(@f)##
            $this->_aggregates []= 'AVG('.cached_ffn("##@f.strName s##").') as ##concat @f.strName "AVG" w##';
            $this->_aggregates []= 'COUNT('.cached_ffn("##@f.strName s##").') as ##concat @f.strName "NAVG" w##';
			  ##endif##
            ##endif##
            ##if @f.bSum##
              ##if !IsDateField(@f)##
            $this->_aggregates []= 'SUM('.cached_ffn("##@f.strName s##").') as ##concat @f.strName "SUM" w##';
			  ##endif##
            ##endif##
        ##endfor##
        
		$this->_reportPageSummary = ##@TABLE.bReportPageSummary##;
		$this->_reportGlobalSummary = ##@TABLE.bReportGlobalSummary##;
		$this->_reportSummary = $this->_reportPageSummary || $this->_reportGlobalSummary;
		$this->_pagesize = $pagesize;
		$this->_details = ##@TABLE.bReportShowDetails##;
		
		$this->_where = new WhereClause();
	}
	
	function getOriginal($useOriginalOrder = true)
	{
		$sql = $this->applyWhere($this->_originalSql);
		return $sql[0].' '.($useOriginalOrder && $this->_order_in && !$this->_oldAlgorithm ? ', ' . $this->_order_in . ' ' : '').$sql[1].' '.
			($sql[2] ? ' WHERE ' . $sql[2] : '').' '.$sql[3];
	}
	
	function setRecordBasedRequest($recordBasedRequest)
	{
		$this->_recordBasedRequest = $recordBasedRequest;
		for($nCnt = 0; $nCnt < count($this->_fields); $nCnt++)
			$this->_fields[$nCnt]->_recordBasedRequest = $recordBasedRequest;
	}
	
	function getGroup($data)
	{
		return $this->_fields[0]->getGroup($data);
	}
	
	function field($num)
	{
		return $this->_fields[$num];
	}
	
	function getSQLLimits($sql, $from)
	{
		if($from >= 0 && $this->_pagesize >= 0)
		{
			##if @BUILDER.nDatabaseType==nDATABASE_MySQL || @BUILDER.nDatabaseType==nDATABASE_PostgreSQL##
	        $out = $sql.' LIMIT '.intval($this->_pagesize).' OFFSET '.intval($from);
			$this->_skipCount = 0;
			##elseif @BUILDER.nDatabaseType==nDATABASE_MSSQLServer || @BUILDER.nDatabaseType==nDATABASE_Access##
			$nsel = stripos($sql, "select");
			$out = substr_replace($sql, 'select top '.($from + $this->_pagesize), $nsel, strlen("select"));
			$this->_skipCount = $from;
			##elseif @BUILDER.nDatabaseType==nDATABASE_Oracle##
			$out = 'select * from (select original2.*, rownum as '.cached_ffn('rownumber').' from ('.
				$sql.") original2) where ".cached_ffn('rownumber').' between '.$from.' and '.($from + $this->_pagesize);
			$this->_skipCount = 0;
			##endif##
			return $out;
		}
		else
		{
			return $sql;
		}
	}
	
	function sqlg($donotlimit = false, $doorder = true)
	{
		$hsql = array();
		
		$s = array();
		$g = array();
		$o = array();
		
		if($this->_hasGroups)
		{
			$s []= $this->_fields[0]->getSelectSql(true);
			$g []= $this->_fields[0]->getGroupSql();
			$o []= $this->_fields[0]->getOrderSql();				
		}
		
		if(count($s))
			$hsql['select'] []= join(', ', $s);
		if(count($g))
			$hsql['groupby'] []= join(', ', $g);
		if(count($o) && $doorder)
			$hsql['orderby'] = join(', ', $o);
			
		if($this->_limitLevel == 1 && !$donotlimit)
			$hsql['limits'] = 1;
			
		return $this->buildsql($hsql);
	}
	
	function sqlcg()
	{
		$gsql = $this->sqlg(true, false);
		return 'select count(*) as c from ('.$gsql.') countgroups';
	}
	
	function sqlt()
	{
		if($this->_reportSummary)
		{
			$hsql = array();
			$hsql['select'] []= 'count(1) as '.cached_ffn('countField');
			if(count($this->_aggregates))
				$hsql['select'] []= join(', ', $this->_aggregates);
			return $this->buildsql($hsql);
		}
		else
		{
			return false;
		}
	}
	
	function sql2($groups = null)
	{
		$hsql = array();
			
		if(!$this->_hasGroups || $this->_recordBasedRequest)
		{
			$hsql['original'] = true;
			
			$o = array();
			foreach($this->_fields as $f)
				$o []= $f->getOrderSql();
			if(count($o))
				$hsql['orderby'] = join(', ', $o);
		}
		else
		{
			if($this->_details)
			{
				$hsql['select'] []= 'original.*';
			}
			else
			{
				if(count($this->_aggregates))
					$hsql['select'] []= join(', ', $this->_aggregates);
			}
			
			$s = array();
			$g = array();
			$o = array();
			
			foreach($this->_fields as $f)
			{
				$s []= $f->getSelectSql(!$this->_details);
				if(!$this->_details)
					$g []= $f->getGroupSql();
				$o []= $f->getOrderSql();
			}
			
			if($this->_reportSummary && $this->_hasGroups && !$this->_details)
			{
				$hsql['select'] []= 'count(1) as '.cached_ffn('countField');
			}
			
			if(count($s))
			{
				$hsql['select'] []= join(', ', $s);
			}
			
			if($groups !== null && count($groups))
			{
				$where = $this->_fields[0]->getWhereSql($groups);
				if($where)
					$hsql['where'] = $where;
			}
			
			if(count($g))
			{
				$hsql['groupby'] = $g;
			}
			
			if(count($o))
			{
				$hsql['orderby'] = join(', ', $o);
			}
		}
		
		if($this->_limitLevel == 2)
		{
			$hsql['limits'] = 1;
		}
			
		if($this->_details)
		{
			$hsql['origorder'] = 1;
		}
		
		return $this->buildsql($hsql);
	}
	
	function buildsql($hsql)
	{
		$this->_skipCount = 0;
		$ordered = false;
		
		if(count($hsql) == 0 || $hsql['original'])
		{
			$sql = $this->getOriginal();
		}
		else
		{
			$sql = 'SELECT ';
			if($hsql['select'] && count($hsql['select']) > 0)
				$sql .= join(', ', $hsql['select']);
			else
				$sql .= ' * ';
			$sql .= ' FROM ('.$this->getOriginal($hsql['origorder']).') original';
			if($hsql['where'] && count($hsql['where']) > 0)
				$sql .= ' WHERE '.$hsql['where'];
			if($hsql['groupby'] && count($hsql['groupby']) > 0)
				$sql .= ' GROUP BY '.join(', ', $hsql['groupby']);
		}
		
		$osql = '';
		if($hsql['orderby'] && count($hsql['orderby']) > 0)
		{
			$osql .= $hsql['orderby'];
			$ordered = true;
		}
		if($hsql['origorder'])
		{
			if(!$this->_oldAlgorithm)
			{
				if($this->_order_out)
				{
					$osql .= ($osql ? ', ' : '').$this->_order_out;
				}
			}
			else
			{
				if($this->_order_old)
				{
					$osql .= ($osql ? ', ' : '').$this->_order_old;
				}
			}
		}
		if($osql)
		{
			$sql .= ' ORDER BY '.$osql;
		}
		
		if($hsql['limits'])
			$sql = $this->getSQLLimits($sql, $this->_from);
			
		return $sql;
	}
	
	function initWhere()
	{
		$this->_where->parseRequest();
	}
	
	function getWhere()
	{
		return $this->_where;
	}
	
	function setWhere($where)
	{
		$this->_where = $where;
	}
	
	function applyWhere(&$sql)
	{
		return $this->_where->applyWhere($sql);
	}
	
	function setOldAlgorithm($useOldAlgorithm = true)
	{
		for($nCnt = 0; $nCnt < count($this->_fields); $nCnt ++)
		{
			$this->_fields[$nCnt]->_oldAlgorithm = $useOldAlgorithm;
		}
		
		$this->_oldAlgorithm = $useOldAlgorithm;
	}
}

////////////////////////////////////////////////////////////////////////////////////////
class Summarable
{
	var $_summary = array();
	
    function Summarable()
    {
		Summarable::init();
	}
	
	function init($from = 0)
	{
		$this->_from = $from;
	}
	
	function writeGroup(&$begin, &$end, $gkey, $grp, $nField) {}
	
	function addSummary($recordsMode, &$summary, $data, &$nTotalRecords)
	{
		$countInGroup = isset($summary['count']) ? $summary['count'] : 0;
		##if Fields[bAvg || bSum || bMin || bMax].len##
		if(!is_array($summary['summary']))
			$summary['summary']=array();
		$s =& $summary['summary'];
		##endif##
		if($recordsMode)
		{
		    ##foreach Fields as @f filter @f.bMax || @f.bMin || @f.bAvg || @f.bSum##
			if($data['##@f.strName##'] !== null)
			{
				if(!is_array($s['##@f.strName##']))
					$s['##@f.strName##'] = array();
		        ##if @f.bMax##
		        if(!isset($s['##@f.strName##']['MAX']) || $s['##@f.strName##']['MAX'] < $data['##@f.strName##'])
					$s['##@f.strName##']['MAX'] = $data['##@f.strName##'];
		        ##endif##
		        ##if @f.bMin##
		        if(!isset($s['##@f.strName##']['MIN']) || $s['##@f.strName##']['MIN'] > $data['##@f.strName##'])
					$s['##@f.strName##']['MIN'] = $data['##@f.strName##'];
		        ##endif##
		        ##if @f.bAvg##
					##if @f.strViewFormat=="Time"##
				$avg_value = $this->value2time($data['##@f.strName##']);
					##else##
				$avg_value = $data['##@f.strName##'];
					##endif##
				$s['##@f.strName##']['AVG'] = $s['##@f.strName##']['AVG']*$s['##@f.strName##']['count'] + $avg_value;
		        $s['##@f.strName##']['count'] ++;
				$s['##@f.strName##']['AVG'] /= $s['##@f.strName##']['count'];
		        ##endif##
		        ##if @f.bSum##
					##if @f.strViewFormat=="Time"##
		        $s['##@f.strName##']['SUM'] += $this->value2time($data['##@f.strName##']);
					##else##
		        $s['##@f.strName##']['SUM'] += $data['##@f.strName##'];
					##endif##
		        ##endif##
			}
		    ##endfor##
		    $nTotalRecords ++;
			$countInGroup ++;
		}
		else
		{
	        ##foreach Fields as @f filter @f.bMax || @f.bMin || @f.bAvg || @f.bSum##
				if(!is_array($s['##@f.strName##']))
					$s['##@f.strName##'] = array();	
		        ##if @f.bMax##
		        if($data['##@f.strName##MAX'] !== null)
				{
					if(!isset($s['##@f.strName##']['MAX']) || $s['##@f.strName##']['MAX'] < $data['##@f.strName##MAX'])
						$s['##@f.strName##']['MAX'] = $data['##@f.strName##MAX'];
				}
		        ##endif##
		        ##if @f.bMin##
		        if($data['##@f.strName##MIN'] !== null)
				{
					if(!isset($s['##@f.strName##']['MIN']) || $s['##@f.strName##']['MIN'] > $data['##@f.strName##MIN'])
						$s['##@f.strName##']['MIN'] = $data['##@f.strName##MIN'];
				}
		        ##endif##
		        ##if @f.bAvg##
				if($data['##@f.strName##AVG'] !== null)
				{
					##if @f.strViewFormat=="Time"##
					$avg_value = $this->value2time($data['##@f.strName##AVG']);
					##else##
					$avg_value = $data['##@f.strName##AVG'];
					##endif##
					$s['##@f.strName##']['AVG'] = $s['##@f.strName##']['AVG']*$s['##@f.strName##']['count'] + $avg_value*$data['##@f.strName##NAVG'];
					$s['##@f.strName##']['count'] += $data['##@f.strName##NAVG'];
					$s['##@f.strName##']['AVG'] /= $s['##@f.strName##']['count'];
				}
   		        ##endif##
		        ##if @f.bSum##
		        if($data['##@f.strName##SUM'] !== null)
				{
					##if @f.strViewFormat=="Time"##
			        $s['##@f.strName##']['SUM'] += $this->value2time($data['##@f.strName##SUM']);
					##else##
			        $s['##@f.strName##']['SUM'] += $data['##@f.strName##SUM'];
			        ##endif##
				}
		        ##endif##
	        ##endfor##
	        $nTotalRecords += $data['countField'];
			$countInGroup += $data['countField'];
		}
        $summary['count'] = $countInGroup;
	}
	
	function _makeSummary(&$summary, $deep)
	{
		if($summary['values'])
		{
	        foreach($summary['values'] as $gkey => $group)
	        {
				$grp =& $summary['values'][$gkey];
	            if(isset($grp['values']))
	                $this->_makeSummary($grp, $deep + 1);
					
				if(isset($grp['_begin']) && isset($grp['_end']))
					$this->writeGroup($grp['_begin'], $grp['_end'], $gkey, $grp, $deep);
					
				if(!is_array($summary['summary']))
					$summary['summary']=array();
	            ##foreach Fields as @f##
					if(!is_array($summary['summary']['##@f.strName##']))
						$summary['summary']['##@f.strName##']=array();
	                if(is_array($grp['summary']))
					{
					if(is_array($grp['summary']['##@f.strName##']))
					{
	                ##if @f.bMax##
						if(isset($grp['summary']['##@f.strName##']['MAX']))
						{
							if(!isset($summary['summary']['##@f.strName##']['MAX']) || $summary['summary']['##@f.strName##']['MAX'] < $grp['summary']['##@f.strName##']['MAX'])
								$summary['summary']['##@f.strName##']['MAX'] = $grp['summary']['##@f.strName##']['MAX'];
						}
	                ##endif##
	                ##if @f.bMin##
	                	if(isset($grp['summary']['##@f.strName##']['MIN']))
						{
							if(!isset($summary['summary']['##@f.strName##']['MIN']) || $summary['summary']['##@f.strName##']['MIN'] > $grp['summary']['##@f.strName##']['MIN'])
								$summary['summary']['##@f.strName##']['MIN'] = $grp['summary']['##@f.strName##']['MIN'];
						}
	                ##endif##
	                ##if @f.bAvg##
	    	            if(isset($grp['summary']['##@f.strName##']['AVG']))
						{
		        	        $summary['summary']['##@f.strName##']['AVG'] =
								$summary['summary']['##@f.strName##']['AVG']*$summary['summary']['##@f.strName##']['count'] +
								$grp['summary']['##@f.strName##']['AVG']*$grp['summary']['##@f.strName##']['count'];
							$summary['summary']['##@f.strName##']['count'] += $grp['summary']['##@f.strName##']['count'];
							$summary['summary']['##@f.strName##']['AVG'] /= $summary['summary']['##@f.strName##']['count'];
						}
	                ##endif##
	                ##if @f.bSum##
	        	        if($grp['summary']['##@f.strName##']['SUM'])
							$summary['summary']['##@f.strName##']['SUM'] += $grp['summary']['##@f.strName##']['SUM'];
		            ##endif##
					}
					}
		        ##endfor##
		        
		        $summary['count'] += $grp['count'];
		    }
		}
	}
	
	function value2time($value)
	{
		$res = 0;
		$arr = parsenumbers($value);
		if(isset($arr[0]))
			$res += $arr[0] * 60 * 60;
		if(isset($arr[1]))
			$res += $arr[1] * 60;
		if(isset($arr[2]))
			$res += $arr[2];
		return $res;
	}
	
	function time2printable($time)
	{
		return array(intval($time / (60 * 60)), intval($time / 60), $time % 60);
	}
}

class ReportGroups extends Summarable
{
	var $_global;
	var $_pagesize;
	var $_totalRecords;
	var $_maxpages;
	var $_nGroup;
	var	$_oldFirst;
	var $_from;
	var $_sql;
	var $_connection;
	var $_allGroupsUsed;
	var $_countGroups;
	
	function ReportGroups(&$sql, $connection, $pagesize)
	{
		Summarable::Summarable();
		$this->init();
		$this->_pagesize = $pagesize;
		$this->_sql =& $sql;
		$this->_connection = $connection;
	}
	
	function init($from = 0)
	{
		Summarable::init($from);
		$this->_global = array();
		$this->_totalRecords = 0;
		$this->_maxpages = -1;
		$this->_from = $from;
		$this->_nGroup = -1;
		$this->_oldFirst = '';
		$this->_allGroupsUsed = false;
		$this->_countGroups = 0;
	}
	
	function setGlobalSummary($recordsMode, $data)
	{
		$this->addSummary($recordsMode, $this->_global, $data, $this->_totalRecords);
	}
	
	function setGroup($data)
	{
		$field = $this->_sql->field(0);
		$firstKey = $field->getKey($data);
		if($firstKey != $this->_oldFirst)
		{
			$this->_nGroup ++;
			$this->_oldFirst = $firstKey;
		}
	}
	
	function isVisibleGroup()
	{
		return $this->_nGroup >= $this->_from && $this->_nGroup < $this->_from + $this->_pagesize;
	}

	function getDisplayGroups($from)
	{
		$this->init($from);
		
		if($this->_pagesize == -1)
		{
			// request 'All Groups'
			return array();
		}
		else
		{
			$groups = array();
			$this->_allGroupsUsed = false;
		
			##if @TABLE.arrReportGroupFields.len##
			$sql = $this->_sql->sqlg();
	        $cursor = db_query($sql, $this->_connection);
	        while($data = db_fetch_array($cursor))
			{
				$groups[] = $this->_sql->getGroup($data);
			}
			if(count($groups) < $this->_pagesize)
				$this->_allGroupsUsed = true;
			##endif##
			
			if($this->_sql->_skipCount > 0)
			{
				array_splice($groups, 0, $this->_sql->_skipCount);
				$this->_allGroupsUsed = false;
			}
		
			$this->_countGroups = count($groups);
	
			return $groups;
		}
	}
	
	function getCountGroups($fullRequest = false)
	{
		##if @TABLE.arrReportGroupFields.len##
		if($this->_nGroup >= 0 && !$fullRequest)
		{
			return $this->_nGroup;
		}
		else
		{
			if($this->_allGroupsUsed)
			{
				return $this->_countGroups;
			}
			else
			{
				$sql = $this->_sql->sqlcg();
		        $cursor = db_query($sql, $this->_connection);
		        $data = db_fetch_array($cursor);
				return $data['c'];
			}
		}
		##else##
		return 0;
		##endif##
	}
	
	function getSummary()
	{
		return $this->_global;
	}
	
	function allGroupsUsed()
	{
		return $this->_allGroupsUsed;
	}
}

class ReportLogic extends Summarable
{
	var $_list;
	var $_totalRecords;
	var $_pages;
	var $_pagesize;
	var $_printpagesize;
	var $_from = 0;
    var $_connection;
	var	$_sql;
	var $_groups;
	var $_groupKeys;
	
	var $_fullRequest = false;
	var $_recordBasedRequest = false;
	var $_doPaging = false;
	var $_lastPageNumber = 0;
	var $_pageSummary;
	var $_printRecordCount = 0;
	var $_listedRows = 0;
	var $_oldLevels;
	
    function ReportLogic($sql, $order, $dosearch, $connection, $pagesize, $printpagesize)
    {
		Summarable::Summarable();
        $this->_connection = $connection;
		$this->_sql = new SQLStatement($sql, $order, $pagesize, $connection);
		$this->_groups = new ReportGroups($this->_sql, $connection, $pagesize);
		$this->_pagesize = $pagesize;
		$this->_printpagesize = $printpagesize < 0 ? $pagesize : $printpagesize;
		if($dosearch)
			$this->_sql->initWhere();
		$this->init();
	}
	
	function init($from = 0)
	{
		Summarable::init($from);
		$this->_sql->_from = $from;
		$this->_list = array();
		$this->_totalRecords = 0;
		$this->_pages = array();
		$this->_groupKeys = array();
		$this->_lastPageNumber = 0;
		$this->_pageSummary = array();
		$this->_printRecordCount = 0;
		$this->_listedRows = 0;
		$this->_oldLevels = array();
	}
	
	//// accessors
	function getWhere()
	{
		return $this->_sql->getWhere();
	}
	
	function setWhere($where)
	{
		$this->_sql->setWhere($where);
	}
	
	function getPages()
	{
		return $this->_pages;
	}
	
	/// logic
	function getFormattedRow($value) {}
	function writeGroup(&$begin, &$end, $gkey, $grp, $nField) {}
	function _writePage(&$page, $src, $count) {}
	function writeGlobalSummary($source) {}

	function writePageSummary()
	{
		if($this->_doPaging)
		{
			for($nCnt = 0; $nCnt < count($this->_list); $nCnt++)
			{
				if(!isset($this->_pages[$nCnt]))
					$this->_pages[$nCnt]=array();
				$result =& $this->_pages[$nCnt];
				if(isset($this->_pageSummary[$nCnt]))
				{
					$page = $this->_pageSummary[$nCnt];
					$this->_writePage($result,
							  isset($page['summary']) ? $page['summary'] : array(),
							  isset($page['count']) ? $page['count'] : 0);
				}
				else
					$this->_writePage($result, array(), 0);
			}
		}
		else
		{
			$result = array();
			$page = $this->_summary;
			$this->_writePage($result,
							  isset($page['summary']) ? $page['summary'] : array(),
							  isset($page['count']) ? $page['count'] : 0);
			$this->_summary = $result;
		}
		
		if(0 == count($this->_pages) && count($this->_list) > 0)
			$this->_pages []= $this->_summary;
	}
	
	function makeSummary()
	{
		$this->_makeSummary($this->_summary, 0);
	}
	
	function setSummary($recordsMode, $data, $rowToAppend = null)
	{
		$level =& $this->_summary;
		
		$setBegin = false;
		##if @TABLE.arrReportGroupFields.len##
		// get array of keys for current record
		$recordkeys = array();
		##foreach @TABLE.arrReportGroupFields as @g order @g.nGroupOrder##
		$field = $this->_sql->field(##@g.nGroupOrder## - 1);
		$recordkeys[##@g.nGroupOrder## - 1] = $field->getKey($data);
		##endfor##
			
		// add empty record for each closed group to hold its summary
		if(count($this->_groupKeys) > 0)
		{
			$changed = false;
			$nKey = 0;
			for(; $nKey < count($recordkeys); $nKey ++)
			{
				if($recordkeys[$nKey] != $this->_groupKeys[$nKey])
				{
					$changed = true;
					break;
				}
			}
			if($changed)
			{
				// in reverse order
				for($nKey2 = count($recordkeys) - 1; $nKey2 >= $nKey; $nKey2 --)
				{
					$emptyRow =& $this->appendRow(array());
					$field = $this->_sql->field($nKey2);
					$this->_printRecordCount += $field->_rowsInSummary;
					$this->_listedRows ++;
					$this->_oldLevels[$nKey2]['_end'] =& $emptyRow;
				}
			}
		}
			
		// store new record keys
		$this->_groupKeys = $recordkeys;
			
		// find current summary
		$levels = array();
		##foreach @TABLE.arrReportGroupFields as @g order @g.nGroupOrder##
		if(!isset($level['values'][$recordkeys[##@g.nGroupOrder## - 1]]))
		{
			$level['values'][$recordkeys[##@g.nGroupOrder## - 1]] = array();
			$level =& $level['values'][$recordkeys[##@g.nGroupOrder## - 1]];
			$field = $this->_sql->field(##@g.nGroupOrder## - 1);
			$this->_printRecordCount +=  $field->_rowsInHeader;
			$setBegin = true;
			$level['_first'] = $data;
		}
		else
		{
			$level =& $level['values'][$recordkeys[##@g.nGroupOrder## - 1]];
		}
		$levels[] =& $level;
		##endfor##
		
		// and finally calculate summary
		$this->addSummary($recordsMode, $level, $data, $this->_totalRecords);
		$this->_oldLevels =& $levels;
		##else##
		$this->addSummary($recordsMode, $level, $data, $this->_totalRecords);
		##endif##
		
		if($rowToAppend)
		{
			$added =& $this->appendRow($rowToAppend);
			$this->_printRecordCount ++;
			$this->_listedRows ++;
			##if @TABLE.arrReportGroupFields.len##
			if($setBegin)
			{
				for($nCnt = 0; $nCnt < count($levels); $nCnt ++)
					if(!isset($levels[$nCnt]['_begin']))
						$levels[$nCnt]['_begin'] =& $added;
			}
			##endif##
		}
		
		##if @TABLE.bReportPageSummary##
		if($this->_doPaging && $rowToAppend)
		{
			$nPage = count($this->_list) - 1;
			if(!isset($this->_pageSummary[$nPage]))
				$this->_pageSummary[$nPage]["count"]=0;
			$this->addSummary($recordsMode, $this->_pageSummary[$nPage], $data, $this->_pageSummary[$nPage]['count']);
		}
		##endif##
	}
	
	function setFinish()
	{
		if(count($this->_groupKeys) > 0)
		{
			for($nKey = count($this->_groupKeys) - 1; $nKey >= 0; $nKey --)
			{
				$field = $this->_sql->field($nKey);
				$this->_printRecordCount += $field->_rowsInSummary;
				$emptyRow =& $this->appendRow(array());
				$this->_listedRows ++;
				$this->_oldLevels[$nKey]['_end'] =& $emptyRow;
			}
		}
	}
	
	function & appendRow($row)
	{
		if($this->_doPaging)
		{
			$page = intval($this->_printRecordCount / $this->_printpagesize);
			// check we have no gaps in page list
			if($page > 0 && !isset($this->_list[$page - 1]))
				die("Please, increase numer of records per print page to display report properly");
			$this->_list[$page][] = $row;
			return $this->_list[$page][count($this->_list[$page]) - 1];
		}
		else
		{
			$this->_list[] = $row;
			return $this->_list[count($this->_list) - 1];
		}
	}
	
	function recordVisible($nRecord)
	{
		return
			$this->_doPaging ||
			$this->_sql->_limitLevel == 1 ||
			$this->_pagesize == -1 ||
			($this->_sql->_limitLevel == 2 &&
				($nRecord - $this->_sql->_skipCount >= 0 && $nRecord - $this->_sql->_skipCount < $this->_pagesize));
	}
	
	function getTotals()
	{
		if($this->_fullRequest)
		{
			return $this->_groups->getSummary();
		}
		else
		{
			if($this->_groups->allGroupsUsed())
			{
				return $this->_summary;
			}
			else
			{
				$totals = array();
				$sql = $this->_sql->sqlt();
				if($sql !== false)
				{
					$totalRecords = 0;
			        $cursor = db_query($sql, $this->_connection);
					$data = db_fetch_array($cursor);
					$this->addSummary(false, $totals, $data, $totalRecords);	
				}
				return $totals;
			}
		}
	}
	
	function getReport($from = 0)
	{
		global $bSubqueriesSupported;
		
		$this->init($from);
		
		// include all records in report with pagination
		$this->_doPaging = $from == -1;
		
		// calculate global summary
		$globalSummary = ##@TABLE.bReportGlobalSummary##;
		
		// retrieve ALL records from table
		$this->_fullRequest = $this->_doPaging || ($globalSummary && ##Fields[strViewFormat=="Time"].len##);
		
		// MYSQL version < 5.0
		// a very bad thing to do, but we need this for global summary and pagination
		if(!$bSubqueriesSupported)
			$this->_fullRequest = true;
		
		// use non-optimized algorithm
		$this->_recordBasedRequest = $this->_fullRequest; 
		
		// request records if there is no grouping
		if(!##@TABLE.arrReportGroupFields.len##)
			$this->_recordBasedRequest=true;
		
		//////////////////////////////// start building report
		$this->_sql->setRecordBasedRequest($this->_recordBasedRequest);
		if($this->_doPaging)
		{
			$this->_sql->_limitLevel = 0; // no limits
		}
		else
		{
			if(!##@TABLE.arrReportGroupFields.len##)
				$this->_sql->_limitLevel = 2; // limit records
			else
				$this->_sql->_limitLevel = 1; // limit groups
		}
		
		$page = -1;
		$nRow = 0;
		
		if(!$this->_recordBasedRequest)
		{
			// get groups to show
			$groups = $this->_groups->getDisplayGroups($from);
			
			// iterate through records in these groups
			$sql = $this->_sql->sql2($groups);
		    $cursor = db_query($sql, $this->_connection);
		    while($data = db_fetch_array($cursor))
		    {
				$this->setSummary($this->_sql->_details, $data,
					$this->recordVisible($nRow) ? $this->getFormattedRow($data) : null);
				$nRow ++;
		    }
		}
		else
		{
			$this->_groups->init($from);
			$this->_sql->setOldAlgorithm();
			
			$sql = $this->_sql->sql2(null);
	        $cursor = db_query($sql, $this->_connection);
	        while($data = db_fetch_array($cursor))
			{
				##if @TABLE.arrReportGroupFields.len##
				// take a record group into account
				$this->_groups->setGroup($data);
				##endif##
				
				if($this->_fullRequest)
					$this->_groups->setGlobalSummary(true, $data);
				
				##if @TABLE.arrReportGroupFields.len##
				$visible = $this->_doPaging || $this->_groups->isVisibleGroup();
				##else##
				$visible = $this->_doPaging || $this->recordVisible($nRow);
				##endif##
				
				if($visible)
				{
					$this->setSummary(true, $data, $this->getFormattedRow($data));
					$nRow ++;
				}
				else if(!$this->_fullRequest && count($this->_list) > 0)
				{
					// exit loop at the end of visible recordset
					break;
				}
			}
			
			$this->_sql->setOldAlgorithm(false);
		}
		$this->setFinish();
		
		$this->makeSummary();
		$global_totals = $this->getTotals();
		
		$this->writePageSummary();
		$globals = $this->writeGlobalSummary($global_totals);
		##if @TABLE.arrReportGroupFields.len##
		$countrows = $this->_groups->getCountGroups($this->_fullRequest);
		##else##
		$countrows = $global_totals['count'];
		##endif##

		$maxpages=1;
		if($this->_pagesize>0)
			$maxpages=ceil($countrows/$this->_pagesize);
		return array('list' => $this->_list, 'global' => $globals, 'page' => $this->_summary,
					'maxpages' => $maxpages);
	}
}

//////////////////////////////////////////////////////////////////////////////
class Report extends ReportLogic
{
	var $forExport = false;
	
    function Report($sql, $order, $dosearch, $connection, $pagesize, $printpagesize)
    {
		ReportLogic::ReportLogic($sql, $order, $dosearch, $connection, $pagesize, $printpagesize);
	}
	
	function getFormattedRow($value)
	{
		$row =  array('row_data' => true);
			
		$keylink = "";
		##foreach @TABLE.arrKeyFields as @k##
		$keylink.="&key##@index##=".htmlspecialchars(rawurlencode(@$value["##@k##"]));
		##endfor##
      
		##foreach Fields as @f filter @f.bReportPage && (@TABLE.bReportShowDetails || @TABLE.arrReportGroupFields[strGroupField==@f.strName && nGroupInterval==0].len) order nReportPageOrder##
		##if @f.strViewFormat==FORMAT_DATABASE_IMAGE##
		if(!$this->forExport)
		{
			##if @f.bShowThumbnail##
				$val.="<a target=_blank href=\"##@TABLE.strShortTableName s##_imager.php?field=##@f.strName uhs##".$keylink."\">";
				$val.= "<img border=0";
				$val.=" src=\"##@TABLE.strShortTableName s##_imager.php?field=##@f.strThumbnail uhs##&alt=##@f.strName uhs##".$keylink."\">";
				$val.= "</a>";
			##else##
				$val = "<img";
				##if @f.ListFormatObj.nImageWidth##
				$val.=" width=##@f.ListFormatObj.nImageWidth##";
				##endif##
				##if @f.ListFormatObj.nImageHeight##
				$val.=" height=##@f.ListFormatObj.nImageHeight##";
				##endif##
				$val.=" border=0";
				$val.=" src=\"##@TABLE.strShortTableName s##_imager.php?field=##@f.strName uhs##".$keylink."\">";
			##endif##
		}
		else
		{
			$val = ##message LONG_BINARY##;
		}
		##elseif @f.strViewFormat==FORMAT_FILE_IMAGE##
		if(!$this->forExport)
		{
			if(CheckImageExtension($value["##@f.strName##"])) 
			{
				##if @f.bShowThumbnail##
				 	// show thumbnail
					$thumbname = "##@f.strThumbnail s##".$value["##@f.strName##"];
					if(substr("##@f.strhlPrefix s##",0,7)!="http://" && !myfile_exists(GetUploadFolder("##@f.strName s##").$thumbname))
						$thumbname = $value["##@f.strName##"];
					$val = "<a target=_blank href=\"".htmlspecialchars(AddLinkPrefix("##@f.strName s##", $value["##@f.strName##"]))."\">";
					$val .= "<img";
					if($thumbname == $value["##@f.strName##"])
					{
					##if @f.ListFormatObj.nImageWidth##
						$val .= " width=##@f.ListFormatObj.nImageWidth##";
					##endif##
					##if @f.ListFormatObj.nImageHeight##
						$val .= " height=##@f.ListFormatObj.nImageHeight##";
					##endif##
					}
					$val .= " border=0";
					$val .= " src=\"".htmlspecialchars(AddLinkPrefix("##@f.strName s##", $thumbname))."\"></a>";
				##else##
					$val = "<img";
					##if @f.ListFormatObj.nImageWidth##
					$val .= " width=##@f.ListFormatObj.nImageWidth##";
					##endif##
					##if @f.ListFormatObj.nImageHeight##
					$val .= " height=##@f.ListFormatObj.nImageHeight##";
					##endif##
					$val .= " border=0";
					$val .= " src=\"".htmlspecialchars(AddLinkPrefix("##@f.strName s##",$value["##@f.strName##"]))."\">";
				##endif##
			}
		}
		else
		{
			$val = ##message LONG_BINARY##;
		}
		##elseif @f.strViewFormat==FORMAT_DATABASE_FILE##
		if(!$this->forExport)
		{
			##if @f.ListFormatObj.strFilename##
			$filename = $value["##@f.ListFormatObj.strFilename##"];
			if(!$filename)
				$filename = "file.bin";
			##else##
			$filename = "file.bin";
			##endif##
			if(strlen($value["##@f.strName##"]))
			{
				$val = "<a href=\"##@TABLE.strShortTableName##_getfile.php?filename=".rawurlencode($filename)."&field=##@f.strName uhs##".$keylink."\">";
				$val .= htmlspecialchars($filename);
				$val .= "</a>";
			}
		}
		else
		{
			$val = ##message LONG_BINARY##;
		}
		##elseif (@f.strEditFormat==EDIT_FORMAT_LOOKUP_WIZARD || @f.strEditFormat==EDIT_FORMAT_RADIO) && pLookupObj.nLookupType == LT_LOOKUPTABLE##
		$val = DisplayLookupWizard("##@f.strName s##", $value["##@f.strName##"], $value, $keylink, MODE_LIST);
		##elseif NeedEncode(@f)##
		$val = ProcessLargeText(GetData($value, "##@f.strName##", "##@f.strViewFormat##"), "field=##@f.strName uhs##".$keylink, "", MODE_LIST);
		##else##
		$val = GetData($value, "##@f.strName##", "##@f.strViewFormat##");
		##endif##
		
		$row["##@f.strName g##_value"] = $val;
		##endfor##
			
		##if @TABLE.nReportLayout==REPORT_BLOCK##
		$row[GoodFieldName('nonewgroup')] = true;
		##endif##
			
		return $row;
	}
	
	function writeGroup(&$begin, &$end, $gkey, $grp, $nField)
	{
		$field = $this->_sql->field($nField);
        $gname = $field->name();
		
		##foreach @TABLE.arrReportGroupFields as @g##
		if($gname == '##@g.strGroupField##')
		{
			##if @TABLE.nReportLayout==REPORT_BLOCK##
			$bFound = false;
			for($nG = 0; $nG < ##@TABLE.arrReportGroupFields.len##; $nG ++)
			{
				$field = $this->_sql->field($nG);
				$gname2 = $field->name();
				if($nG < $nField)
				{
					if(isset($begin[GoodFieldName($gname2.'_firstnewgroup')]))
						$bFound = true;
				}
				else
					unset($begin[GoodFieldName($gname2.'_firstnewgroup')]);
			}
			if(!$bFound)
				$begin[GoodFieldName($gname.'_firstnewgroup')] = true;
			unset($begin[GoodFieldName('nonewgroup')]);
			##else##
			$begin[GoodFieldName($gname.'_newgroup')] = true;
			##endif##
			$end[GoodFieldName($gname.'_endgroup')] = true;
			
			##if @g.bShowGroupSummary##
			$end[GoodFieldName('group'.$gname.'_total_cnt')] = $grp['count'];
			##endif##
			
			##foreach Fields as @f##
				if(is_array($grp['summary']))
				{
				if(is_array($grp['summary']['##@f.strName##']))
				{
				##if @f.bMax##
					$end["group##@g.strGroupField g##_total##@f.strName g##_max"] =
						getFormattedValue($grp['summary']['##@f.strName##']['MAX'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
				##endif##
				##if @f.bMin##
					$end["group##@g.strGroupField g##_total##@f.strName g##_min"] =
						getFormattedValue($grp['summary']['##@f.strName##']['MIN'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
				##endif##
				##if @f.bAvg##
					$end["group##@g.strGroupField g##_total##@f.strName g##_avg"] =
						getFormattedValue($grp['summary']['##@f.strName##']['AVG'], '##@f.strName##', '##@f.strViewFormat##');
				##endif##
				##if @f.bSum##
					$end["group##@g.strGroupField g##_total##@f.strName g##_sum"] =
						getFormattedValue($grp['summary']['##@f.strName##']['SUM'], '##@f.strName##', '##@f.strViewFormat##');
				##endif##
				}
				}
				##if @f.strName == @g.strGroupField##
				$field = $this->_sql->field($nField);
				$gvalue = $field->getFieldName($gkey, $grp['_first']);
			    if($field->overrideFormat())
				{
					$begin[GoodFieldName($gname.'_grval')] = $gvalue;
					##if @TABLE.arrReportGroupFields[bShowGroupSummary].len##
					$end[GoodFieldName($gname.'_grval')] = $gvalue;
					##endif##
				}
				else
				{
					$formattedValue = getFormattedValue($gvalue, '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
					$begin[GoodFieldName($gname.'_grval')] = $formattedValue;
					##if @TABLE.arrReportGroupFields[bShowGroupSummary].len##
					$end[GoodFieldName($gname.'_grval')] = $formattedValue;
					##endif##
				}
				##endif##
			##endfor##                        
		}
		##endfor##
	}
	
	function _writePage(&$page, $src, $count)
	{
		$page['page_summary'] = true;
		##if @TABLE.bReportPageSummary##
        ##foreach Fields as @f##
		if(is_array($src['##@f.strName##']))
		{
        ##if @f.bSum##
			$page['page_total##@f.strName g##_sum'] = getFormattedValue($src['##@f.strName##']['SUM'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
        ##if @f.bAvg##
			$page['page_total##@f.strName g##_avg'] = getFormattedValue($src['##@f.strName##']['AVG'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
        ##if @f.bMin##
			$page['page_total##@f.strName g##_min'] = getFormattedValue($src['##@f.strName##']['MIN'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
        ##if @f.bMax##
			$page['page_total##@f.strName g##_max'] = getFormattedValue($src['##@f.strName##']['MAX'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
		}
		##endfor##
		$page['page_total_cnt'] = $count;
		##endif##
	}
	
	function writeGlobalSummary($source)
	{
		$result = array();
			
		##if @TABLE.bReportGlobalSummary##
		if(is_array($source["summary"]))
		{
		##foreach Fields as @f##
		if(is_array($source["summary"]['##@f.strName##']))
		{
	    ##if @f.bMax##
		    $result["global_total##@f.strName g##_max"] = getFormattedValue($source['summary']['##@f.strName##']['MAX'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
		##if @f.bMin##
			$result["global_total##@f.strName g##_min"] = getFormattedValue($source['summary']['##@f.strName##']['MIN'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
		##if @f.bAvg##
			$result["global_total##@f.strName g##_avg"] = getFormattedValue($source['summary']['##@f.strName##']['AVG'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
		##if @f.bSum##
			$result["global_total##@f.strName g##_sum"] = getFormattedValue($source['summary']['##@f.strName##']['SUM'], '##@f.strName##', '##@f.strViewFormat##', '##@f.strEditFormat##');
		##endif##
		}
		##endfor##
		}
		$result["global_total_cnt"] = $source['count'];
		##endif##
		
		return $result;
	}
}	
?>
