<?php

// webtrees: Web based Family History software

// Copyright (C) 2014 webtrees development team.

//

// This program is free software; you can redistribute it and/or modify

// it under the terms of the GNU General Public License as published by

// the Free Software Foundation; either version 2 of the License, or

// (at your option) any later version.

//

// This program is distributed in the hope that it will be useful,

// but WITHOUT ANY WARRANTY; without even the implied warranty of

// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

// GNU General Public License for more details.

//

// You should have received a copy of the GNU General Public License

// along with this program; if not, write to the Free Software

// Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

use WT\Auth;

define('WT_SCRIPT_NAME', 'admin_media.php');

require './includes/session.php';

require WT_ROOT . 'includes/functions/functions_edit.php';

// type of file/object to include

$files = WT_Filter::get('files', 'local|external|unused', 'local');

// family tree setting MEDIA_DIRECTORY

$media_folders = all_media_folders();

$media_folder = WT_Filter::get('media_folder', null, ''); // MySQL needs an empty string, not NULL

// User folders may contain special characters. Restrict to actual folders.

if (!array_key_exists($media_folder, $media_folders)) {

$media_folder = reset($media_folders);

}

// prefix to filename

$media_paths = media_paths($media_folder);

$media_path = WT_Filter::get('media_path', null, ''); // MySQL needs an empty string, not NULL

// User paths may contain special characters. Restrict to actual paths.

if (!array_key_exists($media_path, $media_paths)) {

$media_path = reset($media_paths);

}

// subfolders within $media_path

$subfolders = WT_Filter::get('subfolders', 'include|exclude', 'include');

$action = WT_Filter::get('action');

////////////////////////////////////////////////////////////////////////////////

// POST callback for file deletion

////////////////////////////////////////////////////////////////////////////////

$delete_file = WT_Filter::post('delete');

if ($delete_file) {

$controller = new WT_Controller_Ajax;

// Only delete valid (i.e. unused) media files

$media_folder = WT_Filter::post('media_folder', null, ''); // MySQL needs an empty string, not NULL

$disk_files = all_disk_files ($media_folder, '', 'include', '');

if (in_array($delete_file, $disk_files)) {

$tmp = WT_DATA_DIR . $media_folder . $delete_file;

if (@unlink($tmp)) {

WT_FlashMessages::addMessage(WT_I18N::translate('The file %s was deleted.', $tmp));

} else {

WT_FlashMessages::addMessage(WT_I18N::translate('The file %s could not be deleted.', $tmp));

}

$tmp = WT_DATA_DIR . $media_folder . 'thumbs/' . $delete_file;

if (file_exists($tmp)) {

if (@unlink($tmp)) {

WT_FlashMessages::addMessage(WT_I18N::translate('The file %s was deleted.', $tmp));

} else {

WT_FlashMessages::addMessage(WT_I18N::translate('The file %s could not be deleted.', $tmp));

}

}

} else {

// File no longer exists? Maybe it was already deleted or renamed.

}

$controller->pageHeader();

exit;

}

////////////////////////////////////////////////////////////////////////////////

// GET callback for server-side pagination

////////////////////////////////////////////////////////////////////////////////

