<?php
if (@file_exists('inc/auth.inc.php') && @include_once 'inc/auth.inc.php');
else exit;
function ezin_admin_exports_main($restricted = 0) {
global $ezin_cfg;
global $ezin_sys;
global $rest;
$rest = $restricted;
/**
* Exportovanie udajov zo struktur Metafoxu
*
* Vyzaduje:
* - pre pripojenia k databaze pouziva $ezin_db_handle
*
* Autor: Igor Mino, igor@platon.sk
*
* Verzia: 1.0 2012-05-25 19:03 - vytvorene
* 1.1 2012-06-05 14:30 - doplneny export pre SQL a fixnuta diakritika
*
*/
if (!isset($_POST['action'])) {
$languages_html = '';
if (is_array($ezin_cfg['languages'])
&& count($ezin_cfg['languages']) > 0)
{
foreach ($ezin_cfg['languages'] as $l) {
$languages_html .= '<option value="'.$l.'">'.strtoupper($l).'</option>';
}
}
$authors = ezin_get_authors();
$authors_html = '';
if (is_array($authors)
&& count($authors) > 0)
{
foreach ($authors as $author) {
$access_key = strlen($author['access_key']) > 0 ? '('.$author['access_key'].')' : '';
$authors_html .= '
<option value="'.$author['id'].'">'.$author['name'].' '.$access_key.'</option>';
}
}
$sections = ezin_get_section_list();
$sections_html = '';
if (is_array($sections)
&& count($sections) > 0)
{
foreach ($sections as $section) {
$access_key = strlen($section['section_access_key']) > 0 ? '('.$section['section_access_key'].')' : '';
$sections_html .= '
<option value="'.$section['section_id'].'">'.$section['section_name'].' '.$access_key.' #'.$section['section_id'].'</option>';
}
}
$articles = ezin_get_article_list();
$articles_html = '';
if (is_array($articles)
&& count($articles) > 0)
{
foreach ($articles as $article) {
$access_key = strlen($article['article_access_key']) > 0 ? '('.$article['article_access_key'].')' : '';
$name = strlen($article['article_title']) > 20 ? substr($article['article_title'], 0, 20).'...' : $article['article_title'];
$articles_html .= '
<option value="'.$article['article_id'].'">'.$name.' '.$access_key.' #'.$article['article_id'].'</option>';
}
}
$users = ezin_get_users();
$users_html = '';
$users_username_html = '';
if (is_array($users)
&& count($users) > 0)
{
foreach ($users as $user) {
$users_html .= '
<option value="'.$user['id'].'">'.$user['username'].' #'.$user['id'].'</option>';
$users_username_html .= '
<option value="'.$user['username'].'">'.$user['username'].' #'.$user['id'].'</option>';
}
}
$roles = ezin_get_roles();
$roles_html = '';
if (is_array($roles)
&& count($roles) > 0)
{
foreach ($roles as $role) {
$roles_html .= '
<option value="'.$role['id'].'">'.$role['name'].'</option>';
}
}
echo '<p>
<form method="post" action="exports.php">
<fieldset class="pme-left-fieldset">
<legend>Data</legend>
<table rules="rows">
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="articles" checked="checked" />Articles</td>
<td>
[Order by <select name="articles_orderby">
<option value="id">ID</option>
<option value="datetime">Date & Time</option>
<option value="section_id">Section</option>
<option value="title">Title</option>
</select>]<br>
[Only section <select name="articles_section">
<option value="">all</option>
'.$sections_html.'
</select>]<br>
[Only author <select name="articles_author">
<option value="">all</option>
'.$authors_html.'
</select>]
</td>
</tr>
<tr class="pme-row-1">
<td valign="top"><input type="radio" name="action" value="pages" />Pages</td>
<td>
[Order by <select name="pages_orderby">
<option value="id">ID</option>
<option value="datetime">Date & Time</option>
<option value="section_id">Section</option>
<option value="title">Title</option>
</select>]<br>
[Only section <select name="pages_section">
<option value="">all</option>
'.$sections_html.'
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="boxes" />Boxes</td>
<td>
[Order by <select name="boxes_orderby">
<option value="id">ID</option>
<option value="name">Name</option>
</select>]
</td>
</tr>
<tr class="pme-row-1">
<td valign="top"><input type="radio" name="action" value="authors" />Authors</td>
<td>
[Order by <select name="authors_orderby">
<option value="id">ID</option>
<option value="type">Type</option>
<option value="name">Name</option>
</select>]<br>
[Only type <select name="authors_type">
<option value="">all</option>
<option value="internal">Internal</option>
<option value="external">External</option>
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="sections" />Sections</td>
<td>
[Order by <select name="sections_orderby">
<option value="id">ID</option>
<option value="ord">Ord</option>
<option value="name">Name</option>
</select>]<br>
[Only language <select name="sections_lang">
<option value="">all</option>
'.$languages_html.'
</select>]
</td>
</tr>
<tr class="pme-row-1">
<td valign="top"><input type="radio" name="action" value="editorials" />Editorials</td>
<td>
[Order by <select name="editorials_orderby">
<option value="id">ID</option>
<option value="section_id">Section</option>
<option value="article_id">Article</option>
<option value="datetime">Date & Time</option>
<option value="title">Title</option>
</select>]<br>
[Only section <select name="editorials_section">
<option value="">all</option>
'.$sections_html.'
</select>]<br>
[Only article <select name="editorials_article">
<option value="">all</option>
'.$articles_html.'
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="messages" />Messages</td>
<td>
[Order by <select name="messages_orderby">
<option value="id">ID</option>
<option value="article_id">Article</option>
<option value="datetime">Date & Time</option>
<option value="subject">Subject</option>
</select>]<br>
[Only article <select name="messages_article">
<option value="">all</option>
'.$articles_html.'
</select>]
</td>
</tr>
<tr class="pme-row-1">
<td valign="top"><input type="radio" name="action" value="polls" />Polls</td>
<td>
[Order by <select name="polls_orderby">
<option value="id">ID</option>
<option value="section_id">Section</option>
<option value="dt_valid_from">Date & Time</option>
</select>]<br>
[Only section <select name="polls_section">
<option value="">all</option>
'.$sections_html.'
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="feedbacks" />Feedbacks</td>
<td>
[Order by <select name="feedbacks_orderby">
<option value="id">ID</option>
<option value="section_id">Section</option>
<option value="datetime">Date & Time</option>
<option value="ip">IP address</option>
</select>]<br>
[Only section <select name="feedbacks_section">
<option value="">all</option>
'.$sections_html.'
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="newsletters" />Newsletters</td>
<td>
[Order by <select name="newsletters_orderby">
<option value="id">ID</option>
<option value="section_id">Section</option>
<option value="email">E-mail</option>
<option value="dt_added">Date & Time</option>
<option value="ip">IP address</option>
</select>]<br>
[Only section <select name="newsletters_section">
<option value="">all</option>
'.$sections_html.'
</select>]<br>
[Only active <select name="newsletters_active">
<option value="">all</option>
<option value="active">active</option>
<option value="removed">removed</option>
</select>]
</td>
</tr>
<tr class="pme-row-1">
<td valign="top"><input type="radio" name="action" value="users" />Users</td>
<td>
[Order by <select name="users_orderby">
<option value="id">ID</option>
<option value="username">Username</option>
<option value="role_id">Role</option>
</select>]<br>
[Only role <select name="users_role">
<option value="">all</option>
'.$roles_html.'
</select>]
</td>
</tr>
<tr class="pme-row-0">
<td valign="top"><input type="radio" name="action" value="sessions" />Sessions</td>
<td>
[Order by <select name="sessions_orderby">
<option value="session_id">Session ID</option>
<option value="username">Username</option>
<option value="login">Login time</option>
<option value="status">Status</option>
<option value="ip">IP address</option>
</select>]<br>
[Only username <select name="sessions_username">
<option value="">all</option>
'.$users_username_html.'
</select>]<br>
[Only status <select name="sessions_status">
<option value="">all</option>
<option value="online">Online</option>
<option value="logout">Logout</option>
<option value="timeout">Timeout</option>
<option value="failed">Failed</option>
</select>]
</td>
</tr>
</table>
</fieldset>
<fieldset class="pme-right-fieldset">
<legend>Output</legend>
<input type="radio" name="output" value="txt" />TXT - standard text file<br />
<input type="radio" name="output" value="html" />HTML - hypertext markup language<br />
<input type="radio" name="output" value="csv" checked="checked" />CSV - comma-separated values<br />
<input type="radio" name="output" value="sql" />SQL - structured query language<br />
</fieldset>
</p>
<div style="clear: both; height:10px;"></div>
<hr>
<div style="clear: both; height:10px;"></div>
<input type="submit" value="Export">
<input type="hidden" name="backup" value="" id="backup">
<input type="button" value="Complete Backup" onclick="document.getElementById(\'backup\').value=\'all\'; this.form.submit();">
</form>';
} else {
// ak je nastanvene $_POST['action']
function build_condition($cond) {
$cond = array_filter($cond);
$con = implode(' AND ', $cond);
if (strlen($con) > 0) {
$con = 'WHERE '.$con;
}
return $con;
}
function escape_simple($input) {
return '"'.mysqli_real_escape_string($input).'"';
}
function get_output($action, $output, $out = true) {
global $ezin_sys;
global $ezin_cfg;
$ret = '';
$cond = array();
switch ($action) {
case 'articles':
$cond[] = strlen($_POST['articles_section']) ? 'section_id = '.intval($_POST['articles_section']) : '';
$cond[] = strlen($_POST['articles_author']) ? 'author_id = '.intval($_POST['articles_author']) : '';
$cond[] = 'UNIX_TIMESTAMP(datetime) != 0';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['articles'],
build_condition($cond),
escape_simple($_POST['articles_orderby']));
$table = $ezin_sys['db']['tables']['articles'];
break;
case 'pages':
$cond[] = strlen($_POST['pages_section']) ? 'section_id = '.intval($_POST['pages_section']) : '';
$cond[] = '(datetime IS NULL OR UNIX_TIMESTAMP(datetime) = 0)';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['articles'],
build_condition($cond),
escape_simple($_POST['pages_orderby']));
$table = $ezin_sys['db']['tables']['articles'];
break;
case 'boxes':
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['boxes'],
build_condition($cond),
escape_simple($_POST['boxes_orderby']));
$table = $ezin_sys['db']['tables']['boxes'];
break;
case 'authors':
$cond[] = strlen($_POST['authors_type']) ? 'type = '.escape_simple(strtolower($_POST['authors_type'])) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['authors'],
build_condition($cond),
escape_simple($_POST['authors_orderby']));
$table = $ezin_sys['db']['tables']['authors'];
break;
case 'sections':
$cond[] = strlen($_POST['sections_lang']) ? 'lang = '.escape_simple(strtolower($_POST['sections_lang'])) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['sections'],
build_condition($cond),
escape_simple($_POST['sections_orderby']));
$table = $ezin_sys['db']['tables']['sections'];
break;
case 'editorials':
$cond[] = strlen($_POST['editorials_section']) ? 'section_id = '.intval($_POST['editorials_section']) : '';
$cond[] = strlen($_POST['editorials_article']) ? 'article_id = '.intval($_POST['editorials_article']) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['editorials'],
build_condition($cond),
escape_simple($_POST['editorials_orderby']));
$table = $ezin_sys['db']['tables']['editorials'];
break;
case 'messages':
$cond[] = strlen($_POST['messages_article']) ? 'article_id = '.intval($_POST['messages_article']) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['messages'],
build_condition($cond),
escape_simple($_POST['messages_orderby']));
$table = $ezin_sys['db']['tables']['messages'];
break;
case 'polls':
$cond[] = strlen($_POST['polls_section']) ? 'section_id = '.intval($_POST['polls_section']) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['polls'],
build_condition($cond),
escape_simple($_POST['polls_orderby']));
$table = $ezin_sys['db']['tables']['polls'];
break;
case 'feedbacks':
$cond[] = strlen($_POST['feedbacks_section']) ? 'section_id = '.intval($_POST['feedbacks_section']) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['feedbacks'],
build_condition($cond),
escape_simple($_POST['feedbacks_orderby']));
$table = $ezin_sys['db']['tables']['feedbacks'];
break;
case 'newsletters':
$cond[] = strlen($_POST['newsletters_section']) ? 'section_id = '.intval($_POST['newsletters_section']) : '';
$cond[] = $_POST['newsletters_active'] == 'active' ? 'dt_removed IS NULL' : '';
$cond[] = $_POST['newsletters_active'] == 'removed' ? 'dt_removed IS NOT NULL' : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['nl_emails'],
build_condition($cond),
escape_simple($_POST['newsletters_orderby']));
$table = $ezin_sys['db']['tables']['nl_emails'];
break;
case 'users':
$cond[] = strlen($_POST['users_role']) ? 'role_id = '.intval($_POST['users_role']) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['users'],
build_condition($cond),
escape_simple($_POST['users_orderby']));
$table = $ezin_sys['db']['tables']['users'];
break;
case 'sessions':
$cond[] = strlen($_POST['sessions_username']) ? 'username = '.escape_simple(strtolower($_POST['sessions_username'])) : '';
$cond[] = strlen($_POST['sessions_status']) ? 'status = '.escape_simple(strtolower($_POST['sessions_status'])) : '';
$query = sprintf('SELECT *'
.' FROM %s'
.' %s'
.' ORDER BY %s',
$ezin_sys['db']['tables']['sessions'],
build_condition($cond),
escape_simple($_POST['sessions_orderby']));
$table = $ezin_sys['db']['tables']['sessions'];
break;
default:
$ret = 'Error: Incorrect action "'.$action.'". <a href="exports.php">Exports</a>';
if ($out) {
echo $ret;
}
return $ret;
break;
}
global $ezin_db_handle;
$arr = array();
$result = mysqli_query($ezin_db_handle, $query);
while (($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) != null) {
$arr[] = $row;
}
if (count($arr) > 0) {
if ($out) {
ob_clean();
}
$ret = '';
$filename = 'export_'.$ezin_cfg['name'].'_'.$action.'_'.date('Y-m-d_H-i-s', Time());
switch($output) {
case 'txt':
if ($out) {
header('Content-type: text/plain; charset=urf-8');
header('Content-Disposition: attachment; filename="'.$filename.'.txt"');
}
foreach(array_keys($arr[0]) as $key) {
$ret .= $key."\t";
}
$ret .= "\r\n";
foreach($arr as $row) {
foreach ($row as $value) {
$value = str_replace("\n", ' ', $value);
$value = str_replace("\r", ' ', $value);
$ret .= $value."\t";
}
$ret .= "\r\n";
}
if ($out) {
echo $ret;
Exit();
} else {
return $ret;
}
break;
case 'html':
if ($out) {
header('Content-type: text/html; charset=utf-8');
header('Content-Disposition: attachment; filename="'.$filename.'.html"');
}
$ret .= '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>'.$filename.'</title>
<meta name="Generator" content="Metafox">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body>'."\r\n";
$ret .= '<h1>'.$ezin_cfg['name'].' - '.$action.'</h1>';
$ret .= '<table border="1" rules="all"><tr>'."\r\n";
foreach(array_keys($arr[0]) as $key) {
$ret .= '<th>'.$key.'</th>'."\r\n";
}
$ret .= "</tr>\r\n";
foreach($arr as $row) {
$ret .= '<tr>'."\r\n";
foreach ($row as $value) {
$ret .= '<td valign="top">'.$value."</td>\r\n";
}
$ret .= "</tr>\r\n";
}
$ret .= '</table>'."\r\n";
$ret .= '<small>Exported: '.date('Y-m-d H:i').'</small>'."\r\n";
$ret .= '</body></html>'."\r\n";
if ($out) {
echo $ret;
Exit();
} else {
return $ret;
}
break;
case 'csv':
if ($out) {
header('Content-type: application/csv; charset=WINDOWS-1250');
header('Content-Disposition: attachment; filename="'.$filename.'.csv"');
}
foreach(array_keys($arr[0]) as $key) {
$ret .= $key.';';
}
$ret .= "\n";
foreach($arr as $row) {
foreach ($row as $value) {
$value = str_replace("\n", ' ', $value);
$value = str_replace("\r", ' ', $value);
$value = iconv ($ezin_cfg['encoding'], "WINDOWS-1250", $value);
$ret .= $value.';';
}
$ret .= "\n";
}
if ($out) {
echo $ret;
Exit();
} else {
return $ret;
}
break;
case 'sql':
if ($out) {
header('Content-type: text/plain; charset='.$ezin_cfg['encoding']);
header('Content-Disposition: attachment; filename="'.$filename.'.sql"');
}
$ret .= 'INSERT INTO `'.$table.'` (`';
$ret .= implode('`, `', array_keys($arr[0]));
$ret .= "`) VALUES \r\n";
$rows = array();
foreach($arr as $row) {
$r = '(';
$values = array();
foreach ($row as $value) {
$value = str_replace("\n", ' ', $value);
$value = str_replace("\r", ' ', $value);
if (strlen($value) > 0) {
$values[] = escape_simple($value);
} else {
$values[] = 'NULL';
}
}
$r .= implode(', ', $values);
$r .= ")";
$rows[] = $r;
}
$ret .= implode(", \r\n", $rows);
$ret .= "; \r\n";
if ($out) {
echo $ret;
Exit();
} else {
return $ret;
}
break;
default:
$ret = 'Incorrect output "'.$output.'", <a href="exports.php">Exports</a>';
if ($out) {
echo $ret;
}
return $ret;
break;
}
// DEBUG
/*echo '<hr>';
print_r($query);
echo '<hr><pre>';
print_r($arr);
echo '</pre>';
echo '<hr> <a href="exports.php">Exports</a>';
*/
Exit();
} else {
echo '<p>No matching records, <a href="exports.php">back to Exports</a></p>';
}
} // function get_output
function get_backup_table($table) {
global $ezin_db_handle;
global $ezin_sys;
global $ezin_cfg;
$arr = array();
$query = 'SELECT * FROM '.$table;
$result = mysqli_query($ezin_db_handle, $query);
while (($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) != null) {
$arr[] = $row;
}
if (count($arr) <= 0) {
return '-- empty'."\r\n";
}
$ret = 'INSERT INTO `'.$table.'` (`';
$ret .= implode('`, `', array_keys($arr[0]));
$ret .= "`) VALUES \r\n";
$rows = array();
foreach($arr as $row) {
$r = '(';
$values = array();
foreach ($row as $value) {
$value = str_replace("\n", ' ', $value);
$value = str_replace("\r", ' ', $value);
if (strlen($value) > 0) {
$values[] = escape_simple($value);
} else {
$values[] = 'NULL';
}
}
$r .= implode(', ', $values);
$r .= ")";
$rows[] = $r;
}
$ret .= implode(", \r\n", $rows);
$ret .= "; \r\n";
return $ret;
}
function get_backup() {
global $ezin_sys;
$ret = array();
foreach ($ezin_sys['db']['tables'] as $name => $table) {
$ret[] = '-- ';
$ret[] = '-- '.$name.' ('.$table.')';
$ret[] = '-- ';
$ret[] = get_backup_table($table);
}
return implode("\r\n", $ret);
}
$action = $_POST['action'];
$output = $_POST['output'];
if ($_POST['backup'] == 'all') {
$filename = 'backup_'.$ezin_cfg['name'].'_'.date('Y-m-d_H-i-s', Time());
ob_clean();
header('Content-type: text/plain; charset='.$ezin_cfg['encoding']);
header('Content-Disposition: attachment; filename="'.$filename.'.sql"');
echo get_backup();
Exit();
} else {
get_output($action, $output);
}
}
}
/*
* Main
*/
if (strstr($ezin_user['role_config'], 'C')) {
ezin_admin_exports_main(0);
} elseif (ezin_admin_check_full_list_privileges($ezin_user['role_config'])) {
ezin_admin_exports_main(1);
} elseif (ezin_admin_check_restricted_list_privileges($ezin_user['role_config'])) {
ezin_admin_exports_main(2);
}
?>
Platon Group <platon@platon.sk> http://platon.sk/
|