/**
* Report indexed sources from pia.source.
*/
public static function reportSources()
{
global $piaApp;
//
// ----- build SQL to read the data
//
$sql = 'SELECT ' . piaSource::getFields();
$sql .= ', i.num_items, i.size_bytes';
$sql .= piaSource::getJoins();
$sql .= ' INNER JOIN
(
SELECT i.source_rowid_fk, COUNT(i.rowid) AS num_items,
SUM(i.size_bytes) AS size_bytes
FROM pia.index i
GROUP BY i.source_rowid_fk
) i ON i.source_rowid_fk = source.rowid';
//
// ----- connect to the database and read the data
//
$db = piaDB::connect();
if ($db) {
$result = pg_query($db, $sql);
if (!$result) {
piaDB::errorWithSQL($sql);
}
if (pg_num_rows($result) > 0) {
$s = new piaSource();
echo '<h2>Sources</h2>';
$tableId = 'SourcesTable';
$piaApp->initTablesorterTable($tableId);
piaApp::startTable($tableId, 'tablesorter', False, 'width:70%;');
piaApp::writeTableHeader(array(array('Name', '35%'), array('Description', '35%'), array('Num Items', '15%'), array('Size (bytes)', '15%')));
echo '<tbody>';
//
// ----- loop through the results
//
while ($row = pg_fetch_array($result)) {
$cur = $s->readRowFromDatabase($row);
$num_items = piaDB::cleanDataRead($row[$cur++]);
$size_bytes = piaDB::cleanDataRead($row[$cur++]);
echo '<tr>';
piaApp::writeTableData($s->name);
piaApp::writeTableData($s->description);
piaApp::writeTableData(piaApp::getFormattedNumber($num_items, 0, -9));
piaApp::writeTableData(piaApp::getFormattedNumber($size_bytes, 0, -9));
echo '</tr>';
}
echo '</tbody></table><p />';
}
pg_free_result($result);
piaDB::close($db);
}
}
/**
* Show the search results.
*/
protected function showSearchResults()
{
global $piaApp;
if (strlen($this->searchstr) > 0) {
$str = '%' . $this->searchstr . '%';
//
// ----- build SQL to read the data
//
$sql = "SELECT rowid, full_path, filename, extension, size_bytes\n FROM pia.view_full_path\n WHERE full_path ILIKE \$1 ORDER BY full_path LIMIT ";
$sql .= piaSearch::MAX_RESULTS;
//
// ----- connect to the database and read the data
//
$db = piaDB::connect();
if ($db) {
$result = pg_query_params($db, $sql, array($str));
if (!$result) {
piaDB::errorWithSQL($sql);
}
if (pg_num_rows($result) > 0) {
$tableId = 'SearchResultsTable';
$piaApp->initTablesorterTable($tableId);
piaApp::startTable($tableId, 'tablesorter', False, 'width:100%;');
piaApp::writeTableHeader(array(array('Full Path', '60%'), array('Filename', '20%'), array('Extension', '10%'), array('Size (bytes)', '10%')));
echo '<tbody>';
//
// ----- loop through the results
//
while ($row = pg_fetch_array($result)) {
$cur = 0;
$rowid = piaDB::cleanDataRead($row[$cur++]);
$full_path = piaDB::cleanDataRead($row[$cur++]);
$filename = piaDB::cleanDataRead($row[$cur++]);
$extension = piaDB::cleanDataRead($row[$cur++]);
$size_bytes = piaDB::cleanDataRead($row[$cur++]);
echo '<tr>';
piaApp::writeTableData($full_path);
piaApp::writeTableData($filename);
piaApp::writeTableData($extension);
piaApp::writeTableData(piaApp::getFormattedNumber($size_bytes, 0, -9));
echo '</tr>';
}
echo '</tbody></table><p />';
}
pg_free_result($result);
piaDB::close($db);
}
}
}
/**
* Execute a query against the database, typically an INSERT or UPDATE query, uses the pg_query_params() function.
* @param string $query A SQL query command with coded parameters.
* @param array $parms An array of parameters that will be substituted into the query string.
* @param string $logActivityType Optional log activity type, when specified must be a valid entry in std.activity_type;
* @param connection $db Optional opened database connection, if not specified a new connection will be opened and closed, default is null.
* @param boolean $showErrors True (the default) to show error messages, False otherwise.
* @param boolean $logMessage An optional message for the activity log, default is the SQL used for the query.
*/
public static function executeQuery($query, $parms, $logActivityType = '', $db = null, $showErrors = True, $logMessage = null)
{
$success = false;
$close_conn = false;
//
// ----- open and write to the database
//
if (!$db) {
$db = piaDB::connect();
$close_conn = true;
}
if ($db) {
$result = pg_query_params($db, $query, $parms);
if (!$result) {
if ($showErrors) {
piaDB::errorWithSQL($query, $parms);
}
} else {
if (pg_affected_rows($result) > 0) {
$success = true;
}
}
//
// ----- cleanup
//
if (is_resource($result)) {
pg_free_result($result);
}
if ($close_conn) {
piaDB::close($db);
}
//
// ----- log activity
//
if (strlen($logActivityType) > 0) {
if (isset($logMessage)) {
piaDB::logActivity($logActivityType, $logMessage);
} else {
piaDB::logActivity($logActivityType, $query);
}
}
}
return $success;
}