switch($action) {

case 'load_json':

Zend_Session::writeClose();

$search = WT_Filter::get('search');

$search = $search['value'];

$start = WT_Filter::getInteger('start');

$length = WT_Filter::getInteger('length');

switch ($files) {

case 'local':

// Filtered rows

$SELECT1 =

"SELECT SQL_CACHE SQL_CALC_FOUND_ROWS TRIM(LEADING ? FROM m_filename) AS media_path, m_id AS xref, m_titl, m_file AS gedcom_id, m_gedcom AS gedcom" .

" FROM `##media`" .

" JOIN `##gedcom_setting` ON (m_file = gedcom_id AND setting_name = 'MEDIA_DIRECTORY')" .

" JOIN `##gedcom` USING (gedcom_id)" .

" WHERE setting_value=?" .

" AND m_filename LIKE CONCAT(?, '%')" .

" AND (SUBSTRING_INDEX(m_filename, '/', -1) LIKE CONCAT('%', ?, '%')" .

" OR m_titl LIKE CONCAT('%', ?, '%'))" .

" AND m_filename NOT LIKE 'http://%'" .

" AND m_filename NOT LIKE 'https://%'";

$ARGS1 = array(

$media_path,

$media_folder,

WT_Filter::escapeLike($media_path),

WT_Filter::escapeLike($search),

WT_Filter::escapeLike($search)

);

// Unfiltered rows

$SELECT2 =

"SELECT SQL_CACHE COUNT(*)" .

" FROM `##media`" .

" JOIN `##gedcom_setting` ON (m_file = gedcom_id AND setting_name = 'MEDIA_DIRECTORY')" .

" WHERE setting_value=?" .

" AND m_filename LIKE CONCAT(?, '%')" .

" AND m_filename NOT LIKE 'http://%'" .

" AND m_filename NOT LIKE 'https://%'";

$ARGS2 = array(

$media_folder,

$media_path

);

if ($subfolders=='exclude') {

$SELECT1 .= " AND m_filename NOT LIKE CONCAT(?, '%/%')";

$ARGS1[] = WT_Filter::escapeLike($media_path);

$SELECT2 .= " AND m_filename NOT LIKE CONCAT(?, '%/%')";

$ARGS2[] = WT_Filter::escapeLike($media_path);

}

if ($length > 0) {

$LIMIT = " LIMIT " . $start . ',' . $length;

} else {

$LIMIT = "";

}

$order = WT_Filter::get('order');

if ($order) {

$ORDER_BY = " ORDER BY ";

for ($i = 0; $i < count($order); ++$i) {

if ($i > 0) {

$ORDER_BY .= ',';

}

// Datatables numbers columns 0, 1, 2, ...

// MySQL numbers columns 1, 2, 3, ...

switch ($order[$i]['dir']) {

case 'asc':

$ORDER_BY .= (1 + $order[$i]['column']) . ' ASC ';

break;

case 'desc':

$ORDER_BY .= (1 + $order[$i]['column']) . ' DESC ';

break;

}

}

} else {

$ORDER_BY = " ORDER BY 1 ASC";

}

$rows = WT_DB::prepare($SELECT1.$ORDER_BY.$LIMIT)->execute($ARGS1)->fetchAll();

// Total filtered/unfiltered rows

$recordsFiltered = WT_DB::prepare("SELECT FOUND_ROWS()")->fetchColumn();

$recordsTotal = WT_DB::prepare($SELECT2)->execute($ARGS2)->fetchColumn();

$data = array();

foreach ($rows as $row) {

$media = WT_Media::getInstance($row->xref, $row->gedcom_id);

$data[] = array(

media_file_info($media_folder, $media_path, $row->media_path),

$media->displayImage(),

media_object_info($media),

);

}

break;

case 'external':

// Filtered rows

$SELECT1 =

"SELECT SQL_CACHE SQL_CALC_FOUND_ROWS m_filename, m_id AS xref, m_titl, m_file AS gedcom_id, m_gedcom AS gedcom" .

" FROM `##media`" .

" WHERE (m_filename LIKE 'http://%' OR m_filename LIKE 'https://%')" .

" AND (m_filename LIKE CONCAT('%', ?, '%') OR m_titl LIKE CONCAT('%', ?, '%'))";

$ARGS1 = array(

WT_Filter::escapeLike($search),

WT_Filter::escapeLike($search)

);

// Unfiltered rows

$SELECT2 =

"SELECT SQL_CACHE COUNT(*)" .

" FROM `##media`" .

" WHERE (m_filename LIKE 'http://%' OR m_filename LIKE 'https://%')";

$ARGS2 = array();

if ($length>0) {

$LIMIT = " LIMIT " . $start . ',' . $length;

} else {

$LIMIT = "";

}

$order = WT_Filter::get('order');

if ($order) {

$ORDER_BY = " ORDER BY ";

for ($i=0; $i < count($order); ++$i) {

if ($i > 0) {

$ORDER_BY .= ',';

}

// Datatables numbers columns 0, 1, 2, ...

// MySQL numbers columns 1, 2, 3, ...

switch ($order[$i]['dir']) {

case 'asc':

$ORDER_BY .= (1 + $order[$i]['column']).' ASC ';

break;

case 'desc':

$ORDER_BY .= (1 + $order[$i]['column']).' DESC ';

break;

}

}

} else {

$ORDER_BY = " ORDER BY 1 ASC";

}

$rows = WT_DB::prepare($SELECT1.$ORDER_BY.$LIMIT)->execute($ARGS1)->fetchAll();

// Total filtered/unfiltered rows

$recordsFiltered = WT_DB::prepare("SELECT FOUND_ROWS()")->fetchColumn();

$recordsTotal = WT_DB::prepare($SELECT2)->execute($ARGS2)->fetchColumn();

$data = array();

foreach ($rows as $row) {

$media = WT_Media::getInstance($row->xref, $row->gedcom_id, $row->gedcom);

$data[] = array(

WT_Gedcom_Tag::getLabelValue('URL', $row->m_filename),

$media->displayImage(),

media_object_info($media),

);

}

break;

case 'unused':

// Which trees use this media folder?

$media_trees = WT_DB::prepare(

"SELECT gedcom_name, gedcom_name" .

" FROM `##gedcom`" .

" JOIN `##gedcom_setting` USING (gedcom_id)" .

" WHERE setting_name='MEDIA_DIRECTORY' AND setting_value=?"

)->execute(array($media_folder))->fetchAssoc();

$disk_files = all_disk_files ($media_folder, $media_path, $subfolders, $search);

$db_files = all_media_files($media_folder, $media_path, $subfolders, $search);

// All unused files

$unused_files = array_diff($disk_files, $db_files);

$recordsTotal = count($unused_files);

// Filter unused files

if ($search) {

// Lambda functions can’t be used until PHP5.3

//$unused_files = array_filter($unused_files, function($x) use ($sSearch) {return strpos($x, $sSearch)!==false;});

function substr_search($x) {global $search; return strpos($x, $search)!==false;}

$unused_files = array_filter($unused_files, 'substr_search');

}

$recordsFiltered = count($unused_files);

// Sort files - only option is column 0

sort($unused_files);

$order = WT_Filter::get('order');

if ($order && $order[0]['dir'] === 'desc') {

$unused_files = array_reverse($unused_files);

}

// Paginate unused files

$unused_files = array_slice($unused_files, $start, $length);

$data = array();

foreach ($unused_files as $unused_file) {

$full_path = WT_DATA_DIR . $media_folder . $media_path . $unused_file;

$thumb_path = WT_DATA_DIR . $media_folder . 'thumbs/' . $media_path . $unused_file;

if (!file_exists($thumb_path)) {

$thumb_path = $full_path;

}

$imgsize=@getimagesize($thumb_path);

if ($imgsize && $imgsize[0] && $imgsize[1]) {

// We can’t create a URL (not in public_html) or use the media firewall (no such object)

// so just the base64-encoded image inline.

$img = '<img src="data:' . $imgsize['mime'] . ';base64,' . base64_encode(file_get_contents($thumb_path)) . '" class="thumbnail" ' . $imgsize[3] . '" style="max-width:100px;height:auto;">';

} else {

$img = '-';

}

// Is there a pending record for this file?

$exists_pending = WT_DB::prepare(

"SELECT 1 FROM `##change` WHERE status='pending' AND new_gedcom LIKE CONCAT('%\n1 FILE ', ?, '\n%')"

)->execute(array(WT_Filter::escapeLike($unused_file)))->fetchOne();

// Form to create new media object in each tree

$create_form='';

if (!$exists_pending) {

foreach ($media_trees as $media_tree) {

$create_form .=

'<p><a onclick="window.open(\'addmedia.php?action=showmediaform&amp;ged=' . rawurlencode($media_tree) . '&amp;filename=' . rawurlencode(basename($unused_file)) . '&amp;folder=' . rawurlencode(dirname($unused_file)) . '\', \'_blank\', edit_window_specs); return false;">' . WT_I18N::translate('Create') . '</a> — ' . WT_Filter::escapeHtml($media_tree) . '<p>';

}

}

$conf = WT_I18N::translate('Are you sure you want to delete “%s”?', $unused_file);

$delete_link =

'<p><a onclick="if (confirm(\'' . WT_Filter::escapeJs($conf) . '\')) jQuery.post(\'admin_media.php\',{delete:\'' .WT_Filter::escapeJs($media_path . $unused_file) . '\',media_folder:\'' . WT_Filter::escapeJs($media_folder) . '\'},function(){location.reload();})" href="#">' . WT_I18N::Translate('Delete') . '</a></p>';

$data[] = array(

media_file_info($media_folder, $media_path, $unused_file) . $delete_link,

$img,

$create_form,

);

}

break;

}

header('Content-type: application/json');

echo json_encode(array( // See http://www.datatables.net/usage/server-side

'draw' => WT_Filter::getInteger('draw'), // String, but always an integer

'recordsTotal' => $recordsTotal,

'recordsFiltered' => $recordsFiltered,

'data' => $data

));

exit;

}

