<?php
/**
 * Phoebe pedagogic planner
 *
 * PHP version 5
 *
 * LICENSE: The GNU General Public License (GPL), version 2. See LICENSE.txt
 *
 * Copyright (c) 2006-2008 JISC/HEFCE/University of Oxford
 *
 * @author      David Balch <david.balch@conted.ox.ac.uk>
 * @license     http://opensource.org/licenses/gpl-license.php GPL2
 * @link        http://phoebe-project.conted.ox.ac.uk/
 */

$content['settings'] = 'admin';
include_once($_SERVER['DOCUMENT_ROOT'].'/inc/core.php');
include_once($_SERVER['DOCUMENT_ROOT'].'/inc/utils.php');
include_once($_SERVER['DOCUMENT_ROOT'].'/inc/session.php');

$mdb2->loadModule('Manager');

/* *** Get querystring values & filter them *** */
if ($_POST) {
    $_GET = $_POST;
}

if (!ctype_digit($_GET['numStart'])) {
    $_GET['numStart'] = 0;
}
if (!ctype_digit($_GET['numResults'])) {
    $_GET['numResults'] = 500;
}

$qsArray = $_GET;
$qs = '?';
foreach($qsArray as $name => $value) {
    $quoted[$name] = $mdb2->quote($value);
    $html[$name] = htmlentities($value);
    $qs .= $name.'='.$html[$name].'&amp;';
}

// Build a new querystring, adding/changing values as neccessary
function buildQs($vals) {
    global $qsArray;
    $thisArray = $qsArray;
    $thisQs = '?';

    // Update qs vals with the given variable.
    $thisArray = array_merge($thisArray, $vals);

    // Loop over values from original GET, building new querystring
    foreach($thisArray as $name => $value) {
        $html[$name] = htmlentities($value);
        $thisQs .= $name.'='.$html[$name].'&amp;';
    }

    return $thisQs;
}

// Construct page
$content['windowTitle'] = 'Explore database ' . $mdb2->database_name . ' | ' . $CNF->appName;
if ($html['table']) {
    $content['windowTitle'] = $html['table'].' | '.$content['windowTitle'];
}

$content['main'] = '<h1><a href="/admin/exploredb.php">Phoebe database</a></h1>';

if (!$html['table']) {
    // List tables
    $tableList = "<h2>Tables</h2>\n<ol>\n";
    foreach ($mdb2->listTables() as $table) {
        //$tableList .='<li><a href="'.$table.'"><h3>'.$table.'</h3>';
        
        $tableList .='<li><a href="exploredb.php'.$qs.'table='.$table.'">'.$table.'</a></li>';
    }
    $tableList .= '</ol>';

    $content['main'] .= $tableList;
} else {
    // Display table info

    // List of columns
    $columns = $mdb2->listTableFields($_GET['table']);
    if (PEAR::isError($columns)) {
        echo ($columns->getMessage());
    }

    if ($_GET['showVal'] != '' && isset($_GET['showCol']) && !preg_match('/[^a-z_]/', $_GET['showCol'])) {
        if (!preg_match('/[^A-Za-z0-9_]/', $_GET['showVal'])) {
            $showCol = $_GET['showCol'];
            $showVal = $_GET['showVal'];
            // Specify query more closely
            $where = " WHERE ".$showCol." = ".$mdb2->quote($showVal);
        }
        //echo $where;
    }

    // Form to modify exploration parameters.
    $exploreForm = ' <form action="" method="GET" style="display: inline">';
    // Add qs vals as hidden fields
    foreach($qsArray as $name => $value) {
        switch ($name) {
            case 'showCol':
            case 'showVal':
            case 'numStart':
            case 'numResults':
                break;
            default:
                $exploreForm .= '<input type="hidden" name="'.$name.'" value="'.$value.'"/>';
        }
    }
    $exploreForm .= '<select name="showCol">';
    foreach($columns as $colName) {
        if ($showCol == $colName) {
            $exploreForm .= '<option selected="selected">';
        } else {
            $exploreForm .= '<option>';
        }
        $exploreForm .= $colName.'</option>';
    }
    $exploreForm .= '</select>=';
    $exploreForm .= '<input type="text" name="showVal" value="'.$showVal.'"/>, ';
    $exploreForm .= '<label>Start=<input type="text" name="numStart" size="5" value="'.$_GET['numStart'].'"/></label>, ';
    $exploreForm .= '<label># results=<input type="text" name="numResults" size="5" value="'.$_GET['numResults'].'"/></label>';
    $exploreForm .= ' <input type="submit"/></form>';
    //$content['main'] .= $exploreForm;

    // Table data
    $tableCaption = '<caption>Viewing: '.$html['table'].$exploreForm.'</caption>';


    if (isset($_GET['orderBy']) && !preg_match('/[^a-z_]/', $_GET['orderBy'])) {
        // Order results by a given column
        $orderBy = " ORDER BY ".$mdb2->quote($_GET['orderBy']);
    
        // Sort asceding/descending
        if ($_GET['dir'] == 'asc') {
            $orderBy .= " ASC";
        } else if ($_GET['dir'] == 'desc') {
            $orderBy .= " DESC";
        }
    }


    $tableHead = '<thead><tr>';
    if (isset($orderBy)) {
        $tableHead .= '<th><a href="exploredb.php'.buildQs(array('dir'=>'asc')).'" title="Order ascending">&#8679;</a> <a href="exploredb.php'.buildQs(array('dir'=>'desc')).'" title="Order descending">&#8681;</a></th>';
    } else {
        $tableHead .= '<th></th>';
    }
    foreach($columns as $colName) {
        $tableHead .= '<th><a href="exploredb.php'.buildQs(array('orderBy'=>$colName)).'">'.$colName.'</a></th>';
    }
    $tableHead .= "</tr></thead>\n";

    // Table values
    $sql = 'SELECT * FROM '.$html['table'].$where.$orderBy;
    $content['main'] .= $sql;
    $mdb2->setLimit($_GET['numResults'], $_GET['numStart']);
    $res = $mdb2->queryAll($sql);
    if (PEAR::isError($res)) {
        echo ($res->getMessage());
    }

    $tableBody = '<tbody>';
    $count = 1;
    foreach ($res as $row) { 
        $tableBody .= "<tr><td>".$count."&nbsp;</td>\n";
        foreach($row as $value){
            $tableBody .= "  <td>".htmlentities($value)."</td>\n";
        }
        $count ++;
        $tableBody .= "</tr>\n";
    }
    $tableBody .= '</tbody>';

    $tableDesc = '<table class="dbTable">'.$tableCaption.$tableHead.$tableBody.'</table>';

    $content['main'] .= $tableDesc;
}

// Write via template
include_once($CNF->template);
?>