Projects

WebGL Demos
PHP Data Pivot
PHP Data Subtotals
HTML5 Graph
Java NW3D2
JS Code Formatter
HTML5 Clock
Silverlight Gauge
Java NW3D
Java Fireworks
Java Early 3D
Java Snow
Java Dogfight
Java Water Simulation
Java Bump Mapping
Java Elite Ships

Data Subtotals

Description

This is a code snippet that I wrote years ago and have used variations of in my day job many, many times. It provides a view of a dataset with dynamic subtotalling. An array of field names/titles is used to drive both the form selection boxes and necessary grouping logic. In this example, a static object property is used to control the number of grouping levels.

The code is based around a MVC (Model-View-Controller) pattern. A summary of the logic flow:-

In the resulting table output, each subtotal can be expanded/collapsed using an image-based (plus/minus) toggle, processed using client-side Javascript.

Demonstration - Customer Sales

Use the drop-down selection boxes to choose grouping fields. The page will auto-reload each time you make a change.

Subtotal level 1: 2: 3:
ItemCustomerCityCountrySalesmanValue
ABC123 Cursus Institute Sierra Gorda Peru Brooke Sanchez 4.26
ABC100 Magna Lorem Incorporated Penrith UK Elton Schneider 19.15
ABC200 Purus Gravida Limited Cabano Equador Calvin Melton 35.88
AAA001 Nulla Associates Penrith UK Dolan Wolf 96.18
AAA010 Magna Lorem Incorporated Penrith UK Elton Schneider 46.73
ABC025 Diam Company Moose Jaw USA Calvin Melton 90.59
AAB500 Purus Gravida Limited Cabano Equador Elton Schneider 53.14
AAA005 Turpis Industries Guben Germany Elton Schneider 76.21
ABC025 Diam Company Moose Jaw USA Leroy Irwin 57.72
AAB001 Turpis Industries Guben Germany Calvin Melton 55.31
ABC050 Diam Company Moose Jaw USA Dolan Wolf 60.56
AAB001 Nulla Associates Penrith UK Brooke Sanchez 75.03
ABC050 Nulla Associates Penrith UK Elton Schneider 82.86
ABC123 Diam Company Moose Jaw USA Brooke Sanchez 59.17
ABC123 Diam Company Moose Jaw USA Leroy Irwin 4.87
ABC200 Cursus Institute Sierra Gorda Peru Leandra Lynn 48.32
ABC100 Magna Lorem Incorporated Penrith UK Dolan Wolf 10.23
ABB010 Cursus Institute Sierra Gorda Peru Calvin Melton 56.40
AAA100 Nulla Associates Penrith UK Leroy Irwin 52.68
ABB250 Turpis Industries Guben Germany Calvin Melton 38.58
ABC050 Blandit Company Pike Creek USA Calvin Melton 28.17
AAA005 Turpis Industries Guben Germany Elton Schneider 69.51
AAB001 Diam Company Moose Jaw USA Dolan Wolf 58.31
AAB001 Magna Lorem Incorporated Penrith UK Elton Schneider 39.02
ABC123 Turpis Industries Guben Germany Brooke Sanchez 93.20
ABC025 Diam Company Moose Jaw USA Leandra Lynn 53.12
ABC025 Diam Company Moose Jaw USA Leandra Lynn 21.66
ABC100 Blandit Company Pike Creek USA Calvin Melton 55.41
AAA100 Magna Lorem Incorporated Penrith UK Leroy Irwin 90.85
AAB500 Magna Lorem Incorporated Penrith UK Dolan Wolf 54.85
ABC250 Diam Company Moose Jaw USA Dolan Wolf 51.14
AAB500 Blandit Company Pike Creek USA Brooke Sanchez 68.94
ABB010 Purus Gravida Limited Cabano Equador Leroy Irwin 95.58
ABB010 Blandit Company Pike Creek USA Leroy Irwin 79.22
AAB001 Purus Gravida Limited Cabano Equador Leroy Irwin 29.49
AAB001 Purus Gravida Limited Cabano Equador Leandra Lynn 98.17
AAB001 Turpis Industries Guben Germany Elton Schneider 36.64
ABC200 Magna Lorem Incorporated Penrith UK Dolan Wolf 50.49
AAA001 Nulla Associates Penrith UK Leandra Lynn 71.67
ABC025 Blandit Company Pike Creek USA Leroy Irwin 62.79
AAA100 Turpis Industries Guben Germany Leandra Lynn 56.94
ABC250 Blandit Company Pike Creek USA Dolan Wolf 72.56
AAB001 Diam Company Moose Jaw USA Leroy Irwin 91.76
ABC123 Cursus Institute Sierra Gorda Peru Brooke Sanchez 43.61
ABC250 Turpis Industries Guben Germany Elton Schneider 79.93
ABC001 Purus Gravida Limited Cabano Equador Calvin Melton 94.84
ABC001 Diam Company Moose Jaw USA Calvin Melton 62.79
AAB001 Nulla Associates Penrith UK Calvin Melton 47.36
ABB250 Nulla Associates Penrith UK Calvin Melton 69.46
ABB250 Magna Lorem Incorporated Penrith UK Dolan Wolf 73.10
ABC001 Nulla Associates Penrith UK Dolan Wolf 78.65
ABC050 Cursus Institute Sierra Gorda Peru Leandra Lynn 68.60
AAA010 Nulla Associates Penrith UK Dolan Wolf 91.67
ABB250 Blandit Company Pike Creek USA Leandra Lynn 31.38
ABC001 Blandit Company Pike Creek USA Elton Schneider 19.95
ABB010 Magna Lorem Incorporated Penrith UK Dolan Wolf 52.56
ABC050 Cursus Institute Sierra Gorda Peru Calvin Melton 79.87
AAA010 Magna Lorem Incorporated Penrith UK Leroy Irwin 24.87
AAA100 Magna Lorem Incorporated Penrith UK Leroy Irwin 11.93
ABB010 Turpis Industries Guben Germany Calvin Melton 56.07
ABC001 Blandit Company Pike Creek USA Elton Schneider 94.41
AAB001 Magna Lorem Incorporated Penrith UK Calvin Melton 3.73
AAA001 Cursus Institute Sierra Gorda Peru Elton Schneider 90.27
AAA010 Turpis Industries Guben Germany Brooke Sanchez 12.38
AAA001 Magna Lorem Incorporated Penrith UK Dolan Wolf 87.53
ABC250 Turpis Industries Guben Germany Leroy Irwin 73.03
ABC250 Nulla Associates Penrith UK Brooke Sanchez 64.99
AAA001 Purus Gravida Limited Cabano Equador Dolan Wolf 90.08
AAA001 Turpis Industries Guben Germany Calvin Melton 64.28
ABC100 Diam Company Moose Jaw USA Dolan Wolf 59.58
ABC001 Purus Gravida Limited Cabano Equador Dolan Wolf 77.37
ABB010 Magna Lorem Incorporated Penrith UK Dolan Wolf 61.92
ABC025 Magna Lorem Incorporated Penrith UK Leandra Lynn 45.37
ABC100 Blandit Company Pike Creek USA Leandra Lynn 68.81
ABC050 Purus Gravida Limited Cabano Equador Dolan Wolf 67.78
ABC123 Magna Lorem Incorporated Penrith UK Brooke Sanchez 59.74
AAA001 Nulla Associates Penrith UK Leandra Lynn 30.04
ABB250 Cursus Institute Sierra Gorda Peru Brooke Sanchez 51.68
AAA001 Cursus Institute Sierra Gorda Peru Elton Schneider 35.54
ABC250 Nulla Associates Penrith UK Calvin Melton 42.32
ABC025 Magna Lorem Incorporated Penrith UK Leroy Irwin 57.94
ABB250 Diam Company Moose Jaw USA Leroy Irwin 64.53
ABC250 Turpis Industries Guben Germany Leandra Lynn 1.86
ABC100 Nulla Associates Penrith UK Brooke Sanchez 55.28
AAA100 Purus Gravida Limited Cabano Equador Elton Schneider 73.01
AAA100 Nulla Associates Penrith UK Leroy Irwin 33.94
ABC250 Turpis Industries Guben Germany Leroy Irwin 71.64
ABC100 Nulla Associates Penrith UK Dolan Wolf 94.90
ABC250 Purus Gravida Limited Cabano Equador Calvin Melton 84.36
ABC123 Cursus Institute Sierra Gorda Peru Brooke Sanchez 16.62
ABA005 Cursus Institute Sierra Gorda Peru Brooke Sanchez 99.39
ABC250 Purus Gravida Limited Cabano Equador Elton Schneider 71.03
ABC123 Cursus Institute Sierra Gorda Peru Leroy Irwin 30.12
AAA001 Magna Lorem Incorporated Penrith UK Leandra Lynn 63.30
ABC250 Diam Company Moose Jaw USA Elton Schneider 48.10
AAA100 Magna Lorem Incorporated Penrith UK Calvin Melton 66.99
ABB010 Magna Lorem Incorporated Penrith UK Leandra Lynn 37.35
ABB250 Turpis Industries Guben Germany Calvin Melton 82.59
ABB250 Purus Gravida Limited Cabano Equador Leandra Lynn 51.11
AAA005 Blandit Company Pike Creek USA Leandra Lynn 5.39
Final Totals5,730.30