////////////////////////////////////////////////////////////////////////////////

// Local functions

////////////////////////////////////////////////////////////////////////////////

// A unique list of media folders, from all trees.

function all_media_folders() {

}

function media_paths($media_folder) {

}

function scan_dirs($dir, $recursive, $filter) {

}

// Fetch a list of all files on disk

function all_disk_files($media_folder, $media_path, $subfolders, $filter) {

return scan_dirs(WT_DATA_DIR . $media_folder . $media_path, $subfolders=='include', $filter);

}

// Fetch a list of all files on in the database

function all_media_files($media_folder, $media_path, $subfolders, $filter) {

}

function media_file_info($media_folder, $media_path, $file) {

}

function media_object_info(WT_Media $media) {

}

////////////////////////////////////////////////////////////////////////////////

// Start here

////////////////////////////////////////////////////////////////////////////////

// Preserver the pagination/filtering/sorting between requests, so that the

// browser’s back button works. Pagination is dependent on the currently

// selected folder.

$table_id=md5($files.$media_folder.$media_path.$subfolders);

$controller=new WT_Controller_Page();

$controller

->restrictAccess(Auth::isAdmin())

->setPageTitle(WT_I18N::translate('Media'))

->addExternalJavascript(WT_JQUERY_DATATABLES_URL)

