UserSpice JSON Graph Data

// Flot graph with JSON data via AJAX for UserSpice 3.0.4b
//
// *** NOTE: This requires updating your root files with the footer modification in v3.1x
//
// If you have problems, update to the latest flot
//
// Feedback invited

Provides sample data (user/permissions count) for:
Flot pie-chart

// create a new file in the root of your website named json_chart.php
// set UserSpice permissions on this file as required (after installation)
// paste the following code:

<?php
/*
UserSpice 3.0.4b
by Dan Hoover at http://UserSpice.com

a modern version of
UserCake Version: 2.0.2

UserCake created by: Adam Davis
UserCake V2.0 designed by: Jonathan Cassels

*/
require_once("models/config.php");
if (!securePage($_SERVER['PHP_SELF'])){die();}

$newArray[] = array();
$makeArray = fetchUserjson();
$i = 0;

foreach ($makeArray as $v1)
	  {
	  $permid = $v1['permission_id'];
	  $value = $v1['sum1'];
	  $name = $v1['name'];

	  $newArray[$i] = array(
						"label" => $name,
						"data" => $value
						);
		$i++;
	  }

echo json_encode($newArray);
?>




// In models/funcs.php (or your user defined funcs)
// add the following code:

	function fetchUserjson()
		{
		global $mysqli,$db_table_prefix;
		$stmt = $mysqli->prepare("SELECT
		COUNT(permission_id) AS sum1,
		permission_id,name
		FROM ".$db_table_prefix."user_permission_matches LEFT JOIN ".$db_table_prefix."permissions ON permission_id = ".$db_table_prefix."permissions.id  GROUP BY permission_id");
		$stmt->execute();
		$stmt->bind_result($sum1, $permission,$name);
		while ($stmt->fetch()){
		$row[] = array('sum1' => $sum1, 'permission_id' => $permission, 'name' => $name);
		}
		$stmt->close();
		return ($row);
		}


// Usage eg index.php -- *** NOTE revised footer structure
// also need some CSS

#flotcontainer {
    height: 400px;
    text-align: left;
}

<div id="legendPlaceholder"></div>
<div id="flotcontainer"></div>

<!-- footers -->
<?php require_once("models/page_footer.php"); // the final html footer copyright row + the external js calls ?>

 <!-- Flot Charts JavaScript -->
<!--[if lte IE 8]><script src="js/excanvas.min.js"></script><![endif]-->
<script type="text/javascript" src="js/plugins/flot/jquery.flot.js"></script>
<script type="text/javascript" src="js/plugins/flot/jquery.flot.tooltip.min.js"></script>
<script type="text/javascript" src="js/plugins/flot/jquery.flot.resize.js"></script>
<script type="text/javascript" src="js/plugins/flot/jquery.flot.pie.js"></script>

<script type="text/javascript">
$(document).ready(function(){
	$.ajax({
			url: 'json_chart.php',
			contentType: "application/json; charset=utf-8",
			type: "GET",
			dataType: 'json',
			success: function (data) {
				$.plot($("#flotcontainer"), data, {
					 series: {
        pie: {
            show: true
        }
    },
    legend: {
        show: false
    }
});
			},
			failure: function (response) {
				alert(response.d);
			}

		});

	});
</script>

<?php require_once("models/html_footer.php"); // currently just the closing /body and /html ?>


// Check permissions for the json_chart.php file

// finished!