Totorial: Create customized reports

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
miwalder
Posts: 3
Joined: 2013-02-11 20:36

Totorial: Create customized reports

Post by miwalder » 2016-01-26 12:37

Many users asked how to make reports in Appgini and Ahmed anounced it as a new feature in one of the next version. For all those who can't wait like mi here is how I solved this:

1. Create a table view in your database. See in https://www.404techsupport.com/2012/04/ ... hpmyadmin/ how it works with phpmyadmin. The view contains all the necesary fields you need afterwards to build your report table. IMPORTANT: The view must contain the ids of the main category and the ids of the subcategories you want to display in your future report.

2. Add functions to the hooks/_global.php. We add two funcions to the _global.php file in the hooks folder. The first one is to build a jumpmenu to choose the main category of your report the second one builds the subcategory titles and the tables below.

Code: Select all

//Program Selector
function programselector($a, $b, $c) {

	$sqllistquery = sql("".$c."");
	
	echo "<form name='viewselector' class='noprint'>";
	echo "<select name='list1' id='list1' class='noPrint' onChange='javascript:goToPage(options[selectedIndex].value)'>";
	echo "<option>Escoge un programa</option>";
	while ($rowselector = db_fetch_assoc($sqllistquery)) {
		$pr_id = $rowselector["pr_id"];
		$pr_name = $rowselector["pr_name"];
		if ($a==$pr_id) {
		$currentprogram = $pr_name;
		echo "<option selected value='".$b."?pr_id=".$a."'>".$pr_name."</option>";
		}
		else {
		echo "<option value='".$b."?pr_id=".$pr_id."'>".$pr_name."</option>";
		}
	}
	echo "</select></form>";
	return "<h1>".$currentprogram."</h1>";
}

function tablebuilder($a, $b, $c, $e, $f, $g, $h, $j) {


// Build Table titles
$groupquery = sql($h);
while ($row = db_fetch_assoc($groupquery)) {
	$gr_id = $row["gr_id"];
	$gr_name = $row["gr_name"];

	// Build table sums
	$sumquery = sql("SELECT COUNT(*) as sum ".$j."WHERE pr_id='$g' AND gr_id='$gr_id'");
	//$test = "SELECT COUNT(*) ".$j. "WHERE pr_id='$g' AND gr_id='$gr_id'";
	$sum = db_fetch_assoc($sumquery);
	$sumresult = $sum["sum"];
	
	if ($sumresult > 0) {
	echo "<h2>".$gr_name."</h2>";

		//Generate Tables
		$tablehead = "<table  width='".$e."' border ='1' frame='void' rules='rows' >
					<tr class='tableheader'>";
		$tablefoot = "<tr class='tablebottom'><td colspan =".count($c).">".$sumresult." Alumnos</td></tr></table>";	
	
		echo $tablehead;

		for ($i = 0; $i < count($c); $i++) {
		echo "<td width=".$f.">".$c[$i]."</td>";
		}			    
		echo "</tr>";
	
		$query = sql($a.$j." WHERE pr_id = '$g' AND gr_id='$gr_id'");

		while ($row = db_fetch_assoc($query)) {
	  		echo "<tr>";
			for ($i = 0; $i < count($b); $i++) {
			echo "<td>".$row[$b[$i]]."</td>";
			}
			echo "</tr>";
		}
3. Create files for the new reports. Like Ahmed explained in this tutorial you can create a custom page in appgini: http://bigprof.com/appgini/help/advance ... cess-pages

Create a custom page and add this code to line 30 (after the access configuration for appgini custom pages)

Code: Select all

//Category selector
//Query you need to choose the main categories (must contain the id of the category)
$catquery = "SELECT pr_id, pr_name FROM programs ORDER BY pr_name";
echo programselector($pr_id, $view, $catquery);

// Tablebuilder configuration
// Data

// Specify the name of the sql view you nee for your report.
$sqlfrom = "FROM `parents_students_tech_view`";						

// Select two fields (id and name of the group) which serve as table subtitles.
$groupquery = "SELECT gr_id, gr_name FROM grades ORDER BY gr_name ";			

// Select the fields of your mysql view you need in your report 
//(must contain the main report key and the subkey) skip the FROM part at the end.
$query = "SELECT pr_id, gr_id, st_lastname, st_name, st_birthday ";

// Array of all fields you want to be displayed in your report
$fields = array("st_lastname", "st_name", "st_birthday");	

// Column captions for the table
$column_caption = array("Apellido", "Nombre", "Cumpleaño");		

// Table design
$table_width = "800 px";										
$column_width = "100 px";		

echo tablebuilder($query, $fields, $column_caption, $table_width, $column_width, $pr_id, $groupquery, $sqlfrom);

4. Integrate the tables to the main menu of your application. Add these lines to links-navmenu.php
$navLinks[] = array(
			'url' => 'report_studentsparents.php', 
			'title' => 'Alumnos y Padres', 'Reports',
			'groups' => array('*'), // groups allowed to see this link, use '*' if you want to show the link to all groups
			'icon' => 'resources/table_icons/cash_register.png'
			);
5. Thats it! I hope this tutorial was useful. Please let mi know how you use it or how you could improve it. ;)

jhon66
Posts: 1
Joined: 2017-02-08 07:45

Re: Totorial: Create customized reports

Post by jhon66 » 2017-02-08 07:50

Hi there,
That's a great tutorial.
I have found many important tricks from this post.
It really helps.
Anyway,Thanks for this awesome tutorial :)

Post Reply