Totorial: Create customized reports
Posted: 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.
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)
5. Thats it! I hope this tutorial was useful. Please let mi know how you use it or how you could improve it.
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>";
}
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'
);