Newer
Older
<?php
/*************************************************************************
class.export.php
Exports stuff (details to follow)
Jared Hancock <jared@osticket.com>
http://www.osticket.com
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See LICENSE.TXT for details.
vim: expandtab sw=4 ts=4 sts=4:
**********************************************************************/
class Export {
static function dumpQuery($sql, $headers, $how='csv', $options=array()) {
$exporters = array(
'csv' => CsvResultsExporter,
'json' => JsonResultsExporter
);
$exp = new $exporters[$how]($sql, $headers, $options);
return $exp->dump();
}
# XXX: Think about facilitated exporting. For instance, we might have a
# TicketExporter, which will know how to formulate or lookup a
# format query (SQL), and cooperate with the output process to add
# extra (recursive) information. In this funciton, the top-level
# SQL is exported, but for something like tickets, we will need to
# export attached messages, reponses, and notes, as well as
# attachments associated with each, ...
static function dumpTickets($sql, $how='csv') {
// Add custom fields to the $sql statement
$cdata = $fields = $select = array();
foreach (TicketForm::getInstance()->getFields() as $f) {
// Ignore core fields
if (in_array($f->get('name'), array('subject','priority')))
continue;
// Ignore non-data fields
elseif (!$f->hasData() || $f->isPresentationOnly())
continue;
$name = $f->get('name') ? $f->get('name') : 'field_'.$f->get('id');
$key = '__field_'.$f->get('id');
// Fetch ID values for ID-based data
if ($f->hasIdValue()) {
$name .= '_id';
}
$cdata[$key] = $f->get('label');
$fields[$key] = $f;
$select[] = "cdata.`$name` AS __field_".$f->get('id');
}
if ($select)
$sql = str_replace(' FROM ', ',' . implode(',', $select) . ' FROM ', $sql);
return self::dumpQuery($sql,
array(
'created' => 'Date',
'subject' => 'Subject',
'name' => 'From',
'email' => 'From Email',
'priority_desc' => 'Priority',
'dept_name' => 'Department',
'helptopic' => 'Help Topic',
'status' => 'Current Status',
'effective_date' => 'Last Updated',
'duedate' => 'Due Date',
'isoverdue' => 'Overdue',
'isanswered' => 'Answered',
'assigned' => 'Assigned To',
'staff' => 'Staff Assigned',
'team' => 'Team Assigned',
'thread_count' => 'Thread Count',
'attachments' => 'Attachment Count',
) + $cdata,
$how,
array('modify' => function(&$record, $keys) use ($fields) {
foreach ($fields as $k=>$f) {
if (($i = array_search($k, $keys)) !== false) {
$record[$i] = $f->export($f->to_php($record[$i]));
}
}
return $record;
})
);
}
/* static */ function saveTickets($sql, $filename, $how='csv') {
ob_start();
self::dumpTickets($sql, $how);
$stuff = ob_get_contents();
ob_end_clean();
if ($stuff)
Http::download($filename, "text/$how", $stuff);
return false;
}
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
static function saveUsers($sql, $filename, $how='csv') {
$exclude = array('name', 'email');
$form = UserForm::getUserForm();
$fields = $form->getExportableFields($exclude);
// Field selection callback
$fname = function ($f) {
return 'cdata.`'.$f->getSelectName().'` AS __field_'.$f->get('id');
};
$sql = substr_replace($sql,
','.implode(',', array_map($fname, $fields)).' ',
strpos($sql, 'FROM '), 0);
$sql = substr_replace($sql,
'LEFT JOIN ('.$form->getCrossTabQuery($form->type, 'user_id', $exclude).') cdata
ON (cdata.user_id = user.id) ',
strpos($sql, 'WHERE '), 0);
$cdata = array_combine(array_keys($fields),
array_values(array_map(
function ($f) { return $f->get('label'); }, $fields)));
ob_start();
echo self::dumpQuery($sql,
array(
'name' => 'Name',
'organization' => 'Organization',
'email' => 'Email'
) + $cdata,
$how,
array('modify' => function(&$record, $keys) use ($fields) {
foreach ($fields as $k=>$f) {
if ($f && ($i = array_search($k, $keys)) !== false) {
$record[$i] = $f->export($f->to_php($record[$i]));
}
}
return $record;
})
);
$stuff = ob_get_contents();
ob_end_clean();
if ($stuff)
Http::download($filename, "text/$how", $stuff);
return false;
}
static function saveOrganizations($sql, $filename, $how='csv') {
$exclude = array('name');
$form = OrganizationForm::getDefaultForm();
$fields = $form->getExportableFields($exclude);
// Field selection callback
$fname = function ($f) {
return 'cdata.`'.$f->getSelectName().'` AS __field_'.$f->get('id');
};
$sql = substr_replace($sql,
','.implode(',', array_map($fname, $fields)).' ',
strpos($sql, 'FROM '), 0);
$sql = substr_replace($sql,
'LEFT JOIN ('.$form->getCrossTabQuery($form->type, '_org_id', $exclude).') cdata
ON (cdata._org_id = org.id) ',
strpos($sql, 'WHERE '), 0);
$cdata = array_combine(array_keys($fields),
array_values(array_map(
function ($f) { return $f->get('label'); }, $fields)));
$cdata += array('account_manager' => 'Account Manager', 'users' => 'Users');
ob_start();
echo self::dumpQuery($sql,
array(
'name' => 'Name',
) + $cdata,
$how,
array('modify' => function(&$record, $keys) use ($fields) {
foreach ($fields as $k=>$f) {
if ($f && ($i = array_search($k, $keys)) !== false) {
$record[$i] = $f->export($f->to_php($record[$i]));
}
}
return $record;
})
);
$stuff = ob_get_contents();
ob_end_clean();
if ($stuff)
Http::download($filename, "text/$how", $stuff);
return false;
}
}
class ResultSetExporter {
function ResultSetExporter($sql, $headers, $options=array()) {
$this->headers = array_values($headers);
if ($s = strpos(strtoupper($sql), ' LIMIT '))
$sql = substr($sql, 0, $s);
# TODO: If $filter, add different LIMIT clause to query
$this->options = $options;
$this->output = $options['output'] ?: fopen('php://output', 'w');
$this->_res = db_query($sql);
if ($row = db_fetch_array($this->_res)) {
$query_fields = array_keys($row);
$this->headers = array();
$this->keys = array();
$this->lookups = array();
foreach ($headers as $field=>$name) {
if (array_key_exists($field, $row)) {
$this->headers[] = $name;
$this->keys[] = $field;
# Remember the location of this header in the query results
# (column-wise) so we don't have to do hashtable lookups for every
# column of every row.
$this->lookups[] = array_search($field, $query_fields);
}
}
db_data_reset($this->_res);
}
}
function getHeaders() {
return $this->headers;
}
function next() {
if (!($row = db_fetch_row($this->_res)))
return false;
$record = array();
foreach ($this->lookups as $idx)
$record[] = $row[$idx];
if (isset($this->options['modify']) && is_callable($this->options['modify']))
$record = $this->options['modify']($record, $this->keys);
return $record;
}
function nextArray() {
if (!($row = $this->next()))
return false;
return array_combine($this->keys, $row);
}
function dump() {
# Useful for debug output
while ($row=$this->nextArray()) {
var_dump($row);
}
}
}
class CsvResultsExporter extends ResultSetExporter {
if (!$this->output)
$this->output = fopen('php://output', 'w');
fputcsv($this->output, $this->getHeaders());
while ($row=$this->next())
fputcsv($this->output, $row);
fclose($this->output);
}
}
class JsonResultsExporter extends ResultSetExporter {
function dump() {
require_once(INCLUDE_DIR.'class.json.php');
$exp = new JsonDataEncoder();
$rows = array();
while ($row=$this->nextArray()) {
$rows[] = $row;
}
echo $exp->encode($rows);
}
}
require_once INCLUDE_DIR . 'class.json.php';
require_once INCLUDE_DIR . 'class.migrater.php';
require_once INCLUDE_DIR . 'class.signal.php';
class DatabaseExporter {
var $stream;
var $tables = array(CONFIG_TABLE, SYSLOG_TABLE, FILE_TABLE,
FILE_CHUNK_TABLE, STAFF_TABLE, DEPT_TABLE, TOPIC_TABLE, GROUP_TABLE,
GROUP_DEPT_TABLE, TEAM_TABLE, TEAM_MEMBER_TABLE, FAQ_TABLE,
FAQ_TOPIC_TABLE, FAQ_CATEGORY_TABLE, DRAFT_TABLE,
CANNED_TABLE, TICKET_TABLE, ATTACHMENT_TABLE,
TICKET_THREAD_TABLE, TICKET_ATTACHMENT_TABLE, TICKET_PRIORITY_TABLE,
TICKET_LOCK_TABLE, TICKET_EVENT_TABLE, TICKET_EMAIL_INFO_TABLE,
EMAIL_TABLE, EMAIL_TEMPLATE_TABLE, EMAIL_TEMPLATE_GRP_TABLE,
FILTER_TABLE, FILTER_RULE_TABLE, SLA_TABLE, API_KEY_TABLE,
TIMEZONE_TABLE, SESSION_TABLE, PAGE_TABLE,
FORM_SEC_TABLE, FORM_FIELD_TABLE, LIST_TABLE, LIST_ITEM_TABLE,
FORM_ENTRY_TABLE, FORM_ANSWER_TABLE, USER_TABLE, USER_EMAIL_TABLE,
);
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
function DatabaseExporter($stream) {
$this->stream = $stream;
}
function write_block($what) {
fwrite($this->stream, JsonDataEncoder::encode($what));
fwrite($this->stream, "\x1e");
}
function dump($error_stream) {
// Allow plugins to change the tables exported
Signal::send('export.tables', $this, $this->tables);
$header = array(
array(OSTICKET_BACKUP_SIGNATURE, OSTICKET_BACKUP_VERSION),
array(
'version'=>THIS_VERSION,
'table_prefix'=>TABLE_PREFIX,
'salt'=>SECRET_SALT,
'dbtype'=>DBTYPE,
'streams'=>DatabaseMigrater::getUpgradeStreams(
UPGRADE_DIR . 'streams/'),
),
);
$this->write_block($header);
foreach ($this->tables as $t) {
if ($error_stream) $error_stream->write("$t\n");
// Inspect schema
$table = $indexes = array();
$res = db_query("show columns from $t");
while ($field = db_fetch_array($res))
$table[] = $field;
$res = db_query("show indexes from $t");
while ($col = db_fetch_array($res))
$indexes[] = $col;
$res = db_query("select * from $t");
$types = array();
if (!$table) {
if ($error_stream) $error_stream->write(
$t.': Cannot export table with no fields'."\n");
die();
}
$this->write_block(
array('table', substr($t, strlen(TABLE_PREFIX)), $table,
$indexes));
// Dump row data
while ($row = db_fetch_row($res))
$this->write_block($row);
$this->write_block(array('end-table'));
}
}
}