/** * build one table * * Accept following variants: * - csv - to provide a downloadable csv page * - json - to provide all values in one column * - inline - to render tables within articles * - simple - the legacy fixed table * - sortable - click on column to sort the row * * @param the id of the table to build * @param string the variant to provide - default is 'simple' * @return a displayable string */ public static function build($id, $variant = 'simple') { global $context; // split parameters $attributes = preg_split("/\\s*,\\s*/", $id, 3); $id = $attributes[0]; // get the table object if (!($table = Tables::get($id))) { return NULL; } // do the SELECT statement if (!($rows = SQL::query($table['query']))) { Logger::error(sprintf(i18n::s('Error in table query %s'), $id) . BR . htmlspecialchars($table['query']) . BR . SQL::error()); return NULL; } // build the resulting string $text = ''; switch ($variant) { // produce a table readable into MS-Excel case 'csv': // comma separated values $separator = ","; // one row for the title if ($table['title']) { $label = preg_replace('/\\s/', ' ', $table['title']); // encode to ASCII $label = utf8::to_ascii($label, PRINTABLE_SAFE_ALPHABET); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); $text .= '"' . $label . '"'; $text .= "\n"; } // one row for header fields $index = 0; while ($field = SQL::fetch_field($rows)) { if ($index++) { $text .= $separator; } $label = trim(preg_replace('/\\s/', ' ', ucfirst($field->name))); // encode $label = utf8::to_ascii($label, PRINTABLE_SAFE_ALPHABET); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); $text .= '"' . $label . '"'; } $text .= "\n"; // process every table row $row_index = 0; while ($row = SQL::fetch($rows)) { // one cell at a time $index = 0; foreach ($row as $name => $value) { // glue cells if ($index++) { $text .= $separator; } // remove HTML tags $value = strip_tags(str_replace('</', ' </', str_replace(BR, ' / ', $value))); // clean spaces $label = trim(preg_replace('/\\s+/', ' ', $value)); // encode $label = utf8::to_ascii($label, PRINTABLE_SAFE_ALPHABET); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); // make a link if this is a reference if ($index == 1 && $table['with_zoom'] == 'Y') { $label = $context['url_to_home'] . $context['url_to_root'] . $label; } // quote data $text .= '"' . $label . '"'; } // new line $text .= "\n"; } return $text; // a JSON set of values // a JSON set of values case 'json': // get header labels $labels = array(); while ($field = SQL::fetch_field($rows)) { $labels[] = trim(preg_replace('/[^\\w]+/', '', ucfirst($field->name))); } // all items $data = array(); $data['items'] = array(); while ($row = SQL::fetch_row($rows)) { // all rows $datum = array(); $label = FALSE; $index = 0; $link = NULL; foreach ($row as $name => $value) { $index++; // first column is only a link if ($index == 1 && $table['with_zoom'] == 'Y') { $link = $context['url_to_home'] . $context['url_to_root'] . ltrim($value, '/'); continue; } // adjust types to not fool the json encoder if (preg_match('/^(\\+|-){0,1}[0-9]+$/', $value)) { $value = intval($value); } elseif (preg_match('/^(\\+|-){0,1}[0-9\\.,]+$/', $value)) { $value = floatval($value); } elseif (preg_match('/^(true|false)$/i', $value)) { $value = intval($value); } // ensure we have some label for SIMILE Exhibit if (!$label) { $label = $value; } // combine first and second columns if ($index == 2 && $link) { $value = Skin::build_link($link, $value, 'basic'); } // save this value $datum[$labels[$name]] = utf8::to_ascii($value, PRINTABLE_SAFE_ALPHABET); } if ($label && !in_array('label', $labels)) { $datum['label'] = utf8::to_ascii($label, PRINTABLE_SAFE_ALPHABET); } // add a tip, if any $data['items'][] = $datum; } include_once $context['path_to_root'] . 'included/json.php'; $text .= json_encode2($data); return $text; // list of facets for SIMILE Exhibit // list of facets for SIMILE Exhibit case 'json-facets': // columns are actual facets $facets = array(); $index = 0; while ($field = SQL::fetch_field($rows)) { $index++; // first column is only a link if ($index == 1 && $table['with_zoom'] == 'Y') { continue; } // first column has a link if ($index == 2 && $table['with_zoom'] == 'Y') { continue; } // column is a facet $label = '.' . trim(preg_replace('/[^\\w]+/', '', ucfirst($field->name))); $title = trim(str_replace(',', '', ucfirst($field->name))); $facets[] = '<div ex:role="facet" ex:expression="' . $label . '" ex:facetLabel="' . $title . '"></div>'; // only last columns can be faceted if (count($facets) > 7) { array_shift($facets); } } // reverse facet order $facets = array_reverse($facets); // job done $text = join("\n", $facets); return $text; // list of columns for SIMILE Exhibit // list of columns for SIMILE Exhibit case 'json-labels': // get header labels $labels = array(); $index = 0; while ($field = SQL::fetch_field($rows)) { $index++; // first column is only a link if ($index == 1 && $table['with_zoom'] == 'Y') { continue; } // column id $labels[] = '.' . trim(preg_replace('/[^\\w]+/', '', ucfirst($field->name))); // limit the number of columns put on screen if (count($labels) >= 7) { break; } } // job done $text = join(', ', $labels); return $text; // titles of columns for SIMILE Exhibit // titles of columns for SIMILE Exhibit case 'json-titles': // get header labels $labels = array(); $index = 0; while ($field = SQL::fetch_field($rows)) { $index++; // first column is only a link if ($index == 1 && $table['with_zoom'] == 'Y') { continue; } // column header $labels[] = trim(str_replace(',', '', ucfirst($field->name))); } $text = join(', ', $labels); return $text; // produce an HTML table // produce an HTML table default: case 'inline': case 'sortable': // a table with a grid $text .= Skin::table_prefix('grid'); // the title, with a menu to download the table into Excel if ($variant == 'inline') { $item_bar = array(); $item_bar += array(Tables::get_url($id) => $table['title']); $item_bar += array(Tables::get_url($id, 'fetch_as_csv') => 'CSV (Excel)'); if (Surfer::is_associate()) { $item_bar += array(Tables::get_url($id, 'edit') => i18n::s('edit')); } if (count($item_bar)) { $text .= '<caption>' . Skin::build_list($item_bar, 'menu') . "</caption>\n"; } } // column headers are clickable links $cells = array(); $index = 0; while ($field = SQL::fetch_field($rows)) { if ($index++ != 0 || $table['with_zoom'] != 'Y') { $cells[] = ucfirst($field->name); } } $text .= "\t\t" . Skin::table_row($cells, 'sortable'); // the table body $count = 0; $row_index = 0; while ($row = SQL::fetch_row($rows)) { $cells = array(); $link = ''; for ($index = 0; $index < count($row); $index++) { if ($index == 0 && $table['with_zoom'] == 'Y') { $link = $row[$index]; } elseif ($link) { $cells[] = Skin::build_link($link, $row[$index]); $link = ''; } else { $cells[] = $row[$index]; } } $text .= "\t\t" . Skin::table_row($cells, $count++); } $text .= Skin::table_suffix(); return $text; // adapted to open chart flash // adapted to open chart flash case 'chart': // get title for y series $y_title = $y2_title = $y3_title = NULL; $y_index = $y2_index = $y3_index = 0; $index = 0; while ($field = SQL::fetch_field($rows)) { // time will be used for x labels if ($index == 0 && $table['with_zoom'] == 'T') { } elseif ($index == 0 && $table['with_zoom'] == 'Y') { } elseif (!$y_title) { $y_title = '"' . ucfirst($field->name) . '"'; $y_index = $index; } elseif (!$y2_title) { $y2_title = '"' . ucfirst($field->name) . '"'; $y2_index = $index; } elseif (!$y3_title) { $y3_title = '"' . ucfirst($field->name) . '"'; $y3_index = $index; break; } $index++; } // process every table row $x_labels = array(); $y_values = array(); $y2_values = array(); $y3_values = array(); $y_min = $y_max = NULL; $count = 1; while ($row = SQL::fetch($rows)) { // one cell at a time $index = 0; foreach ($row as $name => $value) { // clean spaces $label = trim(preg_replace('/\\s/', ' ', $value)); // encode in iso8859 $label = utf8::to_iso8859($label); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); // quote data if (preg_match('/-*[^0-9\\,\\.]/', $label)) { $label = '"' . $label . '"'; } // x labels if ($index == 0) { if ($table['with_zoom'] == 'T') { array_unshift($x_labels, $label); } else { $x_labels[] = $count++; } // y value } elseif ($index == $y_index) { if ($table['with_zoom'] == 'T') { array_unshift($y_values, $label); } else { $y_values[] = $label; } if (!isset($y_min) || intval($label) < $y_min) { $y_min = intval($label); } if (!isset($y_max) || intval($label) > $y_max) { $y_max = intval($label); } // y2 value } elseif ($index == $y2_index) { if ($table['with_zoom'] == 'T') { array_unshift($y2_values, $label); } else { $y_values[] = $label; } if (!isset($y_min) || intval($label) < $y_min) { $y_min = intval($label); } if (!isset($y_max) || intval($label) > $y_max) { $y_max = intval($label); } // y3 value } elseif ($index == $y3_index) { if ($table['with_zoom'] == 'T') { array_unshift($y3_values, $label); } else { $y_values[] = $label; } if (!isset($y_min) || intval($label) < $y_min) { $y_min = intval($label); } if (!isset($y_max) || intval($label) > $y_max) { $y_max = intval($label); } // we won't process the rest break; } // next column $index++; } } // y minimum if ($y_min > 0) { $y_min = 0; } // y maximum $y_max = strval($y_max); if (strlen($y_max) == 1) { $y_max = 10; } elseif (strlen($y_max) == 2) { $y_max = (intval(substr($y_max, 0, 1)) + 1) * 10; } elseif (strlen($y_max) == 3) { $y_max = (intval(substr($y_max, 0, 2)) + 1) * 10; } else { $y_max = strval(intval(substr($y_max, 0, 2)) + 1) . substr('0000000000000000000000000000000000000000000000000000', 0, strlen($y_max) - 2); } // data series $elements = array(); if (count($y_values)) { $elements[] = '{ "type":"bar_glass", "colour":"#BF3B69", "values": [ ' . join(',', $y_values) . ' ], "text": ' . $y_title . ', "font-size": 12 }'; } if (count($y2_values)) { $elements[] = '{ "type": "line", "width": 1, "colour": "#5E4725", "values": [ ' . join(',', $y2_values) . ' ], "text": ' . $y2_title . ', "font-size": 12 }'; } if (count($y3_values)) { $elements[] = '{ "type":"bar_glass", "colour":"#5E0722", "values": [ ' . join(',', $y3_values) . ' ], "text": ' . $y3_title . ', "font-size": 12 }'; } // the full setup $text = '{ "elements": [ ' . join(',', $elements) . ' ], "x_axis": { "offset": false, "steps": 1, "labels": { "steps": 3, "rotate": 310, "labels": [ ' . join(',', $x_labels) . ' ] } }, "y_axis": { "min": ' . $y_min . ', "max": ' . $y_max . ' } }'; return $text; // first number // first number case 'column': // comma separated values $separator = ","; // process every table row while ($row = SQL::fetch($rows)) { // not always the first column $index = 0; foreach ($row as $name => $value) { $index++; // skip dates and links if ($index == 1 && $table['with_zoom'] != 'N') { continue; } // glue cells if ($text) { $text .= $separator; } // clean spaces $label = trim(preg_replace('/\\s/', ' ', $value)); // encode in iso8859 $label = utf8::to_iso8859($label); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); // quote data if (preg_match('/[^a-zA-Z0-9\\,\\.\\-_]/', $label)) { $text .= '"' . $label . '"'; } else { $text .= $label; } // only first column break; } } return $text; // produce a raw table // produce a raw table case 'raw': // comma separated values $separator = ","; // process every table row while ($row = SQL::fetch($rows)) { // one cell at a time $index = 0; foreach ($row as $name => $value) { // glue cells if ($index++) { $text .= $separator; } // clean spaces $label = trim(preg_replace('/\\s/', ' ', $value)); // encode in iso8859 $label = utf8::to_iso8859($label); // escape quotes to preserve them in the data $label = str_replace('"', '""', $label); // make a link if this is a reference if ($index == 0 && $table['with_zoom'] == 'Y') { $label = $context['url_to_home'] . $context['url_to_root'] . $label; } // quote data if (preg_match('/[^a-zA-Z0-9\\-_]/', $label)) { $text .= '"' . $label . '"'; } else { $text .= $label; } } // new line $text .= "\n"; } return $text; // a simple table // a simple table case 'simple': $text .= Skin::table_prefix('table'); // columns headers $index = 0; while ($field = SQL::fetch_field($rows)) { $cells[] = ucfirst($field->name); } $text .= Skin::table_row($cells, 'header'); // other rows while ($row = SQL::fetch_row($rows)) { $text .= Skin::table_row($row, $count++); } $text .= Skin::table_suffix(); return $text; // xml table // xml table case 'xml': $text = ''; while ($row = SQL::fetch($rows)) { $text .= ' <item>' . "\n"; foreach ($row as $name => $value) { $type = preg_replace('/[^a-z0-9]+/i', '_', $name); if (preg_match('/^[^a-z]/i', $type)) { $type = '_' . $type; } $text .= ' <' . $type . '>' . preg_replace('/&(?!(amp|#\\d+);)/i', '&', utf8::transcode(str_replace(array('left=', 'right='), '', $value))) . '</' . $type . '>' . "\n"; } $text .= ' </item>' . "\n\n"; } return '<?xml version="1.0" encoding="' . $context['charset'] . '"?>' . "\n" . '<items>' . "\n" . $text . '</items>' . "\n"; } }
* @reference * @license http://www.gnu.org/copyleft/lesser.txt GNU Lesser General Public License */ // common definitions and initial processing include_once '../shared/global.php'; include_once 'tables.php'; // look for the id $id = NULL; if (isset($_REQUEST['id'])) { $id = $_REQUEST['id']; } elseif (isset($context['arguments'][0])) { $id = $context['arguments'][0]; } $id = strip_tags($id); // get the item from the database $item = Tables::get($id); // get the related anchor, if any $anchor = NULL; if (isset($item['anchor']) && $item['anchor']) { $anchor = Anchors::get($item['anchor']); } // only associates can proceed if (Surfer::is_associate()) { $permitted = TRUE; } else { $permitted = FALSE; } // load the skin, maybe with a variant load_skin('tables', $anchor); // clear the tab we are in, if any if (is_object($anchor)) {
if ($anchor = Articles::lookup('my_article')) { $fields = array(); $fields['anchor'] = $anchor; $fields['description'] = i18n::c('Hello world'); $fields['edit_name'] = $names[rand(0, 2)]; if (Comments::post($fields)) { $text .= sprintf(i18n::s('Comments have been added to "%s".'), 'my_article') . BR . "\n"; } else { $text .= Logger::error_pop() . BR . "\n"; } } // tables // $text .= Skin::build_block(i18n::s('Tables'), 'subtitle'); // 'my_articles' article if (Tables::get('my_articles')) { $text .= i18n::s('A sample "my_articles" table already exists.') . BR . "\n"; } elseif ($anchor = Articles::lookup('my_article')) { $fields = array(); $fields['anchor'] = $anchor; $fields['nick_name'] = 'my_articles'; $fields['title'] = i18n::c('My Articles'); $fields['description'] = i18n::c('This is a sample table to let you learn and practice.'); $fields['query'] = "SELECT \n" . "articles.title as titre, \n" . "articles.id as 'id', \n" . "articles.introduction as introduction, \n" . "articles.edit_name as 'last editor', \n" . "articles.edit_date as 'Date' \n" . "FROM " . SQL::table_name('articles') . " AS articles \n" . "WHERE (articles.active='Y') \n" . "ORDER BY articles.rank, articles.edit_date DESC, articles.title LIMIT 0,10"; if (Tables::post($fields)) { $text .= sprintf(i18n::s('A table "%s" has been created.'), $fields['nick_name']) . BR . "\n"; } else { $text .= Logger::error_pop() . BR . "\n"; } } // job done