PHP code

<?php

	//***CONTROLLER CODE
	
	//Read grouping parameters
	$i=1;
	$sel = array();
	while ($param = trim(strip_tags($_GET['group'.$i]))) {
		array_push($sel, $param);
		$i++;
	}
	
	$model = new Model;
	$view = new View;
	
	$view->view_output($model, $sel);



	//***MODEL

	class Model {
	
		function fetch_rows($groupings) {
			$db = "";
			$user = "";
			$pass = "";
			$host = 'mysql15.namesco.net';
			$charset = 'utf8';
			$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
			try {
				$pdo = new PDO($dsn, $user, $pass);
			} catch (PDOException $e) {
				echo 'Connection failed: ' . $e->getMessage();
				exit;
			}
			$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			//Build query string
			$sql = "SELECT Item, Customer, City, Country, Salesman, Value FROM sales ";
			if ($groupings) $sql .= " order by " . implode(", ", $groupings);
			
			//Execute and return result set
			$rs = $pdo->query($sql);
			$rows = $rs->fetchAll(PDO::FETCH_ASSOC);
			return $rows;
		}
	
	}

	//***VIEW
	
	class View {
		public $html;
		private $rowid;
		private $firstrec;
		private $subtot;
		private $cat;
		private $oldcat;
		private $group_titles;
		private $group_fields;
		const MAX_LEVELS = 3;
		
		function __construct() {
			$this->html = "";
			$this->rowid = 1;
			$this->group_titles = array('[None]', 'Item', 'Customer', 'City', 'Country', 'Salesman');
			$this->group_fields = array('', 'Item', 'Customer', 'City', 'Country', 'Salesman');
			$this->firstrec = true;
			$this->subtot = array();
			$this->cat = array();
			$this->oldcat = array();
		}
	
		public function view_output($model, $sel) {
			//Determine grouping fields
			$sel_fields = array();
			foreach ($sel as &$val) array_push($sel_fields, $this->group_fields[$val]);
			
			//Pull data from model
			$rows = $model->fetch_rows($sel_fields);
			
			$max_level = count($sel);
			$detail_level = $max_level + 1;
			//Initialise final total array element
			$this->subtot[0] = 0;
	
			$this->html.= "<form style='width: 750px;' name='mainform' method='get'>";
			//Create a set of numbered group selection boxes up to the number
			//specified in MAX_LEVELS.
			//Populate each box with every one of the possible grouping fields.
			$this->html.="Subtotal level ";
			for ($a=1; $a <= View::MAX_LEVELS; $a++) {
				//Begin selection element
				$this->html.= "$a:  <select size='1' name='group$a' 
					onchange='document.mainform.submit();'>";
				//Create an option entry for each grouping field
				for ($b=0; $b < count($this->group_titles); $b++) {
					$group_option = $this->group_titles[$b];
						$this->html.= "<option ";
							if ($max_level>=$a) {
								if ($sel[$a-1]==$b) $this->html.= "selected ";
							}
							$this->html.= "value='$b'>$group_option</option>";
				}
				//End selection element
				$this->html.= "</select>";
			}
			$this->html.= "</form>";
			$this->html.= "<table class='subtotals'>";
			$this->html.= "<thead><tr><th>Item</th><th>Customer</th><th>City</th><th>Country</th>
				<th>Salesman</th><th>Value</th></tr></thead>";

			foreach ($rows as $row) {
				for ($i=1; $i<=$max_level; $i++) {
					$this->cat[$i] = $row[$sel_fields[$i-1]];
					//First record, copy group field values to old group field values
					//in order to prevent summaries being triggered.
					if ($this->firstrec) {
						$this->subtot[$i] = 0;
						$this->oldcat[$i] = $this->cat[$i];
					}
				}
			
				//At each grouping level check for a change in value
				for ($i=1; $i<=$max_level; $i++) {
					//If a grouping field has changed value then generate subtotals for this level and any sub-levels
					if ($this->cat[$i] <> $this->oldcat[$i]) 
						$this->write_subtotal($i, $max_level);
				}

				//The subtotal array is used to sum the sales value for each group.
				$this->subtot[$max_level] += $row['Value'];
				//Process the detail records.
				//Check whether grouping is being used. If not then just write a plain table row.
				$this->html.= ($max_level) ? "<tr style='display: none;' 
					id='row$this->rowid' class='level$detail_level'>" : "<tr>";
				$this->html.= "<td>".$row['Item']."</td>";
				$this->html.= "<td>".$row['Customer']."</td>";
				$this->html.= "<td>".$row['City']."</td>";
				$this->html.= "<td>".$row['Country']."</td>";
				$this->html.= "<td>".$row['Salesman']."</td>";
				$this->html.= "<td class='r'>".number_format($row['Value'],2)."</td>";
				$this->html.= "</tr>";
				
				$this->rowid++;
				$this->firstrec=false;
			}
			//End of records, write final set of subtotals as needed
			$this->write_subtotal(0, $max_level);
			//Close table
			$this->html.="</table>";
			//Output view
			echo $this->html;
		}


		private function write_subtotal($level, $max_level = 0) {
			//If this level isn't the highest then recursively process all higher levels first
			if ($level < $max_level) $this->write_subtotal($level+1, $max_level);

			//By default all levels should be visible from the outset.
			$style = ($level>1) ? "style='display: none;' " : "";	//Only first level visible
			//Indentation based on level
			$indent = ($level-1) * 10;
			//Output the subtotal, storing the level in the class name
			//and the row number in the row id.
			//The plus/minus image has an 'onclick' event to toggle visibility.
			$this->html.= "<tr id='row$this->rowid' $style class='level$level'>";
			if ($level > 0) {
				$this->html.= "<th style='text-align: left; padding-left: 
					{$indent}px;' colspan='5'><a onclick='return 
					toggleVisibility($this->rowid);' href=''>
					<img id='img$this->rowid' src='/images/plus.png'></a>".
					$this->oldcat[$level]."</th><th class='r'>".
					number_format($this->subtot[$level],2)."</th>";
			} else {
				$this->html.= "<th style='text-align: left;' colspan='5'>Final Totals</th>
				<th class='r'>".number_format($this->subtot[$level],2)."</th>";
			}
			$this->html.= "</tr>";
			//Accumulate lower level totals if we are not already at the 
			//lowest level (i.e. final total)
			if ($level>0) {
				$this->subtot[$level-1] += $this->subtot[$level];
				$this->oldcat[$level] = $this->cat[$level]; //Change category value
			}
			//Incrment the row id, and reset the group record count.
			$this->rowid++;
			$this->subtot[$level]=0;
		} 
	}
