/**
* Fetch a list of all files on in the database.
*
* The subfolders parameter is not implemented. However, as we
* currently use this function as an exclusion list, it is harmless
* to always include sub-folders.
*
* @param string $media_folder
* @param string $media_path
* @param string $subfolders
* @param string $filter
*
* @return string[]
*/
function all_media_files($media_folder, $media_path, $subfolders, $filter)
{
return Database::prepare("SELECT SQL_CACHE SQL_CALC_FOUND_ROWS TRIM(LEADING :media_path_1 FROM m_filename) AS media_path, 'OBJE' AS type, m_titl, m_id AS xref, m_file AS ged_id, m_gedcom AS gedrec, m_filename" . " FROM `##media`" . " JOIN `##gedcom_setting` ON (m_file = gedcom_id AND setting_name = 'MEDIA_DIRECTORY')" . " JOIN `##gedcom` USING (gedcom_id)" . " WHERE setting_value = :media_folder" . " AND m_filename LIKE CONCAT(:media_path_2, '%')" . " AND (SUBSTRING_INDEX(m_filename, '/', -1) LIKE CONCAT('%', :filter_1, '%')" . " OR m_titl LIKE CONCAT('%', :filter_2, '%'))" . " AND m_filename NOT LIKE 'http://%'" . " AND m_filename NOT LIKE 'https://%'")->execute(array('media_path_1' => $media_path, 'media_folder' => $media_folder, 'media_path_2' => Filter::escapeLike($media_path), 'filter_1' => Filter::escapeLike($filter), 'filter_2' => Filter::escapeLike($filter)))->fetchOneColumn();
}
/**
* Search the repositories
*
* @param string[] $query Search terms
* @param Tree[] $trees The trees to search
*
* @return Repository[]
*/
public static function searchRepositories(array $query, array $trees)
{
// Convert the query into a regular expression
$queryregex = array();
$sql = "SELECT o_id AS xref, o_file AS gedcom_id, o_gedcom AS gedcom FROM `##other` WHERE o_type = 'REPO'";
$args = array();
foreach ($query as $n => $q) {
$queryregex[] = preg_quote(I18N::strtoupper($q), '/');
$sql .= " AND o_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')";
$args['collate_' . $n] = I18N::collation();
$args['query_' . $n] = Filter::escapeLike($q);
}
$sql .= " AND o_file IN (";
foreach ($trees as $n => $tree) {
$sql .= $n ? ", " : "";
$sql .= ":tree_id_" . $n;
$args['tree_id_' . $n] = $tree->getTreeId();
}
$sql .= ")";
$list = array();
$rows = Database::prepare($sql)->execute($args)->fetchAll();
foreach ($rows as $row) {
// SQL may have matched on private data or gedcom tags, so check again against privatized data.
$record = Repository::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom);
// Ignore non-genealogy data
$gedrec = preg_replace('/\\n\\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom());
// Ignore links and tags
$gedrec = preg_replace('/\\n\\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec);
// Ignore tags
$gedrec = preg_replace('/\\n\\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec);
// Re-apply the filtering
$gedrec = I18N::strtoupper($gedrec);
foreach ($queryregex as $regex) {
if (!preg_match('/' . $regex . '/', $gedrec)) {
continue 2;
}
}
$list[] = $record;
}
$list = array_filter($list, function (Repository $x) {
return $x->canShowName();
});
return $list;
}
/**
* Generate a filtered, sourced, privacy-checked list of media objects - for the media list.
*
* @param string $folder folder to search
* @param string $subfolders either "include" or "exclude"
* @param string $sort either "file" or "title"
* @param string $filter optional search string
* @param string $form_type option OBJE/FILE/FORM/TYPE
*
* @throws \Exception
*
* @return Media[]
*/
public static function mediaList($folder, $subfolders, $sort, $filter, $form_type)
{
global $WT_TREE;
// All files in the folder, plus external files
$sql = "SELECT m_id AS xref, m_gedcom AS gedcom" . " FROM `##media`" . " WHERE m_file=?";
$args = array($WT_TREE->getTreeId());
// Only show external files when we are looking at the root folder
if ($folder == '') {
$sql_external = " OR m_filename LIKE 'http://%' OR m_filename LIKE 'https://%'";
} else {
$sql_external = "";
}
// Include / exclude subfolders (but always include external)
switch ($subfolders) {
case 'include':
$sql .= " AND (m_filename LIKE CONCAT(?, '%') {$sql_external})";
$args[] = Filter::escapeLike($folder);
break;
case 'exclude':
$sql .= " AND (m_filename LIKE CONCAT(?, '%') AND m_filename NOT LIKE CONCAT(?, '%/%') {$sql_external})";
$args[] = Filter::escapeLike($folder);
$args[] = Filter::escapeLike($folder);
break;
default:
throw new \Exception('Bad argument (subfolders=' . $subfolders . ') in QueryMedia::mediaList()');
}
// Apply search terms
if ($filter) {
$sql .= " AND (SUBSTRING_INDEX(m_filename, '/', -1) LIKE CONCAT('%', ?, '%') OR m_titl LIKE CONCAT('%', ?, '%'))";
$args[] = Filter::escapeLike($filter);
$args[] = Filter::escapeLike($filter);
}
if ($form_type) {
$sql .= " AND (m_gedcom LIKE CONCAT('%\n3 TYPE ', ?, '%'))";
$args[] = $form_type;
}
switch ($sort) {
case 'file':
$sql .= " ORDER BY m_filename";
break;
case 'title':
$sql .= " ORDER BY m_titl";
break;
default:
throw new \Exception('Bad argument (sort=' . $sort . ') in QueryMedia::mediaList()');
}
$rows = Database::prepare($sql)->execute($args)->fetchAll();
$list = array();
foreach ($rows as $row) {
$media = Media::getInstance($row->xref, $WT_TREE, $row->gedcom);
if ($media->canShow()) {
$list[] = $media;
}
}
return $list;
}
switch ($action) {
case 'load':
// AJAX callback for datatables
$search = Filter::get('search');
$search = $search['value'];
$start = Filter::getInteger('start');
$length = Filter::getInteger('length');
$sql = "SELECT SQL_CACHE SQL_CALC_FOUND_ROWS" . " '', INET_NTOA(ip_address_start), ip_address_start, INET_NTOA(ip_address_end), ip_address_end, user_agent_pattern, rule, comment, site_access_rule_id" . " FROM `##site_access_rule`";
$args = array();
if ($search) {
$sql .= " WHERE (INET_ATON(:search_1) BETWEEN ip_address_start AND ip_address_end" . " OR INET_NTOA(ip_address_start) LIKE CONCAT('%', :search_2, '%')" . " OR INET_NTOA(ip_address_end) LIKE CONCAT('%', :search_3, '%')" . " OR user_agent_pattern LIKE CONCAT('%', :search_4, '%')" . " OR comment LIKE CONCAT('%', :search_5, '%'))";
$args['search_1'] = Filter::escapeLike($search);
$args['search_2'] = Filter::escapeLike($search);
$args['search_3'] = Filter::escapeLike($search);
$args['search_4'] = Filter::escapeLike($search);
$args['search_5'] = Filter::escapeLike($search);
}
$order = Filter::getArray('order');
$sql .= ' ORDER BY';
if ($order) {
foreach ($order as $key => $value) {
if ($key > 0) {
$sql .= ',';
}
// Datatables numbers columns 0, 1, 2, ...
// MySQL numbers columns 1, 2, 3, ...
switch ($value['dir']) {
case 'asc':
$sql .= " :col_" . $key . " ASC";
break;
case 'desc':