->pageHeader()

->addInlineJavascript('

jQuery("#media-table-' . $table_id . '").dataTable({

dom: \'<"H"pf<"dt-clear">irl>t<"F"pl>\',

processing: true,

serverSide: true,

ajax: "'.WT_SERVER_NAME.WT_SCRIPT_PATH.WT_SCRIPT_NAME.'?action=load_json&files='.$files.'&media_folder='.$media_folder.'&media_path='.$media_path.'&subfolders='.$subfolders.'",

' . WT_I18N::datatablesI18N(array(5, 10, 20, 50, 100, 500, 1000, -1)) . ',

jQueryUI: true,

autoWidth:false,

pageLength: 10,

pagingType: "full_numbers",

stateSave: true,

stateDuration: 300,

columns: [

{},

{ sortable: false },

{ sortable: ' . ($files=='unused' ? 'false' : 'true') . ' }

]

});

');

?>

<form method="get" action="<?php echo WT_SCRIPT_NAME; ?>">

<table class="media_items">

<tr>

<th><?php echo WT_I18N::translate('Media files'); ?></th>

<th><?php echo WT_I18N::translate('Media folders'); ?></th>

</tr>

<tr>

<td>

<input type="radio" name="files" value="local"<?php echo $files=='local' ? ' checked="checked"' : ''; ?> onchange="this.form.submit();">

<?php echo /* I18N: “Local files” are stored on this computer */ WT_I18N::translate('Local files'); ?>

<br>

<input type="radio" name="files" value="external"<?php echo $files=='external' ? ' checked="checked"' : ''; ?> onchange="this.form.submit();">

<?php echo /* I18N: “External files” are stored on other computers */ WT_I18N::translate('External files'); ?>

<br>

<input type="radio" name="files" value="unused"<?php echo $files=='unused' ? ' checked="checked"' : ''; ?> onchange="this.form.submit();">

<?php echo WT_I18N::translate('Unused files'); ?>

</td>

<td>

<?php

switch ($files) {

case 'local':

case 'unused':

$extra = 'onchange="this.form.submit();"';

echo

'<span dir="ltr">', // The full path will be LTR or mixed LTR/RTL. Force LTR.

WT_DATA_DIR;

// Don’t show a list of media folders if it just contains one folder

if (count($media_folders)>1) {

echo '&nbsp;', select_edit_control('media_folder', $media_folders, null, $media_folder, $extra);

} else {

echo $media_folder, '<input type="hidden" name="media_folder" value="', WT_Filter::escapeHtml($media_folder), '">';

}

// Don’t show a list of subfolders if it just contains one subfolder

if (count($media_paths)>1) {

echo '&nbsp;', select_edit_control('media_path', $media_paths, null, $media_path, $extra);

} else {

echo $media_path, '<input type="hidden" name="media_path" value="', WT_Filter::escapeHtml($media_path), '">';

}

echo

'</span>',

'<div>',

'<input type="radio" name="subfolders" value="include"', ($subfolders=='include' ? ' checked="checked"' : ''), ' onchange="this.form.submit();">',

WT_I18N::translate('Include subfolders'),

'<br>',

'<input type="radio" name="subfolders" value="exclude"', ($subfolders=='exclude' ? ' checked="checked"' : ''), ' onchange="this.form.submit();">',

WT_I18N::translate('Exclude subfolders'),

'</div>';

break;

case 'external':

echo WT_I18N::translate('External media files have a URL instead of a filename.');

echo '<input type="hidden" name="media_folder" value="', WT_Filter::escapeHtml($media_folder), '">';

echo '<input type="hidden" name="media_path" value="', WT_Filter::escapeHtml($media_path), '">';

break;

}

?>

</td>

</tr>

</table>

</form>

<br>

<br>

<table class="media_table" id="media-table-<?php echo $table_id ?>">

<thead>

<tr>

<th><?php echo WT_I18N::translate('Media file'); ?></th>

<th><?php echo WT_I18N::translate('Media'); ?></th>

<th><?php echo WT_I18N::translate('Media object'); ?></th>

</tr>

</thead>

<tbody>

</tbody>

</table>