?>


Javascript code

//Respond to user clicking on plus/minus symbols by showing/hiding rows as appropriate
function toggleVisibility(rowid) {
    var makevisible;
	//Check for the existence of the row and its related toggle image
	var togglerow = document.getElementById("row"+rowid);
	var imgsrc = document.getElementById("img"+rowid).src;
	if (togglerow) {
		//Using the source of the image clicked on, determine whether we are
		//showing or hiding rows. Also, toggle the image.
		if (/plus/.test(imgsrc)) {
			document.getElementById("img"+rowid).src=imgsrc.replace(/plus/, "minus");
			makevisible = true;
		} else {
			document.getElementById("img"+rowid).src=imgsrc.replace(/minus/, "plus");
			makevisible = false;
		}
		//Establish the grouping level of the row that the user clicked on
		//by stripping the word 'level' from the row class.
		var togglelevel=parseInt(togglerow.className.replace(/level/,""));
		//Walk backward through the table rows until we either reach the beginning of the table 
		//or a row with the same/lower grouping level.
		for (var i=rowid-1; i>0; i--) {
		     var row=document.getElementById("row"+i);
			 if (row) {
			     var level = parseInt(row.className.replace(/level/,""));
				//Early exit test - if level found to be same or less than toggling row 
				//then exit, no more child rows to toggle
				if (level<=togglelevel) break;
				 
				if (makevisible) {
					//We are making rows visible
					//Only make visible rows with the next highest grouping level
					if (level==togglelevel+1) {
						document.getElementById("row"+i).style.display="";
					}
				} else {
					//We are making rows invisible
					//Hide all rows with a higher grouping level 
					if (level>togglelevel) {
						document.getElementById("row"+i).style.display="none";
						//Check for toggle images that need to 
						//be reset to a 'plus' symbol
						if (document.getElementById("img"+i)) {
							document.getElementById("img"+i).src=imgsrc.replace
								(/minus/, "plus");
						}
					}
				}
			}
		}
	}
    return false;
}