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'
);